数据仓库DWD层-离线数仓-大数据

红太狼 2022-11-29 13:11 163阅读 0赞

### dwd层 ###

> 对ODS层数据进行清洗（去除空值，脏数据，超过极限范围的数据，行式存储改为列存储，改压缩格式）

--------------------

#### 启动日志表 ####

#step1. 建表语句
    CREATE EXTERNAL TABLE dwd_start_log(
    	`mid_id` string,
    	`user_id` string, 
    	`version_code` string, 
    	`version_name` string, 
    	`lang` string, 
    	`source` string, 
    	`os` string, 
    	`area` string, 
    	`model` string,
    	`brand` string, 
    	`sdk_version` string, 
    	`gmail` string, 
    	`height_width` string,  
    	`app_time` string,
    	`network` string, 
    	`lng` string, 
    	`lat` string, 
    	`entry` string, 
    	`open_ad_type` string, 
    	`action` string, 
    	`loading_time` string, 
    	`detail` string, 
    	`extend1` string
    )
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_start_log/';
    
    #step2. 从ods层展开数据(从json里读取
    insert overwrite table dwd_start_log
    PARTITION (dt='2020-08-19')
    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 ods_start_log 
    where dt='2020-08-19';
    
    #step3. 测试加载的数据
    SELECT * FROM dwd_start_log where dt='2020-08-19' LIMIT 2;
    
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model    |brand  |sdk_version|gmail             |height_width|app_time     |network|lng  |lat  |entry|open_ad_type|action|loading_time|detail|extend1|dt        |
    ------|-------|------------|------------|----|------|-----|----|---------|-------|-----------|------------------|------------|-------------|-------|-----|-----|-----|------------|------|------------|------|-------|----------|
    1     |1      |15          |1.3.0       |en  |A     |8.1.5|MX  |sumsung-0|Sumsung|V2.6.6     |QQ4V37RD@gmail.com|750*1134    |1597827058186|WIFI   |-41.7|-13.6|3    |1           |1     |15          |      |       |2020-08-19|
    4     |4      |1           |1.2.9       |es  |F     |8.1.3|MX  |HTC-18   |HTC    |V2.1.2     |O865BZ34@gmail.com|640*960     |1597770467249|3G     |-99.1|18.4 |5    |2           |1     |2           |433   |       |2020-08-19|

到此基础数据展开就已经做完了，但是这步仅仅是对json数据的展开，还没开始数据清洗，因为还需要编写展开的脚本

#!/bin/bash
    
    # 定义变量方便修改
    APP=gmall
    hive=#HIVE_HOME/bin/hive
    
    # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
    	do_date=$1
    else 
    	do_date=`date -d "-1 day" +%F`  
    fi 
    
    sql="
    set hive.exec.dynamic.partition.mode=nonstrict;
    
    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';
    "
    
    $hive -e "$sql"

--------------------

#### 事件日志表 ####

#step1. 建立日志的明细表
    CREATE EXTERNAL TABLE dwd_base_event_log(
    `mid_id` string,
    `user_id` string, 
    `version_code` string, 
    `version_name` string, 
    `lang` string, 
    `source` string, 
    `os` string, 
    `area` string, 
    `model` string,
    `brand` string, 
    `sdk_version` string, 
    `gmail` string, 
    `height_width` string, 
    `app_time` string, 
    `network` string, 
    `lng` string, 
    `lat` string, 
    `event_name` string, 
    `event_json` string, 
    `server_time` string)
    PARTITIONED BY (`dt` string)
    stored as parquet
    location '/warehouse/gmall/dwd/dwd_base_event_log/';

在插入事件日志表之前需要先编写两个hive数据处理UDF函数

