关于数据仓库:尚硅谷数仓实战之3数仓搭建

@TOC

数仓笔记

数据仓库和数据集市详解：ODS、DW、DWD、DWM、DWS、ADS

尚硅谷数仓实战之 1 我的项目需要及架构设计

尚硅谷数仓实战之 2 数仓分层 + 维度建模

尚硅谷数仓实战之 3 数仓搭建

尚硅谷数据仓库 4.0 视频教程

B 站中转：2021 新版电商数仓 V4.0 丨大数据数据仓库我的项目实战
百度网盘：https://pan.baidu.com/s/1FGUb…，提取码：yyds
阿里云盘：https://www.aliyundrive.com/s…，提取码：335o

1）保持数据原貌不做任何批改，起到备份数据的作用。

2）数据采纳 LZO 压缩，缩小磁盘存储空间。100G 数据能够压缩到 10G 以内。

3）创立分区表，避免后续的全表扫描，在企业开发中大量应用分区表。

4）创立内部表。在企业开发中，除了本人用的长期表，创立外部表外，绝大多数场景都是创立内部表。

ODS 层业务表分区规划如下

ODS 层业务表数据装载思路如下

DROP TABLE IF EXISTS ods_activity_info;

CREATE EXTERNAL TABLE ods_activity_info(

  `id` STRING COMMENT '编号',

  `activity_name` STRING  COMMENT '流动名称',

  `activity_type` STRING  COMMENT '流动类型',

  `start_time` STRING  COMMENT '开始工夫',

  `end_time` STRING  COMMENT '完结工夫',

  `create_time` STRING  COMMENT '创立工夫'

) COMMENT '流动信息表'

PARTITIONED BY (`dt` STRING)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

STORED AS

 INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'

 OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

LOCATION '/warehouse/gmall/ods/ods_activity_info/';

1. 建表语句

DROP TABLE IF EXISTS dim_sku_info;

CREATE EXTERNAL TABLE dim_sku_info (

  `id` STRING COMMENT '商品 id',

  `price` DECIMAL(16,2) COMMENT '商品价格',

  `sku_name` STRING COMMENT '商品名称',

  `sku_desc` STRING COMMENT '商品形容',

  `weight` DECIMAL(16,2) COMMENT '分量',

  `is_sale` BOOLEAN COMMENT '是否在售',

  `spu_id` STRING COMMENT 'spu 编号',

  `spu_name` STRING COMMENT 'spu 名称',

  `category3_id` STRING COMMENT '三级分类 id',

  `category3_name` STRING COMMENT '三级分类名称',

  `category2_id` STRING COMMENT '二级分类 id',

  `category2_name` STRING COMMENT '二级分类名称',

  `category1_id` STRING COMMENT '一级分类 id',

  `category1_name` STRING COMMENT '一级分类名称',

  `tm_id` STRING COMMENT '品牌 id',

  `tm_name` STRING COMMENT '品牌名称',

  `sku_attr_values` ARRAY<STRUCT<attr_id:STRING,value_id:STRING,attr_name:STRING,value_name:STRING>> COMMENT '平台属性',

  `sku_sale_attr_values` ARRAY<STRUCT<sale_attr_id:STRING,sale_attr_value_id:STRING,sale_attr_name:STRING,sale_attr_value_name:STRING>> COMMENT '销售属性',

  `create_time` STRING COMMENT '创立工夫'

) COMMENT '商品维度表'

PARTITIONED BY (`dt` STRING)

STORED AS PARQUET

LOCATION '/warehouse/gmall/dim/dim_sku_info/'

TBLPROPERTIES ("parquet.compression"="lzo");

2. 分区规划

3. 数据装载

1）什么是拉链表

2）为什么要做拉链表

3）如何应用拉链表

4）拉链表造成过程

1. 建表语句

DROP TABLE IF EXISTS dim_user_info;

CREATE EXTERNAL TABLE dim_user_info(

  `id` STRING COMMENT '用户 id',

  `login_name` STRING COMMENT '用户名称',

  `nick_name` STRING COMMENT '用户昵称',

  `name` STRING COMMENT '用户姓名',

  `phone_num` STRING COMMENT '手机号码',

  `email` STRING COMMENT '邮箱',

  `user_level` STRING COMMENT '用户等级',

  `birthday` STRING COMMENT '生日',

  `gender` STRING COMMENT '性别',

  `create_time` STRING COMMENT '创立工夫',

  `operate_time` STRING COMMENT '操作工夫',

  `start_date` STRING COMMENT '开始日期',

  `end_date` STRING COMMENT '完结日期'

) COMMENT '用户表'

PARTITIONED BY (`dt` STRING)

STORED AS PARQUET

LOCATION '/warehouse/gmall/dim/dim_user_info/'

TBLPROPERTIES ("parquet.compression"="lzo");

2. 分区规划

3. 数据装载

1）首日装载

拉链表首日装载，须要进行初始化操作，具体工作为将截止到初始化当日的全副历史用户导入一次性导入到拉链表中。目前的 ods_user_info 表的第一个分区，即 2020-06-14 分区中就是全副的历史用户，故将该分区数据进行肯定解决后导入拉链表的 9999-99-99 分区即可。

2）每日装载

（1）实现思路

1）对用户行为数据解析。

2）对业务数据采纳维度模型从新建模。

1）日志构造回顾

（1）页面埋点日志

（2）启动日志

2）日志解析思路

Mysql 中也有响应的 JSON 处理函数，不过性能没有 hive 高。

启动日志解析思路：启动日志表中每行数据对应一个启动记录，一个启动记录应该蕴含日志中的公共信息和启动信息。先将所有蕴含 start 字段的日志过滤出来，而后应用 get_json_object 函数解析每个字段。

1）建表语句

DROP TABLE IF EXISTS dwd_start_log;

CREATE EXTERNAL TABLE dwd_start_log(

  `area_code` STRING COMMENT '地区编码',

  `brand` STRING COMMENT '手机品牌',

  `channel` STRING COMMENT '渠道',

  `is_new` STRING COMMENT '是否首次启动',

  `model` STRING COMMENT '手机型号',

  `mid_id` STRING COMMENT '设施 id',

  `os` STRING COMMENT '操作系统',

  `user_id` STRING COMMENT '会员 id',

  `version_code` STRING COMMENT 'app 版本号',

  `entry` STRING COMMENT 'icon 手机图标 notice 告诉 install 装置后启动',

  `loading_time` BIGINT COMMENT '启动加载工夫',

  `open_ad_id` STRING COMMENT '广告页 ID',

  `open_ad_ms` BIGINT COMMENT '广告总共播放工夫',

  `open_ad_skip_ms` BIGINT COMMENT '用户跳过广告时点',

  `ts` BIGINT COMMENT '工夫'

) COMMENT '启动日志表'

PARTITIONED BY (`dt` STRING) -- 依照工夫创立分区

STORED AS PARQUET -- 采纳 parquet 列式存储

LOCATION '/warehouse/gmall/dwd/dwd_start_log' -- 指定在 HDFS 上存储地位

TBLPROPERTIES('parquet.compression'='lzo') -- 采纳 LZO 压缩

;

页面日志解析思路：页面日志表中每行数据对应一个页面拜访记录，一个页面拜访记录应该蕴含日志中的公共信息和页面信息。先将所有蕴含 page 字段的日志过滤出来，而后应用 get_json_object 函数解析每个字段。

动作日志解析思路：动作日志表中每行数据对应用户的一个动作记录，一个动作记录该当蕴含公共信息、页面信息以及动作信息。先将蕴含 action 字段的日志过滤出来，而后通过 UDTF 函数，将 action 数组“炸开”（相似于 explode 函数的成果），而后应用 get_json_object 函数解析每个字段。

谬误日志解析思路：谬误日志表中每行数据对应一个谬误记录，为不便定位谬误，一个谬误记录该当蕴含与之对应的公共信息、页面信息、曝光信息、动作信息、启动信息以及错误信息。先将蕴含 err 字段的日志过滤出来，而后应用 get_json_object 函数解析所有字段。

