Hive建表中对JSON格式的日志文件处理

【背景说明】

我正在搭建离线数仓，目前已经hive建表阶段，业务数据和(用于测试的)用户历史数据已采集到hdfs上。因用户行为日志都已JSON格式存储，因此需要在hive建表时指定相应格式

页面日志格式：

{
	"common": {                       -- 环境信息
		"ar": "15",                   -- 省份ID
		"ba": "iPhone",              -- 手机品牌
		"ch": "Appstore",           -- 渠道
		"is_new": "1",              -- 是否首日使用，首次使用的当日，该字段值为1，过了24:00，该字段置为0。
		"md": "iPhone 8",            -- 手机型号
		"mid": "YXfhjAYH6As2z9Iq",  -- 设备id
		"os": "iOS 13.2.9",          -- 操作系统
		"sid": "3981c171-558a-437c-be10-da6d2553c517",     -- 会话id
		"uid": "485",                 -- 会员id
		"vc": "v2.1.134"             -- app版本号
	},
	"actions": [{                     -- 动作(事件)
		"action_id": "favor_add",   -- 动作id
		"item": "3",                  -- 目标id
		"item_type": "sku_id",      -- 目标类型
		"ts": 1585744376605          -- 动作时间戳
	    }
	],
	"displays": [{                    -- 曝光
			"displayType": "query", -- 曝光类型
			"item": "3",              -- 曝光对象id
			"item_type": "sku_id",  -- 曝光对象类型
			"order": 1,               -- 出现顺序
			"pos_id": 2,               -- 曝光位置
	        "pos_seq": 1              -- 曝光序列号（同一坑位多个对象的编号）
		},
		{
			"displayType": "promotion",
			"item": "6",
			"item_type": "sku_id",
			"order": 2,
			"pos_id": 1,
            "pos_seq": 1
		},
		{
			"displayType": "promotion",
			"item": "9",
			"item_type": "sku_id",
			"order": 3,
			"pos_id": 3,
            "pos_seq": 1
		},
		{
			"displayType": "recommend",
			"item": "6",
			"item_type": "sku_id",
			"order": 4,
			"pos_id": 2,
	        "pos_seq": 1
		},
		{
			"displayType": "query ",
			"item": "6",
			"item_type": "sku_id",
			"order": 5,
			"pos_id": 1,
	        "pos_seq": 1
		}
	],
	"page": {                          -- 页面信息
		"during_time": 7648,         -- 持续时间毫秒
		"item": "3", 	                 -- 目标id
		"item_type": "sku_id",       -- 目标类型
		"last_page_id": "login",       -- 上页ID
		"page_id": "good_detail",      -- 页面ID
		"from_pos_id":999,              -- 来源坑位ID
        "from_pos_seq":999,             -- 来源坑位序列号
        "refer_id":"2",			        -- 外部营销渠道ID
		"sourceType": "promotion"      -- 来源类型
	},                                 
	"err": {                              --错误
		"error_code": "1234",          --错误码
		"msg": "***********"           --错误信息
	},                                 
	"ts": 1585744374423                --跳入时间戳
}

启动日志格式：

{
  "common": {
    "ar": "370000",
    "ba": "Honor",
    "ch": "wandoujia",
    "is_new": "1",
    "md": "Honor 20s",
    "mid": "eQF5boERMJFOujcp",
    "os": "Android 11.0",
    "sid":"a1068e7a-e25b-45dc-9b9a-5a55ae83fc81",
    "uid": "76",
    "vc": "v2.1.134"
  },
  "start": {   
    "entry": "icon",         --icon手机图标  notice 通知   install 安装后启动
    "loading_time": 18803,    --启动加载时间
    "open_ad_id": 7,           --广告页ID
    "open_ad_ms": 3449,        -- 广告总共播放时间
    "open_ad_skip_ms": 1989   --  用户跳过广告时点
  },
   "err":{                         --错误
    "error_code": "1234",      --错误码
    "msg": "***********"       --错误信息
},
   "ts": 1585744304000
}

需要建立用一张表映射上述两种格式的行为日志：

Hive建表格式：

DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc -- 学习阶段用外部表，工作阶段因为没人用你的表，用内部表就行
(
    `common` STRUCT<ar :STRING,
        ba :STRING,
        ch :STRING,
        is_new :STRING,
        md :STRING,
        mid :STRING,
        os :STRING,
        sid :STRING,
        uid :STRING,
        vc :STRING> COMMENT '公共信息',
    `page` STRUCT<
        during_time :STRING,
        item :STRING,
        item_type :STRING,
        last_page_id :STRING,
        page_id :STRING,
        from_pos_id :STRING,
        from_pos_seq :STRING,
        refer_id :STRING
    > COMMENT '页面信息',
    `actions` ARRAY<STRUCT<action_id:STRING,
        item:STRING,
        item_type:STRING,
        ts:BIGINT>> COMMENT '动作信息',
    `displays` ARRAY<
        STRUCT<
            display_type :STRING,
            item :STRING,
            item_type :STRING,
            `pos_seq` :STRING,
            pos_id :STRING
        >
    > COMMENT '曝光信息',
    `start` STRUCT<entry :STRING,
        first_open :BIGINT,
        loading_time :BIGINT,
        open_ad_id :BIGINT,
        open_ad_ms :BIGINT,
        open_ad_skip_ms :BIGINT> COMMENT '启动信息',
    `err` STRUCT<error_code:BIGINT,
            msg:STRING> COMMENT '错误信息',
    `ts` BIGINT  COMMENT '时间戳'
) COMMENT '活动信息表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
    LOCATION '/warehouse/gmall/ods/ods_log_inc/'
    TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

说明：

1.建表多出来的字段（属性）如果源文件没有这个字段（属性），是不报错的，只是不被解析而已，表中对应的那个字段就为空。比如页面日志就没有start属性，那么表中对应的那一列就是空值，但启动日志这列就有值。

2.如果属性值对应的{}，且字段行数是固定的，那么用struct存储，具体参见上述common，page，err属性建表

3.如果属性值对应[]，里面又是{}且字段固定，外层用array存储，里面用struct，参见displays或actions属性字段建表

4.属性值对应的时间戳，用BIGINT存储，参加ts属性建表方式

5.单一字段，用string存储，如item_type :STRING