电商数仓笔记8_数据仓库系统（Azkaban部署，创建MySQL数据库和表，Sqoop导出脚本，全调度流程）

全流程调度

一、Azkaban部署

1、 Azkaban概论

（1）为什么需要工作流调度系统

• 1）一个完整的数据分析系统通常都是由大量任务单元组成：
  Shell脚本程序，Java程序，MapReduce程序、Hive脚本等
• 2）各任务单元之间存在时间先后及前后依赖关系
• 3）为了很好地组织起这样的复杂执行计划，需要一个工作流调度系统来调度执行；
  

（2）常见工作流调度系统

• 1）简单的任务调度：直接使用Linux的Crontab来定义；
• 2）复杂的任务调度：开发调度平台或使用现成的开源调度系统，比如Ooize、Azkaban、 Airflow、DolphinScheduler等。

（3）Azkaban与Oozie对比

• 总体来说，Ooize相比Azkaban是一个重量级的任务调度系统，功能全面，但配置使用也更复杂。如果可以不在意某些功能的缺失，轻量级调度器Azkaban是很不错的候选对象。

2、Azkaban入门

（1）集群模式安装

（1.1）上传tar包

1）将azkaban-db-3.84.4.tar.gz，azkaban-exec-server-3.84.4.tar.gz，azkaban-web-server-3.84.4.tar.gz上传到hadoop102的/opt/software路径

[lyh@hadoop102 software]$ ll
总用量 35572
-rw-r--r--. 1 lyh lyh     6433 4月  18 17:24 azkaban-db-3.84.4.tar.gz
-rw-r--r--. 1 lyh lyh 16175002 4月  18 17:26 azkaban-exec-server-3.84.4.tar.gz
-rw-r--r--. 1 lyh lyh 20239974 4月  18 17:26 azkaban-web-server-3.84.4.tar.gz

2）新建/opt/module/azkaban目录，并将所有tar包解压到这个目录下

[lyh@hadoop102 software]$ mkdir /opt/module/azkaban

3）解压azkaban-db-3.84.4.tar.gz、 azkaban-exec-server-3.84.4.tar.gz和azkaban-web-server-3.84.4.tar.gz到/opt/module/azkaban目录下

[lyh@hadoop102 software]$ tar -zxvf azkaban-db-3.84.4.tar.gz -C /opt/module/azkaban/
[lyh@hadoop102 software]$ tar -zxvf azkaban-exec-server-3.84.4.tar.gz -C /opt/module/azkaban/
[lyh@hadoop102 software]$ tar -zxvf azkaban-web-server-3.84.4.tar.gz -C /opt/module/azkaban/

4）进入到/opt/module/azkaban目录，依次修改名称

[lyh@hadoop102 azkaban]$ mv azkaban-exec-server-3.84.4/ azkaban-exec
[lyh@hadoop102 azkaban]$ mv azkaban-web-server-3.84.4/ azkaban-web

（1.2）配置MySQL

1）正常安装MySQL
2）启动MySQL

[lyh@hadoop102 azkaban]$ mysql -uroot -p000000

3）登陆MySQL，创建Azkaban数据库

mysql> create database azkaban;

4）创建azkaban用户并赋予权限
设置密码有效长度4位及以上

mysql> set global validate_password_length=4;

设置密码策略最低级别

mysql> set global validate_password_policy=0;

创建Azkaban用户，任何主机都可以访问Azkaban，密码是000000

mysql> CREATE USER 'azkaban'@'%' IDENTIFIED BY '000000';

赋予Azkaban用户增删改查权限

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON azkaban.* to 'azkaban'@'%' WITH GRANT OPTION;

5）创建Azkaban表，完成后退出MySQL

mysql> use azkaban;
mysql> source /opt/module/azkaban/azkaban-db-3.84.4/create-all-sql-3.84.4.sql
mysql> quit;

6）更改MySQL包大小；防止Azkaban连接MySQL阻塞

[lyh@hadoop102 software]$ sudo vim /etc/my.cnf

在[mysqld]下面加一行max_allowed_packet=1024M
[mysqld]

max_allowed_packet=1024M

8）重启MySQL

[lyh@hadoop102 software]$ sudo systemctl restart mysqld

（1.3）配置Executor Server

• Azkaban Executor Server处理工作流和作业的实际执行。
  1）编辑azkaban.properties

[lyh@hadoop102 azkaban]$ vim /opt/module/azkaban/azkaban-exec/conf/azkaban.properties

修改如下标红的属性

#...
default.timezone.id=Asia/Shanghai
#...
azkaban.webserver.url=http://hadoop102:8081

executor.port=12321
#...
database.type=mysql
mysql.port=3306
mysql.host=hadoop102
mysql.database=azkaban
mysql.user=azkaban
mysql.password=000000
mysql.numconnections=100

