devbox
Cpp五条
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

离线数仓（11）：DIM层实现_ys region和gz region

作者：Cpp五条 | 2024-06-07 17:53:47

赞

踩

ys region和gz region

目录

0. 相关文章链接

1. DIM层概述

1.1. 数仓分层图

1.2. 数仓数据走向图

1.3. 实现目的

2. 地区维度表

2.1. 创建

2.2. 导入数据

2.3. 查看最终数据

2.4. 数据装载示意图

3. 商品维度表

3.1. 数据装载代码

3.2. 数据装载示意图

3.3. 说明

4. 时间维度表

4.1. 建表语句

4.2. 数据说明

4.3. 数据展示

5. 用户维度表

5.1. 拉链表概述

5.1.1. 什么是拉链表

5.1.2. 为什么要做拉链表

5.1.3. 如何使用拉链表

5.1.4. 拉链表形成过程

5.2. 数据装载代码

5.3. 数据装载示意图

6. 总结

0. 相关文章链接

 离线数仓文章汇总 

1. DIM层概述

1.1. 数仓分层图

1.2. 数仓数据走向图

1.3. 实现目的

根据如上数仓分层图和数仓数据走向图，可以看出dim层的主要目的有如下2点：

  • 对ods层相关维度进行聚合，减少表的数量（例如3级分类聚合成一张分类维度表），后续使用维度减少雪花模型的使用；
  • 一般维度表每天创建一个分区（商品维度表等），特殊维度表导入数据（时间维度表），需要记录历史变化的创建拉链表（用户维度表）；

2. 地区维度表

2.1. 创建

ODS建表语句如下：

  1. create EXTERNAL table ${yishou_data_dbname}.ods_fmys_region_dt(
  2. 	region_id BIGINT comment "地区id",
  3. 	parent_id BIGINT comment "父地区id",
  4. 	region_name STRING comment "地区名",
  5. 	region_type BIGINT comment "区域级别{0:国家,1:省份,2:城市,4:区域 }",
  6. 	agency_id BIGINT comment "未知用途",
  7. 	auto_time STRING comment "处理更新时间"
  8. )
  9. PARTITIONED BY (`dt` BIGINT COMMENT '日期分区(yyyymmdd)')
  10. STORED AS orc LOCATION 'obs://yishou-test/yishou_data_qa_test.db/ods_fmys_region_dt'
  11. ;

DIM建表语句如下：

  1. create EXTERNAL table ${yishou_data_dbname}.dim_region_info(
  2.     id BIGINT comment '区域id（包括区域、城市、省份、国家）'
  3.     , name STRING comment '区域名称（包括区域、城市、省份、国家）'
  4.     , type BIGINT comment '区域级别{0:国家,1:省份,2:城市,3:地区}'
  5.     , region_id BIGINT comment '地区id'
  6.     , region_name STRING comment '地区名称'
  7.     , city_id BIGINT comment '城市id'
  8.     , city_name STRING comment '城市名称'
  9.     , province_id BIGINT comment '省份id'
  10.     , province_name STRING comment '省份名称'
  11.     , country_id BIGINT comment '国家id'
  12.     , country_name STRING comment '国家名称'
  13.     , agency_id BIGINT comment '未知用途'
  14.     , auto_time STRING comment '处理更新时间'
  15. )
  16. PARTITIONED BY (`dt` BIGINT COMMENT '日期分区(yyyymmdd)')
  17. STORED AS orc LOCATION 'obs://yishou-test/yishou_data_qa_test.db/dim_region_info'
  18. ;

