Array:

create table if not exists explode_array
(
    userId string,
    userName string,
    tags   array<string>
) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

导入数据：

load data local inpath '/Users/zhenqin/temp/data.txt' overwrite into table explode_array;

查询：

select userId,userName,tagId from explode_array lateral view explode(tags) as tagId;


select user_tag.tagId, count(*) as count from (select userId,userName,tagId from explode_array lateral view explode(tags) tags as tagId) as user_tag group by user_tag.tagId order by count DESC;

Map

create table if not exists explode_map
(
    userId string,
    userName string,
    tags   map<string, int>
) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS  TERMINATED BY ':';

导入数据：

load data local inpath '/Users/zhenqin/temp/data_map.txt' overwrite into table explode_map;

查询：

select userId,userName,tagId,weight from explode_map lateral view explode(tags) tags as tagId, weight;


select user_tag.tagId, count(*) as count from (select userId,userName,tagId,weight from explode_map lateral view explode(tags) tags as tagId, weight) as user_tag group by user_tag.tagId order by count DESC;

select userId, userName, array_contains(map_keys(tags), '90') as tagId from explode_map;
select userId, userName, map_keys(tags) as tagIds from explode_map where array_contains(map_keys(tags), '90');