Hive_02统计各个城市下最受欢迎的TopN产品

概述

需求：统计各个城市所属区域下最受欢迎的Top 3产品

分析：各个城市的TopN产品，是分组求TopN问题，需要使用窗口函数

数据：城市区域对应信息、产品信息表以及用户点击日志
其中 城市区域对应信息、产品信息表 存在MySQL中，而我们需要在Hive中去分析数据，所以需要将MySQL中的表导入Hive中，这里使用sqoop

步骤

1. 创建 用户点击行为日志表 ，并load数据

//创建表格
create table user_click(user_id int,
session_id string,
action_time string,
city_id int,
product_id int)
partitioned by(day string)
row format delimited fields terminated by ',';
//加载数据
load data local inpath 'xxx' overwrite into table user_click partition(day='2016-05-05');

2. 创建 城市区域对应信息表 并用Sqoop将MySQL中的表导入Hive

//创建表格
create table city_info(
city_id int,
city_name string,
area string
)row format delimited fields terminated by '\t';

//使用Sqoop将MySQL表导入Hive
sqoop import \
--connect jdbc:mysql://localhost:3306/test06 \
--username root --password root \
--delete-target-dir \
--table city_info \
--hive-import \
--hive-table city_info \
--hive-overwrite \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--split-by city_id \
-m 2

3. 创建 商品对应信息表 并用Sqoop将MySQL中的表导入Hive

//创建表格
create table product_info(
product_id int,
product_name string,
extend_info string
)row format delimited fields terminated by '\t';

//使用Sqoop将MySQL表导入Hive
sqoop import \
--connect jdbc:mysql://localhost:3306/test06 \
--username root --password root \
--delete-target-dir \
--table product_info \
--hive-import \
--hive-table product_info \
--hive-overwrite \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--split-by product_id \
-m 2

4. 补全商品城市信息

// 创建一个临时的产品点击的基本信息表
create table tmp_product_click_basic_info
as
select u.product_id, u.city_id, c.city_name, c.area
from
(select product_id, city_id from user_click where day='2016-05-05' ) u
join
(select city_id, city_name,area from city_info) c
on u.city_id = c.city_id;

5. 统计各区域各商品的点击次数

create table tmp_area_product_click_count 
as 
select 
product_id, area, count(1) click_count 
from 
tmp_product_click_basic_info 
group by 
product_id, area;

6. 获取完整的商品信息的各区域的访问次数

create table tmp_area_product_click_count_full_info
as
select 
a.product_id, b.product_name, a.area, a.click_count
from 
tmp_area_product_click_count a join product_info b
on a.product_id = b.product_id;

7. 统计出结果

create table area_product_click_count_top3
select *
from (
select 
product_id, product_name,area, click_count,
row_number() over(partition by area order by click_count desc) rank
from 
tmp_area_product_click_count_full_info
) t where t.rank <=3;