#假设已经编写好了UDF函数的jar包
    #step1. 把jar包传到linux服务器
    ...
    linux> pwd
    /opt/software
    
    #step2. 打开hive，把jar包加到hive的class path
    linux> hive
    ---
    hive>  add jar /opt/software/udf.jar;
     ok!
     
    #step3.先退出hive，现在把jar包上传到hdfs,并且观察hdfs的web服务器，看看有没有相应的jar包
    hive> quit;
    linux> hdfs dfs -mkdir /lib/
    linux> hdfs dfs -put udf.jar /lib/udf.jar
    ####！非常重要的一步，jar包一定要拷贝到hive的lib目录下，因此被坑了几个小时
    linux> cp udf.jar $HIVE_HOME/lib
    
    #step4.创建永久函数
    #展开单个业务字段
    create function base_analizer as 'com.github.aly8246.hive.udf.BaseFieldUDF' using jar 'hdfs://hadoop100:9000/lib/udf.jar';
    
    #展开多个业务字段
    create function flat_analizer as 'com.github.aly8246.hive.udtf.EventJsonUDTF' using jar 'hdfs://hadoop100:9000/lib/udf.jar';

最后执行sql，把数据从ods层加载到dwd层

insert overwrite table dwd_base_event_log 
    PARTITION (dt='2020-08-19')
    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,
    event_name,
    event_json,
    server_time
    from
    (
    select
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[0]   as mid_id,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[1]   as user_id,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[2]   as version_code,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[3]   as version_name,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[4]   as lang,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[5]   as source,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[6]   as os,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[7]   as area,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[8]   as model,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[9]   as brand,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[10]   as sdk_version,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[11]  as gmail,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[12]  as height_width,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[13]  as app_time,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[14]  as network,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[15]  as lng,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[16]  as lat,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[17]  as ops,
    split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[18]  as server_time
    from ods_event_log where dt='2020-08-19'  and base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la')<>'' 
    ) sdk_log lateral view flat_analizer(ops) tmp_k as event_name, event_json;
    
    #最后查看数据是否加载到了dwd层的表里
    SELECT * FROM dwd_base_event_log limit 2;
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model |brand|sdk_version|gmail             |height_width|app_time     |network|lng  |lat |event_name|event_json                                                                                                                                     |server_time  |dt        |
    ------|-------|------------|------------|----|------|-----|----|------|-----|-----------|------------------|------------|-------------|-------|-----|----|----------|-----------------------------------------------------------------------------------------------------------------------------------------------|-------------|----------|
    0     |0      |19          |1.1.0       |en  |W     |8.1.1|MX  |HTC-13|HTC  |V2.4.6     |XX709418@gmail.com|640*960     |1597768603806|4G     |-37.2|10.6|display   |{
       "ett":"1597749982150","en":"display","kv":{
       "goodsid":"0","action":"1","extend1":"1","place":"0","category":"51"}}                             |1597844984120|2020-08-19|
    0     |0      |19          |1.1.0       |en  |W     |8.1.1|MX  |HTC-13|HTC  |V2.4.6     |XX709418@gmail.com|640*960     |1597768603806|4G     |-37.2|10.6|loading   |{
       "ett":"1597835017019","en":"loading","kv":{
       "extend2":"","loading_time":"0","action":"2","extend1":"","type":"1","type1":"","loading_way":"2"}}|1597844984120|2020-08-19|

测试成功最后编写shell脚本

#!/bin/bash
    
    # 定义变量方便修改
    APP=gmall
    hive=$HIVE_HOME/bin/hive
    
    # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
            do_date=$1
    else
            do_date=`date -d "-1 day" +%F`
    fi
    
    sql="
            add jar $HIVE_HOME/lib/udf.jar;
    
            create temporary function base_analizer as 'com.github.aly8246.hive.udf.BaseFieldUDF';
            create temporary function flat_analizer as 'com.github.aly8246.hive.udtf.EventJsonUDTF';
    
            set hive.exec.dynamic.partition.mode=nonstrict;
    
            insert overwrite table "$APP".dwd_base_event_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 ,
            network ,
            lng ,
            lat ,
            app_time ,
            event_name ,
            event_json ,
            server_time
            from
            (
            select
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[0]   as mid_id,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[1]   as user_id,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[2]   as version_code,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[3]   as version_name,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[4]   as lang,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[5]   as source,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[6]   as os,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[7]   as area,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[8]   as model,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[9]   as brand,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[10]   as sdk_version,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[11]  as gmail,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[12]  as height_width,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[13]  as app_time,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[14]  as network,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[15]  as lng,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[16]  as lat,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[17]  as ops,
            split(base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la'),'\t')[18]  as server_time
            from "$APP".ods_event_log where dt='$do_date'  and base_analizer(line,'mid,uid,vc,vn,l,sr,os,ar,md,ba,sv,g,hw,t,nw,ln,la')<>''
            ) sdk_log lateral view flat_analizer(ops) tmp_k as event_name, event_json;
    "
    
    $hive -e "$sql"

自此dwd层的基础搭建已经完毕

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70_pic_center]

