sqoop-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Denes Bodo (Jira)" <j...@apache.org>
Subject [jira] [Assigned] (SQOOP-3451) Importing FLOAT from Oracle to Hive results in INTEGER
Date Thu, 26 Sep 2019 13:36:00 GMT

     [ https://issues.apache.org/jira/browse/SQOOP-3451?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Denes Bodo reassigned SQOOP-3451:
---------------------------------

    Assignee: Denes Bodo

> Importing FLOAT from Oracle to Hive results in INTEGER
> ------------------------------------------------------
>
>                 Key: SQOOP-3451
>                 URL: https://issues.apache.org/jira/browse/SQOOP-3451
>             Project: Sqoop
>          Issue Type: Bug
>          Components: codegen, connectors/oracle, hive-integration
>    Affects Versions: 1.4.7
>            Reporter: Denes Bodo
>            Assignee: Denes Bodo
>            Priority: Major
>
> We ran into an issue where there is a table created in Oracle 11g:
> {noformat}
> create table floattest (column1 float(30), column2 number(30,-127), column3 number(30));
> {noformat}
> We want to import date from Oracle to Hive:
> {noformat}
> sqoop import -D mapred.child.java.opts='-Djava.security.egd=file:/dev/../dev/urandom'
-Dmapreduce.job.queuename=default --connect "jdbc:oracle:thin:@DBHOST:1521/xe" --username
sqoop --password sqoop --table floattest --hcatalog-database default --hcatalog-table floattest
--create-hcatalog-table --hcatalog-external-table --hcatalog-storage-stanza "stored as orc"
-m 1 --columns COLUMN1,COLUMN2,COLUMN3 --verbose
> {noformat}
> In Sqoop logs we see the following:
> {noformat}
> 19/09/24 13:51:45 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM floattest
t WHERE 1=0
> 19/09/24 13:51:45 DEBUG manager.SqlManager: Found column COLUMN1 of type [2, 30, -127]
> 19/09/24 13:51:45 DEBUG manager.SqlManager: Found column COLUMN2 of type [2, 30, -84]
> 19/09/24 13:51:45 DEBUG manager.SqlManager: Found column COLUMN3 of type [2, 30, 0]
> 19/09/24 13:51:45 INFO hcat.SqoopHCatUtilities: Database column names projected : [COLUMN1,
COLUMN2, COLUMN3]
> 19/09/24 13:51:45 INFO hcat.SqoopHCatUtilities: Database column name - info map :
>         COLUMN3 : [Type : 2,Precision : 30,Scale : 0]
>         COLUMN2 : [Type : 2,Precision : 30,Scale : -84]
>         COLUMN1 : [Type : 2,Precision : 30,Scale : -127]
> 19/09/24 13:51:45 INFO hcat.SqoopHCatUtilities: Creating HCatalog table default.floattest
for import
> 19/09/24 13:51:45 INFO hcat.SqoopHCatUtilities: HCatalog Create table statement:
> create external table `default`.`floattest` (
>         `column1` decimal(30),
>         `column2` decimal(30),
>         `column3` decimal(30))
> stored as orc
> {noformat}
> From this output we can see that Oracle states about column1 has Type=2 which is NUMERIC
(regarding to https://docs.oracle.com/javase/7/docs/api/constant-values.html#java.sql.Types.FLOAT).
Sqoop translates NUMERIC to DECIMAL (https://github.com/apache/sqoop/blob/trunk/src/java/org/apache/sqoop/mapreduce/hcat/SqoopHCatUtilities.java#L1050L1107).
Due to Oracle uses {{scale=-127}} to sign about a NUMERIC that it is a FLOAT instead of stating
{{Type=6}}, Sqoop creates integers (decimal with 0 scale) from NUMBER.
> I think it is the fault of Oracle as it does not use Java Type=6 to sign type of a float.
What do you think?
> ----
> Thank you for the details and investigation to [~mbalakrishnan] and Andrew Miller



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Mime
View raw message