使用sqoop/oozie将数据从hive加载到mysql表

uujelgoq  于 2021-06-02  发布在  Hadoop
关注(0)|答案(4)|浏览(327)

在使用sqoop(oozie中的sqoop命令)将数据加载到mysql表时,我遇到了一些严重的问题(尝试了196次都没有成功)。如果hdfs中只有一列数据(这里是foo),那么就没有问题了,但是当有超过1列(例如2列)时,数据不会加载到mysql。
如果我自己运行sqoop,那么数据会被加载到mysql中,但是当我放入oozie时,数据不会被加载。
xml有两个部分,第一部分将数据从配置单元表加载到hdfs,第二部分将数据从hdfs加载到mysql。
我正在使用clouderavm。
错误消息是:

Caused by: java.lang.NumberFormatException: For input string: "1    a"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:458)
hive> CREATE EXTERNAL TABLE IF NOT EXISTS foo (
id int,
city string
 )
ROW FORMAT DELIMITED
 FIELDS TERMINATED BY '\t'
 LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/cloudera/foo';
$ vi foo
1   a
4   b

hive> load data local inpath '/home/cloudera/foo' into table foo;
mysql> CREATE TABLE `foo` (`id` int(11) DEFAULT NULL, `city` varchar(22) DEFAULT NULL );

工作流.xml:

<?xml version="1.0" encoding="UTF-8"?>
<workflow-app xmlns="uri:oozie:workflow:0.2" name="etl-wf">
    <start to="hive-node"/>

    <action name="hive-node">
    <hive xmlns="uri:oozie:hive-action:0.2">
             <job-tracker>${jobTracker}</job-tracker>
             <name-node>${nameNode}</name-node>
        <job-xml>hive-site.xml</job-xml>
       <script>script.q</script>
    </hive>
    <ok to="sqoop-node"/>
    <error to="fail"/>
 </action>

 <action name="sqoop-node">
    <sqoop xmlns="uri:oozie:sqoop-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
            <command>export --connect jdbc:mysql://www.abc.net/test --username rio --password r005 --table foo  --export-dir /user/cloudera/test --input-fields-terminated-by '\t' --input-lines-terminated-by '\n'</command>
    </sqoop>
    <ok to="end"/>
    <error to="fail"/>
</action>

<kill name="fail">
    <message>Hive failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
</kill>
<end name="end"/>

</workflow-app>

注意:这里第一部分工作,即数据被加载到hive中的test表,但没有从hdfs:/user/cloudera/test加载到mysql表foo。

六、问:

CREATE EXTERNAL TABLE IF NOT EXISTS test (
id int,
city string
  )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION
 '/user/cloudera/test';

INSERT OVERWRITE table test SELECT * FROM foo;

标准日志

Note: /tmp/sqoop-mapred/compile/d4f769ef09667984820f21a38ae27bb4/foo.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
java.io.IOException: Can't export data, please check task tracker logs
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:140)
at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:672)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:330)
at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
at org.apache.hadoop.mapred.Child.main(Child.java:262)

Caused by: java.lang.NumberFormatException: For input string: "1    a"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:458)
at java
java.io.IOException: Can't export data, please check task tracker logs
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:140)
at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:672)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:330)
at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
at org.apache.hadoop.mapred.Child.main(Child.java:262)

Caused by: java.lang.NumberFormatException: For input string: "1    a"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:458)
at java
java.io.IOException: Can't export data, please check task tracker logs
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:140)
at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:672)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:330)
at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at  org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
at org.apache.hadoop.mapred.Child.main(Child.java:262)

Caused by: java.lang.NumberFormatException: For input string: "1    a"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:458)
at java
Intercepting System.exit(1)
Failing Oozie Launcher, Main class [org.apache.oozie.action.hadoop.SqoopMain], exit code [1]
0pizxfdo

0pizxfdo1#

这可能是一个变压器在记录器,但仍然是值得一看:你有4个空间在“1A”。你查过hdfs里的文件了吗?列是否由\t分隔?

h9vpoimq

h9vpoimq2#

可能的原因是数据没有很好地插入到配置单元表中。您可以打印以下命令的结果吗?

hive -e "select id from foo;"

如果上述查询打印为

1   a
4   b

然后编辑输入文件,使其具有制表符或更改分隔符。

n53p2ov0

n53p2ov03#

使用oozie时不需要转义参数。必须删除所有转义序列及其周围的单引号和双引号。oozie工作流中的sqoop命令应如下所示:
导出--连接jdbc:mysql://www..net/test --username rio--password r005--table foo--export dir/user/cloudera/test--输入字段以\t结尾--输入行以\n结尾
在\t和\n之间不应该有任何单引号。

bq9c1y66

bq9c1y664#

原因:java.lang.numberformatexception:对于输入字符串:“1 a”
问题就在这里。更改mysql脚本并使用 mysql> CREATE TABLE( 身份证件 varchar(11) DEFAULT NULL, 城市 varchar(22) DEFAULT NULL ); 问题是数据中的第一列被推断为整数。这不是解决你问题的办法。我刚刚推荐了这种方法作为一种测试,以检查数字格式是否是您端的问题。请执行此操作并通知我们。我们将尽力进一步帮助你。
p、 s:很可能1和a之间的字符不是制表符分隔符。可能吧,可能吧。

相关问题