sqoop导入hsqldb数据示例

  1. $ cp -r examples/apps/sqoop* oozie-apps/
  2. $ ls oozie-apps/sqoop*
  3. oozie-apps/sqoop:
  4. db.hsqldb.properties  db.hsqldb.script  job.properties  workflow.xml
  6. oozie-apps/sqoop-freeform:
  7. db.hsqldb.properties  db.hsqldb.script  job.properties  workflow.xml

使用sqoop-freeform示例执行。

job.properties

  1. nameNode=hdfs://192.168.32.130:8020
  2. jobTracker=192.168.32.130:8032
  3. queueName=default
  4. examplesRoot=oozie-apps
  6. oozie.use.system.libpath=true
  8. oozie.wf.application.path=${nameNode}/user/${user.name}/${examplesRoot}/sqoop-freeform

workflow.xml

  2. <?xml version="1.0" encoding="UTF-8"?>
  3. <!--
  4.   Licensed to the Apache Software Foundation (ASF) under one
  5.   or more contributor license agreements.  See the NOTICE file
  6.   distributed with this work for additional information
  7.   regarding copyright ownership.  The ASF licenses this file
  8.   to you under the Apache License, Version 2.0 (the
  9.   "License"); you may not use this file except in compliance
  10.   with the License.  You may obtain a copy of the License at
  12.        http://www.apache.org/licenses/LICENSE-2.0
  14.   Unless required by applicable law or agreed to in writing, software
  15.   distributed under the License is distributed on an "AS IS" BASIS,
  16.   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  17.   See the License for the specific language governing permissions and
  18.   limitations under the License.
  19. -->
  20. <workflow-app xmlns="uri:oozie:workflow:0.2" name="sqoop-freeform-wf">
  21.     <start to="sqoop-freeform-node"/>
  23.     <action name="sqoop-freeform-node">
  24.         <sqoop xmlns="uri:oozie:sqoop-action:0.2">
  25.             <job-tracker>${jobTracker}</job-tracker>
  26.             <name-node>${nameNode}</name-node>
  27.             <prepare>
  28.                 <delete path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data/sqoop-freeform"/>
  29.                 <mkdir path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data"/>
  30.             </prepare>
  31.             <configuration>
  32.                 <property>
  33.                     <name>mapred.job.queue.name</name>
  34.                     <value>${queueName}</value>
  35.                 </property>
  36.             </configuration>
  37.             <arg>import</arg>
  38.             <arg>--connect</arg>
  39.             <arg>jdbc:hsqldb:file:db.hsqldb</arg>
  40.             <arg>--username</arg>
  41.             <arg>sa</arg>
  42.             <arg>--password</arg>
  43.             <arg></arg>
  44.             <arg>--verbose</arg>
  45.             <arg>--query</arg>
  46.             <arg>select TT.I, TT.S from TT where $CONDITIONS</arg>
  47.             <arg>--target-dir</arg>
  48.             <arg>/user/${wf:user()}/${examplesRoot}/output-data/sqoop-freeform</arg>
  49.             <arg>-m</arg>
  50.             <arg>1</arg>
  51.             <file>db.hsqldb.properties#db.hsqldb.properties</file>
  52.             <file>db.hsqldb.script#db.hsqldb.script</file>
  53.         </sqoop>
  54.         <ok to="end"/>
  55.         <error to="fail"/>
  56.     </action>
  58.     <kill name="fail">
  59.         <message>Sqoop free form failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
  60.     </kill>
  61.     <end name="end"/>
  62. </workflow-app>

db.hsqldb.properties

  1. #HSQL Database Engine 1.8.0.5
  2. #Tue Oct 05 11:20:19 SGT 2010
  3. hsqldb.script_format=0
  4. runtime.gc_interval=0
  5. sql.enforce_strict_size=false
  6. hsqldb.cache_size_scale=8
  7. readonly=false
  8. hsqldb.nio_data_file=true
  9. hsqldb.cache_scale=14
  10. version=1.8.0
  11. hsqldb.default_table_type=memory
  12. hsqldb.cache_file_scale=1
  13. hsqldb.log_size=200
  14. modified=no
  15. hsqldb.cache_version=1.7.0
  16. hsqldb.original_version=1.8.0
  17. hsqldb.compatible_version=1.8.0

