共计 4499 个字符，预计需要花费 12 分钟才能阅读完成。
Oozie 在执行 sqoop 的时候报错，同样的 SQL 在 sqoop 中可用，在 oozie 中不可用：
| Caused by: java.sql.SQLSyntaxErrorException: ORA-00918: 未明确定义列 | |
| at Oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) | |
| at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) | |
| at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) | |
| at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) | |
| at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) | |
| at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) | |
| at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) | |
| at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) | |
| at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) | |
| at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) | |
| at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) | |
| at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) | |
| at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) | |
| at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) | |
| at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111) | |
| at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235) | |
| ... 12 more | |
| 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:144) | |
| 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:164) | |
| 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:1693) | |
| at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158) |
原因，在使用 left join 的时候使用了别名。而 split-by 字段，没有指定别名。
| <action name="sqoop-jypt-pos-his-zz-node"> | |
| <sqoop xmlns="uri:oozie:sqoop-action:0.3"> | |
| <job-tracker>${jobTracker}</job-tracker> | |
| <name-node>${nameNode}</name-node> | |
| <prepare> | |
| <delete path="${wf:actionData('db-shell-node')['misPath']}${wf:actionData('db-shell-node')['time']}pos_his_zz/"/> | |
| </prepare> | |
| <configuration> | |
| <property> | |
| <name>mapred.compress.map.output</name> | |
| <value>true</value> | |
| </property> | |
| </configuration> | |
| <arg>import</arg> | |
| <arg>--connect</arg> | |
| <arg>${wf:actionData('db-shell-node')['mis_oracle']}</arg> | |
| <arg>--username</arg> | |
| <arg>${wf:actionData('db-shell-node')['mis_oracle_jypt_username']}</arg> | |
| <arg>--password</arg> | |
| <arg>${wf:actionData('db-shell-node')['mis_oracle_jypt_password']}</arg> | |
| <arg>--query</arg> | |
| <arg>select t.storecode,t.jysj,t.syjh,t.jyh,t.syyh,t.dsyyh,b.cid,t.jyje,t.jyjf,t.thyy,t.jysbm from pos_his_zz t left join sjpt.DA_MMC_CARD_TG b on t.cid||t.ccd = b.cardcode where t.optime >= to_date('${wf:actionData('db-shell-node')['sql_time']}', 'yyyy-mm-ddhh24:mi:ss') and t.optime <= to_date('${wf:actionData('db-shell-node')['sql_time']}', 'yyyy-mm-ddhh24:mi:ss')+1 and $CONDITIONS</arg> | |
| <arg>--split-by</arg> | |
| <arg>t.storecode</arg> | |
| <arg>--null-string</arg> | |
| <arg>\\N</arg> | |
| <arg>--null-non-string</arg> | |
| <arg>\\N</arg> | |
| <arg>--fields-terminated-by</arg> | |
| <arg>^</arg> | |
| <arg>--target-dir</arg> | |
| <arg>${wf:actionData('db-shell-node')['misPath']}${wf:actionData('db-shell-node')['time']}pos_his_zz/</arg> | |
| <arg>--mapreduce-job-name</arg> | |
| <arg>pos_his_zz_sqoop</arg> | |
| </sqoop> | |
| <ok to="end"/> | |
| <error to="fail"/> | |
| </action> |
至于为什么在 sqoop 中好使，这个有时间看看源码吧。
另外，Oozie 在使用时间字段进行 split-by 的时候也会报错！
下面关于 Oozie 的文章您也可能喜欢，不妨看看：
Oozie4.0.1 详细安装教程 http://www.linuxidc.com/Linux/2014-12/110456tm
指定 Oozie Java 节点的 Hadoop 属性 http://www.linuxidc.com/Linux/2014-06/103617.htm
Hadoop 平台上 Oozie 调度系统的安装配置 http://www.linuxidc.com/Linux/2014-04/100382.htm
Oozie 中运行 mapreduce node-action 时的常见异常解决方法 http://www.linuxidc.com/Linux/2014-02/96685.htm
Oozie web-console 时间本地化 http://www.linuxidc.com/Linux/2012-11/74797.htm
Hadoop Oozie 学习笔记 使用 Oozie, 通过命令行运行 example http://www.linuxidc.com/Linux/2012-08/67029.htm
Hadoop Oozie 学习笔记 自定义安装和启动 http://www.linuxidc.com/Linux/2012-08/67028.htm
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-11/137428.htm
正文完
星哥玩云-微信公众号
