数据仓库哈哈

数据仓库

基本概念
- 数据库（database）和数据仓库（Data Warehouse）的异同
整体架构
分层架构
- 方法论
- - ER模型（建模理论）
  - 维度模型
- 何为分层
- 第一层：数据源（ODS ER模型）
- - 设计要点
  - 日志表
  - 业务表
  - - 1活动信息表（全量表）
    - 2活动规则表（全量表）
    - 3一级品类表（全量表）
    - 4二级品类表（全量表）
    - 5三级品类表（全量表）
    - 6编码字典表（全量表）
    - 7省份表（全量表）
    - 8地区表（全量表）
    - 9品牌表（全量表）
    - 10购物车表（全量表）
    - 11优惠券信息表（全量表）
    - 12商品平台属性表（全量表）
    - 13商品表（全量表）
    - 14商品销售属性值表（全量表）
    - 15SPU表（全量表）
    - 16营销坑位表（全量表）
    - 17营销渠道表（全量表）
    - 18购物车表（增量表）
    - 19评论表（增量表）
    - 20优惠券领用表（增量表）
    - 21收藏表（增量表）
    - 22订单明细表（增量表）
    - 23订单明细活动关联表（增量表）
    - 24订单明细优惠券关联表（增量表）
    - 25订单表（增量表）
    - 26退单表（增量表）
    - 27订单状态流水表（增量表）
    - 28支付表（增量表）
    - 29退款表（增量表）
    - 30用户表（增量表）
    - 31数据装载脚本
- 第二层：数据加工（DWD data warehouse detail）
- - - 事实表设计（事务型事实表）
  - 事务的原子性
  - 事实表设计（周期型快照事实表）
- 从当前表中取数据后再放回去需考虑去重问题，增加retry的容错性
- - 事实表设计（累积型快照事实表）
  - 分区策略
- 第三层：数据统计（DWS data warehouse summary 提高性能的关键层）
- 第四层：数据分析（ADS application data service）
- - 表的设计（要点）
  - 优化（假）
  - 1流量主题
  - - 1.1各渠道流量统计
- 第五层：共通层（DIM dimension）
- - 设计要点
  - 是否创建表
  - 维度表设计
  - - 1商品维度表
    - 2优惠券维度表
    - 3活动（规则）维度表
    - 4地区维度表
    - 5营销坑位维度表
    - 6营销渠道维度表
    - 7日期维度表
    - 8用户维度表（拉链（压缩）表）
- CTE : 共通表表达式
- 拉链表设计
- 任务调度器

基本概念

本质是对数据进行加工处理后对外提供数据服务

数据库（database）和数据仓库（Data Warehouse）的异同

数据库用于存储企业基础，核心的业务数据
从数据来源进行区分
- 数据库：企业的业务系统
- 数据仓库：数据库（后台的后台）
从数据存储进行区分
- 数据库：存储的目的为了可以快速进行数据查询操作
  索引： SQL
  存储方式：行式存储
  数据量：不能存储海量数据
- 数据仓库：存储的目的为了可以快速进行统计分析
  索引：没有索引（k-v）
  存储方式：列式存储
  数据量：必须存储海量数据
从数据价值进行区分
- 数据库：保障企业业务系统的执行
  事务（回滚）
- 数据仓库：统计分析的结果可以为企业的经营决策提供数据依据
  没有事务
  数据仓库不是数据流转的终点：可视化才是数据的终点

在这里插入图片描述

整体架构

Spark : 数据的统计分析
在这里插入图片描述
数据仓库：数据的统计分析

数据仓库不能直接对接MySQL数据库作为数据源！

数据库不是为了数据仓库服务的。数据仓库如果直接对象数据库，会导致数据库的性能降低
数据库不能存储海量数据。数据仓库必须获取海量数据
数据库采用行式存储。数据仓库为了提高统计分析效率，所以需要列式存储

数据仓库应该增加自己的数据源

在这里插入图片描述
数据仓库的数据源中的数据应该和MySQL数据库中的数据保持一致
数据仓库的数据源应该不断融合（汇总）MySQL数据库中的数据
将数据库的数据汇总的到数据仓库数据源的过程，一般称之为数据同步，也称之为数据采集
在这里插入图片描述

分层架构

数据仓库计算周期为1天：1天统计一回数据结果

方法论

ER模型（建模理论）

ER（Entity Relationship）（实体关系）模型
采用面向对象的方式设计表（和Java一样）

将对象理解为表
将对象之间的关系理解为表之间的关系

超详细内容（带图）看这里

维度模型

事实：行为所产生的事情（数据）
维度：分析数据的角度（状态）

超详细内容（带图）看这里

何为分层

Spark中的方法可能会含有shuffle功能，
shuffle操作会将完整的计算流程一分为二，会分为2个阶段（Stage）,前面一个阶段称之为Map阶段，后面的阶段称之为Reduce阶段，
shuffle中前一个阶段的任务不执行完，后面的阶段的任务不允许执行的，
Task Pool(任务池) - 任务调度（FIFO, FAIR）。

数据仓库也存在同样的问题，将整个计算流程分为了4段，
在数据仓库中不称之为段，一般称之为层，每一层有特殊的含义和特殊的功能
前面一层的数据没有处理完，后面一层的数据没有办法处理

在这里插入图片描述

第一层：数据源（ODS ER模型）

功能：

为整个数据仓库作为数据来源
不断汇总业务数据和日志数据
数据量非常大：海量数据 -> 考虑资源问题：使用最少的资源存储最多的资源（考虑使用压缩算法gzip、lzo、snappy）；考虑网络资源：考虑传输方式，数据尽可能不变（格式、压缩方式、存储方式）

统计本质上就是对行为数据进行统计
分析本质上就是站在什么角度对统计结果进行分析

-- ODS-- 1. ODS层表建模方式：ER模型-- 2. 数据格式不变，数据压缩方式 gzip-- 3. 表名-- 分层标记（ods_） + 同步数据的表名 + 增量/全量（inc/full）-- 增量，全量

在这里插入图片描述

设计要点

（1）ODS层的表结构设计依托于从业务系统同步过来的数据结构。
（2）ODS层要保存全部历史数据，故其压缩格式应选择压缩比较高的，此处选择gzip。
（3）ODS层表名的命名规范为：ods_表名_单分区增量全量标识（inc/full）。

日志表

1）建表语句

DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc
(`common` STRUCT<ar :STRING,ba :STRING,ch :STRING,is_new :STRING,md :STRING,mid :STRING,os :STRING,sid :STRING,uid :STRING,vc :STRING> COMMENT '公共信息',`page` STRUCT<during_time :STRING,item :STRING,item_type :STRING,last_page_id :STRING,page_id :STRING,from_pos_id :STRING,from_pos_seq :STRING,refer_id :STRING> COMMENT '页面信息',`actions` ARRAY<STRUCT<action_id:STRING,item:STRING,item_type:STRING,ts:BIGINT>> COMMENT '动作信息',`displays` ARRAY<STRUCT<display_type :STRING,item :STRING,item_type :STRING,`pos_seq` :STRING,pos_id :STRING>> COMMENT '曝光信息',`start` STRUCT<entry :STRING,first_open :BIGINT,loading_time :BIGINT,open_ad_id :BIGINT,open_ad_ms :BIGINT,open_ad_skip_ms :BIGINT> COMMENT '启动信息',`err` STRUCT<error_code:BIGINT,msg:STRING> COMMENT '错误信息',`ts` BIGINT  COMMENT '时间戳'
) COMMENT '活动信息表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_log_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

2）数据装载

load data inpath '/origin_data/gmall/log/topic_log/2022-06-08' into table ods_log_inc partition(dt='2022-06-08');

3）每日数据装载脚本
（1）在hadoop102的/home/atguigu/bin目录下创建hdfs_to_ods_log.sh

vim hdfs_to_ods_log.sh

（2）编写如下内容

#!/bin/bash# 定义变量方便修改
APP=gmall# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;thendo_date=$1
elsedo_date=`date -d "-1 day" +%F`
fiecho ================== 日志日期为 $do_date ==================
sql="
load data inpath '/origin_data/$APP/log/topic_log/$do_date' into table ${APP}.ods_log_inc partition(dt='$do_date');
"
hive -e "$sql"

（3）增加脚本执行权限

chmod +x hdfs_to_ods_log.sh

（4）脚本用法

 hdfs_to_ods_log.sh 2022-06-08

业务表

1活动信息表（全量表）

DROP TABLE IF EXISTS ods_activity_info_full;
CREATE EXTERNAL TABLE ods_activity_info_full
(`id`              STRING COMMENT '活动id',`activity_name` STRING COMMENT '活动名称',`activity_type` STRING COMMENT '活动类型',`activity_desc` STRING COMMENT '活动描述',`start_time`     STRING COMMENT '开始时间',`end_time`        STRING COMMENT '结束时间',`create_time`    STRING COMMENT '创建时间',`operate_time`   STRING COMMENT '修改时间'
) COMMENT '活动信息表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_activity_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

2活动规则表（全量表）

DROP TABLE IF EXISTS ods_activity_rule_full;
CREATE EXTERNAL TABLE ods_activity_rule_full
(`id`                  STRING COMMENT '编号',`activity_id`       STRING COMMENT '活动ID',`activity_type`     STRING COMMENT '活动类型',`condition_amount` DECIMAL(16, 2) COMMENT '满减金额',`condition_num`     BIGINT COMMENT '满减件数',`benefit_amount`    DECIMAL(16, 2) COMMENT '优惠金额',`benefit_discount` DECIMAL(16, 2) COMMENT '优惠折扣',`benefit_level`     STRING COMMENT '优惠级别',`create_time`       STRING COMMENT '创建时间',`operate_time`      STRING COMMENT '修改时间'
) COMMENT '活动规则表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_activity_rule_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

3一级品类表（全量表）

DROP TABLE IF EXISTS ods_base_category1_full;
CREATE EXTERNAL TABLE ods_base_category1_full
(`id`               STRING COMMENT '编号',`name`             STRING COMMENT '分类名称',`create_time`    STRING COMMENT '创建时间',`operate_time`   STRING COMMENT '修改时间'
) COMMENT '一级品类表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_base_category1_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

4二级品类表（全量表）

DROP TABLE IF EXISTS ods_base_category2_full;
CREATE EXTERNAL TABLE ods_base_category2_full
(`id`               STRING COMMENT '编号',`name`             STRING COMMENT '二级分类名称',`category1_id`   STRING COMMENT '一级分类编号',`create_time`    STRING COMMENT '创建时间',`operate_time`   STRING COMMENT '修改时间'
) COMMENT '二级品类表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_base_category2_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

5三级品类表（全量表）

DROP TABLE IF EXISTS ods_base_category3_full;
CREATE EXTERNAL TABLE ods_base_category3_full
(`id`               STRING COMMENT '编号',`name`             STRING COMMENT '三级分类名称',`category2_id`   STRING COMMENT '二级分类编号',`create_time`    STRING COMMENT '创建时间',`operate_time`   STRING COMMENT '修改时间'
) COMMENT '三级品类表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_base_category3_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

6编码字典表（全量表）

DROP TABLE IF EXISTS ods_base_dic_full;
CREATE EXTERNAL TABLE ods_base_dic_full
(`dic_code`     STRING COMMENT '编号',`dic_name`     STRING COMMENT '编码名称',`parent_code`  STRING COMMENT '父编号',`create_time`  STRING COMMENT '创建日期',`operate_time` STRING COMMENT '修改日期'
) COMMENT '编码字典表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_base_dic_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

7省份表（全量表）

DROP TABLE IF EXISTS ods_base_province_full;
CREATE EXTERNAL TABLE ods_base_province_full
(`id`              STRING COMMENT '编号',`name`            STRING COMMENT '省份名称',`region_id`      STRING COMMENT '地区ID',`area_code`      STRING COMMENT '地区编码',`iso_code`   STRING COMMENT '旧版国际标准地区编码，供可视化使用',`iso_3166_2` STRING COMMENT '新版国际标准地区编码，供可视化使用',`create_time`    STRING COMMENT '创建时间',`operate_time`   STRING COMMENT '修改时间'
) COMMENT '省份表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_base_province_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

8地区表（全量表）

DROP TABLE IF EXISTS ods_base_region_full;
CREATE EXTERNAL TABLE ods_base_region_full
(`id`               STRING COMMENT '地区ID',`region_name`    STRING COMMENT '地区名称',`create_time`    STRING COMMENT '创建时间',`operate_time`   STRING COMMENT '修改时间'
) COMMENT '地区表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_base_region_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

9品牌表（全量表）

DROP TABLE IF EXISTS ods_base_trademark_full;
CREATE EXTERNAL TABLE ods_base_trademark_full
(`id`               STRING COMMENT '编号',`tm_name`         STRING COMMENT '品牌名称',`logo_url`        STRING COMMENT '品牌LOGO的图片路径',`create_time`    STRING COMMENT '创建时间',`operate_time`   STRING COMMENT '修改时间'
) COMMENT '品牌表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_base_trademark_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

10购物车表（全量表）

