一个数仓拉链表的案例 - 《大数据》

#!/usr/bin/env bash
#--------------------#
# Author: 
# Date: 
# Desc: 添加数据到 会员信息拉链表fdl.fdl_center_meb_Main_chain
# Source_table: bdl.bdl_center_meb_Main
#--------------------#
export warehouse_base_dir=/data/warehouse
##装载config文件
. /opt/dw/yaduo-warehouse/etl/config/hive_config.conf
##表名
bdltb=bdl_center_meb_main
fdltb=fdl_center_meb_Main_chain
baktb=meb_Main_active_bak
##获取ETL运行日期
if  [ ! -n "$1" ] ;then
    run_date
else
    run_date $1
fi
#昨天
echo $etl_date
#前天
echo $yes_date
info "$fdltb"  "$etl_date" "etl数据加工开始"
impala-shell -i 192.168.2.52:21000 -q "
select 'meb_main拉链表';
select '-------------------------------------------------------------------';
INVALIDATE METADATA bdl.$bdltb;
select '插入过期数据';
INSERT OVERWRITE TABLE fdl.$fdltb PARTITION (dp='expired', dt='$etl_date', chain_end_date='$etl_date')
SELECT
        H.mebid,
        H.MebName,
        H.DocType,
        H.DocNo,
        H.Sex,
        H.Birthday,
        H.BirthPlaceID,
        H.StayPlaceID,
        H.Mobile,
        H.EMail,
        H.Address,
        H.CardNo,
        H.MebType,
        H.SellerID,
        H.SellerDepID,
        H.CreateUserID,
        H.CreateTime,
        H.Remark,
        H.State,
        H.IsOnCredit,
        H.Password,
        H.PayPassword,
        H.Nick,
        H.CustomImageID,
        H.ActiveCode,
        H.ModyfyUserID,
        H.ModifyTime,
        H.OnCreditTotalLimit,
        H.OnCreditEachLimit,
        H.IsExclusive,
        H.RegistSource,
        H.RegistType,
        H.HistoryMobile,
        H.IsNeverLost,
        H.Tag,
        H.TotalUgNight,
        H.ChannelID,
        H.DeviceID,
        H.ActivitySource,
        H.ActiveID,
        H.ActTotalUgNight,
        H.InactiveID,
        H.chain_start_date,
        H.chain_change_code
FROM
 (select * from bakactive.$baktb where dt ='$yes_date') H
FULL OUTER JOIN
 (select * from bdl.$bdltb where dt = '$etl_date') C
ON
  (H.mebid = C.mebid)
