ods_to_dwd_sql

主要是这两个sql

dwd_start_log(利用get_json_object函数):

insert overwrite table "$app".dwd_start_log 
PARTITION (dt='$do_date') 
select 
    get_json_object(line,'$.mid') mid_id, 
    get_json_object(line,'$.uid') user_id, 
    get_json_object(line,'$.vc') version_code, 
    get_json_object(line,'$.vn') version_name, 
    get_json_object(line,'$.l') lang, 
    get_json_object(line,'$.sr') source, 
    get_json_object(line,'$.os') os, 
    get_json_object(line,'$.ar') area, 
    get_json_object(line,'$.md') model, 
    get_json_object(line,'$.ba') brand, 
    get_json_object(line,'$.sv') sdk_version,
    get_json_object(line,'$.g') gmail, 
    get_json_object(line,'$.hw') height_width, 
    get_json_object(line,'$.t') app_time, 
    get_json_object(line,'$.nw') network, 
    get_json_object(line,'$.ln') lng, 
    get_json_object(line,'$.la') lat, 
    get_json_object(line,'$.entry') entry, 
    get_json_object(line,'$.open_ad_type') open_ad_type, 
    get_json_object(line,'$.action') action, 
    get_json_object(line,'$.loading_time') loading_time, 
    get_json_object(line,'$.detail') detail, 
    get_json_object(line,'$.extend1') extend1
from "$app".ods_start_log 
where dt='$do_date';
"

dwd_base_event(利用UDF,UDTF函数)：

use "$APP"; 
insert overwrite table "$APP".dwd_base_event_log partition(dt='$do_date') 
select base_analizer(line,'mid') as mid_id, 
　　base_analizer(line,'uid') as user_id, 
　　base_analizer(line,'vc') as version_code, 
　　base_analizer(line,'vn') as version_name, 
　　base_analizer(line,'l') as lang, 
　　base_analizer(line,'sr') as source, 
　　base_analizer(line,'os') as os, 
　　base_analizer(line,'ar') as area,
　　base_analizer(line,'md') as model, 
　　base_analizer(line,'ba') as brand, 
　　base_analizer(line,'sv') as sdk_version, 
　　base_analizer(line,'g') as gmail, 
　　base_analizer(line,'hw') as height_width, 
　　base_analizer(line,'t') as app_time, 
　　base_analizer(line,'nw') as network, 
　　base_analizer(line,'ln') as lng, 
　　base_analizer(line,'la') as lat, 
　　event_name, 
　　event_json, 
　　base_analizer(line,'st') as server_time 
from "$APP".ods_event_log 
lateral view flat_analizer(base_analizer(line,'et')) tem_flat as event_name,event_json 
where dt='$do_date' and base_analizer(line,'et')<>'';
"

dwd_event_log (公共字段加get_json_object提取对应数据):

insert overwrite table "$APP".dwd_display_log 
PARTITION (dt='$do_date') 
select 
    mid_id, 
    user_id, 
    version_code, 
    version_name, 
    lang, 
    source, 
    os, 
    area, 
    model, 
    brand, 
    sdk_version, 
    gmail, 
    height_width, 
    app_time, 
    network, 
    lng, 
    lat, 
    get_json_object(event_json,'$.kv.action') action,         
    get_json_object(event_json,'$.kv.goodsid') goodsid,     
    get_json_object(event_json,'$.kv.place') place, 
    get_json_object(event_json,'$.kv.extend1') extend1, 
    get_json_object(event_json,'$.kv.category') category, 
    server_time 
from "$APP".dwd_base_event_log 
where dt='$do_date' and event_name='display';