SQOOP-2737: Cannot import table from Oracle with column with spaces in name
(Sowmya Ramesh via Venkat Ranganathan)
diff --git a/src/java/org/apache/sqoop/manager/OracleManager.java b/src/java/org/apache/sqoop/manager/OracleManager.java
index d088265..d769972 100644
--- a/src/java/org/apache/sqoop/manager/OracleManager.java
+++ b/src/java/org/apache/sqoop/manager/OracleManager.java
@@ -43,6 +43,7 @@
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
+import org.apache.sqoop.manager.oracle.OracleUtils;
import org.apache.sqoop.util.LoggingUtils;
import com.cloudera.sqoop.SqoopOptions;
@@ -916,6 +917,21 @@
}
}
+ @Override
+ public String escapeColName(String colName) {
+ return OracleUtils.escapeIdentifier(colName);
+ }
+
+ @Override
+ public String escapeTableName(String tableName) {
+ return OracleUtils.escapeIdentifier(tableName);
+ }
+
+ @Override
+ public boolean escapeTableNameOnExport() {
+ return true;
+ }
+
@Override
public String[] getColumnNames(String tableName) {
Connection conn = null;
@@ -947,7 +963,7 @@
rset = pStmt.executeQuery();
while (rset.next()) {
- columns.add(rset.getString(1));
+ columns.add(rset.getString(1));
}
conn.commit();
} catch (SQLException e) {
diff --git a/src/java/org/apache/sqoop/manager/oracle/OraOopConnManager.java b/src/java/org/apache/sqoop/manager/oracle/OraOopConnManager.java
index 9d74625..216c771 100644
--- a/src/java/org/apache/sqoop/manager/oracle/OraOopConnManager.java
+++ b/src/java/org/apache/sqoop/manager/oracle/OraOopConnManager.java
@@ -151,12 +151,12 @@
String selectedColumn = selectedColumns[idx];
// If the user did not escape this column name, then we should
// uppercase it...
- if (!isEscaped(selectedColumn)) {
+ if (!OracleUtils.isEscaped(selectedColumn)) {
selectedColumns[idx] = selectedColumn.toUpperCase();
} else {
// If the user escaped this column name, then we should
// retain its case...
- selectedColumns[idx] = unescapeOracleColumnName(selectedColumn);
+ selectedColumns[idx] = OracleUtils.unescapeIdentifier(selectedColumn);
}
}
@@ -221,7 +221,7 @@
if (idx > 0) {
sb.append(",");
}
- sb.append(escapeOracleColumnName(colNames.get(idx))); // <- See notes at
+ sb.append(escapeColName(colNames.get(idx))); // <- See notes at
// top about escaped
// column names
}
@@ -513,7 +513,7 @@
// Unescape the column names being returned...
int colType = columnTypes.get(columnNameInTable);
- String key = unescapeOracleColumnName(columnNameInTable); // <- See
+ String key = OracleUtils.unescapeIdentifier(columnNameInTable); // <- See
// notes at
// top about
// escaped
@@ -527,35 +527,21 @@
return this.columnTypesInOracleTable;
}
- private boolean isEscaped(String name) {
-
- return name.startsWith("\"") && name.endsWith("\"");
- }
-
- private String escapeOracleColumnName(String columnName) {
- // See notes at top about escaped column names
- if (isEscaped(columnName)) {
- return columnName;
- } else {
- return "\"" + columnName + "\"";
- }
- }
-
@Override
public String escapeColName(String colName) {
-
- return escapeOracleColumnName(colName); // <- See notes at top about escaped
+ return OracleUtils.escapeIdentifier(colName); // <- See notes at top about escaped
// column names
}
- private String unescapeOracleColumnName(String columnName) {
-
- if (isEscaped(columnName)) {
- return columnName.substring(1, columnName.length() - 1);
- } else {
- return columnName;
+ @Override
+ public String escapeTableName(String tableName) {
+ return OracleUtils.escapeIdentifier(tableName);
}
- }
+
+ @Override
+ public boolean escapeTableNameOnExport() {
+ return true;
+ }
private void logImportTableDetails(ImportJobContext context) {
diff --git a/src/java/org/apache/sqoop/manager/oracle/OraOopOracleQueries.java b/src/java/org/apache/sqoop/manager/oracle/OraOopOracleQueries.java
index 82e4266..6b27bd8 100644
--- a/src/java/org/apache/sqoop/manager/oracle/OraOopOracleQueries.java
+++ b/src/java/org/apache/sqoop/manager/oracle/OraOopOracleQueries.java
@@ -680,7 +680,7 @@
if (idx > 0) {
columnList.append(",");
}
- columnList.append("\"").append(result.get(idx).getName()).append("\"");
+ columnList.append(OracleUtils.escapeIdentifier(result.get(idx).getName()));
}
sql =
String.format("SELECT %s FROM %s WHERE 0=1", columnList.toString(),
@@ -1244,12 +1244,12 @@
if (targetColumnsClause.length() > 0) {
targetColumnsClause.append(",");
}
- targetColumnsClause.append(String.format("a.\"%s\"", columnName));
+ targetColumnsClause.append(String.format("a.%s", OracleUtils.escapeIdentifier(columnName)));
if (sourceColumnsClause.length() > 0) {
sourceColumnsClause.append(",");
}
- sourceColumnsClause.append(String.format("b.\"%s\"", columnName));
+ sourceColumnsClause.append(String.format("b.%s", OracleUtils.escapeIdentifier(columnName)));
}
String sourceClause = sourceColumnsClause.toString();
@@ -1313,7 +1313,7 @@
if (idx > 0) {
columnClause.append(",");
}
- columnClause.append("a.\"" + columnNames.get(idx) + "\"");
+ columnClause.append(String.format("a.%s", OracleUtils.escapeIdentifier(columnNames.get(idx))));
}
StringBuilder rowEqualityClause = new StringBuilder();
@@ -1443,7 +1443,7 @@
if (idx > 0) {
columnClause.append(",");
}
- columnClause.append(columnNames.get(idx));
+ columnClause.append(columnNames.get(idx));
}
String columnsClause = columnClause.toString();
diff --git a/src/java/org/apache/sqoop/manager/oracle/OraOopOutputFormatBase.java b/src/java/org/apache/sqoop/manager/oracle/OraOopOutputFormatBase.java
index 8f7016a..8f94cf8 100644
--- a/src/java/org/apache/sqoop/manager/oracle/OraOopOutputFormatBase.java
+++ b/src/java/org/apache/sqoop/manager/oracle/OraOopOutputFormatBase.java
@@ -354,7 +354,7 @@
if (colCount > 0) {
sqlNames.append("\n,");
}
- sqlNames.append("\"").append(columnName).append("\"");
+ sqlNames.append(OracleUtils.escapeIdentifier(columnName));
// column values...
if (colCount > 0) {
diff --git a/src/java/org/apache/sqoop/manager/oracle/OracleUtils.java b/src/java/org/apache/sqoop/manager/oracle/OracleUtils.java
new file mode 100644
index 0000000..b46034b
--- /dev/null
+++ b/src/java/org/apache/sqoop/manager/oracle/OracleUtils.java
@@ -0,0 +1,69 @@
+/**
+ * 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.manager.oracle;
+
+import org.apache.commons.lang3.StringUtils;
+
+/**
+ * Utility class for Oracle.
+ *
+ */
+public final class OracleUtils {
+ private static final String PERIOD_REGEX = "\\.";
+ private static final String PERIOD_DELIMITER = ".";
+
+ public static boolean isEscaped(final String identifier) {
+ return !StringUtils.isBlank(identifier) && identifier.startsWith("\"") && identifier.endsWith("\"");
+ }
+
+ public static String escapeIdentifier(final String identifier) {
+ if (StringUtils.isBlank(identifier)) {
+ return identifier;
+ }
+
+ if (isEscaped(identifier)) {
+ return identifier;
+ } else {
+ // If format is a.b.c then each identifier has to be escaped separately to avoid "ORA-00972: identifier is
+ // too long" error
+ String[] parts = identifier.split(PERIOD_REGEX);
+
+ StringBuilder escapedIdentifier = new StringBuilder();
+ for(String part : parts) {
+ if (StringUtils.isNotBlank(escapedIdentifier)) {
+ escapedIdentifier.append(PERIOD_DELIMITER);
+ }
+ escapedIdentifier.append("\"" ).append(part).append("\"");
+ }
+ return StringUtils.isNotBlank(escapedIdentifier) ? escapedIdentifier.toString() : null;
+ }
+ }
+
+ public static String unescapeIdentifier(final String identifier) {
+ if (StringUtils.isBlank(identifier)) {
+ return identifier;
+ }
+
+ if (isEscaped(identifier)) {
+ return identifier.substring(1, identifier.length() - 1);
+ } else {
+ return identifier;
+ }
+ }
+}
diff --git a/src/java/org/apache/sqoop/mapreduce/db/OracleDBRecordReader.java b/src/java/org/apache/sqoop/mapreduce/db/OracleDBRecordReader.java
index 9058a55..f1647b8 100644
--- a/src/java/org/apache/sqoop/mapreduce/db/OracleDBRecordReader.java
+++ b/src/java/org/apache/sqoop/mapreduce/db/OracleDBRecordReader.java
@@ -22,6 +22,7 @@
import java.sql.Connection;
import java.sql.SQLException;
+import org.apache.sqoop.manager.oracle.OracleUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.hadoop.conf.Configuration;
@@ -66,7 +67,7 @@
query.append("SELECT ");
for (int i = 0; i < fieldNames.length; i++) {
- query.append(fieldNames[i]);
+ query.append(OracleUtils.escapeIdentifier(fieldNames[i]));
if (i != fieldNames.length -1) {
query.append(", ");
}
