Sqoop
  1. Sqoop
  2. SQOOP-467

Add support for Oracle BINARY_FLOAT and BINARY_DOUBLE data types

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.4.1-incubating
    • Fix Version/s: 1.4.3
    • Component/s: connectors
    • Labels:
      None

      Description

      I get this error when I try to run import from Oracle table which has columns of type BINARY_FLOAT and BINARY_DOUBLE.

      12/03/23 11:19:05 ERROR orm.ClassWriter: Cannot resolve SQL type 100
      12/03/23 11:19:05 ERROR orm.ClassWriter: Cannot resolve SQL type 101
      12/03/23 11:19:05 ERROR orm.ClassWriter: No Java type for SQL type 100 for column B
      12/03/23 11:19:05 ERROR orm.ClassWriter: No Java type for SQL type 101 for column C
      
      1. SQOOP-467-2.patch
        13 kB
        Cheolsoo Park
      2. SQOOP-467-3.patch
        13 kB
        Cheolsoo Park
      3. SQOOP-467-4.patch
        11 kB
        Cheolsoo Park
      4. SQOOP-467-5.patch
        10 kB
        Cheolsoo Park

        Issue Links

          Activity

          Hide
          Hudson added a comment -

          Integrated in Sqoop-ant-jdk-1.6-hadoop200 #268 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop200/268/)
          SQOOP-467: Add support for Oracle BINARY_FLOAT and BINARY_DOUBLE data types (Revision 5eb987a785d89272ac21d890ba89ce56e1446f48)

          Result = SUCCESS
          jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=5eb987a785d89272ac21d890ba89ce56e1446f48
          Files :

          • src/java/org/apache/sqoop/manager/OracleManager.java
          • src/java/org/apache/sqoop/manager/MySQLManager.java
          • src/test/com/cloudera/sqoop/manager/OracleCompatTest.java
          Show
          Hudson added a comment - Integrated in Sqoop-ant-jdk-1.6-hadoop200 #268 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop200/268/ ) SQOOP-467 : Add support for Oracle BINARY_FLOAT and BINARY_DOUBLE data types (Revision 5eb987a785d89272ac21d890ba89ce56e1446f48) Result = SUCCESS jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=5eb987a785d89272ac21d890ba89ce56e1446f48 Files : src/java/org/apache/sqoop/manager/OracleManager.java src/java/org/apache/sqoop/manager/MySQLManager.java src/test/com/cloudera/sqoop/manager/OracleCompatTest.java
          Hide
          Hudson added a comment -

          Integrated in Sqoop-ant-jdk-1.6-hadoop23 #413 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop23/413/)
          SQOOP-467: Add support for Oracle BINARY_FLOAT and BINARY_DOUBLE data types (Revision 5eb987a785d89272ac21d890ba89ce56e1446f48)

          Result = SUCCESS
          jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=5eb987a785d89272ac21d890ba89ce56e1446f48
          Files :

          • src/java/org/apache/sqoop/manager/MySQLManager.java
          • src/test/com/cloudera/sqoop/manager/OracleCompatTest.java
          • src/java/org/apache/sqoop/manager/OracleManager.java
          Show
          Hudson added a comment - Integrated in Sqoop-ant-jdk-1.6-hadoop23 #413 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop23/413/ ) SQOOP-467 : Add support for Oracle BINARY_FLOAT and BINARY_DOUBLE data types (Revision 5eb987a785d89272ac21d890ba89ce56e1446f48) Result = SUCCESS jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=5eb987a785d89272ac21d890ba89ce56e1446f48 Files : src/java/org/apache/sqoop/manager/MySQLManager.java src/test/com/cloudera/sqoop/manager/OracleCompatTest.java src/java/org/apache/sqoop/manager/OracleManager.java
          Hide
          Hudson added a comment -

          Integrated in Sqoop-ant-jdk-1.6-hadoop100 #258 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop100/258/)
          SQOOP-467: Add support for Oracle BINARY_FLOAT and BINARY_DOUBLE data types (Revision 5eb987a785d89272ac21d890ba89ce56e1446f48)

          Result = SUCCESS
          jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=5eb987a785d89272ac21d890ba89ce56e1446f48
          Files :

          • src/java/org/apache/sqoop/manager/MySQLManager.java
          • src/java/org/apache/sqoop/manager/OracleManager.java
          • src/test/com/cloudera/sqoop/manager/OracleCompatTest.java
          Show
          Hudson added a comment - Integrated in Sqoop-ant-jdk-1.6-hadoop100 #258 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop100/258/ ) SQOOP-467 : Add support for Oracle BINARY_FLOAT and BINARY_DOUBLE data types (Revision 5eb987a785d89272ac21d890ba89ce56e1446f48) Result = SUCCESS jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=5eb987a785d89272ac21d890ba89ce56e1446f48 Files : src/java/org/apache/sqoop/manager/MySQLManager.java src/java/org/apache/sqoop/manager/OracleManager.java src/test/com/cloudera/sqoop/manager/OracleCompatTest.java
          Hide
          Hudson added a comment -

          Integrated in Sqoop-ant-jdk-1.6-hadoop20 #261 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop20/261/)
          SQOOP-467: Add support for Oracle BINARY_FLOAT and BINARY_DOUBLE data types (Revision 5eb987a785d89272ac21d890ba89ce56e1446f48)

          Result = SUCCESS
          jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=5eb987a785d89272ac21d890ba89ce56e1446f48
          Files :

          • src/test/com/cloudera/sqoop/manager/OracleCompatTest.java
          • src/java/org/apache/sqoop/manager/OracleManager.java
          • src/java/org/apache/sqoop/manager/MySQLManager.java
          Show
          Hudson added a comment - Integrated in Sqoop-ant-jdk-1.6-hadoop20 #261 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop20/261/ ) SQOOP-467 : Add support for Oracle BINARY_FLOAT and BINARY_DOUBLE data types (Revision 5eb987a785d89272ac21d890ba89ce56e1446f48) Result = SUCCESS jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=5eb987a785d89272ac21d890ba89ce56e1446f48 Files : src/test/com/cloudera/sqoop/manager/OracleCompatTest.java src/java/org/apache/sqoop/manager/OracleManager.java src/java/org/apache/sqoop/manager/MySQLManager.java
          Jarek Jarcec Cecho made changes -
          Status Patch Available [ 10002 ] Resolved [ 5 ]
          Fix Version/s 1.4.3 [ 12322644 ]
          Resolution Fixed [ 1 ]
          Hide
          Jarek Jarcec Cecho added a comment -

          Committed: https://git-wip-us.apache.org/repos/asf?p=sqoop.git;a=commit;h=5eb987a785d89272ac21d890ba89ce56e1446f48

          Thank you very much for your contribution Cheolsoo!

          Jarcec

          Show
          Jarek Jarcec Cecho added a comment - Committed: https://git-wip-us.apache.org/repos/asf?p=sqoop.git;a=commit;h=5eb987a785d89272ac21d890ba89ce56e1446f48 Thank you very much for your contribution Cheolsoo! Jarcec
          Cheolsoo Park made changes -
          Attachment SQOOP-467-5.patch [ 12551803 ]
          Cheolsoo Park made changes -
          Attachment SQOOP-467-4.patch [ 12551792 ]
          Cheolsoo Park made changes -
          Attachment SQOOP-467-3.patch [ 12551742 ]
          Hide
          Cheolsoo Park added a comment -

          Thanks Jarcec for taking a look at this.

          I rebased the patch and uploaded it to the RB.

          Show
          Cheolsoo Park added a comment - Thanks Jarcec for taking a look at this. I rebased the patch and uploaded it to the RB.
          Cheolsoo Park made changes -
          Remote Link This issue links to "Review Board (Web Link)" [ 11412 ]
          Hide
          Jarek Jarcec Cecho added a comment -

          Hi Cheolsoo,
          please accept my apology for letting this without attention for so long. Would you mind rebasing your patch against current trunk and put it on Review board so that we can get it committed?

          Jarcec

          Show
          Jarek Jarcec Cecho added a comment - Hi Cheolsoo, please accept my apology for letting this without attention for so long. Would you mind rebasing your patch against current trunk and put it on Review board so that we can get it committed? Jarcec
          Cheolsoo Park made changes -
          Attachment SQOOP-467-2.patch [ 12530573 ]
          Hide
          jiraposter@reviews.apache.org added a comment -

          -----------------------------------------------------------
          This is an automatically generated e-mail. To reply, visit:
          https://reviews.apache.org/r/4641/
          -----------------------------------------------------------

          (Updated 2012-04-09 23:03:39.230896)

          Review request for Sqoop.

          Changes
          -------

          Fix minor typos.

          Summary
          -------

          Sqoop import does not support BINARY_FLOAT and BINARY_DOUBLE for Oracle db.

          The changes include:
          1) Add 3 methods to SqlManager class: toDbSpecificJavaType(), toDbSpecificHiveType(), and toDbSpecificAvroType().
          2) Override these methods in OracleManager to identify Oracle-specific types based on their type name strings.
          3) Eliminate uses of reflection from OracleManager.
          4) Factor out special handling for "YEAR" in MySQLManager into a separate method: fixupSqlType().
          5) Add unit tests to OracleCompatTest for BINARY_FLOAT and BINARY_DOUBLE.

          Note that Hive/Avro support for BINARY_FLOAT and BINARY_DOUBLE is not added here. It will be implemented in the future.

          This addresses bug SQOOP-467.
          https://issues.apache.org/jira/browse/SQOOP-467

          Diffs (updated)


          /src/java/org/apache/sqoop/manager/MySQLManager.java 1310648
          /src/java/org/apache/sqoop/manager/OracleManager.java 1310648
          /src/java/org/apache/sqoop/manager/SqlManager.java 1310648
          /src/test/com/cloudera/sqoop/manager/OracleCompatTest.java 1310648

          Diff: https://reviews.apache.org/r/4641/diff

          Testing
          -------

          1) Verified with Oracle XE 10g that new tests pass.
          2) Ran ant test, ant test -Dthirdparty=true, and ant checkstyle.

          Thanks,

          Cheolsoo

          Show
          jiraposter@reviews.apache.org added a comment - ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4641/ ----------------------------------------------------------- (Updated 2012-04-09 23:03:39.230896) Review request for Sqoop. Changes ------- Fix minor typos. Summary ------- Sqoop import does not support BINARY_FLOAT and BINARY_DOUBLE for Oracle db. The changes include: 1) Add 3 methods to SqlManager class: toDbSpecificJavaType(), toDbSpecificHiveType(), and toDbSpecificAvroType(). 2) Override these methods in OracleManager to identify Oracle-specific types based on their type name strings. 3) Eliminate uses of reflection from OracleManager. 4) Factor out special handling for "YEAR" in MySQLManager into a separate method: fixupSqlType(). 5) Add unit tests to OracleCompatTest for BINARY_FLOAT and BINARY_DOUBLE. Note that Hive/Avro support for BINARY_FLOAT and BINARY_DOUBLE is not added here. It will be implemented in the future. This addresses bug SQOOP-467 . https://issues.apache.org/jira/browse/SQOOP-467 Diffs (updated) /src/java/org/apache/sqoop/manager/MySQLManager.java 1310648 /src/java/org/apache/sqoop/manager/OracleManager.java 1310648 /src/java/org/apache/sqoop/manager/SqlManager.java 1310648 /src/test/com/cloudera/sqoop/manager/OracleCompatTest.java 1310648 Diff: https://reviews.apache.org/r/4641/diff Testing ------- 1) Verified with Oracle XE 10g that new tests pass. 2) Ran ant test, ant test -Dthirdparty=true, and ant checkstyle. Thanks, Cheolsoo
          Hide
          jiraposter@reviews.apache.org added a comment -

          -----------------------------------------------------------
          This is an automatically generated e-mail. To reply, visit:
          https://reviews.apache.org/r/4641/
          -----------------------------------------------------------

          (Updated 2012-04-09 22:13:15.609452)

          Review request for Sqoop.

          Changes
          -------

          Use column type name strings instead of reflection to handle DB-specific types.

          Summary (updated)
          -------

          Sqoop import does not support BINARY_FLOAT and BINARY_DOUBLE for Oracle db.

          The changes include:
          1) Add 3 methods to SqlManager class: toDbSpecificJavaType(), toDbSpecificHiveType(), and toDbSpecificAvroType().
          2) Override these methods in OracleManager to identify Oracle-specific types based on their type name strings.
          3) Eliminate uses of reflection from OracleManager.
          4) Factor out special handling for "YEAR" in MySQLManager into a separate method: fixupSqlType().
          5) Add unit tests to OracleCompatTest for BINARY_FLOAT and BINARY_DOUBLE.

          Note that Hive/Avro support for BINARY_FLOAT and BINARY_DOUBLE is not added here. It will be implemented in the future.

          This addresses bug SQOOP-467.
          https://issues.apache.org/jira/browse/SQOOP-467

          Diffs (updated)


          /src/java/org/apache/sqoop/manager/MySQLManager.java 1310648
          /src/java/org/apache/sqoop/manager/OracleManager.java 1310648
          /src/java/org/apache/sqoop/manager/SqlManager.java 1310648
          /src/test/com/cloudera/sqoop/manager/OracleCompatTest.java 1310648

          Diff: https://reviews.apache.org/r/4641/diff

          Testing
          -------

          1) Verified with Oracle XE 10g that new tests pass.
          2) Ran ant test, ant test -Dthirdparty=true, and ant checkstyle.

          Thanks,

          Cheolsoo

          Show
          jiraposter@reviews.apache.org added a comment - ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4641/ ----------------------------------------------------------- (Updated 2012-04-09 22:13:15.609452) Review request for Sqoop. Changes ------- Use column type name strings instead of reflection to handle DB-specific types. Summary (updated) ------- Sqoop import does not support BINARY_FLOAT and BINARY_DOUBLE for Oracle db. The changes include: 1) Add 3 methods to SqlManager class: toDbSpecificJavaType(), toDbSpecificHiveType(), and toDbSpecificAvroType(). 2) Override these methods in OracleManager to identify Oracle-specific types based on their type name strings. 3) Eliminate uses of reflection from OracleManager. 4) Factor out special handling for "YEAR" in MySQLManager into a separate method: fixupSqlType(). 5) Add unit tests to OracleCompatTest for BINARY_FLOAT and BINARY_DOUBLE. Note that Hive/Avro support for BINARY_FLOAT and BINARY_DOUBLE is not added here. It will be implemented in the future. This addresses bug SQOOP-467 . https://issues.apache.org/jira/browse/SQOOP-467 Diffs (updated) /src/java/org/apache/sqoop/manager/MySQLManager.java 1310648 /src/java/org/apache/sqoop/manager/OracleManager.java 1310648 /src/java/org/apache/sqoop/manager/SqlManager.java 1310648 /src/test/com/cloudera/sqoop/manager/OracleCompatTest.java 1310648 Diff: https://reviews.apache.org/r/4641/diff Testing ------- 1) Verified with Oracle XE 10g that new tests pass. 2) Ran ant test, ant test -Dthirdparty=true, and ant checkstyle. Thanks, Cheolsoo
          Hide
          jiraposter@reviews.apache.org added a comment -

          -----------------------------------------------------------
          This is an automatically generated e-mail. To reply, visit:
          https://reviews.apache.org/r/4641/
          -----------------------------------------------------------

          (Updated 2012-04-05 04:30:35.627666)

          Review request for Sqoop.

          Changes
          -------

          Simplify tests.

          Summary
          -------

          Sqoop import does not support BINARY_FLOAT and BINARY_DOUBLE for Oracle db.

          The patch includes:
          1) Map BINARY_FLOAT and BINARY_DOUBLE to Java float and double for Oracle.
          2) Add test cases to OracleCompatTest to verify the mapping.

          This addresses bug SQOOP-467.
          https://issues.apache.org/jira/browse/SQOOP-467

          Diffs (updated)


          /src/java/org/apache/sqoop/manager/OracleManager.java 1309536
          /src/test/com/cloudera/sqoop/manager/OracleCompatTest.java 1309536

          Diff: https://reviews.apache.org/r/4641/diff

          Testing
          -------

          1) Verified with Oracle XE 10g that new tests pass.
          2) Ran ant test, ant test -Dthirdparty=true, and ant checkstyle.

          Thanks,

          Cheolsoo

          Show
          jiraposter@reviews.apache.org added a comment - ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4641/ ----------------------------------------------------------- (Updated 2012-04-05 04:30:35.627666) Review request for Sqoop. Changes ------- Simplify tests. Summary ------- Sqoop import does not support BINARY_FLOAT and BINARY_DOUBLE for Oracle db. The patch includes: 1) Map BINARY_FLOAT and BINARY_DOUBLE to Java float and double for Oracle. 2) Add test cases to OracleCompatTest to verify the mapping. This addresses bug SQOOP-467 . https://issues.apache.org/jira/browse/SQOOP-467 Diffs (updated) /src/java/org/apache/sqoop/manager/OracleManager.java 1309536 /src/test/com/cloudera/sqoop/manager/OracleCompatTest.java 1309536 Diff: https://reviews.apache.org/r/4641/diff Testing ------- 1) Verified with Oracle XE 10g that new tests pass. 2) Ran ant test, ant test -Dthirdparty=true, and ant checkstyle. Thanks, Cheolsoo
          Hide
          jiraposter@reviews.apache.org added a comment -

          -----------------------------------------------------------
          This is an automatically generated e-mail. To reply, visit:
          https://reviews.apache.org/r/4641/
          -----------------------------------------------------------

          Review request for Sqoop.

          Summary
          -------

          Sqoop import does not support BINARY_FLOAT and BINARY_DOUBLE for Oracle db.

          The patch includes:
          1) Map BINARY_FLOAT and BINARY_DOUBLE to Java float and double for Oracle.
          2) Add test cases to OracleCompatTest to verify the mapping.

          This addresses bug SQOOP-467.
          https://issues.apache.org/jira/browse/SQOOP-467

          Diffs


          /src/java/org/apache/sqoop/manager/OracleManager.java 1309536
          /src/test/com/cloudera/sqoop/manager/OracleCompatTest.java 1309536

          Diff: https://reviews.apache.org/r/4641/diff

          Testing
          -------

          1) Verified with Oracle XE 10g that new tests pass.
          2) Ran ant test, ant test -Dthirdparty=true, and ant checkstyle.

          Thanks,

          Cheolsoo

          Show
          jiraposter@reviews.apache.org added a comment - ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/4641/ ----------------------------------------------------------- Review request for Sqoop. Summary ------- Sqoop import does not support BINARY_FLOAT and BINARY_DOUBLE for Oracle db. The patch includes: 1) Map BINARY_FLOAT and BINARY_DOUBLE to Java float and double for Oracle. 2) Add test cases to OracleCompatTest to verify the mapping. This addresses bug SQOOP-467 . https://issues.apache.org/jira/browse/SQOOP-467 Diffs /src/java/org/apache/sqoop/manager/OracleManager.java 1309536 /src/test/com/cloudera/sqoop/manager/OracleCompatTest.java 1309536 Diff: https://reviews.apache.org/r/4641/diff Testing ------- 1) Verified with Oracle XE 10g that new tests pass. 2) Ran ant test, ant test -Dthirdparty=true, and ant checkstyle. Thanks, Cheolsoo
          Cheolsoo Park made changes -
          Status Open [ 1 ] Patch Available [ 10002 ]
          Cheolsoo Park made changes -
          Field Original Value New Value
          Assignee Cheolsoo Park [ cheolsoo ]
          Hide
          Cheolsoo Park added a comment -

          Reading the Oracle SQL reference,

          BINARY_FLOAT is a 32-bit, single-precision floating-point number datatype. Each BINARY_FLOAT value requires 5 bytes, including a length byte.
          BINARY_DOUBLE is a 64-bit, double-precision floating-point number datatype. Each BINARY_DOUBLE value requires 9 bytes, including a length byte.

          I think that it makes sense to map BINARY_FLOAT and BINARY_BOUBLE to java float and java double respectively.

          Since these are Oracle-specific types, we can do the mapping in OracleManager just like we do with TIMESTAMPTZ and TIMESTAMPLTZ. Probably we can add the following code to the dbToJavaType() method of OracleManager:

          // check if it is BINARY_FLOAT
          dbType = getDatabaseType(typeClass, "BINARY_FLOAT");
          if (sqlType == dbType) {
            return "Float";
          }
          
          // check if it is BINARY_DOUBLE
          dbType = getDatabaseType(typeClass, "BINARY_DOUBLE");
          if (sqlType == dbType) {
            return "Double";
          }
          

          According to some tests that I quickly wrote up, this seems to work.

          Please let me know if anyone thinks otherwise.

          Show
          Cheolsoo Park added a comment - Reading the Oracle SQL reference, BINARY_FLOAT is a 32-bit, single-precision floating-point number datatype. Each BINARY_FLOAT value requires 5 bytes, including a length byte. BINARY_DOUBLE is a 64-bit, double-precision floating-point number datatype. Each BINARY_DOUBLE value requires 9 bytes, including a length byte. I think that it makes sense to map BINARY_FLOAT and BINARY_BOUBLE to java float and java double respectively. Since these are Oracle-specific types, we can do the mapping in OracleManager just like we do with TIMESTAMPTZ and TIMESTAMPLTZ. Probably we can add the following code to the dbToJavaType() method of OracleManager: // check if it is BINARY_FLOAT dbType = getDatabaseType(typeClass, "BINARY_FLOAT" ); if (sqlType == dbType) { return " Float " ; } // check if it is BINARY_DOUBLE dbType = getDatabaseType(typeClass, "BINARY_DOUBLE" ); if (sqlType == dbType) { return " Double " ; } According to some tests that I quickly wrote up, this seems to work. Please let me know if anyone thinks otherwise.
          Prashant Gokhale created issue -

            People

            • Assignee:
              Cheolsoo Park
              Reporter:
              Prashant Gokhale
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development