数据仓库ETL加载策略

-- ETL的 四种形式：

1、全量加载C1：rdw_t01_administrator.sql

INSERT overwrite table rdw.t01_administrator
SELECT
id, --员工编号
id, --Mis用户编号
name, --员工姓名
password, --密码
mobile, --手机号
mail, --员工邮箱
register_time, --注册时间
login_time, --登录时间
gendan, --员工是否跟单标志
is_del, --员工是否删除标志
0 as del_time, --删除时间
to_date(from_unixtime(0))as del_dt, --删除日期
to_date(from_unixtime(unix_timestamp())), --更新日期
team_id, --归属团队编号
team_role, --团队角色
team_superior --归属团队直接上级
FROM rsc.r360_administrator
where etl_dt='${hivevar:etl_dt}';

2、增量加载-日期追加C2：rdw_t01_jsd_user_record.sql

INSERT overwrite table rdw.t01_jsd_user_record PARTITION (etl_dt=${hivevar:etl_dt})
SELECT
id, --极速贷用户信息编号
userid, --用户编号
knuid, --卡牛编号
platform, --来源平台站点
phone, --电话号码
name, --用户姓名
deviceNum, --设备号
email, --邮箱
idcard, --身份证号
scanIdCard, --扫描身份证号
quota, --授信额度
during, --期限
zhifuId, --支付编号
urgentPhone, --紧急联系电话
urgentName, --紧急联系人姓名
urgentRelation, --紧急联系人关系代码
idpic, --身份证图片地址
idpic2, --手持身份证图片地址位置
appLocation, --app地理位置
status, --状态
antiFraudScore, --反欺诈得分
antiFraudStr, --反欺诈串
time, --时间戳
finish, --完成标识
riskJson, --风控结成串
receivedJson, --回收结成串
to_date(time) as create_dt, --创建日期
get_json_object(appLocation,'$.address'), --地址
get_json_object(appLocation,'$.lon'), --经度
get_json_object(appLocation,'$.lat') --纬度
FROM rsc.icredit_user_final_record
where etl_dt='${hivevar:etl_dt}';

3、增量加载-主键更新C3：rdw_t01_licai_customer.sql

insert overwrite TABLE rdw.t01_licai_customer
select
*
-- 可以 用 exist 方式 替代 ，以提高效率
from rdw.t01_licai_customer t where t.licai_customer_id not in(
select customer_id
from rsc.pay_customers
where etl_dt='${hivevar:etl_dt}'
) ;
insert into TABLE rdw.t01_licai_customer
select
customer_id, --理财用户编号
passport_id, --注册编号
pay_pwd, --支付密码
pay_salt, --支付密码加盐值
real_name, --用户实名
cert_type, --证件类型
log_msg, --登录提示信息
pay_server, --支付服务提供方
pay_user_id, --第三方支付用户注册编号
tender_plan_type, --自动投标类型
tender_plan, --自动投标计划
balance, --账户总余额
balance_free, --账户可用余额
freeze, --账户冻结资金
note, --备注信息
first_recharge, --首次充值时间
first_invest, --首次投资时间
create_time, --理财账号创建时间标签
update_time, --更新时间标签
to_date(from_unixtime(create_time)) as create_dt, --理财账号创建日期
to_date(from_unixtime(update_time)) as update_dt --更新日期
from rsc.pay_customers
where etl_dt='${hivevar:etl_dt}';

其中2与3的区别为：3中的每日增量数据可能包含在之前的日期数据中（原因：数据进行了update操作，会再次取到）。

4、历史拉链表C4：参考脚本 rdw_ t02_banker_bidding_history .sql

-- 说明：拉链表有两种形式。一种是只存储变更的数据，一种是 以天为时间间隔，全量存储。

--开发时间:20150609
--开发人员:zhangpengfei
--描 述:信贷员竞价历史表
--修改人员 修改时间 修改原因
--
--
--

--C4-1 加载

--插入信贷员竞价历史数据，生成历史拉链表

--备份
-- insert overwrite table rdw_bak.t02_banker_bidding_history partition(etl_dt='${hivevar:etl_dt}')
-- select *
-- from rdw.t02_banker_bidding_history;

