拉链表的hive实现、mysql+kettle实现

hive实现=================================================================

以下方法注意点：

1. 切片表每个字段都不可为空，空值必须使用其他字符串代替！！（因为在求更新表时使用了等值关联）
2. 以下方法只反映了更新和增加，不能反映删除（可以增加一列将记录的删除转化为这一列的更新）
3. 每次将失效记录的失效时间定为昨天，生效记录的生效时间定为今天（这样查询切片时条件就是start_date<='?' and end_date>='?'）

创建简表，作为原始切片表
CREATE TABLE `zxdm_dim.d_acct_info_backup_simple`(
`acct_id` string,
`acct_name` string,
`company_name` string,
`cust_id` string,
`op_unit_name2` string,
`op_unit_name1` string)
stored as orc;

创建拉链表（注意开头多了两行，生效日和失效日）
CREATE TABLE `zxdm_dim.d_acct_info_backup_simple_zipper`(
start_date string,
end_date string,
`acct_id` string,
`acct_name` string,
`company_name` string,
`cust_id` string,
`op_unit_name2` string,
`op_unit_name1` string)
stored as orc;

向切片表加入数据
insert into zxdm_dim.d_acct_info_backup_simple 
select acct_id,acct_name,company_name,cust_id,op_unit_name2,op_unit_name1
from zxdm_dim.d_acct_info_backup limit 10;

第一次使用时向拉链表表加入初始数据(此处假设原始表为2019-06-05的切片)
insert into zxdm_dim.d_acct_info_backup_simple_zipper
select 
'2019-06-05' as start_date,
'9999-12-31' as end_date,*
from zxdm_dim.d_acct_info_backup_simple;

创建新的切片表
create table zxdm_dim.d_acct_info_backup_simple_new as
select *
from zxdm_dim.d_acct_info_backup_simple;
手动修改部分值作为新表
hdfs dfs -get hdfs://ns1/user/hive/warehouse/zxdm_dim.db/d_acct_info_backup_simple_new /share/00
vim /share/00/000000_0
hdfs dfs -rm hdfs://ns1/user/hive/warehouse/zxdm_dim.db/d_acct_info_backup_simple_new/*  
        */
hdfs dfs -put /share/00/000000_0 hdfs://ns1/user/hive/warehouse/zxdm_dim.db/d_acct_info_backup_simple_new/

创建更新变化表（新切片表-原始切片表），包含更改和插入（注意删除记录无法体现）
注意切片表每个字段都不可为空，空值必须使用其他字符串代替！！否则关联键将永远不等导致错误结果
drop table if exists temp.zipper_update;
create table temp.zipper_update as
select s1.*
from zxdm_dim.d_acct_info_backup_simple_new s1
left join zxdm_dim.d_acct_info_backup_simple s2
on s1.acct_id=s2.acct_id 
and s1.acct_name=s2.acct_name
and s1.company_name=s2.company_name
and s1.cust_id=s2.cust_id
and s1.op_unit_name2=s2.op_unit_name2
and s1.op_unit_name1=s2.op_unit_name1
where s2.acct_id is null;


更新拉链表(以下假设切片为今天生效，前半部分将失效记录的失效日设为昨天，后半部分新记录的生效日设为今天)，注意此处case中的s1.end_date='9999-12-31'就是保证只对每个账号的上一版本记录做失效处理，而忽略更早的已经失效的记录
insert overwrite table zxdm_dim.d_acct_info_backup_simple_zipper
select 
s1.start_date,
(case when s1.end_date='9999-12-31' and s2.acct_id is not null then date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1) else s1.end_date end)as end_date, 
s1.acct_id,s1.acct_name,s1.company_name,s1.cust_id,s1.op_unit_name2,s1.op_unit_name1
from zxdm_dim.d_acct_info_backup_simple_zipper s1
left join temp.zipper_update s2
on s1.acct_id=s2.acct_id
union all
select 
from_unixtime(unix_timestamp(),'yyyy-MM-dd') as start_date,
'9999-12-31' as end_date,
s1.acct_id,s1.acct_name,s1.company_name,s1.cust_id,s1.op_unit_name2,s1.op_unit_name1
from temp.zipper_update s1;


持续更新，区别在于更新表要用（最新版-上一版）
获取今日与昨日的变动(下面两句假设上一个版本是2019-06-07，新版本为2019-06-08)
drop table if exists temp.zipper_update;
create table temp.zipper_update as
select s1.*
from zxdm_dim.d_acct_info_backup_simple_new s1
left join
(select acct_id,acct_name,company_name,cust_id,op_unit_name2,op_unit_name1 
from zxdm_dim.d_acct_info_backup_simple_zipper
where start_date<='2019-06-07' and end_date>='2019-06-07'
) s2
on s1.acct_id=s2.acct_id 
and s1.acct_name=s2.acct_name
and s1.company_name=s2.company_name
and s1.cust_id=s2.cust_id
and s1.op_unit_name2=s2.op_unit_name2
and s1.op_unit_name1=s2.op_unit_name1
where s2.acct_id is null;

insert overwrite table zxdm_dim.d_acct_info_backup_simple_zipper
select 
s1.start_date,
(case when s1.end_date='9999-12-31' and s2.acct_id is not null then '2019-06-07' else s1.end_date end)as end_date, 
s1.acct_id,s1.acct_name,s1.company_name,s1.cust_id,s1.op_unit_name2,s1.op_unit_name1
from zxdm_dim.d_acct_info_backup_simple_zipper s1
left join temp.zipper_update s2
on s1.acct_id=s2.acct_id
union all
select 
'2019-06-08' as start_date,
'9999-12-31' as end_date,
s1.acct_id,s1.acct_name,s1.company_name,s1.cust_id,s1.op_unit_name2,s1.op_unit_name1
from temp.zipper_update s1;


查询昨日切片
select * from zxdm_dim.d_acct_info_backup_simple_zipper where start_date<='2019-06-05' and end_date>='2019-06-05';
select * from zxdm_dim.d_acct_info_backup_simple_zipper where start_date<='2019-06-07' and end_date>='2019-06-07';
select * from zxdm_dim.d_acct_info_backup_simple_zipper where start_date<='2019-06-08' and end_date>='2019-06-08';

mysql+kettle实现===========================================================

1、涉及时间字段时，数据表库连接配置必须有这一项，否则遇到0000-00-00的日期就会报错

2、表输入中应该通过查询获取切片表的主键、维度，另外还需要增加一个字段：记录日期record_date(date)，注意该字段的类型。

3、拉链表比切片表多4个字段， 代理主键agency_key(int)、版本version(int)、生效日期stdate_date(date)、失效日期end_date(date)，注意字段类型要求。拉链表的所有字段都必须可以为空。

4、维度查询/更新组件设置

5、由于kettle生成的拉链本次生效记录的start_date与上次失效记录的end_date相等，查询如下，注意前面是<=后面是>。

select * from zhixiao_acct.acct_info_zipper s1
where s1.start_date<='2019-04-03'
and s1.end_date>'2019-04-03';