背景

内容推荐数据处理

需求

根据用户行为以及文章标签筛选出**用户最感兴趣(阅读最多)**的标签

重要函数

collect_set/collect_list
- 将group by中的某列转为一个数组返回
- collect_list不去重而collect_set去重

sort_array
- 排序

explode
- 将array 拆分
- lateral view 和 explode 配合使用,将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合

concat(str1,str2…)
- 返回结果为连接参数产生的字符串

concat_ws(separator,str1,str2)
- separator 连接符，连接符的位置放在两个字符串之间

str_to_map
- 把string聚合成map

流程分析和代码

1.上传数据并创建表

数据上传hdfs

hadoop fs -mkdir /tmp/demo  hadoop fs -mkdir /tmp/demo/user_action  hadoop fs -mkdir /tmp/demo/article_keywords  hadoop fs -put user_action.txt /tmp/demo/user_action  hadoop fs -put article_keywords.txt /tmp/demo/article_keywords

创建用户行为表

create external table if not exists user_actions(      user_id string,      article_id string,      time_stamp string  )  row format delimited fields terminated by ','  location '/tmp/demo/user_action';

创建文章表

create external table if not exists articles(      article_id string,      url string,      key_words array
   
      )  row format delimited fields terminated by ','   collection items terminated by '|'  location '/tmp/demo/article_keywords';  /*  key_words array
    
       数组的数据类型  collection items terminated by '|'  数组的元素之间用'|'分割  */

2. 分组查询每个用户的浏览记录

select user_id,collect_set(article_id) 	from user_actions group by user_id;

11      ["101","104"]	22      ["102","103","104"]	33      ["103","102","101"]	35      ["105","102"]	77      ["103","104"]	99      ["102","105"]

3. 查看每篇文章的关键词

select article_id,kw from articles 	laternal view explode(key_words) t as kw;

101     kw8	101     kw1	102     kw6	102     kw3	103     kw7	104     kw5	104     kw1	104     kw4	104     kw9

4. 找到用户查看文章的关键词并统计频率

select a.user_id,b.kw,count(1) as weight 	from user_actoions as a left outer join 	(select article_id,kw from articles laternal view explode(key_words) t as kw) b	on (a.article_id = b.article_id)	group by a.user_id,b.kw	order by a.user_id,weight desc

11      kw1     4	11      kw8     3	11      kw5     1	11      kw9     1	11      kw4     1	22      kw7     2	22      kw9     1	22      kw1     1	22      kw3     1	22      kw4     1	22      kw5     1	22      kw6     1	33      kw3     1	33      kw8     1	33      kw7     1	33      kw6     1	33      kw1     1	35      NULL    1	35      kw3     1	35      kw6     1	77      kw1     1	77      kw4     1	77      kw5     1	77      kw7     1	77      kw9     1	99      NULL    1	99      kw3     1	99      kw6     1

5. 将用户查看的关键字和频率合并成 key:value形式并按用户聚合

select c.user_id,concat_ws(',',collect_set(c.kw_w))from(--  select a.user_id,concat_ws(':',b.kw,cast(count(1) as string)) as kw_w select a.user_id,concat(b.kw,':',count(1)) as kw_w from user_action as a  left join (select article_id,kw from article lateral view explode(key_words) t as kw) b on a.article_id = b.article_id group by a.user_id,b.kw)as cgroup by c.user_id;

11	kw1:4,kw4:1,kw5:1,kw8:3,kw9:122	kw1:1,kw3:1,kw4:1,kw5:1,kw6:1,kw7:2,kw9:133	kw1:1,kw3:1,kw6:1,kw7:1,kw8:135	kw3:1,kw6:177	kw1:1,kw4:1,kw5:1,kw7:1,kw9:199	kw3:1,kw6:1

6.将上面聚合结果转为map放入表中

create table user_kws as 	select c.user_id,str_to_map(concat_ws(',',collect_set(c.kw_w))) as wm	from(		select a.user_id, concat_ws(':',b.kw,cast (count(1) as string)) as kw_w 		from user_actions as a left outer join 		(select article_id,kw from articles lateral view outer explode(key_words) t as kw) b		on (a.article_id = b.article_id)		group by a.user_id,b.kw	) as c	group by c.user_id;

7.从表中通过key查询map中的值

通过key查询map中的值

select user_id, wm['kw1'] from user_kws;

11      4	22      1	33      1	35      NULL	77      1	99      NULL

从表中获取map的所有key和value

select user_id,map_keys(wm),map_values(wm) from user_kws;

11	["kw1","kw4","kw5","kw8","kw9"]	["4","1","1","3","1"]22	["kw1","kw3","kw4","kw5","kw6","kw7","kw9"]	["1","1","1","1","1","2","1"]33	["kw1","kw3","kw6","kw7","kw8"]	["1","1","1","1","1"]35	["kw3","kw6"]	["1","1"]77	["kw1","kw4","kw5","kw7","kw9"]	["1","1","1","1","1"]99	["kw3","kw6"]	["1","1"]

转载地址：http://fmdsn.baihongyu.com/

你可能感兴趣的文章