hive DIM 层数据装载解析

原创

弟大翻着洗

修改于 2024-09-01 13:51:01

1290

修改于 2024-09-01 13:51:01

文章被收录于专栏：脚本

简介

DIM 层主要是对相关状态数据的总结，我们主要关键点是对字段（维度）的确定

关联方式：

join 方式需要有关联条件

A B C D E 表示数据，空白表示空的数据，C是两张表的关联条件

Left Join红色数据 + 黄色数据

Right Join红色数据 + 绿色数据

Full Join 红色数据 + 黄色数据 + 绿色数据

Join红色数据

Union方式需要两个表列数相同，具有相近属性的列 Union Union All

应用场景：

Left Join

用法：当你想要从左表（A）获取所有记录，并且希望包含与右表（B）匹配的记录，即使右表没有匹配时也会返回左表的记录，右表则显示空

Right Join

用法：与 Left Join 相似，但从右表获取所有记录，并连接左表中的匹配记录。

Full Join

用法：获取两个表中的所有记录，无论是否有匹配。

Union

用法：当你需要从两个查询中获取所有唯一的结果。

Union All

用法：类似于 Union，但包括重复行。

营销坑位维度表

原始业务数据库中只有promotion_pos表与营销坑位维度相关，从ods_promotion_pos_full表中筛选2022-06-08分区的数据，选择所须字段写入dim_promotion_pos_full表的2022-06-08分区即可

insert overwrite table dim_promotion_pos_full partition (dt = '2022-06-08')
select
    `id`                    ,   --   STRING COMMENT '营销坑位ID',
    `pos_location`          ,   --   STRING COMMENT '营销坑位位置',
    `pos_type`              ,   --   STRING COMMENT '营销坑位类型 ',
    `promotion_type`        ,   --   STRING COMMENT '营销类型',
    `create_time`           ,   --   STRING COMMENT '创建时间',
    `operate_time`              --   STRING COMMENT '修改时间'
from ods_promotion_pos_full
where dt = '2022-06-08';

营销渠道维度表

原始业务数据库中只有promotion_refer表与营销渠道维度相关，从ods_promotion_refer_full表中筛选2022-06-08分区的数据，选取所须字段写入dim_promotion_refer_full表的2022-06-08分区即可

insert overwrite table dim_promotion_refer_full partition (dt = '2022-06-08')
select
    `id`                    ,   --   STRING COMMENT '营销坑位ID',
    `refer_name`            ,   --   STRING COMMENT '营销渠道名称',
    `create_time`           ,   --   STRING COMMENT '创建时间',
    `operate_time`              --   STRING COMMENT '修改时间'
from ods_promotion_refer_full
where dt = '2022-06-08';

地区维度表

原始业务数据库中与地区相关的表有base_province和base_region，二者通过region_id产生联系。下单等业务过程相关的表中都通过province_id字段与地区维度产生关联，显然base_province应为主维表

insert overwrite table dim_province_full partition (dt = '2022-06-08')
select
     prv.`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 '地区名称'
from (
    select
        `id`                    ,
         name `province_name`   ,
        `area_code`             ,
        `iso_code`              ,
        `iso_3166_2`            ,
        `region_id`
    from ods_base_province_full
    where dt = '2022-06-08'
) prv
left join (
    select
        id,
        region_name
    from ods_base_region_full
    where dt = '2022-06-08'
) area on prv.region_id = area.id;

日期维度表

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

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


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


insert overwrite table dim_date select * from tmp_dim_date_info;

商品维度表

商品维度相关的业务表有八张：

sku_info，spu_info，base_trademark，base_category1_info，base_category2_info，base_category3_info，sku_attr_value，sku_sale_attr_value

这些表都做了全量采集，在ODS层有相应的原始表与之对应。基于维度建模理论，我们要确定主维表，将商品维度相关的原始表关联起来。维度表的粒度与主维表保持一致，后者的主键就是维度表的唯一标识。

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`       , -- COMMENT '平台属性',
    `sku_sale_attr_values`  , --COMMENT '销售属性',
    `create_time`             -- STRING COMMENT '创建时间'
