Author: blee
Date: Fri Jun 1 17:34:27 2012
New Revision: 1345282
URL: http://svn.apache.org/viewvc?rev=1345282&view=rev
Log:
SQOOP-476 If table name is a qualified name, Sqoop import fails in DB2
Added:
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DBRecordReader.java
(with props)
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBInputFormat.java
(with props)
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBRecordReader.java
(with props)
Modified:
sqoop/trunk/src/java/org/apache/sqoop/manager/Db2Manager.java
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/DBInputFormat.java
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/DataDrivenDBRecordReader.java
sqoop/trunk/src/test/com/cloudera/sqoop/manager/DB2ManagerImportManualTest.java
Modified: sqoop/trunk/src/java/org/apache/sqoop/manager/Db2Manager.java
URL:
http://svn.apache.org/viewvc/sqoop/trunk/src/java/org/apache/sqoop/manager/Db2Manager.java?rev=1345282&r1=1345281&r2=1345282&view=diff
==============================================================================
--- sqoop/trunk/src/java/org/apache/sqoop/manager/Db2Manager.java (original)
+++ sqoop/trunk/src/java/org/apache/sqoop/manager/Db2Manager.java Fri Jun 1
17:34:27 2012
@@ -26,11 +26,13 @@ import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
+import org.apache.sqoop.mapreduce.db.Db2DataDrivenDBInputFormat;
import com.cloudera.sqoop.SqoopOptions;
import com.cloudera.sqoop.mapreduce.ExportBatchOutputFormat;
import com.cloudera.sqoop.mapreduce.JdbcExportJob;
import com.cloudera.sqoop.util.ExportException;
+import com.cloudera.sqoop.util.ImportException;
/**
* Manages connections to DB2 databases. Requires the DB2 JDBC driver.
@@ -50,6 +52,19 @@ public class Db2Manager
}
/**
+ * Perform an import of a table from the database into HDFS.
+ */
+ @Override
+ public void importTable(
+ com.cloudera.sqoop.manager.ImportJobContext context)
+ throws IOException, ImportException {
+ context.setConnManager(this);
+ // Specify the DB2-specific DBInputFormat for import.
+ context.setInputFormat(Db2DataDrivenDBInputFormat.class);
+ super.importTable(context);
+ }
+
+ /**
* Export data stored in HDFS into a table in a database.
*/
@Override
Modified: sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/DBInputFormat.java
URL:
http://svn.apache.org/viewvc/sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/DBInputFormat.java?rev=1345282&r1=1345281&r2=1345282&view=diff
==============================================================================
--- sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/DBInputFormat.java
(original)
+++ sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/DBInputFormat.java Fri
Jun 1 17:34:27 2012
@@ -212,6 +212,11 @@ extends InputFormat<LongWritable, T> imp
return new OracleDBRecordReader<T>(split, inputClass,
conf, getConnection(), getDBConf(), conditions, fieldNames,
tableName);
+ } else if (dbProductName.startsWith("DB2")) {
+ // use DB2-specific db reader.
+ return new Db2DBRecordReader<T>(split, inputClass,
+ conf, getConnection(), getDBConf(), conditions, fieldNames,
+ tableName);
} else {
// Generic reader.
return new DBRecordReader<T>(split, inputClass,
Modified:
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/DataDrivenDBRecordReader.java
URL:
http://svn.apache.org/viewvc/sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/DataDrivenDBRecordReader.java?rev=1345282&r1=1345281&r2=1345282&view=diff
==============================================================================
---
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/DataDrivenDBRecordReader.java
(original)
+++
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/DataDrivenDBRecordReader.java
Fri Jun 1 17:34:27 2012
@@ -97,9 +97,11 @@ public class DataDrivenDBRecordReader<T
}
query.append(" FROM ").append(tableName);
- if (!dbProductName.startsWith("ORACLE")) {
- // Seems to be necessary for hsqldb? Oracle explicitly does *not*
- // use this clause.
+ if (!dbProductName.startsWith("ORACLE")
+ && !dbProductName.startsWith("DB2")) {
+ // The AS clause is required for hsqldb, but Oracle explicitly does
+ // not use it, and DB2 does not allow a qualified name in alias. Since
+ // this is not necessary for Oracle and DB2, we do not append.
query.append(" AS ").append(tableName);
}
query.append(" WHERE ");
Added: sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DBRecordReader.java
URL:
http://svn.apache.org/viewvc/sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DBRecordReader.java?rev=1345282&view=auto
==============================================================================
--- sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DBRecordReader.java
(added)
+++ sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DBRecordReader.java
Fri Jun 1 17:34:27 2012
@@ -0,0 +1,63 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.sqoop.mapreduce.db;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.sqoop.mapreduce.DBWritable;
+
+import com.cloudera.sqoop.mapreduce.db.DBConfiguration;
+import com.cloudera.sqoop.mapreduce.db.DBInputFormat;
+import com.cloudera.sqoop.mapreduce.db.DBRecordReader;
+
+/**
+ * A RecordReader that reads records from DB2.
+ */
+public class Db2DBRecordReader<T extends DBWritable>
+extends DBRecordReader<T> {
+
+ private static final Log LOG = LogFactory.getLog(Db2DBRecordReader.class);
+
+ // CHECKSTYLE:OFF
+ public Db2DBRecordReader(DBInputFormat.DBInputSplit split,
+ Class<T> inputClass, Configuration conf, Connection conn,
+ DBConfiguration dbConfig, String cond, String [] fields,
+ String table) throws SQLException {
+ super(split, inputClass, conf, conn, dbConfig, cond, fields, table);
+ }
+ // CHECKSTYLE:ON
+
+ /** Returns the query for selecting the records from DB2. */
+ protected String getSelectQuery() {
+ String query = super.getSelectQuery();
+ if (getDBConf().getInputQuery() == null) {
+ // If there is no user-defined query, we construct a default select query
+ // as follows:
+ // SELECT <columns> FROM <table name> AS <table name>
+ // However, in DB2 'AS <table name>' can cause a syntax error if table
+ // name is a qualified name. Since the AS clause is not necessary, we
+ // remove it.
+ query = query.replace(" AS " + getTableName(), "");
+ }
+ return query;
+ }
+}
Propchange:
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DBRecordReader.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange:
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DBRecordReader.java
------------------------------------------------------------------------------
svn:mime-type = text/plain
Added:
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBInputFormat.java
URL:
http://svn.apache.org/viewvc/sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBInputFormat.java?rev=1345282&view=auto
==============================================================================
---
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBInputFormat.java
(added)
+++
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBInputFormat.java
Fri Jun 1 17:34:27 2012
@@ -0,0 +1,55 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.sqoop.mapreduce.db;
+
+import java.io.IOException;
+import java.sql.SQLException;
+
+import org.apache.hadoop.conf.Configurable;
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.io.LongWritable;
+import org.apache.hadoop.mapreduce.RecordReader;
+import org.apache.sqoop.mapreduce.DBWritable;
+
+import com.cloudera.sqoop.mapreduce.db.DBConfiguration;
+import com.cloudera.sqoop.mapreduce.db.DataDrivenDBInputFormat;
+
+/**
+ * A InputFormat that reads input data from DB2.
+ */
+public class Db2DataDrivenDBInputFormat<T extends DBWritable>
+ extends DataDrivenDBInputFormat<T> implements Configurable {
+
+ @Override
+ protected RecordReader<LongWritable, T> createDBRecordReader(
+ DBInputSplit split, Configuration conf) throws IOException {
+
+ DBConfiguration dbConf = getDBConf();
+ @SuppressWarnings("unchecked")
+ Class<T> inputClass = (Class<T>) (dbConf.getInputClass());
+
+ try {
+ // Use DB2-specific db reader
+ return new Db2DataDrivenDBRecordReader<T>(split, inputClass,
+ conf, getConnection(), dbConf, dbConf.getInputConditions(),
+ dbConf.getInputFieldNames(), dbConf.getInputTableName());
+ } catch (SQLException ex) {
+ throw new IOException(ex);
+ }
+ }
+}
Propchange:
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBInputFormat.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange:
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBInputFormat.java
------------------------------------------------------------------------------
svn:mime-type = text/plain
Added:
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBRecordReader.java
URL:
http://svn.apache.org/viewvc/sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBRecordReader.java?rev=1345282&view=auto
==============================================================================
---
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBRecordReader.java
(added)
+++
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBRecordReader.java
Fri Jun 1 17:34:27 2012
@@ -0,0 +1,45 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.sqoop.mapreduce.db;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+
+import org.apache.hadoop.conf.Configuration;
+import org.apache.sqoop.mapreduce.DBWritable;
+
+import com.cloudera.sqoop.mapreduce.db.DBConfiguration;
+import com.cloudera.sqoop.mapreduce.db.DBInputFormat;
+import com.cloudera.sqoop.mapreduce.db.DataDrivenDBRecordReader;
+
+/**
+ * A RecordReader that reads records from DB2 via DataDrivenDBRecordReader.
+ */
+public class Db2DataDrivenDBRecordReader<T extends DBWritable>
+ extends DataDrivenDBRecordReader<T> {
+
+ // CHECKSTYLE:OFF
+ public Db2DataDrivenDBRecordReader(DBInputFormat.DBInputSplit split,
+ Class<T> inputClass, Configuration conf, Connection conn,
+ DBConfiguration dbConfig, String cond, String [] fields,
+ String table) throws SQLException {
+
+ super(split, inputClass, conf, conn, dbConfig, cond, fields, table, "DB2");
+ }
+ // CHECKSTYLE:ON
+}
Propchange:
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBRecordReader.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange:
sqoop/trunk/src/java/org/apache/sqoop/mapreduce/db/Db2DataDrivenDBRecordReader.java
------------------------------------------------------------------------------
svn:mime-type = text/plain
Modified:
sqoop/trunk/src/test/com/cloudera/sqoop/manager/DB2ManagerImportManualTest.java
URL:
http://svn.apache.org/viewvc/sqoop/trunk/src/test/com/cloudera/sqoop/manager/DB2ManagerImportManualTest.java?rev=1345282&r1=1345281&r2=1345282&view=diff
==============================================================================
---
sqoop/trunk/src/test/com/cloudera/sqoop/manager/DB2ManagerImportManualTest.java
(original)
+++
sqoop/trunk/src/test/com/cloudera/sqoop/manager/DB2ManagerImportManualTest.java
Fri Jun 1 17:34:27 2012
@@ -73,24 +73,31 @@ public class DB2ManagerImportManualTest
static final String DATABASE_USER = "SQOOP";
static final String DATABASE_PASSWORD = "PASSWORD";
static final String TABLE_NAME = "EMPLOYEES_DB2";
+ static final String QUALIFIED_TABLE_NAME = "PREFIX.EMPLOYEES_DB2";
static final String CONNECT_STRING = HOST_URL
+ "/" + DATABASE_NAME
+ ":currentSchema=" + DATABASE_USER +";";
// instance variables populated during setUp, used during tests
private Db2Manager manager;
+ private boolean useQualifiedTableName;
+ private boolean useDefaultConnectManager;
@Override
protected boolean useHsqldbTestServer() {
return false;
}
+ @Override
+ protected String getTableName() {
+ return useQualifiedTableName ? QUALIFIED_TABLE_NAME : TABLE_NAME;
+ }
+
@Before
public void setUp() {
super.setUp();
- SqoopOptions options = new SqoopOptions(CONNECT_STRING,
- TABLE_NAME);
+ SqoopOptions options = new SqoopOptions(CONNECT_STRING, getTableName());
options.setUsername(DATABASE_USER);
options.setPassword(DATABASE_PASSWORD);
@@ -102,7 +109,7 @@ public class DB2ManagerImportManualTest
try {
conn = manager.getConnection();
stmt = conn.createStatement();
- stmt.execute("DROP TABLE " + TABLE_NAME);
+ stmt.execute("DROP TABLE " + getTableName());
} catch (SQLException sqlE) {
LOG.info("Table was not dropped: " + sqlE.getMessage());
} finally {
@@ -122,20 +129,20 @@ public class DB2ManagerImportManualTest
stmt = conn.createStatement();
// create the database table and populate it with data.
- stmt.executeUpdate("CREATE TABLE " + TABLE_NAME + " ("
+ stmt.executeUpdate("CREATE TABLE " + getTableName() + " ("
+ "id INT NOT NULL, "
+ "name VARCHAR(24) NOT NULL, "
+ "salary FLOAT, "
+ "dept VARCHAR(32), "
+ "PRIMARY KEY (id))");
- stmt.executeUpdate("INSERT INTO " + TABLE_NAME + " VALUES("
+ stmt.executeUpdate("INSERT INTO " + getTableName() + " VALUES("
+ "1,'Aaron', "
+ "1000000.00,'engineering')");
- stmt.executeUpdate("INSERT INTO " + TABLE_NAME + " VALUES("
+ stmt.executeUpdate("INSERT INTO " + getTableName() + " VALUES("
+ "2,'Bob', "
+ "400.00,'sales')");
- stmt.executeUpdate("INSERT INTO " + TABLE_NAME + " VALUES("
+ stmt.executeUpdate("INSERT INTO " + getTableName() + " VALUES("
+ "3,'Fred', 15.00,"
+ "'marketing')");
conn.commit();
@@ -167,13 +174,36 @@ public class DB2ManagerImportManualTest
@Test
public void testDb2Import() throws IOException {
- String [] expectedResults = {
+ useQualifiedTableName = false;
+
+ // Verify that GenericJdbcManager works.
+ useDefaultConnectManager = true;
+ runDb2Test(getExpectedResults());
+
+ // Verify that Db2Manager works.
+ useDefaultConnectManager = false;
+ runDb2Test(getExpectedResults());
+ }
+
+ @Test
+ public void testDb2ImportQualifiedTableName() throws IOException {
+ useQualifiedTableName = true;
+
+ // Verify that GenericJdbcManager works.
+ useDefaultConnectManager = true;
+ runDb2Test(getExpectedResults());
+
+ // Verify that Db2Manager works.
+ useDefaultConnectManager = false;
+ runDb2Test(getExpectedResults());
+ }
+
+ private String [] getExpectedResults() {
+ return new String [] {
"1,Aaron,1000000.0,engineering",
"2,Bob,400.0,sales",
"3,Fred,15.0,marketing",
};
-
- runDb2Test(expectedResults);
}
private String [] getArgv() {
@@ -182,7 +212,7 @@ public class DB2ManagerImportManualTest
CommonArgs.addHadoopFlags(args);
args.add("--table");
- args.add(TABLE_NAME);
+ args.add(getTableName());
args.add("--warehouse-dir");
args.add(getWarehouseDir());
args.add("--connect");
@@ -194,13 +224,20 @@ public class DB2ManagerImportManualTest
args.add("--num-mappers");
args.add("1");
+ if (useDefaultConnectManager) {
+ // Specifying the driver class forces DefaultManagerFactory
+ // to instantiate GenericJdbcManager.
+ args.add("--driver");
+ args.add("com.ibm.db2.jcc.DB2Driver");
+ }
+
return args.toArray(new String[0]);
}
private void runDb2Test(String [] expectedResults) throws IOException {
Path warehousePath = new Path(this.getWarehouseDir());
- Path tablePath = new Path(warehousePath, TABLE_NAME);
+ Path tablePath = new Path(warehousePath, getTableName());
Path filePath = new Path(tablePath, "part-m-00000");
File tableFile = new File(tablePath.toString());