2）同步azkaban-exec到所有节点

[lyh@hadoop102 azkaban]$ xsync /opt/module/azkaban/azkaban-exec

3）必须进入到/opt/module/azkaban/azkaban-exec路径，分别在三台机器上，启动executor server

[lyh@hadoop102 azkaban-exec]$ bin/start-exec.sh
[lyh@hadoop103 azkaban-exec]$ bin/start-exec.sh
[lyh@hadoop104 azkaban-exec]$ bin/start-exec.sh

注意：如果在/opt/module/azkaban/azkaban-exec目录下出现executor.port文件，说明启动成功
4）下面激活executor，需要

[lyh@hadoop102 azkaban-exec]$ curl -G "hadoop102:12321/executor?action=activate" && echo

[lyh@hadoop103 azkaban-exec]$ curl -G "hadoop103:12321/executor?action=activate" && echo

[lyh@hadoop104 azkaban-exec]$ curl -G "hadoop104:12321/executor?action=activate" && echo

如果三台机器都出现如下提示，则表示激活成功
{"status":"success"}

（1.4）配置Web Server

• Azkaban Web Server处理项目管理，身份验证，计划和执行触发。
  1）编辑azkaban.properties

[lyh@hadoop102 azkaban]$ vim /opt/module/azkaban/azkaban-web/conf/azkaban.properties

修改如下属性

...
default.timezone.id=Asia/Shanghai
...
database.type=mysql
mysql.port=3306
mysql.host=hadoop102
mysql.database=azkaban
mysql.user=azkaban
mysql.password=000000
mysql.numconnections=100
...
azkaban.executorselector.filters=StaticRemainingFlowSize,CpuStatus

说明：
#StaticRemainingFlowSize：正在排队的任务数；
#CpuStatus：CPU占用情况
#MinimumFreeMemory：内存占用情况。测试环境，必须将MinimumFreeMemory删除掉，否则它会认为集群资源不够，不执行。
2）修改azkaban-users.xml文件，添加lyh用户

[lyh@hadoop102 azkaban-web]$ vim /opt/module/azkaban/azkaban-web/conf/azkaban-users.xml

<azkaban-users>
  <user groups="azkaban" password="azkaban" roles="admin" username="azkaban"/>
  <user password="metrics" roles="metrics" username="metrics"/>
  <user password="lyh" roles="admin" username="lyh"/>
  <role name="admin" permissions="ADMIN"/>
  <role name="metrics" permissions="METRICS"/>
</azkaban-users>

3）必须进入到hadoop102的/opt/module/azkaban/azkaban-web路径，启动web server

[lyh@hadoop102 azkaban-web]$ bin/start-web.sh

4）访问http://hadoop102:8081,并用lyh用户登陆

3、Work Flow案例实操

（1）HelloWorld案例

1）在windows环境，新建azkaban.project文件，编辑内容如下

azkaban-flow-version: 2.0

注意：该文件作用，是采用新的Flow-API方式解析flow文件。
2）新建basic.flow文件，内容如下

nodes:
  - name: jobA
    type: command
    config:
      command: echo "Hello World"

（1）Name：job名称
（2）Type：job类型。command表示你要执行作业的方式为命令
（3）Config：job配置

3）将azkaban.project、basic.flow文件压缩到一个zip文件，文件名称必须是英文。

4）在WebServer新建项目：http://hadoop102:8081/index

5）给项目名称命名和添加项目描述

6）first.zip文件上传

7）选择上传的文件

8）执行任务流



9）在日志中，查看运行结果

（2）作业依赖案例

• 需求：JobA和JobB执行完了，才能执行JobC

具体步骤：
1）修改basic.flow为如下内容

nodes:
  - name: jobC
    type: command
    # jobC 依赖 JobA和JobB
    dependsOn:
      - jobA
      - jobB
    config:
      command: echo "I’m JobC"

  - name: jobA
    type: command
    config:
      command: echo "I’m JobA"

  - name: jobB
    type: command
    config:
      command: echo "I’m JobB"

（1）dependsOn：作业依赖，后面案例中演示

2）将修改后的basic.flow和azkaban.project压缩成second.zip文件

3）重复2.3.1节HelloWorld后续步骤。

（3）自动失败重试案例

• 需求：如果执行任务失败，需要重试3次，重试的时间间隔10000ms
  具体步骤：
  1）编译配置流

nodes:
  - name: JobA
    type: command
    config:
      command: sh /not_exists.sh
      retries: 3
      retry.backoff: 10000

参数说明：
retries：重试次数
retry.backoff：重试的时间间隔
2）将修改后的basic.flow和azkaban.project压缩成four.zip文件
3）重复2.3.1节HelloWorld后续步骤。
4）执行并观察到一次失败+三次重试

