Updated Branches: refs/heads/trunk 0a30ae174 -> 5eb987a78
SQOOP-467: Add support for Oracle BINARY_FLOAT and BINARY_DOUBLE data types (Cheolsoo Park via Jarek Jarcec Cecho) Project: http://git-wip-us.apache.org/repos/asf/sqoop/repo Commit: http://git-wip-us.apache.org/repos/asf/sqoop/commit/5eb987a7 Tree: http://git-wip-us.apache.org/repos/asf/sqoop/tree/5eb987a7 Diff: http://git-wip-us.apache.org/repos/asf/sqoop/diff/5eb987a7 Branch: refs/heads/trunk Commit: 5eb987a785d89272ac21d890ba89ce56e1446f48 Parents: 0a30ae1 Author: Jarek Jarcec Cecho <[email protected]> Authored: Fri Nov 2 12:16:17 2012 -0700 Committer: Jarek Jarcec Cecho <[email protected]> Committed: Fri Nov 2 12:16:17 2012 -0700 ---------------------------------------------------------------------- .../org/apache/sqoop/manager/MySQLManager.java | 27 +-- .../org/apache/sqoop/manager/OracleManager.java | 143 ++++++--------- .../cloudera/sqoop/manager/OracleCompatTest.java | 32 ++++ 3 files changed, 97 insertions(+), 105 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/sqoop/blob/5eb987a7/src/java/org/apache/sqoop/manager/MySQLManager.java ---------------------------------------------------------------------- diff --git a/src/java/org/apache/sqoop/manager/MySQLManager.java b/src/java/org/apache/sqoop/manager/MySQLManager.java index a3f586a..b79c926 100644 --- a/src/java/org/apache/sqoop/manager/MySQLManager.java +++ b/src/java/org/apache/sqoop/manager/MySQLManager.java @@ -275,8 +275,8 @@ public class MySQLManager private Map<String, String> colTypeNames; private static final int YEAR_TYPE_OVERWRITE = Types.SMALLINT; - @Override - public String toJavaType(String tableName, String columnName, int sqlType) { + private int overrideSqlType(String tableName, String columnName, + int sqlType) { if (colTypeNames == null) { colTypeNames = getColumnTypeNames(tableName, options.getSqlQuery()); } @@ -284,33 +284,24 @@ public class MySQLManager if ("YEAR".equalsIgnoreCase(colTypeNames.get(columnName))) { sqlType = YEAR_TYPE_OVERWRITE; } + return sqlType; + } + @Override + public String toJavaType(String tableName, String columnName, int sqlType) { + sqlType = overrideSqlType(tableName, columnName, sqlType); return super.toJavaType(tableName, columnName, sqlType); } @Override public String toHiveType(String tableName, String columnName, int sqlType) { - if (colTypeNames == null) { - colTypeNames = getColumnTypeNames(tableName, options.getSqlQuery()); - } - - if ("YEAR".equalsIgnoreCase(colTypeNames.get(columnName))) { - sqlType = YEAR_TYPE_OVERWRITE; - } - + sqlType = overrideSqlType(tableName, columnName, sqlType); return super.toHiveType(tableName, columnName, sqlType); } @Override public Type toAvroType(String tableName, String columnName, int sqlType) { - if (colTypeNames == null) { - colTypeNames = getColumnTypeNames(tableName, options.getSqlQuery()); - } - - if ("YEAR".equalsIgnoreCase(colTypeNames.get(columnName))) { - sqlType = YEAR_TYPE_OVERWRITE; - } - + sqlType = overrideSqlType(tableName, columnName, sqlType); return super.toAvroType(tableName, columnName, sqlType); } } http://git-wip-us.apache.org/repos/asf/sqoop/blob/5eb987a7/src/java/org/apache/sqoop/manager/OracleManager.java ---------------------------------------------------------------------- diff --git a/src/java/org/apache/sqoop/manager/OracleManager.java b/src/java/org/apache/sqoop/manager/OracleManager.java index 6974e1b..22a057a 100644 --- a/src/java/org/apache/sqoop/manager/OracleManager.java +++ b/src/java/org/apache/sqoop/manager/OracleManager.java @@ -469,121 +469,90 @@ public class OracleManager return execute(sqlCmd); } + private Map<String, String> columnTypeNames; + /** * Resolve a database-specific type to the Java type that should contain it. * @param tableName table name - * @param columnName column name - * @param sqlType sql data type + * @param colName column name * @return the name of a Java type to hold the sql datatype, or null if none. */ - @Override - public String toJavaType(String tableName, String columnName, int sqlType) { - String defaultJavaType = super.toJavaType(tableName, columnName, sqlType); - return (defaultJavaType == null) ? dbToJavaType(sqlType) : defaultJavaType; - } - - /** - * Attempt to map sql type to java type. - * @param sqlType sql type - * @return java type - */ - private String dbToJavaType(int sqlType) { - // load class oracle.jdbc.OracleTypes - // need to use reflection because oracle specific libraries - // are not accessible in this context - Class typeClass = getTypeClass("oracle.jdbc.OracleTypes"); - - // check if it is TIMESTAMPTZ - int dbType = getDatabaseType(typeClass, "TIMESTAMPTZ"); - if (sqlType == dbType) { - return "java.sql.Timestamp"; + private String toDbSpecificJavaType(String tableName, String colName) { + if (columnTypeNames == null) { + columnTypeNames = getColumnTypeNames(tableName, options.getSqlQuery()); } - // check if it is TIMESTAMPLTZ - dbType = getDatabaseType(typeClass, "TIMESTAMPLTZ"); - if (sqlType == dbType) { - return "java.sql.Timestamp"; + String colTypeName = columnTypeNames.get(colName); + if (colTypeName != null) { + if (colTypeName.equalsIgnoreCase("BINARY_FLOAT")) { + return "Float"; + } + if (colTypeName.equalsIgnoreCase("BINARY_DOUBLE")) { + return "Double"; + } + if (colTypeName.toUpperCase().startsWith("TIMESTAMP")) { + return "java.sql.Timestamp"; + } } - - // return null if no java type was found for sqlType return null; } /** - * Attempt to map sql type to hive type. + * Resolve a database-specific type to the Hive type that should contain it. * @param tableName table name - * @param columnName column name - * @param sqlType sql data type - * @return hive data type - */ - public String toHiveType(String tableName, String columnName, int sqlType) { - String defaultHiveType = super.toHiveType(tableName, columnName, sqlType); - return (defaultHiveType == null) ? dbToHiveType(sqlType) : defaultHiveType; - } - - /** - * Resolve a database-specific type to Hive type. - * @param sqlType sql type - * @return hive type + * @param colName column name + * @return the name of a Hive type to hold the sql datatype, or null if none. */ - private String dbToHiveType(int sqlType) { - // load class oracle.jdbc.OracleTypes - // need to use reflection because oracle specific libraries - // are not accessible in this context - Class typeClass = getTypeClass("oracle.jdbc.OracleTypes"); - - // check if it is TIMESTAMPTZ - int dbType = getDatabaseType(typeClass, "TIMESTAMPTZ"); - if (sqlType == dbType) { - return "STRING"; + private String toDbSpecificHiveType(String tableName, String colName) { + if (columnTypeNames == null) { + columnTypeNames = getColumnTypeNames(tableName, options.getSqlQuery()); } - // check if it is TIMESTAMPLTZ - dbType = getDatabaseType(typeClass, "TIMESTAMPLTZ"); - if (sqlType == dbType) { - return "STRING"; + String colTypeName = columnTypeNames.get(colName); + if (colTypeName != null) { + if (colTypeName.equalsIgnoreCase("BINARY_FLOAT")) { + return "FLOAT"; + } + if (colTypeName.equalsIgnoreCase("BINARY_DOUBLE")) { + return "DOUBLE"; + } + if (colTypeName.toUpperCase().startsWith("TIMESTAMP")) { + return "STRING"; + } } - - // return null if no hive type was found for sqlType return null; } /** - * Get database type. - * @param clazz oracle class representing sql types - * @param fieldName field name - * @return value of database type constant + * Return java type for SQL type. + * @param tableName table name + * @param columnName column name + * @param sqlType sql type + * @return java type */ - private int getDatabaseType(Class clazz, String fieldName) { - // Need to use reflection to extract constant values because the database - // specific java libraries are not accessible in this context. - int value = -1; - try { - java.lang.reflect.Field field = clazz.getDeclaredField(fieldName); - value = field.getInt(null); - } catch (NoSuchFieldException ex) { - LOG.error("Could not retrieve value for field " + fieldName, ex); - } catch (IllegalAccessException ex) { - LOG.error("Could not retrieve value for field " + fieldName, ex); + @Override + public String toJavaType(String tableName, String columnName, int sqlType) { + String javaType = super.toJavaType(tableName, columnName, sqlType); + if (javaType == null) { + javaType = toDbSpecificJavaType(tableName, columnName); } - return value; + return javaType; } /** - * Load class by name. - * @param className class name - * @return class instance + * Return hive type for SQL type. + * @param tableName table name + * @param columnName column name + * @param sqlType sql data type + * @return hive type */ - private Class getTypeClass(String className) { - // Need to use reflection to load class because the database specific java - // libraries are not accessible in this context. - Class typeClass = null; - try { - typeClass = Class.forName(className); - } catch (ClassNotFoundException ex) { - LOG.error("Could not load class " + className, ex); + @Override + public String toHiveType(String tableName, String columnName, int sqlType) { + String hiveType = super.toHiveType(tableName, columnName, sqlType); + if (hiveType == null) { + hiveType = toDbSpecificHiveType(tableName, columnName); } - return typeClass; + return hiveType; } @Override http://git-wip-us.apache.org/repos/asf/sqoop/blob/5eb987a7/src/test/com/cloudera/sqoop/manager/OracleCompatTest.java ---------------------------------------------------------------------- diff --git a/src/test/com/cloudera/sqoop/manager/OracleCompatTest.java b/src/test/com/cloudera/sqoop/manager/OracleCompatTest.java index 309a960..5bf920a 100644 --- a/src/test/com/cloudera/sqoop/manager/OracleCompatTest.java +++ b/src/test/com/cloudera/sqoop/manager/OracleCompatTest.java @@ -184,6 +184,14 @@ public class OracleCompatTest extends ManagerCompatTestCase { return sb.toString(); } + protected String getBinaryFloatInsertStr(float f) { + return "TO_BINARY_FLOAT('" + f + "')"; + } + + protected String getBinaryDoubleInsertStr(double d) { + return "TO_BINARY_DOUBLE('" + d + "')"; + } + // Disable this test since Oracle isn't ANSI compliant. @Override public void testEmptyStringCol() { @@ -221,5 +229,29 @@ public class OracleCompatTest extends ManagerCompatTestCase { public void testRawVal() { verifyType("RAW(8)", "'12ABCD'", getVarBinarySeqOutput("12ABCD"), true); } + + public void testBinaryFloat() { + verifyType("BINARY_FLOAT", getBinaryFloatInsertStr(25f), "25.0"); + verifyType("BINARY_FLOAT", getBinaryFloatInsertStr(+6.34f), "6.34"); + + // Max and min are from Oracle DB SQL reference for 10g release 2 + float max = 3.40282E+38F; + verifyType("BINARY_FLOAT", getBinaryFloatInsertStr(max), "3.40282E38"); + float min = 1.17549E-38F; + verifyType("BINARY_FLOAT", getBinaryFloatInsertStr(min), "1.17549E-38"); + } + + public void testBinaryDouble() { + verifyType("BINARY_DOUBLE", getBinaryDoubleInsertStr(0.5d), "0.5"); + verifyType("BINARY_DOUBLE", getBinaryDoubleInsertStr(-1d), "-1.0"); + + // Max and min are from Oracle DB SQL reference for 10g release 2 + double max = 1.79769313486231E+308; + verifyType("BINARY_DOUBLE", getBinaryDoubleInsertStr(max), + "1.79769313486231E308"); + double min = 2.22507485850720E-308; + verifyType("BINARY_DOUBLE", getBinaryDoubleInsertStr(min), + "2.2250748585072E-308"); + } }
