• 数仓学习笔记(4)——数仓搭建（ODS层和DIM层）

    目录

    一、数仓搭建-ODS层

    1、ODS层(用户行为数据)

    1.1 创建日志表ods_log

    1.2 Shell中单引号和双引号区别

    1.3 ODS层日志表加载数据脚本

    2、ODS层(业务数据)

    二、数仓搭建-DIM层

    1、商品维度表（全量）

    1.1 建表语句

    1.2 分区规划

    1.3 数据装载

    1.4 Hive读取索引文件问题

    1.5 首日装载和每日装载

    2、优惠卷维度表（全量）

    2.1 建表语句

    2.2 分区规划

    2.3 数据装载

    2.4 首日装载和每日装载

    3、活动维度表（全量）

    3.1 建表语句

    3.2 分区规划

    3.3 数据装载

     3.4 首日装载和每日装载

    4、地区维度表（特殊）

    4.1 建表语句

    4.2 数据装载

    5、时间维度表（特殊）

    5.1 建表语句

    5.2 数据装载

    6、拉链表

    6.1 拉链表概述

    6.2 制作拉链表

    7、DIM层首日装载数据脚本

    8、DIM层每日数据装载脚本

    一、数仓搭建-ODS层

    1、ODS层(用户行为数据)

    1.1 创建日志表ods_log

    1.2 Shell中单引号和双引号区别

    1.3 ODS层日志表加载数据脚本

    脚本中编写如下内容

    1. #!/bin/bash
    2.  
    3. # 定义变量方便修改
    4. APP=gmall
    5.  
    6. # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    7. if [ -n "$1" ] ;then
    8.    do_date=$1
    9. else 
    10.    do_date=`date -d "-1 day" +%F`
    11. fi 
    12.  
    13. echo ================== 日志日期为 $do_date ==================
    14. sql="
    15. load data inpath '/origin_data/$APP/log/topic_log/$do_date' into table ${APP}.ods_log partition(dt='$do_date');
    16. "
    17.  
    18. hive -e "$sql"
    19.  
    20. hadoop jar /opt/module/hadoop-3.1.3/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/$APP/ods/ods_log/dt=$do_date

    2、ODS层(业务数据)

    二、数仓搭建-DIM层

    DIM层储存的基本都为维度表，且基本都是进行全量同步操作

    1、商品维度表（全量）

    1.1 建表语句

    1. DROP TABLE IF EXISTS dim_sku_info;
    2. CREATE EXTERNAL TABLE dim_sku_info (
    3.     `id` STRING COMMENT '商品id',
    4.     `price` DECIMAL(16,2) COMMENT '商品价格',
    5.     `sku_name` STRING COMMENT '商品名称',
    6.     `sku_desc` STRING COMMENT '商品描述',
    7.     `weight` DECIMAL(16,2) COMMENT '重量',
    8.     `is_sale` BOOLEAN COMMENT '是否在售',
    9.     `spu_id` STRING COMMENT 'spu编号',
    10.     `spu_name` STRING COMMENT 'spu名称',
    11.     `category3_id` STRING COMMENT '三级分类id',
    12.     `category3_name` STRING COMMENT '三级分类名称',
    13.     `category2_id` STRING COMMENT '二级分类id',
    14.     `category2_name` STRING COMMENT '二级分类名称',
    15.     `category1_id` STRING COMMENT '一级分类id',
    16.     `category1_name` STRING COMMENT '一级分类名称',
    17.     `tm_id` STRING COMMENT '品牌id',
    18.     `tm_name` STRING COMMENT '品牌名称',
    19.     `sku_attr_values` ARRAY<STRUCT<attr_id:STRING,value_id:STRING,attr_name:STRING,value_name:STRING>> COMMENT '平台属性',
    20.     `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 '销售属性',
    21.     `create_time` STRING COMMENT '创建时间'
    22. ) COMMENT '商品维度表'
    23. PARTITIONED BY (`dt` STRING)
    24. STORED AS PARQUET
    25. LOCATION '/warehouse/gmall/dim/dim_sku_info/'
    26. TBLPROPERTIES ("parquet.compression"="lzo");

    这里的平台属性表由于每个商品的平台属性不一样，不会因为某个商品的特殊属性去增加一个字段。因此这里将商品的平台属性设为一个结构体，把所有的关于该商品的平台属性放到结构体中并最后由一个数组保存。

    1.2 分区规划

    1.3 数据装载

    1.4 Hive读取索引文件问题

    1.5 首日装载和每日装载

    首日装载：

    1. with
    2. sku as
    3. (
    4.     select
    5.         id,
    6.         price,
    7.         sku_name,
    8.         sku_desc,
    9.         weight,
    10.         is_sale,
    11.         spu_id,
    12.         category3_id,
    13.         tm_id,
    14.         create_time
    15.     from ods_sku_info
    16.     where dt='2020-06-14'
    17. ),
    18. spu as
    19. (
    20.     select
    21.         id,
    22.         spu_name
    23.     from ods_spu_info
    24.     where dt='2020-06-14'
    25. ),
    26. c3 as
    27. (
    28.     select
    29.         id,
    30.         name,
    31.         category2_id
    32.     from ods_base_category3
    33.     where dt='2020-06-14'
    34. ),
    35. c2 as
    36. (
    37.     select
    38.         id,
    39.         name,
    40.         category1_id
    41.     from ods_base_category2
    42.     where dt='2020-06-14'
    43. ),
    44. c1 as
    45. (
    46.     select
    47.         id,
    48.         name
    49.     from ods_base_category1
    50.     where dt='2020-06-14'
    51. ),
    52. tm as
    53. (
    54.     select
    55.         id,
    56.         tm_name
    57.     from ods_base_trademark
    58.     where dt='2020-06-14'
    59. ),
    60. attr as
    61. (
    62.     select
    63.         sku_id,
    64.         collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs
    65.     from ods_sku_attr_value
    66.     where dt='2020-06-14'
    67.     group by sku_id
    68. ),
    69. sale_attr as
    70. (
    71.     select
    72.         sku_id,
    73.         collect_set(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)) sale_attrs
    74.     from ods_sku_sale_attr_value
    75.     where dt='2020-06-14'
    76.     group by sku_id
    77. )
    78. insert overwrite table dim_sku_info partition(dt='2020-06-14')
    79. select
    80.     sku.id,
    81.     sku.price,
    82.     sku.sku_name,
    83.     sku.sku_desc,
    84.     sku.weight,
    85.     sku.is_sale,
    86.     sku.spu_id,
    87.     spu.spu_name,
    88.     sku.category3_id,
    89.     c3.name,
    90.     c3.category2_id,
    91.     c2.name,
    92.     c2.category1_id,
    93.     c1.name,
    94.     sku.tm_id,
    95.     tm.tm_name,
    96.     attr.attrs,
    97.     sale_attr.sale_attrs,
    98.     sku.create_time
    99. from sku
    100. left join spu on sku.spu_id=spu.id
    101. left join c3 on sku.category3_id=c3.id
    102. left join c2 on c3.category2_id=c2.id
    103. left join c1 on c2.category1_id=c1.id
    104. left join tm on sku.tm_id=tm.id
    105. left join attr on sku.id=attr.sku_id
    106. left join sale_attr on sku.id=sale_attr.sku_id;

    每日装载：

    1. with
    2. sku as
    3. (
    4.     select
    5.         id,
    6.         price,
    7.         sku_name,
    8.         sku_desc,
    9.         weight,
    10.         is_sale,
    11.         spu_id,
    12.         category3_id,
    13.         tm_id,
    14.         create_time
    15.     from ods_sku_info
    16.     where dt='2020-06-15'
    17. ),
    18. spu as
    19. (
    20.     select
    21.         id,
    22.         spu_name
    23.     from ods_spu_info
    24.     where dt='2020-06-15'
    25. ),
    26. c3 as
    27. (
    28.     select
    29.         id,
    30.         name,
    31.         category2_id
    32.     from ods_base_category3
    33.     where dt='2020-06-15'
    34. ),
    35. c2 as
    36. (
    37.     select
    38.         id,
    39.         name,
    40.         category1_id
    41.     from ods_base_category2
    42.     where dt='2020-06-15'
    43. ),
    44. c1 as
    45. (
    46.     select
    47.         id,
    48.         name
    49.     from ods_base_category1
    50.     where dt='2020-06-15'
    51. ),
    52. tm as
    53. (
    54.     select
    55.         id,
    56.         tm_name
    57.     from ods_base_trademark
    58.     where dt='2020-06-15'
    59. ),
    60. attr as
    61. (
    62.     select
    63.         sku_id,
    64.         collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs
    65.     from ods_sku_attr_value
    66.     where dt='2020-06-15'
    67.     group by sku_id
    68. ),
    69. sale_attr as
    70. (
    71.     select
    72.         sku_id,
    73.         collect_set(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)) sale_attrs
    74.     from ods_sku_sale_attr_value
    75.     where dt='2020-06-15'
    76.     group by sku_id
    77. )
    78. insert overwrite table dim_sku_info partition(dt='2020-06-15')
    79. select
    80.     sku.id,
    81.     sku.price,
    82.     sku.sku_name,
    83.     sku.sku_desc,
    84.     sku.weight,
    85.     sku.is_sale,
    86.     sku.spu_id,
    87.     spu.spu_name,
    88.     sku.category3_id,
    89.     c3.name,
    90.     c3.category2_id,
    91.     c2.name,
    92.     c2.category1_id,
    93.     c1.name,
    94.     sku.tm_id,
    95.     tm.tm_name,
    96.     attr.attrs,
    97.     sale_attr.sale_attrs,
    98.     sku.create_time
    99. from sku
    100. left join spu on sku.spu_id=spu.id
    101. left join c3 on sku.category3_id=c3.id
    102. left join c2 on c3.category2_id=c2.id
    103. left join c1 on c2.category1_id=c1.id
    104. left join tm on sku.tm_id=tm.id
    105. left join attr on sku.id=attr.sku_id
    106. left join sale_attr on sku.id=sale_attr.sku_id;

    2、优惠卷维度表（全量）

    2.1 建表语句

    1. DROP TABLE IF EXISTS dim_coupon_info;
    2. CREATE EXTERNAL TABLE dim_coupon_info(
    3.     `id` STRING COMMENT '购物券编号',
    4.     `coupon_name` STRING COMMENT '购物券名称',
    5.     `coupon_type` STRING COMMENT '购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券',
    6.     `condition_amount` DECIMAL(16,2) COMMENT '满额数',
    7.     `condition_num` BIGINT COMMENT '满件数',
    8.     `activity_id` STRING COMMENT '活动编号',
    9.     `benefit_amount` DECIMAL(16,2) COMMENT '减金额',
    10.     `benefit_discount` DECIMAL(16,2) COMMENT '折扣',
    11.     `create_time` STRING COMMENT '创建时间',
    12.     `range_type` STRING COMMENT '范围类型 1、商品 2、品类 3、品牌',
    13.     `limit_num` BIGINT COMMENT '最多领取次数',
    14.     `taken_count` BIGINT COMMENT '已领取次数',
    15.     `start_time` STRING COMMENT '可以领取的开始日期',
    16.     `end_time` STRING COMMENT '可以领取的结束日期',
    17.     `operate_time` STRING COMMENT '修改时间',
    18.     `expire_time` STRING COMMENT '过期时间'
    19. ) COMMENT '优惠券维度表'
    20. PARTITIONED BY (`dt` STRING)
    21. STORED AS PARQUET
    22. LOCATION '/warehouse/gmall/dim/dim_coupon_info/'
    23. TBLPROPERTIES ("parquet.compression"="lzo");

    2.2 分区规划

    2.3 数据装载

    2.4 首日装载和每日装载

    首日装载：

    这里不需要和别的表进行join操作，因此只需要select进行操作即可，这里看上去没有进行其他操作，但是数据的存储格式从ODS层的lzo压缩文件变为了DIM层的列式存储加压缩的格式。

    1. insert overwrite table dim_coupon_info partition(dt='2020-06-14')
    2. select
    3.     id,
    4.     coupon_name,
    5.     coupon_type,
    6.     condition_amount,
    7.     condition_num,
    8.     activity_id,
    9.     benefit_amount,
    10.     benefit_discount,
    11.     create_time,
    12.     range_type,
    13.     limit_num,
    14.     taken_count,
    15.     start_time,
    16.     end_time,
    17.     operate_time,
    18.     expire_time
    19. from ods_coupon_info
    20. where dt='2020-06-14';

    每日装载：

    1. insert overwrite table dim_coupon_info partition(dt='2020-06-15')
    2. select
    3.     id,
    4.     coupon_name,
    5.     coupon_type,
    6.     condition_amount,
    7.     condition_num,
    8.     activity_id,
    9.     benefit_amount,
    10.     benefit_discount,
    11.     create_time,
    12.     range_type,
    13.     limit_num,
    14.     taken_count,
    15.     start_time,
    16.     end_time,
    17.     operate_time,
    18.     expire_time
    19. from ods_coupon_info
    20. where dt='2020-06-15';

    3、活动维度表（全量）

    3.1 建表语句

    1. DROP TABLE IF EXISTS dim_activity_rule_info;
    2. CREATE EXTERNAL TABLE dim_activity_rule_info(
    3.     `activity_rule_id` STRING COMMENT '活动规则ID',
    4.     `activity_id` STRING COMMENT '活动ID',
    5.     `activity_name` STRING  COMMENT '活动名称',
    6.     `activity_type` STRING  COMMENT '活动类型',
    7.     `start_time` STRING  COMMENT '开始时间',
    8.     `end_time` STRING  COMMENT '结束时间',
    9.     `create_time` STRING  COMMENT '创建时间',
    10.     `condition_amount` DECIMAL(16,2) COMMENT '满减金额',
    11.     `condition_num` BIGINT COMMENT '满减件数',
    12.     `benefit_amount` DECIMAL(16,2) COMMENT '优惠金额',
    13.     `benefit_discount` DECIMAL(16,2) COMMENT '优惠折扣',
    14.     `benefit_level` STRING COMMENT '优惠级别'
    15. ) COMMENT '活动信息表'
    16. PARTITIONED BY (`dt` STRING)
    17. STORED AS PARQUET
    18. LOCATION '/warehouse/gmall/dim/dim_activity_rule_info/'
    19. TBLPROPERTIES ("parquet.compression"="lzo");

    3.2 分区规划

    3.3 数据装载

     3.4 首日装载和每日装载

    首日装载：

    1. insert overwrite table dim_activity_rule_info partition(dt='2020-06-14')
    2. select
    3.     ar.id,
    4.     ar.activity_id,
    5.     ai.activity_name,
    6.     ar.activity_type,
    7.     ai.start_time,
    8.     ai.end_time,
    9.     ai.create_time,
    10.     ar.condition_amount,
    11.     ar.condition_num,
    12.     ar.benefit_amount,
    13.     ar.benefit_discount,
    14.     ar.benefit_level
    15. from
    16. (
    17.     select
    18.         id,
    19.         activity_id,
    20.         activity_type,
    21.         condition_amount,
    22.         condition_num,
    23.         benefit_amount,
    24.         benefit_discount,
    25.         benefit_level
    26.     from ods_activity_rule
    27.     where dt='2020-06-14'
    28. )ar
    29. left join
    30. (
    31.     select
    32.         id,
    33.         activity_name,
    34.         start_time,
    35.         end_time,
    36.         create_time
    37.     from ods_activity_info
    38.     where dt='2020-06-14'
    39. )ai
    40. on ar.activity_id=ai.id;

    每日装载：

    1. insert overwrite table dim_activity_rule_info partition(dt='2020-06-15')
    2. select
    3.     ar.id,
    4.     ar.activity_id,
    5.     ai.activity_name,
    6.     ar.activity_type,
    7.     ai.start_time,
    8.     ai.end_time,
    9.     ai.create_time,
    10.     ar.condition_amount,
    11.     ar.condition_num,
    12.     ar.benefit_amount,
    13.     ar.benefit_discount,
    14.     ar.benefit_level
    15. from
    16. (
    17.     select
    18.         id,
    19.         activity_id,
    20.         activity_type,
    21.         condition_amount,
    22.         condition_num,
    23.         benefit_amount,
    24.         benefit_discount,
    25.         benefit_level
    26.     from ods_activity_rule
    27.     where dt='2020-06-15'
    28. )ar
    29. left join
    30. (
    31.     select
    32.         id,
    33.         activity_name,
    34.         start_time,
    35.         end_time,
    36.         create_time
    37.     from ods_activity_info
    38.     where dt='2020-06-15'
    39. )ai
    40. on ar.activity_id=ai.id;

    4、地区维度表（特殊）

    地区维度表一般不会改变，因此不需要每天都进行同步，因此就不需要进行分区

    4.1 建表语句

    1. DROP TABLE IF EXISTS dim_base_province;
    2. CREATE EXTERNAL TABLE dim_base_province (
    3.     `id` STRING COMMENT 'id',
    4.     `province_name` STRING COMMENT '省市名称',
    5.     `area_code` STRING COMMENT '地区编码',
    6.     `iso_code` STRING COMMENT 'ISO-3166编码，供可视化使用',
    7.     `iso_3166_2` STRING COMMENT 'IOS-3166-2编码，供可视化使用',
    8.     `region_id` STRING COMMENT '地区id',
    9.     `region_name` STRING COMMENT '地区名称'
    10. ) COMMENT '地区维度表'
    11. STORED AS PARQUET
    12. LOCATION '/warehouse/gmall/dim/dim_base_province/'
    13. TBLPROPERTIES ("parquet.compression"="lzo");

    4.2 数据装载

    地区维度表数据相对稳定，变化概率较低，故无需每日装载。

    1. insert overwrite table dim_base_province
    2. select
    3.     bp.id,
    4.     bp.name,
    5.     bp.area_code,
    6.     bp.iso_code,
    7.     bp.iso_3166_2,
    8.     bp.region_id,
    9.     br.region_name
    10. from ods_base_province bp
    11. join ods_base_region br on bp.region_id = br.id;

    5、时间维度表（特殊）

    5.1 建表语句

    1. DROP TABLE IF EXISTS dim_date_info;
    2. CREATE EXTERNAL TABLE dim_date_info(
    3.     `date_id` STRING COMMENT '日',
    4.     `week_id` STRING COMMENT '周ID',
    5.     `week_day` STRING COMMENT '周几',
    6.     `day` STRING COMMENT '每月的第几天',
    7.     `month` STRING COMMENT '第几月',
    8.     `quarter` STRING COMMENT '第几季度',
    9.     `year` STRING COMMENT '年',
    10.     `is_workday` STRING COMMENT '是否是工作日',
    11.     `holiday_id` STRING COMMENT '节假日'
    12. ) COMMENT '时间维度表'
    13. STORED AS PARQUET
    14. LOCATION '/warehouse/gmall/dim/dim_date_info/'
    15. TBLPROPERTIES ("parquet.compression"="lzo");

    5.2 数据装载

    通常情况下，时间维度表的数据并不是来自于业务系统，而是手动写入，并且由于时间维度表数据的可预见性，无须每日导入，一般可一次性导入一年的数据。

    日期数据文件是文本文件，而表数据是lzo压缩和parquet存储，因此直接load进表中是无法识别的。这里建一个临时表，不使用parquet存储和lzo压缩，将日期的文本文件先load该表中，然后再将该临时表中选择数据，去insert到时间维度表

    6、拉链表

    6.1 拉链表概述

     

    6.2 制作拉链表

    建表语句：

    1. DROP TABLE IF EXISTS dim_user_info;
    2. CREATE EXTERNAL TABLE dim_user_info(
    3.     `id` STRING COMMENT '用户id',
    4.     `login_name` STRING COMMENT '用户名称',
    5.     `nick_name` STRING COMMENT '用户昵称',
    6.     `name` STRING COMMENT '用户姓名',
    7.     `phone_num` STRING COMMENT '手机号码',
    8.     `email` STRING COMMENT '邮箱',
    9.     `user_level` STRING COMMENT '用户等级',
    10.     `birthday` STRING COMMENT '生日',
    11.     `gender` STRING COMMENT '性别',
    12.     `create_time` STRING COMMENT '创建时间',
    13.     `operate_time` STRING COMMENT '操作时间',
    14.     `start_date` STRING COMMENT '开始日期',
    15.     `end_date` STRING COMMENT '结束日期'
    16. ) COMMENT '用户表'
    17. PARTITIONED BY (`dt` STRING)
    18. STORED AS PARQUET
    19. LOCATION '/warehouse/gmall/dim/dim_user_info/'
    20. TBLPROPERTIES ("parquet.compression"="lzo");

    分区规划：

    数据装载：

    首日装载：

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

    1. insert overwrite table dim_user_info partition(dt='9999-99-99')
    2. select
    3.     id,
    4.     login_name,
    5.     nick_name,
    6.     md5(name),
    7.     md5(phone_num),
    8.     md5(email),
    9.     user_level,
    10.     birthday,
    11.     gender,
    12.     create_time,
    13.     operate_time,
    14.     '2020-06-14',
    15.     '9999-99-99'
    16. from ods_user_info
    17. where dt='2020-06-14';

    每日装载sql编写： 

    1. with
    2. tmp as
    3. (
    4.     select
    5.         old.id old_id,
    6.         old.login_name old_login_name,
    7.         old.nick_name old_nick_name,
    8.         old.name old_name,
    9.         old.phone_num old_phone_num,
    10.         old.email old_email,
    11.         old.user_level old_user_level,
    12.         old.birthday old_birthday,
    13.         old.gender old_gender,
    14.         old.create_time old_create_time,
    15.         old.operate_time old_operate_time,
    16.         old.start_date old_start_date,
    17.         old.end_date old_end_date,
    18.         new.id new_id,
    19.         new.login_name new_login_name,
    20.         new.nick_name new_nick_name,
    21.         new.name new_name,
    22.         new.phone_num new_phone_num,
    23.         new.email new_email,
    24.         new.user_level new_user_level,
    25.         new.birthday new_birthday,
    26.         new.gender new_gender,
    27.         new.create_time new_create_time,
    28.         new.operate_time new_operate_time,
    29.         new.start_date new_start_date,
    30.         new.end_date new_end_date
    31.     from
    32.     (
    33.         select
    34.             id,
    35.             login_name,
    36.             nick_name,
    37.             name,
    38.             phone_num,
    39.             email,
    40.             user_level,
    41.             birthday,
    42.             gender,
    43.             create_time,
    44.             operate_time,
    45.             start_date,
    46.             end_date
    47.         from dim_user_info
    48.         where dt='9999-99-99'
    49.     )old
    50.     full outer join
    51.     (
    52.         select
    53.             id,
    54.             login_name,
    55.             nick_name,
    56.             md5(name) name,
    57.             md5(phone_num) phone_num,
    58.             md5(email) email,
    59.             user_level,
    60.             birthday,
    61.             gender,
    62.             create_time,
    63.             operate_time,
    64.             '2020-06-15' start_date,
    65.             '9999-99-99' end_date
    66.         from ods_user_info
    67.         where dt='2020-06-15'
    68.     )new
    69.     on old.id=new.id
    70. )
    71. insert overwrite table dim_user_info partition(dt)
    72. select
    73.     nvl(new_id,old_id),
    74.     nvl(new_login_name,old_login_name),
    75.     nvl(new_nick_name,old_nick_name),
    76.     nvl(new_name,old_name),
    77.     nvl(new_phone_num,old_phone_num),
    78.     nvl(new_email,old_email),
    79.     nvl(new_user_level,old_user_level),
    80.     nvl(new_birthday,old_birthday),
    81.     nvl(new_gender,old_gender),
    82.     nvl(new_create_time,old_create_time),
    83.     nvl(new_operate_time,old_operate_time),
    84.     nvl(new_start_date,old_start_date),
    85.     nvl(new_end_date,old_end_date),
    86.     nvl(new_end_date,old_end_date) dt
    87. from tmp
    88. union all
    89. select
    90.     old_id,
    91.     old_login_name,
    92.     old_nick_name,
    93.     old_name,
    94.     old_phone_num,
    95.     old_email,
    96.     old_user_level,
    97.     old_birthday,
    98.     old_gender,
    99.     old_create_time,
    100.     old_operate_time,
    101.     old_start_date,
    102.     cast(date_add('2020-06-15',-1) as string),
    103.     cast(date_add('2020-06-15',-1) as string) dt
    104. from tmp
    105. where new_id is not null and old_id is not null;

    7、DIM层首日装载数据脚本

    1. #!/bin/bash
    2.  
    3. APP=gmall
    4.  
    5. if [ -n "$2" ] ;then
    6.    do_date=$2
    7. else 
    8.    echo "请传入日期参数"
    9.    exit
    10. fi 
    11.  
    12. dim_user_info="
    13. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    14. insert overwrite table ${APP}.dim_user_info partition(dt='9999-99-99')
    15. select
    16.     id,
    17.     login_name,
    18.     nick_name,
    19.     md5(name),
    20.     md5(phone_num),
    21.     md5(email),
    22.     user_level,
    23.     birthday,
    24.     gender,
    25.     create_time,
    26.     operate_time,
    27.     '$do_date',
    28.     '9999-99-99'
    29. from ${APP}.ods_user_info
    30. where dt='$do_date';
    31. "
    32.  
    33. dim_sku_info="
    34. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    35. with
    36. sku as
    37. (
    38.     select
    39.         id,
    40.         price,
    41.         sku_name,
    42.         sku_desc,
    43.         weight,
    44.         is_sale,
    45.         spu_id,
    46.         category3_id,
    47.         tm_id,
    48.         create_time
    49.     from ${APP}.ods_sku_info
    50.     where dt='$do_date'
    51. ),
    52. spu as
    53. (
    54.     select
    55.         id,
    56.         spu_name
    57.     from ${APP}.ods_spu_info
    58.     where dt='$do_date'
    59. ),
    60. c3 as
    61. (
    62.     select
    63.         id,
    64.         name,
    65.         category2_id
    66.     from ${APP}.ods_base_category3
    67.     where dt='$do_date'
    68. ),
    69. c2 as
    70. (
    71.     select
    72.         id,
    73.         name,
    74.         category1_id
    75.     from ${APP}.ods_base_category2
    76.     where dt='$do_date'
    77. ),
    78. c1 as
    79. (
    80.     select
    81.         id,
    82.         name
    83.     from ${APP}.ods_base_category1
    84.     where dt='$do_date'
    85. ),
    86. tm as
    87. (
    88.     select
    89.         id,
    90.         tm_name
    91.     from ${APP}.ods_base_trademark
    92.     where dt='$do_date'
    93. ),
    94. attr as
    95. (
    96.     select
    97.         sku_id,
    98.         collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs
    99.     from ${APP}.ods_sku_attr_value
    100.     where dt='$do_date'
    101.     group by sku_id
    102. ),
    103. sale_attr as
    104. (
    105.     select
    106.         sku_id,
    107.         collect_set(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)) sale_attrs
    108.     from ${APP}.ods_sku_sale_attr_value
    109.     where dt='$do_date'
    110.     group by sku_id
    111. )
    113. insert overwrite table ${APP}.dim_sku_info partition(dt='$do_date')
    114. select
    115.     sku.id,
    116.     sku.price,
    117.     sku.sku_name,
    118.     sku.sku_desc,
    119.     sku.weight,
    120.     sku.is_sale,
    121.     sku.spu_id,
    122.     spu.spu_name,
    123.     sku.category3_id,
    124.     c3.name,
    125.     c3.category2_id,
    126.     c2.name,
    127.     c2.category1_id,
    128.     c1.name,
    129.     sku.tm_id,
    130.     tm.tm_name,
    131.     attr.attrs,
    132.     sale_attr.sale_attrs,
    133.     sku.create_time
    134. from sku
    135. left join spu on sku.spu_id=spu.id
    136. left join c3 on sku.category3_id=c3.id
    137. left join c2 on c3.category2_id=c2.id
    138. left join c1 on c2.category1_id=c1.id
    139. left join tm on sku.tm_id=tm.id
    140. left join attr on sku.id=attr.sku_id
    141. left join sale_attr on sku.id=sale_attr.sku_id;
    142. "
    143.  
    144. dim_base_province="
    145. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    146. insert overwrite table ${APP}.dim_base_province
    147. select
    148.     bp.id,
    149.     bp.name,
    150.     bp.area_code,
    151.     bp.iso_code,
    152.     bp.iso_3166_2,
    153.     bp.region_id,
    154.     br.region_name
    155. from ${APP}.ods_base_province bp
    156. join ${APP}.ods_base_region br on bp.region_id = br.id;
    157. "
    158.  
    159. dim_coupon_info="
    160. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    161. insert overwrite table ${APP}.dim_coupon_info partition(dt='$do_date')
    162. select
    163.     id,
    164.     coupon_name,
    165.     coupon_type,
    166.     condition_amount,
    167.     condition_num,
    168.     activity_id,
    169.     benefit_amount,
    170.     benefit_discount,
    171.     create_time,
    172.     range_type,
    173.     limit_num,
    174.     taken_count,
    175.     start_time,
    176.     end_time,
    177.     operate_time,
    178.     expire_time
    179. from ${APP}.ods_coupon_info
    180. where dt='$do_date';
    181. "
    182.  
    183. dim_activity_rule_info="
    184. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    185. insert overwrite table ${APP}.dim_activity_rule_info partition(dt='$do_date')
    186. select
    187.     ar.id,
    188.     ar.activity_id,
    189.     ai.activity_name,
    190.     ar.activity_type,
    191.     ai.start_time,
    192.     ai.end_time,
    193.     ai.create_time,
    194.     ar.condition_amount,
    195.     ar.condition_num,
    196.     ar.benefit_amount,
    197.     ar.benefit_discount,
    198.     ar.benefit_level
    199. from
    200. (
    201.     select
    202.         id,
    203.         activity_id,
    204.         activity_type,
    205.         condition_amount,
    206.         condition_num,
    207.         benefit_amount,
    208.         benefit_discount,
    209.         benefit_level
    210.     from ${APP}.ods_activity_rule
    211.     where dt='$do_date'
    212. )ar
    213. left join
    214. (
    215.     select
    216.         id,
    217.         activity_name,
    218.         start_time,
    219.         end_time,
    220.         create_time
    221.     from ${APP}.ods_activity_info
    222.     where dt='$do_date'
    223. )ai
    224. on ar.activity_id=ai.id;
    225. "
    226.  
    227. case $1 in
    228. "dim_user_info"){
    229.     hive -e "$dim_user_info"
    230. };;
    231. "dim_sku_info"){
    232.     hive -e "$dim_sku_info"
    233. };;
    234. "dim_base_province"){
    235.     hive -e "$dim_base_province"
    236. };;
    237. "dim_coupon_info"){
    238.     hive -e "$dim_coupon_info"
    239. };;
    240. "dim_activity_rule_info"){
    241.     hive -e "$dim_activity_rule_info"
    242. };;
    243. "all"){
    244.     hive -e "$dim_user_info$dim_sku_info$dim_coupon_info$dim_activity_rule_info$dim_base_province"
    245. };;
    246. esac

    注意：该脚本不包含时间维度表的装载，时间维度表需要手动装载

    8、DIM层每日数据装载脚本

    1. #!/bin/bash
    2.  
    3. APP=gmall
    4.  
    5. # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    6. if [ -n "$2" ] ;then
    7.     do_date=$2
    8. else 
    9.     do_date=`date -d "-1 day" +%F`
    10. fi
    11.  
    12. dim_user_info="
    13. set hive.exec.dynamic.partition.mode=nonstrict;
    14. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    15. with
    16. tmp as
    17. (
    18.     select
    19.         old.id old_id,
    20.         old.login_name old_login_name,
    21.         old.nick_name old_nick_name,
    22.         old.name old_name,
    23.         old.phone_num old_phone_num,
    24.         old.email old_email,
    25.         old.user_level old_user_level,
    26.         old.birthday old_birthday,
    27.         old.gender old_gender,
    28.         old.create_time old_create_time,
    29.         old.operate_time old_operate_time,
    30.         old.start_date old_start_date,
    31.         old.end_date old_end_date,
    32.         new.id new_id,
    33.         new.login_name new_login_name,
    34.         new.nick_name new_nick_name,
    35.         new.name new_name,
    36.         new.phone_num new_phone_num,
    37.         new.email new_email,
    38.         new.user_level new_user_level,
    39.         new.birthday new_birthday,
    40.         new.gender new_gender,
    41.         new.create_time new_create_time,
    42.         new.operate_time new_operate_time,
    43.         new.start_date new_start_date,
    44.         new.end_date new_end_date
    45.     from
    46.     (
    47.         select
    48.             id,
    49.             login_name,
    50.             nick_name,
    51.             name,
    52.             phone_num,
    53.             email,
    54.             user_level,
    55.             birthday,
    56.             gender,
    57.             create_time,
    58.             operate_time,
    59.             start_date,
    60.             end_date
    61.         from ${APP}.dim_user_info
    62.         where dt='9999-99-99'
    63.         and start_date<'$do_date'
    64.     )old
    65.     full outer join
    66.     (
    67.         select
    68.             id,
    69.             login_name,
    70.             nick_name,
    71.             md5(name) name,
    72.             md5(phone_num) phone_num,
    73.             md5(email) email,
    74.             user_level,
    75.             birthday,
    76.             gender,
    77.             create_time,
    78.             operate_time,
    79.             '$do_date' start_date,
    80.             '9999-99-99' end_date
    81.         from ${APP}.ods_user_info
    82.         where dt='$do_date'
    83.     )new
    84.     on old.id=new.id
    85. )
    86. insert overwrite table ${APP}.dim_user_info partition(dt)
    87. select
    88.     nvl(new_id,old_id),
    89.     nvl(new_login_name,old_login_name),
    90.     nvl(new_nick_name,old_nick_name),
    91.     nvl(new_name,old_name),
    92.     nvl(new_phone_num,old_phone_num),
    93.     nvl(new_email,old_email),
    94.     nvl(new_user_level,old_user_level),
    95.     nvl(new_birthday,old_birthday),
    96.     nvl(new_gender,old_gender),
    97.     nvl(new_create_time,old_create_time),
    98.     nvl(new_operate_time,old_operate_time),
    99.     nvl(new_start_date,old_start_date),
    100.     nvl(new_end_date,old_end_date),
    101.     nvl(new_end_date,old_end_date) dt
    102. from tmp
    103. union all
    104. select
    105.     old_id,
    106.     old_login_name,
    107.     old_nick_name,
    108.     old_name,
    109.     old_phone_num,
    110.     old_email,
    111.     old_user_level,
    112.     old_birthday,
    113.     old_gender,
    114.     old_create_time,
    115.     old_operate_time,
    116.     old_start_date,
    117.     cast(date_add('$do_date',-1) as string),
    118.     cast(date_add('$do_date',-1) as string) dt
    119. from tmp
    120. where new_id is not null and old_id is not null;
    121. "
    122.  
    123. dim_sku_info="
    124. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    125. with
    126. sku as
    127. (
    128.     select
    129.         id,
    130.         price,
    131.         sku_name,
    132.         sku_desc,
    133.         weight,
    134.         is_sale,
    135.         spu_id,
    136.         category3_id,
    137.         tm_id,
    138.         create_time
    139.     from ${APP}.ods_sku_info
    140.     where dt='$do_date'
    141. ),
    142. spu as
    143. (
    144.     select
    145.         id,
    146.         spu_name
    147.     from ${APP}.ods_spu_info
    148.     where dt='$do_date'
    149. ),
    150. c3 as
    151. (
    152.     select
    153.         id,
    154.         name,
    155.         category2_id
    156.     from ${APP}.ods_base_category3
    157.     where dt='$do_date'
    158. ),
    159. c2 as
    160. (
    161.     select
    162.         id,
    163.         name,
    164.         category1_id
    165.     from ${APP}.ods_base_category2
    166.     where dt='$do_date'
    167. ),
    168. c1 as
    169. (
    170.     select
    171.         id,
    172.         name
    173.     from ${APP}.ods_base_category1
    174.     where dt='$do_date'
    175. ),
    176. tm as
    177. (
    178.     select
    179.         id,
    180.         tm_name
    181.     from ${APP}.ods_base_trademark
    182.     where dt='$do_date'
    183. ),
    184. attr as
    185. (
    186.     select
    187.         sku_id,
    188.         collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs
    189.     from ${APP}.ods_sku_attr_value
    190.     where dt='$do_date'
    191.     group by sku_id
    192. ),
    193. sale_attr as
    194. (
    195.     select
    196.         sku_id,
    197.         collect_set(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)) sale_attrs
    198.     from ${APP}.ods_sku_sale_attr_value
    199.     where dt='$do_date'
    200.     group by sku_id
    201. )
    203. insert overwrite table ${APP}.dim_sku_info partition(dt='$do_date')
    204. select
    205.     sku.id,
    206.     sku.price,
    207.     sku.sku_name,
    208.     sku.sku_desc,
    209.     sku.weight,
    210.     sku.is_sale,
    211.     sku.spu_id,
    212.     spu.spu_name,
    213.     sku.category3_id,
    214.     c3.name,
    215.     c3.category2_id,
    216.     c2.name,
    217.     c2.category1_id,
    218.     c1.name,
    219.     sku.tm_id,
    220.     tm.tm_name,
    221.     attr.attrs,
    222.     sale_attr.sale_attrs,
    223.     sku.create_time
    224. from sku
    225. left join spu on sku.spu_id=spu.id
    226. left join c3 on sku.category3_id=c3.id
    227. left join c2 on c3.category2_id=c2.id
    228. left join c1 on c2.category1_id=c1.id
    229. left join tm on sku.tm_id=tm.id
    230. left join attr on sku.id=attr.sku_id
    231. left join sale_attr on sku.id=sale_attr.sku_id;
    232. "
    233.  
    234. dim_base_province="
    235. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    236. insert overwrite table ${APP}.dim_base_province
    237. select
    238.     bp.id,
    239.     bp.name,
    240.     bp.area_code,
    241.     bp.iso_code,
    242.     bp.iso_3166_2,
    243.     bp.region_id,
    244.     bp.name
    245. from ${APP}.ods_base_province bp
    246. join ${APP}.ods_base_region br on bp.region_id = br.id;
    247. "
    248.  
    249. dim_coupon_info="
    250. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    251. insert overwrite table ${APP}.dim_coupon_info partition(dt='$do_date')
    252. select
    253.     id,
    254.     coupon_name,
    255.     coupon_type,
    256.     condition_amount,
    257.     condition_num,
    258.     activity_id,
    259.     benefit_amount,
    260.     benefit_discount,
    261.     create_time,
    262.     range_type,
    263.     limit_num,
    264.     taken_count,
    265.     start_time,
    266.     end_time,
    267.     operate_time,
    268.     expire_time
    269. from ${APP}.ods_coupon_info
    270. where dt='$do_date';
    271. "
    272.  
    273. dim_activity_rule_info="
    274. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
    275. insert overwrite table ${APP}.dim_activity_rule_info partition(dt='$do_date')
    276. select
    277.     ar.id,
    278.     ar.activity_id,
    279.     ai.activity_name,
    280.     ar.activity_type,
    281.     ai.start_time,
    282.     ai.end_time,
    283.     ai.create_time,
    284.     ar.condition_amount,
    285.     ar.condition_num,
    286.     ar.benefit_amount,
    287.     ar.benefit_discount,
    288.     ar.benefit_level
    289. from
    290. (
    291.     select
    292.         id,
    293.         activity_id,
    294.         activity_type,
    295.         condition_amount,
    296.         condition_num,
    297.         benefit_amount,
    298.         benefit_discount,
    299.         benefit_level
    300.     from ${APP}.ods_activity_rule
    301.     where dt='$do_date'
    302. )ar
    303. left join
    304. (
    305.     select
    306.         id,
    307.         activity_name,
    308.         start_time,
    309.         end_time,
    310.         create_time
    311.     from ${APP}.ods_activity_info
    312.     where dt='$do_date'
    313. )ai
    314. on ar.activity_id=ai.id;
    315. "
    316.  
    317. case $1 in
    318. "dim_user_info"){
    319.     hive -e "$dim_user_info"
    320. };;
    321. "dim_sku_info"){
    322.     hive -e "$dim_sku_info"
    323. };;
    324. "dim_base_province"){
    325.     hive -e "$dim_base_province"
    326. };;
    327. "dim_coupon_info"){
    328.     hive -e "$dim_coupon_info"
    329. };;
    330. "dim_activity_rule_info"){
    331.     hive -e "$dim_activity_rule_info"
    332. };;
    333. "all"){
    334.     hive -e "$dim_user_info$dim_sku_info$dim_coupon_info$dim_activity_rule_info"
    335. };;
    336. esac

  • 相关阅读:
    [TFF学习]官方教程jupyter运行记录_联邦学习之图像分类任务_1
    第四章 文件管理 九、文件系统的层次结构
    深度干货 | 38道Java基础面试题 （1.2W字详细解析）
    【单片机基础】C51语言基础
    位于同一子网下的ip在子网掩码配置错误的情况下如何进行通信（wireshrak抓包分析）
    vue3中sync修饰符的使用
    PID 控制理论
    Redis 内存淘汰策略，从根儿上理解
    Unity学习笔记---材质纹理&天空盒
    springmvc第十六个练习（多个拦截器的执行和拦截器过滤器的比较）
  • 原文地址：https://blog.csdn.net/qq_64557330/article/details/126613939