32dfc5eb238dba53fcf3595f3a9b1a1c2346a90a
[sqoop.git] / src / test / org / apache / sqoop / manager / db2 / DB2ManagerImportManualTest.java
1 /**
2 * Licensed to the Apache Software Foundation (ASF) under one
3 * or more contributor license agreements. See the NOTICE file
4 * distributed with this work for additional information
5 * regarding copyright ownership. The ASF licenses this file
6 * to you under the Apache License, Version 2.0 (the
7 * "License"); you may not use this file except in compliance
8 * with the License. You may obtain a copy of the License at
9 *
10 * http://www.apache.org/licenses/LICENSE-2.0
11 *
12 * Unless required by applicable law or agreed to in writing, software
13 * distributed under the License is distributed on an "AS IS" BASIS,
14 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15 * See the License for the specific language governing permissions and
16 * limitations under the License.
17 */
18 package org.apache.sqoop.manager.db2;
19
20 import java.io.BufferedReader;
21 import java.io.File;
22 import java.io.FileInputStream;
23 import java.io.IOException;
24 import java.io.InputStreamReader;
25 import java.sql.Connection;
26 import java.sql.SQLException;
27 import java.sql.Statement;
28 import java.util.ArrayList;
29
30 import org.apache.commons.logging.Log;
31 import org.apache.commons.logging.LogFactory;
32 import org.apache.hadoop.fs.Path;
33 import org.apache.hadoop.io.IOUtils;
34 import org.apache.sqoop.manager.Db2Manager;
35 import org.apache.sqoop.testcategories.thirdpartytest.Db2Test;
36 import org.apache.sqoop.testcategories.sqooptest.ManualTest;
37 import org.junit.After;
38 import org.junit.Before;
39 import org.junit.Test;
40
41 import org.apache.sqoop.SqoopOptions;
42 import org.apache.sqoop.testutil.CommonArgs;
43 import org.apache.sqoop.testutil.ImportJobTestCase;
44 import org.apache.sqoop.util.FileListing;
45 import org.junit.experimental.categories.Category;
46
47 import static org.junit.Assert.assertEquals;
48 import static org.junit.Assert.assertTrue;
49 import static org.junit.Assert.fail;
50
51 /**
52 * Test the DB2Manager implementation.
53 *
54 * This uses JDBC to import data from an DB2 database into HDFS.
55 *
56 * Since this requires an DB2 Server installation,
57 * this class is named in such a way that Sqoop's default QA process does
58 * not run it. You need to run this manually with
59 * -Dtestcase=DB2ManagerImportManualTest
60 *
61 * You need to put DB2 JDBC driver library (db2jcc4.jar) in a location
62 * where Sqoop will be able to access it (since this library cannot be checked
63 * into Apache's tree for licensing reasons).
64 *
65 * To set up your test environment:
66 * Install DB2 Express 9.7 C server.
67 * Create a database SQOOP
68 * Create a login SQOOP with password PASSWORD and grant all
69 * access for database SQOOP to user SQOOP.
70 */
71 @Category({ManualTest.class, Db2Test.class})
72 public class DB2ManagerImportManualTest extends ImportJobTestCase {
73
74 public static final Log LOG = LogFactory.getLog(
75 DB2ManagerImportManualTest.class.getName());
76
77 static final String HOST_URL = System.getProperty(
78 "sqoop.test.db2.connectstring.host_url",
79 "jdbc:db2://db2host:50000");
80
81 static final String DATABASE_NAME = System.getProperty(
82 "sqoop.test.db2.connectstring.database",
83 "SQOOP");
84 static final String DATABASE_USER = System.getProperty(
85 "sqoop.test.db2.connectstring.username",
86 "SQOOP");
87 static final String DATABASE_PASSWORD = System.getProperty(
88 "sqoop.test.db2.connectstring.password",
89 "SQOOP");
90 static final String TABLE_NAME = "EMPLOYEES_DB2";
91 static final String QUALIFIED_TABLE_NAME = DATABASE_USER + ".EMPLOYEES_DB2";
92 static final String CONNECT_STRING = HOST_URL
93 + "/" + DATABASE_NAME
94 + ":currentSchema=" + DATABASE_USER +";";
95
96 static {
97 LOG.info("Using DB2 CONNECT_STRING: " + CONNECT_STRING);
98 }
99
100 // instance variables populated during setUp, used during tests
101 private Db2Manager manager;
102 private boolean useQualifiedTableName;
103 private boolean useDefaultConnectManager;
104
105 @Override
106 protected boolean useHsqldbTestServer() {
107 return false;
108 }
109
110 @Override
111 protected String getTableName() {
112 return useQualifiedTableName ? QUALIFIED_TABLE_NAME : TABLE_NAME;
113 }
114
115 @Before
116 public void setUp() {
117 super.setUp();
118
119 SqoopOptions options = new SqoopOptions(CONNECT_STRING, getTableName());
120 options.setUsername(DATABASE_USER);
121 options.setPassword(DATABASE_PASSWORD);
122
123 manager = new Db2Manager(options);
124
125 // Drop the existing table, if there is one.
126 Connection conn = null;
127 Statement stmt = null;
128 try {
129 conn = manager.getConnection();
130 stmt = conn.createStatement();
131 stmt.execute("DROP TABLE " + getTableName());
132 } catch (SQLException sqlE) {
133 LOG.info("Table was not dropped: " + sqlE.getMessage());
134 } finally {
135 try {
136 if (null != stmt) {
137 stmt.close();
138 }
139 } catch (Exception ex) {
140 LOG.warn("Exception while closing stmt", ex);
141 }
142 }
143
144 // Create and populate table
145 try {
146 conn = manager.getConnection();
147 conn.setAutoCommit(false);
148 stmt = conn.createStatement();
149
150 // create the database table and populate it with data.
151 stmt.executeUpdate("CREATE TABLE " + getTableName() + " ("
152 + "id INT NOT NULL, "
153 + "name VARCHAR(24) NOT NULL, "
154 + "salary FLOAT, "
155 + "dept VARCHAR(32), "
156 + "PRIMARY KEY (id))");
157
158 stmt.executeUpdate("INSERT INTO " + getTableName() + " VALUES("
159 + "1,'Aaron', "
160 + "1000000.00,'engineering')");
161 stmt.executeUpdate("INSERT INTO " + getTableName() + " VALUES("
162 + "2,'Bob', "
163 + "400.00,'sales')");
164 stmt.executeUpdate("INSERT INTO " + getTableName() + " VALUES("
165 + "3,'Fred', 15.00,"
166 + "'marketing')");
167 conn.commit();
168 } catch (SQLException sqlE) {
169 LOG.error("Encountered SQL Exception: ", sqlE);
170 sqlE.printStackTrace();
171 fail("SQLException when running test setUp(): " + sqlE);
172 } finally {
173 try {
174 if (null != stmt) {
175 stmt.close();
176 }
177 } catch (Exception ex) {
178 LOG.warn("Exception while closing connection/stmt", ex);
179 }
180 }
181 }
182
183 @After
184 public void tearDown() {
185 super.tearDown();
186 try {
187 manager.close();
188 } catch (SQLException sqlE) {
189 LOG.error("Got SQLException: " + sqlE.toString());
190 fail("Got SQLException: " + sqlE.toString());
191 }
192 }
193
194 @Test
195 public void testDb2Import() throws IOException {
196 useQualifiedTableName = false;
197
198 // Verify that GenericJdbcManager works.
199 useDefaultConnectManager = true;
200 runDb2Test(getExpectedResults());
201
202 // Verify that Db2Manager works.
203 useDefaultConnectManager = false;
204 runDb2Test(getExpectedResults());
205 }
206
207 @Test
208 public void testDb2ImportQualifiedTableName() throws IOException {
209 useQualifiedTableName = true;
210
211 // Verify that GenericJdbcManager works.
212 useDefaultConnectManager = true;
213 runDb2Test(getExpectedResults());
214
215 // Verify that Db2Manager works.
216 useDefaultConnectManager = false;
217 runDb2Test(getExpectedResults());
218 }
219
220 private String [] getExpectedResults() {
221 return new String [] {
222 "1,Aaron,1000000.0,engineering",
223 "2,Bob,400.0,sales",
224 "3,Fred,15.0,marketing",
225 };
226 }
227
228 private String [] getArgv() {
229 ArrayList<String> args = new ArrayList<String>();
230
231 CommonArgs.addHadoopFlags(args);
232
233 args.add("--table");
234 args.add(getTableName());
235 args.add("--warehouse-dir");
236 args.add(getWarehouseDir());
237 args.add("--connect");
238 args.add(CONNECT_STRING);
239 args.add("--username");
240 args.add(DATABASE_USER);
241 args.add("--password");
242 args.add(DATABASE_PASSWORD);
243 args.add("--num-mappers");
244 args.add("1");
245
246 if (useDefaultConnectManager) {
247 // Specifying the driver class forces DefaultManagerFactory
248 // to instantiate GenericJdbcManager.
249 args.add("--driver");
250 args.add("com.ibm.db2.jcc.DB2Driver");
251 }
252
253 return args.toArray(new String[0]);
254 }
255
256 private void runDb2Test(String [] expectedResults) throws IOException {
257
258 Path warehousePath = new Path(this.getWarehouseDir());
259 Path tablePath = new Path(warehousePath, getTableName());
260 Path filePath = new Path(tablePath, "part-m-00000");
261
262 File tableFile = new File(tablePath.toString());
263 if (tableFile.exists() && tableFile.isDirectory()) {
264 // remove the directory before running the import.
265 FileListing.recursiveDeleteDir(tableFile);
266 }
267
268 String [] argv = getArgv();
269 try {
270 runImport(argv);
271 } catch (IOException ioe) {
272 LOG.error("Got IOException during import: " + ioe.toString());
273 ioe.printStackTrace();
274 fail(ioe.toString());
275 }
276
277 File f = new File(filePath.toString());
278 assertTrue("Could not find imported data file", f.exists());
279 BufferedReader r = null;
280 try {
281 // Read through the file and make sure it's all there.
282 r = new BufferedReader(new InputStreamReader(new FileInputStream(f)));
283 for (String expectedLine : expectedResults) {
284 assertEquals(expectedLine, r.readLine());
285 }
286 } catch (IOException ioe) {
287 LOG.error("Got IOException verifying results: " + ioe.toString());
288 ioe.printStackTrace();
289 fail(ioe.toString());
290 } finally {
291 IOUtils.closeStream(r);
292 }
293 }
294
295 }