2.2. 导入数据

  1. -- 跑数脚本（从ODS层获取出昨天的数据，进行计算，写入到DIM昨天的分区中）
  2. with
  3. region_temp as (
  4.     select
  5.         region_id
  6.         , parent_id
  7.         , region_name
  8.         , region_type
  9.         , agency_id
  10.         , auto_time
  11.     from ${yishou_data_dbname}.ods_fmys_region_dt
  12.     where dt = ${one_day_ago}
  13. )
  14.  
  15. insert overwrite table ${yishou_data_dbname}.dim_region_info partition(dt = ${one_day_ago})
  16. SELECT
  17.     region_table.region_id as id
  18.     , region_table.region_name as name
  19.     , region_table.region_type as type
  20.     , case
  21.         when region_table.region_type = 3 then region_table.region_id
  22.       end as region_id
  23.     , case
  24.         when region_table.region_type = 3 then region_table.region_name
  25.       end as region_name
  26.     , case
  27.         when region_table.region_type = 3 then city_table.region_id
  28.         when region_table.region_type = 2 then region_table.region_id
  29.       end as city_id
  30.     , case
  31.         when region_table.region_type = 3 then city_table.region_name
  32.         when region_table.region_type = 2 then region_table.region_name
  33.       end as city_name
  34.     , case
  35.         when region_table.region_type = 3 then province_table.region_id
  36.         when region_table.region_type = 2 then city_table.region_id
  37.         when region_table.region_type = 1 then region_table.region_id
  38.       end as province_id
  39.     , case
  40.         when region_table.region_type = 3 then province_table.region_name
  41.         when region_table.region_type = 2 then city_table.region_name
  42.         when region_table.region_type = 1 then region_table.region_name
  43.       end as province_name
  44.     , case
  45.         when region_table.region_type = 3 then country_table.region_id
  46.         when region_table.region_type = 2 then province_table.region_id
  47.         when region_table.region_type = 1 then city_table.region_id
  48.         when region_table.region_type = 0 then region_table.region_id
  49.       end as country_id
  50.     , case
  51.         when region_table.region_type = 3 then country_table.region_name
  52.         when region_table.region_type = 2 then province_table.region_name
  53.         when region_table.region_type = 1 then city_table.region_name
  54.         when region_table.region_type = 0 then region_table.region_name
  55.       end as country_name
  56.     , region_table.agency_id as agency_id
  57.     , region_table.auto_time as auto_time
  58. FROM region_temp as region_table
  59.  
  60. left join region_temp as city_table
  61. on region_table.parent_id = city_table.region_id
  62.  
  63. left join region_temp as province_table
  64. on city_table.parent_id = province_table.region_id
  65.  
  66. left join region_temp as country_table
  67. on province_table.parent_id = country_table.region_id
  68.  
  69. DISTRIBUTE BY floor(rand()*1)
  70. ;

2.3. 查看最终数据

执行的SQL：

SELECT * FROM ${yishou_data_dbname}.dim_region_info where dt = 20220902 order by id;

结果展示：

2.4. 数据装载示意图

3. 商品维度表

3.1. 数据装载代码