db.hsqldb.script

  1. CREATE SCHEMA PUBLIC AUTHORIZATION DBA
  2. CREATE MEMORY TABLE TT(I INTEGER NOT NULL PRIMARY KEY,S VARCHAR(256))
  3. CREATE USER SA PASSWORD ""
  4. GRANT DBA TO SA
  5. SET WRITE_DELAY 10
  6. SET SCHEMA PUBLIC
  7. INSERT INTO TT VALUES(1,'a')
  8. INSERT INTO TT VALUES(2,'a')
  9. INSERT INTO TT VALUES(3,'a')

执行

  1. $ # 上传任务
  2. $ ~/Documents/hadoop/bin/hadoop fs -put oozie-apps/sqoop-freeform/ oozie-apps
  4. $ # 查看目录信息
  5. $ ~/Documents/hadoop/bin/hadoop fs -ls /user/jack/oozie-apps/sqoop-freeform
  6. Found 2 items
  7. Found 4 items
  8. -rw-r--r--   1 jack supergroup       1208 2020-05-13 18:12 /user/jack/oozie-apps/sqoop-freeform/db.hsqldb.properties
  9. -rw-r--r--   1 jack supergroup       1081 2020-05-13 18:12 /user/jack/oozie-apps/sqoop-freeform/db.hsqldb.script
  10. -rw-r--r--   1 jack supergroup       1018 2020-05-13 18:12 /user/jack/oozie-apps/sqoop-freeform/job.properties
  11. -rw-r--r--   1 jack supergroup       2508 2020-05-13 18:12 /user/jack/oozie-apps/sqoop-freeform/workflow.xml
  12. $ # 执行工作流任务
  13. $ bin/oozied.sh start
  14. $ export OOZIE_URL="http://192.168.32.130:11000/oozie"
  15. $ bin/oozie job -config oozie-apps/sqoop-freeform/job.properties -run
  16. job: 0000000-200513181559469-oozie-jack-W
  18. $ # 检查工作流任务状态，也可以通过Oozie web控制台检查工作流作业状态，http://localhost:11000/oozie
  19. $ bin/oozie job -info 0000000-200513181559469-oozie-jack-W
  20. Job ID : 0000000-200513181559469-oozie-jack-W
  21. ------------------------------------------------------------------------------------------------------------------------------------
  22. Workflow Name : sqoop-freeform-wf
  23. App Path      : hdfs://192.168.32.130:8020/user/jack/oozie-apps/sqoop-freeform
  24. Status        : RUNNING
  25. Run           : 0
  26. User          : jack
  27. Group         : -
  28. Created       : 2020-05-14 01:17 GMT
  29. Started       : 2020-05-14 01:17 GMT
  30. Last Modified : 2020-05-14 01:17 GMT
  31. Ended         : -
  32. CoordAction ID: -
  34. Actions
  35. ------------------------------------------------------------------------------------------------------------------------------------
  36. ID                                                                            Status    Ext ID                 Ext Status Err Code  
  37. ------------------------------------------------------------------------------------------------------------------------------------
  38. 0000000-200513181559469-oozie-jack-W@:start:                                  OK        -                      OK         -         
  39. ------------------------------------------------------------------------------------------------------------------------------------
  40. 0000000-200513181559469-oozie-jack-W@sqoop-freeform-node                      RUNNING   job_1588552715711_0025 RUNNING    -         
  41. ------------------------------------------------------------------------------------------------------------------------------------
  43. ··· ···
  45. Actions
  46. ------------------------------------------------------------------------------------------------------------------------------------
  47. ID                                                                            Status    Ext ID                 Ext Status Err Code  
  48. ------------------------------------------------------------------------------------------------------------------------------------
  49. 0000000-200513181559469-oozie-jack-W@:start:                                  OK        -                      OK         -         
  50. ------------------------------------------------------------------------------------------------------------------------------------
  51. 0000000-200513181559469-oozie-jack-W@sqoop-freeform-node                      OK        job_1588552715711_0025 SUCCEEDED  -         
  52. ------------------------------------------------------------------------------------------------------------------------------------
  53. 0000000-200513181559469-oozie-jack-W@end                                      OK        -                      OK         -         
  54. ------------------------------------------------------------------------------------------------------------------------------------
  56. $ # 查看输出内容
  57. $ ~/Documents/hadoop/bin/hadoop fs -ls /user/jack/oozie-apps/output-data/sqoop-freeform
  58. Found 2 items
  59. -rw-r--r--   1 jack supergroup          0 2020-05-13 18:18 /user/jack/oozie-apps/output-data/sqoop-freeform/_SUCCESS
  60. -rw-r--r--   1 jack supergroup         12 2020-05-13 18:18 /user/jack/oozie-apps/output-data/sqoop-freeform/part-m-00000
  61. $ ~/Documents/hadoop/bin/hadoop fs -cat /user/jack/oozie-apps/output-data/sqoop-freeform/part-m-00000
  62. 1,a
  63. 2,a
  64. 3,a

