运行环境

  • hive metastore
  • oozie bootstrap

    oozie自带示例

    ```bash $ cd oozie/ $ cp -r examples/apps/hive/ oozie-apps/ $ cat oozie-apps/hive/ job.properties script.q workflow.xml 
  1. <a name="OEap9"></a>
  2. ## job.properties

nameNode=hdfs://192.168.32.130:8020 jobTracker=192.168.32.130:8032 queueName=default examplesRoot=oozie-apps

oozie.use.system.libpath=true

oozie.wf.application.path=${nameNode}/user/${user.name}/${examplesRoot}/hive

  1. <a name="LOfqg"></a>
  2. ## workflow.xml
  3. 注意：
  5. - 输出、输入目录
  6. - <br />
  7. ```xml
  8. <workflow-app xmlns="uri:oozie:workflow:0.2" name="hive-wf">
  9.     <start to="hive-node"/>
  11.     <action name="hive-node">
  12.         <hive xmlns="uri:oozie:hive-action:0.2">
  13.             <job-tracker>${jobTracker}</job-tracker>
  14.             <name-node>${nameNode}</name-node>
  15.             <prepare>
  16.                 <delete path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data/hive"/>
  17.                 <mkdir path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data"/>
  18.             </prepare>
  19.             <configuration>
  20.                 <property>
  21.                     <name>mapred.job.queue.name</name>
  22.                     <value>${queueName}</value>
  23.                 </property>
  24.             </configuration>
  25.             <script>script.q</script>
  26.             <param>INPUT=/user/${wf:user()}/${examplesRoot}/input-data/table</param>
  27.             <param>OUTPUT=/user/${wf:user()}/${examplesRoot}/output-data/hive</param>
  28.         </hive>
  29.         <ok to="end"/>
  30.         <error to="fail"/>
  31.     </action>
  33.     <kill name="fail">
  34.         <message>Hive failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
  35.     </kill>
  36.     <end name="end"/>
  37. </workflow-app>

