7.ADS层数据导出

本文详细介绍了如何将ADS层的报表数据从Hive导出到MySQL,包括数据库和表的创建,DataX配置文件的编写与执行,以及每日数据导出的自动化脚本。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


前言

前面完成了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
### 如何在ADS中批量导出多条曲线数据 #### 准备工作 为了实现从ADS数据应用)中批量导出多条曲线数据,需先确认所使用的工具和技术栈支持该操作。通常情况下,这类操作涉及数据库查询、API接口调用或是特定BI工具的功能。 #### 方法一:通过SQL脚本自动化处理 如果目标平台允许执行自定义SQL语句,则可以通过编写SQL脚本来完成这一需求。此方法适用于具有编程能力和一定权限的操作人员。具体做法如下: ```sql WITH RECURSIVE curve_ids AS ( SELECT id FROM curves WHERE condition -- 替换为具体的筛选条件 ), curve_data AS ( SELECT c.id, d.timestamp, d.value FROM curve_ids cid JOIN data_points d ON cid.id = d.curve_id ) SELECT * INTO OUTFILE '/tmp/curves.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM curve_data; ``` 这段代码首先构建了一个包含所需曲线ID列表的临时表`curve_ids`,接着将其与实际存储数值的时间序列表格连接起来形成完整的记录集合`curve_data`,最后利用MySQL特有的`INTO OUTFILE`语法将结果保存至文件系统中的CSV文档[^1]。 #### 方法二:借助BI工具功能模块 现代商业智能(BI)解决方案一般都提供了图形化界面来简化复杂的数据提取流程。对于不具备深厚技术背景但熟悉业务逻辑的人来说尤为适用。以Tableau为例,用户可以在可视化编辑器内选定多个指标作为Y轴变量,并指定X轴代表时间或其他连续量级单位,随后一键生成并下载所需的图表组合图像或Excel格式的工作簿[^2]。 #### 方法三:RESTful API请求方式 当面对的是一个开放式的Web应用程序时,还可以考虑采用HTTP协议下的GET/POST命令发起远程过程调用来获取结构化的JSON响应体。这种方式灵活性最高但也要求开发者具备一定的网络通信知识以及对目的站点API文档的理解程度。下面给出Python语言版本的一个简单实例: ```python import requests from datetime import date start_date = '2023-01-01' end_date = str(date.today()) url_template = "https://example.com/api/v1/timeseries?metric={}&startDate={}&endDate={}" metrics_of_interest = ['cpu_usage', 'memory_consumption'] for metric in metrics_of_interest: response = requests.get(url_template.format(metric, start_date, end_date)) with open(f'{metric}.json', mode='w') as f: json.dump(response.json(), fp=f) print('All done.') ``` 上述片段展示了如何循环遍历一系列感兴趣的度量名称,并依次发送带有相应参数配置项的URL地址到服务器端口处等待回应;一旦接收到有效载荷即刻解析成易于人类阅读的形式存入本地磁盘里去[^4]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值