Repository: sqoop Updated Branches: refs/heads/trunk 39feebff2 -> 4e0149b3e
SQOOP-2737: Cannot import table from Oracle with column with spaces in name (Sowmya Ramesh via Venkat Ranganathan) Project: http://git-wip-us.apache.org/repos/asf/sqoop/repo Commit: http://git-wip-us.apache.org/repos/asf/sqoop/commit/4e0149b3 Tree: http://git-wip-us.apache.org/repos/asf/sqoop/tree/4e0149b3 Diff: http://git-wip-us.apache.org/repos/asf/sqoop/diff/4e0149b3 Branch: refs/heads/trunk Commit: 4e0149b3ed8355cf290d82e33a977be5ee536faf Parents: 39feebf Author: Venkat Ranganathan <[email protected]> Authored: Fri Feb 19 22:11:17 2016 -0800 Committer: Venkat Ranganathan <[email protected]> Committed: Fri Feb 19 22:11:17 2016 -0800 ---------------------------------------------------------------------- .../org/apache/sqoop/manager/OracleManager.java | 18 ++++- .../sqoop/manager/oracle/OraOopConnManager.java | 38 ++++------- .../manager/oracle/OraOopOracleQueries.java | 10 +-- .../manager/oracle/OraOopOutputFormatBase.java | 2 +- .../sqoop/manager/oracle/OracleUtils.java | 69 ++++++++++++++++++++ .../mapreduce/db/OracleDBRecordReader.java | 3 +- 6 files changed, 106 insertions(+), 34 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/sqoop/blob/4e0149b3/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 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 java.util.TreeMap; 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 @@ public class OracleManager } } + @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 @@ public class OracleManager rset = pStmt.executeQuery(); while (rset.next()) { - columns.add(rset.getString(1)); + columns.add(rset.getString(1)); } conn.commit(); } catch (SQLException e) { http://git-wip-us.apache.org/repos/asf/sqoop/blob/4e0149b3/src/java/org/apache/sqoop/manager/oracle/OraOopConnManager.java ---------------------------------------------------------------------- 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 @@ public class OraOopConnManager extends GenericJdbcManager { 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 @@ public class OraOopConnManager extends GenericJdbcManager { 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 @@ public class OraOopConnManager extends GenericJdbcManager { // 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 @@ public class OraOopConnManager extends GenericJdbcManager { 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) { + @Override + public String escapeTableName(String tableName) { + return OracleUtils.escapeIdentifier(tableName); + } - if (isEscaped(columnName)) { - return columnName.substring(1, columnName.length() - 1); - } else { - return columnName; + @Override + public boolean escapeTableNameOnExport() { + return true; } - } private void logImportTableDetails(ImportJobContext context) { http://git-wip-us.apache.org/repos/asf/sqoop/blob/4e0149b3/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 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 @@ public final class OraOopOracleQueries { 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 @@ public final class OraOopOracleQueries { 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 @@ public final class OraOopOracleQueries { 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 @@ public final class OraOopOracleQueries { if (idx > 0) { columnClause.append(","); } - columnClause.append(columnNames.get(idx)); + columnClause.append(columnNames.get(idx)); } String columnsClause = columnClause.toString(); http://git-wip-us.apache.org/repos/asf/sqoop/blob/4e0149b3/src/java/org/apache/sqoop/manager/oracle/OraOopOutputFormatBase.java ---------------------------------------------------------------------- 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 @@ abstract class OraOopOutputFormatBase<K extends SqoopRecord, V> extends if (colCount > 0) { sqlNames.append("\n,"); } - sqlNames.append("\"").append(columnName).append("\""); + sqlNames.append(OracleUtils.escapeIdentifier(columnName)); // column values... if (colCount > 0) { http://git-wip-us.apache.org/repos/asf/sqoop/blob/4e0149b3/src/java/org/apache/sqoop/manager/oracle/OracleUtils.java ---------------------------------------------------------------------- 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; + } + } +} http://git-wip-us.apache.org/repos/asf/sqoop/blob/4e0149b3/src/java/org/apache/sqoop/mapreduce/db/OracleDBRecordReader.java ---------------------------------------------------------------------- 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.lang.reflect.Method; 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 @@ extends DBRecordReader<T> { 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(", "); }
