MYSQL模拟hive仓库拉链表实现

最新推荐文章于 2023-02-03 22:20:19 发布

人间清醒vv子

最新推荐文章于 2023-02-03 22:20:19 发布

阅读量532

点赞数 2

文章标签： hive mysql 链表

本文链接：https://blog.csdn.net/qq_45925467/article/details/119937520

版权

创建MySQL 数据库

create database  if not exists demo;

创建MySQL 数据库业务数据表

create table if not exists demo.t_product (
    goods_id varchar(255),      -- 商品编号
    goods_status varchar(255),  -- 商品状态
    createtime varchar(255),    -- 商品创建时间
    modifytime varchar(255)     -- 商品修改时间
);

录入2019-12-20日业务数据到业务型数据库中

insert into `demo`.`t_product`(goods_id, goods_status, createtime, modifytime) values
('001', '待审核', '2019-12-18', '2019-12-20'),
('002', '待售', '2019-12-19', '2019-12-20'),
('003', '在售', '2019-12-20', '2019-12-20'),
('004', '已删除', '2019-12-15', '2019-12-20');

select * from demo.t_product;
/*
    001	待审核	2019-12-18	2019-12-20
    002	待售	2019-12-19	2019-12-20
    003	在售	2019-12-20	2019-12-20
    004	已删除	2019-12-15	2019-12-20
 */

这里继续使用mysql ，模仿hive数据仓库过程

创建hive表首先创建ODS ，ODS 原始数据层和业务数据库数据保持一致=

