离线数仓 (十二) --------- DIM 层搭建

布满荆棘的人生 2024-04-01 11:11 156阅读 0赞

目录

  • 一、商品维度表 (全量)
      1. Hive读取索引文件问题
      1. 首日装载
      1. 每日装载
  • 二、优惠券维度表 (全量)
      1. 首日装载
      1. 每日装载
  • 三、活动维度表 (全量)
      1. 首日装载
      1. 每日转载
  • 四、地区维度表 (特殊)
  • 五、时间维度表 (特殊)
      1. 创建临时表
      1. 导入数据
  • 六、用户维度表 (拉链表)
      1. 拉链表概述
      1. 制作拉链表
      1. 首日装载
      1. 每日装载
  • 七、DIM 层首日数据装载脚本
  • 八、DIM 层每日数据装载脚本

一、商品维度表 (全量)

A、建表语句

  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");

B、分区规划

在这里插入图片描述

C、数据装载
在这里插入图片描述

1. Hive读取索引文件问题

（1）两种方式，分别查询数据有多少行

  1. hive (gmall)> select * from ods_log;
  2. Time taken: 0.706 seconds, Fetched: 2955 row(s)
  4. hive (gmall)> select count(*) from ods_log;
  5. 2959

（2）两次查询结果不一致。

原因是 select * from ods_log 不执行 MR 操作，直接采用的是 ods_log 建表语句中指定的 DeprecatedLzoTextInputFormat，能够识别 lzo.index 为索引文件。
select count(*) from ods_log 执行 MR 操作，会先经过 hive.input.format，其默认值为 CombineHiveInputFormat，其会先将索引文件当成小文件合并，将其当做普通文件处理。更严重的是，这会导致 LZO 文件无法切片。

  1. hive (gmall)> 
  2. hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

解决办法：修改CombineHiveInputFormat为HiveInputFormat

  1. hive (gmall)> 
  2. set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

2. 首日装载

  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;

3. 每日装载

  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;

二、优惠券维度表 (全量)

A、建表语句

  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");

B、分区规划

在这里插入图片描述

C、数据装载

在这里插入图片描述

1. 首日装载

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

2. 每日装载

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

三、活动维度表 (全量)

A、建表语句

  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");

B、分区规划

在这里插入图片描述
C、数据装载

在这里插入图片描述

1. 首日装载

  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;

2. 每日转载

  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;

四、地区维度表 (特殊)

A、建表语句

  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");

B、数据装载

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

在这里插入图片描述

  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;

五、时间维度表 (特殊)

A、建表语句

  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");

B、数据装载

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

1. 创建临时表

DROP TABLE IF EXISTS tmp_dim_date_info;

  1. CREATE EXTERNAL TABLE tmp_dim_date_info (
  2.     `date_id` STRING COMMENT '日',
  3.     `week_id` STRING COMMENT '周ID',
  4.     `week_day` STRING COMMENT '周几',
  5.     `day` STRING COMMENT '每月的第几天',
  6.     `month` STRING COMMENT '第几月',
  7.     `quarter` STRING COMMENT '第几季度',
  8.     `year` STRING COMMENT '年',
  9.     `is_workday` STRING COMMENT '是否是工作日',
  10.     `holiday_id` STRING COMMENT '节假日'
  11. ) COMMENT '时间维度表'
  12. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  13. LOCATION '/warehouse/gmall/tmp/tmp_dim_date_info/';

2. 导入数据

将数据文件上传到 HFDS 上临时表指定路径 /warehouse/gmall/tmp/tmp_dim_date_info/

执行以下语句将其导入时间维度表

  1. insert overwrite table dim_date_info select * from tmp_dim_date_info;

检查数据是否导入成功

六、用户维度表 (拉链表)

1. 拉链表概述

A、什么是拉链表

拉链表，记录每条信息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，当前日期放入生效开始日期。如果当前信息至今有效，在生效结束日期中填入一个极大值(如9999-99-99)。

在这里插入图片描述

B、为什么要做拉链表

拉链表适合于：数据会发生变化，但是变化频率并不高的维度（即；缓慢变化维）。

比如：用户信息会发生变化，但是每天变化的比例不高。如果数据量有一定规模，按照每日全量的方式保存效率很低。

