Review Board 1.7.22


SQOOP-540: Microsoft SQL Connector doesn't support custom schemas

Review Request #7369 - Created Oct. 1, 2012 and submitted

Jarek Cecho
SQOOP-540
Reviewers
Sqoop
sqoop-trunk
I've chosen similar implementation path as was used in PostgreSQL Connector.
I've test my changes (both import and export) against Microsoft SQL Server Express 2012.
src/java/org/apache/sqoop/manager/SQLServerManager.java
Revision 7ce1edd New Change
[20] 17 lines
[+20]
18

    
   
18

   
19
package org.apache.sqoop.manager;
19
package org.apache.sqoop.manager;
20

    
   
20

   
21
import java.io.IOException;
21
import java.io.IOException;
22

    
   
22

   

    
   
23
import org.apache.commons.cli.CommandLine;

    
   
24
import org.apache.commons.cli.CommandLineParser;

    
   
25
import org.apache.commons.cli.GnuParser;

    
   
26
import org.apache.commons.cli.OptionBuilder;

    
   
27
import org.apache.commons.cli.ParseException;
23
import org.apache.commons.logging.Log;
28
import org.apache.commons.logging.Log;
24
import org.apache.commons.logging.LogFactory;
29
import org.apache.commons.logging.LogFactory;
25

    
   
30

   
26
import com.cloudera.sqoop.SqoopOptions;
31
import com.cloudera.sqoop.SqoopOptions;
27
import com.cloudera.sqoop.mapreduce.ExportBatchOutputFormat;
32
import com.cloudera.sqoop.mapreduce.ExportBatchOutputFormat;
28
import com.cloudera.sqoop.mapreduce.JdbcExportJob;
33
import com.cloudera.sqoop.mapreduce.JdbcExportJob;
29
import com.cloudera.sqoop.util.ExportException;
34
import com.cloudera.sqoop.util.ExportException;

    
   
35
import org.apache.sqoop.cli.RelatedOptions;
30

    
   
36

   
31
/**
37
/**
32
 * Manages connections to SQLServer databases. Requires the SQLServer JDBC
38
 * Manages connections to SQLServer databases. Requires the SQLServer JDBC
33
 * driver.
39
 * driver.
34
 */
40
 */