注意：下述代码只写出了部分，将商品表（博主这里的电商描述跟业界的有点不一样，下述的goods_no可以理解成业界的spu_id）和商品扩展表关联，并将品类和供应商相关信息并入

  1.  
  2. with
  3. category_temp as (
  4.     -- 将ods层的品类表获取出来（ods层品类表为分区表，只需要最新的分区即可）
  5.     select
  6.         cat_id
  7.         , cat_name
  8.         , keywords
  9.         , cat_desc
  10.         , parent_id
  11.         , sort_order
  12.         , template_file
  13.         , measure_unit
  14.         , show_in_nav
  15.         , is_show
  16.         , grade
  17.         , lv
  18.         , rv
  19.         , filter_attr
  20.         , goods_weight
  21.         , goods_model_id
  22.         , img
  23.         , icon
  24.         , normal_weight
  25.         , hot_order
  26.         , is_recommend
  27.         , auto_time
  28.         , normal_refund
  29.         , completely_refund
  30.         , normal_refund_grade
  31.     from ${yishou_data_dbname}.ods_fmys_category_dt
  32.     where dt = ${one_day_ago}
  33. ),
  34. cat_temp as (
  35.     -- 业务库的品类表里面信息较少，进行聚集，需要获取一个品类的三级品类、二级品类、一级品类等所有信息
  36.     select
  37.         fc.cat_id,
  38.         fc.cat_name,
  39.         fc.sort_order,
  40.         fc.is_show,
  41.         fc.grade,
  42.         fc.goods_model_id,
  43.         fc.img,
  44.         fc.icon,
  45.         fc.normal_weight,
  46.         fc.hot_order,
  47.         fc.is_recommend,
  48.         fc.auto_time,
  49.         case
  50.             when fc.grade = 3 then fc.cat_id
  51.         end as third_cat_id,
  52.         case
  53.             when fc.grade = 3 then fc.cat_name
  54.         end as third_cat_name,
  55.         case
  56.             when fc.grade = 3 then fc1.cat_id
  57.             when fc.grade = 2 then fc.cat_id
  58.         end as second_cat_id,
  59.         case
  60.             when fc.grade = 3 then fc1.cat_name
  61.             when fc.grade = 2 then fc.cat_name
  62.         end as second_cat_name,
  63.         case
  64.             when fc.grade = 3 then fc2.cat_id
  65.             when fc.grade = 2 then fc1.cat_id
  66.             when fc.grade = 1 then fc.cat_id
  67.         end as primary_cat_id,
  68.         case
  69.             when fc.grade = 3 then fc2.cat_name
  70.             when fc.grade = 2 then fc1.cat_name
  71.             when fc.grade = 1 then fc.cat_name
  72.         end as primary_cat_name,
  73.         fc.normal_refund,
  74.         fc.completely_refund
  75.     from
  76.         category_temp fc
  77.         left join category_temp fc1 on fc.parent_id = fc1.cat_id
  78.         left join category_temp fc2 on fc1.parent_id = fc2.cat_id
  79. ),
  80. goods_lib_temp as (
  81.     -- 将ods层的商品表获取出来（ods层商品表为分区表，只需要最新的分区即可）
  82.     select
  83.         goods_no
  84.         , supply_id
  85.         , supplier_id
  86.         , brand_id
  87.         , market_id
  88.         , picker_group
  89.         , picker_group_code
  90.         , pgid
  91.         , cat_id
  92.         , goods_kh
  93.         , goods_name
  94.         , goods_desc
  95.         , goods_tag
  96.         , custom_tag
  97.         , enough_number
  98.         , goods_weight
  99.         , goods_model_id
  100.         , fabric_id
  101.         , market_price
  102.         , shop_price
  103.         , extra_price
  104.         , shoppe_price
  105.         , vip_discount
  106.         , old_price
  107.         , attribute
  108.         , goods_model_setting
  109.         , goods_img
  110.         , video_url
  111.         , video_status
  112.         , origin
  113.         , add_admin
  114.         , goods_status
  115.         , is_on_sale
  116.         , is_action
  117.         , action_value
  118.         , add_time
  119.         , action_time
  120.         , stop_time
  121.         , update_time
  122.         , update_admin
  123.         , size_chart
  124.         , fitting_report
  125.         , size_chart_id
  126.         , parts
  127.         , auto_time
  128.         , estimated_return_time
  129.         , activity_goods
  130.         , picker_assist
  131.         , vendor_supply_id
  132.     from ${yishou_data_dbname}.ods_fmys_goods_lib_dt
  133.     where dt = ${one_day_ago}
  134. ),
  135. goods_lib_ext_temp as (
  136.     -- 将ods层的商品扩展表获取出来（ods层商品扩展表为分区表，只需要最新的分区即可）
  137.     select
  138.         goods_no
  139.         , limit_day
  140.         , first_sale_period
  141.         , handle_time
  142.         , handle_admin
  143.         , is_stop_sale
  144.         , off_sale_remark
  145.         , pgm_recommend_desc
  146.         , ys_sort
  147.         , update_time
  148.         , create_time
  149.         , goods_type
  150.         , scheduling_day
  151.         , scheduling_day_time
  152.         , scheduling_erp_day
  153.         , scheduling_erp_day_time
  154.         , goods_from
  155.         , customize_one
  156.         , customize_two
  157.         , customize_three
  158.         , extra_plus
  159.         , put_off_times
  160.         , change_to_sale_stock
  161.         , if_supply_limit
  162.         , fabric_desc
  163.         , special_remark
  164.         , supply_channel
  165.         , grade
  166.         , is_sole
  167.         , model_param
  168.         , if_lock
  169.         , start_lock_time
  170.         , lock_time
  171.         , business_time
  172.         , benefit_point
  173.         , goods_style
  174.         , live_model_id
  175.         , is_delete
  176.         , shop_push_new
  177.         , one_hand
  178.         , one_hand_size_title
  179.         , extra_price_type
  180.         , is_no_reason_support
  181.         , is_advance_support
  182.         , advance_time
  183.     from ${yishou_data_dbname}.ods_fmys_goods_lib_ext_dt
  184.     where dt = ${one_day_ago}
  185. ),
  186. supply_temp as (
  187.     -- 将ods层的供应商表获取出来（ods层供应商表为分区表，只需要最新的分区即可）
  188.     select
  189.         supply_id
  190.         , supply_name
  191.         , supply_mobile_phone
  192.         , supply_telephone
  193.         , supply_wechat
  194.         , supply_status
  195.         , limit_day
  196.         , create_time
  197.         , create_admin
  198.         , update_time
  199.         , pgid
  200.         , supply_floor
  201.         , supply_area_id
  202.         , is_scal
  203.         , market_id
  204.         , supply_level
  205.         , settle_type
  206.         , settle_days
  207.         , next_settle_start_time
  208.         , last_settle_time
  209.         , need_settle_flag
  210.         , purchase_status
  211.         , share_status
  212.         , the_new_flag
  213.         , is_signed_contract
  214.         , city
  215.         , remark
  216.         , supplier_num
  217.         , pic
  218.         , auto_time
  219.         , is_strength
  220.         , license
  221.         , season
  222.         , age_group
  223.         , cat_ids
  224.         , styles
  225.         , styles_char
  226.         , is_show_brand
  227.         , advantage
  228.         , disadvantage
  229.         , avatar
  230.         , is_full
  231.         , is_self
  232.         , supply_nickname
  233.         , pg_member_id
  234.         , pg_id
  235.         , is_live
  236.         , live_type
  237.         , live_num
  238.         , is_lower_enter
  239.         , is_ka
  240.         , m_goal
  241.         , pg_say
  242.         , disable_count
  243.         , disable_remark
  244.         , is_order_ability
  245.         , shopId
  246.         , is_all_style
  247.         , baobmobile
  248.         , baob_allot
  249.         , fans
  250.         , picture
  251.         , baob_stock_sharing
  252.         , first_sync_time
  253.         , wcg_order_sync_time
  254.         , supply_grade
  255.         , second_styles
  256.         , supply_picker_group_member_id
  257.         , is_benchmarking
  258.         , is_self_need_audit
  259.         , as_plan
  260.         , all_rebuild_plan
  261.         , supply_developer_admin
  262.         , dgid
  263.         , dg_member_id
  264.         , recommend_desc
  265.         , follower_admin_id
  266.         , sub_acct_no
  267.         , is_sign
  268.         , is_screenshot_for_add_goods
  269.         , supply_action_value
  270.     from ${yishou_data_dbname}.ods_fmys_supply_dt
  271.     where dt = ${one_day_ago}
  272. ),
  273. insert overwrite table ${yishou_data_dbname}.dim_goods_no_info partition(dt = ${one_day_ago})
  274. select
  275.     *
  276. from goods_lib_temp
  277. left join goods_lib_ext_temp on goods_lib_temp.goods_no = goods_lib_ext_temp.goods_no
  278. left join cat_temp on goods_lib_temp.cat_id = cat_temp.cat_id
  279. left join supply_temp on goods_lib_temp.supply_id = supply_temp.supply_id
  280. ;

