ods_to_dwd_sql [数据库教程]

  • Z时代
  • 2024-01-10
  • 分类：综合

database

主要是这两个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‘) asuser_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‘)<>‘‘;
"

 

ods_to_dwd_sql

以上是 ods_to_dwd_sql [数据库教程] 的全部内容， 来源链接： utcz.com/z/535348.html

回到顶部