hive实现拉链表，并防止重跑历史数据

--数据清理(where条件是为了重跑该脚本时，清除当日 M_BSG_CIF_BASE_H 新增的数据)
--解释：1.采用CASE WHEN去判断 END_DT ，把关链数据重新开链
--      2.WHERE条件排除了 START_DT = ETLDATE 且 END_DT = '9999-12-31'的数据，即当日已经跑批后新增的数据
DROP TABLE IF EXISTS M_BSG_CIF_BASE_H_CLEAR;
CREATE TABLE M_BSG_CIF_BASE_H_CLEAR AS
SELECT CUST_NO                   --客户号
     , CUST_NAME                 --客户名称
     , ID_TYPE                   --证件类型
     , ID_NO                     --证件号码
     , TEL_NO                    --电话号码
     , OPEN_DATE                 --客户号开立日期
     , MODIFY_DATE               --修改日期
     , START_DT                  --开始日期
     , CASE WHEN END_DT = FROM_UNIXTIME(UNIX_TIMESTAMP('${ETLDATE}','yyyy-MM-dd'),'yyyy-MM-dd') 
            THEN FROM_UNIXTIME(UNIX_TIMESTAMP('9999-12-31','yyyy-MM-dd'),'yyyy-MM-dd')
            ELSE END_DT 
        END          AS END_DT   --结束日期
  FROM M_BSG_CIF_BASE_H
  WHERE START_DT <> FROM_UNIXTIME(UNIX_TIMESTAMP('${ETLDATE}','yyyy-MM-dd'),'yyyy-MM-dd')
;

--取当日数据（为了防止重跑历史数据， DT 不能小于拉链表 START_DT 最大值）
--解释： M_BSG_CIF_BASE_H_CLEAR 为拉链表，最新 START_DT 是昨天，如果传入的 DT 为更早时间，则此段sql取不到数据
DROP TABLE IF EXISTS M_BSG_CIF_BASE_H_I;
CREATE TABLE M_BSG_CIF_BASE_H_I AS
SELECT T.CUSTOMER_NO CUST_NO     --客户号
     , T.CUSTOMER_NAME CUST_NAME --客户名称
     , T.ID_TYPE                 --证件类型
     , T.ID_NO                   --证件号码
     , T.TEL_NO                  --电话号码
     , T.OPEN_DATE               --客户号开立日期
     , T.MODIFY_DATE             --修改日期
     , FROM_UNIXTIME(UNIX_TIMESTAMP('${ETLDATE}','yyyy-MM-dd'),'yyyy-MM-dd')       AS START_DT
     , FROM_UNIXTIME(UNIX_TIMESTAMP('9999-12-31','yyyy-MM-dd'),'yyyy-MM-dd')       AS END_DT
     , T.DT
  FROM ODS.BSG_CIF_BASE T
  JOIN (SELECT MAX(START_DT) MAX_DT 
          FROM M_BSG_CIF_BASE_H_CLEAR 
         WHERE END_DT = '9999-12-31'
        ) T1
 WHERE T.DT = '${ETLDATE}'
   AND T.DT > T1.MAX_DT
;

--建立临时表存储历史有效的数据
DROP TABLE IF EXISTS M_BSG_CIF_BASE_H_PRE;
CREATE TABLE M_BSG_CIF_BASE_H_PRE AS
SELECT CUST_NO                   --客户号
     , CUST_NAME                 --客户名称
     , ID_TYPE                   --证件类型
     , ID_NO                     --证件号码
     , TEL_NO                    --电话号码
     , OPEN_DATE                 --客户号开立日期
     , MODIFY_DATE               --修改日期
     , START_DT                  --开始日期
     , END_DT                    --结束日期
  FROM M_BSG_CIF_BASE_H_CLEAR
 WHERE END_DT = '9999-12-31'
;

--当日新增数据量（用于后续关链校验）
DROP TABLE IF EXISTS M_BSG_CIF_BASE_CNT;
CREATE TABLE M_BSG_CIF_BASE_CNT AS
SELECT COUNT(*) CUST_CNT
  FROM M_BSG_CIF_BASE_H_I
;