--------------------

接下来是dwd层明细层

1.  商品点击表

#step1. 创建商品点击表
    CREATE EXTERNAL TABLE dwd_display_log(
    `mid_id` string,
    `user_id` string,
    `version_code` string,
    `version_name` string,
    `lang` string,
    `source` string,
    `os` string,
    `area` string,
    `model` string,
    `brand` string,
    `sdk_version` string,
    `gmail` string,
    `height_width` string,
    `app_time` string,
    `network` string,
    `lng` string,
    `lat` string,
    `action` string,
    `goodsid` string,
    `place` string,
    `extend1` string,
    `category` string,
    `server_time` string
    )
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_display_log/';
    
    #step2. 从base表中导入有关商品点击表的数据
    insert overwrite table dwd_display_log
    PARTITION (dt='2020-08-19')
    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 dwd_base_event_log 
    where dt='2020-08-19' and event_name='display';
    
    #step3. 接下来是验证导入数据是否成功
    SELECT * FROM dwd_display_log where dt='2020-08-19' limit 2;
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model    |brand |sdk_version|gmail             |height_width|app_time     |network|lng  |lat  |action|goodsid|place|extend1|category|server_time  |dt        |
    ------|-------|------------|------------|----|------|-----|----|---------|------|-----------|------------------|------------|-------------|-------|-----|-----|------|-------|-----|-------|--------|-------------|----------|
    0     |0      |19          |1.1.0       |en  |W     |8.1.1|MX  |HTC-13   |HTC   |V2.4.6     |XX709418@gmail.com|640*960     |1597768603806|4G     |-37.2|10.6 |1     |0      |0    |1      |51      |1597844984120|2020-08-19|
    7     |7      |12          |1.3.8       |pt  |R     |8.0.9|MX  |Huawei-19|Huawei|V2.7.6     |16340JJA@gmail.com|750*1134    |1597759212150|WIFI   |-87.2|-18.2|1     |2      |3    |2      |17      |1597844984151|2020-08-19|

1.  商品详情页表

#step1. 接下来是商品详情页表
    CREATE EXTERNAL TABLE dwd_newsdetail_log(
    `mid_id` string,
    `user_id` string, 
    `version_code` string, 
    `version_name` string, 
    `lang` string, 
    `source` string, 
    `os` string, 
    `area` string, 
    `model` string,
    `brand` string, 
    `sdk_version` string, 
    `gmail` string, 
    `height_width` string, 
    `app_time` string,  
    `network` string, 
    `lng` string, 
    `lat` string, 
    `entry` string,
    `action` string,
    `goodsid` string,
    `showtype` string,
    `news_staytime` string,
    `loading_time` string,
    `type1` string,
    `category` string,
    `server_time` string)
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_newsdetail_log/';
    
    #step2. 从base表导入详情页数据
    insert overwrite table dwd_newsdetail_log
    PARTITION (dt='2020-08-19')
    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.entry') entry,
    get_json_object(event_json,'$.kv.action') action,
    get_json_object(event_json,'$.kv.goodsid') goodsid,
    get_json_object(event_json,'$.kv.showtype') showtype,
    get_json_object(event_json,'$.kv.news_staytime') news_staytime,
    get_json_object(event_json,'$.kv.loading_time') loading_time,
    get_json_object(event_json,'$.kv.type1') type1,
    get_json_object(event_json,'$.kv.category') category,
    server_time
    from dwd_base_event_log
    where dt='2020-08-19' and event_name='newsdetail';
    
    #step3. 验证新增数据是否正确
    SELECT * FROM dwd_newsdetail_log where dt = '2020-08-19' limit 2;
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model    |brand  |sdk_version|gmail             |height_width|app_time     |network|lng  |lat  |entry|action|goodsid|showtype|news_staytime|loading_time|type1|category|server_time  |dt        |
    ------|-------|------------|------------|----|------|-----|----|---------|-------|-----------|------------------|------------|-------------|-------|-----|-----|-----|------|-------|--------|-------------|------------|-----|--------|-------------|----------|
    7     |7      |12          |1.3.8       |pt  |R     |8.0.9|MX  |Huawei-19|Huawei |V2.7.6     |16340JJA@gmail.com|750*1134    |1597759212150|WIFI   |-87.2|-18.2|2    |4     |3      |0       |16           |5           |433  |60      |1597844984151|2020-08-19|
    221   |221    |10          |1.1.5       |pt  |A     |8.1.4|MX  |sumsung-5|Sumsung|V2.6.7     |638096Z9@gmail.com|640*1136    |1597837006032|3G     |-65.7|-7.0 |2    |3     |54     |5       |0            |0           |     |77      |1597844984276|2020-08-19|

