拉链表相关练习

需求：在mysql数据库中有一个人员信息表（userinfo）

数据字段如下：
列名 类型 说明
user_id varchar(50) 用户编号
phone_number varchar(50) 手机号
health_code varchar(50) 健康码的颜色(绿色，黄色，红色)
reg_time varchar(50) 用户注册日期
modify_time varchar(50) 健康码颜色修改日期
在Mysql中的建表语句为：

– 创建数据库

create database if not exists exam;

– 创建人员表

create table if not exists exam.userinfo(
user_id varchar(50), – 人员编号
phone_number varchar(50), – 人员手机号
health_code varchar(50), – 健康码的颜色
reg_time varchar(50), – 用户注册日期
modify_time varchar(50) – 健康码颜色修改日期
);

2020年04月30日的数据如下：

user_id phone_number health_code reg_time modify_time
001 15953100001 绿色 2020-03-14 2020-04-30
002 15953100002 绿色 2020-03-14 2020-04-30
003 15953100003 黄色 2020-03-14 2020-04-30
004 15953100004 红色 2020-03-14 2020-04-30

插入语句:

insert into exam.userinfo(user_id, phone_number, health_code, reg_time,modify_time) values
(‘001’, ‘15953100001’, ‘绿色’, ‘2020-03-14’, ‘2020-04-30’),
(‘002’, ‘15953100002’, ‘绿色’, ‘2020-03-14’, ‘2020-04-30’),
(‘003’, ‘15953100003’, ‘黄色’, ‘2020-03-14’, ‘2020-04-30’),
(‘004’, ‘15953100004’, ‘红色’, ‘2020-03-14’, ‘2020-04-30’);

创建 hive_ods层 数据库

create database users;

创建 hive表

create table userinfo(
user_id string, – 人员编号
phone_number string, – 人员手机号
health_code string, – 健康码的颜色
reg_time string, – 用户注册日期
modify_time string – 健康码颜色修改日期
)partitioned by (dt string)
STORED AS PARQUET;

kettle导入全量数据

创建 dw 层

create database users_dw;

create table userinfo_dw(
user_id string, – 人员编号
phone_number string, – 人员手机号
health_code string, – 健康码的颜色
reg_time string, – 用户注册日期
modify_time string, – 健康码颜色修改日期
dw_start_date string,
dw_end_date string
)
STORED AS PARQUET;

导入全量数据到 dw层

insert overwrite table userinfo_dw
select user_id string, – 人员编号
phone_number string, – 人员手机号
health_code string, – 健康码的颜色
reg_time string, – 用户注册日期
modify_time string, – 健康码颜色修改日期
modify_time as dw_start_date, – 生效日期
‘9999-12-31’ as dw_end_date – 失效日期
from users.userinfo
where dt = ‘20200430’;

2020年05月01日的数据如下：

user_id phone_number health_code reg_time modify_time
001 15953100001 绿色 2020-03-14 2020-05-01
002 15953100002 绿色 2020-03-14 2020-05-01
003 15953100003 绿色 2020-03-14 2020-05-01
004 15953100004 黄色 2020-03-14 2020-05-01
005 15953100005 绿色 2020-05-01 2020-05-01

修改和添加的语句

：
UPDATE exam.userinfo SET health_code = ‘绿色’, modify_time = ‘2020-05-01’ WHERE user_id = ‘003’;
UPDATE exam.userinfo SET health_code = ‘黄色’, modify_time = ‘2020-05-01’ WHERE user_id = ‘004’;
INSERT INTO exam.userinfo(user_id, phone_number, health_code, reg_time,modify_time) VALUES
(‘005’, ‘15953100005’, ‘绿色’, ‘2020-05-01’, ‘2020-05-01’);

hive表创建0501分区

alter table users.userinfo add if not exists partition (dt=‘20200501’)

修复分区

msck repair table users.userinfo;

导入 0501 的 增量数据

编写SQL处理dw层历史数据，重新计算之前的dw_end_date

