本文详细介绍了如何将ADS层的报表数据从Hive导出到MySQL，包括数据库和表的创建，DataX配置文件的编写与执行，以及每日数据导出的自动化脚本。
ADS层数据导出
前言
前面完成了ADS层所有表的设计和实现，下面就要将这些数据导出到MySQL数据库中，方便可视化的使用。
一、报表数据导出
1.MySQL建库建表
我们要将HDFS里的统计数据导出到MySQL中，首先要建数据库，再建相应的表。
1.1 创建数据库
CREATE DATABASE IF NOT EXISTS gmall_report DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
1.2 创建表
1）各渠道流量统计
Hive中的表：
DROP TABLE IF EXISTS ads_traffic_stats_by_channel;
CREATE EXTERNAL TABLE ads_traffic_stats_by_channel
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`channel` STRING COMMENT '渠道',
`uv_count` BIGINT COMMENT '访客人数',
`avg_duration_sec` BIGINT COMMENT '会话平均停留时长，单位为秒',
`avg_page_count` BIGINT COMMENT '会话平均浏览页面数',
`sv_count` BIGINT COMMENT '会话数',
`bounce_rate` DECIMAL(16, 2) COMMENT '跳出率'
) COMMENT '各渠道流量统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_traffic_stats_by_channel/';
MySQL中的表：
DROP TABLE IF EXISTS `ads_traffic_stats_by_channel`;
CREATE TABLE `ads_traffic_stats_by_channel` (
`dt` date NOT NULL COMMENT '统计日期',
`recent_days` bigint(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`channel` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '渠道',
`uv_count` bigint(20) NULL DEFAULT NULL COMMENT '访客人数',
`avg_duration_sec` bigint(20) NULL DEFAULT NULL COMMENT '会话平均停留时长，单位为秒',
`avg_page_count` bigint(20) NULL DEFAULT NULL COMMENT '会话平均浏览页面数',
`sv_count` bigint(20) NULL DEFAULT NULL COMMENT '会话数',
`bounce_rate` decimal(16, 2) NULL DEFAULT NULL COMMENT '跳出率',
PRIMARY KEY (`dt`, `recent_days`, `channel`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各渠道流量统计' ROW_FORMAT = Dynamic;
2）路径分析
Hive中的表：
DROP TABLE IF EXISTS ads_page_path;
CREATE EXTERNAL TABLE ads_page_path
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`source` STRING COMMENT '跳转起始页面ID',
`target` STRING COMMENT '跳转终到页面ID',
`path_count` BIGINT COMMENT '跳转次数'
) COMMENT '页面浏览路径分析'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_page_path/';
MySQL建表语句：
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(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '跳转起始页面ID',
`target` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '跳转终到页面ID',
`path_count` bigint(20) NULL DEFAULT NULL COMMENT '跳转次数',
PRIMARY KEY (`dt`, `recent_days`, `source`, `target`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '页面浏览路径分析' ROW_FORMAT = Dynamic;
3）用户变动统计
Hive中的表：
DROP TABLE IF EXISTS ads_user_change;
CREATE EXTERNAL TABLE ads_user_change
(
`dt` STRING COMMENT '统计日期',
`user_churn_count` BIGINT COMMENT '流失用户数',
`user_back_count` BIGINT COMMENT '回流用户数'
) COMMENT '用户变动统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_user_change/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_user_change`;
CREATE TABLE `ads_user_change` (
`dt` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '统计日期',
`user_churn_count` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '流失用户数',
`user_back_count` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '回流用户数',
PRIMARY KEY (`dt`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户变动统计' ROW_FORMAT = Dynamic;
4）用户留存率
Hive建表语句：
DROP TABLE IF EXISTS ads_user_retention;
CREATE EXTERNAL TABLE ads_user_retention
(
`dt` STRING COMMENT '统计日期',
`create_date` STRING COMMENT '用户新增日期',
`retention_day` INT COMMENT '截至当前日期留存天数',
`retention_count` BIGINT COMMENT '留存用户数量',
`new_user_count` BIGINT COMMENT '新增用户数量',
`retention_rate` DECIMAL(16, 2) COMMENT '留存率'
) COMMENT '用户留存率'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_user_retention/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_user_retention`;
CREATE TABLE `ads_user_retention` (
`dt` date NOT NULL COMMENT '统计日期',
`create_date` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户新增日期',
`retention_day` int(20) NOT NULL COMMENT '截至当前日期留存天数',
`retention_count` bigint(20) NULL DEFAULT NULL COMMENT '留存用户数量',
`new_user_count` bigint(20) NULL DEFAULT NULL COMMENT '新增用户数量',
`retention_rate` decimal(16, 2) NULL DEFAULT NULL COMMENT '留存率',
PRIMARY KEY (`dt`, `create_date`, `retention_day`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '留存率' ROW_FORMAT = Dynamic;
5）用户新增活跃统计
Hive建表语句：
DROP TABLE IF EXISTS ads_user_stats;
CREATE EXTERNAL TABLE ads_user_stats
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近n日,1:最近1日,7:最近7日,30:最近30日',
`new_user_count` BIGINT COMMENT '新增用户数',
`active_user_count` BIGINT COMMENT '活跃用户数'
) COMMENT '用户新增活跃统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_user_stats/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_user_stats`;
CREATE TABLE `ads_user_stats` (
`dt` date NOT NULL COMMENT '统计日期',
`recent_days` bigint(20) NOT NULL COMMENT '最近n日,1:最近1日,7:最近7日,30:最近30日',
`new_user_count` bigint(20) NULL DEFAULT NULL COMMENT '新增用户数',
`active_user_count` bigint(20) NULL DEFAULT NULL COMMENT '活跃用户数',
PRIMARY KEY (`dt`, `recent_days`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户新增活跃统计' ROW_FORMAT = Dynamic;
6）用户行为漏斗分析
Hive建表语句：
DROP TABLE IF EXISTS ads_user_action;
CREATE EXTERNAL TABLE ads_user_action
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`home_count` BIGINT COMMENT '浏览首页人数',
`good_detail_count` BIGINT COMMENT '浏览商品详情页人数',
`cart_count` BIGINT COMMENT '加入购物车人数',
`order_count` BIGINT COMMENT '下单人数',
`payment_count` BIGINT COMMENT '支付人数'
) COMMENT '漏斗分析'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_user_action/';
MySQL建表语句：
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) NULL DEFAULT NULL COMMENT '浏览首页人数',
`good_detail_count` bigint(20) NULL DEFAULT NULL COMMENT '浏览商品详情页人数',
`cart_count` bigint(20) NULL DEFAULT NULL COMMENT '加入购物车人数',
`order_count` bigint(20) NULL DEFAULT NULL COMMENT '下单人数',
`payment_count` bigint(20) NULL DEFAULT NULL COMMENT '支付人数',
PRIMARY KEY (`dt`, `recent_days`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '漏斗分析' ROW_FORMAT = Dynamic;
7）新增交易用户统计
Hive建表语句：
DROP TABLE IF EXISTS ads_new_buyer_stats;
CREATE EXTERNAL TABLE ads_new_buyer_stats
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`new_order_user_count` BIGINT COMMENT '新增下单人数',
`new_payment_user_count` BIGINT COMMENT '新增支付人数'
) COMMENT '新增交易用户统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_new_buyer_stats/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_new_buyer_stats`;
CREATE TABLE `ads_new_buyer_stats` (
`dt` date NOT NULL COMMENT '统计日期',
`recent_days` bigint(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`new_order_user_count` bigint(20) NULL DEFAULT NULL COMMENT '新增下单人数',
`new_payment_user_count` bigint(20) NULL DEFAULT NULL COMMENT '新增支付人数',
PRIMARY KEY (`dt`, `recent_days`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '新增交易用户统计' ROW_FORMAT = Dynamic;
8）最近7/30日各品牌复购率
Hive建表语句：
DROP TABLE IF EXISTS ads_repeat_purchase_by_tm;
CREATE EXTERNAL TABLE ads_repeat_purchase_by_tm
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,7:最近7天,30:最近30天',
`tm_id` STRING COMMENT '品牌ID',
`tm_name` STRING COMMENT '品牌名称',
`order_repeat_rate` DECIMAL(16, 2) COMMENT '复购率'
) COMMENT '各品牌复购率统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_repeat_purchase_by_tm/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_repeat_purchase_by_tm`;
CREATE TABLE `ads_repeat_purchase_by_tm` (
`dt` date NOT NULL COMMENT '统计日期',
`recent_days` bigint(20) NOT NULL COMMENT '最近天数,7:最近7天,30:最近30天',
`tm_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '品牌ID',
`tm_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '品牌名称',
`order_repeat_rate` decimal(16, 2) NULL DEFAULT NULL COMMENT '复购率',
PRIMARY KEY (`dt`, `recent_days`, `tm_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各品牌复购率统计' ROW_FORMAT = Dynamic;
9）各品牌商品交易统计
Hive建表语句：
DROP TABLE IF EXISTS ads_trade_stats_by_tm;
CREATE EXTERNAL TABLE ads_trade_stats_by_tm
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`tm_id` STRING COMMENT '品牌ID',
`tm_name` STRING COMMENT '品牌名称',
`order_count` BIGINT COMMENT '订单数',
`order_user_count` BIGINT COMMENT '订单人数',
`order_refund_count` BIGINT COMMENT '退单数',
`order_refund_user_count` BIGINT COMMENT '退单人数'
) COMMENT '各品牌商品交易统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_trade_stats_by_tm/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_trade_stats_by_tm`;
CREATE TABLE `ads_trade_stats_by_tm` (
`dt` date NOT NULL COMMENT '统计日期',
`recent_days` bigint(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`tm_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '品牌ID',
`tm_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '品牌名称',
`order_count` bigint(20) NULL DEFAULT NULL COMMENT '订单数',
`order_user_count` bigint(20) NULL DEFAULT NULL COMMENT '订单人数',
`order_refund_count` bigint(20) NULL DEFAULT NULL COMMENT '退单数',
`order_refund_user_count` bigint(20) NULL DEFAULT NULL COMMENT '退单人数',
PRIMARY KEY (`dt`, `recent_days`, `tm_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各品牌商品交易统计' ROW_FORMAT = Dynamic;
10）各品类商品交易统计
Hive建表语句：
DROP TABLE IF EXISTS ads_trade_stats_by_cate;
CREATE EXTERNAL TABLE ads_trade_stats_by_cate
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`category1_id` STRING COMMENT '一级分类id',
`category1_name` STRING COMMENT '一级分类名称',
`category2_id` STRING COMMENT '二级分类id',
`category2_name` STRING COMMENT '二级分类名称',
`category3_id` STRING COMMENT '三级分类id',
`category3_name` STRING COMMENT '三级分类名称',
`order_count` BIGINT COMMENT '订单数',
`order_user_count` BIGINT COMMENT '订单人数',
`order_refund_count` BIGINT COMMENT '退单数',
`order_refund_user_count` BIGINT COMMENT '退单人数'
) COMMENT '各分类商品交易统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_trade_stats_by_cate/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_trade_stats_by_cate`;
CREATE TABLE `ads_trade_stats_by_cate` (
`dt` date NOT NULL COMMENT '统计日期',
`recent_days` bigint(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`category1_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '一级分类id',
`category1_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '一级分类名称',
`category2_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '二级分类id',
`category2_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '二级分类名称',
`category3_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '三级分类id',
`category3_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '三级分类名称',
`order_count` bigint(20) NULL DEFAULT NULL COMMENT '订单数',
`order_user_count` bigint(20) NULL DEFAULT NULL COMMENT '订单人数',
`order_refund_count` bigint(20) NULL DEFAULT NULL COMMENT '退单数',
`order_refund_user_count` bigint(20) NULL DEFAULT NULL COMMENT '退单人数',
PRIMARY KEY (`dt`, `recent_days`, `category1_id`, `category2_id`, `category3_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各分类商品交易统计' ROW_FORMAT = Dynamic;
11）各分类商品购物车存货量Top10
Hive建表语句：
DROP TABLE IF EXISTS ads_sku_cart_num_top3_by_cate;
CREATE EXTERNAL TABLE ads_sku_cart_num_top3_by_cate
(
`dt` STRING COMMENT '统计日期',
`category1_id` STRING COMMENT '一级分类ID',
`category1_name` STRING COMMENT '一级分类名称',
`category2_id` STRING COMMENT '二级分类ID',
`category2_name` STRING COMMENT '二级分类名称',
`category3_id` STRING COMMENT '三级分类ID',
`category3_name` STRING COMMENT '三级分类名称',
`sku_id` STRING COMMENT '商品id',
`sku_name` STRING COMMENT '商品名称',
`cart_num` BIGINT COMMENT '购物车中商品数量',
`rk` BIGINT COMMENT '排名'
) COMMENT '各分类商品购物车存量Top10'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_sku_cart_num_top3_by_cate/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_sku_cart_num_top3_by_cate`;
CREATE TABLE `ads_sku_cart_num_top3_by_cate` (
`dt` date NOT NULL COMMENT '统计日期',
`category1_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '一级分类ID',
`category1_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '一级分类名称',
`category2_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '二级分类ID',
`category2_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '二级分类名称',
`category3_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '三级分类ID',
`category3_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '三级分类名称',
`sku_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品id',
`sku_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
`cart_num` bigint(20) NULL DEFAULT NULL COMMENT '购物车中商品数量',
`rk` bigint(20) NULL DEFAULT NULL COMMENT '排名',
PRIMARY KEY (`dt`, `sku_id`, `category1_id`, `category2_id`, `category3_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各分类商品购物车存量Top10' ROW_FORMAT = Dynamic;
12）交易综合统计
Hive建表语句：
DROP TABLE IF EXISTS ads_trade_stats;
CREATE EXTERNAL TABLE ads_trade_stats
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1日,7:最近7天,30:最近30天',
`order_total_amount` DECIMAL(16, 2) COMMENT '订单总额,GMV',
`order_count` BIGINT COMMENT '订单数',
`order_user_count` BIGINT COMMENT '下单人数',
`order_refund_count` BIGINT COMMENT '退单数',
`order_refund_user_count` BIGINT COMMENT '退单人数'
) COMMENT '交易统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_trade_stats/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_trade_stats`;
CREATE TABLE `ads_trade_stats` (
`dt` date NOT NULL COMMENT '统计日期',
`recent_days` bigint(255) NOT NULL COMMENT '最近天数,1:最近1日,7:最近7天,30:最近30天',
`order_total_amount` decimal(16, 2) NULL DEFAULT NULL COMMENT '订单总额,GMV',
`order_count` bigint(20) NULL DEFAULT NULL COMMENT '订单数',
`order_user_count` bigint(20) NULL DEFAULT NULL COMMENT '下单人数',
`order_refund_count` bigint(20) NULL DEFAULT NULL COMMENT '退单数',
`order_refund_user_count` bigint(20) NULL DEFAULT NULL COMMENT '退单人数',
PRIMARY KEY (`dt`, `recent_days`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '交易统计' ROW_FORMAT = Dynamic;
13）各省份交易统计
Hive建表语句：
DROP TABLE IF EXISTS ads_order_by_province;
CREATE EXTERNAL TABLE ads_order_by_province
(
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`province_id` STRING COMMENT '省份ID',
`province_name` STRING COMMENT '省份名称',
`area_code` STRING COMMENT '地区编码',
`iso_code` STRING COMMENT '国际标准地区编码',
`iso_code_3166_2` STRING COMMENT '国际标准地区编码',
`order_count` BIGINT COMMENT '订单数',
`order_total_amount` DECIMAL(16, 2) COMMENT '订单金额'
) COMMENT '各地区订单统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_order_by_province/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_order_by_province`;
CREATE TABLE `ads_order_by_province` (
`dt` date NOT NULL COMMENT '统计日期',
`recent_days` bigint(20) NOT NULL COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`province_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '省份ID',
`province_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '省份名称',
`area_code` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地区编码',
`iso_code` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '国际标准地区编码',
`iso_code_3166_2` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '国际标准地区编码',
`order_count` bigint(20) NULL DEFAULT NULL COMMENT '订单数',
`order_total_amount` decimal(16, 2) NULL DEFAULT NULL COMMENT '订单金额',
PRIMARY KEY (`dt`, `recent_days`, `province_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '各地区订单统计' ROW_FORMAT = Dynamic;
14）最近30天发布的优惠券的补贴率
Hive建表语句：
DROP TABLE IF EXISTS ads_coupon_stats;
CREATE EXTERNAL TABLE ads_coupon_stats
(
`dt` STRING COMMENT '统计日期',
`coupon_id` STRING COMMENT '优惠券ID',
`coupon_name` STRING COMMENT '优惠券名称',
`start_date` STRING COMMENT '发布日期',
`rule_name` STRING COMMENT '优惠规则，例如满100元减10元',
`reduce_rate` DECIMAL(16, 2) COMMENT '补贴率'
) COMMENT '优惠券统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_coupon_stats/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_coupon_stats`;
CREATE TABLE `ads_coupon_stats` (
`dt` date NOT NULL COMMENT '统计日期',
`coupon_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '优惠券ID',
`coupon_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '优惠券名称',
`start_date` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发布日期',
`rule_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '优惠规则，例如满100元减10元',
`reduce_rate` decimal(16, 2) NULL DEFAULT NULL COMMENT '补贴率',
PRIMARY KEY (`dt`, `coupon_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '优惠券统计' ROW_FORMAT = Dynamic;
15）最近30天发布的活动的补贴率
Hive建表语句：
DROP TABLE IF EXISTS ads_activity_stats;
CREATE EXTERNAL TABLE ads_activity_stats
(
`dt` STRING COMMENT '统计日期',
`activity_id` STRING COMMENT '活动ID',
`activity_name` STRING COMMENT '活动名称',
`start_date` STRING COMMENT '活动开始日期',
`reduce_rate` DECIMAL(16, 2) COMMENT '补贴率'
) COMMENT '活动统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_activity_stats/';
MySQL建表语句：
DROP TABLE IF EXISTS `ads_activity_stats`;
CREATE TABLE `ads_activity_stats` (
`dt` date NOT NULL COMMENT '统计日期',
`activity_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '活动ID',
`activity_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '活动名称',
`start_date` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '活动开始日期',
`reduce_rate` decimal(16, 2) NULL DEFAULT NULL COMMENT '补贴率',
PRIMARY KEY (`dt`, `activity_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '活动统计' ROW_FORMAT = Dynamic;
1.3 建表注意事项
1）数据类型：建表的时候要根据Hive中字段的类型设计mysql表中的数据类型。
2）主键的确定：mysql建表的时候要根据Hive表中的字段确定相应的主键。
2.数据导出
这里数据导出工具采用DataX，从HDFS导出到MySQL，所以采用HDFSReader和MySQLWriter。
2.1 编写DataX配置文件
总共有15张表需要导出，所以要编写15个配置文件，此处以ads_traffic_stats_by_channel这张表为例，我编写配置文件如下：
HDFS导出到MySQL：
{
"job": {
"setting": {
"speed": {
"channel": 3
}
},
"content": [
{
"reader": {
"name": "hdfsreader",
"parameter": {
"path": "${exportDir}",
"defaultFS": "hdfs://hadoop102:8020",
"column": [
{
"index": 0,
"type": "STRING"
},
{
"index": 1,
"type": "BIGINT"
},
{
"index": 2,
"type": "STRING"
},
{
"index": 3,
"type": "BIGINT"
},
{
"index": 4,
"type": "BIGINT"
},
{
"index": 5,
"type": "BIGINT"
},
{
"index": 6,
"type": "BIGINT"
},
{
"index": 7,
"type": "DECIMAL(16,2)"
}
],
"fileType": "text",
"encoding": "UTF-8",
"fieldDelimiter": "\t"
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "replace",
"username": "root",
"password": "000000",
"column": [
"dt",
"rencent_days",
"channel",
"uv_count",
"avg_duration_sec",
"avg_page_count",
"sv_count",
"bounce_rate"
],
"connection": [
{
"jdbcUrl": "jdbc:mysql://hadoop102:3306/datax?useUnicode=true&characterEncoding=utf-8",
"table": [
"ads_traffic_stats_by_channel"
]
}
]
}
}
}
]
}
}
给出的写法：
{
"job": {
"content": [
{
"reader": {
"name": "hdfsreader",
"parameter": {
"column": [
"*"
],
"defaultFS": "hdfs://hadoop102:8020",
"encoding": "UTF-8",
"fieldDelimiter": "\t",
"fileType": "text",
"nullFormat": "\\N",
"path": "${exportdir}"
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"column": [
"dt",
"recent_days",
"channel",
"uv_count",
"avg_duration_sec",
"avg_page_count",
"sv_count",
"bounce_rate"
],
"connection": [
{
"jdbcUrl": "jdbc:mysql://hadoop102:3306/gmall_report?useUnicode=true&characterEncoding=utf-8",
"table": [
"ads_traffic_stats_by_channel"
]
}
],
"password": "000000",
"username": "root",
"writeMode": "replace"
}
}
}
],
"setting": {
"errorLimit": {
"percentage": 0.02,
"record": 0
},
"speed": {
"channel": 3
}
}
}
}
注意说明：
要注意对null值的处理，再HDFS中null值被定义为\N字符，这样定义是为了遇到\N，DataX就将它视作null值。
2.2 DataX配置文件生成脚本
1）创建生成DataX配置文件脚本
为了方便起见，这里编写一个DataX配置文件批量生成脚本，脚本内容如下：
# coding=utf-8
import json
import getopt
import os
import sys
import MySQLdb
#MySQL相关配置，需根据实际情况作出修改
mysql_host = "hadoop102"
mysql_port = "3306"
mysql_user = "root"
mysql_passwd = "000000"
#HDFS NameNode相关配置，需根据实际情况作出修改
hdfs_nn_host = "hadoop102"
hdfs_nn_port = "8020"
#生成配置文件的目标路径，可根据实际情况作出修改
output_path = "/opt/module/datax/job/export"
def get_connection():
return MySQLdb.connect(host=mysql_host, port=int(mysql_port), user=mysql_user, passwd=mysql_passwd)
def get_mysql_meta(database, table):
connection = get_connection()
cursor = connection.cursor()
sql = "SELECT COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS WHERE TABLE_SCHEMA=%s AND TABLE_NAME=%s ORDER BY ORDINAL_POSITION"
cursor.execute(sql, [database, table])
fetchall = cursor.fetchall()
cursor.close()
connection.close()
return fetchall
def get_mysql_columns(database, table):
return map(lambda x: x[0], get_mysql_meta(database, table))
def generate_json(target_database, target_table):
job = {
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [{
"reader": {
"name": "hdfsreader",
"parameter": {
"path": "${exportdir}",
"defaultFS": "hdfs://" + hdfs_nn_host + ":" + hdfs_nn_port,
"column": ["*"],
"fileType": "text",
"encoding": "UTF-8",
"fieldDelimiter": "\t",
"nullFormat": "\\N"
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "replace",
"username": mysql_user,
"password": mysql_passwd,
"column": get_mysql_columns(target_database, target_table),
"connection": [
{
"jdbcUrl":
"jdbc:mysql://" + mysql_host + ":" + mysql_port + "/" + target_database + "?useUnicode=true&characterEncoding=utf-8",
"table": [target_table]
}
]
}
}
}]
}
}
if not os.path.exists(output_path):
os.makedirs(output_path)
with open(os.path.join(output_path, ".".join([target_database, target_table, "json"])), "w") as f:
json.dump(job, f)
def main(args):
target_database = ""
target_table = ""
options, arguments = getopt.getopt(args, '-d:-t:', ['targetdb=', 'targettbl='])
for opt_name, opt_value in options:
if opt_name in ('-d', '--targetdb'):
target_database = opt_value
if opt_name in ('-t', '--targettbl'):
target_table = opt_value
generate_json(target_database, target_table)
if __name__ == '__main__':
main(sys.argv[1:])
安装Python Mysql驱动：sudo yum install -y MySQL-python
脚本使用说明：python gen_export_config.py -d database -t table
通过-d传入MySQL数据库名，-t传入MySQL表名，执行上述命令即可生成该表的DataX同步配置文件。
2）创建批量生成所有表配置文件的脚本
脚本内容如下：
#!/bin/bash
python ~/bin/gen_export_config.py -d gmall_report -t ads_activity_stats
python ~/bin/gen_export_config.py -d gmall_report -t ads_coupon_stats
python ~/bin/gen_export_config.py -d gmall_report -t ads_new_buyer_stats
python ~/bin/gen_export_config.py -d gmall_report -t ads_order_by_province
python ~/bin/gen_export_config.py -d gmall_report -t ads_page_path
python ~/bin/gen_export_config.py -d gmall_report -t ads_repeat_purchase_by_tm
python ~/bin/gen_export_config.py -d gmall_report -t ads_sku_cart_num_top3_by_cate
python ~/bin/gen_export_config.py -d gmall_report -t ads_trade_stats
python ~/bin/gen_export_config.py -d gmall_report -t ads_trade_stats_by_cate
python ~/bin/gen_export_config.py -d gmall_report -t ads_trade_stats_by_tm
python ~/bin/gen_export_config.py -d gmall_report -t ads_traffic_stats_by_channel
python ~/bin/gen_export_config.py -d gmall_report -t ads_user_action
python ~/bin/gen_export_config.py -d gmall_report -t ads_user_change
python ~/bin/gen_export_config.py -d gmall_report -t ads_user_retention
python ~/bin/gen_export_config.py -d gmall_report -t ads_user_stats
为脚本增加执行权限：chmod +x ~/bin/gen_export_config.sh
使用方式：gen_export_config.sh
2.3 DataX配置文件的执行
执行命令：
python /opt/module/datax/bin/datax.py -p"-DexportDir=/warehouse/gmall/ads/ads_traffic_stats_by_channel" /opt/module/datax/job/export/gmall_report.ads_traffic_stats_by_channel.json
2.4 每日数据导出脚本
因为每天都要将统计结果导出到MySQL中，所以编写一个脚本，脚本内容如下：
#! /bin/bash
DATAX_HOME=/opt/module/datax
#DataX导出路径不允许存在空文件，该函数作用为清理空文件
handle_export_path(){
for i in `hadoop fs -ls -R $1 | awk '{print $8}'`; do
hadoop fs -test -z $i
if [[ $? -eq 0 ]]; then
echo "$i文件大小为0，正在删除"
hadoop fs -rm -r -f $i
fi
done
}
#数据导出
export_data() {
datax_config=$1
export_dir=$2
handle_export_path $export_dir
$DATAX_HOME/bin/datax.py -p"-Dexportdir=$export_dir" $datax_config
}
case $1 in
"ads_new_buyer_stats")
export_data /opt/module/datax/job/export/gmall_report.ads_new_buyer_stats.json /warehouse/gmall/ads/ads_new_buyer_stats
;;
"ads_order_by_province")
export_data /opt/module/datax/job/export/gmall_report.ads_order_by_province.json /warehouse/gmall/ads/ads_order_by_province
;;
"ads_page_path")
export_data /opt/module/datax/job/export/gmall_report.ads_page_path.json /warehouse/gmall/ads/ads_page_path
;;
"ads_repeat_purchase_by_tm")
export_data /opt/module/datax/job/export/gmall_report.ads_repeat_purchase_by_tm.json /warehouse/gmall/ads/ads_repeat_purchase_by_tm
;;
"ads_trade_stats")
export_data /opt/module/datax/job/export/gmall_report.ads_trade_stats.json /warehouse/gmall/ads/ads_trade_stats
;;
"ads_trade_stats_by_cate")
export_data /opt/module/datax/job/export/gmall_report.ads_trade_stats_by_cate.json /warehouse/gmall/ads/ads_trade_stats_by_cate
;;
"ads_trade_stats_by_tm")
export_data /opt/module/datax/job/export/gmall_report.ads_trade_stats_by_tm.json /warehouse/gmall/ads/ads_trade_stats_by_tm
;;
"ads_traffic_stats_by_channel")
export_data /opt/module/datax/job/export/gmall_report.ads_traffic_stats_by_channel.json /warehouse/gmall/ads/ads_traffic_stats_by_channel
;;
"ads_user_action")
export_data /opt/module/datax/job/export/gmall_report.ads_user_action.json /warehouse/gmall/ads/ads_user_action
;;
"ads_user_change")
export_data /opt/module/datax/job/export/gmall_report.ads_user_change.json /warehouse/gmall/ads/ads_user_change
;;
"ads_user_retention")
export_data /opt/module/datax/job/export/gmall_report.ads_user_retention.json /warehouse/gmall/ads/ads_user_retention
;;
"ads_user_stats")
export_data /opt/module/datax/job/export/gmall_report.ads_user_stats.json /warehouse/gmall/ads/ads_user_stats
;;
"ads_activity_stats")
export_data /opt/module/datax/job/export/gmall_report.ads_activity_stats.json /warehouse/gmall/ads/ads_activity_stats
;;
"ads_coupon_stats")
export_data /opt/module/datax/job/export/gmall_report.ads_coupon_stats.json /warehouse/gmall/ads/ads_coupon_stats
;;
"ads_sku_cart_num_top3_by_cate")
export_data /opt/module/datax/job/export/gmall_report.ads_sku_cart_num_top3_by_cate.json /warehouse/gmall/ads/ads_sku_cart_num_top3_by_cate
;;
"all")
export_data /opt/module/datax/job/export/gmall_report.ads_new_buyer_stats.json /warehouse/gmall/ads/ads_new_buyer_stats
export_data /opt/module/datax/job/export/gmall_report.ads_order_by_province.json /warehouse/gmall/ads/ads_order_by_province
export_data /opt/module/datax/job/export/gmall_report.ads_page_path.json /warehouse/gmall/ads/ads_page_path
export_data /opt/module/datax/job/export/gmall_report.ads_repeat_purchase_by_tm.json /warehouse/gmall/ads/ads_repeat_purchase_by_tm
export_data /opt/module/datax/job/export/gmall_report.ads_trade_stats.json /warehouse/gmall/ads/ads_trade_stats
export_data /opt/module/datax/job/export/gmall_report.ads_trade_stats_by_cate.json /warehouse/gmall/ads/ads_trade_stats_by_cate
export_data /opt/module/datax/job/export/gmall_report.ads_trade_stats_by_tm.json /warehouse/gmall/ads/ads_trade_stats_by_tm
export_data /opt/module/datax/job/export/gmall_report.ads_traffic_stats_by_channel.json /warehouse/gmall/ads/ads_traffic_stats_by_channel
export_data /opt/module/datax/job/export/gmall_report.ads_user_action.json /warehouse/gmall/ads/ads_user_action
export_data /opt/module/datax/job/export/gmall_report.ads_user_change.json /warehouse/gmall/ads/ads_user_change
export_data /opt/module/datax/job/export/gmall_report.ads_user_retention.json /warehouse/gmall/ads/ads_user_retention
export_data /opt/module/datax/job/export/gmall_report.ads_user_stats.json /warehouse/gmall/ads/ads_user_stats
export_data /opt/module/datax/job/export/gmall_report.ads_activity_stats.json /warehouse/gmall/ads/ads_activity_stats
export_data /opt/module/datax/job/export/gmall_report.ads_coupon_stats.json /warehouse/gmall/ads/ads_coupon_stats
export_data /opt/module/datax/job/export/gmall_report.ads_sku_cart_num_top3_by_cate.json /warehouse/gmall/ads/ads_sku_cart_num_top3_by_cate
;;
esac
增加脚本执行权限：chmod +x hdfs_to_mysql.sh
脚本使用方法：hdfs_to_mysql.sh all
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