1.  商品列表页表

#step1. 商品列表页表
    CREATE EXTERNAL TABLE dwd_loading_log(
    `mid_id` string,
    `user_id` string, 
    `version_code` string, 
    `version_name` string, 
    `lang` string, 
    `source` string, 
    `os` string, 
    `area` string, 
    `model` string,
    `brand` string, 
    `sdk_version` string, 
    `gmail` string,
    `height_width` string,  
    `app_time` string,
    `network` string, 
    `lng` string, 
    `lat` string, 
    `action` string,
    `loading_time` string,
    `loading_way` string,
    `extend1` string,
    `extend2` string,
    `type` string,
    `type1` string,
    `server_time` string)
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_loading_log/';
    
    #step2. 从base表导入数据
    insert overwrite table dwd_loading_log
    PARTITION (dt='2020-08-19')
    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.loading_time') loading_time,
    get_json_object(event_json,'$.kv.loading_way') loading_way,
    get_json_object(event_json,'$.kv.extend1') extend1,
    get_json_object(event_json,'$.kv.extend2') extend2,
    get_json_object(event_json,'$.kv.type') type,
    get_json_object(event_json,'$.kv.type1') type1,
    server_time
    from dwd_base_event_log
    where dt='2020-08-19' and event_name='loading';
    
    #step3. 验证导入数据是否成功
    SELECT * FROM dwd_loading_log WHERE dt='2020-08-19' LIMIT 2;
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model    |brand |sdk_version|gmail             |height_width|app_time     |network|lng  |lat  |action|loading_time|loading_way|extend1|extend2|type|type1|server_time  |dt        |
    ------|-------|------------|------------|----|------|-----|----|---------|------|-----------|------------------|------------|-------------|-------|-----|-----|------|------------|-----------|-------|-------|----|-----|-------------|----------|
    0     |0      |19          |1.1.0       |en  |W     |8.1.1|MX  |HTC-13   |HTC   |V2.4.6     |XX709418@gmail.com|640*960     |1597768603806|4G     |-37.2|10.6 |2     |0           |2          |       |       |1   |     |1597844984120|2020-08-19|
    7     |7      |12          |1.3.8       |pt  |R     |8.0.9|MX  |Huawei-19|Huawei|V2.7.6     |16340JJA@gmail.com|750*1134    |1597759212150|WIFI   |-87.2|-18.2|3     |32          |1          |       |       |1   |     |1597844984151|2020-08-19|

4.广告表