DROP TABLE IF EXISTS ods_cart_info_full;
CREATE EXTERNAL TABLE ods_cart_info_full
(`id`            STRING COMMENT '编号',`user_id`      STRING COMMENT '用户ID',`sku_id`       STRING COMMENT 'SKU_ID',`cart_price`   DECIMAL(16, 2) COMMENT '放入购物车时价格',`sku_num`      BIGINT COMMENT '数量',`img_url`      BIGINT COMMENT '商品图片地址',`sku_name`     STRING COMMENT 'SKU名称 (冗余)',`is_checked`   STRING COMMENT '是否被选中',`create_time`  STRING COMMENT '创建时间',`operate_time` STRING COMMENT '修改时间',`is_ordered`   STRING COMMENT '是否已经下单',`order_time`   STRING COMMENT '下单时间'
) COMMENT '购物车全量表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_cart_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

11优惠券信息表（全量表）

DROP TABLE IF EXISTS ods_coupon_info_full;
CREATE EXTERNAL TABLE ods_coupon_info_full
(`id`                 STRING COMMENT '购物券编号',`coupon_name`      STRING COMMENT '购物券名称',`coupon_type`      STRING COMMENT '购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券',`condition_amount` DECIMAL(16, 2) COMMENT '满额数',`condition_num`    BIGINT COMMENT '满件数',`activity_id`      STRING COMMENT '活动编号',`benefit_amount`   DECIMAL(16, 2) COMMENT '减免金额',`benefit_discount` DECIMAL(16, 2) COMMENT '折扣',`create_time`      STRING COMMENT '创建时间',`range_type`       STRING COMMENT '范围类型 1、商品(SPUID) 2、品类(三级品类id) 3、品牌',`limit_num`        BIGINT COMMENT '最多领用次数',`taken_count`      BIGINT COMMENT '已领用次数',`start_time`       STRING COMMENT '可以领取的开始时间',`end_time`         STRING COMMENT '可以领取的结束时间',`operate_time`     STRING COMMENT '修改时间',`expire_time`      STRING COMMENT '过期时间',`range_desc`       STRING COMMENT '范围描述'
) COMMENT '优惠券信息表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_coupon_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

12商品平台属性表（全量表）

DROP TABLE IF EXISTS ods_sku_attr_value_full;
CREATE EXTERNAL TABLE ods_sku_attr_value_full
(`id`              STRING COMMENT '编号',`attr_id`        STRING COMMENT '平台属性ID',`value_id`       STRING COMMENT '平台属性值ID',`sku_id`         STRING COMMENT 'SKU_ID',`attr_name`      STRING COMMENT '平台属性名称',`value_name`     STRING COMMENT '平台属性值名称',`create_time`    STRING COMMENT '创建时间',`operate_time`   STRING COMMENT '修改时间'
) COMMENT '商品平台属性表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_sku_attr_value_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

13商品表（全量表）

DROP TABLE IF EXISTS ods_sku_info_full;
CREATE EXTERNAL TABLE ods_sku_info_full
(`id`                STRING COMMENT 'SKU_ID',`spu_id`           STRING COMMENT 'SPU_ID',`price`            DECIMAL(16, 2) COMMENT '价格',`sku_name`         STRING COMMENT 'SKU名称',`sku_desc`         STRING COMMENT 'SKU规格描述',`weight`           DECIMAL(16, 2) COMMENT '重量',`tm_id`             STRING COMMENT '品牌ID',`category3_id`     STRING COMMENT '三级品类ID',`sku_default_img` STRING COMMENT '默认显示图片地址',`is_sale`           STRING COMMENT '是否在售',`create_time`      STRING COMMENT '创建时间',`operate_time`     STRING COMMENT '修改时间'
) COMMENT '商品表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_sku_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

14商品销售属性值表（全量表）

DROP TABLE IF EXISTS ods_sku_sale_attr_value_full;
CREATE EXTERNAL TABLE ods_sku_sale_attr_value_full
(`id`                      STRING COMMENT '编号',`sku_id`                 STRING COMMENT 'SKU_ID',`spu_id`                 STRING COMMENT 'SPU_ID',`sale_attr_value_id`   STRING COMMENT '销售属性值ID',`sale_attr_id`          STRING COMMENT '销售属性ID',`sale_attr_name`        STRING COMMENT '销售属性名称',`sale_attr_value_name` STRING COMMENT '销售属性值名称',`create_time`            STRING COMMENT '创建时间',`operate_time`           STRING COMMENT '修改时间'
) COMMENT '商品销售属性值表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_sku_sale_attr_value_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

15SPU表（全量表）

DROP TABLE IF EXISTS ods_spu_info_full;
CREATE EXTERNAL TABLE ods_spu_info_full
(`id`              STRING COMMENT 'SPU_ID',`spu_name`       STRING COMMENT 'SPU名称',`description`   STRING COMMENT '描述信息',`category3_id`  STRING COMMENT '三级品类ID',`tm_id`           STRING COMMENT '品牌ID',`create_time`    STRING COMMENT '创建时间',`operate_time`   STRING COMMENT '修改时间'
) COMMENT 'SPU表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_spu_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

16营销坑位表（全量表）

DROP TABLE IF EXISTS ods_promotion_pos_full;
CREATE EXTERNAL TABLE ods_promotion_pos_full
(`id`                   STRING COMMENT '营销坑位ID',`pos_location`       STRING COMMENT '营销坑位位置',`pos_type`            STRING COMMENT '营销坑位类型：banner,宫格,列表,瀑布',`promotion_type`     STRING COMMENT '营销类型：算法、固定、搜索',`create_time`         STRING COMMENT '创建时间',`operate_time`        STRING COMMENT '修改时间'
) COMMENT '营销坑位表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_promotion_pos_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

17营销渠道表（全量表）

