Denes Bodo created SQOOP-3451:
---------------------------------
Summary: 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
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)