#step1. 建表语句
    CREATE EXTERNAL TABLE dwd_ad_log(
    `mid_id` string,
    `user_id` string, 
    `version_code` string, 
    `version_name` string, 
    `lang` string, 
    `source` string, 
    `os` string, 
    `area` string, 
    `model` string,
    `brand` string, 
    `sdk_version` string, 
    `gmail` string, 
    `height_width` string,  
    `app_time` string,
    `network` string, 
    `lng` string, 
    `lat` string, 
    `entry` string,
    `action` string,
    `content` string,
    `detail` string,
    `ad_source` string,
    `behavior` string,
    `newstype` string,
    `show_style` string,
    `server_time` string)
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_ad_log/';
    
    #step2. 从base表导入数据
    insert overwrite table dwd_ad_log
    PARTITION (dt='2020-08-19')
    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.entry') entry,
    get_json_object(event_json,'$.kv.action') action,
    get_json_object(event_json,'$.kv.content') content,
    get_json_object(event_json,'$.kv.detail') detail,
    get_json_object(event_json,'$.kv.source') ad_source,
    get_json_object(event_json,'$.kv.behavior') behavior,
    get_json_object(event_json,'$.kv.newstype') newstype,
    get_json_object(event_json,'$.kv.show_style') show_style,
    server_time
    from dwd_base_event_log 
    where dt='2020-08-19' and event_name='ad';
    
    #step3. 验证导入数据是正确
    SELECT * FROM dwd_ad_log WHERE dt='2020-08-19' LIMIT 2;
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model    |brand |sdk_version|gmail             |height_width|app_time     |network|lng  |lat  |entry|action|content|detail|ad_source|behavior|newstype|show_style|server_time  |dt        |
    ------|-------|------------|------------|----|------|-----|----|---------|------|-----------|------------------|------------|-------------|-------|-----|-----|-----|------|-------|------|---------|--------|--------|----------|-------------|----------|
    7     |7      |12          |1.3.8       |pt  |R     |8.0.9|MX  |Huawei-19|Huawei|V2.7.6     |16340JJA@gmail.com|750*1134    |1597759212150|WIFI   |-87.2|-18.2|1    |1     |2      |201   |3        |2       |6       |5         |1597844984151|2020-08-19|
    190   |190    |17          |1.2.6       |es  |N     |8.1.1|MX  |HTC-6    |HTC   |V2.4.9     |21JCD5OZ@gmail.com|640*1136    |1597828409212|4G     |-45.5|-27.8|1    |5     |1      |      |3        |2       |1       |4         |1597844984268|2020-08-19|

1.  消息通知表

CREATE EXTERNAL TABLE dwd_notification_log(
    `mid_id` string,
    `user_id` string, 
    `version_code` string, 
    `version_name` string, 
    `lang` string,
    `source` string, 
    `os` string, 
    `area` string, 
    `model` string,
    `brand` string, 
    `sdk_version` string, 
    `gmail` string, 
    `height_width` string,  
    `app_time` string,
    `network` string, 
    `lng` string, 
    `lat` string, 
    `action` string,
    `noti_type` string,
    `ap_time` string,
    `content` string,
    `server_time` string
    )
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_notification_log/';
    
    #step2. 从base表导入数据
    insert overwrite table dwd_notification_log
    PARTITION (dt='2020-08-19')
    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.noti_type') noti_type,
    get_json_object(event_json,'$.kv.ap_time') ap_time,
    get_json_object(event_json,'$.kv.content') content,
    server_time
    from dwd_base_event_log
    where dt='2020-08-19' and event_name='notification';
    
    #step3. 验证导入的数据是否正确
    SELECT * FROM dwd_notification_log WHERE dt='2020-08-19' LIMIT 2;
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model    |brand |sdk_version|gmail             |height_width|app_time     |network|lng  |lat  |action|noti_type|ap_time      |content|server_time  |dt        |
    ------|-------|------------|------------|----|------|-----|----|---------|------|-----------|------------------|------------|-------------|-------|-----|-----|------|---------|-------------|-------|-------------|----------|
    0     |0      |19          |1.1.0       |en  |W     |8.1.1|MX  |HTC-13   |HTC   |V2.4.6     |XX709418@gmail.com|640*960     |1597768603806|4G     |-37.2|10.6 |4     |         |1597765495148|       |1597844984120|2020-08-19|
    7     |7      |12          |1.3.8       |pt  |R     |8.0.9|MX  |Huawei-19|Huawei|V2.7.6     |16340JJA@gmail.com|750*1134    |1597759212150|WIFI   |-87.2|-18.2|4     |         |1597762842827|       |1597844984151|2020-08-19|

1.  用户前台活跃表