--创建临时表用于存放新增及更新数据
--这一步用主键和需要拉链的字段关联，是为了判断是否有新增或者修改的数据
DROP TABLE IF EXISTS M_BSG_CIF_BASE_H_INS;
CREATE TABLE M_BSG_CIF_BASE_H_INS AS
SELECT T1.CUST_NO                   --客户号
     , T1.CUST_NAME                 --客户名称
     , T1.ID_TYPE                   --证件类型
     , T1.ID_NO                     --证件号码
     , T1.TEL_NO                    --电话号码
     , T1.OPEN_DATE                 --客户号开立日期
     , T1.MODIFY_DATE               --修改日期
     , T1.START_DT                  --开始日期
     , T1.END_DT                    --结束日期
  FROM M_BSG_CIF_BASE_H_I T1
  LEFT JOIN M_BSG_CIF_BASE_H_PRE T2
    ON T1.CUST_NO = T2.CUST_NO
   AND COALESCE(T1.CUST_NAME,' ')   = COALESCE(T2.CUST_NAME,' ')
   AND COALESCE(T1.ID_TYPE,' ')     = COALESCE(T2.ID_TYPE,' ')
   AND COALESCE(T1.ID_NO,' ')       = COALESCE(T2.ID_NO,' ')
   AND COALESCE(T1.TEL_NO,' ')      = COALESCE(T2.TEL_NO,' ')
   AND COALESCE(T1.OPEN_DATE,' ')   = COALESCE(T2.OPEN_DATE,' ')
   AND COALESCE(T1.MODIFY_DATE,' ') = COALESCE(T2.MODIFY_DATE,' ')
 WHERE T2.CUST_NO IS NULL
;

--创建临时表存储删除的数据
--这一步只用主键关联，是为了判断该客户在新数据里是否存在，不存在就代表失效了要删除(即关链)
DROP TABLE IF EXISTS M_BSG_CIF_BASE_H_D;
CREATE TABLE M_BSG_CIF_BASE_H_D AS
SELECT T1.CUST_NO                   --客户号
     , T1.CUST_NAME                 --客户名称
     , T1.ID_TYPE                   --证件类型
     , T1.ID_NO                     --证件号码
     , T1.TEL_NO                    --电话号码
     , T1.OPEN_DATE                 --客户号开立日期
     , T1.MODIFY_DATE               --修改日期
     , T1.START_DT                  --开始日期
     , T1.END_DT                    --结束日期
     , T3.CUST_CNT                  --新增客户数量
  FROM M_BSG_CIF_BASE_H_PRE T1
  LEFT JOIN M_BSG_CIF_BASE_H_I T2
    ON T1.CUST_NO = T2.CUST_NO
  JOIN M_BSG_CIF_BASE_CNT T3
 WHERE T2.CUST_NO IS NULL
;

--对数据进行关链
DROP TABLE IF EXISTS M_BSG_CIF_BASE_H_CLOSE;
CREATE TABLE M_BSG_CIF_BASE_H_CLOSE AS
SELECT T1.CUST_NO                   --客户号
     , T1.CUST_NAME                 --客户名称
     , T1.ID_TYPE                   --证件类型
     , T1.ID_NO                     --证件号码
     , T1.TEL_NO                    --电话号码
     , T1.OPEN_DATE                 --客户号开立日期
     , T1.MODIFY_DATE               --修改日期
     , T1.START_DT                  --开始日期
     , CASE WHEN T2.CUST_NO IS NOT NULL AND T1.END_DT = '9999-12-31'
            THEN '${ETLDATE}'
       ELSE T1.END_DT      END AS  END_DT     --结束日期
  FROM M_BSG_CIF_BASE_H_CLEAR T1
  LEFT JOIN (SELECT CUST_NO
               FROM M_BSG_CIF_BASE_H_INS
              UNION 
             SELECT CUST_NO
               FROM M_BSG_CIF_BASE_H_D
              WHERE CUST_CNT > 0  
              --如果 M_BSG_CIF_BASE_H_I 传入了历史数据，此处 CUST_CNT = 0
              --这里取 CUST_CNT > 0 ，防止无效跑批导致有效数据被关链
            ) T2
    ON T1.CUST_NO = T2.CUST_NO
;

--插入新增的数据
INSERT OVERWRITE TABLE M_BSG_CIF_BASE_H
SELECT T1.CUST_NO                   --客户号
     , T1.CUST_NAME                 --客户名称
     , T1.ID_TYPE                   --证件类型
     , T1.ID_NO                     --证件号码
     , T1.TEL_NO                    --电话号码
     , T1.OPEN_DATE                 --客户号开立日期
     , T1.MODIFY_DATE               --修改日期
     , T1.START_DT                  --开始日期
     , T1.END_DT                    --结束日期
  FROM M_BSG_CIF_BASE_H_CLOSE T1
 UNION ALL
SELECT T2.CUST_NO                   --客户号
     , T2.CUST_NAME                 --客户名称
     , T2.ID_TYPE                   --证件类型
     , T2.ID_NO                     --证件号码
     , T2.TEL_NO                    --电话号码
     , T2.OPEN_DATE                 --客户号开立日期
     , T2.MODIFY_DATE               --修改日期
     , T2.START_DT                  --开始日期
     , T2.END_DT                    --结束日期
  FROM M_BSG_CIF_BASE_H_INS T2
;