sqoop-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jar...@apache.org
Subject sqoop git commit: SQOOP-2983: OraOop export has degraded performance with wide tables
Date Wed, 26 Oct 2016 19:20:49 GMT
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 <jarcec@apache.org>
Authored: Wed Oct 26 12:20:17 2016 -0700
Committer: Jarek Jarcec Cecho <jarcec@apache.org>
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;");
+    }
+  }
+
 }


Mime
View raw message