CREATE EXTERNAL TABLE dwd_active_foreground_log(
    `mid_id` string,
    `user_id` string,
    `version_code` string,
    `version_name` string,
    `lang` string,
    `source` string,
    `os` string,
    `area` string,
    `model` string,
    `brand` string,
    `sdk_version` string,
    `gmail` string,
    `height_width` string,
    `app_time` string,
    `network` string,
    `lng` string,
    `lat` string,
    `push_id` string,
    `access` string,
    `server_time` string)
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_foreground_log/';
    
    #step2. 从base表导入数据
    insert overwrite table dwd_active_foreground_log
    PARTITION (dt='2020-08-19')
    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.push_id') push_id,
    get_json_object(event_json,'$.kv.access') access,
    server_time
    from dwd_base_event_log
    where dt='2020-08-19' and event_name='active_foreground';
    
    #step3. 验证导入数据是否正确
    SELECT * FROM dwd_active_foreground_log WHERE dt='2020-08-19' LIMIT 2; 
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model    |brand |sdk_version|gmail             |height_width|app_time     |network|lng  |lat  |push_id|access|server_time  |dt        |
    ------|-------|------------|------------|----|------|-----|----|---------|------|-----------|------------------|------------|-------------|-------|-----|-----|-------|------|-------------|----------|
    7     |7      |12          |1.3.8       |pt  |R     |8.0.9|MX  |Huawei-19|Huawei|V2.7.6     |16340JJA@gmail.com|750*1134    |1597759212150|WIFI   |-87.2|-18.2|1      |1     |1597844984151|2020-08-19|
    190   |190    |17          |1.2.6       |es  |N     |8.1.1|MX  |HTC-6    |HTC   |V2.4.9     |21JCD5OZ@gmail.com|640*1136    |1597828409212|4G     |-45.5|-27.8|1      |1     |1597844984268|2020-08-19|

1.  用户后台活跃表

CREATE EXTERNAL TABLE dwd_active_background_log(
    `mid_id` string,
    `user_id` string,
    `version_code` string,
    `version_name` string,
    `lang` string,
    `source` string,
    `os` string,
    `area` string,
    `model` string,
    `brand` string,
    `sdk_version` string,
    `gmail` string,
     `height_width` string,
    `app_time` string,
    `network` string,
    `lng` string,
    `lat` string,
    `active_source` string,
    `server_time` string
    )
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_background_log/';
    
    #step2. 从base表导入数据
    
    insert overwrite table dwd_active_background_log
    PARTITION (dt='2020-08-19')
    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.active_source') active_source,
    server_time
    from dwd_base_event_log
    where dt='2020-08-19' and event_name='active_background';
    
    #step3. 验证导入数据是否正确
    SELECT * FROM dwd_active_background_log WHERE dt='2020-08-19' LIMIT 2;
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model    |brand  |sdk_version|gmail             |height_width|app_time     |network|lng  |lat  |active_source|server_time  |dt        |
    ------|-------|------------|------------|----|------|-----|----|---------|-------|-----------|------------------|------------|-------------|-------|-----|-----|-------------|-------------|----------|
    13    |13     |19          |1.3.0       |en  |B     |8.2.4|MX  |sumsung-5|Sumsung|V2.9.0     |0N557142@gmail.com|640*960     |1597809892131|4G     |-83.9|-31.6|1            |1597844984156|2020-08-19|
    190   |190    |17          |1.2.6       |es  |N     |8.1.1|MX  |HTC-6    |HTC    |V2.4.9     |21JCD5OZ@gmail.com|640*1136    |1597828409212|4G     |-45.5|-27.8|2            |1597844984268|2020-08-19|

1.  评论表

