宽表加工
切换数据库 use ods_zhaozhihao;
查看表 show tables;
u_memcard_reg.json √
{
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "zhiyun",
"password": "zhiyun",
"column": ["*"],
"connection": [
{
"table": [
"u_memcard_reg"
],
"jdbcUrl": [
"jdbc:mysql://zhiyun.biz:23306/his"
]
}
]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS": "hdfs://cdh01:8020",
"fileType": "text",
"path": "/zhiyun/zhaozhihao/temp/u_memcard_reg",
"fileName": "u_memcard_reg",
"column": [
{"name":"id","type":"int"},
{"name":"memcardno","type":"string"},
{"name":"busno","type":"string"},
{"name":"introducer","type":"string"},
{"name":"cardtype","type":"int"},
{"name":"cardlevel","type":"int"},
{"name":"cardpass","type":"string"},
{"name":"cardstatus","type":"int"},
{"name":"saleamount","type":"string"},
{"name":"realamount","type":"string"},
{"name":"puramount","type":"string"},
{"name":"integral","type":"string"},
{"name":"integrala","type":"string"},
{"name":"integralflag","type":"int"},
{"name":"cardholder","type":"string"},
{"name":"cardaddress","type":"string"},
{"name":"sex","type":"string"},
{"name":"tel","type":"string"},
{"name":"handset","type":"string"},
{"name":"fax","type":"string"},
{"name":"createuser","type":"string"},
{"name":"createtime","type":"string"},
{"name":"tstatus","type":"int"},
{"name":"notes","type":"string"},
{"name":"stamp","type":"string"},
{"name":"idcard","type":"string"},
{"name":"birthday","type":"string"},
{"name":"allowintegral","type":"int"},
{"name":"apptype","type":"string"},
{"name":"applytime","type":"string"},
{"name":"invalidate","type":"string"},
{"name":"lastdate","type":"string"},
{"name":"bak1","type":"string"},
{"name":"scrm_userid","type":"string"}
],
"writeMode": "append",
"fieldDelimiter": "\t",
}
}
}
]
}
}
建文件夹
hadoop fs -mkdir -p /zhiyun/zhaozhihao/ods/u_memcard_reg
抽取
/opt/datax/bin/datax.py /zhiyun/zhaozhihao/jobs/u_memcard_reg.json
命令
beeline -u jdbc:hive2://localhost:10000 -n root -p 123
建临时表
create database temp_zhaozhihao;
建表
create external table temp_zhaozhihao.ods_u_memcard_reg(
id int,
memcardno string,
busno string,
introducer string,
cardtype int,
cardlevel int,
cardpass string,
cardstatus int,
saleamount string,
realamount string,
puramount string,
integral string,
integrala string,
integralflag int,
cardholder string,
cardaddress string,
sex string,
tel string,
handset string,
fax string,
createuser string,
createtime string,
tstatus int,
notes string,
stamp string,
idcard string,
birthday string,
allowintegral int,
apptype string,
applytime string,
invalidate string,
lastdate string,
bak1 string,
scrm_userid string
)row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/zhiyun/zhaozhihao/temp/u_memcard_reg';
开启动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=600000;
set hive.exec.max.dynamic.partitions=6000000;
set hive.exec.max.created.files=6000000;
插入数据
insert into table ods_zhaozhihao.ods_u_memcard_reg partition(dt)
select
id,
memcardno,
busno,
introducer,
cardtype,
cardlevel,
cardpass,
cardstatus,
saleamount,
realamount,
puramount,
integral,
integrala,
integralflag,
cardholder,
cardaddress,
sex,
tel,
handset,
fax,
createuser,
createtime,
tstatus,
notes,
stamp,
idcard,
birthday,
allowintegral,
apptype,
applytime,
invalidate,
lastdate,
bak1,
scrm_userid,
to_date(createtime)
from temp_zhaozhihao.ods_u_memcard_reg;
验证
show partitions ods_zhaozhihao.ods_u_memcard_reg;
crm_user_base_info_his.json √
配置
{
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "zhiyun",
"password": "zhiyun",
"column": ["*"],
"connection": [
{
"table": [
"crm_user_base_info_his"
],
"jdbcUrl": [
"jdbc:mysql://zhiyun.biz:23306/his"
]
}
]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS": "hdfs://cdh01:8020",
"fileType": "text",
"path": "/zhiyun/zhaozhihao/temp/crm_user_base_info_his",
"fileName": "crm_user_base_info_his",
"column": [
{"name":"id","type":"int"},
{"name":"user_id","type":"string"},
{"name":"user_type","type":"string"},
{"name":"source","type":"string"},
{"name":"erp_code","type":"string"},
{"name":"active_time","type":"string"},
{"name":"name","type":"string"},
{"name":"sex","type":"string"},
{"name":"education","type":"string"},
{"name":"job","type":"string"},
{"name":"email","type":"string"},
{"name":"wechat","type":"string"},
{"name":"webo","type":"string"},
{"name":"birthday","type":"string"},
{"name":"id_card_no","type":"string"},
{"name":"social_insurance_no","type":"string"},
{"name":"address","type":"string"},
{"name":"last_subscribe_time","type":"string"}
],
"writeMode": "append",
"fieldDelimiter": "\t",
}
}
}
]
}
}
建立文件夹
hadoop fs -mkdir -p /zhiyun/zhaozhihao/ods/crm_user_base_info_his
抽取
/opt/datax/bin/datax.py /zhiyun/zhaozhihao/jobs/crm_user_base_info_his.json
命令
beeline -u jdbc:hive2://localhost:10000 -n root -p 123
建表
create external table temp_zhaozhihao.ods_crm_user_base_info_his(
id int,
user_id string,
user_type string,
source string,
erp_code string,
active_time string,
name string,
sex string,
education string,
job string,
email string,
wechat string,
webo string,
birthday string,
id_card_no string,
social_insurance_no string,
address string,
last_subscribe_time string
)row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/zhiyun/zhaozhihao/temp/crm_user_base_info_his';
加载数据
insert into table ods_zhaozhihao.ods_crm_user_base_info_his partition(dt)
select
id,
user_id,
user_type,
source,
erp_code,
active_time,
name,
sex,
education,
job,
email,
wechat,
webo,
birthday,
id_card_no,
social_insurance_no,
address,
last_subscribe_time,
to_date(active_time)
from temp_zhaozhihao.ods_crm_user_base_info_his;
验证
show partitions ods_zhaozhihao.ods_crm_user_base_info_his;
u_sale_m √
配置
{
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "zhiyun",
"password": "zhiyun",
"column": ["*"],
"connection": [
{
"table": [
"u_sale_m"
],
"jdbcUrl": [
"jdbc:mysql://zhiyun.biz:23306/his"
]
}
]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS": "hdfs://cdh01:8020",
"fileType": "text",
"path": "/zhiyun/zhaozhihao/temp/u_sale_m",
"fileName": "u_sale_m",
"column": [
{"name":"id","type":"int"},
{"name":"saleno","type":"string"},
{"name":"busno","type":"string"},
{"name":"posno","type":"string"},
{"name":"extno","type":"string"},
{"name":"extsource","type":"string"},
{"name":"o2o_trade_from","type":"string"},
{"name":"channel","type":"int"},
{"name":"starttime","type":"string"},
{"name":"finaltime","type":"string"},
{"name":"payee","type":"string"},
{"name":"discounter","type":"string"},
{"name":"crediter","type":"string"},
{"name":"returner","type":"string"},
{"name":"warranter1","type":"string"},
{"name":"warranter2","type":"string"},
{"name":"stdsum","type":"string"},
{"name":"netsum","type":"string"},
{"name":"loss","type":"string"},
{"name":"member","type":"string"},
{"name":"precash","type":"string"},
{"name":"stamp","type":"string"},
{"name":"shiftid","type":"string"},
{"name":"shiftdate","type":"string"},
{"name":"yb_saleno","type":"string"}
],
"writeMode": "append",
"fieldDelimiter": "\t",
}
}
}
]
}
}
建文件夹
hadoop fs -mkdir -p /zhiyun/zhaozhihao/ods/u_sale_m
抽取
/opt/datax/bin/datax.py /zhiyun/zhaozhihao/jobs/u_sale_m.json
建表
create external table temp_zhaozhihao.ods_u_sale_m(
id int,
saleno string,
busno string,
posno string,
extno string,
extsource string,
o2o_trade_from string,
channel int,
starttime string,
finaltime string,
payee string,
discounter string,
crediter string,
returner string,
warranter1 string,
warranter2 string,
stdsum string,
netsum string,
loss string,
member string,
precash string,
stamp string,
shiftid string,
shiftdate string,
yb_saleno string
)row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/zhiyun/zhaozhihao/temp/u_sale_m';
加载数据
insert into table ods_zhaozhihao.ods_u_sale_m partition(dt)
select
id,
saleno,
busno,
posno,
extno,
extsource,
o2o_trade_from,
channel,
starttime,
finaltime,
payee,
discounter,
crediter,
returner,
warranter1,
warranter2,
stdsum,
netsum,
loss,
member,
precash,
stamp,
shiftid,
shiftdate,
yb_saleno,
to_date(starttime)
from temp_zhaozhihao.ods_u_sale_m;
验证
show partitions ods_zhaozhihao.ods_u_sale_m;
u_sale_pay √
配置
{
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "zhiyun",
"password": "zhiyun",
"column": ["*"],
"connection": [
{
"table": [
"u_sale_pay"
],
"jdbcUrl": [
"jdbc:mysql://zhiyun.biz:23306/his"
]
}
]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS": "hdfs://cdh01:8020",
"fileType": "text",
"path": "/zhiyun/zhaozhihao/temp/u_sale_pay",
"fileName": "u_sale_pay",
"column": [
{"name":"id","type":"int"},
{"name":"saleno","type":"string"},
{"name":"cardno","type":"string"},
{"name":"netsum","type":"string"},
{"name":"paytype","type":"string"},
{"name":"bak1","type":"string"}
],
"writeMode": "append",
"fieldDelimiter": "\t",
}
}
}
]
}
}
建文件夹
hadoop fs -mkdir -p /zhiyun/zhaozhihao/ods/u_sale_pay
抽取
/opt/datax/bin/datax.py /zhiyun/zhaozhihao/jobs/u_sale_pay.json
建表
create external table temp_zhaozhihao.ods_u_sale_pay(
id int,
saleno string,
cardno string,
netsum string,
paytype string,
bak1 string
)row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/zhiyun/zhaozhihao/temp/u_sale_pay';
加载数据
insert into table ods_zhaozhihao.ods_u_sale_pay partition(dt)
select
id,
saleno,
cardno,
netsum,
paytype,
bak1,
to_date(concat(20,substr(saleno,1,2),'-',substr(saleno,3,2),'-',substr(saleno,5,2)))
from temp_zhaozhihao.ods_u_sale_pay;
验证
show partitions ods_zhaozhihao.ods_u_sale_pay;
chronic_patient_info_new √
配置
{
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "zhiyun",
"password": "zhiyun",
"column": ["*"],
"connection": [
{
"table": [
"chronic_patient_info_new"
],
"jdbcUrl": [
"jdbc:mysql://zhiyun.biz:23306/his"
]
}
]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS": "hdfs://cdh01:8020",
"fileType": "text",
"path": "/zhiyun/zhaozhihao/temp/chronic_patient_info_new",
"fileName": "chronic_patient_info_new",
"column": [
{"name":"id","type":"int"},
{"name":"member_id","type":"string"},
{"name":"erp_code","type":"string"},
{"name":"extend","type":"string"},
{"name":"detect_time","type":"string"},
{"name":"bec_chr_mbr_date","type":"string"}
],
"writeMode": "append",
"fieldDelimiter": "\t",
}
}
}
]
}
}
建文件夹
hadoop fs -mkdir -p /zhiyun/zhaozhihao/ods/chronic_patient_info_new
抽取
/opt/datax/bin/datax.py /zhiyun/zhaozhihao/jobs/chronic_patient_info_new.json
建表
create external table temp_zhaozhihao.ods_chronic_patient_info_new(
id int,
member_id string,
erp_code string,
extend string,
detect_time string,
bec_chr_mbr_date string
)row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/zhiyun/zhaozhihao/temp/chronic_patient_info_new';
加载数据
insert into table ods_zhaozhihao.ods_chronic_patient_info_new partition(dt)
select
id,
member_id,
erp_code,
extend,
detect_time,
bec_chr_mbr_date,
to_date(detect_time)
from temp_zhaozhihao.ods_chronic_patient_info_new;
验证
show partitions ods_zhaozhihao.ods_chronic_patient_info_new;
c_org_busi
配置
{
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "zhiyun",
"password": "zhiyun",
"column": ["*"],
"connection": [
{
"table": [
"c_org_busi"
],
"jdbcUrl": [
"jdbc:mysql://zhiyun.biz:23306/his"
]
}
]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS": "hdfs://cdh01:8020",
"fileType": "text",
"path": "/zhiyun/zhaozhihao/temp/c_org_busi",
"fileName": "c_org_busi",
"column": [
{"name":"id","type":"int"},
{"name":"busno","type":"string"},
{"name":"orgname","type":"string"},
{"name":"orgsubno","type":"string"},
{"name":"orgtype","type":"string"},
{"name":"salegroup","type":"string"},
{"name":"org_tran_code","type":"string"},
{"name":"accno","type":"string"},
{"name":"sendtype","type":"string"},
{"name":"sendday","type":"string"},
{"name":"maxday","type":"string"},
{"name":"minday","type":"string"},
{"name":"notes","type":"string"},
{"name":"stamp","type":"string"},
{"name":"status","type":"string"},
{"name":"customid","type":"string"},
{"name":"whl_vendorno","type":"string"},
{"name":"whlgroup","type":"string"},
{"name":"rate","type":"string"},
{"name":"creditamt","type":"string"},
{"name":"creditday","type":"string"},
{"name":"peoples","type":"string"},
{"name":"area","type":"string"},
{"name":"abc","type":"string"},
{"name":"address","type":"string"},
{"name":"tel","type":"string"},
{"name":"principal","type":"string"},
{"name":"identity_card","type":"string"},
{"name":"mobil","type":"string"},
{"name":"corporation","type":"string"},
{"name":"saler","type":"string"},
{"name":"createtime","type":"string"},
{"name":"bank","type":"string"},
{"name":"bankno","type":"string"},
{"name":"bak1","type":"string"},
{"name":"bak2","type":"string"},
{"name":"a_bak1","type":"string"},
{"name":"aa_bak1","type":"string"},
{"name":"b_bak1","type":"string"},
{"name":"bb_bak1","type":"string"},
{"name":"y_bak1","type":"string"},
{"name":"t_bak1","type":"string"},
{"name":"ym_bak1","type":"string"},
{"name":"tm_bak1","type":"string"},
{"name":"supervise_code","type":"string"},
{"name":"monthrent","type":"string"},
{"name":"wms_warehid","type":"string"},
{"name":"settlement_cycle","type":"string"},
{"name":"apply_cycle","type":"string"},
{"name":"applydate","type":"string"},
{"name":"accounttype","type":"string"},
{"name":"applydate_last","type":"string"},
{"name":"paymode","type":"string"},
{"name":"yaolian_flag","type":"string"},
{"name":"org_longitude","type":"string"},
{"name":"org_latitude","type":"string"},
{"name":"org_province","type":"string"},
{"name":"org_city","type":"string"},
{"name":"org_area","type":"string"},
{"name":"business_time","type":"string"},
{"name":"yaolian_group","type":"string"},
{"name":"pacard_storeid","type":"string"},
{"name":"opening_time","type":"string"},
{"name":"ret_ent_id","type":"string"},
{"name":"ent_id","type":"string"}
],
"writeMode": "append",
"fieldDelimiter": "\t",
}
}
}
]
}
}
建文件夹
hadoop fs -mkdir -p /zhiyun/zhaozhihao/ods/c_org_busi
抽取
/opt/datax/bin/datax.py /zhiyun/zhaozhihao/jobs/c_org_busi.json
建表
create external table temp_zhaozhihao.ods_c_org_busi(
id int,
busno string,
orgname string,
orgsubno string,
orgtype string,
salegroup string,
org_tran_code string,
accno string,
sendtype string,
sendday string,
maxday string,
minday string,
notes string,
stamp string,
status string,
customid string,
whl_vendorno string,
whlgroup string,
rate string,
creditamt string,
creditday string,
peoples string,
area string,
abc string,
address string,
tel string,
principal string,
identity_card string,
mobil string,
corporation string,
saler string,
createtime string,
bank string,
bankno string,
bak1 string,
bak2 string,
a_bak1 string,
aa_bak1 string,
b_bak1 string,
bb_bak1 string,
y_bak1 string,
t_bak1 string,
ym_bak1 string,
tm_bak1 string,
supervise_code string,
monthrent string,
wms_warehid string,
settlement_cycle string,
apply_cycle string,
applydate string,
accounttype string,
applydate_last string,
paymode string,
yaolian_flag string,
org_longitude string,
org_latitude string,
org_province string,
org_city string,
org_area string,
business_time string,
yaolian_group string,
pacard_storeid string,
opening_time string,
ret_ent_id string,
ent_id string
)row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/zhiyun/zhaozhihao/temp/c_org_busi';
加载数据
insert into table ods_zhaozhihao.ods_c_org_busi partition(dt)
select
id,
busno,
orgname,
orgsubno,
orgtype,
salegroup,
org_tran_code,
accno,
sendtype,
sendday,
maxday,
minday,
notes,
stamp,
status,
customid,
whl_vendorno,
whlgroup,
rate,
creditamt,
creditday,
peoples,
area,
abc,
address,
tel,
principal,
identity_card,
mobil,
corporation,
saler,
createtime,
bank,
bankno,
bak1,
bak2,
a_bak1,
aa_bak1,
b_bak1,
bb_bak1,
y_bak1,
t_bak1,
ym_bak1,
tm_bak1,
supervise_code,
monthrent,
wms_warehid,
settlement_cycle,
apply_cycle,
applydate,
accounttype,
applydate_last,
paymode,
yaolian_flag,
org_longitude,
org_latitude,
org_province,
org_city,
org_area,
business_time,
yaolian_group,
pacard_storeid,
opening_time,
ret_ent_id,
ent_id,
to_date(applydate)
from temp_zhaozhihao.ods_c_org_busi;
验证
show partitions ods_zhaozhihao.ods_c_org_busi;
xbd_mxm_memberinfo_dim_t
建数据库
create database dw_zhaozhihao；
建表
create external table dw_zhaozhihao.dw_xbd_mxm_memberinfo_dim_t(
mbr_code string,
mbr_type string,
mbr_resource string,
mbr_cardno string,
store_code string,
sto_reg_date string,
reg_platform string,
platform_reg_date string,
name string,
gender string,
birthdate string,
mbr_id_card string,
social_security_no string,
edu_background string,
profession string,
is_marriage string,
have_children string,
address string,
region string,
province string,
city string,
last_order_date string,
first_order_date string,
order_total int,
order_amount decimal(10,2),
cancel_date string,
phone string,
cell_phone string,
email string,
wechat string,
weibo string,
alipay string,
cowell string,
rec_detect_store string,
rec_detect_date string,
check_count int,
filing_date string,
store_name string,
is_anamnesis string,
is_chr_mbr string,
etl_time string,
comments string
)partitioned by (dt string)
row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/zhiyun/zhaozhihao/dw/xbd_mxm_memberinfo_dim_t';
加载数据
insert into table dw_zhaozhihao.dw_xbd_mxm_memberinfo_dim_t partition(dt)
select
mbr_code,
mbr_type,
mbr_resource,
mbr_cardno,
store_code,
sto_reg_date,
reg_platform,
platform_reg_date,
name,
gender,
birthdate,
mbr_id_card,
social_security_no,
edu_background,
profession,
is_marriage,
have_children,
address,
region,
province,
city,
last_order_date,
first_order_date,
order_total,
order_amount,
cancel_date,
phone,
cell_phone,
email,
wechat,
weibo,
alipay,
cowell,
rec_detect_store,
rec_detect_date,
check_count,
filing_date,
store_name,
is_anamnesis,
is_chr_mbr,
etl_tine,
comment
to_date(sto_reg_date)
from dw_lijinquan.dw_xbd_mxm_memberinfo_dim_t;
部署
drop table dw_zhaozhihao.dw_xbd_mxm_memberinfo_dim_t;
建表
create external table if not exists dw_zhaozhihao.dw_xbd_mxm_memberinfo_dim_t(
mbr_code string,
mbr_type string,
mbr_resource string,
mbr_cardno string,
store_code string,
sto_reg_date string,
reg_platform string,
platform_reg_date string,
name string,
gender string,
birthdate string,
mbr_id_card string,
social_security_no string,
edu_background string,
profession string,
is_marriage string,
have_children string,
address string,
region string,
province string,
city string,
last_order_date string,
first_order_date string,
order_total string,
order_amount string,
cancel_date string,
phone string,
cell_phone string,
email string,
wechat string,
weibo string,
alipay string,
cowell string,
rec_detect_store string,
rec_detect_date string,
check_count string,
filing_date string,
store_name string,
is_anamnesis string,
is_chr_mbr string,
etl_tine string,
comment string
)row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/zhiyun/zhaozhihao//dw/xbd_mxm_memberinfo_dim_t';
#!/bin/bash
echo "正在建表"
beeline -u jdbc:hive2://localhost:10000 -n root -p 123 -e'
create external table if not exists dw_zhaozhihao.dw_xbd_mxm_memberinfo_dim_t(
mbr_code string,
mbr_type string,
mbr_resource string,
mbr_cardno string,
store_code string,
sto_reg_date string,
reg_platform string,
platform_reg_date string,
name string,
gender string,
birthdate string,
mbr_id_card string,
social_security_no string,
edu_background string,
profession string,
is_marriage string,
have_children string,
address string,
region string,
province string,
city string,
last_order_date string,
first_order_date string,
order_total string,
order_amount string,
cancel_date string,
phone string,
cell_phone string,
email string,
wechat string,
weibo string,
alipay string,
cowell string,
rec_detect_store string,
rec_detect_date string,
check_count string,
filing_date string,
store_name string,
is_anamnesis string,
is_chr_mbr string,
etl_tine string,
comment string
)row format delimited fields terminated by '\t'
lines terminated by '\n'
location '/zhiyun/zhaozhihao//dw/xbd_mxm_memberinfo_dim_t';
'
echo "开始执行宽表数据加载"
beeline -u jdbc:hive2://localhost:10000 -n root -p 123 -e'
with pay as(
select
cardno,
cnt,
total,
concat("20",substr(first_order_date,1,2),"-",substr(first_order_date,3,2),"- ",SUBSTR(first_order_date,5,2)) as first_order_date2, CONCAT ("20",SUBSTR(last_order_date,1,2),"-",SUBSTR(last_order_date,3,2),"- ",SUBSTR(last_order_date,5,2)) as last_order_date2
from(
select
cardno,
max(SUBSTR(Saleno,1,6)) as last_order_date,
min(SUBSTR(saleno,1,6)) as first_order_date,
count(1) as cnt,
sum(netsum) as total
from ods_lijinquan.ods_u_sale_pay
group by cardno ) t
),
patient as (
select
member_id,
orgname,
detect_time,
cnt
from (
select
cpi.member_id as member_id,
row_number()over(partition by cpi.member_id order by cpi.detect_time desc) r, cob.orgname as orgname,
cpi.detect_time as detect_time,
count(1)over(partition by cpi.member_id) as cnt
from ods_lijinquan.ods_chronic_patient_info_new cpi
left join ods_lijinquan.ods_c_org_busi cob
on cob.id = cpi.erp_code ) t
where r =1
),
patient2 as (
select member_id,
orgname,
detect_time,
extend,
chr_mbr
from (
select
cpi.member_id as member_id,
row_number()over(partition by cpi.member_id order by cpi.detect_time) r, cob.orgname as orgname,
cpi.detect_time as detect_time,
cpi.extend as extend,
if(cpi.bec_chr_mbr_date is not null,1,0) as chr_mbr
from ods_lijinquan.ods_chronic_patient_info_new cpi
left join ods_lijinquan.ods_c_org_busi cob
on cob.id=cpi.erp_code ) t
where r = 1
)
insert overwrite table dw_zhaozhihao.dw_xbd_mxm_memberinfo_dim_t
select
u.user_id as mbr_code,
u.user_type as mbr_type,
u.source as mbr_resource,
m.memcardno as mbr_cardno,
u.erp_code as store_code,
u.active_time as sto_reg_date,
"未知" as reg_platform,
"未知" as platform_reg_date,
u.name as name,
u.sex as gender,
u.birthday as birthdate,
u.id_card_no as mbr_id_card,
u.social_insurance_no as social_security_no,
u.education as edu_background,
u.job as profession,
"无" as is_marriage,
"无" as have_children,
u.address as address,
"未知" as region,
"未知" as province,
"未知" as city,
pay.last_order_date2 as last_order_date,
pay.first_order_date2 as first_order_date,
pay.cnt as order_total,
cast(pay.total as decimal) as order_amount,
u.last_subscribe_time as cancel_date,
"" as phone,
m.tel as cell_phone,
u.email as email,
u.wechat as wechat,
u.webo as weibo,
"未知" as alipay,
"未知" as cowell,
patient.orgname as rec_detect_store,
patient.detect_time as rec_detect_date,
patient.cnt as check_count,
patient2.detect_time as filing_date,
patient2.orgname as store_name,
patient2.extend as is_anamnesis,
patient2.chr_mbr as is_chr_mbr,
current_date() as etl_tine,
""as comment
from ods_lijinquan.ods_u_memcard_reg m
left join ods_lijinquan.ods_crm_user_base_info_his u
on u.user_id = m.scrm_userid
left join pay
on pay.cardno = m.memcardno
left join patient
on patient.member_id = m.memcardno
left join patient2
on patient2.member_id = m.memcardno;
select * from dw_zhaozhihao.dw_xbd_mxm_memberinfo_dim_t limit 5;
'
dd=$(date +%Y年%m月%d日)
ddd=$(date +%H:%M:%S)
echo "=========恭喜 *** 完成了 xbd_mxm_memberinfo_dim_t 的数据抽取 完成时间:$dd $ddd======"
浙公网安备 33010602011771号