比如：1亿用户*365天，每天一份用户信息。（做每日全量效率低）

在这里插入图片描述

C、如何使用拉链表

通过，生效开始日期 <= 某个日期且生效结束日期某个日期，能够得到某个时间点的数据全量切片。

1）拉链表数据

在这里插入图片描述

2）例如获取2019-01-01的历史切片: select*from user_info where start_date=2019-01-01'and end date=2019-01-01

在这里插入图片描述

3）例如获取 2019-01-02 的历史切片: seleet*from order_info where start_datec="2019-01-02'and end_date=2019-01-02'

在这里插入图片描述

D、拉链表形成过程

在这里插入图片描述

1. 制作拉链表

A、建表语句

  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");

B、分区规划

在这里插入图片描述

C、数据装载

在这里插入图片描述

3. 首日装载

拉链表首日装载，需要进行初始化操作，具体工作为将截止到初始化当日的全部历史用户导入一次性导入到拉链表中。目前的 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';

4. 每日装载

A、实现思路

在这里插入图片描述

B、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;

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

A、编写脚本
在 /home/fancy/bin 目录下创建脚本 ods_to_dim_db_init.sh

  1. [fancy@node101 bin]$ vim ods_to_dim_db_init.sh

在脚本中填写如下内容

  1. #!/bin/bash
  3. APP=gmall
  5. if [ -n "$2" ] ;then
  6.    do_date=$2
  7. else 
  8.    echo "请传入日期参数"
  9.    exit
  10. fi 
  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. "
  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. "
  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. "
  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. "
  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. "
  227. case $1 in
  228. "dim_user_info"){
  230.     hive -e "$dim_user_info"
  231. };;
  232. "dim_sku_info"){
  234.     hive -e "$dim_sku_info"
  235. };;
  236. "dim_base_province"){
  238.     hive -e "$dim_base_province"
  239. };;
  240. "dim_coupon_info"){
  242.     hive -e "$dim_coupon_info"
  243. };;
  244. "dim_activity_rule_info"){
  246.     hive -e "$dim_activity_rule_info"
  247. };;
  248. "all"){
  250.     hive -e "$dim_user_info$dim_sku_info$dim_coupon_info$dim_activity_rule_info$dim_base_province"
  251. };;
  252. esac

增加执行权限

  1. [fancy@node101 bin]$ chmod +x ods_to_dim_db_init.sh

B、脚本使用

执行脚本

  1. [fancy@node101 bin]$ ods_to_dim_db_init.sh all 2020-06-14

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

查看数据是否导入成功

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

A、编写脚本

在 /home/fancy/bin 目录下创建脚本 ods_to_dim_db.sh

  1. [fancy@node101 bin]$ vim ods_to_dim_db.sh

在脚本中填写如下内容

  1. #!/bin/bash
  3. APP=gmall
  5. # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
  6. if [ -n "$2" ] ;then
  7.     do_date=$2
  8. else 
  9.     do_date=`date -d "-1 day" +%F`
  10. fi
  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. "
  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. "
  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. "
  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. "
  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. "
  317. case $1 in
  318. "dim_user_info"){
  320.     hive -e "$dim_user_info"
  321. };;
  322. "dim_sku_info"){
  324.     hive -e "$dim_sku_info"
  325. };;
  326. "dim_base_province"){
  328.     hive -e "$dim_base_province"
  329. };;
  330. "dim_coupon_info"){
  332.     hive -e "$dim_coupon_info"
  333. };;
  334. "dim_activity_rule_info"){
  336.     hive -e "$dim_activity_rule_info"
  337. };;
  338. "all"){
  340.     hive -e "$dim_user_info$dim_sku_info$dim_coupon_info$dim_activity_rule_info"
  341. };;
  342. esac

增加执行权限

  1. [fancy@node101 bin]$ chmod +x ods_to_dim_db.sh

B、脚本使用

执行脚本

  1. [fancy@node101 bin]$ ods_to_dim_db.sh all 2020-06-14

查看数据是否导入成功

发表评论

表情：
评论列表 （有 0 条评论，156人围观）

还没有评论，来说两句吧...

相关阅读