select t1.user_id, – 商品编号
t1.phone_number, – 商品状态
t1.health_code, – 商品创建时间
t1.reg_time,
t1.modify_time, – 商品修改时间
t1.dw_start_date, – 生效日期（生效日期无需重新计算）
case
when (t2.user_id is not null and t1.dw_end_date > ‘2020-05-01’)
then ‘2020-05-01’
else t1.dw_end_date – 小的是以前修改的，不用修改，只修改9999-12-31的数据
end as dw_end_date – 更新生效日期（需要重新计算）
from users_dw.userinfo_dw t1
left join
(select * from users.userinfo where dt = ‘20200501’) t2
on t1.user_id = t2.user_id;

合并当天最新的数据和历史数据到dw

insert overwrite table users_dw.userinfo_dw
select t1.user_id, – 商品编号
t1.phone_number, – 商品状态
t1.health_code, – 商品创建时间
t1.reg_time,
t1.modify_time, – 商品修改时间
t1.dw_start_date, – 生效日期（生效日期无需重新计算）
case
when (t2.user_id is not null and t1.dw_end_date > ‘2020-05-01’)
then ‘2020-05-01’
else t1.dw_end_date
end as dw_end_date – 更新生效日期（需要重新计算）
from users_dw.userinfo_dw t1
left join
(select * from users.userinfo where dt = ‘20200501’) t2
on t1.user_id = t2.user_id
union all
select userinfo.user_id, – 商品编号
userinfo.phone_number, – 商品状态
userinfo.health_code, – 商品创建时间
userinfo.reg_time,
userinfo.modify_time, – 商品修改时间
userinfo.modify_time as dw_start_date, – 生效日期
‘9999-12-31’ as dw_end_date – 失效日期
from users.userinfo
where dt = ‘20200501’ – 只有新增和修改的数据
order by dw_start_date, user_id;

最后结果:

2020年05月02日的数据如下：

user_id phone_number health_code reg_time modify_time
001 15953100001 绿色 2020-03-14 2020-05-01
002 15953100002 绿色 2020-03-14 2020-05-01
003 15953100003 绿色 2020-03-14 2020-05-01
004 15953100004 绿色 2020-03-14 2020-05-02
005 15953100005 黄色 2020-05-01 2020-05-02
006 15953100006 红色 2020-05-02 2020-05-02

修改和添加语句：

UPDATE exam.userinfo SET health_code = ‘绿色’, modify_time = ‘2020-05-02’ WHERE user_id = ‘004’;
UPDATE exam.userinfo SET health_code = ‘黄色’, modify_time = ‘2020-05-02’ WHERE user_id = ‘005’;
INSERT INTO exam.userinfo(user_id, phone_number, health_code, reg_time,modify_time) VALUES
(‘006’, ‘15953100006’, ‘绿色’, ‘2020-05-02’, ‘2020-05-02’);

创建0502分区

alter table users.userinfo add if not exists partition (dt=‘20200502’)

修复分区

msck repair table users.userinfo;

导入0502的增量数据

更新数据覆盖dw层

insert overwrite table users_dw.userinfo_dw
select t1.user_id, – 商品编号
t1.phone_number, – 商品状态
t1.health_code, – 商品创建时间
t1.reg_time,
t1.modify_time, – 商品修改时间
t1.dw_start_date, – 生效日期（生效日期无需重新计算）
case
when (t2.user_id is not null and t1.dw_end_date > ‘2020-05-02’)
then ‘2020-05-02’
else t1.dw_end_date – 小的是以前修改的，不用修改，只修改9999-12-31的数据
end as dw_end_date – 更新生效日期（需要重新计算）
from users_dw.userinfo_dw t1
left join
(select * from users.userinfo where dt = ‘20200502’) t2
on t1.user_id = t2.user_id
union all
select userinfo.user_id, – 商品编号
userinfo.phone_number, – 商品状态
userinfo.health_code, – 商品创建时间
userinfo.reg_time,
userinfo.modify_time, – 商品修改时间
userinfo.modify_time as dw_start_date, – 生效日期
‘9999-12-31’ as dw_end_date – 失效日期
from users.userinfo
where dt = ‘20200502’ – 只有新增和修改的数据
order by dw_start_date, user_id;

最后结果: