数据仓库哈哈

数据仓库

基本概念

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

数据库（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）建表语句

DROPTABLEIFEXISTS 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`BIGINTCOMMENT'时间戳')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）数据装载

loaddata inpath '/origin_data/gmall/log/topic_log/2022-06-08'intotable 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"];then
   do_date=$1else
   do_date=`date -d "-1 day" +%F`
fi

echo ================== 日志日期为 $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活动信息表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_activity_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

2活动规则表（全量表）

DROPTABLEIFEXISTS 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`BIGINTCOMMENT'满减件数',`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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_activity_rule_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

3一级品类表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_base_category1_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

4二级品类表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_base_category2_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

5三级品类表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_base_category3_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

6编码字典表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_base_dic_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

7省份表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_base_province_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

8地区表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_base_region_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

9品牌表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_base_trademark_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

10购物车表（全量表）

DROPTABLEIFEXISTS 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`BIGINTCOMMENT'数量',`img_url`BIGINTCOMMENT'商品图片地址',`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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_cart_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

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

DROPTABLEIFEXISTS 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`BIGINTCOMMENT'满件数',`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`BIGINTCOMMENT'最多领用次数',`taken_count`BIGINTCOMMENT'已领用次数',`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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_coupon_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

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

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_sku_attr_value_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

13商品表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_sku_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

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

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_sku_sale_attr_value_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

15SPU表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_spu_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

16营销坑位表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_promotion_pos_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

17营销渠道表（全量表）

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\t'NULL DEFINED AS''
LOCATION '/warehouse/gmall/ods/ods_promotion_refer_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

18购物车表（增量表）

DROPTABLEIFEXISTS ods_cart_info_inc;CREATE EXTERNAL TABLE ods_cart_info_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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评论表（增量表）

DROPTABLEIFEXISTS ods_comment_info_inc;CREATE EXTERNAL TABLE ods_comment_info_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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优惠券领用表（增量表）

DROPTABLEIFEXISTS ods_coupon_use_inc;CREATE EXTERNAL TABLE ods_coupon_use_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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收藏表（增量表）

DROPTABLEIFEXISTS ods_favor_info_inc;CREATE EXTERNAL TABLE ods_favor_info_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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订单明细表（增量表）

DROPTABLEIFEXISTS ods_order_detail_inc;CREATE EXTERNAL TABLE ods_order_detail_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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订单明细活动关联表（增量表）

DROPTABLEIFEXISTS ods_order_detail_activity_inc;CREATE EXTERNAL TABLE ods_order_detail_activity_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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订单明细优惠券关联表（增量表）

DROPTABLEIFEXISTS ods_order_detail_coupon_inc;CREATE EXTERNAL TABLE ods_order_detail_coupon_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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订单表（增量表）

DROPTABLEIFEXISTS ods_order_info_inc;CREATE EXTERNAL TABLE ods_order_info_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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退单表（增量表）

DROPTABLEIFEXISTS ods_order_refund_info_inc;CREATE EXTERNAL TABLE ods_order_refund_info_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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订单状态流水表（增量表）

DROPTABLEIFEXISTS ods_order_status_log_inc;CREATE EXTERNAL TABLE ods_order_status_log_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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支付表（增量表）

DROPTABLEIFEXISTS ods_payment_info_inc;CREATE EXTERNAL TABLE ods_payment_info_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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退款表（增量表）

DROPTABLEIFEXISTS ods_refund_payment_inc;CREATE EXTERNAL TABLE ods_refund_payment_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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用户表（增量表）

DROPTABLEIFEXISTS ods_user_info_inc;CREATE EXTERNAL TABLE ods_user_info_inc
(`type` STRING COMMENT'变动类型',`ts`BIGINTCOMMENT'变动时间',`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=gmall

if[-n"$2"];thendo_date=$2elsedo_date=`date-d'-1 day' +%F`fiload_data(){sql=""foriin$*;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');"fidone
    hive -e"$sql"}case$1in"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）建表语句