DROP TABLE IF EXISTS ods_promotion_refer_full;
CREATE EXTERNAL TABLE ods_promotion_refer_full
(`id`                  STRING COMMENT '外部营销渠道ID',`refer_name`        STRING COMMENT '外部营销渠道名称',`create_time`       STRING COMMENT '创建时间',`operate_time`      STRING COMMENT '修改时间'
) COMMENT '营销渠道表'PARTITIONED BY (`dt` STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_promotion_refer_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

18购物车表（增量表）

DROP TABLE IF EXISTS ods_cart_info_inc;
CREATE EXTERNAL TABLE ods_cart_info_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,user_id :STRING,sku_id :STRING,cart_price :DECIMAL(16, 2),sku_num :BIGINT,img_url :STRING,sku_name :STRING,is_checked :STRING,create_time :STRING,operate_time :STRING,is_ordered :STRING,order_time:STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '购物车增量表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_cart_info_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

19评论表（增量表）

DROP TABLE IF EXISTS ods_comment_info_inc;
CREATE EXTERNAL TABLE ods_comment_info_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,user_id :STRING,nick_name :STRING,head_img :STRING,sku_id :STRING,spu_id :STRING,order_id :STRING,appraise :STRING,comment_txt :STRING,create_time :STRING,operate_time :STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '评论表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_comment_info_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

20优惠券领用表（增量表）

DROP TABLE IF EXISTS ods_coupon_use_inc;
CREATE EXTERNAL TABLE ods_coupon_use_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING, coupon_id :STRING,user_id :STRING,order_id :STRING,coupon_status :STRING,get_time :STRING,using_time:STRING,used_time :STRING,expire_time :STRING, create_time :STRING,operate_time :STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '优惠券领用表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_coupon_use_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

21收藏表（增量表）

DROP TABLE IF EXISTS ods_favor_info_inc;
CREATE EXTERNAL TABLE ods_favor_info_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,user_id :STRING,sku_id :STRING,spu_id :STRING,is_cancel :STRING,create_time :STRING,operate_time:STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '收藏表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_favor_info_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

22订单明细表（增量表）

DROP TABLE IF EXISTS ods_order_detail_inc;
CREATE EXTERNAL TABLE ods_order_detail_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,order_id :STRING,sku_id :STRING,sku_name :STRING,img_url :STRING,order_price:DECIMAL(16, 2),sku_num :BIGINT,create_time :STRING,source_type :STRING,source_id :STRING,split_total_amount:DECIMAL(16, 2),split_activity_amount :DECIMAL(16, 2),split_coupon_amount:DECIMAL(16, 2),operate_time :STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '订单明细表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_order_detail_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

23订单明细活动关联表（增量表）

DROP TABLE IF EXISTS ods_order_detail_activity_inc;
CREATE EXTERNAL TABLE ods_order_detail_activity_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,order_id :STRING,order_detail_id :STRING,activity_id :STRING,activity_rule_id :STRING,sku_id:STRING,create_time :STRING, operate_time :STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '订单明细活动关联表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_order_detail_activity_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

24订单明细优惠券关联表（增量表）

DROP TABLE IF EXISTS ods_order_detail_coupon_inc;
CREATE EXTERNAL TABLE ods_order_detail_coupon_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,order_id :STRING,order_detail_id :STRING,coupon_id :STRING,coupon_use_id :STRING,sku_id:STRING,create_time :STRING, operate_time :STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '订单明细优惠券关联表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_order_detail_coupon_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

25订单表（增量表）

DROP TABLE IF EXISTS ods_order_info_inc;
CREATE EXTERNAL TABLE ods_order_info_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,consignee :STRING,consignee_tel :STRING,total_amount :DECIMAL(16, 2),order_status :STRING,user_id:STRING,payment_way :STRING,delivery_address :STRING,order_comment :STRING,out_trade_no :STRING,trade_body:STRING,create_time :STRING,operate_time :STRING,expire_time :STRING,process_status :STRING,tracking_no:STRING,parent_order_id :STRING,img_url :STRING,province_id :STRING,activity_reduce_amount:DECIMAL(16, 2),coupon_reduce_amount :DECIMAL(16, 2),original_total_amount :DECIMAL(16, 2),freight_fee:DECIMAL(16, 2),freight_fee_reduce :DECIMAL(16, 2),refundable_time :DECIMAL(16, 2)> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '订单表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_order_info_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

26退单表（增量表）

DROP TABLE IF EXISTS ods_order_refund_info_inc;
CREATE EXTERNAL TABLE ods_order_refund_info_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,user_id :STRING,order_id :STRING,sku_id :STRING,refund_type :STRING,refund_num :BIGINT,refund_amount:DECIMAL(16, 2),refund_reason_type :STRING,refund_reason_txt :STRING,refund_status :STRING,create_time:STRING,operate_time :STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '退单表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_order_refund_info_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

27订单状态流水表（增量表）

DROP TABLE IF EXISTS ods_order_status_log_inc;
CREATE EXTERNAL TABLE ods_order_status_log_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,order_id :STRING,order_status :STRING,create_time :STRING,operate_time :STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '订单状态流水表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_order_status_log_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

28支付表（增量表）

DROP TABLE IF EXISTS ods_payment_info_inc;
CREATE EXTERNAL TABLE ods_payment_info_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,out_trade_no :STRING,order_id :STRING,user_id :STRING,payment_type :STRING,trade_no:STRING,total_amount :DECIMAL(16, 2),subject :STRING,payment_status :STRING,create_time :STRING,callback_time:STRING,callback_content :STRING,operate_time :STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '支付表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_payment_info_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

29退款表（增量表）

DROP TABLE IF EXISTS ods_refund_payment_inc;
CREATE EXTERNAL TABLE ods_refund_payment_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,out_trade_no :STRING,order_id :STRING,sku_id :STRING,payment_type :STRING,trade_no :STRING,total_amount:DECIMAL(16, 2),subject :STRING,refund_status :STRING,create_time :STRING,callback_time :STRING,callback_content:STRING,operate_time :STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '退款表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_refund_payment_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

30用户表（增量表）

DROP TABLE IF EXISTS ods_user_info_inc;
CREATE EXTERNAL TABLE ods_user_info_inc
(`type` STRING COMMENT '变动类型',`ts`   BIGINT COMMENT '变动时间',`data` STRUCT<id :STRING,login_name :STRING,nick_name :STRING,passwd :STRING,name :STRING,phone_num :STRING,email:STRING,head_img :STRING,user_level :STRING,birthday :STRING,gender :STRING,create_time :STRING,operate_time:STRING,status :STRING> COMMENT '数据',`old`  MAP<STRING,STRING> COMMENT '旧值'
) COMMENT '用户表'PARTITIONED BY (`dt` STRING)ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_user_info_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

31数据装载脚本

（1）在hadoop102的/home/atguigu/bin目录下创建hdfs_to_ods_db.sh

vim hdfs_to_ods_db.sh

（2）编写如下内容

#!/bin/bashAPP=gmallif [ -n "$2" ] ;thendo_date=$2
else do_date=`date -d '-1 day' +%F`
fiload_data(){sql=""for i in $*; do#判断路径是否存在hadoop fs -test -e /origin_data/$APP/db/${i:4}/$do_date#路径存在方可装载数据if [[ $? = 0 ]]; thensql=$sql"load data inpath '/origin_data/$APP/db/${i:4}/$do_date' OVERWRITE into table ${APP}.$i partition(dt='$do_date');"fidonehive -e "$sql"
}case $1 in"ods_activity_info_full")load_data "ods_activity_info_full";;"ods_activity_rule_full")load_data "ods_activity_rule_full";;"ods_base_category1_full")load_data "ods_base_category1_full";;"ods_base_category2_full")load_data "ods_base_category2_full";;"ods_base_category3_full")load_data "ods_base_category3_full";;"ods_base_dic_full")load_data "ods_base_dic_full";;"ods_base_province_full")load_data "ods_base_province_full";;"ods_base_region_full")load_data "ods_base_region_full";;"ods_base_trademark_full")load_data "ods_base_trademark_full";;"ods_cart_info_full")load_data "ods_cart_info_full";;"ods_coupon_info_full")load_data "ods_coupon_info_full";;"ods_sku_attr_value_full")load_data "ods_sku_attr_value_full";;"ods_sku_info_full")load_data "ods_sku_info_full";;"ods_sku_sale_attr_value_full")load_data "ods_sku_sale_attr_value_full";;"ods_spu_info_full")load_data "ods_spu_info_full";;"ods_promotion_pos_full")load_data "ods_promotion_pos_full";;"ods_promotion_refer_full")load_data "ods_promotion_refer_full";;"ods_cart_info_inc")load_data "ods_cart_info_inc";;"ods_comment_info_inc")load_data "ods_comment_info_inc";;"ods_coupon_use_inc")load_data "ods_coupon_use_inc";;"ods_favor_info_inc")load_data "ods_favor_info_inc";;"ods_order_detail_inc")load_data "ods_order_detail_inc";;"ods_order_detail_activity_inc")load_data "ods_order_detail_activity_inc";;"ods_order_detail_coupon_inc")load_data "ods_order_detail_coupon_inc";;"ods_order_info_inc")load_data "ods_order_info_inc";;"ods_order_refund_info_inc")load_data "ods_order_refund_info_inc";;"ods_order_status_log_inc")load_data "ods_order_status_log_inc";;"ods_payment_info_inc")load_data "ods_payment_info_inc";;"ods_refund_payment_inc")load_data "ods_refund_payment_inc";;"ods_user_info_inc")load_data "ods_user_info_inc";;"all")load_data "ods_activity_info_full" "ods_activity_rule_full" "ods_base_category1_full" "ods_base_category2_full" "ods_base_category3_full" "ods_base_dic_full" "ods_base_province_full" "ods_base_region_full" "ods_base_trademark_full" "ods_cart_info_full" "ods_coupon_info_full" "ods_sku_attr_value_full" "ods_sku_info_full" "ods_sku_sale_attr_value_full" "ods_spu_info_full" "ods_promotion_pos_full" "ods_promotion_refer_full" "ods_cart_info_inc" "ods_comment_info_inc" "ods_coupon_use_inc" "ods_favor_info_inc" "ods_order_detail_inc" "ods_order_detail_activity_inc" "ods_order_detail_coupon_inc" "ods_order_info_inc" "ods_order_refund_info_inc" "ods_order_status_log_inc" "ods_payment_info_inc" "ods_refund_payment_inc" "ods_user_info_inc";;
esac

