Sqoop import all tables

[#1]

Hello,

I would like to run a command like:

sqoop import-all-tables --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://localhost/testdb --username username --password password -- --schema the_schema --hive-database TestDB --hive-import

However, the "-- --schema the_schema" is not being picked up. Instead, all the tables are assuming that "testdb" is the schema instead of "the_schema".

Is there some way to correct this? we would like to the import-all-tables to work as we have many tables that maybe changing throughout the life of the database and hadoop.

Thanks,

Marcia

[#2]

These might help:

https://community.hortonworks.com/questions/77221/sqoop-import-all-tables-into-hdfs.html

https://community.hortonworks.com/questions/27621/sqoop-import-multiple-table.html

[#3]

As per design of import-all-tables https://sqoop.apache.org/docs/1.4.3/SqoopUserGuide.html#_literal_sqoop_import_all_tables_literal --schema is not part of that tool. You may have to fetch individual tables and provide schema with the import tool.

[#4]

i'm trying to run:

sqoop import-all-tables --driver com.ibm.db2.jcc.DB2Driver --connect jdbc:db2://local/TESTDB --username SCHEMA --password password --hive-database HIVEDB --hive-import -m 1

I get the error: ERROR tool.ImportAllTablesTool: Error during import: Import job failed!

Please let me know what I am doing wrong...

Thanks,

Marcy

[#5]

Also get this error:

failed with state KILLED due to: MAP capability required is more than the supported max container capability in the cluster. Killing the Job.

[#6 · Accepted solution]

-D mapreduce.map.memory.mb=2048-D mapreduce.map.java.opts=-Xmx1024m . Change the memory acccordingly based on your cluster and try it.

[#7]

Previous errors solved.

Now I get:

Error: java.io.IOException: SQLException in nextKeyValue at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277) at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:556)

[#8]

i think we are dealing with multiple things here. Can you please accept the previous answers?

[#9]

Post the entire error message and stack trace always that will help. Update your post with the entire stack trace to understand it better.

[#10]

17/03/13 14:41:22 INFO mapreduce.Job: Task Id : attempt_1489428784285_0004_m_000000_0, Status : F AILED Error: java.io.IOException: SQLException in nextKeyValue at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277) at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:556 ) at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80) at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.j ava:91) at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145) at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64) at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162) Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=4270 3, SQLERRMC=PROJECT_ID, DRIVER=4.11.77 at com.ibm.db2.jcc.am.gd.a(gd.java:676) at com.ibm.db2.jcc.am.gd.a(gd.java:60) at com.ibm.db2.jcc.am.gd.a(gd.java:127) at com.ibm.db2.jcc.am.jn.c(jn.java:2561) at com.ibm.db2.jcc.am.jn.d(jn.java:2549) at com.ibm.db2.jcc.am.jn.a(jn.java:2025) at com.ibm.db2.jcc.am.kn.a(kn.java:6836) at com.ibm.db2.jcc.t4.cb.g(cb.java:140) at com.ibm.db2.jcc.t4.cb.a(cb.java:40) at com.ibm.db2.jcc.t4.q.a(q.java:32) at com.ibm.db2.jcc.t4.rb.i(rb.java:135) at com.ibm.db2.jcc.am.jn.ib(jn.java:1996) at com.ibm.db2.jcc.am.kn.sc(kn.java:3058) at com.ibm.db2.jcc.am.kn.b(kn.java:3841) at com.ibm.db2.jcc.am.kn.fc(kn.java:702) at com.ibm.db2.jcc.am.kn.executeQuery(kn.java:672) at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111) at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235) ... 12 more 17/03/13 14:41:28 INFO mapreduce.Job: Task Id : attempt_1489428784285_0004_m_000000_1, Status : FAILED Error: java.io.IOException: SQLException in nextKeyValue at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277) at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:556) at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80) at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91) at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145) at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64) at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162) Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=PROJECT_ID, DRIVER=4.11.77 at com.ibm.db2.jcc.am.gd.a(gd.java:676) at com.ibm.db2.jcc.am.gd.a(gd.java:60) at com.ibm.db2.jcc.am.gd.a(gd.java:127) at com.ibm.db2.jcc.am.jn.c(jn.java:2561) at com.ibm.db2.jcc.am.jn.d(jn.java:2549) at com.ibm.db2.jcc.am.jn.a(jn.java:2025) at com.ibm.db2.jcc.am.kn.a(kn.java:6836) at com.ibm.db2.jcc.t4.cb.g(cb.java:140) at com.ibm.db2.jcc.t4.cb.a(cb.java:40) at com.ibm.db2.jcc.t4.q.a(q.java:32) at com.ibm.db2.jcc.t4.rb.i(rb.java:135) at com.ibm.db2.jcc.am.jn.ib(jn.java:1996) at com.ibm.db2.jcc.am.kn.sc(kn.java:3058) at com.ibm.db2.jcc.am.kn.b(kn.java:3841) at com.ibm.db2.jcc.am.kn.fc(kn.java:702) at com.ibm.db2.jcc.am.kn.executeQuery(kn.java:672) at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111) at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235) ... 12 more Container killed by the ApplicationMaster. Container killed on request. Exit code is 143 Container exited with a non-zero exit code 143 17/03/13 14:41:33 INFO mapreduce.Job: Task Id : attempt_1489428784285_0004_m_000000_2, Status : FAILED Error: java.io.IOException: SQLException in nextKeyValue at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277) at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:556) at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80) at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91) at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145) at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64) at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162) Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=PROJECT_ID, DRIVER=4.11.77 at com.ibm.db2.jcc.am.gd.a(gd.java:676) at com.ibm.db2.jcc.am.gd.a(gd.java:60) at com.ibm.db2.jcc.am.gd.a(gd.java:127) at com.ibm.db2.jcc.am.jn.c(jn.java:2561) at com.ibm.db2.jcc.am.jn.d(jn.java:2549) at com.ibm.db2.jcc.am.jn.a(jn.java:2025) at com.ibm.db2.jcc.am.kn.a(kn.java:6836) at com.ibm.db2.jcc.t4.cb.g(cb.java:140) at com.ibm.db2.jcc.t4.cb.a(cb.java:40) at com.ibm.db2.jcc.t4.q.a(q.java:32) at com.ibm.db2.jcc.t4.rb.i(rb.java:135) at com.ibm.db2.jcc.am.jn.ib(jn.java:1996) at com.ibm.db2.jcc.am.kn.sc(kn.java:3058) at com.ibm.db2.jcc.am.kn.b(kn.java:3841) at com.ibm.db2.jcc.am.kn.fc(kn.java:702) at com.ibm.db2.jcc.am.kn.executeQuery(kn.java:672) at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111) at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235) ... 12 more Container killed by the ApplicationMaster. Container killed on request. Exit code is 143 Container exited with a non-zero exit code 143 17/03/13 14:41:44 INFO mapreduce.Job: map 100% reduce 0% 17/03/13 14:41:45 INFO mapreduce.Job: Job job_1489428784285_0004 failed with state FAILED due to: Task failed task_1489428784285_0004_m_000000 Job failed as tasks failed. failedMaps:1 failedReduces:0 17/03/13 14:41:45 INFO mapreduce.Job: Counters: 8 Job Counters Failed map tasks=4 Launched map tasks=4 Other local map tasks=4 Total time spent by all maps in occupied slots (ms)=21762 Total time spent by all reduces in occupied slots (ms)=0 Total time spent by all map tasks (ms)=21762 Total vcore-milliseconds taken by all map tasks=21762 Total megabyte-milliseconds taken by all map tasks=55710720 17/03/13 14:41:45 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead 17/03/13 14:41:45 INFO mapreduce.ImportJobBase: Transferred 0 bytes in 51.3195 seconds (0 bytes/sec) 17/03/13 14:41:45 WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead 17/03/13 14:41:45 INFO mapreduce.ImportJobBase: Retrieved 0 records. 17/03/13 14:41:45 ERROR tool.ImportAllTablesTool: Error during import: Import job failed!