数仓项目拉链表的小练习

拉链表试题
链接:https://pan.baidu.com/s/1MURoAfcqJQ7q86JoFTg8fQ
提取码:zvni

需求:

在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日的数据如下:

在这里插入图片描述
插入mysql语句:

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

使用kettle技术把4月30号的全量数据导入到hive的ods层的odsuserinfo表中

create table odsuserinfo(
user_id varchar(50),  
phone_number varchar(50), 
health_code varchar(50), 
reg_time varchar(50),  
modify_time varchar(50)  
)
partitioned by (dt string)
STORED AS PARQUET

kettle导入4月30号到分区为4月30分区中(ktr下载 需要的自己下载)
链接:https://pan.baidu.com/s/1sj2VGQWEfBpP2xsMIFGsMQ
提取码:zblt
复制这段内容后打开百度网盘手机App,操作更方便哦

进行修复表分区

 msck repair table odsuserinfo;

** 创建拉链表 ,把ods层的odsuserinfo表导入到拉链表dwuserinfo中并新增两个字段 dw_start_date string, – 生效日期
dw_end_date string – 失效日期**

create table dwuserinfo(
                           user_id varchar(50),
                           phone_number varchar(50),
                           health_code varchar(50),
                           reg_time varchar(50),
                           modify_time varchar(50),
                            dw_start_date string, -- 生效日期
                           dw_end_date string -- 失效日期
)
    STORED AS PARQUET;
insert overwrite table  exam.dwuserinfo
select
    user_id,
    phone_number,
    health_code,
    reg_time,
    modify_time,
    modify_time as dw_start_date,
    '9999-12-31' as dw_end_date
from
    exam.odsuserinfo
where
        dt='20200430';

模拟新增和修改数据

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

kettle导入5月1号到分区为5月1分区中(ktr下载 需要的自己下载)
链接:https://pan.baidu.com/s/1K0DA0ti6l6K6OxPC7BMwEw
提取码:5ggw
复制这段内容后打开百度网盘手机App,操作更方便哦

把数据导入到dw层的拉链表

insert overwrite table `exam`.`dwuserinfo`
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-04-30')
             then '20200501'
         else t1.dw_end_date -- 小的是以前修改的,不用修改,只修改9999-12-31的数据
        end as dw_end_date -- 更新生效日期(需要重新计算)
from
    `exam`.`dwuserinfo` t1
        left join
    (select * from `exam`.`odsuserinfo` where dt='20200501') t2
    on t1.user_id = t2.user_id  union all
select
    user_id,
    phone_number,
    health_code,
    reg_time,
    modify_time,
    modify_time as  dw_start_date,
    '9999-12-31' as dw_end_date
from
    `exam`.`odsuserinfo` where dt='20200501'
order by dw_start_date, user_id;

最终拉链表的数据
加粗样式

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 像素格子 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读