业务数据方面 DWD 层的搭建次要留神点在于维度建模。这边只是展现一个订单明细的实例，全副的文档请下载尚硅谷数仓实战笔记。

1）建表语句

DROP TABLE IF EXISTS dwd_order_detail;

CREATE EXTERNAL TABLE dwd_order_detail (

  `id` STRING COMMENT '订单编号',

  `order_id` STRING COMMENT '订单号',

  `user_id` STRING COMMENT '用户 id',

  `sku_id` STRING COMMENT 'sku 商品 id',

  `province_id` STRING COMMENT '省份 ID',

  `activity_id` STRING COMMENT '流动 ID',

  `activity_rule_id` STRING COMMENT '流动规定 ID',

  `coupon_id` STRING COMMENT '优惠券 ID',

  `create_time` STRING COMMENT '创立工夫',

  `source_type` STRING COMMENT '起源类型',

  `source_id` STRING COMMENT '起源编号',

  `sku_num` BIGINT COMMENT '商品数量',

  `original_amount` DECIMAL(16,2) COMMENT '原始价格',

  `split_activity_amount` DECIMAL(16,2) COMMENT '流动优惠摊派',

  `split_coupon_amount` DECIMAL(16,2) COMMENT '优惠券优惠摊派',

  `split_final_amount` DECIMAL(16,2) COMMENT '最终价格摊派'

) COMMENT '订单明细事实表表'

PARTITIONED BY (`dt` STRING)

STORED AS PARQUET

LOCATION '/warehouse/gmall/dwd/dwd_order_detail/'

TBLPROPERTIES ("parquet.compression"="lzo");

2）分区规划

3）数据装载

1）建表语句

DROP TABLE IF EXISTS dwd_cart_info;
CREATE EXTERNAL TABLE dwd_cart_info(
  `id` STRING COMMENT '编号',
  `user_id` STRING COMMENT '用户 ID',
  `sku_id` STRING COMMENT '商品 ID',
  `source_type` STRING COMMENT '起源类型',
  `source_id` STRING COMMENT '起源编号',
  `cart_price` DECIMAL(16,2) COMMENT '退出购物车时的价格',
  `is_ordered` STRING COMMENT '是否已下单',
  `create_time` STRING COMMENT '创立工夫',
  `operate_time` STRING COMMENT '批改工夫',
  `order_time` STRING COMMENT '下单工夫',
  `sku_num` BIGINT COMMENT '加购数量'
) COMMENT '加购事实表'
PARTITIONED BY (`dt` STRING)
STORED AS PARQUET
LOCATION '/warehouse/gmall/dwd/dwd_cart_info/'
TBLPROPERTIES ("parquet.compression"="lzo");

2）分区规划

3）数据装载

1）建表语句

DROP TABLE IF EXISTS dwd_payment_info;

CREATE EXTERNAL TABLE dwd_payment_info (

  `id` STRING COMMENT '编号',

  `order_id` STRING COMMENT '订单编号',

  `user_id` STRING COMMENT '用户编号',

  `province_id` STRING COMMENT '地区 ID',

  `trade_no` STRING COMMENT '交易编号',

  `out_trade_no` STRING COMMENT '对外交易编号',

  `payment_type` STRING COMMENT '领取类型',

  `payment_amount` DECIMAL(16,2) COMMENT '领取金额',

  `payment_status` STRING COMMENT '领取状态',

  `create_time` STRING COMMENT '创立工夫',-- 调用第三方领取接口的工夫

  `callback_time` STRING COMMENT '实现工夫'-- 领取实现工夫，即领取胜利回调工夫

) COMMENT '领取事实表表'

PARTITIONED BY (`dt` STRING)

STORED AS PARQUET

LOCATION '/warehouse/gmall/dwd/dwd_payment_info/'

TBLPROPERTIES ("parquet.compression"="lzo");

2）分区规划

3）数据装载

1）建表语句

DROP TABLE IF EXISTS dws_visitor_action_daycount;

CREATE EXTERNAL TABLE dws_visitor_action_daycount