from (
    select
         `id`                   ,
         `price`                ,
         `sku_name`             ,
         `sku_desc`             ,
         `weight`               ,
         `is_sale`              ,
         `spu_id`               ,
         `category3_id`         ,
         `tm_id`                ,
         `create_time`
    from ods_sku_info_full
    where dt = '2022-06-08'
) sku
left join (
    select
           id,
           spu_name
    from ods_spu_info_full
    where dt = '2022-06-08'
) spu on sku.spu_id = spu.id
left join (
    select
           id,
           tm_name
    from ods_base_trademark_full
    where dt = '2022-06-08'
) tm on sku.tm_id = tm.id
left join (
    select
           id,
           name category3_name,
           category2_id
    from ods_base_category3_full
    where dt = '2022-06-08'
) c3 on sku.category3_id = c3.id
left join (
    select
           id,
           name category2_name,
           category1_id
    from ods_base_category2_full
    where dt = '2022-06-08'
) c2 on c3.category2_id = c2.id
left join (
    select
           id,
           name category1_name
    from ods_base_category1_full
    where dt = '2022-06-08'
) c1 on c2.category1_id = c1.id
left join (
    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'
    group by sku_id
) sav on sku.id = sav.sku_id
left join (
    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'
    group by sku_id
) ssav on sku.id = ssav.sku_id;

活动维度表

活动相关的原始业务表有activity_rule、activity_info，此外，为了获取活动类型名称，还需要关联字典表。activity_rule中记录了活动的规则描述，activity_info记录了活动描述，用户下单时，每条明细记录都可能参与活动，order_detail_activity（订单活动关联表）记录了这些信息，该表中记录的是每个SKU具体参与了那次活动，满足了该活动的哪条规则，因此，要让事实表与活动维度进行关联，活动维度表的粒度应细化至活动规则粒度。综上，以activity_rule作为主表

insert overwrite table dim_activity_full partition (dt = '2022-06-08')
select
    `activity_rule_id`          ,   --  STRING COMMENT '活动规则ID',
    `activity_id`               ,   --  STRING COMMENT '活动ID',
    `activity_name`             ,   --  STRING COMMENT '活动名称',
    `activity_type_code`        ,   --  STRING COMMENT '活动类型编码',
    `activity_type_name`        ,   --  STRING COMMENT '活动类型名称',
    `activity_desc`             ,   --  STRING COMMENT '活动描述',
    `start_time`                ,   --  STRING COMMENT '开始时间',
    `end_time`                  ,   --  STRING COMMENT '结束时间',
    `create_time`               ,   --  STRING COMMENT '创建时间',
    `condition_amount`          ,   --  DECIMAL(16, 2) COMMENT '满减金额',
    `condition_num`             ,   --  BIGINT COMMENT '满减件数',
    `benefit_amount`            ,   --  DECIMAL(16, 2) COMMENT '优惠金额',
    `benefit_discount`          ,   --  DECIMAL(16, 2) COMMENT '优惠折扣',
    `benefit_rule`              ,   --  STRING COMMENT '优惠规则',
    `benefit_level`                 --  STRING COMMENT '优惠级别'
