@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
第4章 数仓搭建-ODS层
1）保持数据原貌不做任何批改，起到备份数据的作用。
2）数据采纳LZO压缩，缩小磁盘存储空间。100G数据能够压缩到10G以内。
3）创立分区表，避免后续的全表扫描，在企业开发中大量应用分区表。
4）创立内部表。在企业开发中，除了本人用的长期表，创立外部表外，绝大多数场景都是创立内部表。
4.2 ODS层（业务数据）
ODS层业务表分区规划如下
ODS层业务表数据装载思路如下
4.2.1 流动信息表
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/';第5章 数仓搭建-DIM层
5.1 商品维度表（全量）
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 PARQUETLOCATION '/warehouse/gmall/dim/dim_sku_info/'TBLPROPERTIES ("parquet.compression"="lzo");2.分区规划
3.数据装载
5.6 用户维度表（拉链表）
5.6.1 拉链表概述
1）什么是拉链表
2）为什么要做拉链表
3）如何应用拉链表
4）拉链表造成过程
5.6.2 制作拉链表
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 PARQUETLOCATION '/warehouse/gmall/dim/dim_user_info/'TBLPROPERTIES ("parquet.compression"="lzo");2.分区规划
3.数据装载
1）首日装载
拉链表首日装载，须要进行初始化操作，具体工作为将截止到初始化当日的全副历史用户导入一次性导入到拉链表中。目前的ods_user_info表的第一个分区，即2020-06-14分区中就是全副的历史用户，故将该分区数据进行肯定解决后导入拉链表的9999-99-99分区即可。
2）每日装载
（1）实现思路
第6章 数仓搭建-DWD层
1）对用户行为数据解析。
2）对业务数据采纳维度模型从新建模。
6.1 DWD层（用户行为日志）
6.1.1 日志解析思路
1）日志构造回顾
（1）页面埋点日志
（2）启动日志
2）日志解析思路
6.1.2 json_object函数应用
Mysql中也有响应的JSON处理函数，不过性能没有hive高。
6.1.3 启动日志表
启动日志解析思路：启动日志表中每行数据对应一个启动记录，一个启动记录应该蕴含日志中的公共信息和启动信息。先将所有蕴含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压缩;6.1.4 页面日志表
页面日志解析思路：页面日志表中每行数据对应一个页面拜访记录，一个页面拜访记录应该蕴含日志中的公共信息和页面信息。先将所有蕴含page字段的日志过滤出来，而后应用get_json_object函数解析每个字段。
6.1.5 动作日志表
动作日志解析思路：动作日志表中每行数据对应用户的一个动作记录，一个动作记录该当蕴含公共信息、页面信息以及动作信息。先将蕴含action字段的日志过滤出来，而后通过UDTF函数，将action数组“炸开”（相似于explode函数的成果），而后应用get_json_object函数解析每个字段。
6.1.7 谬误日志表
谬误日志解析思路：谬误日志表中每行数据对应一个谬误记录，为不便定位谬误，一个谬误记录该当蕴含与之对应的公共信息、页面信息、曝光信息、动作信息、启动信息以及错误信息。先将蕴含err字段的日志过滤出来，而后应用get_json_object函数解析所有字段。
6.2 DWD层（业务数据）
业务数据方面DWD层的搭建次要留神点在于维度建模。这边只是展现一个订单明细的实例，全副的文档请下载尚硅谷数仓实战笔记。
6.2.2 订单明细事实表（事务型事实表）
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 PARQUETLOCATION '/warehouse/gmall/dwd/dwd_order_detail/'TBLPROPERTIES ("parquet.compression"="lzo");2）分区规划
3）数据装载
6.2.4 加购事实表（周期型快照事实表，每日快照）
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 PARQUETLOCATION '/warehouse/gmall/dwd/dwd_cart_info/'TBLPROPERTIES ("parquet.compression"="lzo");2）分区规划
3）数据装载
6.2.7 领取事实表（累积型快照事实表）
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 PARQUETLOCATION '/warehouse/gmall/dwd/dwd_payment_info/'TBLPROPERTIES ("parquet.compression"="lzo");2）分区规划
3）数据装载
第7章 数仓搭建-DWS层
7.2 DWS层
7.2.1 访客主题
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 PARQUETLOCATION '/warehouse/gmall/dws/dws_visitor_action_daycount'TBLPROPERTIES ("parquet.compression"="lzo");第8章 数仓搭建-DWT层
第9章 数仓搭建-ADS层
9.1 建表阐明
ADS层不波及建模，建表依据具体需要而定。
9.3 用户主题
9.3.1 用户统计
该需要为用户综合统计，其中蕴含若干指标，以下为对每个指标的解释阐明。
| 指标 | 阐明 | 对应字段 |
|---|---|---|
| 新增用户数 | 统计新增注册用户人数 | 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/';9.3.2 用户变动统计
该需要包含两个指标，别离为散失用户数和回流用户数，以下为对两个指标的解释阐明。
| 指标 | 阐明 | 对应字段 |
|---|---|---|
| 散失用户数 | 之前沉闷过的用户，最近一段时间未沉闷，就称为散失用户。此处要求统计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/';9.3.3 用户行为漏斗剖析
漏斗剖析是一个数据分析模型，它可能迷信反映一个业务过程从终点到起点各阶段用户转化状况。因为其能将各阶段环节都展现进去，故哪个阶段存在问题，就能高深莫测。
该需要要求统计一个残缺的购物流程各个阶段的人数。
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/';9.3.4 用户留存率
留存剖析个别蕴含新增留存和沉闷留存剖析。
新增留存剖析是剖析某天的新增用户中，有多少人有后续的沉闷行为。沉闷留存剖析是剖析某天的沉闷用户中，有多少人有后续的沉闷行为。
留存剖析是掂量产品对用户价值高下的重要指标。
此处要求统计新增留存率，新增留存率具体是指留存用户数与新增用户数的比值，例如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/';9.5 订单主题
9.5.1 订单统计
该需要蕴含订单总数，订单总金额和下单总人数。
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/';9.5.2 各地区订单统计
该需要蕴含各省份订单总数和订单总金额。
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/';