3.1 离线数仓—DIM层实现

前言

DIM层维度表总共有6张，前面完成了5张表，全都是全量快照维度表，最后一张用户维度表是拉链表，需要好好分析一下。

一、拉链表回顾

二、用户维度表

1.建表语句

DROP TABLE IF EXISTS dim_user_zip;
CREATE EXTERNAL TABLE dim_user_zip
(
    `id`           STRING COMMENT '用户id',
    `login_name`   STRING COMMENT '用户名称',
    `nick_name`    STRING COMMENT '用户昵称',
    `name`         STRING COMMENT '用户姓名',
    `phone_num`    STRING COMMENT '手机号码',
    `email`        STRING COMMENT '邮箱',
    `user_level`   STRING COMMENT '用户等级',
    `birthday`     STRING COMMENT '生日',
    `gender`       STRING COMMENT '性别',
    `create_time`  STRING COMMENT '创建时间',
    `operate_time` STRING COMMENT '操作时间',
    `start_date`   STRING COMMENT '开始日期',
    `end_date`     STRING COMMENT '结束日期'
) COMMENT '用户表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_user_zip/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

用户拉链表中除了一些基本的信息意外，还有两个额外的字段，一个是信息的开始日期，一个是信息的结束日期，在这个日期内，这条信息是有效的。

2.分区规划

我们将到现在位置的最新信息放到9999-12-31这个分区，将过期的信息放到过期那天的分区。

3.数据装载流程分析

4.数据流向分析

假设2020-06-14是系统的第一天，我们对用户表执行的是首日全量同步
2020-06-14：这一天ods层里user_info_inc表里都是bootstrap-insert类型的数据，我们将这些数据统统放到9999-12-31这个分区，并将这些数据的开始时间设置为2020-06-14
2020-06-15：这一天ods层里user_info_inc表里的数据是insert、update、delete类型的数据，我们将insert类型的数据放到9999-12-31这个分区，将update修改后新的数据放到9999-12-31这个分区里（要修改开始日期为当天2020-06-15，同时结束日期为9999-12-31），然后将修改前的数据放到2020-06-14这个分区里（，因为数据是在2020-06-15日发生变化的，所以要修改结束日期为前一天2020-06-14）
2020-06-16：这一天ods层里user_info_inc表里的数据是insert、update、delete类型的数据，我们将insert类型的数据放到9999-12-31这个分区，将update修改后新的数据放到9999-12-31这个分区里（要修改开始日期为当天2020-06-16，同时结束日期为9999-12-31），然后将修改前的数据放到2020-06-15这个分区里（，因为数据是在2020-06-16日发生变化的，所以要修改结束日期为前一天2020-06-15）
后面每天都是这样…

5.首日数据装载分析与实现

5.1 首日数据装载分析

首日ods层user_info_inc里的是全量同步数据，我们需要将首日文件夹里的数据全部放到dim_user_zip表的9999-12-31分区内。

5.2 首日数据装载实现

select
  data.id,
  data.login_name,
  data.nick_name,
  md5(data.name),
  md5(data.phone_num),
  md5(data.email),
  data.user_level,
  data.birthday,
  data.gender,
  data.create_time,
  data.operate_time,
  '2020-06-14' start_time,
  '9999-12-31' end_date
from ods_user_info_inc
where dt='2020-06-14' and type='bootstrap-insert';

md5()函数是为了对一些字段进行加密

6.每日数据装载分析与实现

6.1 每日数据装载分析

每日数据装载有两部分操作：
1）第一部分操作是找到type='insert’类型的数据，将这些数据全部放到9999-12-31这个分区；
2）第二部分操作是找到type='update’类型的数据，将修改后的数据放到9999-12-31这个分区（注意修改开始时间为今天，结束时间为9999-12-31），同时将修改前的数据放到前一天的分区（因为拿的就是前一天的数据，所以它的结束时间应该修改为前一天日期）。
比如在2020-06-16日这一天拿到了2020-06-15这一天的变更数据，对于insert类型的数据，要修改开始时间为2020-06-15；对于update类型的数据，修改后的数据的开始时间为2020-06-16，修改前的数据的开始时间为2020-06-15

6.2 每日数据装载实现方式一

第一种方式是分别找到insert和update类型的数据，分别插入到9999-12-31这张表，写两条sql语句
第一步，获取所有insert类型的数据：

6.3 每日数据装载实现方式二

方式二，写一条sql语句，获取所有类型的数据，跟9999-12-31这个分区的数据进行全连接，若9999-12-31存在但新数据不存在，则代表是原有数据，直接取原有数据；若9999-12-31分区不存在但新数据存在则代表是新增数据，直接取新增数据；若两个分区数据都存在，则代表是update数据，取新数据.
这种方式要将old表中独有的数据和新表中的所有数据插入到9999-12-31中，将old表中的update数据插入到日期前一天分区中，所有的数据都需要。
1）先获取当天所有的变更数据：

select
  id,
  login_name,
  nick_name,
  name,
  phone_num,
  email,
  user_level,
  birthday,
  gender,
  create_time,
  operate_time,
  '2020-06-15' start_date,
  '9999-12-31' end_date
from
(
select
  data.id,</