Oozie企业级项目实战

ゝ一世哀愁。 2022-12-19 13:29 267阅读 0赞

### 目录 ###

*   *  一、需求分析
     *  二、实施方案
     *   *  （1）方案一
         *  （2）方案二

## 一、需求分析 ##

根据前面学习，我们做了基于Sqoop+Hive+MySQL网站日志分析的项目实战，上次是通过shell脚本没有使用Oozie调度，那么这次我们使用Oozie工作流调度框架将Sqoop+Hive+MySQL串联起来。

由于本次项目是在上次项目的基础上完成的，需要参考上次项目流程：  
[https://blog.csdn.net/weixin\_45366499/article/details/109444046][https_blog.csdn.net_weixin_45366499_article_details_109444046]

项目需求
    
    1.数据加载  -> shell action
    
    
    2.hive数据分析  -> hive action
    
    
    3.sqoop数据导出  -> sqoop action

## 二、实施方案 ##

实施步骤：  
1.创建project目录，包括文件如下：

coordinator.xml  job.properties  lib  workflow.xml

2.将mysql jar包拷贝到lib目录下

cp ../../hive-select/lib/mysql-connector-java-5.1.48-bin.jar .

3.将上一章编写shell脚本全部拷贝到project目录下

scp daily_hour_visit.sh export_visit.txt hive_visit.sql load_track_log.sh /opt/modules/oozie/oozie-apps/project/

4.将hive-site.xml文件拷贝到project目录下

cp hive-site.xml ../project/

### （1）方案一 ###

根据需求，workflow的开发我们实现了两个方案：

第一个方案：将时间写死，不能动态获取前一天的时间

2-1.数据加载load data  ->  shell action
    
       load_track_log.sh
    
    
    2-2.hive数据分析       -> hive action
    
        hive-visi.sql
    
    2-3.sqoop数据导出      -> sqoop action
    
        export_visit.txt

这里我只测试了workflow，没有测试coordinator，编写job.properties文件，内容如下：

nameNode=hdfs://bigdata-pro-m01:9000
    jobTracker=bigdata-pro-m01:8032
    
    queueName=default
    oozieAppRoot=user/caizhengjie/oozie-apps
    oozieDataRoot=user/caizhengjie/oozie-datas
    
    
    script=load_track_log.sh
    yesterday=20201106
    EXEC=export_visit.txt
    
    oozie.use.system.libpath=true
    
    # oozie.coord.application.path=${nameNode}/${oozieAppRoot}/project
    start=2010-01-01T22:00+0800
    end=2010-01-01T23:00+0800
    # workflowAppUri=${nameNode}/${oozieAppRoot}/project
    
    oozie.wf.application.path=${nameNode}/${oozieAppRoot}/project

编写workflow.xml文件，内容如下：

<workflow-app xmlns="uri:oozie:workflow:0.5" name="kfk-project">
    
      
        <start to="shell-node"/>
        <action name="shell-node">
            <shell xmlns="uri:oozie:shell-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>
                <exec>${script}</exec>
                <file>${nameNode}/${oozieAppRoot}/project/${script}#${script}</file> 
    
            </shell>
            <ok to="hive-node"/>
            <error to="shell-fail"/>
        </action>
    
        <kill name="shell-fail">
            <message>Incorrect output, expected [Hello Oozie] but was [${ wf:actionData('shell-node')['my_output']}]</message>
        </kill>
    
      
        <action name="hive-node">
            <hive xmlns="uri:oozie:hive-action:0.6">
                <job-tracker>${jobTracker}</job-tracker>
                <name-node>${nameNode}</name-node>
    
                <job-xml>${nameNode}/${oozieAppRoot}/project/hive-site.xml</job-xml>
                <configuration>
                    <property>
                        <name>mapred.job.queue.name</name>
                        <value>${queueName}</value>
                    </property>
                </configuration>
                <script>hive_visit.sql</script>
                
                <param>YESTERDAY=${yesterday}</param>
            </hive>
            <ok to="sqoop-node"/>
            <error to="hive-fail"/>
        </action>
    
        <kill name="hive-fail">
            <message>Hive failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
        </kill>
    
      
    
        <action name="sqoop-node">
            <sqoop xmlns="uri:oozie:sqoop-action:0.3">
                <job-tracker>${jobTracker}</job-tracker>
                <name-node>${nameNode}</name-node>
    
                <configuration>
                    <property>
                        <name>mapred.job.queue.name</name>
                        <value>${queueName}</value>
                    </property>
                </configuration>
    
                <command>export --options-file ${EXEC}</command>
                <file>${nameNode}/${oozieAppRoot}/project/${EXEC}#${EXEC}</file>
                
            </sqoop>
            <ok to="end"/>
            <error to="sqoop-fail"/>
        </action>
    
        <kill name="sqoop-fail">
            <message>Sqoop failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
        </kill>
    
    
        <end name="end"/>
    
      
    </workflow-app>

编写load\_track\_log.sh文件，内容如下：

#! /bin/bash
    
    ## track log dir path
    LOD_DIR=/opt/track/track_log
    
    ## hive home
    HIVE_HOME=/opt/modules/hive
    
    yesterday=`date -d "1 day ago" +"%Y%m%d"`
    
    
    cd $LOD_DIR
    
    for line in `ls $yesterday`;
        do
            date=${line:0:4}${line:4:2}${line:6:2}
            hour=${line:8:2}
            $HIVE_HOME/bin/hive -e "load data local inpath '$LOD_DIR/$yesterday/$line' overwrite into table track.track_log partition(date='$date',hour='$hour');"
    
        done

编写hive\_visit.sql文件，内容如下：

truncate table track.daily_hour_visit;
    
    insert into table  track.daily_hour_visit 
    select  date,hour,count(url) pv, count(distinct guid) uv from track.track_log 
    where date='${YESTERDAY}' group by date,hour;

编写export\_visit.txt文件，内容如下：

--connect 
    jdbc:mysql://bigdata-pro-m01:3306/db_track 
    --username 
    root 
    --password 
    199911 
    --num-mappers 
    1
    --table 
    daily_hour_visit 
    --input-fields-terminated-by 
    '\t' 
    --export-dir 
    /user/hive/warehouse/track.db/daily_hour_visit

将project项目上传到HDFS

bin/hdfs dfs -put /opt/modules/oozie/oozie-apps/project /user/caizhengjie/oozie-apps

启动测试：

bin/oozie job -oozie http://bigdata-pro-m01:11000/oozie -config oozie-apps/project/job.properties -run

运行结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center]

