Oozie调度报错——ORA-00918：未明确定义列

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 &gt;= to_date('${wf:actionData('db-shell-node')['sql_time']}', 'yyyy-mm-ddhh24:mi:ss')   and t.optime &lt;= 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的时候也会报错！

原因

在sqoop中有一个参数-m可以设置sqoop任务的并行度，默认是4。如果设置了多个并行度，sqoop就会根据split-by指定的参数拼接sql进行任务的切分。上面的例子中就没有设置-m参数，因此需要根据split-by指定的字段进行切分，拼接出来的sql由于没有设置别名而报错。但是之前在shell里面，-m别设置成了1，相当于忽略split-by参数，因此无论怎么写都不会报错。

巴特西

Oozie调度报错——ORA-00918：未明确定义列

原因

最新文章

热门文章