5）也可以点击上图中的Log，在任务日志中看到，总共执行了4次。
6）也可以在Flow全局配置中添加任务失败重试配置，此时重试配置会应用到所有Job。
案例如下：

config:
  retries: 3
  retry.backoff: 10000
nodes:
 - name: JobA
    type: command
    config:
      command: sh /not_exists.sh

（4）手动失败重试案例

• 需求：JobA=》JobB（依赖于A）=》JobC=》JobD=》JobE=》JobF。生产环境，任何Job都有可能挂掉，可以根据需求执行想要执行的Job。
  具体步骤：
  1）编译配置流

nodes:
  - name: JobA
    type: command
    config:
      command: echo "This is JobA."

  - name: JobB
    type: command
    dependsOn:
      - JobA
    config:
      command: echo "This is JobB."

  - name: JobC
    type: command
    dependsOn:
      - JobB
    config:
      command: echo "This is JobC."

  - name: JobD
    type: command
    dependsOn:
      - JobC
    config:
      command: echo "This is JobD."

  - name: JobE
    type: command
    dependsOn:
      - JobD
    config:
      command: echo "This is JobE."

  - name: JobF
    type: command
    dependsOn:
      - JobE
    config:
      command: echo "This is JobF."

2）将修改后的basic.flow和azkaban.project压缩成five.zip文件
3）重复2.3.1节HelloWorld后续步骤。



Enable和Disable下面都分别有如下参数：
Parents：该作业的上一个任务
Ancestors：该作业前的所有任务
Children：该作业后的一个任务
Descendents：该作业后的所有任务
Enable All：所有的任务
4）可以根据需求选择性执行对应的任务。

3、Azkaban进阶

（1）JavaProcess作业类型案例

JavaProcess类型可以运行一个自定义主类方法，type类型为javaprocess，可用的配置为：
Xms：最小堆
Xmx：最大堆
classpath：类路径
java.class：要运行的Java对象，其中必须包含Main方法
main.args：main方法的参数
案例：
1）新建一个azkaban的maven工程
2）创建包名：com.lyh
3）创建AzTest类

package com.lyh;

public class AzTest {
    public static void main(String[] args) {
        System.out.println("This is for testing!");
    }
}

4）打包成jar包azkaban-1.0-SNAPSHOT.jar
5）新建testJava.flow，内容如下

nodes:
  - name: test_java
    type: javaprocess
    config:
      Xms: 96M
      Xmx: 200M
      java.class: com.lyh.AzTest

6）将Jar包、flow文件和project文件打包成javatest.zip
7）创建项目=》上传javatest.zip =》执行作业=》观察结果

（2）条件工作流案例

• 条件工作流功能允许用户自定义执行条件来决定是否运行某些Job。条件可以由当前Job的父Job输出的运行时参数构成，也可以使用预定义宏。在这些条件下，用户可以在确定Job执行逻辑时获得更大的灵活性，例如，只要父Job之一成功，就可以运行当前Job。

（2.1）运行时参数案例

1）基本原理
（1）父Job将参数写入JOB_OUTPUT_PROP_FILE环境变量所指向的文件
（2）子Job使用 ${jobName:param}来获取父Job输出的参数并定义执行条件
2）支持的条件运算符：
（1）== 等于
（2）!= 不等于
（3）> 大于
（4）>= 大于等于
（5）< 小于
（6）<= 小于等于
（7）&& 与
（8）|| 或
（9）! 非
3）案例：
需求：
JobA执行一个shell脚本。
JobB执行一个shell脚本，但JobB不需要每天都执行，而只需要每个周一执行。
（1）新建JobA.sh

#!/bin/bash
echo "do JobA"
wk=`date +%w`
echo "{\"wk\":$wk}" > $JOB_OUTPUT_PROP_FILE

（2）新建JobB.sh

#!/bin/bash
echo "do JobB"

（3）新建condition.flow

nodes:
 - name: JobA
   type: command
   config:
     command: sh JobA.sh

 - name: JobB
   type: command
   dependsOn:
     - JobA
   config:
     command: sh JobB.sh
   condition: ${JobA:wk} == 1

（4）将JobA.sh、JobB.sh、condition.flow和azkaban.project打包成condition.zip
（5）创建condition项目=》上传condition.zip文件=》执行作业=》观察结果
（6）按照我们设定的条件，JobB会根据当日日期决定是否执行。

（2.3）预定义宏案例

