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

￡神魔★判官ぃ 2022-11-29 12:58 159阅读 0赞

### ods层 ###

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

字段line -json数据。根据时间分区

#step1. 创建启动日志表，启动line为json数据
    CREATE EXTERNAL TABLE ods_start_log (`line` string)
    PARTITIONED BY (`dt` string)
    LOCATION '/warehouse/gmall/ods/ods_start_log';
    
    #step2. 从hdfs测试加载数据
    load data inpath '/flume/topic_start/2020-08-19' 
    into table gmall.ods_start_log partition(dt='2020-08-19');
    
    #step3. 查看是否加载成功
    select * from ods_start_log limit 2;
    
    line                                                                                                                                                                                                                                                           |dt        |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------|
    {
       "action":"1","ar":"MX","ba":"Sumsung","detail":"","en":"start","entry":"3","extend1":"","g":"QQ4V37RD@gmail.com","hw":"750*1134","l":"en","la":"-13.6","ln":"-41.7","loading_time":"15","md":"sumsung-0","mid":"1","nw":"WIFI","open_ad_type":"1","os":"8.1.5"|2020-08-19|
    {
       "action":"1","ar":"MX","ba":"HTC","detail":"433","en":"start","entry":"5","extend1":"","g":"O865BZ34@gmail.com","hw":"640*960","l":"es","la":"18.4","ln":"-99.1","loading_time":"2","md":"HTC-18","mid":"4","nw":"3G","open_ad_type":"2","os":"8.1.3","sr":"F"|2020-08-19|

启动日志ods层加载完毕

–编写脚本完成加载

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

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

#step1. 创建事件日志表,启动line为全部json数据
    CREATE EXTERNAL TABLE ods_event_log(`line` string)
    PARTITIONED BY (`dt` string)
    LOCATION '/warehouse/gmall/ods/ods_event_log';
    
    #step2. 测试导入数据
    load data inpath '/flume/topic_event/2020-08-19' into table ods_event_log partition(dt='2020-08-19');
    
    #step3. 测试查询数据
     select * from ods_event_log limit 2;
    
    line                                                                                                                                                                                                                                                           |dt        |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------|
    1597844984120|{
       "cm":{
       "ln":"-37.2","sv":"V2.4.6","os":"8.1.1","g":"XX709418@gmail.com","mid":"0","nw":"4G","l":"en","vc":"19","hw":"640*960","ar":"MX","uid":"0","t":"1597768603806","la":"10.6","md":"HTC-13","vn":"1.1.0","ba":"HTC","sr":"W"},"ap":"app","et"|2020-08-19|
    1597844984151|{
       "cm":{
       "ln":"-87.2","sv":"V2.7.6","os":"8.0.9","g":"16340JJA@gmail.com","mid":"7","nw":"WIFI","l":"pt","vc":"12","hw":"750*1134","ar":"MX","uid":"7","t":"1597759212150","la":"-18.2","md":"Huawei-19","vn":"1.3.8","ba":"Huawei","sr":"R"},"ap":|2020-08-19|

ods层建立完毕，接下来是自动导入日志到ods层的脚本

#!/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
    
    echo "===日志日期为 $do_date==="
    sql="
    load data inpath '/flume/topic_start/$do_date' into table "$APP".ods_start_log partition(dt='$do_date');
    
    load data inpath '/flume/topic_event/$do_date' into table "$APP".ods_event_log partition(dt='$do_date');
    "
    
    $hive -e "$sql"