用户行为比较复杂json串日志解析

场景题（数仓处理项目的一个缩影）： 用户行为日志（ods_user_logs）中有关浏览商品的数据如下,数据schema如下：

user_id,
view_params,
exts,
ct

数据如下

user_id
view_params："order_condi排序条件01默认排序02价格排序03成交量排序&order_type 1升序 0降序 &key 查询热词 如苹果手机、华为、三只松鼠、图书等等(假设我们这里只针对产品信息搜索，即target_type=04)"

exts： {target_type 查询类型
		 target_category 查询商品分类如100表示手机类别
		 target_ids:[] 查询结果显示的商品ID记录
	   }

ct 浏览事件发送时间



{
    "user_id":"u0001",
    "view_params":"order_condition=03&order_type=1&key=华为手机",
    "exts":{"target_type":"04","target_category":"100",
    		"target_ids":"["1","2","3"]"
    		}
    "ct":"1567429965000"
}

需求如下：

用到的函数：
split(split(t.view_params,’&’)[0],’=’)[1] 可嵌套的切分函数split，

from_unixtime(cast(t.ct/1000 as bigint),‘yyyyMMddHH’) 时间戳转具体时间函数from_unixtime 和 强制类型转换函数 cast，

lateral view json_tuple(line,“user_id”,“view_params”,“exts”,“ct”) tmp
as user_id,view_params,exts,ct 一次性处理多个json字段的函数json_tuple，结合虚拟表lateral view 使用
get_json_object(get_json_object(b,’$.ext{s}^{\prime }){,}^{\prime }$.target_type’) as target_type 一次性处理一个json字段的可嵌套的函数get_json_object，

str_to_map(get_json_object(b,’$.view_params’),"&","=")[‘order_type’] as order_type str_to_map(text, delimiter1, delimiter2)[‘key’]分隔符1将文本分成K-V对，分隔符2分割每个K-V对

regexp_extract(get_json_object(b,’$.view_params’),’.=(.)\&.=(.)\&.=(.)’,3) as key 正则分组获取字符串中相应值的函数regexp_extract,
regexp_replace(target_ids,’\\[\\]"’,"") target_ids 正则匹配替换字符串的函数

concat_ws(",",collect_set(target_id)) target_ids concat_ws(separator, str1, str2,…) 指定分隔符(第一位)连接字符串的函数。collect_set函数把某列值连接成数组的函数。

row_number() over(sort by pv desc) rn 连续不重复的排名函数row_number

1. 首先解析上表字段并把结果放到dw_user_logs

insert into dw_user_logs

第一种方式：
select 
user_id,
split(split(t.view_params,'&')[0],'=')[1] as order_condition,
split(split(t.view_params,'&')[1],'=')[1] as order_type,
split(split(t.view_params,'&')[2],'=')[1] as key,
 target_type ,  
 target_category,
 target_ids,
from_unixtime(cast(t.ct/1000 as bigint),'yyyyMMddHH') as ct
from
(
select 
user_id,
view_params,
exts,
ct
from ods_user_logs
lateral view json_tuple(line,"user_id","view_params","exts","ct") tmp 
as user_id,view_params,exts,ct
) t1
lateral view json_tuple(exts,"target_type", "target_category","target_ids") tmp
as  target_type ,  target_category,target_ids 
;

第二种方式：
select
get_json_object(b,'$.user_id') as user_id,
str_to_map(get_json_object(b,'$.view_params'),"&","=")['order_condition'] as order_condition,
str_to_map(get_json_object(b,'$.view_params'),"&","=")['order_type'] as order_type,
str_to_map(get_json_object(b,'$.view_params'),"&","=")['key'] as key,
get_json_object(get_json_object(b,'$.exts'),'$.target_type') as target_type,
get_json_object(get_json_object(b,'$.exts'),'$.target_category') as target_category,
get_json_object(get_json_object(b,'$.exts'),'$.target_ids') as target_ids,
from_unixtime(cast((get_json_object(b,'$.ct')/1000)as bigint),'yyyyMMddHH') as ct
from log_json
;

第三种方式：
select
get_json_object(b,'$.user_id') as user_id,
regexp_extract(get_json_object(b,'$.view_params'),'.*=(.*)\\&.*=(.*)\\&.*=(.*)',1) as order_condition,
regexp_extract(get_json_object(b,'$.view_params'),'.*=(.*)\\&.*=(.*)\\&.*=(.*)',2) as order_type,
regexp_extract(get_json_object(b,'$.view_params'),'.*=(.*)\\&.*=(.*)\\&.*=(.*)',3) as key,
get_json_object(get_json_object(b,'$.exts'),'$.target_type') as target_type,
get_json_object(get_json_object(b,'$.exts'),'$.target_category') as target_category,
get_json_object(get_json_object(b,'$.exts'),'$.target_ids') as target_ids,
from_unixtime(cast((get_json_object(b,'$.ct')/1000)as bigint),'yyyyMMddHH') as ct
from log_json
;

2. 请统计每天每小时的用户浏览统计

时间 产品分类 UV浏览人次 PV浏览次数
2019091809 100 10000 12000

select 
hour,
 target_category,
 count(distinct user_id) uv,
count(user_id)  pv
from
(select 
user_id,
 target_category ,  
ct hour
from
dw_user_logs
) t1
group by target_category,ct
;

3. 请统计产品分类的热门产品TopN（以浏览次数作为比较条件）

产品分类 产品
100 1,2,3
101 6,7,8
102 10,11,12

 select
 target_category,
concat_ws(",",collect_set(target_id)) target_ids
from  
 (
 select
 target_category,
 target_id 
  from
  (
 select
 target_category,
row_number() over(sort by pv desc) rn,
target_id
from
(
select 
 target_category,
 target_id,
count(target_id)  pv
from
(
select 
 target_category ,
 target_id
from
select 
 target_category ,
 regexp_replace(target_ids,'\\[\\]"',"") target_ids
from
dw_user_logs
) t0
lateral view explode(split(target_ids,",")) tmp as target_id
) t1
group by target_category,target_id
) t2
) t3 where rn<=3
) t4
group by target_category
;

4. 统计查询热词

热词 查询用户数量uv 查询数量pv
Mate30 100000 250000
apple11 99999 199999

select 
key ,
count(distinct user_id) uv,
count(key)  pv
from
dw_user_logs
group by key
;