离线数仓（十）

2024-01-18 13:47:10

第10章全流程调度

10.1 Azkaban部署

　　详情请看博客：https://www.cnblogs.com/LzMingYueShanPao/p/14911782.html

10.2 创建MySQL数据库和表

　　1）创建 gmall_report 数据库（也可以使用SQL语句创建）

#使用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 ‘补贴率‘,
  `rule_comment`  VARCHAR(2000) DEFAULT NULL COMMENT ‘订单规则占比，按订单数统计，要求结果形式如下：满100减10：19.5%，满200减30：80.5%‘,
  `spu_comment` VARCHAR(2000) DEFAULT NULL COMMENT ‘商品占比，按订单数统计，要求结果形式如下:iPhone X:30%,xiaomi 10:20%,iPhone 12:30%,其他:20%‘,
  `tm_comment` VARCHAR(2000) DEFAULT NULL COMMENT ‘品牌占比，按订单数统计，要求结果形式如下:Apple:30%,Xiaomi:20%,Huawei:30%,其他:20%‘,
  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 ‘补贴率‘,
  `spu_comment` VARCHAR(2000) DEFAULT NULL COMMENT ‘商品占比，按订单数统计，要求结果形式如下:iPhone X:30%,xiaomi 10:20%,iPhone 12:30%,其他:20%‘,
  `tm_comment` VARCHAR(2000) DEFAULT NULL COMMENT ‘品牌占比，按订单数统计，要求结果形式如下:Apple:30%,Xiaomi:20%,Huawei:30%,其他:20%‘,
  PRIMARY KEY (`dt`,`coupon_id` )
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

　　2）编写一次性创建表的sql文件，运行该文件即可创建全部的表（免得一张张创建麻烦）

use gmall_report;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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 ‘补贴率‘,
  `rule_comment`  VARCHAR(2000) DEFAULT NULL COMMENT ‘订单规则占比，按订单数统计，要求结果形式如下：满100减10：19.5%，满200减30：80.5%‘,
  `spu_comment` VARCHAR(2000) DEFAULT NULL COMMENT ‘商品占比，按订单数统计，要求结果形式如下:iPhone X:30%,xiaomi 10:20%,iPhone 12:30%,其他:20%‘,
  `tm_comment` VARCHAR(2000) DEFAULT NULL COMMENT ‘品牌占比，按订单数统计，要求结果形式如下:Apple:30%,Xiaomi:20%,Huawei:30%,其他:20%‘,
  PRIMARY KEY (`dt`,`activity_id` )
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

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 ‘补贴率‘,
  `spu_comment` VARCHAR(2000) DEFAULT NULL COMMENT ‘商品占比，按订单数统计，要求结果形式如下:iPhone X:30%,xiaomi 10:20%,iPhone 12:30%,其他:20%‘,
  `tm_comment` VARCHAR(2000) DEFAULT NULL COMMENT ‘品牌占比，按订单数统计，要求结果形式如下:Apple:30%,Xiaomi:20%,Huawei:30%,其他:20%‘,
  PRIMARY KEY (`dt`,`coupon_id` )
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

View Code

10.3 Sqoop导出脚本

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

vim hdfs_sqoop_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 root123 --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的问题

　　　　（1）update-mode：

　　　　　　（a）updateonly：只更新，无法插入新数据

　　　　　　（b）allowinsert：允许新增

　　　　（2）update-key：允许更新的情况下，指定哪些字段匹配视为同一条数据，进行更新而不增加。多个字段用逗号分隔

　　　　（3）input-null-string 和 --input-null-non-string分别表示：将字符串列和非字符串列的空串和“null”转义

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

翻译过来就是：
默认情况下，Sqoop将空值作为字符串空值导入。但是，配置单元使用字符串\N表示空值，因此处理空值的谓词（如is NULL）将无法正常工作。
如果希望正确地保留空值，则应该在导入作业的情况下追加参数--null string和--null non string，或者在导出作业的情况下追加参数--input null string和--input null non string。
由于sqoop正在生成的代码中使用这些参数，因此需要将值\N正确转义为\\N：
$sqoop导入--空字符串‘\\N‘--空非字符串‘\\N‘

　　　　Hive中的Null在底层是以“\N”来存储，而MySQL中的Null在底层就是Null，为了保证数据两端的一致性：

　　　　　　a）在导出数据时采用--input-null-string和--input-null-non-string两个参数

　　　　　　b）导入数据时采用--null-string和--null-non-string。2）执行Sqoop导出脚本

　　2）给脚本添加可执行权限

chmod 777 hdfs_sqoop_mysql.sh

　　3）执行脚本，导入数据

hdfs_sqoop_mysql.sh all

10.4 全调度流程

10.4.1 数据准备

　　详情请看博客：https://www.cnblogs.com/LzMingYueShanPao/p/14862980.html

10.4.2 编写Azkaban工作流程配置文件

　　1）数仓首次导入工作流

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

azkaban-flow-version: 2.0

　　　　（2）编写gmall_init.flow文件，内容如下（你的脚本放在哪个路径下就写哪个，还有我的Sqoop和Hive安装在hadoop102，但是Azkaban的web服务安装在hadoop103，所以我在执行脚本之前

　　　　　　　　　　　　　　　　　　　　　　　　加上 ssh hadoop102）