sqoop导入oracle数据

  1. $ cp -r oozie-apps/sqoop-freeform/ oozie-apps/sqoop-freeform-oracle
  2. $ rm -rf oozie-apps/sqoop-freeform-oracle/db.hsqldb.*

job.properties

  1. nameNode=hdfs://192.168.32.130:8020
  2. jobTracker=192.168.32.130:8032
  3. queueName=default
  4. examplesRoot=oozie-apps
  6. oozie.use.system.libpath=true
  8. oozie.wf.application.path=${nameNode}/user/${user.name}/${examplesRoot}/sqoop-freeform-oracle

workflow.xml

  2. <?xml version="1.0" encoding="UTF-8"?>
  3. <!--
  4.   Licensed to the Apache Software Foundation (ASF) under one
  5.   or more contributor license agreements.  See the NOTICE file
  6.   distributed with this work for additional information
  7.   regarding copyright ownership.  The ASF licenses this file
  8.   to you under the Apache License, Version 2.0 (the
  9.   "License"); you may not use this file except in compliance
  10.   with the License.  You may obtain a copy of the License at
  12.        http://www.apache.org/licenses/LICENSE-2.0
  14.   Unless required by applicable law or agreed to in writing, software
  15.   distributed under the License is distributed on an "AS IS" BASIS,
  16.   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  17.   See the License for the specific language governing permissions and
  18.   limitations under the License.
  19. -->
  20. <workflow-app xmlns="uri:oozie:workflow:0.2" name="sqoop-freeform-wf">
  21.     <start to="sqoop-freeform-node"/>
  23.     <action name="sqoop-freeform-node">
  24.         <sqoop xmlns="uri:oozie:sqoop-action:0.2">
  25.             <job-tracker>${jobTracker}</job-tracker>
  26.             <name-node>${nameNode}</name-node>
  27.             <prepare>
  28.                 <delete path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data/sqoop-freeform-oracle/h_log_lotbase"/>
  29.                 <mkdir path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data/sqoop-freeform-oracle/"/>
  30.             </prepare>
  31.             <configuration>
  32.                 <property>
  33.                     <name>mapred.job.queue.name</name>
  34.                     <value>${queueName}</value>
  35.                 </property>
  36.             </configuration>
  37.             <arg>import</arg>
  38.             <arg>--connect</arg>
  39.             <arg>jdbc:oracle:thin:@//192.168.1.38:1521/CMASPROD</arg>
  40.             <arg>--username</arg>
  41.             <arg>mes_bc</arg>
  42.             <arg>--password</arg>
  43.             <arg>mes_bc_real</arg>
  44.             <arg>--table</arg>
  45.             <arg>H_LOG_LOTBASE</arg>
  46.             <arg>--where</arg>
  47.             <arg><![CDATA[complete_time < to_date('2016:3:12 00:00:00', 'yyyy-mm-dd HH24:MI:SS')]]></arg>
  48.             <arg>--delete-target-dir</arg>
  49.             <arg>--target-dir</arg>
  50.             <arg>/user/${wf:user()}/${examplesRoot}/output-data/sqoop-freeform-oracle/h_log_lotbase</arg>
  51.             <arg>-m</arg>
  52.             <arg>1</arg>
  53.             <archive>ojdbc7.jar</archive>
  54.         </sqoop>
  55.         <ok to="end"/>
  56.         <error to="fail"/>
  57.     </action>
  59.     <kill name="fail">
  60.         <message>Sqoop free form failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
  61.     </kill>
  62.     <end name="end"/>
  63. </workflow-app>