3.2. 数据装载示意图

3.3. 说明

        在上述代码中，主要是以业务库的商品表（注意：不同公司业务逻辑不同，博主这里的goods_no就相当于通俗的spu_id，goods_id就相当于sku_id）；对商品维度表（dim_goods_no_info）是一个每天的分区表，每天会保留全量数据；对商品属性关联时，比如上述商品关联品类和供应商时，会尽可能多的将品类和供应商的属性信息也关联到商品维度表中，虽然在这里计算会多点，但后续使用的时候就不需要关联其他属性了，在建设中，会尽可能的使用星型模型，减少后续的join操作。

4. 时间维度表

4.1. 建表语句

  1. CREATE EXTERNAL TABLE `dim_calendar`(
  2.     `date_time` STRING COMMENT '日期',
  3.     `date_str` STRING COMMENT '日期字符串',
  4.     `date_year` BIGINT COMMENT '年',
  5.     `date_month` BIGINT COMMENT '月',
  6.     `date_week` BIGINT COMMENT '周',
  7.     `date_his_week` BIGINT COMMENT '历史以来的第几周（周一到周日算一周）',
  8.     `date_his_week_fri` BIGINT COMMENT '历史以来的第几周（周五到周四算一周）',
  9.     `date_his_week_sat` BIGINT COMMENT '历史以来的第几周（周六到周五算一周）',
  10.     `date_his_week_sun` BIGINT COMMENT '历史以来的第几周（周日到周六算一周）',
  11.     `date_season` BIGINT COMMENT '季',
  12.     `weekday` BIGINT COMMENT '周几{周一:1,周二:2,周三:3,周四:4,周五:5,周六:6,周日:7}',
  13.     `lunar_date` STRING COMMENT '农历'
  14. )
  15. STORED AS ORC LOCATION 'obs://yishou-test/yishou_data_qa_test/dim_calendar'
  16. ;

4.2. 数据说明

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

4.3. 数据展示

5. 用户维度表

        因为用户的信息数据会变化，但变化频率又不高，如果每天在DIM层做一个分区会造成资源浪费（用户数据相对品类、供应商、商品等数据量比较多，而在ods层已经做了每天的快照备份）；这时就可以引入拉链表的概念，将用户的变化情况记录下来，每个变化记录一条数据。

