Hey there, What are your hive import delims? Maybe --input-fields-terminated-by needs to be used: http://sqoop.apache.org/docs/1.4.5/SqoopUserGuide.html#idp6489712.
-Abe On Fri, Oct 24, 2014 at 1:13 AM, 韬奋 <[email protected]> wrote: > > 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]$ > > > >