• Azkaban中预置了几个特殊的判断条件，称为预定义宏。
• 预定义宏会根据所有父Job的完成情况进行判断，再决定是否执行。可用的预定义宏如下：
  （1）all_success: 表示父Job全部成功才执行(默认)
  （2）all_done：表示父Job全部完成才执行
  （3）all_failed：表示父Job全部失败才执行
  （4）one_success：表示父Job至少一个成功才执行
  （5）one_failed：表示父Job至少一个失败才执行
  1）案例
  需求：
  JobA执行一个shell脚本
  JobB执行一个shell脚本
  JobC执行一个shell脚本，要求JobA、JobB中有一个成功即可执行
  （1）新建JobA.sh

#!/bin/bash
echo "do JobA"

（2）新建JobC.sh

#!/bin/bash
echo "do JobC"

（3）新建macro.flow

nodes:
 - name: JobA
   type: command
   config:
     command: sh JobA.sh

 - name: JobB
   type: command
   config:
     command: sh JobB.sh

 - name: JobC
   type: command
   dependsOn:
     - JobA
     - JobB
   config:
     command: sh JobC.sh
   condition: one_success

（4）JobA.sh、JobC.sh、macro.flow、azkaban.project文件，打包成macro.zip。
注意：没有JobB.sh。
（5）创建macro项目=》上传macro.zip文件=》执行作业=》观察结果

（3）定时执行案例

• 需求：JobA每间隔1分钟执行一次；
  具体步骤：

1）Azkaban可以定时执行工作流。在执行工作流时候，选择左下角Schedule

2）右上角注意时区是上海，然后在左面填写具体执行事件，填写的方法和crontab配置定时任务规则一致。


3）观察结果


4）删除定时调度
点击remove Schedule即可删除当前任务的调度规则。

4、邮件报警案例

（1）注册邮箱

1）申请注册一个126邮箱
2）点击邮箱账号=》账号管理

3）开启SMTP服务

4）一定要记住授权码

（2）默认邮件报警案例

Azkaban默认支持通过邮件对失败的任务进行报警，配置方法如下：
1）在azkaban-web节点hadoop102上，编辑/opt/module/azkaban/azkaban-web/conf/azkaban.properties，修改如下内容：

[lyh@hadoop102 azkaban-web]$ vim /opt/module/azkaban/azkaban-web/conf/azkaban.properties

添加如下内容：

#这里设置邮件发送服务器，需要 申请邮箱，切开通stmp服务，以下只是例子
mail.sender=lyh@126.com
mail.host=smtp.126.com
mail.user=lyh@126.com
mail.password=用邮箱的授权码

2）保存并重启web-server。

[lyh@hadoop102 azkaban-web]$ bin/shutdown-web.sh
[lyh@hadoop102 azkaban-web]$ bin/start-web.sh

3）编辑basic.flow

nodes:
  - name: jobA
    type: command
    config:
      command: echo "This is an email test."

4）将azkaban.project和basic.flow压缩成email.zip
5）创建工程=》上传文件=》执行作业=》查看结果



6）观察邮箱，发现执行成功或者失败的邮件

5、Azkaban多Executor模式注意事项

• Azkaban多Executor模式是指，在集群中多个节点部署Executor。在这种模式下， Azkaban web Server会根据策略，选取其中一个Executor去执行任务。
• 为确保所选的Executor能够准确的执行任务，我们须在以下两种方案任选其一，推荐使用方案二。

方案一：指定特定的Executor（hadoop102）去执行任务。
1）在MySQL中azkaban数据库executors表中，查询hadoop102上的Executor的id。

mysql> use azkaban;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from executors;
+----+-----------+-------+--------+
| id | host          | port  | active |
+----+-----------+-------+--------+
|  1   | hadoop103 | 35985 |      1 |
|  2   | hadoop104 | 36363 |      1 |
|  3   | hadoop102 | 12321 |      1 |
+----+-----------+-------+--------+
3 rows in set (0.00 sec)

2）在执行工作流程时加入useExecutor属性，如下

方案二：在Executor所在所有节点部署任务所需脚本和应用。（学习的时候不推荐）

二、创建MySQL数据库和表

1）创建gmall_report数据库

注:SQL语句

CREATE DATABASE `gmall_report` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

2）创建表
（1）访客统计

