drop table if exists dwd_start_log;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)stored as parquetlocation '/warehouse/gmall/dwd/dwd_start_log/'TBLPROPERTIES('parquet.compression'='lzo');

Xjson=[{"name":" ��� ��� ","sex":" ��� ","age":"25"},{"name":" ��� ��� ��� ","sex":" ���","age":"47"}]

SELECT get_json_object(xjson,"$.[0]") FROM person;

SELECT get_json_object(xjson,"$.[0].age") FROM person;

insert overwrite table dwd_start_logPARTITION (dt='2020-03-10')selectget_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') extend1from ods_start_logwhere dt='2020-03-10';

select * from dwd_start_log where dt='2020-03-10' limit 2;

#!/bin/bash# ������������������������APP=gmallhive=/opt/modules/hive/bin/hive# ������������������������������������������������������������������������������������������������if [ -n "$1" ] ;thendo_date=$1elsedo_date=`date -d "-1 day" +%F`fisql="set hive.exec.dynamic.partition.mode=nonstrict;insert overwrite table "$APP".dwd_start_logPARTITION (dt='$do_date')selectget_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') extend1from "$APP".ods_start_logwhere dt='$do_date';"$hive -e "$sql"

chmod 770 ods_to_dwd_log.sh 3���������������ods_to_dwd_log.sh 2020-03-11 4���������������������select * from dwd_start_log where dt='2020-03-11' limit 2;

drop table if exists dwd_base_event_log;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 parquetlocation '/warehouse/gmall/dwd/dwd_base_event_log/'TBLPROPERTIES('parquet.compression'='lzo');

            
     
      2.3.0
     
    
            
                     
      
       spring-plugin
                      
      
       https://repo.spring.io/plugins-release/
              
     
    
            
             
                     
      
       org.apache.hive
                      
      
       hive-exec
                      
      
       ${hive.version}
              
     
    
            
                     
                              
       
        maven-compiler-plugin
                               
       
        2.3.2
                               
                                       
        1.8                                
        
         1.8
                                
                       
                      
                              
       
        maven-assembly-plugin
                               
                                       
                                                
         
          jar-with-dependencies
                                         
                                
                               
                                       
                                                
         
          make-assembly
                                                 
         
          package
                                                 
          
          
           single
           
                                         
                                
                       
              
     
    

-Dmaven.wagon.http.ssl.insecure=true -Dmaven.wagon.http.ssl.allowall=true-Dmaven.wagon.http.ssl.ignore.validity.dates=true

Exception in thread "main" java.lang.StackOverflowError

