【Hive】中拉链表使用场景

• 拉链表的基本操作步骤：

1. 初始化一次全量数据到历史拉链表中【只做一次操作就好】

2. 历史拉链表与每日的日增量数据做merge操作

3.关闭拉链的时间窗口

 

• 拉链表的常用使用场景

      缓慢变化维（Slowly Changing Dimensions）

       业务场景：

        公司内部，员工的职级会随着时间的变化发生缓慢的变化，例如： 升职、离职等；

       针对此情况，采用拉链表的方式既可保留历史，也不影响使用。

       准备材料：

       1. 员工表

CREATE TABLE biz_emp (
  emp_id string  COMMENT '员工id',
  emp_name string  COMMENT '员工名称',
  org_id string  COMMENT '组织id',
  emp_score double  COMMENT '员工得分',
  emp_posi string  COMMENT '员工岗位，此维度属性为会发生缓慢变化',
  createtime string ,
  modifytime string 
) COMMENT '员工表'
PARTITIONED BY (ds STRING comment 'yyyymmdd')
row format delimited
fields terminated by  '\t'
lines terminated by '\n' 
stored as TEXTFILE
;

     2. 关于员工信息的拉链表

CREATE TABLE zipper_biz_emp (
  emp_id string  COMMENT '员工id',
  emp_name string  COMMENT '员工名称',
  org_id string  COMMENT '组织id',
  emp_score double  COMMENT '员工得分',
  emp_posi string  COMMENT '员工岗位',
  createtime string ,
  modifytime string 
) COMMENT '员工表'
row format delimited
fields terminated by  '\t'
lines terminated by '\n' 
stored as TEXTFILE
;


-- 拉链表的临时表
create table if not exists tmp_zipper_biz_emp like zipper_biz_emp  ; 

   操作步骤

1.     利用datax工具全量抽取某一个时间节点之前的数据到员工表biz_emp 的指定分区中；此操作只做一次即可

         以时间点： 2019-07-25 为例

2.    将全量数据初始化到 历史拉链表中 zipper_biz_emp  ； 此操作也仅仅只需做一次即可

insert OVERWRITE table zipper_biz_emp 
SELECT 
	  emp_id
	, emp_name
	, org_id
	, emp_score
	, emp_posi
	, DATE_FORMAT(createtime,'yyyyMMdd') as start_date
	, 99991231  as end_date
FROM biz_emp
where ds = 20190725 
;

 

3.  按照时间字段抽取日增量的数据到biz_emp 的每一个增量分区中； 此操作需要打包成job，周期性的运行

  4.  将 拉链表 zipper_biz_emp  和 日增量表做merge操作，merge后的结果灌入临时表tmp_zipper_biz_emp中；

       *** 此过程中启动“开拉链” 操作，注意end_date 字段值的变化

       *** “开拉链”操作完成后，由于要保留变化的历史记录，需要利用日增量表biz_emp的增量数据，做“关拉链”操作，注意观察start_date 和 end_date 字段值的变化

   tips: 

      1. 如果某一名员工一天之内多次职位变更，则取这一天最后一次职位变更的记录。

      2. 我使用的hive 是1.1 还不支持 union 操作， 因此： 为防止job重复运行导致的数据重复，这里对最后的结果进行了去重。hive1.2 之后的版本可直接使用union 操作，代码会更加简化。

insert overwrite table tmp_zipper_biz_emp 
select
	  t1.emp_id
	, max(t1.emp_name)
	, max(t1.org_id)
	, max(t1.emp_score)
	, t1.emp_posi
	, t1.start_date	
	, t1.end_date
from(
	select 
		  t1.emp_id
		, t1.emp_name
		, t1.org_id
		, t1.emp_score
		, t1.emp_posi
		, t1.start_date
		, case when t2.emp_Id is not null and t1.end_date = '99991231' then ${bdp.system.bizdate}
			else t1.end_date
		 end as end_date
	from zipper_biz_emp t1 
	left outer join  (
		  -- 如果一个员工一天只能连升多级，则去最后一次“升值”记录
		  select
			  t1.* 
		  from(
			  select *,
			  ROW_NUMBER() OVER(partition by emp_id,emp_name order by modifytime desc ) as modifytime_desc_rank
			  from biz_emp t 
			  where ds = '${bdp.system.bizdate}' 
		  ) t1 
		  where t1.modifytime_desc_rank = 1 
	) t2 
	on t2.ds = '${bdp.system.bizdate}' 
	and t1.emp_id = t2.emp_id
	and t1.emp_name = t2.emp_name
	union all
	select 
		  t1.emp_id
		, t1.emp_name
		, t1.org_id
		, t1.emp_score
		, t1.emp_posi 
		, DATE_FORMAT(modifytime,'yyyyMMdd') as start_date 
		, 99991231  as end_date
	from biz_emp t1  
	where t1.ds = '${bdp.system.bizdate}' 
) t1 
group by 	  
	  t1.emp_id
	, t1.emp_posi
	, t1.start_date	
	, t1.end_date

;

     5. 临时表 tmp_zipper_biz_emp 数据灌入拉链表即可

insert overwrite table zipper_biz_emp
select * from tmp_zipper_biz_emp
;

-- 清空临时表
truncate table tmp_zipper_biz_emp ;