DROP TABLE IF EXISTS ads_visit_stats;
CREATE TABLE `ads_visit_stats` (
  `dt` DATE NOT NULL COMMENT '统计日期',
  `is_new` VARCHAR(255) NOT NULL COMMENT '新老标识,1:新,0:老',
  `recent_days` INT NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
  `channel` VARCHAR(255) NOT NULL COMMENT '渠道',
  `uv_count` BIGINT(20) DEFAULT NULL COMMENT '日活(访问人数)',
  `duration_sec` BIGINT(20) DEFAULT NULL COMMENT '页面停留总时长',
  `avg_duration_sec` BIGINT(20)  DEFAULT NULL COMMENT '一次会话，页面停留平均时长',
  `page_count` BIGINT(20) DEFAULT NULL COMMENT '页面总浏览数',
  `avg_page_count` BIGINT(20) DEFAULT NULL COMMENT '一次会话，页面平均浏览数',
  `sv_count` BIGINT(20) DEFAULT NULL COMMENT '会话次数',
  `bounce_count` BIGINT(20) DEFAULT NULL COMMENT '跳出数',
  `bounce_rate` DECIMAL(16,2) DEFAULT NULL COMMENT '跳出率',
  PRIMARY KEY (`dt`,`recent_days`,`is_new`,`channel`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

（2）页面路径分析

DROP TABLE IF EXISTS ads_page_path;
CREATE TABLE `ads_page_path` (      
  `dt` DATE NOT NULL COMMENT '统计日期',
  `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
  `source` VARCHAR(255) DEFAULT NULL COMMENT '跳转起始页面',
  `target` VARCHAR(255) DEFAULT NULL COMMENT '跳转终到页面',
  `path_count` BIGINT(255) DEFAULT NULL COMMENT '跳转次数',
  UNIQUE KEY (`dt`,`recent_days`,`source`,`target`) USING BTREE     
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

（3）用户统计

DROP TABLE IF EXISTS ads_user_total;
CREATE TABLE `ads_user_total` (          
  `dt` DATE NOT NULL COMMENT '统计日期',
  `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,0:累积值,1:最近1天,7:最近7天,30:最近30天',
  `new_user_count` BIGINT(20) DEFAULT NULL COMMENT '新注册用户数',
  `new_order_user_count` BIGINT(20) DEFAULT NULL COMMENT '新增下单用户数',
  `order_final_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '下单总金额',
  `order_user_count` BIGINT(20) DEFAULT NULL COMMENT '下单用户数',
  `no_order_user_count` BIGINT(20) DEFAULT NULL COMMENT '未下单用户数(具体指活跃用户中未下单用户)',
  PRIMARY KEY (`dt`,`recent_days`)           
) ENGINE=INNODB DEFAULT CHARSET=utf8;

（4）用户变动统计

DROP TABLE IF EXISTS ads_user_change;
CREATE TABLE `ads_user_change` (
  `dt` DATE NOT NULL COMMENT '统计日期',
  `user_churn_count` BIGINT(20) DEFAULT NULL  COMMENT '流失用户数',
  `user_back_count` BIGINT(20) DEFAULT NULL  COMMENT '回流用户数',
  PRIMARY KEY (`dt`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

（5）用户行为漏斗分析

DROP TABLE IF EXISTS ads_user_action;
CREATE TABLE `ads_user_action` (
  `dt` DATE NOT NULL COMMENT '统计日期',
  `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
  `home_count` BIGINT(20) DEFAULT NULL COMMENT '浏览首页人数',
  `good_detail_count` BIGINT(20) DEFAULT NULL COMMENT '浏览商品详情页人数',
  `cart_count` BIGINT(20) DEFAULT NULL COMMENT '加入购物车人数',
  `order_count` BIGINT(20) DEFAULT NULL COMMENT '下单人数',
  `payment_count` BIGINT(20) DEFAULT NULL COMMENT '支付人数',
  PRIMARY KEY (`dt`,`recent_days`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

（6）用户留存率分析

DROP TABLE IF EXISTS ads_user_retention;
CREATE TABLE `ads_user_retention` (      
  `dt` DATE DEFAULT NULL COMMENT '统计日期',
  `create_date` VARCHAR(255) NOT NULL COMMENT '用户新增日期',
  `retention_day` BIGINT(20) NOT NULL COMMENT '截至当前日期留存天数',
  `retention_count` BIGINT(20) DEFAULT NULL COMMENT '留存用户数量',
  `new_user_count` BIGINT(20) DEFAULT NULL COMMENT '新增用户数量',
  `retention_rate` DECIMAL(16,2) DEFAULT NULL COMMENT '留存率',
  PRIMARY KEY (`create_date`,`retention_day`) USING BTREE        
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

（7）订单统计

DROP TABLE IF EXISTS ads_order_total;
 CREATE TABLE `ads_order_total` (   
  `dt` DATE NOT NULL COMMENT '统计日期', 
  `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
  `order_count` BIGINT(255) DEFAULT NULL COMMENT '订单数', 
  `order_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '订单金额', 
  `order_user_count` BIGINT(255) DEFAULT NULL COMMENT '下单人数',
  PRIMARY KEY (`dt`,`recent_days`)  
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

（8）各省份订单统计

DROP TABLE IF EXISTS ads_order_by_province;
CREATE TABLE `ads_order_by_province` (
  `dt` DATE NOT NULL,
  `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
  `province_id` VARCHAR(255) NOT NULL COMMENT '统计日期',
  `province_name` VARCHAR(255) DEFAULT NULL COMMENT '省份名称',
  `area_code` VARCHAR(255) DEFAULT NULL COMMENT '地区编码',
  `iso_code` VARCHAR(255) DEFAULT NULL COMMENT '国际标准地区编码',
  `iso_code_3166_2` VARCHAR(255) DEFAULT NULL COMMENT '国际标准地区编码',
  `order_count` BIGINT(20) DEFAULT NULL COMMENT '订单数',
  `order_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '订单金额',
  PRIMARY KEY (`dt`, `recent_days` ,`province_id`) USING BTREE       
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

（9）品牌复购率

DROP TABLE IF EXISTS ads_repeat_purchase;
CREATE TABLE `ads_repeat_purchase` (         
  `dt` DATE NOT NULL COMMENT '统计日期',
  `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
  `tm_id` VARCHAR(255) NOT NULL COMMENT '品牌ID',
  `tm_name` VARCHAR(255) DEFAULT NULL COMMENT '品牌名称',
  `order_repeat_rate` DECIMAL(16,2) DEFAULT NULL COMMENT '复购率',
  PRIMARY KEY (`dt` ,`recent_days`,`tm_id`)          
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

（10）商品统计

DROP TABLE IF EXISTS ads_order_spu_stats;
CREATE TABLE `ads_order_spu_stats` (
  `dt` DATE NOT NULL COMMENT '统计日期',
  `recent_days` BIGINT(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
  `spu_id` VARCHAR(255) NOT NULL COMMENT '商品ID',
  `spu_name` VARCHAR(255) DEFAULT NULL COMMENT '商品名称',
  `tm_id` VARCHAR(255) NOT NULL COMMENT '品牌ID',
  `tm_name` VARCHAR(255) DEFAULT NULL COMMENT '品牌名称',
  `category3_id` VARCHAR(255) NOT NULL COMMENT '三级品类ID',
  `category3_name` VARCHAR(255) DEFAULT NULL COMMENT '三级品类名称',
  `category2_id` VARCHAR(255) NOT NULL COMMENT '二级品类ID',
  `category2_name` VARCHAR(255) DEFAULT NULL COMMENT '二级品类名称',
  `category1_id` VARCHAR(255) NOT NULL COMMENT '一级品类ID',
  `category1_name` VARCHAR(255) NOT NULL COMMENT '一级品类名称',
  `order_count` BIGINT(20) DEFAULT NULL COMMENT '订单数',
  `order_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '订单金额', 
  PRIMARY KEY (`dt`,`recent_days`,`spu_id`)  
) ENGINE=INNODB DEFAULT CHARSET=utf8;

（11）活动统计

DROP TABLE IF EXISTS ads_activity_stats;
CREATE TABLE `ads_activity_stats` (
  `dt` DATE NOT NULL COMMENT '统计日期',
  `activity_id` VARCHAR(255) NOT NULL COMMENT '活动ID',
  `activity_name` VARCHAR(255) DEFAULT NULL COMMENT '活动名称',
  `start_date` DATE DEFAULT NULL COMMENT '开始日期',
  `order_count` BIGINT(11) DEFAULT NULL COMMENT '参与活动订单数',
  `order_original_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '参与活动订单原始金额',
  `order_final_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '参与活动订单最终金额',
  `reduce_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '优惠金额',
  `reduce_rate` DECIMAL(16,2) DEFAULT NULL COMMENT '补贴率',
  PRIMARY KEY (`dt`,`activity_id` )
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

（12）优惠券统计

DROP TABLE IF EXISTS ads_coupon_stats;
CREATE TABLE `ads_coupon_stats` (
  `dt` DATE NOT NULL COMMENT '统计日期',
  `coupon_id` VARCHAR(255) NOT NULL COMMENT '优惠券ID',
  `coupon_name` VARCHAR(255) DEFAULT NULL COMMENT '优惠券名称',
  `start_date` DATE DEFAULT NULL COMMENT '开始日期',  
  `rule_name`  VARCHAR(200) DEFAULT NULL COMMENT '优惠规则',
  `get_count`  BIGINT(20) DEFAULT NULL COMMENT '领取次数',
  `order_count` BIGINT(20) DEFAULT NULL COMMENT '使用(下单)次数',
  `expire_count`  BIGINT(20) DEFAULT NULL COMMENT '过期次数',
  `order_original_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '使用优惠券订单原始金额',
  `order_final_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '使用优惠券订单最终金额',
  `reduce_amount` DECIMAL(16,2) DEFAULT NULL COMMENT '优惠金额',
  `reduce_rate` DECIMAL(16,2) DEFAULT NULL COMMENT '补贴率',
  PRIMARY KEY (`dt`,`coupon_id` )
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

三、Sqoop导出脚本

1）编写Sqoop导出脚本
在/home/lyh/bin目录下创建脚本hdfs_to_mysql.sh

[lyh@hadoop102 bin]$ vim hdfs_to_mysql.sh

在脚本中填写如下内容

#!/bin/bash

hive_db_name=gmall
mysql_db_name=gmall_report

export_data() {
/opt/module/sqoop/bin/sqoop export \
--connect "jdbc:mysql://hadoop102:3306/${mysql_db_name}?useUnicode=true&characterEncoding=utf-8"  \
--username root \
--password yh123456 \
--table $1 \
--num-mappers 1 \
--export-dir /warehouse/$hive_db_name/ads/$1 \
--input-fields-terminated-by "\t" \
--update-mode allowinsert \
--update-key $2 \
--input-null-string '\\N'    \
--input-null-non-string '\\N'
}

case $1 in
  "ads_activity_stats" )
    export_data "ads_activity_stats" "dt,activity_id"
  ;;

  "ads_coupon_stats" )
    export_data "ads_coupon_stats" "dt,coupon_id"
  ;;

  "ads_order_by_province" )
    export_data "ads_order_by_province" "dt,recent_days,province_id"
  ;;

  "ads_order_spu_stats" )
    export_data "ads_order_spu_stats" "dt,recent_days,spu_id"
  ;;

  "ads_order_total" )
    export_data "ads_order_total" "dt,recent_days"
  ;;

  "ads_page_path" )
    export_data "ads_page_path" "dt,recent_days,source,target"
  ;;

  "ads_repeat_purchase" )
    export_data "ads_repeat_purchase" "dt,recent_days,tm_id"
  ;;

  "ads_user_action" )
    export_data "ads_user_action" "dt,recent_days"
  ;;

  "ads_user_change" )
    export_data "ads_user_change" "dt"
  ;;

  "ads_user_retention" )
    export_data "ads_user_retention" "create_date,retention_day"
  ;;

  "ads_user_total" )
    export_data "ads_user_total" "dt,recent_days"
  ;;

  "ads_visit_stats" )
    export_data "ads_visit_stats" "dt,recent_days,is_new,channel"
  ;;
  "all" )
    export_data "ads_activity_stats" "dt,activity_id"
    export_data "ads_coupon_stats" "dt,coupon_id"
    export_data "ads_order_by_province" "dt,recent_days,province_id"
    export_data "ads_order_spu_stats" "dt,recent_days,spu_id"
    export_data "ads_order_total" "dt,recent_days"
    export_data "ads_page_path" "dt,recent_days,source,target"
    export_data "ads_repeat_purchase" "dt,recent_days,tm_id"
    export_data "ads_user_action" "dt,recent_days"
    export_data "ads_user_change" "dt"
    export_data "ads_user_retention" "create_date,retention_day"
    export_data "ads_user_total" "dt,recent_days"
    export_data "ads_visit_stats" "dt,recent_days,is_new,channel"
  ;;
esac

关于导出update还是insert的问题
–update-mode：
updateonly 只更新，无法插入新数据
allowinsert 允许新增
–update-key：允许更新的情况下，指定哪些字段匹配视为同一条数据，进行更新而不增加。多个字段用逗号分隔。
–input-null-string和–input-null-non-string：
分别表示，将字符串列和非字符串列的空串和“null”转义。

官网地址：http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html

Sqoop will by default import NULL values as string null. Hive is however using string \N to denote NULL values and therefore predicates dealing with NULL(like IS NULL) will not work correctly. You should append parameters --null-string and --null-non-string in case of import job or --input-null-string and --input-null-non-string in case of an export job if you wish to properly preserve NULL values. Because sqoop is using those parameters in generated code, you need to properly escape value \N to \\N:

• Hive中的Null在底层是以“\N”来存储，而MySQL中的Null在底层就是Null，为了保证数据两端的一致性。在导出数据时采用–input-null-string和–input-null-non-string两个参数。导入数据时采用–null-string和–null-non-string。
  2）执行Sqoop导出脚本

[lyh@hadoop102 bin]$ chmod 777 hdfs_to_mysql.sh
[lyh@hadoop102 bin]$ hdfs_to_mysql.sh all

四、全调度流程

1、数据准备

1）用户行为数据准备
（1）修改/opt/module/applog下的application.yml

#业务日期
mock.date=2020-06-15
注意：分发至其他需要生成数据的节点
[lyh@hadoop102 applog]$ xsync application.yml

（2）生成数据

[lyh@hadoop102 bin]$ lg.sh

注意：生成数据之后，记得查看HDFS数据是否存在！
（3）观察HDFS的/origin_data/gmall/log/topic_log/2020-06-15路径是否有数据

2）业务数据准备
（1）修改/opt/module/db_log下的application.properties

[lyh@hadoop102 db_log]$ vim application.properties
#业务日期
mock.date=2020-06-15

（2）生成数据

[lyh@hadoop102 db_log]$ java -jar gmall2020-mock-db-2020-04-01.jar

（3）观察SQLyog中order_infor表中operate_time中有2020-06-15日期的数据

2、编写Azkaban工作流程配置文件

1）编写azkaban.project文件，内容如下

azkaban-flow-version: 2.0

2）编写gmall.flow文件，内容如下

nodes:
 - name: mysql_to_hdfs
    type: command
    config:
     command: /home/lyh/bin/mysql_to_hdfs.sh all ${dt}
    
 - name: hdfs_to_ods_log
    type: command
    config:
     command: /home/lyh/bin/hdfs_to_ods_log.sh ${dt}
     
 - name: hdfs_to_ods_db
    type: command
    dependsOn: 
     - mysql_to_hdfs
    config: 
     command: /home/lyh/bin/hdfs_to_ods_db.sh all ${dt}
  
 - name: ods_to_dim_db
    type: command
    dependsOn: 
     - hdfs_to_ods_db
    config: 
     command: /home/lyh/bin/ods_to_dim_db.sh all ${dt}

 - name: ods_to_dwd_log
    type: command
    dependsOn: 
     - hdfs_to_ods_log
    config: 
     command: /home/lyh/bin/ods_to_dwd_log.sh all ${dt}
    
 - name: ods_to_dwd_db
    type: command
    dependsOn: 
     - hdfs_to_ods_db
    config: 
     command: /home/lyh/bin/ods_to_dwd_db.sh all ${dt}
    
 - name: dwd_to_dws
    type: command
    dependsOn:
     - ods_to_dim_db
     - ods_to_dwd_log
     - ods_to_dwd_db
    config:
     command: /home/lyh/bin/dwd_to_dws.sh all ${dt}
    
 - name: dws_to_dwt
    type: command
    dependsOn:
     - dwd_to_dws
    config:
     command: /home/lyh/bin/dws_to_dwt.sh all ${dt}
    
 - name: dwt_to_ads
    type: command
    dependsOn: 
     - dws_to_dwt
    config:
     command: /home/lyh/bin/dwt_to_ads.sh all ${dt}
     
 - name: hdfs_to_mysql
    type: command
    dependsOn:
     - dwt_to_ads
    config:
      command: /home/lyh/bin/hdfs_to_mysql.sh all

3）将azkaban.project、gmall.flow文件压缩到一个zip文件，文件名称必须是英文。
4）在WebServer新建项目：http://hadoop102:8081/index

5）给项目名称命名和添加项目描述

6）gmall.zip文件上传

7）选择上传的文件

8）查看任务流

9）详细任务流展示

10）配置输入dt时间参数


配置executor，值为hadoop102的executor的id

10）执行成功

11）在SQLyog上查看结果

3、Azkaban多Executor模式下注意事项

• Azkaban多Executor模式是指，在集群中多个节点部署Executor。在这种模式下， Azkaban web Server会根据策略，选取其中一个Executor去执行任务。
• 由于我们需要交给Azkaban调度的脚本，以及脚本需要的Hive，Sqoop等应用只在hadoop102部署了，为保证任务顺利执行，我们须在以下两种方案任选其一，推荐使用方案二。

方案一：指定特定的Executor（hadoop102）去执行任务。
1）在MySQL中azkaban数据库executors表中，查询

hadoop102上的Executor的id。
mysql> use azkaban;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from executors;
+----+-----------+-------+--------+
| id | host          | port  | active |
+----+-----------+-------+--------+
|  1   | hadoop103 | 35985 |      1 |
|  2   | hadoop104 | 36363 |      1 |
|  3   | hadoop102 | 12321 |      1 |
+----+-----------+-------+--------+
3 rows in set (0.00 sec)

2）在执行工作流程时加入useExecutor属性，如下

方案二：在Executor所在所有节点部署任务所需脚本和应用。
1）分发脚本、sqoop、spark、my_env.sh

[lyh@hadoop102 ~]$ xsync /home/lyh/bin/
[lyh@hadoop102 ~]$ xsync /opt/module/hive
[lyh@hadoop102 ~]$ xsync /opt/module/sqoop
[lyh@hadoop102 ~]$ xsync /opt/module/spark
[lyh@hadoop102 ~]$ sudo /home/lyh/bin/xsync /etc/profile.d/my_env.sh

2）分发之后，在hadoop103，hadoop104重新加载环境变量配置文件，并重启Azkaban