package com.zsy.udf;import org.apache.commons.lang.StringUtils;import org.apache.hadoop.hive.ql.exec.UDF;import org.json.JSONObject;public class BaseFieldUDF extends UDF {    public String evaluate(String line,String key){        // 1.������������        String[] log = line.split("\\|");        String result = "";        // 2.������        if(log.length != 2 || StringUtils.isBlank(log[1])){            return result;        }        // 3.������������������json������        JSONObject json = new JSONObject(log[1].trim());        // 4.���������������key������������������        if("st".equals(key)){            result = log[0].trim();        }else if("et".equals(key)){            if(json.has("et")){                result = json.getString("et");            }        }else{            JSONObject cm = json.getJSONObject("cm");            if(cm.has(key)){                result = cm.getString(key);            }        }        return result;    }    /**     * ������     *///    public static void main(String[] args) {//        String line = "1583776132686|{"cm":{"ln":"-42.8","sv":"V2.3.9","os":"8.1.7","g":"X470IP70@gmail.com","mid":"0","nw":"4G","l":"en","vc":"13","hw":"1080*1920","ar":"MX","uid":"0","t":"1583758268106","la":"-31.3","md":"sumsung-18","vn":"1.1.1","ba":"Sumsung","sr":"M"},"ap":"app","et":[{"ett":"1583685512624","en":"display","kv":{"goodsid":"0","action":"2","extend1":"2","place":"1","category":"17"}},{"ett":"1583769686402","en":"newsdetail","kv":{"entry":"3","goodsid":"1","news_staytime":"16","loading_time":"0","action":"4","showtype":"5","category":"97","type1":""}},{"ett":"1583709065211","en":"ad","kv":{"activityId":"1","displayMills":"58537","entry":"1","action":"3","contentType":"0"}},{"ett":"1583693966746","en":"active_background","kv":{"active_source":"3"}},{"ett":"1583734521683","en":"error","kv":{"errorDetail":"java.lang.NullPointerException\\\\n    at cn.lift.appIn.web.AbstractBaseController.validInbound(AbstractBaseController.java:72)\\\\n at cn.lift.dfdf.web.AbstractBaseController.validInbound","errorBrief":"at cn.lift.dfdf.web.AbstractBaseController.validInbound(AbstractBaseController.java:72)"}},{"ett":"1583755388633","en":"praise","kv":{"target_id":0,"id":1,"type":3,"add_time":"1583713812739","userid":4}}]}";//        String result = new BaseFieldUDF().evaluate(line, "st");//        System.out.println(result);//    }}

package com.zsy.udtf;import org.apache.commons.lang.StringUtils;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;import org.apache.hadoop.hive.ql.metadata.HiveException;import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;import org.json.JSONArray;import org.json.JSONException;import java.util.ArrayList;import java.util.List;public class EventJsonUDTF extends GenericUDTF {    @Override    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {        // ������UDTF������������������������        List
    
      fieldName = new ArrayList<>();        List
     
       fieldType = new ArrayList<>();        fieldName.add("event_name");        fieldName.add("event_json");        fieldType.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);        fieldType.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldName, fieldType);    }    @Override    public void process(Object[] objects) throws HiveException {        // 1.������������������������������������Json array =��� UDF������et        String input = objects[0].toString();        // 2.������        if (StringUtils.isBlank(input)) {            return;        } else {            JSONArray ja = new JSONArray(input);            if (ja == null) {                return;            }            // ������������array������������������������������������ ���������������������������            for (int i = 0; i < ja.length(); i++) {                String[] result = new String[2];                try {                    result[0] = ja.getJSONObject(i).getString("en");                    result[1] = ja.getString(i);                } catch (JSONException ex) {                    continue;                }                // ������������                forward(result);            }        }    }    @Override    public void close() throws HiveException {    }}
     
    

hdfs dfs -mkdir /user/hive/jars hdfs dfs -put ./hivefunction-1.0-SNAPSHOT.jar /user/hive/jars

insert overwrite table dwd_base_event_log partition(dt='2020-03-10')selectbase_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_timefrom ods_event_log lateral view flat_analizer(base_analizer(line,'et')) tmp_flat asevent_name,event_jsonwhere dt='2020-03-10' and base_analizer(line,'et')<>'';

#!/bin/bash# ������������������������APP=gmallhive=/opt/modules/hive/bin/hive# ������������������������������������������������������������������������������������������������if [ -n "$1" ] ;thendo_date=$1elsedo_date=`date -d "-1 day" +%F`fisql="use gmall;insert overwrite table "$APP".dwd_base_event_log partition(dt='$do_date')selectbase_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_timefrom "$APP".ods_event_log lateral view flat_analizer(base_analizer(line,'et')) tem_flat asevent_name,event_jsonwhere dt='$do_date' and base_analizer(line,'et')<>''; "$hive -e "$sql"

chmod 770 ods_to_dwd_base_log.sh

ods_to_dwd_base_log.sh 2020-03-11

select * from dwd_base_event_log where dt='2020-03-11' limit 2;

drop table if exists dwd_display_log;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)stored as parquetlocation '/warehouse/gmall/dwd/dwd_display_log/'TBLPROPERTIES('parquet.compression'='lzo');

insert overwrite table dwd_display_log PARTITION (dt='2020-03-10')selectmid_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_timefrom dwd_base_event_logwhere dt='2020-03-10' and event_name='display';

select * from dwd_display_log where dt='2020-03-10' limit 2;

drop table if exists dwd_newsdetail_log;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)stored as parquetlocation '/warehouse/gmall/dwd/dwd_newsdetail_log/'TBLPROPERTIES('parquet.compression'='lzo');