nodes:
  - name: mysql_sqoop_hdfs_init
    type: command
    config:
      command: ssh hadoop102 /home/atguigu/bin/mysql_sqoop_hdfs_init.sh all ${dt}

  - name: hdfs_to_ods_db_init
    type: command
    dependsOn:
      - mysql_sqoop_hdfs_init
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/hdfs_to_ods_db_init.sh all ${dt}

  - name: hdfs_to_ods_log
    type: command
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/hdfs_to_ods_log.sh ${dt}

  - name: ods_to_dim_init
    type: command
    dependsOn:
      - hdfs_to_ods_db_init
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/ods_to_dim_db_init.sh all ${dt}

  - name: ods_to_dwd_db_init
    type: command
    dependsOn:
      - hdfs_to_ods_db_init
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/ods_to_dwd_db_init.sh all ${dt}

  - name: ods_to_dwd_log
    type: command
    dependsOn:
      - hdfs_to_ods_log
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/ods_to_dwd_log.sh all ${dt}

  - name: dwd_to_dws_init
    type: command
    dependsOn:
      - ods_to_dim_init
      - ods_to_dwd_db_init
      - ods_to_dwd_log
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/dwd_to_dws_init.sh all ${dt}

  - name: dws_to_dwt_init
    type: command
    dependsOn:
      - dwd_to_dws_init
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/dws_to_dwt_init.sh all ${dt}

  - name: dwt_to_ads
    type: command
    dependsOn:
      - dws_to_dwt_init
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/dwt_to_ads.sh all ${dt}

  - name: hdfs_sqoop_mysql
    type: command
    dependsOn:
      - dwt_to_ads
    config:
      command: ssh hadoop102 /home/atguigu/bin/hdfs_sqoop_mysql.sh all

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

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

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

　　　　（6）gmall_init.zip文件上传

　　　　（7）选择上传的文件

　　　　（8）查看任务流

　　　　　　注意：在第一次创建dim_date_info_tmp表示，要将数据上传至HDFS中

　　　　（9）详细任务流展示

　　　　（10）配置输入dt时间参数

　　　　（10）执行成功

　　　　（11）在SQLyog上查看结果

　　2）数仓首次导入工作流

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

azkaban-flow-version: 2.0

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

nodes:
  - name: mysql_sqoop_hdfs_everyday
    type: command
    config:
      command: ssh hadoop102 /home/atguigu/bin/mysql_sqoop_hdfs_everyday.sh all ${dt}

  - name: hdfs_to_ods_db
    type: command
    dependsOn:
      - mysql_sqoop_hdfs_everyday
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/hdfs_to_ods_db.sh all ${dt}

  - name: hdfs_to_ods_log
    type: command
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/hdfs_to_ods_log.sh ${dt}

  - name: ods_to_dim_db
    type: command
    dependsOn:
      - hdfs_to_ods_db
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/ods_to_dim_db.sh all ${dt}

  - name: ods_to_dwd_db
    type: command
    dependsOn:
      - hdfs_to_ods_db
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/ods_to_dwd_db.sh all ${dt}

  - name: ods_to_dwd_log
    type: command
    dependsOn:
      - hdfs_to_ods_log
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/ods_to_dwd_log.sh all ${dt}

  - name: dwd_to_dws
    type: command
    dependsOn:
      - ods_to_dim_db
      - ods_to_dwd_db
      - ods_to_dwd_log
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/dwd_to_dws.sh all ${dt}

  - name: dws_to_dwt
    type: command
    dependsOn:
      - dwd_to_dws
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/dws_to_dwt.sh all ${dt}

  - name: dwt_to_ads
    type: command
    dependsOn:
      - dws_to_dwt
    config:
      command: ssh hadoop102 /home/atguigu/bin/hiveSh/dwt_to_ads.sh all ${dt}

  - name: hdfs_sqoop_mysql
    type: command
    dependsOn:
      - dwt_to_ads
    config:
      command: ssh hadoop102 /home/atguigu/bin/hdfs_sqoop_mysql.sh all

　　　　（3）将azkaban_everyday.project、gmall_everyday.flow文件压缩到一个zip文件，文件名称必须是英文

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

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

　　　　（6）gmall_everyday.zip文件上传

　　　　（7）选择上传的文件

　　　　（8）查看任务流

　　　　（9）详细任务流展示

　　　　（10）配置输入dt时间参数

　　　　（10）执行成功

　　　　（11）在SQLyog上查看结果

10.4.3 Azkaban多Executor模式下注意事项

　　Azkaban多Executor模式是指，在集群中多个节点部署Executor。在这种模式下， Azkaban web Server会根据策略，选取其中一个Executor去执行任务。

　　由于我们需要交给Azkaban调度的脚本，以及脚本需要的Hive，Sqoop等应用只在hadoop102部署了，为保证任务顺利执行，我们须在以下两种方案任选其一，推荐使用方案二。

　　方案一：指定特定的Executor（hadoop102）去执行任务。

　　1）在MySQL中azkaban数据库executors表中，查询hadoop102上的Executor的id

use azkaban;

select * from executors;

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

　　方案二：在Executor所在所有节点部署任务所需脚本和应用。

　　分发脚本：sqoop、spark、my_env.sh

xsync /home/atguigu/bin/

xsync /opt/module/sqoop

xsync /opt/module/spark-yarn

sudo /home/atguigu/bin/xsync /etc/profile.d/my_env.sh