With Oozie, execute Sqoop action to import data from DB2 into the Hive partition table

test: with Oozie, execute Sqoop action to import data from DB2 into the Hive partition table.

Places to be aware of:

1, to add hive.metastore.uris this parameter. Otherwise, the data cannot be loaded into the hive table. Also, if there is more than one such operation in an XML literal, this parameter needs to be configured in each action.

2, be aware of the escape character problem in XML. here in my SQL, there is a less than sign that needs to be rewritten, otherwise the SQL statement will be said to be incorrect after execution.

3, because it is a partitioned table, according to the daily partition, so to get the system time from the system. There are no other executable methods found here:${replaceall (timestamp (), "t.{0,}", "")}

4, also pay attention to the version of xmlns issue. This time, try a few more just fine.

Workflow.xml

<?xml version= "1.0" encoding= "gb2312"?>

<workflow-app name= "workID1" xmlns= "uri:oozie:workflow:0.5" >

<start to= "step0101"/>

<action name= "step0101" >

<sqoop xmlns= "uri:oozie:sqoop-action:0.4" >

<configuration>

<property>

<name>Hive.metastore.uris</name>

<value>thrift://192.168.20.39:9083</value>

</property>

</configuration>

<arg>import</arg>

<arg>--connect</arg>

<arg>jdbc:db2://192.168.20.236:50001/sample</arg>

<arg>--username</arg>

<arg>db2inst1</arg>

<arg>--password</arg>

<arg>zheshimima</arg>

<arg>--query</arg>

<arg>select projno,projname,prstdate from Project where Prstdate &lt; Date (current date)-1 day and $CO Nditions</arg>

<arg>--fields-terminated-by</arg>

<arg> "\ T" </arg>

<arg>--hive-import</arg>

<arg>--target-dir</arg>

<arg>/qpf/target-dir/</arg>

<arg>--hive-table</arg>

<arg>ceshiku.tb_db2test</arg>

<arg>--null-string</arg>

<arg> "\\n" </arg>

<arg>--null-non-string</arg>

<arg> "\\n" </arg>

<arg>--hive-partition-key</arg>

<arg>shijian</arg>

<arg>--hive-partition-value</arg>

<arg>"${replaceall (timestamp ()," t.{0,} "," ")}"</arg>

<arg>-m</arg>

<arg>1</arg>

</sqoop>

<ok to= "End"/>

<error to= "Kill0101"/>

</action>

<kill name= "Kill0101" >

<message>action failed,step01 Error Message[${wf:errormessage (Wf:lasterrornode ())}]</message>

</kill>

<end name= "End"/>

</workflow-app>

With Oozie, execute Sqoop action to import data from DB2 into the Hive partition table