ods_product(省略设置分区条件 partitioned by (dt string)

create table if not exists demo.ods_product(
    goods_id varchar(255),        -- 商品编号
    goods_status varchar(255),    -- 商品状态
    createtime varchar(255),      -- 商品创建时间
    modifytime varchar(255)       -- 商品修改时间
);

全量导入hive的 ods_product 表中

insert into demo.ods_product select * from demo.t_product;

select * from demo.ods_product;
/*
001	待审核	2019-12-18	2019-12-20
002	待售	2019-12-19	2019-12-20
003	在售	2019-12-20	2019-12-20
004	已删除	2019-12-15	2019-12-20
 */

hive仓库创建临时表 dw_product 不一定是DW ，ODS过滤无效字段或者将数据补全啊也都可以做的

这里字段都有用所以都要，多两个时间戳字段

create table if not exists demo.dw_product(
    goods_id varchar(255),        -- 商品编号
    goods_status varchar(255),    -- 商品状态
    createtime varchar(255),      -- 商品创建时间
    modifytime varchar(255),       -- 商品修改时间
    dw_start_date varchar(255),   -- 生效日期
    dw_end_date varchar(255)      -- 失效日期
);

将ods_product 数据导入拉链表中

insert into demo.dw_product
                select
                                goods_id,
                                goods_status,
                                createtime,
                                modifytime,
                                modifytime as 'dw_start_date',
                                '9999-12-31' as 'dw_end_date'
from demo.ods_product;

select * from demo.dw_product ;
/*
001	待审核	2019-12-18	2019-12-20	2019-12-20	9999-12-31
002	待售	2019-12-19	2019-12-20	2019-12-20	9999-12-31
003	在售	2019-12-20	2019-12-20	2019-12-20	9999-12-31
004	已删除	2019-12-15	2019-12-20	2019-12-20	9999-12-31
 */

现业务数据库t_product中数据发生更新和插入操作时间为2019-12-21

001 商品的Status状态由待审核变为待售

新增005 006商品

UPDATE demo.t_product SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001';
INSERT INTO demo.t_product(goods_id, goods_status, createtime, modifytime) VALUES
('005', '待审核', '2019-12-21', '2019-12-21'),
('006', '待审核', '2019-12-21', '2019-12-21');

select * from demo.t_product;
/*
001	待售	2019-12-18	2019-12-21
002	待售	2019-12-19	2019-12-20
003	在售	2019-12-20	2019-12-20
004	已删除	2019-12-15	2019-12-20
005	待审核	2019-12-21	2019-12-21
006	待审核	2019-12-21	2019-12-21
 */

我们知道 ods层要和业务数据库保持一致所以 hive中要做overwrite操作

我们模仿所以 truncate ，进行 insert into操作将业务数据导入ods层

truncate table demo.ods_product;

insert into demo.ods_product select * from demo.t_product;

接下来保存新增的数据到拉链表中，同时修改已经发生变化的数据并修改end时间戳

1. 先把之前的历史数据结束时间修改掉 left join

select
    dw.goods_id,
    dw.goods_status,
    dw.createtime,
    dw.modifytime,
    dw.dw_start_date,
-- 右表ods的ID 进行关联 不为null,并且dw的结束时间为9999-12-31
       # 不为null 表示 左边也有这条数据 ，结束时间为9999-12-31 说明是最新的/上一条 数据 我们进行判断修改
case when (ods.goods_id is not null and dw.dw_end_date = '9999-12-31')
    then ods.modifytime
    else dw.dw_end_date
    end as dw_end_date
from  demo.dw_product dw
left join
(select * from demo.ods_product where modifytime='2019-12-20') ods
on dw.goods_id = ods.goods_id;
/*
001	待审核	2019-12-18	2019-12-20	2019-12-20	2019-12-21   可以看出 001 发生数据被修改
002	待售	2019-12-19	2019-12-20	2019-12-20	2019-12-20
003	在售	2019-12-20	2019-12-20	2019-12-20	2019-12-20
004	已删除	2019-12-15	2019-12-20	2019-12-20	2019-12-20
 */

2. 关联新增的数据用union

还是之前创建的那块代码照搬

/*
select
ods.goods_id, -- 商品编号
ods.goods_status, -- 商品状态
ods.createtime, -- 商品创建时间
ods.modifytime, -- 商品的修改时间
ods.modifytime as dw_start_date,
'9999-12-31' as dw_end_date
from demo.ods_product ods where modifytime = '2019-12-21';
 */

实现

insert into  demo.dw_product
-- 1. 先把之前的历史数据结束时间修改掉
select
    dw.goods_id,
    dw.goods_status,
    dw.createtime,
    dw.modifytime,
    dw.dw_start_date,
-- 右表ods的ID 进行关联 不为null,并且dw的结束时间为9999-12-31
       # 不为null 表示 左边也有这条数据 ，结束时间为9999-12-31 说明是最新的/上一条 数据 我们进行判断修改
case when (ods.goods_id is not null and dw.dw_end_date = '9999-12-31')
    then ods.modifytime
    else dw.dw_end_date
    end as dw_end_date
from  demo.dw_product dw
left join
(select * from demo.ods_product where modifytime='2019-12-20') ods
on dw.goods_id = ods.goods_id
-- 2. 关联本次新增数据
union all
select
ods.goods_id,
ods.goods_status,
ods.createtime,
ods.modifytime,
ods.modifytime as dw_start_date,
'9999-12-31' as dw_end_date
from demo.ods_product ods where modifytime = '2019-12-21';

原dw数据

/*
001	待审核	2019-12-18	2019-12-20	2019-12-20	9999-12-31
002	待售	2019-12-19	2019-12-20	2019-12-20	9999-12-31
003	在售	2019-12-20	2019-12-20	2019-12-20	9999-12-31
004	已删除	2019-12-15	2019-12-20	2019-12-20	9999-12-31
*/

select * from demo.dw_product;

最终效果

/*
001	待审核	2019-12-18	2019-12-20	2019-12-20	9999-12-31
002	待售	2019-12-19	2019-12-20	2019-12-20	9999-12-31
003	在售	2019-12-20	2019-12-20	2019-12-20	9999-12-31
004	已删除	2019-12-15	2019-12-20	2019-12-20	9999-12-31
这个 以下为 我们想要的数据 因为上面我们使用的MySQL模仿hive 使用的MySQL 的 insert ino 
不是hive的 insert overwrite table 
001	待审核	2019-12-18	2019-12-20	2019-12-20	9999-12-31
002	待售	2019-12-19	2019-12-20	2019-12-20	2019-12-20
003	在售	2019-12-20	2019-12-20	2019-12-20	2019-12-20
004	已删除	2019-12-15	2019-12-20	2019-12-20	2019-12-20
001	待售	2019-12-18	2019-12-21	2019-12-21	9999-12-31
005	待审核	2019-12-21	2019-12-21	2019-12-21	9999-12-31
006	待审核	2019-12-21	2019-12-21	2019-12-21	9999-12-31
 */