（3）增加脚本执行权限

chmod +x hdfs_to_ods_db.sh

（4）脚本用法

hdfs_to_ods_db.sh all 2022-06-08

第二层：数据加工（DWD data warehouse detail）

功能：将数据源中的数据进行加工处理（判空、无效）
为了后续统计分析做数据准备
数据量非常大，所以分离出了DIM层将数据整合
压缩方式：snappy

事实表设计（事务型事实表）

-- DWD-- Data Warehouse Detail-- detail : 详细，明细-- DWD层表主要设计的目的为了统计分析做准备-- 表中主要保存的是行为数据-- 多个行为数据中如果存在共通性的内容，那么可以提炼出来形成DIM层维度表的数据-- 表的设计要点-- 表的设计要依据维度建模理论中的事实表-- 表设计时需要orc列式存储以及snappy压缩-- 命名规范：-- 分层标记（dwd_） + 数据域(分类) + 原子性行为名称 + 增量/全量（inc/full）-- 绝大多数的行为数据都是增量数据采集-- 特殊情况例外，可以采用全量方式实现。-- dwd_user_login_success_inc-- 事实表-- 维度引用 + 度量值（行为产生时可以用于统计分析的数值：金额，数量，个数）-- 事实表会根据场景分为3大类：-- 1. 事务型事实表-- 行为是原子性-- 用户登录（非原子）-- 用户登录成功（原子）-- 用户登录失败（原子）-- 粒度：描述一行数据的详细程度-- 描述的越详细（维度越多），粒度越细-- 描述的越简单（维度越多），粒度越粗-- 设计步骤：-- 1. 选择业务过程 ：确定表-- 2. 声明粒度：确定行-- 3. 确认维度：确定列-- 4. 确认事实：确定度量值-- 2. 周期快照事实表-- 3. 累积快照事实表-- 交易域加购事务事实表-- 交易域 : trade-- 加购 : 行为-- 将商品加入到购物车中的行为-- 购物车中没有这个商品，往购物车中增加商品-- 购物车中有这个商品，继续往购物车中增加该商品-- 事务事实表-- 原子性-- 时间（行为时间） + 用户 + 商品 + 数量-- 表的字段结构：必要的维度属性 + 度量值 + 可选的维度属性-- 建表语句-- 分区策略：哪一天的行为数据存放到哪一天分区

事务的原子性

登录成功（OK）登录失败（OK）
下单成功（OK）下单失败（非正常业务行为，不需要再创建一张表）
支付成功（OK）支付失败（OK）

事实表设计（周期型快照事实表）

全量

-- 事务性事实表局限性
-- 事实表只针对于当前行为进行的统计分析时，性能可以得到保障。
-- 当前行为事实表和其他行为数据进行关联时，数据量会几何爆炸性增长，性能会急剧下降。
-- 存量性统计指标使用事务性事实表效率太低，所以一般会采用其他事实表的设计方式-- 2. 周期型快照事实表-- 交易域购物车周期快照事实表-- 交易域-- 购物车 ： cart_info-- 周期快照事实表

从当前表中取数据后再放回去需考虑去重问题，增加retry的容错性

事实表设计（累积型快照事实表）

-- 多行为统计指标使用事务性事实表效率太低，所以一般会采用其他事实表的设计方式
-- 3. 累积型快照事实表-- 使用一张表保存多个行为的状态数据-- 交易域交易流程累积快照事实表-- 交易域-- 交易流程 : 以订单为基础的交易流程-- 累积快照事实表

分区策略

-- 事务性事实表：哪一天的行为数据存放到哪一天的分区
-- 周期性事实表：每一天存储一份数据
-- 累积快照事实表：从业务流程中获取最后一个业务行为时间作为分区字段-- 下单时间 (X)-- 支付时间 (X)-- 收货时间 (OK)

第三层：数据统计（DWS data warehouse summary 提高性能的关键层）

功能：将加工后的数据进行统计
数据量非常大
压缩方式：snappy

在这里插入图片描述

第四层：数据分析（ADS application data service）

功能：将统计结果进行分析，为用户提供经营决策
压缩方式：gzip
数据格式：tsv

表的设计（要点）

        -- ADS层中存储的是统计分析的最终结果-- 数据量不多-- 表不需要分区-- 无需做进一步聚合-- 无需orc列式存储和snappy压缩-- 行式存储 + gzip-- 结果还需要向后流转（可视化）-- tsv-- 表的结构不能太复杂（满足客户的需求即可）

优化（假）

Spark：

reduceByKey（函数内部combine减少落盘数据量）和groupByKey
cache、persist和checkpoint
DWS

1流量主题

1.1各渠道流量统计

1）建表语句

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

2）数据装载

第五层：共通层（DIM dimension）

功能：将共同的数据放在共通的表中，可在多个统计需求中使用
dimension：维度，分析数据的角度
该层不需要一开始就设计，可以等DWD层设计的差不多了，或是写着写着发现DWD中有好多表都用到了共通的字段，有大量冗余数据，那么就可以将这部分共通的数据提取成一个表

设计要点

（1）DIM层的设计依据是维度建模理论，该层存储维度模型的维度表。
（2）DIM层的数据存储格式为orc列式存储+snappy压缩。
（3）DIM层表名的命名规范为dim_表名_全量表或者拉链表标识（full/zip）。
绝大多数的维度表都是全量表

是否创建表

数据量少，应用面窄：无需创建表（放用得到的表里即可，即维度退化）
数据量少，应用面广：无需创建独立表，一般和其他的数放置在一张表中（数据字典表（编码表->是树形表））
树形(有上下级)数据保存时一般会采用 parent - child节点的设计方式
- 一般情况下，会采用一张表来设计上下级结构：部门（depart）
  – 表中的列：下级部门（主键）（N），上级部门（外键）(1)
- 字典表也是树形表

维度表设计

确定维度表：确定维度的表是否该创建
- 原则上来讲，每一个分析数据的角度（维度）都应该创建一张表
  - 案例：统计各个省份，各个品牌的订单总销量
    – 订单属于事实（行为）表，省份和品牌就是维度表
  - 案例：统计各个性别不同年龄段的订单总销量
    – 订单属于事实（行为）表, 性别和年龄就是维度表
- 如果多个维度存在关联，那么一般就会只创建一张表，表中包含了多个关联的维度
- 如果分析数据的角度应用场景少，而且数据量小，不需要创建专门的维度表
  - 案例：支付方式（微信支付，支付宝支付）
确定主维表和相关维表(用于分析维度表的列)
- 确定表中的列
  - 案例：省份维度表
    – 列：名称
  - 数据仓库的数据都来自于MySQL业务数据，
    – 维度表的列的声明可以参考业务数据库表的字段
  - MySQL业务数据库中具有唯一性字段的那个业务表称之为主维表
    – 其他的表称之为相关维表。
确定表中的列
- 尽可能丰富（多）
- 编码和文字共存
- 沉淀通用属性：tel, xxx
  – 计算或转换

1商品维度表

-- 商品维度表 ：dim_sku_full-- 确定维度表-- 主维表和相关维表-- 主维表和相关维表都是MySQL业务表-- 主要用于分析列的表称之主维表（主键）-- sku_info-- 其他用于分析列的表称之相关维表-- sku_attr_value-- sku_sale_attr_value-- 确定表的列-- 建表语句
DROP TABLE IF EXISTS dim_sku_full;
CREATE EXTERNAL TABLE dim_sku_full
(`id`                   STRING COMMENT 'SKU_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 ORCLOCATION '/warehouse/gmall/dim/dim_sku_full/'TBLPROPERTIES ('orc.compress' = 'snappy');-- 数据装载-- load-- 数据源一定是ODS层-- save-- 分区字段其实也是表的字段，但是我们一般称之为虚拟字段-- 数据字段(列)：存储在数据文件中-- 分区字段：存储在路径中-- 分区-- dt : date(日期)-- 策略：将每天采集的数据存放到ODS层的每天分区中-- 将ODS层每天的数据关联后存放到DIM层的每天分区中-- 分区存储应该采用overwrite而不是into
set hive.vectorized.execution.enabled=false;
insert overwrite table dim_sku_full partition (dt='2022-06-08')
selectsku.`id`               ,--STRING COMMENT 'SKU_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,sku_sale_attr_values,`create_time`          --STRING COMMENT '创建时间'
from (select`id`                   ,--STRING COMMENT 'SKU_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编号',`category3_id`         ,--STRING COMMENT '三级品类ID',`tm_id`                ,--  STRING COMMENT '品牌ID',create_timefrom ods_sku_info_fullwhere dt = '2022-06-08'
) sku
left join (selectid,spu_namefrom ods_spu_info_fullwhere dt = '2022-06-08'
) spu on sku.spu_id = spu.id
left join (selectid,tm_namefrom ods_base_trademark_fullwhere dt = '2022-06-08'
) tm on sku.tm_id = tm.id
left join (selectid,name category3_name,category2_idfrom ods_base_category3_fullwhere dt = '2022-06-08'
) c3 on sku.category3_id = c3.id
left join (selectid,name category2_name,category1_idfrom ods_base_category2_fullwhere dt = '2022-06-08'
) c2 on c3.category2_id = c2.id
left join (selectid,name category1_namefrom ods_base_category1_fullwhere dt = '2022-06-08'
) c1 on c2.category1_id = c1.id
left join (
-- 将查询结果转换为结构体后，形成Array
-- 将多个结构体的数据聚合成一个数组类型的数据（聚合操作）selectsku_id,collect_list(named_struct("attr_id", attr_id, "value_id", value_id, "attr_name", attr_name, "value_name", value_name)) sku_attr_valuesfrom ods_sku_attr_value_fullwhere dt = '2022-06-08'group by sku_id
) sav on sku.id = sav.sku_id
left join (selectsku_id,collect_list(named_struct("sale_attr_id", sale_attr_id, "sale_attr_value_id", sale_attr_value_id, "sale_attr_name", sale_attr_name, "sale_attr_value_name", sale_attr_value_name)) sku_sale_attr_valuesfrom ods_sku_sale_attr_value_fullwhere dt = '2022-06-08'group by sku_id
) ssav on sku.id = ssav.sku_id;-- join & left join-- join 要求2张表的数据同时满足条件才能作为结果返回-- left join 要求2张表的数据左边的表不满足条件也能作为结果返回-- 使用left join 替换 join，必须保证，替换后不影响结果

2优惠券维度表

1）建表语句

-- 创建表
-- 分析表中的列-- 主维表-- coupon_info-- base_dic-- 相关维表
-- 建表语句-- 表名
DROP TABLE IF EXISTS dim_coupon_full;
CREATE EXTERNAL TABLE dim_coupon_full
(`id`                  STRING COMMENT '优惠券编号',`coupon_name`       STRING COMMENT '优惠券名称',`coupon_type_code` STRING COMMENT '优惠券类型编码',`coupon_type_name` STRING COMMENT '优惠券类型名称',`condition_amount` DECIMAL(16, 2) COMMENT '满额数',`condition_num`     BIGINT COMMENT '满件数',`activity_id`       STRING COMMENT '活动编号',`benefit_amount`   DECIMAL(16, 2) COMMENT '减免金额',`benefit_discount` DECIMAL(16, 2) COMMENT '折扣',`benefit_rule`     STRING COMMENT '优惠规则:满元*减*元，满*件打*折',`create_time`       STRING COMMENT '创建时间',`range_type_code`  STRING COMMENT '优惠范围类型编码',`range_type_name`  STRING COMMENT '优惠范围类型名称',`limit_num`         BIGINT COMMENT '最多领取次数',`taken_count`       BIGINT COMMENT '已领取次数',`start_time`        STRING COMMENT '可以领取的开始时间',`end_time`          STRING COMMENT '可以领取的结束时间',`operate_time`      STRING COMMENT '修改时间',`expire_time`       STRING COMMENT '过期时间'
) COMMENT '优惠券维度表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dim/dim_coupon_full/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dim_coupon_full partition(dt='2022-06-08')
selectid,coupon_name,coupon_type,coupon_dic.dic_name,condition_amount,condition_num,activity_id,benefit_amount,benefit_discount,case coupon_typewhen '3201' then concat('满',condition_amount,'元减',benefit_amount,'元')when '3202' then concat('满',condition_num,'件打', benefit_discount,' 折')when '3203' then concat('减',benefit_amount,'元')end benefit_rule,create_time,range_type,range_dic.dic_name,limit_num,taken_count,start_time,end_time,operate_time,expire_time
from
(selectid,coupon_name,coupon_type,condition_amount,condition_num,activity_id,benefit_amount,benefit_discount,create_time,range_type,limit_num,taken_count,start_time,end_time,operate_time,expire_timefrom ods_coupon_info_fullwhere dt='2022-06-08'
)ci
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2022-06-08'and parent_code='32'
)coupon_dic
on ci.coupon_type=coupon_dic.dic_code
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2022-06-08'and parent_code='33'
)range_dic
on ci.range_type=range_dic.dic_code;