(

  `mid_id` STRING COMMENT '设施 id',

  `brand` STRING COMMENT '设施品牌',

  `model` STRING COMMENT '设施型号',

  `is_new` STRING COMMENT '是否首次拜访',

  `channel` ARRAY<STRING> COMMENT '渠道',

  `os` ARRAY<STRING> COMMENT '操作系统',

  `area_code` ARRAY<STRING> COMMENT '地区 ID',

  `version_code` ARRAY<STRING> COMMENT '利用版本',

  `visit_count` BIGINT COMMENT '拜访次数',

  `page_stats` ARRAY<STRUCT<page_id:STRING,page_count:BIGINT,during_time:BIGINT>> COMMENT '页面拜访统计'

) COMMENT '每日设施行为表'

PARTITIONED BY(`dt` STRING)

STORED AS PARQUET

LOCATION '/warehouse/gmall/dws/dws_visitor_action_daycount'

TBLPROPERTIES ("parquet.compression"="lzo");

ADS 层不波及建模，建表依据具体需要而定。

该需要为用户综合统计，其中蕴含若干指标，以下为对每个指标的解释阐明。

指标	阐明	对应字段
新增用户数	统计新增注册用户人数	new_user_count
新增下单用户数	统计新增下单用户人数	new_order_user_count
下单总金额	统计所有订单总额	order_final_amount
下单用户数	统计下单用户总数	order_user_count
未下单用户数	统计沉闷但未下单用户数	no_order_user_count

1. 建表语句

DROP TABLE IF EXISTS ads_user_total;

CREATE EXTERNAL TABLE `ads_user_total` (

 `dt` STRING COMMENT '统计日期',

 `recent_days` BIGINT COMMENT '最近天数,0: 累积值,1: 最近 1 天,7: 最近 7 天,30: 最近 30 天',

 `new_user_count` BIGINT COMMENT '新注册用户数',

 `new_order_user_count` BIGINT COMMENT '新增下单用户数',

 `order_final_amount` DECIMAL(16,2) COMMENT '下单总金额',

 `order_user_count` BIGINT COMMENT '下单用户数',

 `no_order_user_count` BIGINT COMMENT '未下单用户数(具体指沉闷用户中未下单用户)'

) COMMENT '用户统计'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

LOCATION '/warehouse/gmall/ads/ads_user_total/';

该需要包含两个指标，别离为散失用户数和回流用户数，以下为对两个指标的解释阐明。

指标	阐明	对应字段
散失用户数	之前沉闷过的用户，最近一段时间未沉闷，就称为散失用户。此处要求统计 7 日前（只蕴含 7 日前当天）沉闷，但最近 7 日未沉闷的用户总数。	user_churn_count
回流用户数	之前的沉闷用户，一段时间未沉闷（散失），今日又沉闷了，就称为回流用户。此处要求统计回流用户总数。	new_order_user_count

1. 建表语句

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/';

漏斗剖析是一个数据分析模型，它可能迷信反映一个业务过程从终点到起点各阶段用户转化状况。因为其能将各阶段环节都展现进去，故哪个阶段存在问题，就能高深莫测。

该需要要求统计一个残缺的购物流程各个阶段的人数。

1. 建表语句

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/';

留存剖析个别蕴含新增留存和沉闷留存剖析。

新增留存剖析是剖析某天的新增用户中，有多少人有后续的沉闷行为。沉闷留存剖析是剖析某天的沉闷用户中，有多少人有后续的沉闷行为。

留存剖析是掂量产品对用户价值高下的重要指标。

此处要求统计新增留存率，新增留存率具体是指留存用户数与新增用户数的比值，例如 2020-06-14 新增 100 个用户，1 日之后（2020-06-15）这 100 人中有 80 集体沉闷了，那 2020-06-14 的 1 日留存数则为 80，2020-06-14 的 1 日留存率则为 80%。

要求统计每天的 1 至 7 日留存率，如下图所示。

1. 建表语句

DROP TABLE IF EXISTS ads_user_retention;