insert overwrite table dwd_newsdetail_log PARTITION (dt='2020-03-10')selectmid_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_timefrom dwd_base_event_logwhere dt='2020-03-10' and event_name='newsdetail';

select * from dwd_newsdetail_log where dt='2020-03-10' limit 2;

drop table if exists dwd_loading_log;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)stored as parquetlocation '/warehouse/gmall/dwd/dwd_loading_log/'TBLPROPERTIES('parquet.compression'='lzo');

insert overwrite table dwd_loading_log PARTITION (dt='2020-03-10')selectmid_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_timefrom dwd_base_event_logwhere dt='2020-03-10' and event_name='loading';

hive (gmall)> select * from dwd_loading_log where dt='2020-03-10' limit 2;

drop table if exists dwd_ad_log;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,`contentType` string,`displayMills` string,`itemId` string,`activityId` string,`server_time` string)PARTITIONED BY (dt string)stored as parquetlocation '/warehouse/gmall/dwd/dwd_ad_log/'TBLPROPERTIES('parquet.compression'='lzo');

insert overwrite table dwd_ad_log PARTITION (dt='2020-03-10')selectmid_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.contentType') contentType,get_json_object(event_json,'$.kv.displayMills') displayMills,get_json_object(event_json,'$.kv.itemId') itemId,get_json_object(event_json,'$.kv.activityId') activityId,server_timefrom dwd_base_event_logwhere dt='2020-03-10' and event_name='ad';

select * from dwd_ad_log where dt='2020-03-10' limit 2;

drop table if exists dwd_notification_log;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)stored as parquetlocation '/warehouse/gmall/dwd/dwd_notification_log/'TBLPROPERTIES('parquet.compression'='lzo');

insert overwrite table dwd_notification_log PARTITION (dt='2020-03-10')selectmid_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_timefrom dwd_base_event_logwhere dt='2020-03-10' and event_name='notification';

select * from dwd_notification_log where dt='2020-03-10' limit 2;

drop table if exists dwd_active_background_log;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)stored as parquetlocation '/warehouse/gmall/dwd/dwd_background_log/'TBLPROPERTIES('parquet.compression'='lzo');

insert overwrite table dwd_active_background_log PARTITION(dt='2020-03-10')selectmid_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_timefrom dwd_base_event_logwhere dt='2020-03-10' and event_name='active_background';

select * from dwd_active_background_log where dt='2020-03-10' limit 2;

drop table if exists dwd_comment_log;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)stored as parquetlocation '/warehouse/gmall/dwd/dwd_comment_log/'TBLPROPERTIES('parquet.compression'='lzo');

insert overwrite table dwd_comment_log PARTITION (dt='2020-03-10')selectmid_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_timefrom dwd_base_event_logwhere dt='2020-03-10' and event_name='comment';

select * from dwd_comment_log where dt='2020-03-10' limit 2;

drop table if exists dwd_favorites_log;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)stored as parquetlocation '/warehouse/gmall/dwd/dwd_favorites_log/'TBLPROPERTIES('parquet.compression'='lzo');

insert overwrite table dwd_favorites_log PARTITION (dt='2020-03-10')selectmid_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_timefrom dwd_base_event_logwhere dt='2020-03-10' and event_name='favorites';

select * from dwd_favorites_log where dt='2020-03-10' limit 2;