DROPTABLEIFEXISTS ads_traffic_stats_by_channel;CREATE EXTERNAL TABLE ads_traffic_stats_by_channel
(`dt`               STRING COMMENT'统计日期',`recent_days`BIGINTCOMMENT'最近天数,1:最近1天,7:最近7天,30:最近30天',`channel`          STRING COMMENT'渠道',`uv_count`BIGINTCOMMENT'访客人数',`avg_duration_sec`BIGINTCOMMENT'会话平均停留时长，单位为秒',`avg_page_count`BIGINTCOMMENT'会话平均浏览页面数',`sv_count`BIGINTCOMMENT'会话数',`bounce_rate`DECIMAL(16,2)COMMENT'跳出率')COMMENT'各渠道流量统计'ROW FORMAT DELIMITED FIELDSTERMINATEDBY'\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-- 确定表的列-- 建表语句DROPTABLEIFEXISTS 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`BOOLEANCOMMENT'是否在售',`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 ORC
    LOCATION '/warehouse/gmall/dim/dim_sku_full/'
    TBLPROPERTIES ('orc.compress'='snappy');-- 数据装载-- load-- 数据源一定是ODS层-- save-- 分区字段其实也是表的字段，但是我们一般称之为虚拟字段-- 数据字段(列)：存储在数据文件中-- 分区字段：存储在路径中-- 分区-- dt : date(日期)-- 策略：将每天采集的数据存放到ODS层的每天分区中-- 将ODS层每天的数据关联后存放到DIM层的每天分区中-- 分区存储应该采用overwrite而不是intoset hive.vectorized.execution.enabled=false;insert overwrite table dim_sku_full partition(dt='2022-06-08')select
    sku.`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_time
    from ods_sku_info_full
    where dt ='2022-06-08') sku
leftjoin(select
        id,
        spu_name
    from ods_spu_info_full
    where dt ='2022-06-08') spu on sku.spu_id = spu.id
leftjoin(select
        id,
        tm_name
    from ods_base_trademark_full
    where dt ='2022-06-08') tm on sku.tm_id = tm.id
leftjoin(select
        id,
        name category3_name,
        category2_id
    from ods_base_category3_full
    where dt ='2022-06-08') c3 on sku.category3_id = c3.id
leftjoin(select
        id,
        name category2_name,
        category1_id
    from ods_base_category2_full
    where dt ='2022-06-08') c2 on c3.category2_id = c2.id
leftjoin(select
        id,
        name category1_name
    from ods_base_category1_full
    where dt ='2022-06-08') c1 on c2.category1_id = c1.id
leftjoin(-- 将查询结果转换为结构体后，形成Array-- 将多个结构体的数据聚合成一个数组类型的数据（聚合操作）select
        sku_id,
        collect_list(named_struct("attr_id", attr_id,"value_id", value_id,"attr_name", attr_name,"value_name", value_name)) sku_attr_values
    from ods_sku_attr_value_full
    where dt ='2022-06-08'groupby sku_id
) sav on sku.id = sav.sku_id
leftjoin(select
        sku_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_values
    from ods_sku_sale_attr_value_full
    where dt ='2022-06-08'groupby 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-- 相关维表-- 建表语句-- 表名DROPTABLEIFEXISTS 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`BIGINTCOMMENT'满件数',`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`BIGINTCOMMENT'最多领取次数',`taken_count`BIGINTCOMMENT'已领取次数',`start_time`        STRING COMMENT'可以领取的开始时间',`end_time`          STRING COMMENT'可以领取的结束时间',`operate_time`      STRING COMMENT'修改时间',`expire_time`       STRING COMMENT'过期时间')COMMENT'优惠券维度表'
    PARTITIONED BY(`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_coupon_full/'
    TBLPROPERTIES ('orc.compress'='snappy');

2）数据装载

insert overwrite table dim_coupon_full partition(dt='2022-06-08')select
    id,
    coupon_name,
    coupon_type,
    coupon_dic.dic_name,
    condition_amount,
    condition_num,
    activity_id,
    benefit_amount,
    benefit_discount,case coupon_type
        when'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(select
        id,
        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_time
    from ods_coupon_info_full
    where dt='2022-06-08')ci
leftjoin(select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2022-06-08'and parent_code='32')coupon_dic
on ci.coupon_type=coupon_dic.dic_code
leftjoin(select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2022-06-08'and parent_code='33')range_dic
on ci.range_type=range_dic.dic_code;

3活动（规则）维度表

1）建表语句

-- 活动(规则)维度表-- activity_info-- activity_ruleDROPTABLEIFEXISTS 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`BIGINTCOMMENT'满减件数',`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 ORC
    LOCATION '/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,caserule.activity_type
        when'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(select
        id,
        activity_id,
        activity_type,
        condition_amount,
        condition_num,
        benefit_amount,
        benefit_discount,
        benefit_level
    from ods_activity_rule_full
    where dt='2022-06-08')ruleleftjoin(select
        id,
        activity_name,
        activity_type,
        activity_desc,
        start_time,
        end_time,
        create_time
    from ods_activity_info_full
    where dt='2022-06-08')info
onrule.activity_id=info.id
leftjoin(select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2022-06-08'and parent_code='31')dic
onrule.activity_type=dic.dic_code;

4地区维度表

1）建表语句

DROPTABLEIFEXISTS 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 ORC
    LOCATION '/warehouse/gmall/dim/dim_province_full/'
    TBLPROPERTIES ('orc.compress'='snappy');

2）数据装载

insert overwrite table dim_province_full partition(dt='2022-06-08')select
    province.id,
    province.name,
    province.area_code,
    province.iso_code,
    province.iso_3166_2,
    region_id,
    region_name
from(select
        id,
        name,
        region_id,
        area_code,
        iso_code,
        iso_3166_2
    from ods_base_province_full
    where dt='2022-06-08')province
leftjoin(select
        id,
        region_name
    from ods_base_region_full
    where dt='2022-06-08')region
on province.region_id=region.id;

5营销坑位维度表

1）建表语句

DROPTABLEIFEXISTS 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 ORC
    LOCATION '/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）建表语句

DROPTABLEIFEXISTS 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 ORC
    LOCATION '/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）建表语句

DROPTABLEIFEXISTS 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 ORC
    LOCATION '/warehouse/gmall/dim/dim_date/'
    TBLPROPERTIES ('orc.compress'='snappy');

2）数据装载

（1）创建临时表

DROPTABLEIFEXISTS 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 FIELDSTERMINATEDBY'\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_addressDROPTABLEIFEXISTS 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 ORC
    LOCATION '/warehouse/gmall/dim/dim_user_zip/'
    TBLPROPERTIES ('orc.compress'='snappy');

2）数据装载
① 首日装载

-- 全量表-- DataX-- TSV-- 增量表-- Maxwell-- JSON-- 首日(全量-select) : bootstrap (3种类型)-- 每日(增量-binlog) : insert, update, deleteinsert overwrite table dim_user_zip partition(dt ='9999-12-31')selectdata.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'andtype='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(partitionby id orderby start_date desc) rn
         from(select id,
                         name,
                         phone_num,
                         email,
                         user_level,
                         birthday,
                         gender,
                         create_time,
                         operate_time,
                         start_date,
                         end_date
                  from dim_user_zip
                  where 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_date
                  from(selectdata.id,data.name,data.phone_num,data.email,data.user_level,data.birthday,data.gender,data.create_time,data.operate_time,
                                  row_number()over(partitionbydata.id orderby ts desc) rn
                           from ods_user_info_inc
                           where dt ='2022-06-09') t1
                  where 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   : 无法判断-- 折中地考虑-- 从当天开始，结束时间取时间极大值（避免数据频繁修改）-- 分区策略-- 绝大多数得维度表得分区策略都是以天为单位-- 分区不能采用开始日期作为分区字段-- 无法判断数据是否为历史状态还是最新状态-- 好得方式是使用结束时间为分区字段

任务调度器

保证每一层的SQL跑完再跑下一层
在这里插入图片描述

标签：数据仓库

本文转载自: https://blog.csdn.net/Saniana/article/details/140303612
版权归原作者 Saniana 所有，如有侵权，请联系我们删除。

数据仓库

基本概念

数据库（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 : 共通表表达式

拉链表设计

任务调度器

发表评论

“数据仓库哈哈”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航