CREATE EXTERNAL TABLE ads_user_retention (

 `dt` STRING COMMENT '统计日期',

 `create_date` STRING COMMENT '用户新增日期',

 `retention_day` BIGINT 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/';

该需要蕴含订单总数，订单总金额和下单总人数。

1. 建表语句

DROP TABLE IF EXISTS ads_order_total;

CREATE EXTERNAL TABLE `ads_order_total` (

 `dt` STRING COMMENT '统计日期',

 `recent_days` BIGINT COMMENT '最近天数,1: 最近 1 天,7: 最近 7 天,30: 最近 30 天',

 `order_count` BIGINT COMMENT '订单数',

 `order_amount` DECIMAL(16,2) COMMENT '订单金额',

 `order_user_count` BIGINT COMMENT '下单人数'

) COMMENT '订单统计'

ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t'

LOCATION '/warehouse/gmall/ads/ads_order_total/';

该需要蕴含各省份订单总数和订单总金额。

1. 建表语句

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_amount` DECIMAL(16,2) COMMENT '订单金额'

) COMMENT '各地区订单统计'

ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t'

LOCATION '/warehouse/gmall/ads/ads_order_by_province/';

关于数据仓库:尚硅谷数仓实战之3数仓搭建

第 4 章数仓搭建 -ODS 层

4.2 ODS 层（业务数据）

4.2.1 流动信息表

第 5 章数仓搭建 -DIM 层

5.1 商品维度表（全量）

5.6 用户维度表（拉链表）

5.6.1 拉链表概述

5.6.2 制作拉链表

第 6 章数仓搭建 -DWD 层

6.1 DWD 层（用户行为日志）

6.1.1 日志解析思路

6.1.2 json_object 函数应用

6.1.3 启动日志表

6.1.4 页面日志表

6.1.5 动作日志表

6.1.7 谬误日志表

6.2 DWD 层（业务数据）

6.2.2 订单明细事实表（事务型事实表）

6.2.4 加购事实表（周期型快照事实表，每日快照）

6.2.7 领取事实表（累积型快照事实表）

第 7 章数仓搭建 -DWS 层

7.2 DWS 层

7.2.1 访客主题

第 8 章数仓搭建 -DWT 层

第 9 章数仓搭建 -ADS 层

9.1 建表阐明

9.3 用户主题

9.3.1 用户统计

9.3.2 用户变动统计

9.3.3 用户行为漏斗剖析

9.3.4 用户留存率

9.5 订单主题

9.5.1 订单统计

9.5.2 各地区订单统计

第 10 章全流程调度

第 4 章 数仓搭建 -ODS 层

4.2 ODS 层（业务数据）

4.2.1 流动信息表

第 5 章 数仓搭建 -DIM 层

5.1 商品维度表（全量）

5.6 用户维度表（拉链表）

5.6.1 拉链表概述

5.6.2 制作拉链表

第 6 章 数仓搭建 -DWD 层

6.1 DWD 层（用户行为日志）

6.1.1 日志解析思路

6.1.2 json_object 函数应用

6.1.3 启动日志表

6.1.4 页面日志表

6.1.5 动作日志表

6.1.7 谬误日志表

6.2 DWD 层（业务数据）

6.2.2 订单明细事实表（事务型事实表）

6.2.4 加购事实表（周期型快照事实表，每日快照）

6.2.7 领取事实表（累积型快照事实表）

第 7 章 数仓搭建 -DWS 层

7.2 DWS 层

7.2.1 访客主题

第 8 章 数仓搭建 -DWT 层

第 9 章 数仓搭建 -ADS 层

9.1 建表阐明

9.3 用户主题

9.3.1 用户统计

9.3.2 用户变动统计

9.3.3 用户行为漏斗剖析

9.3.4 用户留存率

9.5 订单主题

9.5.1 订单统计

9.5.2 各地区订单统计

第 10 章 全流程调度

第 4 章数仓搭建 -ODS 层

第 5 章数仓搭建 -DIM 层

第 6 章数仓搭建 -DWD 层

第 7 章数仓搭建 -DWS 层

第 8 章数仓搭建 -DWT 层

第 9 章数仓搭建 -ADS 层

第 10 章全流程调度