Oozie Example: Sqoop Actions

appPath

[ambari-qa@ip-10-0-0-51 sqoop-oracle]$ hadoop fs -lsr /user/ambari-qa/example/sqoop-oracle
-rwx------   3 ambari-qa hdfs  335 2013-05-08 20:41 /user/ambari-qa/example/sqoop-oracle/option.par
-rwx------   3 ambari-qa hdfs  1770 2013-05-08 20:39 /user/ambari-qa/example/sqoop-oracle/workflow.xml

argExample.xml

<arg>import</arg>
<arg>--connect</arg>
<arg>jdbc:oracle:thin:@//10.0.0.24:1521/test</arg>
<arg>--table</arg>
<arg>EMPLOYEE_REPORTING.EMP_RECORD</arg>
<arg>--export-dir</arg>
<arg>/user/ambari-qa/example/output/sqoop/emprecord</arg>
<arg>-m</arg>
<arg>6</arg>
....
....
....

common-sqoop-issue.log

13/09/30 21:22:12 INFO tool.CodeGenTool: Beginning code generation
13/09/30 21:22:12 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: Could not load db driver class: oracle.jdbc.driver.OracleDriver
java.lang.RuntimeException: Could not load db driver class: oracle.jdbc.driver.OracleDriver
       at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:716)
       at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
       at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:605)
       at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:628)
       at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:235)
.....................
.....................
.....................

issue-sol.sh

su -l oozie -c "hadoop fs -put /usr/lib/sqoop/lib/ojdbc6.jar /user/oozie/share/lib/sqoop"
su -l oozie -c "hadoop fs -chmod a+xr /user/oozie/share/lib/sqoop/ojdbc6.jar"

job.properties

nameNode=hdfs://ip-10-0-0-52:8020
jobTracker=ip-10-0-0-53:50300
queueName=default
exampleRoot=example

oozie.use.system.libpath=true
oozie.libpath=/user/oozie/share/lib

optionFile=option.par

oozie.wf.application.path=${nameNode}/user/${user.name}/${exampleRoot}/sqoop-oracle

option.par

--connect
jdbc:oracle:thin:@//10.0.0.24:1521/test
--username
username1
--password
password1
--table
EMPLOYEE_REPORTING.EMP_RECORD 
--export-dir
/user/ambari-qa/example/output/sqoop/emprecord
--input-fields-terminated-by
"\t"
--input-lines-terminated-by
"\n"
--verbose
-m
8
--input-null-string
'\\N'
--input-null-non-string
'\\N'

sqoop-cmd

[ambari-qa@ip-10-0-0-51 ~]$ sqoop export --options-file option.par

sqoop-success

03/05/13 20:34:42 INFO mapreduce.ExportJobBase: Transferred 4.4219 KB in 30.4807 seconds (81.3631 bytes/sec)
03/05/13 20:34:42 INFO mapreduce.ExportJobBase: Exported 2 records.

workflow.xml

<?xml version="1.0" encoding="UTF-8"?>
<workflow-app xmlns='uri:oozie:workflow:0.4' name='sqoop-workflow'>
    <start to='sqoop-load' />
    
    <action name="sqoop-load">
        <sqoop xmlns="uri:oozie:sqoop-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
            <configuration>
                <property>
                    <name>mapred.job.queue.name</name>
                    <value>${queueName}</value>
                </property>
            </configuration>
            <arg>export</arg>
            <arg>--options-file</arg>
            <arg>${optionFile}</arg>
            <file>${optionFile}</file>
        </sqoop>
        <ok to="end"/>
        <error to="sqoop-load-fail"/>
    </action>
    <kill name="sqoop-load-fail">
        <message>Sqoop export failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
  
    <end name='end' />
</workflow-app>