3活动（规则）维度表

1）建表语句

-- 活动(规则)维度表-- activity_info-- activity_rule
DROP TABLE IF EXISTS dim_activity_full;
CREATE EXTERNAL TABLE dim_activity_full
(`activity_rule_id`   STRING COMMENT '活动规则ID',`activity_id`         STRING COMMENT '活动ID',`activity_name`       STRING COMMENT '活动名称',`activity_type_code` STRING COMMENT '活动类型编码',`activity_type_name` STRING COMMENT '活动类型名称',`activity_desc`       STRING COMMENT '活动描述',`start_time`           STRING COMMENT '开始时间',`end_time`             STRING COMMENT '结束时间',`create_time`          STRING COMMENT '创建时间',`condition_amount`    DECIMAL(16, 2) COMMENT '满减金额',`condition_num`       BIGINT COMMENT '满减件数',`benefit_amount`      DECIMAL(16, 2) COMMENT '优惠金额',`benefit_discount`   DECIMAL(16, 2) COMMENT '优惠折扣',`benefit_rule`        STRING COMMENT '优惠规则',`benefit_level`       STRING COMMENT '优惠级别'
) COMMENT '活动维度表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dim/dim_activity_full/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dim_activity_full partition(dt='2022-06-08')
selectrule.id,info.id,activity_name,rule.activity_type,dic.dic_name,activity_desc,start_time,end_time,create_time,condition_amount,condition_num,benefit_amount,benefit_discount,case rule.activity_typewhen '3101' then concat('满',condition_amount,'元减',benefit_amount,'元')when '3102' then concat('满',condition_num,'件打', benefit_discount,' 折')when '3103' then concat('打', benefit_discount,'折')end benefit_rule,benefit_level
from
(selectid,activity_id,activity_type,condition_amount,condition_num,benefit_amount,benefit_discount,benefit_levelfrom ods_activity_rule_fullwhere dt='2022-06-08'
)rule
left join
(selectid,activity_name,activity_type,activity_desc,start_time,end_time,create_timefrom ods_activity_info_fullwhere dt='2022-06-08'
)info
on rule.activity_id=info.id
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2022-06-08'and parent_code='31'
)dic
on rule.activity_type=dic.dic_code;

4地区维度表

1）建表语句

DROP TABLE IF EXISTS dim_province_full;
CREATE EXTERNAL TABLE dim_province_full
(`id`              STRING COMMENT '省份ID',`province_name` STRING COMMENT '省份名称',`area_code`     STRING COMMENT '地区编码',`iso_code`      STRING COMMENT '旧版国际标准地区编码，供可视化使用',`iso_3166_2`    STRING COMMENT '新版国际标准地区编码，供可视化使用',`region_id`     STRING COMMENT '地区ID',`region_name`   STRING COMMENT '地区名称'
) COMMENT '地区维度表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dim/dim_province_full/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dim_province_full partition(dt='2022-06-08')
selectprovince.id,province.name,province.area_code,province.iso_code,province.iso_3166_2,region_id,region_name
from
(selectid,name,region_id,area_code,iso_code,iso_3166_2from ods_base_province_fullwhere dt='2022-06-08'
)province
left join
(selectid,region_namefrom ods_base_region_fullwhere dt='2022-06-08'
)region
on province.region_id=region.id;

5营销坑位维度表

1）建表语句

DROP TABLE IF EXISTS dim_promotion_pos_full;
CREATE EXTERNAL TABLE dim_promotion_pos_full
(`id`                 STRING COMMENT '营销坑位ID',`pos_location`     STRING COMMENT '营销坑位位置',`pos_type`          STRING COMMENT '营销坑位类型 ',`promotion_type`   STRING COMMENT '营销类型',`create_time`       STRING COMMENT '创建时间',`operate_time`      STRING COMMENT '修改时间'
) COMMENT '营销坑位维度表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dim/dim_promotion_pos_full/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dim_promotion_pos_full partition(dt='2022-06-08')
select`id`,         `pos_location`,`pos_type`,`promotion_type`,`create_time`,`operate_time`
from ods_promotion_pos_full 
where dt='2022-06-08';

6营销渠道维度表

1）建表语句

