本文共 2572 字，大约阅读时间需要 8 分钟。
1. 同列多行数据组合成一个字段cell的方法， top N 问题的hive方案 如下：
hive 列转行 to json与to array list set等复杂结构，hive topN的提取的窗口统计方法 select ll, collect_list(n) , -- 将topN 转换成 List or Json with the help of collect_set(xx) collect_list(xx) collect_list(nn), collect_list(ll), collect_list(dd) from ( select concat('\'', n, '\'') as nn, n , ll , concat_ws(":", concat('\\\'', n, '\\\''), ll) as dd , row_number() over (partition by ll order by n desc ) as num1 -- 某用户的所有文章点击率排序 from ( select 1 as n, '4' as ll UNION all SELECT 2 as n, '4' as ll UNION all select 3 as n, '5' as ll UNION all SELECT 4 as n, '5' as ll UNION all SELECT 5 as n, '4' as ll UNION all select 6 as n, '5' as ll UNION all SELECT 7 as n, '5' as ll UNION all SELECT 8 as n, '4' as ll UNION all select 9 as n, '5' as ll UNION all SELECT 10 as n, '5' as ll ) a ) c where num1 <= 3 -- 筛选top 3 group by ll
2. 建表存储list类型数据的方法以及注意点
CREATE TABLE if not exists celebrity_basic_info ( author_id bigint COMMENT 'id', area arrayCOMMENT '复杂类型的数据' ) COMMENT '-----'PARTITIONED BY( dt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',' STORED AS textfile ; --这里要注意存储时的序列化转换 collection item 的分隔符。
3. 将array<string>类型数据转成string显示的方法
select author_id ,concat( case when size(area)=-1 then '[' else '["' end,concat_ws('","' , area ) , case when size(area)=-1 then ']' else '"]' end) --组装拼接成json list from celebrity_basic_info