Uploaded image for project: 'Sqoop'
  1. Sqoop
  2. SQOOP-3451

Importing FLOAT from Oracle to Hive results in INTEGER

    XMLWordPrintableJSON

    Details

      Description

      We ran into an issue where there is a table created in Oracle 11g:

      create table floattest (column1 float(30), column2 number(30,-127), column3 number(30));
      

      We want to import date from Oracle to Hive:

      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
      

      In Sqoop logs we see the following:

      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
      

      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 Mahesh Balakrishnan and Andrew Miller

        Attachments

          Activity

            People

            • Assignee:
              dionusos Dénes Bodó
              Reporter:
              dionusos Dénes Bodó
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: