3月12日

解析JSON格式日志

使用LATERAL VIEW EXPLODE展开数组

-- 创建包含嵌套结构的表
CREATE TABLE user_behavior (
    user_id STRING,
    actions ARRAY<STRUCT<page:STRING, time:TIMESTAMP>>
);

-- 查询用户访问页面TOP 3
SELECT user_id, page, COUNT(*) AS cnt
FROM user_behavior
LATERAL VIEW EXPLODE(actions) tmp AS action
GROUP BY user_id, page
ORDER BY cnt DESC LIMIT 3;