CREATE EXTERNAL TABLE dwd_comment_log(
    `mid_id` string,
    `user_id` string,
    `version_code` string,
    `version_name` string,
    `lang` string,
    `source` string,
    `os` string,
    `area` string,
    `model` string,
    `brand` string,
    `sdk_version` string,
    `gmail` string,
    `height_width` string,
    `app_time` string,
    `network` string,
    `lng` string,
    `lat` string,
    `comment_id` int,
    `userid` int,
    `p_comment_id` int, 
    `content` string,
    `addtime` string,
    `other_id` int,
    `praise_count` int,
    `reply_count` int,
    `server_time` string
    )
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_comment_log/';
    
    #step2. 从base表导入数据
    insert overwrite table dwd_comment_log
    PARTITION (dt='2020-08-19')
    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.comment_id') comment_id,
    get_json_object(event_json,'$.kv.userid') userid,
    get_json_object(event_json,'$.kv.p_comment_id') p_comment_id,
    get_json_object(event_json,'$.kv.content') content,
    get_json_object(event_json,'$.kv.addtime') addtime,
    get_json_object(event_json,'$.kv.other_id') other_id,
    get_json_object(event_json,'$.kv.praise_count') praise_count,
    get_json_object(event_json,'$.kv.reply_count') reply_count,
    server_time
    from dwd_base_event_log
    where dt='2020-08-19' and event_name='comment';
    
    #step3. 验证导入数据是否正确
    SELECT * FROM dwd_comment_log WHERE dt='2020-08-19' LIMIT 2; 
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model    |brand |sdk_version|gmail             |height_width|app_time     |network|lng  |lat  |comment_id|userid|p_comment_id|content      |addtime      |other_id|praise_count|reply_count|server_time  |dt        |
    ------|-------|------------|------------|----|------|-----|----|---------|------|-----------|------------------|------------|-------------|-------|-----|-----|----------|------|------------|-------------|-------------|--------|------------|-----------|-------------|----------|
    0     |0      |19          |1.1.0       |en  |W     |8.1.1|MX  |HTC-13   |HTC   |V2.4.6     |XX709418@gmail.com|640*960     |1597768603806|4G     |-37.2|10.6 |         1|     4|           4|灵躺扯仆浑估亩裸澜击佩  |1597777953520|       6|         452|         97|1597844984120|2020-08-19|
    7     |7      |12          |1.3.8       |pt  |R     |8.0.9|MX  |Huawei-19|Huawei|V2.7.6     |16340JJA@gmail.com|750*1134    |1597759212150|WIFI   |-87.2|-18.2|         0|     0|           2|烤旷充蝎炎轴胯袄嘛肌各椒实|1597833374040|       1|          12|        129|1597844984151|2020-08-19|

1.  收藏表

CREATE EXTERNAL TABLE dwd_favorites_log(
    `mid_id` string,
    `user_id` string, 
    `version_code` string, 
    `version_name` string, 
    `lang` string, 
    `source` string, 
    `os` string, 
    `area` string, 
    `model` string,
    `brand` string, 
    `sdk_version` string, 
    `gmail` string, 
    `height_width` string,  
    `app_time` string,
    `network` string, 
    `lng` string, 
    `lat` string, 
    `id` int, 
    `course_id` int, 
    `userid` int,
    `add_time` string,
    `server_time` string
    )
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_favorites_log/';
    
    #step2. 从base表导入数据
    insert overwrite table dwd_favorites_log
    PARTITION (dt='2020-08-19')
    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.id') id,
    get_json_object(event_json,'$.kv.course_id') course_id,
    get_json_object(event_json,'$.kv.userid') userid,
    get_json_object(event_json,'$.kv.add_time') add_time,
    server_time
    from dwd_base_event_log 
    where dt='2020-08-19' and event_name='favorites';
    
    #step3. 验证导入数据是否正确
    SELECT * FROM dwd_favorites_log WHERE dt='2020-08-19' LIMIT 2;
    
    mid_id|user_id|version_code|version_name|lang|source|os   |area|model    |brand  |sdk_version|gmail             |height_width|app_time     |network|lng  |lat  |id|course_id|userid|add_time     |server_time  |dt        |
    ------|-------|------------|------------|----|------|-----|----|---------|-------|-----------|------------------|------------|-------------|-------|-----|-----|--|---------|------|-------------|-------------|----------|
    7     |7      |12          |1.3.8       |pt  |R     |8.0.9|MX  |Huawei-19|Huawei |V2.7.6     |16340JJA@gmail.com|750*1134    |1597759212150|WIFI   |-87.2|-18.2| 0|        2|     5|1597836151486|1597844984151|2020-08-19|
    13    |13     |19          |1.3.0       |en  |B     |8.2.4|MX  |sumsung-5|Sumsung|V2.9.0     |0N557142@gmail.com|640*960     |1597809892131|4G     |-83.9|-31.6| 0|        1|     1|1597806487285|1597844984156|2020-08-19|

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NDI1MjQz_size_16_color_FFFFFF_t_70_pic_center]: /images/20221124/c6750ee1069f4a32a59475b6c4df80c2.png