--获取真正增量数据
insert overwrite table rtp.rdw_t02_banker_bidding_history_tmp01
select
t.Bidding_Id,
t.City_Id,
t.Product_Id,
t.Banker_Id,
t.Bidding_Price,
t.Is_Valid,
t.Modi_Peo_Type,
t.Modi_Tm,
t.Modi_Dt,
t.Reason,
t.Bidding_Type,
t.charge_method,
t.charge_ratio,
t.charge_fixed,
t.charge_special,
t.Is_quit_Bidding,
t.quit_Bidding_Tm,
t.quit_Bidding_Dt
from rdw.t02_banker_bidding t
where not exists (
select tt.Bidding_Id
from rdw.t02_banker_bidding_history tt
where t.Bidding_Id =tt.Bidding_Id
and t.City_Id =tt.City_Id
and t.Product_Id =tt.Product_Id
and t.Banker_Id =tt.Banker_Id
and t.Bidding_Price =tt.Bidding_Price
and t.Is_Valid =tt.Is_Valid
and t.Modi_Peo_Type =tt.Modi_Peo_Type
and t.Modi_Tm =tt.Modi_Tm
and t.Modi_Dt =tt.Modi_Dt
and t.Reason =tt.Reason
and t.Bidding_Type =tt.Bidding_Type
and t.charge_method =tt.charge_method
and t.charge_ratio =tt.charge_ratio
and t.charge_fixed =tt.charge_fixed
and t.charge_special =tt.charge_special
and t.Is_quit_Bidding =tt.Is_quit_Bidding
and t.quit_Bidding_Tm =tt.quit_Bidding_Tm
and t.quit_Bidding_Dt =tt.quit_Bidding_Dt
and tt.end_dt='${hivevar:maxdate}'
);

--update增量数据
insert overwrite table rdw.t02_banker_bidding_history
select
t.Bidding_Id,
t.start_dt,
t.City_Id,
t.Product_Id,
t.Banker_Id,
t.Bidding_Price,
t.Is_Valid,
t.Modi_Peo_Type,
t.Modi_Tm,
t.Modi_Dt,
t.Reason,
t.Bidding_Type,
t.charge_method,
t.charge_ratio,
t.charge_fixed,
t.charge_special,
t.Is_quit_Bidding,
t.quit_Bidding_Tm,
t.quit_Bidding_Dt,
case when tt.Bidding_Id is not null then to_date('${hivevar:etl_dt}') else date_add(t.end_dt,0) end as end_dt
from rdw.t02_banker_bidding_history t
left join rtp.rdw_t02_banker_bidding_history_tmp01 tt
on t.Bidding_Id=tt.Bidding_Id
and t.end_dt='${hivevar:maxdate}'
and t.start_dt<'${hivevar:etl_dt}';

--insert增量数据
insert into table rdw.t02_banker_bidding_history
select
t.Bidding_Id,
'${hivevar:etl_dt}' as start_dt,
t.City_Id,
t.Product_Id,
t.Banker_Id,
t.Bidding_Price,
t.Is_Valid,
t.Modi_Peo_Type,
t.Modi_Tm,
t.Modi_Dt,
t.Reason,
t.Bidding_Type,
t.charge_method,
t.charge_ratio,
t.charge_fixed,
t.charge_special,
t.Is_quit_Bidding,
t.quit_Bidding_Tm,
t.quit_Bidding_Dt,
'${hivevar:maxdate}' as end_dt
from rtp.rdw_t02_banker_bidding_history_tmp01 t
;
--C4-2 加载
-- 另外一种拉链方式（日快照）： t01_credit_copartner_user_info_snapshot

--开发时间：20190306
--开发人员：zhangtao
--描 述：信用卡合伙人用户信息快照表
--修改人员 修改时间 修改原因
--

insert overwrite table rdw.t01_credit_copartner_user_info_snapshot partition(etl_dt='${hivevar:etl_dt}')
select
id as id -- 编号
,passport_user_id as passport_user_id --passport用户编号
,user_name as user_name --用户名称
,is_copartner as is_copartner --是否合伙人
,iden_num as iden_num --身份证号
,mbl_num as mbl_num --手机号
,wechat_platform_user_id as wechat_platform_user_id --微信平台用户编号
,public_platform_unique_id as public_platform_unique_id --公众平台唯一编号
,wechat_acct_num as wechat_acct_num --微信账号
,banker_id as banker_id --信贷员编号
,suppassport_user_id as suppassport_user_id --上级passport用户编号
,bind_sup_tm as bind_sup_tm --绑定上级时间
,bind_sup_dt as bind_sup_dt --绑定上级日期
,is_correct_wechat_platform_user_id as is_correct_wechat_platform_user_id --是否正确微信平台用户编号
,user_from as user_from --用户来源
,curt_lvl as curt_lvl --当前级别
,curt_lvl_tm as curt_lvl_tm --当前级别时间
,curt_lvl_dt as curt_lvl_dt --当前级别日期
,copartner_reg_source as copartner_reg_source --合伙人注册来源
,wechat_qr_picture_path as wechat_qr_picture_path --微信二维码图片路径
,extend_poster_url as extend_poster_url --推广海报链接
,copartner_recruit_qr_picture_path as copartner_recruit_qr_picture_path --合伙人招募二维码图片路径
,copartner_recruit_poster_url as copartner_recruit_poster_url --合伙人招募海报链接
,reg_copartner_tm as reg_copartner_tm --注册合伙人时间
,reg_copartner_dt as reg_copartner_dt --注册合伙人日期
,create_tm as create_tm --创建时间
,create_dt as create_dt --创建日期
,update_tm as update_tm --更新时间
,update_dt as update_dt --更新日期
from rdw.t01_credit_copartner_user_info;