数据仓库构建拉链表之 Hive 操作

1.  什么是拉链表

        拉链表，记录每条信息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期放入生效开始日期。

        如果当前信息至今有效，在生效结束日期中填入一个极大值（如9999-99-99 ）

           

2.  为什么要做拉链表

      拉链表适合于：数据会发生变化，但是大部分是不变的。

      比如：订单信息从未支付、已支付、未发货、已完成等状态经历了一周，大部分时间是不变化的。如果数据量有一定规模，无法按照每日全量的方式保存。 比如：1亿用户*365天，每天一份用户信息。(无法做每日全量)

                  

                  

3.如何使用拉链表

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

          1）拉链表数据

                    

     2）例如获取2019-01-01的历史切片：select * from order_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'

                   

 4. 拉链表形成过程

       

5. 拉链表制作过程 Hive 操作

       1）打开hadoop 集群 （hive依赖Hadoop集群运行）

             在hive路径下执行      bin/hive       进入hive命令窗口

             创建 test 数据库

               use test

      2)   创建订单表 和 并添加测试数据

             创建订单表 

hive (test)> drop table if exists dwd_order_info;

     create external table dwd_order_info (

    `id` string COMMENT '',

    `total_amount` decimal(10,2) COMMENT '',

    `order_status` string COMMENT ' 1 2 3 4 5',

    `user_id` string COMMENT 'id',

    `payment_way` string COMMENT '',

    `out_trade_no` string COMMENT '',

    `create_time` string COMMENT '',

    `operate_time` string COMMENT ''

)

PARTITIONED BY (`dt` string)
stored as parquet

location '/warehouse/gmall/dwd/dwd_order_info/'

tblproperties ("parquet.compression"="snappy");

添加 2 天的测试数据  

insert into table dwd_order_info partition (dt = "2019-05-01")
 values 
 ("1",2300,"待支付","001",null ,null ,"2019-05-01",null),
 ("2",300,"待支付","005",null ,null,"2019-05-01",null),
 ("3",500,"已支付","005","zhifubao","16552313","2019-05-01",null);
 
 
insert into table dwd_order_info partition (dt = "2019-05-02")
values 
("2",300,"已支付","005","weixin","56534","null","2019-05-02"),
("4",234,"待支付","005",null,null,"2019-05-02",null),
("5",234,"已支付","005","zhifu","12325346","2019-05-02",null);

 

 3) 初始化拉链表

      创建

hive (test)>
drop table if exists dwd_order_info_his;
create table dwd_order_info_his(
    `id` string COMMENT '订单编号',
    `total_amount` decimal(10,2) COMMENT '订单金额',
    `order_status` string COMMENT '订单状态',
    `user_id` string COMMENT '用户id' ,
    `payment_way` string COMMENT '支付方式',
    `out_trade_no` string COMMENT '支付流水号',
    `create_time` string COMMENT '创建时间',
    `operate_time` string COMMENT '操作时间',
    `start_date`  string COMMENT '有效开始日期',
    `end_date`  string COMMENT '有效结束日期'
) COMMENT '订单拉链表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_order_info_his/'
tblproperties ("parquet.compression"="snappy");

      初始化第一天的数据

hive (test)>
insert overwrite table dwd_order_info_his
select
    id,
    total_amount,
    order_status,
    user_id,
    payment_way,
    out_trade_no,
    create_time,
    operate_time,
    '2019-05-01',
    '9999-99-99'
from dwd_order_info oi
where oi.dt='2019-05-01';

         查询 

           ***** 尖叫提示 本文章为测试Demo   数据较少  查询未加 limit ， 实际业务操作 查询语句请加 limit

          hive (test)> select * from dwd_order_info_his

 

   4）追加第二天的订单数据到拉链表

           

// 过程中要用到 一个临时的拉链表   先创建

hive (test)>
drop table if exists dwd_order_info_his_tmp;
create external table dwd_order_info_his_tmp( 
    `id` string COMMENT '订单编号',
    `total_amount` decimal(10,2) COMMENT '订单金额', 
    `order_status` string COMMENT '订单状态', 
    `user_id` string COMMENT '用户id' ,
    `payment_way` string COMMENT '支付方式',  
    `out_trade_no` string COMMENT '支付流水号',  
    `create_time` string COMMENT '创建时间',  
    `operate_time` string COMMENT '操作时间',
    `start_date`  string COMMENT '有效开始日期',
    `end_date`  string COMMENT '有效结束日期'
) COMMENT '订单拉链临时表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_order_info_his_tmp/'
tblproperties ("parquet.compression"="snappy");

// 追加数据


hive (test)>
insert overwrite table dwd_order_info_his_tmp
select *
from 
   (select 
        id,
        total_amount,
        order_status,
        user_id,
        payment_way,
        out_trade_no,
        create_time,
        operate_time,
        '2019-05-02' start_date,
        '9999-99-99'  end_date
    from 
       dwd_order_info
    where 
       dt = '2019-05-02'
union all 
    select
        oh.id,  
        oh.total_amount,
        oh.order_status,
        oh.user_id,
        oh.payment_way,
        oh.out_trade_no,
        oh.create_time,
        oh.operate_time,
        oh.start_date,
     // 左连接时 原先拉链表中的数据都保留，第二天的订单id如果是null 即新订单 结束日期9999-99-99
       // 如果第二天的订单id如果不是null， 订单修改了状态 ，结束日期修改为前一天
        if (oi.id is null , oh.end_date, date_add(oi.dt,-1)) end_date
    from 
        dwd_order_info_his oh
    left join
        (select *
         from
           dwd_order_info
         where 
           dt = '2019-05-02') oi
    on oh.id = oi.id ) his 
    order by his.id, start_date;

 

// 用临时拉链表 覆盖之前的 拉链表
hive (test)> insert overwrite table dwd_order_info_his 
select * from dwd_order_info_his_tmp;

 

 

// 查询结果

hive (test)> select * from dwd_order_info_his ;