5.1. 拉链表概述

5.1.1. 什么是拉链表

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

5.1.2. 为什么要做拉链表

        拉链表适合于：数据会发生变化，但是变化频率并不高的维度（即：缓慢变化维） 。比如：用户信息会发生变化，但是每天变化的比例不高。如果数据量有一定规模，按照每日全量的方式保存效率很低。 比如：1亿用户*365天，每天一份用户信息。(做每日全量效率低)

5.1.3. 如何使用拉链表

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

  • 例如获取2019-01-01的历史切片：select * from user_info where start_date<=’2019-01-01’ and end_date>=’2019-01-01’
  • 3）例如获取2019-01-02的历史切片： select * from order_info where start_date<=’2019-01-02’ and end_date>=’2019-01-02’

5.1.4. 拉链表形成过程

        通过业务库表的历史变化行为记录表（从上线之日起的历史行为记录，上线之前的为全量表），根据主键进行分区，按记录时间升序排序；当这条记录更改完之后，那在下次更改前，该主键对应的信息就是这条记录的信息；如果没有后续更改，可以设置一个最大值（如 9999-99-99）；第一条数据，可以设置开始时间为最小值（如 1970-01-01），表示从最开始就一直是这个信息。

5.2. 数据装载代码

        因为拉链表是记录数据的新增和变化，所以使用ods层的记录表（ods_fmys_users_record_dt）；如下代码所示，对每个user_id开窗，每个更改时间一条数据，当在这个时间点，用户修改了信息，那也就是说，从这个时间点到下个时间点，用户都使用的是这条记录的信息；如果下个时间没有，可以将end_date设置成 '9999-99-99 00:00:00' ，表示这是用户当前正在使用的信息；最后还可以将第一条数据的start_date设置成'1970-01-01 00:00:00'，表示用户截止到有记录之前都是使用的这条数据的信息。

        注意：当前拉链表只有等ods_fmys_users_record_dt上线之后才能使用，并且之前的数据已不能记录，只能统计上线之日起后续的更改；另外在下述代码中只贴出了3个字段，真正的用户信息是有很多字段的。

  1. insert overwrite table ${yishou_data_dbname}.dim_user_info
  2. select
  3.     user_id
  4.     , user_name
  5.     , password
  6.     , event
  7.     , record_time
  8.     , nano_time  
  9.     , case
  10.         when row_number_id = 1 then '1970-01-01 00:00:00'
  11.         else start_date
  12.       end as start_date
  13.     , end_date
  14.     , row_number_id
  15. from (
  16.     select
  17.         user_id
  18.         , user_name
  19.         , password
  20.         , event
  21.         , record_time
  22.         , nano_time
  23.         , from_unixtime(record_time / 1000, 'yyyy-MM-dd HH:mm:ss') as start_date
  24.         , lead(from_unixtime(record_time / 1000, 'yyyy-MM-dd HH:mm:ss'), 1, '9999-99-99 00:00:00') over(partition by user_id order by record_time, nano_time) as end_date
  25.         , row_number() over(partition by user_id order by record_time, nano_time) as row_number_id
  26.     from ${yishou_data_dbname}.ods_fmys_users_record_dt
  27.     where dt = ${one_day_ago}
  28. )
  29. ;

5.3. 数据装载示意图

6. 总结

在DIM层维度表中一共分为如下3种：

  • 按天全量维度表，会每天一个分区，并保存历史全量数据，例如商品表、品类表、供应商表；
  • 特殊维度表，只需要导入一次的（或者一年手动导入一次等），平常不用变化的，例如时间表；
  • 拉链表，用了记录历史变化数据的，每次变化就会有一条数据，所以也不用分区，例如用户表

维度表的注意事项：

  • 在维度表中，保存的都是该维度的基本属性，比如商品的颜色、尺码等，这些数据都是通过业务库表join而来；
  • 在维度表中，尽可能的不要对数据进行聚合，比如统计商品昨日的GMV、该供应商下的所有商品数，这不是维度表的职责，不应该在维度层来进行这样的操作；
  • 在维度表中，尽可能多的对维度的属性进行关联，比如商品维度表，可以将对应的品类各属性全部关联进去，这样在后续使用的时候就不需要再去join品类表了，尽可能的做到星型模型，减少雪花模型的使用；

注：其他 离线数仓 相关文章链接由此进 -> 离线数仓文章汇总

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/686458
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号