Repository: sqoop Updated Branches: refs/heads/trunk 14754342d -> 3fc4ff714
SQOOP-2983: OraOop export has degraded performance with wide tables (Attila Szabo 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/3fc4ff71 Tree: http://git-wip-us.apache.org/repos/asf/sqoop/tree/3fc4ff71 Diff: http://git-wip-us.apache.org/repos/asf/sqoop/diff/3fc4ff71 Branch: refs/heads/trunk Commit: 3fc4ff714427df4cc0da7cd9fdb451703f8686c1 Parents: 1475434 Author: Jarek Jarcec Cecho <[email protected]> Authored: Wed Oct 26 12:20:17 2016 -0700 Committer: Jarek Jarcec Cecho <[email protected]> Committed: Wed Oct 26 12:20:17 2016 -0700 ---------------------------------------------------------------------- .../manager/oracle/OraOopOracleQueries.java | 2 +- .../oracle/OraOopOutputFormatInsert.java | 13 +- src/java/org/apache/sqoop/orm/ClassWriter.java | 8 +- .../apache/sqoop/manager/oracle/ExportTest.java | 4 +- .../sqoop/manager/oracle/OraOopTestCase.java | 60 ++++++-- .../sqoop/manager/oracle/OraOopTypesTest.java | 141 +++++++++++++++++++ .../sqoop/manager/oracle/util/OracleData.java | 17 +++ 7 files changed, 223 insertions(+), 22 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/sqoop/blob/3fc4ff71/src/java/org/apache/sqoop/manager/oracle/OraOopOracleQueries.java ---------------------------------------------------------------------- diff --git a/src/java/org/apache/sqoop/manager/oracle/OraOopOracleQueries.java b/src/java/org/apache/sqoop/manager/oracle/OraOopOracleQueries.java index 6b27bd8..2603f83 100644 --- a/src/java/org/apache/sqoop/manager/oracle/OraOopOracleQueries.java +++ b/src/java/org/apache/sqoop/manager/oracle/OraOopOracleQueries.java @@ -969,7 +969,7 @@ public final class OraOopOracleQueries { Statement statement = connection.createStatement(); String sql = - String.format("ALTER TABLE %s EXCHANGE SUBPARTITION %s WITH TABLE %s", + String.format("ALTER TABLE %s EXCHANGE SUBPARTITION %s WITH TABLE %s WITHOUT VALIDATION", table.toString(), subPartitionName, subPartitionTable.toString()); statement.execute(sql); statement.close(); http://git-wip-us.apache.org/repos/asf/sqoop/blob/3fc4ff71/src/java/org/apache/sqoop/manager/oracle/OraOopOutputFormatInsert.java ---------------------------------------------------------------------- diff --git a/src/java/org/apache/sqoop/manager/oracle/OraOopOutputFormatInsert.java b/src/java/org/apache/sqoop/manager/oracle/OraOopOutputFormatInsert.java index d5eebf4..d430ef7 100644 --- a/src/java/org/apache/sqoop/manager/oracle/OraOopOutputFormatInsert.java +++ b/src/java/org/apache/sqoop/manager/oracle/OraOopOutputFormatInsert.java @@ -238,14 +238,15 @@ public class OraOopOutputFormatInsert<K extends SqoopRecord, V> extends void configurePreparedStatement(PreparedStatement statement, List<SqoopRecord> userRecords) throws SQLException { - Map<String, Object> fieldMap; try { - for (SqoopRecord record : userRecords) { - fieldMap = record.getFieldMap(); - - configurePreparedStatementColumns(statement, fieldMap); + for (SqoopRecord sqoopRecord : userRecords) { + int fieldCount = sqoopRecord.write(statement, 0); + if (this.tableHasMapperRowNumberColumn) { + statement.setLong(fieldCount + 1, this.mapperRowNumber); + this.mapperRowNumber++; + } + statement.addBatch(); } - } catch (Exception ex) { if (ex instanceof SQLException) { throw (SQLException) ex; http://git-wip-us.apache.org/repos/asf/sqoop/blob/3fc4ff71/src/java/org/apache/sqoop/orm/ClassWriter.java ---------------------------------------------------------------------- diff --git a/src/java/org/apache/sqoop/orm/ClassWriter.java b/src/java/org/apache/sqoop/orm/ClassWriter.java index 2f14b6e..e289429 100644 --- a/src/java/org/apache/sqoop/orm/ClassWriter.java +++ b/src/java/org/apache/sqoop/orm/ClassWriter.java @@ -896,8 +896,12 @@ public class ClassWriter { continue; } - sb.append(" JdbcWritableBridge." + setterMethod + "(" + col + ", " - + (i + 1) + " + __off, " + sqlType + ", __dbStmt);\n"); + if ("writeString".equals(setterMethod) && sqlType == 2002) { + sb.append("__dbStmt.setString(" + (i + 1) + ", " + col + ");\n"); + } else { + sb.append(" JdbcWritableBridge." + setterMethod + "(" + col + ", " + (i + 1) + " + __off, " + sqlType + + ", __dbStmt);\n"); + } } if (wrapInMethod) { http://git-wip-us.apache.org/repos/asf/sqoop/blob/3fc4ff71/src/test/org/apache/sqoop/manager/oracle/ExportTest.java ---------------------------------------------------------------------- diff --git a/src/test/org/apache/sqoop/manager/oracle/ExportTest.java b/src/test/org/apache/sqoop/manager/oracle/ExportTest.java index 991b221..3752886 100644 --- a/src/test/org/apache/sqoop/manager/oracle/ExportTest.java +++ b/src/test/org/apache/sqoop/manager/oracle/ExportTest.java @@ -47,7 +47,7 @@ public class ExportTest extends OraOopTestCase { @Test public void testProductExport() throws Exception { int retCode = - TEST_CASE.runExportFromTemplateTable("TST_PRODUCT", "TST_PRODUCT_EXP"); + TEST_CASE.runExportFromTemplateTable("TST_PRODUCT", "TST_PRODUCT_EXP", false); Assert.assertEquals("Return code should be 0", 0, retCode); } @@ -55,7 +55,7 @@ public class ExportTest extends OraOopTestCase { public void testProductExportMixedCaseTableName() throws Exception { int retCode = TEST_CASE.runExportFromTemplateTable("TST_PRODUCT", - "\"\"T5+_Pr#duct_Exp\"\""); + "\"\"T5+_Pr#duct_Exp\"\"", false); Assert.assertEquals("Return code should be 0", 0, retCode); } http://git-wip-us.apache.org/repos/asf/sqoop/blob/3fc4ff71/src/test/org/apache/sqoop/manager/oracle/OraOopTestCase.java ---------------------------------------------------------------------- diff --git a/src/test/org/apache/sqoop/manager/oracle/OraOopTestCase.java b/src/test/org/apache/sqoop/manager/oracle/OraOopTestCase.java index 9fe4821..3811e38 100644 --- a/src/test/org/apache/sqoop/manager/oracle/OraOopTestCase.java +++ b/src/test/org/apache/sqoop/manager/oracle/OraOopTestCase.java @@ -24,25 +24,29 @@ import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Statement; import java.util.ArrayList; +import java.util.Collections; +import java.util.HashMap; import java.util.List; +import java.util.Map; +import java.util.Map.Entry; import java.util.regex.Matcher; import java.util.regex.Pattern; -import junit.framework.Assert; - import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.fs.Path; import org.apache.log4j.Layout; import org.apache.log4j.Logger; import org.apache.log4j.PatternLayout; import org.apache.log4j.WriterAppender; +import org.apache.sqoop.manager.oracle.util.HadoopFiles; +import org.apache.sqoop.manager.oracle.util.OracleData; import com.cloudera.sqoop.Sqoop; import com.cloudera.sqoop.manager.OracleUtils; -import org.apache.sqoop.manager.oracle.util.HadoopFiles; -import org.apache.sqoop.manager.oracle.util.OracleData; +import junit.framework.Assert; /** * Base test case for OraOop to handle common functions. @@ -95,6 +99,10 @@ public abstract class OraOopTestCase { return sqoopGenClassName; } + protected void setSqoopGenClassName(String sqoopGenClassName) { + this.sqoopGenClassName = sqoopGenClassName; + } + protected Connection getTestEnvConnection() throws SQLException { if (this.conn == null) { this.conn = @@ -116,7 +124,7 @@ public abstract class OraOopTestCase { this.conn = null; } - protected void createTable(String fileName) { + protected void createTable(String fileName, boolean dropTableIfExists) { try { Connection localConn = getTestEnvConnection(); int parallelProcesses = OracleData.getParallelProcesses(localConn); @@ -125,7 +133,7 @@ public abstract class OraOopTestCase { try { long startTime = System.currentTimeMillis(); OracleData.createTable(localConn, fileName, parallelProcesses, - rowsPerSlave); + rowsPerSlave, dropTableIfExists); LOG.debug("Created and loaded table in " + ((System.currentTimeMillis() - startTime) / 1000) + " seconds."); } catch (SQLException e) { @@ -140,6 +148,19 @@ public abstract class OraOopTestCase { } } + protected void createTable(String fileName) { + createTable(fileName, false); + } + + protected void createTableFromSQL(String sql, String tableName) throws SQLException { + Connection conn = getTestEnvConnection(); + try (Statement stmt = conn.createStatement()) { + stmt.execute("BEGIN EXECUTE IMMEDIATE 'DROP TABLE " + tableName + + "'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;"); + stmt.execute(sql); + } + } + protected int countTable(String inputTableName, List<String> partitionList) { String tableName = org.apache.sqoop.manager.oracle.OracleUtils.escapeIdentifier(inputTableName); if (tableName.startsWith("\"\"") && tableName.endsWith("\"\"")) { @@ -254,7 +275,17 @@ public abstract class OraOopTestCase { } protected int runExportFromTemplateTable(String templateTableName, - String tableName) { + String tableName, boolean isPartitoned) { + Map<String, String> stringConfigEntries = new HashMap<String, String>(); + stringConfigEntries.put("oraoop.template.table", templateTableName); + Map<String, Boolean> booleanConfigEntries = new HashMap<String, Boolean>(); + booleanConfigEntries.put("oraoop.partitioned", isPartitoned); + + return runExport(tableName, new ArrayList<String>(), stringConfigEntries, booleanConfigEntries); + } + + protected int runExport(String tableName, List<String> additionalArgs, Map<String, String> stringConfigEntries, + Map<String, Boolean> booleanConfigEntries) { List<String> sqoopArgs = new ArrayList<String>(); sqoopArgs.add("export"); @@ -285,15 +316,22 @@ public abstract class OraOopTestCase { sqoopArgs.add("--outdir"); sqoopArgs.add(this.sqoopGenSrcDirectory); + sqoopArgs.addAll(additionalArgs); + Configuration sqoopConf = getSqoopConf(); - sqoopConf.set("oraoop.template.table", templateTableName); sqoopConf.setBoolean("oraoop.drop.table", true); sqoopConf.setBoolean("oraoop.nologging", true); - sqoopConf.setBoolean("oraoop.partitioned", false); - return Sqoop.runTool(sqoopArgs.toArray(new String[sqoopArgs.size()]), - sqoopConf); + for (Entry<String, String> entry : stringConfigEntries.entrySet()) { + sqoopConf.set(entry.getKey(), entry.getValue()); + } + + for (Entry<String, Boolean> entry : booleanConfigEntries.entrySet()) { + sqoopConf.setBoolean(entry.getKey(), entry.getValue()); + } + + return Sqoop.runTool(sqoopArgs.toArray(new String[sqoopArgs.size()]), sqoopConf); } protected int runCompareTables(Connection connection, String table1, http://git-wip-us.apache.org/repos/asf/sqoop/blob/3fc4ff71/src/test/org/apache/sqoop/manager/oracle/OraOopTypesTest.java ---------------------------------------------------------------------- diff --git a/src/test/org/apache/sqoop/manager/oracle/OraOopTypesTest.java b/src/test/org/apache/sqoop/manager/oracle/OraOopTypesTest.java new file mode 100644 index 0000000..ad168e2 --- /dev/null +++ b/src/test/org/apache/sqoop/manager/oracle/OraOopTypesTest.java @@ -0,0 +1,141 @@ +package org.apache.sqoop.manager.oracle; + +import static org.junit.Assert.assertEquals; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.Statement; + +import org.junit.Test; + +public class OraOopTypesTest extends OraOopTestCase { + @Test + public void ensureTypesAfterExportMappedAsExpected() throws Exception { + try { + setSqoopTargetDirectory(getSqoopTargetDirectory() + "types_test"); + String tempTableName = "ORACLE_DATATYPES_TEMPLATE"; + String tableName = "ORACLE_DATATYPES"; + createTableFromSQL("create table " + tempTableName + " (" + + "C1_NUM NUMBER(*,0)," + + "C2_NUM NUMBER(*,5)," + + "C3_NUM NUMBER(16,8)," + + "C4_NUM NUMBER(9,-3)," + + "C5_FLOAT BINARY_FLOAT," + + "C6_DOUBLE BINARY_DOUBLE," + + "C7_DATE DATE," + + "C8_TIMESTAMP TIMESTAMP," + + "C9_TIMESTAMP_WITH_TZ TIMESTAMP WITH TIME ZONE," + + "C10_TIMESTAMP_WITH_LTZ TIMESTAMP WITH LOCAL TIME ZONE," + + "C11_CHAR CHAR(255)," + + "C12_VARCHAR VARCHAR(255)," + + "C13_VARCHAR2 VARCHAR2(255)," + + "C14_NCHAR NCHAR(255)," + + "C15_NVARCHAR2 NVARCHAR2(255)," + + "C16_URITYPE UriType" + + ")", tempTableName); + + Connection conn = getTestEnvConnection(); + try (Statement stmt = conn.createStatement()) { + stmt.execute("insert into " + tempTableName + " values (" + + "123456789101112.123456789101112," + + "123456789101112.123456789101112," + + "12345678.12345678," + + "123456789101.123456789101112," + + "123456789101112.123456789101112," + + "123456789101112.123456789101112," + + "DATE '2015-02-23'," + + "TIMESTAMP '2015-02-23 13:42:24.123456 -07:00'," + + "TIMESTAMP '2015-02-23 13:42:24.123456 -08:00'," + + "TIMESTAMP '2015-02-23 13:42:24.123456 -09:00'," + + "'ÃRÃZTÅ°RÅTÃKÃRFÃRÃGÃP'," + + "'ÃRÃZTÅ°RÅTÃKÃRFÃRÃGÃP'," + + "'ÃRÃZTÅ°RÅTÃKÃRFÃRÃGÃP'," + + "'ÃRÃZTÅ°RÅTÃKÃRFÃRÃGÃP'," + + "'ÃRÃZTÅ°RÅTÃKÃRFÃRÃGÃP'," + + "httpuritype.createuri('http://www.oracle.com'))"); + } + conn.commit(); + runImport(tempTableName, getSqoopConf(), false); + runExportFromTemplateTable(tempTableName, tableName, true); + try (Statement stmt = conn.createStatement()) { + ResultSet rs = stmt.executeQuery( + "select count(*) from (" + + "select * from (select " + + "T1.C1_NUM, " + + "T1.C2_NUM, " + + "T1.C3_NUM, " + + "T1.C4_NUM, " + + "T1.C5_FLOAT, " + + "T1.C6_DOUBLE, " + + "T1.C7_DATE, " + + "T1.C8_TIMESTAMP, " + + "T1.C9_TIMESTAMP_WITH_TZ, " + + "T1.C10_TIMESTAMP_WITH_LTZ, " + + "T1.C11_CHAR, " + + "T1.C12_VARCHAR, " + + "T1.C13_VARCHAR2, " + + "T1.C14_NCHAR, " + + "T1.C15_NVARCHAR2, " + + "T1.C16_URITYPE.GETURL() from " + + tempTableName + + " T1 " + + "minus select " + + "T2.C1_NUM, " + + "T2.C2_NUM, " + + "T2.C3_NUM, " + + "T2.C4_NUM, " + + "T2.C5_FLOAT, " + + "T2.C6_DOUBLE, " + + "T2.C7_DATE, " + + "T2.C8_TIMESTAMP, " + + "T2.C9_TIMESTAMP_WITH_TZ, " + + "T2.C10_TIMESTAMP_WITH_LTZ, " + + "T2.C11_CHAR, " + + "T2.C12_VARCHAR, " + + "T2.C13_VARCHAR2, " + + "T2.C14_NCHAR, " + + "T2.C15_NVARCHAR2, " + + "T2.C16_URITYPE.GETURL() from "+tableName+" T2) " + + "union all select * from (select " + + "T1.C1_NUM, " + + "T1.C2_NUM, " + + "T1.C3_NUM, " + + "T1.C4_NUM, " + + "T1.C5_FLOAT, " + + "T1.C6_DOUBLE, " + + "T1.C7_DATE, " + + "T1.C8_TIMESTAMP, " + + "T1.C9_TIMESTAMP_WITH_TZ, " + + "T1.C10_TIMESTAMP_WITH_LTZ, " + + "T1.C11_CHAR, " + + "T1.C12_VARCHAR, " + + "T1.C13_VARCHAR2, " + + "T1.C14_NCHAR, " + + "T1.C15_NVARCHAR2, " + + "T1.C16_URITYPE.GETURL() from "+tableName+" T1 " + + "minus select " + + "T2.C1_NUM, " + + "T2.C2_NUM, " + + "T2.C3_NUM, " + + "T2.C4_NUM, " + + "T2.C5_FLOAT, " + + "T2.C6_DOUBLE, " + + "T2.C7_DATE, " + + "T2.C8_TIMESTAMP, " + + "T2.C9_TIMESTAMP_WITH_TZ, " + + "T2.C10_TIMESTAMP_WITH_LTZ, " + + "T2.C11_CHAR, " + + "T2.C12_VARCHAR, " + + "T2.C13_VARCHAR2, " + + "T2.C14_NCHAR, " + + "T2.C15_NVARCHAR2, " + + "T2.C16_URITYPE.GETURL() from "+tempTableName+" T2))"); + rs.next(); + assertEquals(0, rs.getInt(1)); + } + } finally { + cleanupFolders(); + closeTestEnvConnection(); + } + } +} http://git-wip-us.apache.org/repos/asf/sqoop/blob/3fc4ff71/src/test/org/apache/sqoop/manager/oracle/util/OracleData.java ---------------------------------------------------------------------- diff --git a/src/test/org/apache/sqoop/manager/oracle/util/OracleData.java b/src/test/org/apache/sqoop/manager/oracle/util/OracleData.java index 8846f65..355b1e2 100644 --- a/src/test/org/apache/sqoop/manager/oracle/util/OracleData.java +++ b/src/test/org/apache/sqoop/manager/oracle/util/OracleData.java @@ -23,6 +23,7 @@ import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; +import java.sql.Statement; import java.util.List; import org.apache.commons.io.IOUtils; @@ -220,9 +221,25 @@ public final class OracleData { public static void createTable(Connection conn, String fileName, int parallelDegree, int rowsPerSlave) throws Exception { + createTable(conn, fileName, parallelDegree, rowsPerSlave, false); + } + + public static void createTable(Connection conn, String fileName, + int parallelDegree, int rowsPerSlave, + boolean dropTableIfExists) throws Exception { URL file = classLoader.getResource("oraoop/" + fileName); OracleTableDefinition tableDefinition = new OracleTableDefinition(file); + if (dropTableIfExists) { + dropTableIfExists(conn, tableDefinition.getTableName()); + } createTable(conn, tableDefinition, parallelDegree, rowsPerSlave); } + private static void dropTableIfExists(Connection conn, String tableName) throws Exception { + try (Statement stmt = conn.createStatement()) { + stmt.execute("BEGIN EXECUTE IMMEDIATE 'DROP TABLE " + tableName + + "'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;"); + } + } + }