DROP TABLE IF EXISTS dim_promotion_refer_full;
CREATE EXTERNAL TABLE dim_promotion_refer_full
(`id`                    STRING COMMENT '营销渠道ID',`refer_name`          STRING COMMENT '营销渠道名称',`create_time`         STRING COMMENT '创建时间',`operate_time`        STRING COMMENT '修改时间'
) COMMENT '营销渠道维度表'PARTITIONED BY (`dt` STRING)STORED AS ORCLOCATION '/warehouse/gmall/dim/dim_promotion_refer_full/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

insert overwrite table dim_promotion_refer_full partition(dt='2022-06-08')
select`id`, `refer_name`,`create_time`,`operate_time`   
from ods_promotion_refer_full 
where dt='2022-06-08';

7日期维度表

日期维度表不需要从MySQL中导，而是从文件中另行导入，也不需要每天导入，每年导入一次即可
1）建表语句

DROP TABLE IF EXISTS dim_date;
CREATE EXTERNAL TABLE dim_date
(`date_id`    STRING COMMENT '日期ID',`week_id`    STRING COMMENT '周ID,一年中的第几周',`week_day`   STRING COMMENT '周几',`day`         STRING COMMENT '每月的第几天',`month`       STRING COMMENT '一年中的第几月',`quarter`    STRING COMMENT '一年中的第几季度',`year`        STRING COMMENT '年份',`is_workday` STRING COMMENT '是否是工作日',`holiday_id` STRING COMMENT '节假日'
) COMMENT '日期维度表'STORED AS ORCLOCATION '/warehouse/gmall/dim/dim_date/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载

（1）创建临时表

DROP TABLE IF EXISTS tmp_dim_date_info;
CREATE EXTERNAL TABLE tmp_dim_date_info (`date_id`       STRING COMMENT '日',`week_id`       STRING COMMENT '周ID',`week_day`      STRING COMMENT '周几',`day`            STRING COMMENT '每月的第几天',`month`          STRING COMMENT '第几月',`quarter`       STRING COMMENT '第几季度',`year`           STRING COMMENT '年',`is_workday`    STRING COMMENT '是否是工作日',`holiday_id`    STRING COMMENT '节假日'
) COMMENT '时间维度表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/tmp/tmp_dim_date_info/';

（2）将数据文件上传到HFDS上临时表路径/warehouse/gmall/tmp/tmp_dim_date_info
在这里插入图片描述
TSV格式的数据

-- 日期数据不是由我们自己提供的-- TSV -> ORC-- ODS(全量) -> DIM(ORC)
insert overwrite table dim_date select * from tmp_dim_date_info;

8用户维度表（拉链（压缩）表）

将大量数据的表进行特殊的设计进行改善，让数据减少，并且不影响业务逻辑
– 将数据状态进行时间标记：开始 + 结束
设计拉链表时，需要在基本表的设计基础上，增加2个额外字段，用于表示状态的范围（开始，结束）
拉链表的数据，每一个状态的变化会保存一条数据，如果状态没有任何的变化，那么数据只有一条
1）建表语句

-- 表名：dim_user_full
-- 表中列-- 主维表 : user_info-- 相关维表 : user_address
DROP TABLE IF EXISTS dim_user_zip;
CREATE EXTERNAL TABLE dim_user_zip
(`id`           STRING COMMENT '用户ID',`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 ORCLOCATION '/warehouse/gmall/dim/dim_user_zip/'TBLPROPERTIES ('orc.compress' = 'snappy');

2）数据装载
① 首日装载

-- 全量表-- DataX-- TSV
-- 增量表-- Maxwell-- JSON-- 首日(全量-select) : bootstrap (3种类型)-- 每日(增量-binlog) : insert, update, delete
insert overwrite table dim_user_zip partition (dt = '9999-12-31')
select data.id,concat(substr(data.name, 1, 1), '*')                name,if(data.phone_num regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',concat(substr(data.phone_num, 1, 3), '*'), null) phone_num,if(data.email regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\.[a-zA-Z0-9_-]+)+$',concat('*@', split(data.email, '@')[1]), null)   email,data.user_level,data.birthday,data.gender,data.create_time,data.operate_time,'2022-06-08'                                        start_date,'9999-12-31'                                        end_date
from ods_user_info_inc
where dt = '2022-06-08'and type = 'bootstrap-insert';

② 每日装载

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dim_user_zip partition (dt)
select id,name,phone_num,email,user_level,birthday,gender,create_time,operate_time,start_date,if(rn = 2, date_sub('2022-06-09', 1), end_date)     end_date,if(rn = 1, '9999-12-31', date_sub('2022-06-09', 1)) dt
from (select id,name,phone_num,email,user_level,birthday,gender,create_time,operate_time,start_date,end_date,row_number() over (partition by id order by start_date desc) rnfrom (select id,name,phone_num,email,user_level,birthday,gender,create_time,operate_time,start_date,end_datefrom dim_user_zipwhere dt = '9999-12-31'unionselect id,concat(substr(name, 1, 1), '*')                name,if(phone_num regexp'^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',concat(substr(phone_num, 1, 3), '*'), null) phone_num,if(email regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\.[a-zA-Z0-9_-]+)+$',concat('*@', split(email, '@')[1]), null)   email,user_level,birthday,gender,create_time,operate_time,'2022-06-09'                                   start_date,'9999-12-31'                                   end_datefrom (select data.id,data.name,data.phone_num,data.email,data.user_level,data.birthday,data.gender,data.create_time,data.operate_time,row_number() over (partition by data.id order by ts desc) rnfrom ods_user_info_incwhere dt = '2022-06-09') t1where rn = 1) t2) t3;

CTE : 共通表表达式

with
aa as ( select * from test_table_part ),
ab as (select * from aa)
select*
from aa
join ab;select*
from (select * from test_table_part
)  aa
join (select * from test_table_part
) bb;select*
from dim_sku_full

拉链表设计

-- 数据装载-- load-- save-- 增量表得数据操作一般都会写2个-- 首日数据装载-- 每日数据装载
-- 首日数据装载-- 同步方式：maxwell - 全量 - bootstrap - select * from user_info-- MySQL不保存行为数据，也就意味着不保存历史行为数据-- 拉链表会在当前表得字段得基础上，额外添加两个字段（start, end），用于标记状态得有效范围-- start : 无法判断开始范围-- end   : 无法判断-- 折中地考虑-- 从当天开始，结束时间取时间极大值（避免数据频繁修改）-- 分区策略-- 绝大多数得维度表得分区策略都是以天为单位-- 分区不能采用开始日期作为分区字段-- 无法判断数据是否为历史状态还是最新状态-- 好得方式是使用结束时间为分区字段