SQOOP-3158: Columns added to Mysql after initial sqoop import, export back to table with same schema fails
(Eric Lin via Anna Szonyi)
diff --git a/src/java/org/apache/sqoop/orm/ClassWriter.java b/src/java/org/apache/sqoop/orm/ClassWriter.java
index eaa9123..cdb2364 100644
--- a/src/java/org/apache/sqoop/orm/ClassWriter.java
+++ b/src/java/org/apache/sqoop/orm/ClassWriter.java
@@ -1409,9 +1409,22 @@
private void parseColumn(String colName, int colType, StringBuilder sb) {
// assume that we have __it and __cur_str vars, based on
// __loadFromFields() code.
- sb.append(" __cur_str = __it.next();\n");
+
String javaType = toJavaType(colName, colType);
+ String nullValue;
+ if (javaType.equals("String")) {
+ nullValue = this.options.getInNullStringValue();
+ } else {
+ nullValue = this.options.getInNullNonStringValue();
+ }
+
+ sb.append(" if (__it.hasNext()) {\n");
+ sb.append(" __cur_str = __it.next();\n");
+ sb.append(" } else {\n");
+ sb.append(" __cur_str = \"" + nullValue + "\";\n");
+ sb.append(" }\n");
+
parseNullVal(javaType, colName, sb);
if (javaType.equals("String")) {
// TODO(aaron): Distinguish between 'null' and null. Currently they both
diff --git a/src/test/com/cloudera/sqoop/TestExport.java b/src/test/com/cloudera/sqoop/TestExport.java
index b2edc53..8b468c8 100644
--- a/src/test/com/cloudera/sqoop/TestExport.java
+++ b/src/test/com/cloudera/sqoop/TestExport.java
@@ -895,4 +895,193 @@
verifyExport(TOTAL_RECORDS);
}
+ /**
+ * When we have less columns in the export file than in the export table, without changes in SQOOP-3158
+ * it will fail with errors. After SQOOP-3158, it should succeed without errors
+ *
+ * @throws IOException
+ * @throws SQLException
+ */
+ @Test
+ public void testLessColumnsInFileThanInTable() throws IOException, SQLException {
+ final int TOTAL_RECORDS = 10;
+
+ ColumnGenerator genDate = getDateColumnGenerator();
+ ColumnGenerator genTime = getTimeColumnGenerator();
+
+ createTextFile(0, TOTAL_RECORDS, false, genDate);
+ createTable(genDate, genTime);
+ runExport(getArgv(true, 10, 10));
+ verifyExport(TOTAL_RECORDS);
+ assertColMinAndMax(forIdx(0), genDate);
+
+ // test that the Time column is with NULL values
+ class NullColumnGenerator implements ColumnGenerator {
+ public String getExportText(int rowNum) {
+ return null;
+ }
+ public String getVerifyText(int rowNum) {
+ return null;
+ }
+ public String getType() {
+ return "Timestamp";
+ }
+ }
+
+ ColumnGenerator genNull = new NullColumnGenerator();
+ assertColMinAndMax(forIdx(1), genNull);
+ }
+
+ /**
+ * This test case covers the following:
+ *
+ * - two files under export directory
+ * - first file has one missing column
+ * - second file has correct number of columns
+ * - second file has last column with the value defined by parameter --input-null-string
+ * - last column is of STRING data type
+ *
+ * This case will happen when destination table has been modified to add a new column and
+ * user has data for both before and after the table modification, some files has less columns
+ * and some files has new columns
+ *
+ * @throws IOException
+ * @throws SQLException
+ */
+ @Test
+ public void testLessColumnsInFileThanInTableInputNullStringPassed() throws IOException, SQLException {
+ final int TOTAL_RECORDS = 10;
+
+ // a column that contains string value of "STRING_NULL",
+ // this should be treated as NULL value during test import
+ // due to --input-null-string is defined using "STRING_NULL"
+ class StringNULLValueColumnGenerator implements ColumnGenerator {
+ public String getExportText(int rowNum) {
+ return "STRING_NULL";
+ }
+ public String getVerifyText(int rowNum) {
+ return "STRING_NULL";
+ }
+ public String getType() {
+ return "VARCHAR(255)";
+ }
+ }
+
+ // a normal string column
+ class StringColumnGenerator implements ColumnGenerator {
+ public String getExportText(int rowNum) {
+ int day = rowNum + 1;
+ return "ROW_" + pad(day);
+ }
+ public String getVerifyText(int rowNum) {
+ int day = rowNum + 1;
+ return "ROW_" + pad(day);
+ }
+ public String getType() {
+ return "VARCHAR(255)";
+ }
+ }
+
+ // test that the second column is with NULL values after import
+ class NullColumnGenerator implements ColumnGenerator {
+ public String getExportText(int rowNum) {
+ return null;
+ }
+ public String getVerifyText(int rowNum) {
+ return null;
+ }
+ public String getType() {
+ return "VARCHAR(255)";
+ }
+ }
+
+ ColumnGenerator genString = new StringColumnGenerator();
+ ColumnGenerator genNullString = new StringNULLValueColumnGenerator();
+
+ createTextFile(0, TOTAL_RECORDS, false, genString);
+ createTextFile(1, TOTAL_RECORDS, false, genString, genNullString);
+ createTable(genString, genNullString);
+ runExport(getArgv(true, 10, 10, "--input-null-string", "STRING_NULL"));
+
+ verifyExport(TOTAL_RECORDS * 2);
+ assertColMinAndMax(forIdx(0), genString);
+
+ ColumnGenerator genNull = new NullColumnGenerator();
+ assertColMinAndMax(forIdx(1), genNull);
+ }
+
+ /**
+ * This test case covers the following:
+ *
+ * - two files under export directory
+ * - first file has one missing column
+ * - second file has correct number of columns
+ * - second file has last column with the value defined by parameter --input-null-non-string
+ * - last column is of INTEGER data type
+ *
+ * This case will happen when destination table has been modified to add a new column and
+ * user has data for both before and after the table modification, some files has less columns
+ * and some files has new columns
+ *
+ * @throws IOException
+ * @throws SQLException
+ */
+ @Test
+ public void testLessColumnsInFileThanInTableInputNullIntPassed() throws IOException, SQLException {
+ final int TOTAL_RECORDS = 10;
+
+ // a column that contains string value of "INT_NULL",
+ // this should be treated as NULL value during test import
+ // due to --input-null-non-string is defined using "INT_NULL"
+ class IntNULLValueColumnGenerator implements ColumnGenerator {
+ public String getExportText(int rowNum) {
+ return "INT_NULL";
+ }
+ public String getVerifyText(int rowNum) {
+ return "INT_NULL";
+ }
+ public String getType() {
+ return "INT";
+ }
+ }
+
+ // a normal string column
+ class IntColumnGenerator implements ColumnGenerator {
+ public String getExportText(int rowNum) {
+ int day = rowNum + 1;
+ return String.valueOf(day);
+ }
+ public String getVerifyText(int rowNum) {
+ int day = rowNum + 1;
+ return String.valueOf(day);
+ }
+ public String getType() {
+ return "INT";
+ }
+ }
+
+ // test that the second column is with NULL values after import
+ class NullColumnGenerator implements ColumnGenerator {
+ public String getExportText(int rowNum) {
+ return null;
+ }
+ public String getVerifyText(int rowNum) {
+ return null;
+ }
+ public String getType() {
+ return "INT";
+ }
+ }
+
+ ColumnGenerator genInt = new IntColumnGenerator();
+ ColumnGenerator genNullInt = new IntNULLValueColumnGenerator();
+
+ createTextFile(0, TOTAL_RECORDS, false, genInt);
+ createTextFile(1, TOTAL_RECORDS, false, genInt, genNullInt);
+ createTable(genInt, genNullInt);
+ runExport(getArgv(true, 10, 10, "--input-null-non-string", "INT_NULL"));
+ verifyExport(TOTAL_RECORDS * 2);
+ assertColMinAndMax(forIdx(0), genInt);
+ assertColMinAndMax(forIdx(1), new NullColumnGenerator());
+ }
}
diff --git a/src/test/org/apache/sqoop/TestExportUsingProcedure.java b/src/test/org/apache/sqoop/TestExportUsingProcedure.java
index 519305c..68e79f1 100644
--- a/src/test/org/apache/sqoop/TestExportUsingProcedure.java
+++ b/src/test/org/apache/sqoop/TestExportUsingProcedure.java
@@ -22,6 +22,7 @@
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
+import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
@@ -337,4 +338,48 @@
});
}
+ /**
+ * This test case is special - we're only inserting into a subset of the
+ * columns in the table.
+ */
+ public static void insertFunctiontestLessColumnsInFileThanInTable(int id, String msg,
+ final Date date1, final Timestamp t2) throws SQLException {
+ insertFunction(id, msg, new SetExtraArgs() {
+ @Override
+ public void set(PreparedStatement on) throws SQLException {
+ on.setDate(3, date1);
+ on.setNull(4, Types.TIMESTAMP);
+ }
+ });
+ }
+
+ /**
+ * This test case is special - we're only inserting into a subset of the
+ * columns in the table.
+ */
+ public static void insertFunctiontestLessColumnsInFileThanInTableInputNullStringPassed(int id, String msg,
+ final String str1) throws SQLException {
+ insertFunction(id, msg, new SetExtraArgs() {
+ @Override
+ public void set(PreparedStatement on) throws SQLException {
+ on.setString(3, str1);
+ on.setNull(4, Types.VARCHAR);
+ }
+ });
+ }
+
+ /**
+ * This test case is special - we're only inserting into a subset of the
+ * columns in the table.
+ */
+ public static void insertFunctiontestLessColumnsInFileThanInTableInputNullIntPassed(int id, String msg,
+ final int int1) throws SQLException {
+ insertFunction(id, msg, new SetExtraArgs() {
+ @Override
+ public void set(PreparedStatement on) throws SQLException {
+ on.setInt(3, int1);
+ on.setNull(4, Types.INTEGER);
+ }
+ });
+ }
}