from (
    select
         id `activity_rule_id`              ,
        `activity_id`                       ,
         activity_type `activity_type_code` ,
        `create_time`                       ,
        `condition_amount`                  ,
        `condition_num`                     ,
        `benefit_amount`                    ,
        `benefit_discount`                  ,
         case `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 ods_activity_rule_full
    where dt = '2022-06-08'
) rule
left join (
    select
        `id`,
        `activity_name`,
        `activity_desc`      ,
        `start_time`         ,
        `end_time`
    from ods_activity_info_full
    where dt = '2022-06-08'
) info on rule.activity_id = info.id
left join (
    select
        dic_code,
        dic_name activity_type_name
    from ods_base_dic_full
    where dt = '2022-06-08' and parent_code = '31'
) dic on rule.activity_type_code = dic.dic_code;

优惠券维度表

我们只会用到字典表的编码和名称两个字段，单独建表意义不大，应做维度退化。优惠券相关的原始业务表只有coupon_info，不需要确定主维表和相关维表。因此，只须关联ods_coupon_info_full与ods_base_dic_full。此时ods_base_dic_full表需要当成两种场合来用（ dic_name coupon_type_name，dic_name range_type_name），则不可直接使用where条件一次判断（where是一行一行判断，条件不符时直接进行下一行的判断）。于是我们可以使用把该表当成两张表使用，进行两次连接

insert overwrite table dim_coupon_full partition (dt = '2022-06-28')
select
    `id`                            ,  --  STRING COMMENT '优惠券编号',
    `coupon_name`                   ,  --  STRING COMMENT '优惠券名称',
    `coupon_type_code`              ,  --  STRING COMMENT '优惠券类型编码',
    `coupon_type_name`              ,  --  STRING COMMENT '优惠券类型名称',
    `condition_amount`              ,  --  DECIMAL(16, 2) COMMENT '满额数',
    `condition_num`                 ,  --  BIGINT COMMENT '满件数',
    `activity_id`                   ,  --  STRING COMMENT '活动编号',
    `benefit_amount`                ,  --  DECIMAL(16, 2) COMMENT '减免金额',
    `benefit_discount`              ,  --  DECIMAL(16, 2) COMMENT '折扣',
    `benefit_rule`                  ,  --  STRING COMMENT '优惠规则:满元*减*元，满*件打*折',
    `create_time`                   ,  --  STRING COMMENT '创建时间',
    `range_type_code`               ,  --  STRING COMMENT '优惠范围类型编码',
    `range_type_name`               ,  --  STRING COMMENT '优惠范围类型名称',
    `limit_num`                     ,  --  BIGINT COMMENT '最多领取次数',
    `taken_count`                   ,  --  BIGINT COMMENT '已领取次数',
    `start_time`                    ,  --  STRING COMMENT '可以领取的开始时间',
    `end_time`                      ,  --  STRING COMMENT '可以领取的结束时间',
    `operate_time`                  ,  --  STRING COMMENT '修改时间',
    `expire_time`                      --  STRING COMMENT '过期时间'
from (
    select
        `id`                              ,
        `coupon_name`                     ,
         coupon_type `coupon_type_code`   ,
        `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_type_code`      ,
        `limit_num`                       ,
        `taken_count`                     ,
        `start_time`                      ,
        `end_time`                        ,
        `operate_time`                    ,
        `expire_time`
    from ods_coupon_info_full
    where dt = '2022-06-08'
) cp
left join (
    select
        dic_code,
        dic_name coupon_type_name
    from ods_base_dic_full
    where dt = '2022-06-08' and parent_code = '32'
) dic1 on cp.coupon_type_code = dic1.dic_code
left join (
    select
        dic_code,
        dic_name range_type_name
    from ods_base_dic_full
    where dt = '2022-06-08' and parent_code = '33'
) dic2 on cp.range_type_code = dic2.dic_code;

用户维度表

由于一般电商网站的用户的基数过大，不适合每日全量进行数据的存储，这里我们采用拉链的思维（将变化的数据记录下来，保存每个用户有效期内最后的状态）进行数据的存储

拉链表的分区有两类：9999-12-31分区和普通日期分区。前者保存最新的维度数据，后者保存有效期截至分区日期的数据。

首日

业务数据库的user_info表中记录了全量最新的用户数据，全部进入9999-12-31分区。

每日

从数仓上线次日开始，采集user_info的变更数据，通常业务数据库的数据不会被删除，因而用户信息的变更只有新增和修改两类。新增的用户信息进入9999-12-31分区，修改的用户信息覆盖9999-12-31分区的记录，并将历史数据写入前一分区（如某用户信息在2022-06-09发生变化，当日的历史用户信息有效期截至2022-06-08，进入2022-06-08分区）。

数据装载

首日数据装载：筛选所须字段，对敏感信息加密脱敏，写入9999-12-31分区即可。

每日数据装载较为复杂。首先要考虑到，用户的数据可能在一天内多次变化，而拉链表中对于同一个用户每天至多只会维护一条数据，因此只须保留同一用户每天的最后一次更改。此处不需要区分新增和修改操作，我们只要获取同一用户当天最晚的一次操作就可以获取其最新状态。

接下来，要将当日发生变更的用户信息与历史所有用户的最新信息（拉链表9999-12-31分区的数据）合并起来。最后，在9999-12-31分区保留每个用户最新的状态，并将过期数据写入当日分区。

以下图片来自尚硅谷教学资料

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


# 每日装载
set hive.exec.dynamic.partition.mode=nonstrict
insert overwrite table dim_user_zip partition (dt)
select
            `id`                    , -- 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 '开始日期',     
            `if`(rn == 2 , date_sub('2022-06-09' , 1)  , '9999-12-31') `end_date`,
            `if`(rn == 2 , date_sub('2022-06-09' , 1)  , '9999-12-31')
from (
    select
            `id`                    ,
            `name`                  ,
            `phone_num`             ,
            `email`                 ,
            `user_level`            ,
            `birthday`              ,
            `gender`                ,
            `create_time`           ,
            `operate_time`          ,
            `start_date`            ,
            `end_date`              ,
            row_number() over (partition by id order by start_date desc) 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'
        union all
        select
            `id`                ,
            `name`              ,
            `phone_num`         ,
            `email`             ,
            `user_level`        ,
            `birthday`          ,
            `gender`            ,
            `create_time`       ,
            `operate_time`      ,
            '2022-06-09'        ,
            '9999-12-31'
        from (
            select
                data.`id`                ,
                data.`name`              ,
                data.`phone_num`         ,
                data.`email`             ,
                data.`user_level`        ,
                data.`birthday`          ,
                data.`gender`            ,
                data.`create_time`       ,
                data.`operate_time`      ,
                row_number() over (partition by data.id order by ts desc) num
            from ods_user_info_inc
            where dt = '2022-06-09' and type in ('insert' , 'updata')
        ) a where num = 1
    ) t
) t1;

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

hive

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

hive

登录后参与评论

0 条评论

热度