Hi All,
I have encounter an error when using the export to make data from hive to
RDBMS, could you please give me some suggestions ?
I am using:
Sqoop 1.4.4
Hadoop 2.4.1-3
Hive 0.13.1-4
MySql 5.5.39
mysql-connector-java-5.1.7-bin.jar
My steps:
1) create a new DB and a new Table in DB2 or MySql
For DB2: create database DBXIE
create table db2book( bookpage integer, bookprice integer)
insert into db2book values(1,1)
insert into db2book values(2,2)
insert into db2book values(3,3)
insert into db2book values(4,4)
create table db2book2( bookpage integer, bookprice integer)
For Mysql: create database DBXIE default charset utf8 COLLATE
utf8_general_ci;
create table book ( bookpage int, bookprice int );
insert into book values(1,1);
insert into book values(2,2);
insert into book values(3,3);
insert into book values(4,4);
create table book2 ( bookpage int, bookprice int );
create table book2_stage ( bookpage int, bookprice int );
2) run import to make a hive table
For DB2: ./sqoop import --connect 'jdbc:db2://9.181.64.214:50000/DBXIE'
--username catalog --password catalog --as-textfile --hive-import --hive-table
hive_db2book --table db2book -m 1
For Mysql: ./sqoop import -m 1 --connect
jdbc:mysql://9.181.64.168:3306/DBXIE --table book --username root --password
ihccluster --hive-import
3) run export to make data from hive back to an RDBMS, but encounter below
errors:
For DB2: ./sqoop export --connect jdbc:db2://9.181.64.214:50000/DBXIE
--username catalog --password catalog --export-dir
/biginsights/hive/warehouse/hive_db2book --table DB2BOOK2 --verbose
For Mysql: ./sqoop export --connect jdbc:mysql://9.181.64.168:3306/DBXIE
--username root --password ihccluster --input-fields-terminated-by ‘\001′
--export-dir /biginsights/hive/warehouse/book/part-m-00000 --table book2
--staging-table book2_stage --input-null-string ‘\\N’ --input-null-non-string
‘\\N’ --verbose
But if I make a hdfs file and then export this hdfs file back to RDBMS, then no
any error happen, could you please help me take a look at it ? or sqoop only
support export from hdfs and didn't support export from hive ?
Error log:
--- I have made some change in the source code TextExportMapper.java, add some
blue words in the sentence: throw new IOException("Can't export data, please
check task tracker logs,"+"key="+key+",val="+val, e);
...
...
14/10/22 13:02:03 INFO input.FileInputFormat: Total input paths to process : 1
14/10/22 13:02:03 DEBUG mapreduce.ExportInputFormat: Target numMapTasks=4
14/10/22 13:02:03 DEBUG mapreduce.ExportInputFormat: Total input bytes=16
14/10/22 13:02:03 DEBUG mapreduce.ExportInputFormat: maxSplitSize=4
14/10/22 13:02:03 INFO input.FileInputFormat: Total input paths to process : 1
14/10/22 13:02:03 DEBUG mapreduce.ExportInputFormat: Generated splits:
14/10/22 13:02:03 DEBUG mapreduce.ExportInputFormat:
Paths:/biginsights/hive/warehouse/hive_db2book/part-m-00000:0+4
Locations:shihc055-public.cn.ibm.com:;
14/10/22 13:02:03 DEBUG mapreduce.ExportInputFormat:
Paths:/biginsights/hive/warehouse/hive_db2book/part-m-00000:4+4
Locations:shihc055-public.cn.ibm.com:;
14/10/22 13:02:03 DEBUG mapreduce.ExportInputFormat:
Paths:/biginsights/hive/warehouse/hive_db2book/part-m-00000:8+4
Locations:shihc055-public.cn.ibm.com:;
14/10/22 13:02:03 DEBUG mapreduce.ExportInputFormat:
Paths:/biginsights/hive/warehouse/hive_db2book/part-m-00000:12+4
Locations:shihc055-public.cn.ibm.com:;
14/10/22 13:02:04 INFO mapred.JobClient: Running job: job_201409181508_0035
14/10/22 13:02:05 INFO mapred.JobClient: map 0% reduce 0%
14/10/22 13:02:16 INFO mapred.JobClient: Task Id :
attempt_201409181508_0035_m_000001_0, Status : FAILED
attempt_201409181508_0035_m_000001_0: log4j:WARN No appenders could be found
for logger (org.apache.hadoop.mapred.Child).
attempt_201409181508_0035_m_000001_0: log4j:WARN Please initialize the log4j
system properly.
attempt_201409181508_0035_m_000001_0: log4j:WARN See
http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
14/10/22 13:02:16 INFO mapred.JobClient: Task Id :
attempt_201409181508_0035_m_000000_0, Status : FAILED
java.io.IOException: Can't export data, please check task tracker
logs,key=0,val=11
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:141)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:756)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:367)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(AccessController.java:366)
at javax.security.auth.Subject.doAs(Subject.java:572)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1558)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.lang.NumberFormatException: For input string: "11"
at
java.lang.NumberFormatException.forInputString(NumberFormatException.java:76)
at java.lang.Integer.parseInt(Integer.java:503)
at java.lang.Integer.valueOf(Integer.java:593)
at DB2BOOK2.__loadFromFields(DB2BOOK2.java:195)
at DB2BOOK2.parse(DB2BOOK2.java:147)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
... 10 more
attempt_201409181508_0035_m_000000_0: log4j:WARN No appenders could be found
for logger (org.apache.hadoop.mapred.Child).
attempt_201409181508_0035_m_000000_0: log4j:WARN Please initialize the log4j
system properly.
attempt_201409181508_0035_m_000000_0: log4j:WARN See
http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
14/10/22 13:02:20 INFO mapred.JobClient: Task Id :
attempt_201409181508_0035_m_000001_1, Status : FAILED
java.io.IOException: Can't export data, please check task tracker
logs,key=8,val=33
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:141)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:756)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:367)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(AccessController.java:366)
at javax.security.auth.Subject.doAs(Subject.java:572)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1558)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.lang.NumberFormatException: For input string: "33"
at
java.lang.NumberFormatException.forInputString(NumberFormatException.java:76)
at java.lang.Integer.parseInt(Integer.java:503)
at java.lang.Integer.valueOf(Integer.java:593)
at DB2BOOK2.__loadFromFields(DB2BOOK2.java:195)
at DB2BOOK2.parse(DB2BOOK2.java:147)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
... 10 more
attempt_201409181508_0035_m_000001_1: log4j:WARN No appenders could be found
for logger (org.apache.hadoop.mapred.Child).
attempt_201409181508_0035_m_000001_1: log4j:WARN Please initialize the log4j
system properly.
attempt_201409181508_0035_m_000001_1: log4j:WARN See
http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
14/10/22 13:02:20 INFO mapred.JobClient: Task Id :
attempt_201409181508_0035_m_000000_1, Status : FAILED
java.io.IOException: Can't export data, please check task tracker
logs,key=0,val=11
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:141)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:756)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:367)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(AccessController.java:366)
at javax.security.auth.Subject.doAs(Subject.java:572)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1558)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.lang.NumberFormatException: For input string: "11"
at
java.lang.NumberFormatException.forInputString(NumberFormatException.java:76)
at java.lang.Integer.parseInt(Integer.java:503)
at java.lang.Integer.valueOf(Integer.java:593)
at DB2BOOK2.__loadFromFields(DB2BOOK2.java:195)
at DB2BOOK2.parse(DB2BOOK2.java:147)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
... 10 more
attempt_201409181508_0035_m_000000_1: log4j:WARN No appenders could be found
for logger (org.apache.hadoop.mapred.Child).
attempt_201409181508_0035_m_000000_1: log4j:WARN Please initialize the log4j
system properly.
attempt_201409181508_0035_m_000000_1: log4j:WARN See
http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
14/10/22 13:02:25 INFO mapred.JobClient: Task Id :
attempt_201409181508_0035_m_000001_2, Status : FAILED
java.io.IOException: Can't export data, please check task tracker
logs,key=8,val=33
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:141)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:756)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:367)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(AccessController.java:366)
at javax.security.auth.Subject.doAs(Subject.java:572)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1558)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.lang.NumberFormatException: For input string: "33"
at
java.lang.NumberFormatException.forInputString(NumberFormatException.java:76)
at java.lang.Integer.parseInt(Integer.java:503)
at java.lang.Integer.valueOf(Integer.java:593)
at DB2BOOK2.__loadFromFields(DB2BOOK2.java:195)
at DB2BOOK2.parse(DB2BOOK2.java:147)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
... 10 more
attempt_201409181508_0035_m_000001_2: log4j:WARN No appenders could be found
for logger (org.apache.hadoop.mapred.Child).
attempt_201409181508_0035_m_000001_2: log4j:WARN Please initialize the log4j
system properly.
attempt_201409181508_0035_m_000001_2: log4j:WARN See
http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
14/10/22 13:02:25 INFO mapred.JobClient: Task Id :
attempt_201409181508_0035_m_000000_2, Status : FAILED
java.io.IOException: Can't export data, please check task tracker
logs,key=0,val=11
at
org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:141)
at
org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:756)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:367)
at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
at java.security.AccessController.doPrivileged(AccessController.java:366)
at javax.security.auth.Subject.doAs(Subject.java:572)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1558)
at org.apache.hadoop.mapred.Child.main(Child.java:249)
Caused by: java.lang.NumberFormatException: For input string: "11"
at
java.lang.NumberFormatException.forInputString(NumberFormatException.java:76)
at java.lang.Integer.parseInt(Integer.java:503)
at java.lang.Integer.valueOf(Integer.java:593)
at DB2BOOK2.__loadFromFields(DB2BOOK2.java:195)
at DB2BOOK2.parse(DB2BOOK2.java:147)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:83)
... 10 more
attempt_201409181508_0035_m_000000_2: log4j:WARN No appenders could be found
for logger (org.apache.hadoop.mapred.Child).
attempt_201409181508_0035_m_000000_2: log4j:WARN Please initialize the log4j
system properly.
attempt_201409181508_0035_m_000000_2: log4j:WARN See
http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
14/10/22 13:02:30 INFO mapred.JobClient: Job complete: job_201409181508_0035
14/10/22 13:02:30 INFO mapred.JobClient: Counters: 7
14/10/22 13:02:30 INFO mapred.JobClient:
org.apache.hadoop.mapreduce.JobCounter
14/10/22 13:02:30 INFO mapred.JobClient: NUM_FAILED_MAPS=1
14/10/22 13:02:30 INFO mapred.JobClient: TOTAL_LAUNCHED_MAPS=8
14/10/22 13:02:30 INFO mapred.JobClient: DATA_LOCAL_MAPS=8
14/10/22 13:02:30 INFO mapred.JobClient: SLOTS_MILLIS_MAPS=39916
14/10/22 13:02:30 INFO mapred.JobClient: SLOTS_MILLIS_REDUCES=0
14/10/22 13:02:30 INFO mapred.JobClient: FALLOW_SLOTS_MILLIS_MAPS=0
14/10/22 13:02:30 INFO mapred.JobClient: FALLOW_SLOTS_MILLIS_REDUCES=0
14/10/22 13:02:30 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 28.0263
seconds (0 bytes/sec)
14/10/22 13:02:30 INFO mapreduce.ExportJobBase: Exported 0 records.
14/10/22 13:02:30 ERROR tool.ExportTool: Error during export: Export job failed!
[biadmin@shihc055-public bin]$