执行

  1. $ # 上传任务 -f 覆盖
  2. $ ~/Documents/hadoop/bin/hadoop fs -put oozie-apps/sqoop-freeform-oracle/ oozie-apps
  3. $ # 查看目录信息
  4. $ ~/Documents/hadoop/bin/hadoop fs -ls /user/jack/oozie-apps/sqoop-freeform-oracle
  5. Found 2 items
  6. -rw-r--r--   1 jack supergroup       1025 2020-05-13 18:56 /user/jack/oozie-apps/sqoop-freeform-oracle/job.properties
  7. -rw-r--r--   1 jack supergroup       2503 2020-05-13 18:56 /user/jack/oozie-apps/sqoop-freeform-oracle/workflow.xml
  8. $ bin/oozied.sh start
  9. $ export OOZIE_URL="http://192.168.32.130:11000/oozie"
  10. $ bin/oozie job -config oozie-apps/sqoop-freeform-oracle/job.properties -run
  11. job: 0000015-200513181559469-oozie-jack-W
  12. $ bin/oozie job -info 0000015-200513181559469-oozie-jack-W
  13. Job ID : 0000015-200513181559469-oozie-jack-W
  14. ------------------------------------------------------------------------------------------------------------------------------------
  15. Workflow Name : sqoop-freeform-wf
  16. App Path      : hdfs://192.168.32.130:8020/user/jack/oozie-apps/sqoop-freeform-oracle
  17. Status        : RUNNING
  18. Run           : 0
  19. User          : jack
  20. Group         : -
  21. Created       : 2020-05-14 05:23 GMT
  22. Started       : 2020-05-14 05:23 GMT
  23. Last Modified : 2020-05-14 05:23 GMT
  24. Ended         : -
  25. CoordAction ID: -
  27. Actions
  28. ------------------------------------------------------------------------------------------------------------------------------------
  29. ID                                                                            Status    Ext ID                 Ext Status Err Code  
  30. ------------------------------------------------------------------------------------------------------------------------------------
  31. 0000015-200513181559469-oozie-jack-W@:start:                                  OK        -                      OK         -         
  32. ------------------------------------------------------------------------------------------------------------------------------------
  33. 0000015-200513181559469-oozie-jack-W@sqoop-freeform-node                      RUNNING   job_1588552715711_0046 RUNNING    -         
  34. ------------------------------------------------------------------------------------------------------------------------------------
  36. ··· ···
  38. ------------------------------------------------------------------------------------------------------------------------------------
  39. ID                                                                            Status    Ext ID                 Ext Status Err Code  
  40. ------------------------------------------------------------------------------------------------------------------------------------
  41. 0000015-200513181559469-oozie-jack-W@:start:                                  OK        -                      OK         -         
  42. ------------------------------------------------------------------------------------------------------------------------------------
  43. 0000015-200513181559469-oozie-jack-W@sqoop-freeform-node                      OK        job_1588552715711_0046 SUCCEEDED  -         
  44. ------------------------------------------------------------------------------------------------------------------------------------
  45. 0000015-200513181559469-oozie-jack-W@end                                      OK        -                      OK         -         
  46. ------------------------------------------------------------------------------------------------------------------------------------
  48. # 默认逗号分隔field
  49. $ ~/Documents/hadoop/bin/hadoop fs -cat \
  50. oozie-apps/output-data/sqoop-freeform-oracle/h_log_lotbase/*
  51. 914b7ee3-20c0-4289-8ca2-b883dad9b73a,2016-03-11 09:13:22.0,COMPLETE,11,3,2016,11,null,null,L216-1603040030,L216-1603040030,2310215012240,2,2016-03-11 09:13:22.0,A1301005,MACPRO_NORMAL_01,ROUTE_SET,ROUTE_SET-B01-01,FALSE,216-1600289,null,2,2,0,0,MACPRO_NORMAL_01,MACPRO_NORMAL_01,ROUTE_SET,PROGRAM,INITIAL,WAIT,null,2016-03-11 09:13:22.0,2016-03-11 09:13:22.0,null,RELEASED,ACTIVE,PCS,2016-03-21 00:00:00.0,null,null,null,null,null,null,null,null
  52. 5667f1e5-999e-4b6a-9021-653320e28349,2016-03-11 09:13:24.0,COMPLETE,11,3,2016,11,null,null,L216-1603040035,L216-1603040035,2310215082321,2,2016-03-11 09:13:24.0,A1301005,MACPRO_NORMAL_01,ROUTE_SET,ROUTE_SET-B01-01,FALSE,216-1600289,null,2,2,0,0,MACPRO_NORMAL_01,MACPRO_NORMAL_01,ROUTE_SET,PROGRAM,INITIAL,WAIT,null,2016-03-11 09:13:24.0,2016-03-11 09:13:24.0,null,RELEASED,ACTIVE,PCS,2016-03-21 00:00:00.0,null,null,null,null,null,null,null,null

sqoop导入mysql数据

job.properties

  1. nameNode=hdfs://192.168.32.130:8020
  2. jobTracker=192.168.32.130:8032
  3. queueName=default
  4. examplesRoot=oozie-apps
  6. oozie.use.system.libpath=true
  8. oozie.wf.application.path=${nameNode}/user/${user.name}/${examplesRoot}/sqoop-freeform-mysql

workflow.xml

  2. <?xml version="1.0" encoding="UTF-8"?>
  3. <!--
  4.   Licensed to the Apache Software Foundation (ASF) under one
  5.   or more contributor license agreements.  See the NOTICE file
  6.   distributed with this work for additional information
  7.   regarding copyright ownership.  The ASF licenses this file
  8.   to you under the Apache License, Version 2.0 (the
  9.   "License"); you may not use this file except in compliance
  10.   with the License.  You may obtain a copy of the License at
  12.        http://www.apache.org/licenses/LICENSE-2.0
  14.   Unless required by applicable law or agreed to in writing, software
  15.   distributed under the License is distributed on an "AS IS" BASIS,
  16.   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  17.   See the License for the specific language governing permissions and
  18.   limitations under the License.
  19. -->
  20. <workflow-app xmlns="uri:oozie:workflow:0.2" name="sqoop-freeform-wf">
  21.     <start to="sqoop-freeform-node"/>
  23.     <action name="sqoop-freeform-node">
  24.         <sqoop xmlns="uri:oozie:sqoop-action:0.2">
  25.             <job-tracker>${jobTracker}</job-tracker>
  26.             <name-node>${nameNode}</name-node>
  27.             <prepare>
  28.                 <delete path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data/sqoop-freeform-mysql/roles"/>
  29.                 <mkdir path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data/sqoop-freeform-mysql/"/>
  30.             </prepare>            
  31.             <configuration>
  32.                 <property>
  33.                     <name>mapred.job.queue.name</name>
  34.                     <value>${queueName}</value>
  35.                 </property>
  36.             </configuration>
  37.             <arg>import</arg>
  38.             <arg>--connect</arg>
  39.             <arg>jdbc:mysql://192.168.32.130:3306/hive</arg>
  40.             <arg>--username</arg>
  41.             <arg>root</arg>
  42.             <arg>--password</arg>
  43.             <arg>123456</arg>
  44.             <arg>--table</arg>
  45.             <arg>ROLES</arg>
  46.             <arg>--delete-target-dir</arg>
  47.             <arg>--target-dir</arg>
  48.             <arg>/user/${wf:user()}/${examplesRoot}/output-data/sqoop-freeform-mysql/roles</arg>
  49.             <arg>-m</arg>
  50.             <arg>1</arg>
  51.             <archive>mysql-connector-java-5.1.27-bin.jar</archive>
  52.         </sqoop>
  53.         <ok to="end"/>
  54.         <error to="fail"/>
  55.     </action>
  57.     <kill name="fail">
  58.         <message>Sqoop free form failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
  59.     </kill>
  60.     <end name="end"/>
  61. </workflow-app>

执行

  2. $ bin/oozied.sh start
  3. $ ~/Documents/hadoop/bin/hadoop fs -put -f \
  4. oozie-apps/sqoop-freeform-mysql/ oozie-apps
  5. $ bin/oozie job -config oozie-apps/sqoop-freeform-mysql/job.properties -run
  6. $ bin/oozie job -info 0000013-200513181559469-oozie-jack-W
  7. $ ~/Documents/hadoop/bin/hadoop fs -cat \
  8. /user/jack/oozie-apps/output-data/sqoop-freeform-mysql/roles/*
  9. 20/05/13 22:14:47 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
  10. 1,1585619269,admin,admin
  11. 2,1585619269,public,public

参考

启动Sqoop失败的问题

“Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.SqoopMain], exit code [1]”，引发的原因，Sqoop无法加载访问数据库所需的数据库驱动程序。