35
public class SQLServerManager
41
public class SQLServerManager
36
    extends com.cloudera.sqoop.manager.InformationSchemaManager {
42
    extends com.cloudera.sqoop.manager.InformationSchemaManager {
37

    
   
43

   

    
   
44
  public static final String SCHEMA = "schema";

    
   
45

   
38
  public static final Log LOG = LogFactory.getLog(
46
  public static final Log LOG = LogFactory.getLog(
39
      SQLServerManager.class.getName());
47
      SQLServerManager.class.getName());
40

    
   
48

   
41
  // driver class to ensure is loaded when making db connection.
49
  // driver class to ensure is loaded when making db connection.
42
  private static final String DRIVER_CLASS =
50
  private static final String DRIVER_CLASS =
43
      "com.microsoft.sqlserver.jdbc.SQLServerDriver";
51
      "com.microsoft.sqlserver.jdbc.SQLServerDriver";
44

    
   
52

   

    
   
53
  /**

    
   
54
   * Schema name that we will use.

    
   
55
   */

    
   
56
  private String schema;

    
   
57

   
45
  public SQLServerManager(final SqoopOptions opts) {
58
  public SQLServerManager(final SqoopOptions opts) {
46
    super(DRIVER_CLASS, opts);
59
    super(DRIVER_CLASS, opts);

    
   
60

   

    
   
61
    // Try to parse extra arguments

    
   
62
    try {

    
   
63
      parseExtraArgs(opts.getExtraArgs());

    
   
64
    } catch (ParseException e) {

    
   
65
      throw new RuntimeException("Can't parse extra arguments", e);

    
   
66
    }
47
  }
67
  }
48

    
   
68

   
49
  /**
69
  /**
50
   * Export data stored in HDFS into a table in a database.
70
   * Export data stored in HDFS into a table in a database.
51
   */
71
   */
[+20] [20] 21 lines
[+20] [+] public String getCurTimestampQuery() {
73
    return "SELECT NAME FROM SYS.DATABASES";
93
    return "SELECT NAME FROM SYS.DATABASES";
74
  }
94
  }
75

    
   
95

   
76
  @Override
96
  @Override
77
  protected String getSchemaQuery() {
97
  protected String getSchemaQuery() {

    
   
98
    if (schema == null) {
78
    return "SELECT SCHEMA_NAME()";
99
      return "SELECT SCHEMA_NAME()";
79
  }
100
    }
80

    
   
101

   

    
   
102
    return "'" + schema + "'";

    
   
103
  }

    
   
104

   
81
  @Override
105
  @Override
82
  public String escapeColName(String colName) {
106
  public String escapeColName(String colName) {
83
    if (null == colName) {
107
    return escapeObjectName(colName);
84
      return null;

   
85
    }

   
86
    return "[" + colName + "]";

   
87
  }
108
  }
88

    
   
109

   
89
  @Override
110
  @Override
90
  public String escapeTableName(String tableName) {
111
  public String escapeTableName(String tableName) {
91
    if (null == tableName) {
112
    // Return table name including schema if requested

    
   
113
    if (schema != null && !schema.isEmpty()) {

    
   
114
      return escapeObjectName(schema) + "." + escapeObjectName(tableName);

    
   
115
    }

    
   
116

   

    
   
117
    return escapeObjectName(tableName);

    
   
118
  }

    
   
119

   

    
   
120
  /**

    
   
121
   * Escape database object name (database, table, column, schema).

    
   
122
   *

    
   
123
   * @param objectName Object name in database

    
   
124
   * @return Escaped variant of the name

    
   
125
   */

    
   
126
  public String escapeObjectName(String objectName) {

    
   
127
    if (null == objectName) {
92
      return null;
128
      return null;
93
    }
129
    }
94
    return "[" + tableName + "]";
130
    return "[" + objectName + "]";

    
   
131
  }

    
   
132

   

    
   
133
  /**

    
   
134
   * Parse extra arguments.

    
   
135
   *

    
   
136
   * @param args Extra arguments array

    
   
137
   * @throws ParseException

    
   
138
   */

    
   
139
  void parseExtraArgs(String[] args) throws ParseException {

    
   
140
    // No-op when no extra arguments are present

    
   
141
    if (args == null || args.length == 0) {

    
   
142
      return;

    
   
143
    }

    
   
144

   

    
   
145
    // We do not need extended abilities of SqoopParser, so we're using

    
   
146
    // Gnu parser instead.

    
   
147
    CommandLineParser parser = new GnuParser();

    
   
148
    CommandLine cmdLine = parser.parse(getExtraOptions(), args, true);

    
   
149

   

    
   
150
    // Apply extra options

    
   
151
    if (cmdLine.hasOption(SCHEMA)) {

    
   
152
      String schemaName = cmdLine.getOptionValue(SCHEMA);

    
   
153
      LOG.info("We will use schema " + schemaName);

    
   
154

   

    
   
155
      this.schema = schemaName;

    
   
156
    }

    
   
157
  }

    
   
158

   

    
   
159
  /**

    
   
160
   * Create related options for SQL Server extra parameters.

    
   
161
   *

    
   
162
   * @return

    
   
163
   */

    
   
164
  @SuppressWarnings("static-access")

    
   
165
  private RelatedOptions getExtraOptions() {

    
   
166
    // Connection args (common)

    
   
167
    RelatedOptions extraOptions =

    
   
168
      new RelatedOptions("SQL Server extra options:");

    
   
169

   

    
   
170
    extraOptions.addOption(OptionBuilder.withArgName("string").hasArg()

    
   
171
      .withDescription("Optional schema name")

    
   
172
      .withLongOpt(SCHEMA).create());

    
   
173

   

    
   
174
    return extraOptions;
95
  }
175
  }
96
}
176
}
97

    
   
177

   
src/java/org/apache/sqoop/mapreduce/db/DataDrivenDBRecordReader.java
Revision a56b93d New Change
 
src/test/com/cloudera/sqoop/manager/SQLServerManagerExportManualTest.java
New File
 
src/test/com/cloudera/sqoop/manager/SQLServerManagerImportManualTest.java
Revision 0f949f4 New Change
 
  1. src/java/org/apache/sqoop/manager/SQLServerManager.java: Loading...
  2. src/java/org/apache/sqoop/mapreduce/db/DataDrivenDBRecordReader.java: Loading...
  3. src/test/com/cloudera/sqoop/manager/SQLServerManagerExportManualTest.java: Loading...
  4. src/test/com/cloudera/sqoop/manager/SQLServerManagerImportManualTest.java: Loading...