WHERE H.mebid IS NOT NULL
AND (
        COALESCE(H.mebid,'-979.090708') <> COALESCE(C.mebid,'-979.090708') OR
        COALESCE(H.MebName,'-979.090708') <> COALESCE(C.MebName,'-979.090708') OR
        COALESCE(H.DocType,'-979.090708') <> COALESCE(C.DocType,'-979.090708') OR
        COALESCE(H.DocNo,'-979.090708') <> COALESCE(C.DocNo,'-979.090708') OR
        COALESCE(H.Sex,'-979.090708') <> COALESCE(C.Sex,'-979.090708') OR
        COALESCE(H.Birthday,'-979.090708') <> COALESCE(C.Birthday,'-979.090708') OR
        COALESCE(H.BirthPlaceID,'-979.090708') <> COALESCE(C.BirthPlaceID,'-979.090708') OR
        COALESCE(H.StayPlaceID,'-979.090708') <> COALESCE(C.StayPlaceID,'-979.090708') OR
        COALESCE(H.Mobile,'-979.090708') <> COALESCE(C.Mobile,'-979.090708') OR
      COALESCE(H.EMail,'-979.090708') <> COALESCE(C.EMail,'-979.090708') OR 
        COALESCE(H.Address,'-979.090708') <> COALESCE(C.Address,'-979.090708') OR
      COALESCE(H.CardNo,'-979.090708') <> COALESCE(C.CardNo,'-979.090708') OR
        COALESCE(H.MebType,'-979.090708') <> COALESCE(C.MebType,'-979.090708') OR
        COALESCE(H.SellerID,'-979.090708') <> COALESCE(C.SellerID,'-979.090708') OR
        COALESCE(H.SellerDepID,'-979.090708') <> COALESCE(C.SellerDepID,'-979.090708') OR
        COALESCE(H.CreateUserID,'-979.090708') <> COALESCE(C.CreateUserID,'-979.090708') OR
        COALESCE(H.CreateTime,'-979.090708') <> COALESCE(C.CreateTime,'-979.090708') OR
        COALESCE(H.Remark,'-979.090708') <> COALESCE(C.Remark,'-979.090708') OR 
        COALESCE(H.State,'-979.090708') <> COALESCE(C.State,'-979.090708') OR 
        COALESCE(H.IsOnCredit,'-979.090708') <> COALESCE(C.IsOnCredit,'-979.090708') OR 
        COALESCE(H.Password,'-979.090708') <> COALESCE(C.Password,'-979.090708') OR 
        COALESCE(H.PayPassword,'-979.090708') <> COALESCE(C.PayPassword,'-979.090708') OR 
        COALESCE(H.Nick,'-979.090708') <> COALESCE(C.Nick,'-979.090708') OR 
        COALESCE(H.CustomImageID,'-979.090708') <> COALESCE(C.CustomImageID,'-979.090708') OR 
        COALESCE(H.ActiveCode,'-979.090708') <> COALESCE(C.ActiveCode,'-979.090708') OR 
        COALESCE(H.ModyfyUserID,'-979.090708') <> COALESCE(C.ModyfyUserID,'-979.090708') OR 
        COALESCE(H.ModifyTime,'-979.090708') <> COALESCE(C.ModifyTime,'-979.090708') OR 
        COALESCE(H.OnCreditTotalLimit,'-979.090708') <> COALESCE(C.OnCreditTotalLimit,'-979.090708') OR 
        COALESCE(H.OnCreditEachLimit,'-979.090708') <> COALESCE(C.OnCreditEachLimit,'-979.090708') OR 
        COALESCE(H.IsExclusive,'-979.090708') <> COALESCE(C.IsExclusive,'-979.090708') OR 
        COALESCE(H.RegistSource,'-979.090708') <> COALESCE(C.RegistSource,'-979.090708') OR 
        COALESCE(H.RegistType,'-979.090708') <> COALESCE(C.RegistType,'-979.090708') OR 
        COALESCE(H.HistoryMobile,'-979.090708') <> COALESCE(C.HistoryMobile,'-979.090708') OR 
        COALESCE(H.IsNeverLost,'-979.090708') <> COALESCE(C.IsNeverLost,'-979.090708') OR 
        COALESCE(H.Tag,'-979.090708') <> COALESCE(C.Tag,'-979.090708') OR 
        COALESCE(H.TotalUgNight,'-979.090708') <> COALESCE(C.TotalUgNight,'-979.090708') OR 
        COALESCE(H.ChannelID,'-979.090708') <> COALESCE(C.ChannelID,'-979.090708') OR 
        COALESCE(H.DeviceID,'-979.090708') <> COALESCE(C.DeviceID,'-979.090708') OR 
        COALESCE(H.ActivitySource,'-979.090708') <> COALESCE(C.ActivitySource,'-979.090708') OR 
        COALESCE(H.ActiveID,'-979.090708') <> COALESCE(C.ActiveID,'-979.090708') OR 
        COALESCE(H.ActTotalUgNight,'-979.090708') <> COALESCE(C.ActTotalUgNight,'-979.090708') OR 
        COALESCE(H.InactiveID,'-979.090708') <> COALESCE(C.InactiveID,'-979.090708') 
);
select '插入新数据';
INSERT OVERWRITE TABLE fdl.$fdltb PARTITION (dp='active',dt='9999-12-31', chain_end_date='9999-12-31')
SELECT
        C.mebid,
        C.MebName,
        C.DocType,
        C.DocNo,
        C.Sex,
        C.Birthday,
        C.BirthPlaceID,
        C.StayPlaceID,
        C.Mobile,
        C.EMail,
        C.Address,
        C.CardNo,
        C.MebType,
        C.SellerID,
        C.SellerDepID,
        C.CreateUserID,
        C.CreateTime,
        C.Remark,
        C.State,
        C.IsOnCredit,
        C.Password,
        C.PayPassword,
        C.Nick,
        C.CustomImageID,
        C.ActiveCode,
        C.ModyfyUserID,
        C.ModifyTime,
        C.OnCreditTotalLimit,
        C.OnCreditEachLimit,
        C.IsExclusive,
        C.RegistSource,
        C.RegistType,
        C.HistoryMobile,
        C.IsNeverLost,
        C.Tag,
        C.TotalUgNight,
        C.ChannelID,
        C.DeviceID,
        C.ActivitySource,
        C.ActiveID,
        C.ActTotalUgNight,
        C.InactiveID,
        IF(H.mebid IS NULL OR (
        COALESCE(H.mebid,'-979.090708') <> COALESCE(C.mebid,'-979.090708') OR
        COALESCE(H.MebName,'-979.090708') <> COALESCE(C.MebName,'-979.090708') OR
        COALESCE(H.DocType,'-979.090708') <> COALESCE(C.DocType,'-979.090708') OR
        COALESCE(H.DocNo,'-979.090708') <> COALESCE(C.DocNo,'-979.090708') OR
        COALESCE(H.Sex,'-979.090708') <> COALESCE(C.Sex,'-979.090708') OR
        COALESCE(H.Birthday,'-979.090708') <> COALESCE(C.Birthday,'-979.090708') OR
        COALESCE(H.BirthPlaceID,'-979.090708') <> COALESCE(C.BirthPlaceID,'-979.090708') OR
        COALESCE(H.StayPlaceID,'-979.090708') <> COALESCE(C.StayPlaceID,'-979.090708') OR
        COALESCE(H.Mobile,'-979.090708') <> COALESCE(C.Mobile,'-979.090708') OR
        COALESCE(H.EMail,'-979.090708') <> COALESCE(C.EMail,'-979.090708') OR
        COALESCE(H.Address,'-979.090708') <> COALESCE(C.Address,'-979.090708') OR
        COALESCE(H.CardNo,'-979.090708') <> COALESCE(C.CardNo,'-979.090708') OR
        COALESCE(H.MebType,'-979.090708') <> COALESCE(C.MebType,'-979.090708') OR
        COALESCE(H.SellerID,'-979.090708') <> COALESCE(C.SellerID,'-979.090708') OR
        COALESCE(H.SellerDepID,'-979.090708') <> COALESCE(C.SellerDepID,'-979.090708') OR
        COALESCE(H.CreateUserID,'-979.090708') <> COALESCE(C.CreateUserID,'-979.090708') OR
        COALESCE(H.CreateTime,'-979.090708') <> COALESCE(C.CreateTime,'-979.090708') OR
        COALESCE(H.Remark,'-979.090708') <> COALESCE(C.Remark,'-979.090708') OR 
        COALESCE(H.State,'-979.090708') <> COALESCE(C.State,'-979.090708') OR 
        COALESCE(H.IsOnCredit,'-979.090708') <> COALESCE(C.IsOnCredit,'-979.090708') OR 
        COALESCE(H.Password,'-979.090708') <> COALESCE(C.Password,'-979.090708') OR 
        COALESCE(H.PayPassword,'-979.090708') <> COALESCE(C.PayPassword,'-979.090708') OR 
        COALESCE(H.Nick,'-979.090708') <> COALESCE(C.Nick,'-979.090708') OR 
        COALESCE(H.CustomImageID,'-979.090708') <> COALESCE(C.CustomImageID,'-979.090708') OR 
        COALESCE(H.ActiveCode,'-979.090708') <> COALESCE(C.ActiveCode,'-979.090708') OR 
        COALESCE(H.ModyfyUserID,'-979.090708') <> COALESCE(C.ModyfyUserID,'-979.090708') OR 
        COALESCE(H.ModifyTime,'-979.090708') <> COALESCE(C.ModifyTime,'-979.090708') OR 
        COALESCE(H.OnCreditTotalLimit,'-979.090708') <> COALESCE(C.OnCreditTotalLimit,'-979.090708') OR 
        COALESCE(H.OnCreditEachLimit,'-979.090708') <> COALESCE(C.OnCreditEachLimit,'-979.090708') OR 
        COALESCE(H.IsExclusive,'-979.090708') <> COALESCE(C.IsExclusive,'-979.090708') OR 
        COALESCE(H.RegistSource,'-979.090708') <> COALESCE(C.RegistSource,'-979.090708') OR 
        COALESCE(H.RegistType,'-979.090708') <> COALESCE(C.RegistType,'-979.090708') OR 
        COALESCE(H.HistoryMobile,'-979.090708') <> COALESCE(C.HistoryMobile,'-979.090708') OR 
        COALESCE(H.IsNeverLost,'-979.090708') <> COALESCE(C.IsNeverLost,'-979.090708') OR 
        COALESCE(H.Tag,'-979.090708') <> COALESCE(C.Tag,'-979.090708') OR 
        COALESCE(H.TotalUgNight,'-979.090708') <> COALESCE(C.TotalUgNight,'-979.090708') OR 
        COALESCE(H.ChannelID,'-979.090708') <> COALESCE(C.ChannelID,'-979.090708') OR 
        COALESCE(H.DeviceID,'-979.090708') <> COALESCE(C.DeviceID,'-979.090708') OR 
        COALESCE(H.ActivitySource,'-979.090708') <> COALESCE(C.ActivitySource,'-979.090708') OR 
        COALESCE(H.ActiveID,'-979.090708') <> COALESCE(C.ActiveID,'-979.090708') OR 
        COALESCE(H.ActTotalUgNight,'-979.090708') <> COALESCE(C.ActTotalUgNight,'-979.090708') OR 
        COALESCE(H.InactiveID,'-979.090708') <> COALESCE(C.InactiveID,'-979.090708') 
        ), '$etl_date', H.chain_start_date) AS chain_start_date,
        CASE
            WHEN H.mebid IS NULL THEN
                 1
            WHEN
        COALESCE(H.mebid,'-979.090708') <> COALESCE(C.mebid,'-979.090708') OR
        COALESCE(H.MebName,'-979.090708') <> COALESCE(C.MebName,'-979.090708') OR
        COALESCE(H.DocType,'-979.090708') <> COALESCE(C.DocType,'-979.090708') OR
        COALESCE(H.DocNo,'-979.090708') <> COALESCE(C.DocNo,'-979.090708') OR
        COALESCE(H.Sex,'-979.090708') <> COALESCE(C.Sex,'-979.090708') OR
        COALESCE(H.Birthday,'-979.090708') <> COALESCE(C.Birthday,'-979.090708') OR
        COALESCE(H.BirthPlaceID,'-979.090708') <> COALESCE(C.BirthPlaceID,'-979.090708') OR
        COALESCE(H.StayPlaceID,'-979.090708') <> COALESCE(C.StayPlaceID,'-979.090708') OR
        COALESCE(H.Mobile,'-979.090708') <> COALESCE(C.Mobile,'-979.090708') OR
        COALESCE(H.EMail,'-979.090708') <> COALESCE(C.EMail,'-979.090708') OR
        COALESCE(H.Address,'-979.090708') <> COALESCE(C.Address,'-979.090708') OR
        COALESCE(H.CardNo,'-979.090708') <> COALESCE(C.CardNo,'-979.090708') OR
        COALESCE(H.MebType,'-979.090708') <> COALESCE(C.MebType,'-979.090708') OR
        COALESCE(H.SellerID,'-979.090708') <> COALESCE(C.SellerID,'-979.090708') OR
        COALESCE(H.SellerDepID,'-979.090708') <> COALESCE(C.SellerDepID,'-979.090708') OR
        COALESCE(H.CreateUserID,'-979.090708') <> COALESCE(C.CreateUserID,'-979.090708') OR
        COALESCE(H.CreateTime,'-979.090708') <> COALESCE(C.CreateTime,'-979.090708') OR
        COALESCE(H.Remark,'-979.090708') <> COALESCE(C.Remark,'-979.090708') OR 
        COALESCE(H.State,'-979.090708') <> COALESCE(C.State,'-979.090708') OR 
        COALESCE(H.IsOnCredit,'-979.090708') <> COALESCE(C.IsOnCredit,'-979.090708') OR 
        COALESCE(H.Password,'-979.090708') <> COALESCE(C.Password,'-979.090708') OR 
        COALESCE(H.PayPassword,'-979.090708') <> COALESCE(C.PayPassword,'-979.090708') OR 
        COALESCE(H.Nick,'-979.090708') <> COALESCE(C.Nick,'-979.090708') OR 
        COALESCE(H.CustomImageID,'-979.090708') <> COALESCE(C.CustomImageID,'-979.090708') OR 
        COALESCE(H.ActiveCode,'-979.090708') <> COALESCE(C.ActiveCode,'-979.090708') OR 
        COALESCE(H.ModyfyUserID,'-979.090708') <> COALESCE(C.ModyfyUserID,'-979.090708') OR 
        COALESCE(H.ModifyTime,'-979.090708') <> COALESCE(C.ModifyTime,'-979.090708') OR 
        COALESCE(H.OnCreditTotalLimit,'-979.090708') <> COALESCE(C.OnCreditTotalLimit,'-979.090708') OR 
        COALESCE(H.OnCreditEachLimit,'-979.090708') <> COALESCE(C.OnCreditEachLimit,'-979.090708') OR 
        COALESCE(H.IsExclusive,'-979.090708') <> COALESCE(C.IsExclusive,'-979.090708') OR 
        COALESCE(H.RegistSource,'-979.090708') <> COALESCE(C.RegistSource,'-979.090708') OR 
        COALESCE(H.RegistType,'-979.090708') <> COALESCE(C.RegistType,'-979.090708') OR 
        COALESCE(H.HistoryMobile,'-979.090708') <> COALESCE(C.HistoryMobile,'-979.090708') OR 
        COALESCE(H.IsNeverLost,'-979.090708') <> COALESCE(C.IsNeverLost,'-979.090708') OR 
        COALESCE(H.Tag,'-979.090708') <> COALESCE(C.Tag,'-979.090708') OR 
        COALESCE(H.TotalUgNight,'-979.090708') <> COALESCE(C.TotalUgNight,'-979.090708') OR 
        COALESCE(H.ChannelID,'-979.090708') <> COALESCE(C.ChannelID,'-979.090708') OR 
        COALESCE(H.DeviceID,'-979.090708') <> COALESCE(C.DeviceID,'-979.090708') OR 
        COALESCE(H.ActivitySource,'-979.090708') <> COALESCE(C.ActivitySource,'-979.090708') OR 
        COALESCE(H.ActiveID,'-979.090708') <> COALESCE(C.ActiveID,'-979.090708') OR 
        COALESCE(H.ActTotalUgNight,'-979.090708') <> COALESCE(C.ActTotalUgNight,'-979.090708') OR 
        COALESCE(H.InactiveID,'-979.090708') <> COALESCE(C.InactiveID,'-979.090708') 
        THEN
              2
           ELSE
              H.chain_change_code
        END AS chain_change_code