drop table if exists dwd_praise_log;CREATE EXTERNAL TABLE dwd_praise_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` string,`userid` string,`target_id` string,`type` string,`add_time` string,`server_time` string)PARTITIONED BY (dt string)stored as parquetlocation '/warehouse/gmall/dwd/dwd_praise_log/'TBLPROPERTIES('parquet.compression'='lzo');

insert overwrite table dwd_praise_log PARTITION (dt='2020-03-10')selectmid_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.userid') userid,get_json_object(event_json,'$.kv.target_id') target_id,get_json_object(event_json,'$.kv.type') type,get_json_object(event_json,'$.kv.add_time') add_time,server_timefrom dwd_base_event_logwhere dt='2020-03-10' and event_name='praise';

select * from dwd_praise_log where dt='2020-03-10' limit 2;

drop table if exists dwd_error_log;CREATE EXTERNAL TABLE dwd_error_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,`errorBrief` string,`errorDetail` string,`server_time` string)PARTITIONED BY (dt string)stored as parquetlocation '/warehouse/gmall/dwd/dwd_error_log/'TBLPROPERTIES('parquet.compression'='lzo');

insert overwrite table dwd_error_log PARTITION (dt='2020-03-10')selectmid_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.errorBrief') errorBrief,get_json_object(event_json,'$.kv.errorDetail') errorDetail,server_timefrom dwd_base_event_logwhere dt='2020-03-10' and event_name='error';

select * from dwd_error_log where dt='2020-03-10' limit 2;

#!/bin/bash# ������������������������APP=gmallhive=/opt/modules/hive/bin/hive# ������������������������������������������������������������������������������������������������if [ -n "$1" ] ;thendo_date=$1elsedo_date=`date -d "-1 day" +%F`fisql="insert overwrite table "$APP".dwd_display_logPARTITION (dt='$do_date')selectmid_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_timefrom "$APP".dwd_base_event_logwhere dt='$do_date' and event_name='display';insert overwrite table "$APP".dwd_newsdetail_logPARTITION (dt='$do_date')selectmid_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_timefrom "$APP".dwd_base_event_logwhere dt='$do_date' and event_name='newsdetail';insert overwrite table "$APP".dwd_loading_logPARTITION (dt='$do_date')selectmid_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_timefrom "$APP".dwd_base_event_logwhere dt='$do_date' and event_name='loading';insert overwrite table "$APP".dwd_ad_logPARTITION (dt='$do_date')selectmid_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.contentType') contentType,get_json_object(event_json,'$.kv.displayMills')displayMills,get_json_object(event_json,'$.kv.itemId') itemId,get_json_object(event_json,'$.kv.activityId') activityId,server_timefrom "$APP".dwd_base_event_logwhere dt='$do_date' and event_name='ad';insert overwrite table "$APP".dwd_notification_logPARTITION (dt='$do_date')selectmid_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_timefrom "$APP".dwd_base_event_logwhere dt='$do_date' and event_name='notification';insert overwrite table "$APP".dwd_active_background_logPARTITION (dt='$do_date')selectmid_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_timefrom "$APP".dwd_base_event_logwhere dt='$do_date' and event_name='active_background';insert overwrite table "$APP".dwd_comment_logPARTITION (dt='$do_date')selectmid_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_timefrom "$APP".dwd_base_event_logwhere dt='$do_date' and event_name='comment';insert overwrite table "$APP".dwd_favorites_logPARTITION (dt='$do_date')selectmid_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_timefrom "$APP".dwd_base_event_logwhere dt='$do_date' and event_name='favorites';insert overwrite table "$APP".dwd_praise_logPARTITION (dt='$do_date')selectmid_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.userid') userid,get_json_object(event_json,'$.kv.target_id') target_id,get_json_object(event_json,'$.kv.type') type,get_json_object(event_json,'$.kv.add_time') add_time,server_timefrom "$APP".dwd_base_event_logwhere dt='$do_date' and event_name='praise';insert overwrite table "$APP".dwd_error_logPARTITION (dt='$do_date')selectmid_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.errorBrief') errorBrief,get_json_object(event_json,'$.kv.errorDetail') errorDetail,server_timefrom "$APP".dwd_base_event_logwhere dt='$do_date' and event_name='error';"$hive -e "$sql"

chmod 770 ods_to_dwd_event_log.sh

ods_to_dwd_event_log.sh 2020-03-11

select * from dwd_comment_log where dt='2020-03-11' limit 2;