script.q

  • 后缀名“.q”、“.hql”等
  • 该脚本，创建外部表test，加载文件形式为textfile，文件load地址“${INPUT}”；
    查询（覆盖）输出到地址“${OUTPUT}”；
    1. CREATE EXTERNAL TABLE test (a INT) STORED AS TEXTFILE LOCATION '${INPUT}';
    2. INSERT OVERWRITE DIRECTORY '${OUTPUT}' SELECT * FROM test WHERE a > 2;
    3. -- original
    4. -- INSERT OVERWRITE DIRECTORY '${OUTPUT}' SELECT * FROM test;
    已显示区别，修改查询语句：“ SELECT FROM test *WHERE a > 2”。

    执行测试

    准备并上传测试数据
    1. $ mkdir -p oozie-apps/input-data/table
    2. $ echo 1 > oozie-apps/input-data/table/part-m-00000
    3. $ echo 2 >> oozie-apps/input-data/table/part-m-00000
    4. $ echo 3 >> oozie-apps/input-data/table/part-m-00000
    5. $ echo 4 >> oozie-apps/input-data/table/part-m-00000
    6. $ ~/Documents/hadoop/bin/hadoop fs -put -f oozie-apps/input-data/table oozie-apps/input-data/
    上传oozie任务并运行测试
    1. $ ~/Documents/hadoop/bin/hadoop fs -put -f oozie-apps/hive oozie-apps
    2. $ export OOZIE_URL="http://192.168.32.130:11000/oozie"
    3. $ bin/oozie job -config oozie-apps/hive/job.properties -run -verbose -debug
    4. $ bin/oozie job -info 0000068-200513181559469-oozie-jack-C
    5. # 查看结果
    6. $ ~/Documents/hadoop/bin/hadoop fs -cat /user/jack/oozie-apps/output-data/hive/*
    7. 3
    8. 4

    自定义hive表user分析

    1. $ cd oozie/
    2. $ cp -r oozie-apps/hive oozie-apps/hive-user
    3. $ ls oozie-apps/hive-user
    4. job.properties  script.q        workflow.xml

    job.properties

    ``` nameNode=hdfs://192.168.32.130:8020 jobTracker=192.168.32.130:8032 queueName=default examplesRoot=oozie-apps

oozie.use.system.libpath=true

oozie.wf.application.path=${nameNode}/user/${user.name}/${examplesRoot}/hive-user

  1. <a name="mNIdv"></a>
  2. ## script.q
  3. ```sql
  4. -- hive> desc user;
  5. -- OK
  6. -- id                      int                                         
  7. -- name                    string
  8. -- 查询所有name长度大于4的记录
  9. INSERT OVERWRITE DIRECTORY '${OUTPUT}' SELECT id FROM default.user WHERE length(name) > 4;

测试（修改 ${OUTPUT} 进行测试）

  1. $ cp ~/Documents/oozie/oozie-apps/hive-user/script.q ~/Documents/hive/hql
  3. #  ~/Documents/hive/bin/hive -e \
  4. # " \
  5. # INSERT OVERWRITE DIRECTORY '/user/jack/oozie-apps/output-data/hive-user' SELECT id FROM default.user WHERE length(name) > 4; \
  6. # "
  7. # ${OUTPUT} -> /user/jack/oozie-apps/output-data/hive-user
  8. $ ~/Documents/hive/bin/hive -f ~/Documents/hive/hql/script.q
  10. $ ~/Documents/hive/bin/hive
  11. hive> select * from user;                            
  12. OK
  13. 1       jack
  14. 2       tom
  15. 3       white
  16. 4       black
  17. 5       ming
  18. 6       ning
  19. 7       zhangsan
  20. 8       lisi
  21. 9       wangwu
  22. 10      zhaoliu
  23. 11      aaaaa
  24. 12      bbbbb

workflow.xml

  1. <workflow-app xmlns="uri:oozie:workflow:0.2" name="hive-wf">
  2.     <start to="hive-node"/>
  4.     <action name="hive-node">
  5.         <hive xmlns="uri:oozie:hive-action:0.2">
  6.             <job-tracker>${jobTracker}</job-tracker>
  7.             <name-node>${nameNode}</name-node>
  8.             <prepare>
  9.                 <delete path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data/hive-user"/>
  10.                 <mkdir path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data"/>
  11.             </prepare>
  12.             <configuration>
  13.                 <property>
  14.                     <name>mapred.job.queue.name</name>
  15.                     <value>${queueName}</value>
  16.                 </property>
  17.                 <property>
  18.                     <name>hive.metastore.uris</name>
  19.                     <value>thrift://192.168.32.130:9083</value>
  20.                 </property>
  21.             </configuration>
  22.             <script>script.q</script>
  23.             <param>OUTPUT=/user/${wf:user()}/${examplesRoot}/output-data/hive-user</param>
  24.         </hive>
  25.         <ok to="end"/>
  26.         <error to="fail-hive-node"/>
  27.     </action>
  29.     <kill name="fail">
  30.         <message>Hive failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
  31.     </kill>
  32.     <end name="end"/>
  33. </workflow-app>

执行测试

上传oozie任务并运行测试

  1. $ ~/Documents/hadoop/bin/hadoop fs -put -f oozie-apps/hive-user oozie-apps
  2. $ export OOZIE_URL="http://192.168.32.130:11000/oozie"
  3. $ bin/oozie job -config oozie-apps/hive-user/job.properties -run -verbose -debug
  4. $ bin/oozie job -info 0000101-200513181559469-oozie-jack-W
  5. Job ID : 0000101-200513181559469-oozie-jack-W
  6. ------------------------------------------------------------------------------------------------------------------------------------
  7. Workflow Name : hive-wf
  8. App Path      : hdfs://192.168.32.130:8020/user/jack/oozie-apps/hive-user
  9. Status        : SUCCEEDED
  10. Run           : 0
  11. User          : jack
  12. Group         : -
  13. Created       : 2020-05-18 06:16 GMT
  14. Started       : 2020-05-18 06:16 GMT
  15. Last Modified : 2020-05-18 06:17 GMT
  16. Ended         : 2020-05-18 06:17 GMT
  17. CoordAction ID: -
  19. Actions
  20. ------------------------------------------------------------------------------------------------------------------------------------
  21. ID                                                                            Status    Ext ID                 Ext Status Err Code  
  22. ------------------------------------------------------------------------------------------------------------------------------------
  23. 0000101-200513181559469-oozie-jack-W@:start:                                  OK        -                      OK         -         
  24. ------------------------------------------------------------------------------------------------------------------------------------
  25. 0000101-200513181559469-oozie-jack-W@hive-node                                OK        job_1588552715711_0206 SUCCEEDED  -         
  26. ------------------------------------------------------------------------------------------------------------------------------------
  27. 0000101-200513181559469-oozie-jack-W@end                                      OK        -                      OK         -         
  28. ------------------------------------------------------------------------------------------------------------------------------------
  29. # 查看结果
  30. $ ~/Documents/hadoop/bin/hadoop fs -cat /user/jack/oozie-apps/output-data/hive-user/*
  31. 3
  32. 4
  33. 7
  34. 9
  35. 10
  36. 11
  37. 12
  38. 13
  39. 14