FROM
 (select * from bakactive.$baktb where dt ='$yes_date') H
FULL OUTER JOIN
 (select * from bdl.$bdltb where dt = '$etl_date') C
ON
  (H.mebid = C.mebid)
WHERE C.mebid IS NOT NULL;
INVALIDATE METADATA fdl.$fdltb;
select '备份今天数据';
INSERT OVERWRITE TABLE bakactive.$baktb PARTITION (dt='$etl_date')
select  mebid,
        MebName,
        DocType,
        DocNo,
        Sex,
        Birthday,
        BirthPlaceID,
        StayPlaceID,
        Mobile,
        EMail,
        Address,
        CardNo,
        MebType,
        SellerID,
        SellerDepID,
        CreateUserID,
        CreateTime,
        Remark,
        State,
        IsOnCredit,
        Password,
        PayPassword,
        Nick,
        CustomImageID,
        ActiveCode,
        ModyfyUserID,
        ModifyTime,
        OnCreditTotalLimit,
        OnCreditEachLimit,
        IsExclusive,
        RegistSource,
        RegistType,
        HistoryMobile,
        IsNeverLost,
        Tag,
        TotalUgNight,
        ChannelID,
        DeviceID,
        ActivitySource,
        ActiveID,
        ActTotalUgNight,
        InactiveID,
        chain_start_date,
        chain_change_code
   from fdl.$fdltb
  where dp='active';
INVALIDATE METADATA bakactive.$baktb;
exit;
" >> `get_log_path $fdltb` 2>&1
info "$fdltb" "delete_7day" "删除一周前的bakactive数据"
##delete data of a week ago
impala-shell -i 192.168.2.52:21000 -q "
ALTER TABLE bakactive.$baktb DROP IF EXISTS PARTITION (dt='$etl_date_w');
exit;
"
seven_day_ago_dataDir=$warehouse_base_dir/bakactive/$baktb/$etl_date_w
#hadoop fs -test -e "$seven_day_ago_dataDir"
#if [[ $? -eq 0 ]]; then
#    hadoop fs -rm -r $seven_day_ago_dataDir
#fi
hadoop fs -rm -r $seven_day_ago_dataDir
info "$fdltb" "etl_end" "$etl_date" "etl数据加工完成"