Running Sqoop via Oozie causes ERROR 42X05: Table/View 'DBS' does not exist

[#1]

Hello everyone,

I'm stuck with running sqoop job via Oozie.

I can run the same sqoop job via command line.

It fails via Oozie.

Please note that Hive metastore is on MySQL not in Derby.

HDP version: 2.6.4.0-91

Any help appreciated - thanks in advance!

Caused by: ERROR 42X05: Table/View 'DBS' does not exist.
	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
	at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
	at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
	at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
	at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
	at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
	at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
	at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
	at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
	at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
	... 98 more

Oozie workflow:

<workflow-app name="smoke-test"
	xmlns="uri:oozie:workflow:0.5">
	<global>
		<configuration>
			<property>
				<name>oozie.use.system.libpath</name>
				<value>true</value>
			</property>
			<property>
				<name>oozie.action.sharelib.for.sqoop</name>
				<value>hive,hive2,hcatalog,sqoop</value>
			</property>
			<property>
				<name>oozie.libpath</name>
				<value>/user/oozie/share/lib</value>
			</property>
		</configuration>
	</global>
	<action name="sqoop_1">
		<sqoop
			xmlns="uri:oozie:sqoop-action:0.4">
			<job-tracker>${resourceManager}</job-tracker>
			<name-node>${nameNode}</name-node>
			<arg>import</arg>
			<arg>--connect</arg>
			<arg>jdbc:sqlserver://dbserver:1433</arg>
			<arg>--username</arg>
			<arg>user</arg>
			<arg>--password-file</arg>
			<arg>/user/xx/xx.pwd</arg>
			<arg>--hcatalog-database</arg>
			<arg>smoketest</arg>
			<arg>--hcatalog-table</arg>
			<arg>table-name</arg>
			<arg>--hcatalog-storage-stanza</arg>
			<arg>stored as orcfile</arg>
			<arg>--split-by</arg>
			<arg>id</arg>
			<arg>--query</arg>
			<arg>select field from table WHERE $CONDITIONS</arg>
			<arg>--m</arg>
			<arg>1</arg>
			<arg>--verbose</arg>
		</sqoop>
		<ok to="end"/>
		<error to="kill"/>
	</action>
	<kill name="kill">
		<message>${wf:errorMessage(wf:lastErrorNode())}</message>
	</kill>
	<end name="end"/>
</workflow-app>

[#2]

@Artur Bukowski

Did you set this in the oozie user's .profile or bashrc

HADOOP_CLASSPATH=$(hcat -classpath) 
export HADOOP_CLASSPATH

Can you attach the new error stack.

[#3]

Hi Geoffrey,

Thanks for answering!

To be precise, I did this for all users:

[root@server ~]# cat /etc/profile.d/hadoop-env.sh
export HADOOP_CLASSPATH=$(hcat -classpath)

I also double checked that oozie user has HADOOP_CLASSPATH defined and set (lot of jars).

Unfortunately, error stack is the same.

[#4]

Hi,

did you run the sqoop command on command line? 

like:

sqoop export .... --table xpto  --hcatalog-home /opt/cloudera/parcels/CDH/lib/hive-hcatalog/ --hcatalog-database default --hcatalog-table tout_table_xpto --verbose

Does it work?

I'm also facing the same error on oozie wf and sqoop in hue. The hive metastore is working just fine the somehow oozie/sqoop can not fund the right hive metastore info