ods_to_dwd_sql[亲测有效]

小小码 • 2023-04-08 14:00 • 代码基础 • 阅读 156

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说ods_to_dwd_sql[亲测有效],希望您对编程的造诣更进一步.

ods_to_dwd_sql[数据库教程]

主要是这两个sql

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‘;
"

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‘)<>‘‘;
"