### （2）方案二 ###

方案二：可以动态获取前一天的时间

2-1.数据加载load data  ->  shell action
    
         load_track_log.sh
    
    
     2-2.hive数据分析       -> shell action
    
         daily_hour_visit.sh +  hive-visi.sql
    
     2-3.sqoop数据导出      -> sqoop action
    
         export_visit.txt

这里我只测试了workflow，没有测试coordinator，编写job.properties文件，内容如下：

nameNode=hdfs://bigdata-pro-m01:9000
    jobTracker=bigdata-pro-m01:8032
    
    queueName=default
    oozieAppRoot=user/caizhengjie/oozie-apps
    oozieDataRoot=user/caizhengjie/oozie-datas
    
    
    script=load_track_log.sh
    # yesterday=20201106
    EXEC=export_visit.txt
    
    
    SQL=hive_visit.sql
    shellHive=daily_hour_visit.sh
    
    oozie.use.system.libpath=true
    
    # oozie.coord.application.path=${nameNode}/${oozieAppRoot}/project
    start=2010-01-01T22:00+0800
    end=2010-01-01T23:00+0800
    # workflowAppUri=${nameNode}/${oozieAppRoot}/project
    
    oozie.wf.application.path=${nameNode}/${oozieAppRoot}/project

编写workflow.xml文件，内容如下：

<workflow-app xmlns="uri:oozie:workflow:0.5" name="kfk-project">
    
      
        <start to="shell-node"/>
        <action name="shell-node">
            <shell xmlns="uri:oozie:shell-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>
                <exec>${script}</exec>
                <file>${nameNode}/${oozieAppRoot}/project/${script}#${script}</file> 
    
            </shell>
            <ok to="shell-hive"/>
            <error to="shell-fail"/>
        </action>
    
        <kill name="shell-fail">
            <message>Incorrect output, expected [Hello Oozie] but was [${ wf:actionData('shell-node')['my_output']}]</message>
        </kill>
    
      
        <action name="shell-hive">
            <shell xmlns="uri:oozie:shell-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>
                
                <exec>${shellHive}</exec>
                <argument>${SQL}</argument>
                <file>${nameNode}/${oozieAppRoot}/project/${shellHive}#${shellHive}</file>
                <file>${nameNode}/${oozieAppRoot}/project/${SQL}#${SQL}</file>
    
            </shell>
            <ok to="sqoop-node"/>
            <error to="shellhive-fail"/>
        </action>
    
        <kill name="shellhive-fail">
            <message>Incorrect output, expected [Hello Oozie] but was [${ wf:actionData('shell-node')['my_output']}]</message>
        </kill>
    
      
    
        <action name="sqoop-node">
            <sqoop xmlns="uri:oozie:sqoop-action:0.3">
                <job-tracker>${jobTracker}</job-tracker>
                <name-node>${nameNode}</name-node>
    
                <configuration>
                    <property>
                        <name>mapred.job.queue.name</name>
                        <value>${queueName}</value>
                    </property>
                </configuration>
    
                <command>export --options-file ${EXEC}</command>
                <file>${nameNode}/${oozieAppRoot}/project/${EXEC}#${EXEC}</file>
                
            </sqoop>
            <ok to="end"/>
            <error to="sqoop-fail"/>
        </action>
    
        <kill name="sqoop-fail">
            <message>Sqoop failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
        </kill>
    
    
        <end name="end"/>
    
      
    </workflow-app>

编写load\_track\_log.sh文件，内容如下：

编写hive\_visit.sql文件，内容如下：

编写daily\_hour\_visit.sh文件，内容如下：

#!/bin/bash
    
    yesterday=`date -d "1 day ago" +"%Y%m%d"`
    
    HIVE_HOME=/opt/modules/hive
    
    $HIVE_HOME/bin/hive -hiveconf yesterday=${yesterday} -f $1

编写export\_visit.txt文件，内容如下：

将project项目上传到HDFS

bin/hdfs dfs -put /opt/modules/oozie/oozie-apps/project /user/caizhengjie/oozie-apps

启动测试：

bin/oozie job -oozie http://bigdata-pro-m01:11000/oozie -config oozie-apps/project/job.properties -run

--------------------

以上内容仅供参考学习，如有侵权请联系我删除！  
如果这篇文章对您有帮助，左下角的大拇指就是对博主最大的鼓励。  
您的鼓励就是博主最大的动力！

[https_blog.csdn.net_weixin_45366499_article_details_109444046]: https://blog.csdn.net/weixin_45366499/article/details/109444046
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTM2NjQ5OQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/1d375aab7eee48948cd93cb1f9991317.png