HIVE如何统计各个区域下最热门的TOP3的商品

小编给大家分享一下HIVE如何统计各个区域下最热门的TOP3的商品，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！
环境：
hadoop-2.6.0-cdh6.7.0
hive-1.1.0-cdh6.7.0
sqoop-1.4.6-cdh6.7.0
MySQL5.6.39
需求：HIVE统计各个区域下最热门的TOP3的商品，将统计结果导出到MySQL中
MySQL中有：city_info 城市信息表和product_info 商品信息表
HIVE中有：user_click 用户行为日志，按date分区

一、MySQL数据库建库建表及初始化数据
1.1 MySQL创建 ruozedata数据库：
CREATE DATABASE ruozedata;
1.2 创建city_info表
DROP TABLE if exists city_info;
CREATE TABLE `city_info` (
`city_id` int(11) DEFAULT NULL,
`city_name` varchar(255) DEFAULT NULL,
`area` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
初始化city_info表数据
insert into `city_info`(`city_id`,`city_name`,`area`) values (1,’BEIJING’,’NC’),(2,’SHANGHAI’,’EC’),(3,’NANJING’,’EC’),(4,’GUANGZHOU’,’SC’),(5,’SANYA’,’SC’),(6,’WUHAN’,’CC’),(7,’CHANGSHA’,’CC’),(8,’XIAN’,’NW’),(9,’CHENGDU’,’SW’),(10,’HAERBIN’,’NE’);

1.3 创建product_info表
DROP TABLE if exists product_info;
CREATE TABLE `product_info` (
`product_id` int(11) DEFAULT NULL,
`product_name` varchar(255) DEFAULT NULL,
`extend_info` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
初始化product_info表数据
insert into product_info(product_id,product_name,extend_info) values (1,’product1′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (2,’product2′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (3,’product3′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (4,’product4′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (5,’product5′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (6,’product6′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (7,’product7′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (8,’product8′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (9,’product9′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (10,’product10′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (11,’product11′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (12,’product12′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (13,’product13′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (14,’product14′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (15,’product15′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (16,’product16′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (17,’product17′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (18,’product18′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (19,’product19′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (20,’product20′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (21,’product21′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (22,’product22′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (23,’product23′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (24,’product24′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (25,’product25′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (26,’product26′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (27,’product27′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (28,’product28′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (29,’product29′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (30,’product30′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (31,’product31′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (32,’product32′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (33,’product33′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (34,’product34′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (35,’product35′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (36,’product36′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (37,’product37′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (38,’product38′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (39,’product39′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (40,’product40′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (41,’product41′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (42,’product42′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (43,’product43′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (44,’product44′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (45,’product45′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (46,’product46′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (47,’product47′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (48,’product48′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (49,’product49′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (50,’product50′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (51,’product51′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (52,’product52′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (53,’product53′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (54,’product54′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (55,’product55′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (56,’product56′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (57,’product57′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (58,’product58′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (59,’product59′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (60,’product60′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (61,’product61′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (62,’product62′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (63,’product63′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (64,’product64′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (65,’product65′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (66,’product66′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (67,’product67′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (68,’product68′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (69,’product69′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (70,’product70′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (71,’product71′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (72,’product72′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (73,’product73′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (74,’product74′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (75,’product75′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (76,’product76′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (77,’product77′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (78,’product78′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (79,’product79′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (80,’product80′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (81,’product81′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (82,’product82′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (83,’product83′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (84,’product84′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (85,’product85′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (86,’product86′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (87,’product87′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (88,’product88′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (89,’product89′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (90,’product90′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (91,’product91′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (92,’product92′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (93,’product93′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (94,’product94′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (95,’product95′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (96,’product96′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (97,’product97′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (98,’product98′,'{“product_status”:1}’);
insert into product_info(product_id,product_name,extend_info) values (99,’product99′,'{“product_status”:0}’);
insert into product_info(product_id,product_name,extend_info) values (100,’product100′,'{“product_status”:1}’);

1.4 创建MySQL数据库产品统计表product_stat
drop table if exists `product_stat`;
CREATE TABLE `product_stat`(
`product_id` int(11) DEFAULT NULL
,`product_name` varchar(255) DEFAULT NULL
,`area` varchar(255) DEFAULT NULL
,`click_count` int(11)
,`rank` int
,`days` varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

二、使用sqoop将MySQL的city_info表和product_info表的数据导入到hive（包括创建表）
2.1 操作之前先看看hive里面的表
hive> show databases;
OK
default
hive2
hive2_ruozedata
ruozedata
sqoophive
Time taken: 0.06 seconds, Fetched: 5 row(s)
hive> use ruozedata;
OK
Time taken: 0.069 seconds
hive> show tables;
OK
emp_hive
gw_test
hive_wc
rating_json
ruozedata_emp
ruozedata_emp_managed
ruozedata_emp_partition
ruozedata_person
Time taken: 0.061 seconds, Fetched: 8 row(s)
hive>

2.2 导入city_info表（从MySQL导入数据到hive的ruozedata.city_info表）：
sqoop import
–connect jdbc:mysql://localhost:33066/ruozedata
–username root
–password root
–table city_info -m 1
–mapreduce-job-name FromMySQLToHive
–delete-target-dir
–create-hive-table
–hive-table ruozedata.city_info
–hive-import –hive-overwrite;

2.3 导入product_info表（从MySQL导入数据到hive的ruozedata.product_info表）：
sqoop import
–connect jdbc:mysql://localhost:33066/ruozedata
–username root
–password root
–table product_info -m 1
–mapreduce-job-name FromMySQLToHive
–delete-target-dir
–create-hive-table
–hive-table ruozedata.product_info
–hive-import –hive-overwrite;

2.4 操作完之后再看看hive里面的表：
hive> show tables;
OK
city_info
emp_hive
gw_test
hive_wc
product_info
rating_json
ruozedata_emp
ruozedata_emp_managed
ruozedata_emp_partition
ruozedata_person
Time taken: 0.061 seconds, Fetched: 10 row(s)
hive> select * from city_info;
OK
1 BEIJING NC
2 SHANGHAI EC
3 NANJING EC
4 GUANGZHOU SC
5 SANYA SC
6 WUHAN CC
7 CHANGSHA CC
8 XIAN NW
9 CHENGDU SW
10 HAERBIN NE
Time taken: 0.554 seconds, Fetched: 10 row(s)
hive> select * from product_info;
OK
1 product1 {“product_status”:1}
2 product2 {“product_status”:1}
3 product3 {“product_status”:1}
4 product4 {“product_status”:1}
5 product5 {“product_status”:1}
6 product6 {“product_status”:1}
7 product7 {“product_status”:1}
8 product8 {“product_status”:1}
9 product9 {“product_status”:0}
10 product10 {“product_status”:1}
11 product11 {“product_status”:0}
12 product12 {“product_status”:0}
13 product13 {“product_status”:0}
14 product14 {“product_status”:0}
15 product15 {“product_status”:1}
16 product16 {“product_status”:0}
17 product17 {“product_status”:1}
18 product18 {“product_status”:0}
19 product19 {“product_status”:1}
20 product20 {“product_status”:1}
21 product21 {“product_status”:0}
22 product22 {“product_status”:0}
23 product23 {“product_status”:0}
24 product24 {“product_status”:0}
25 product25 {“product_status”:1}
26 product26 {“product_status”:1}
27 product27 {“product_status”:0}
28 product28 {“product_status”:1}
29 product29 {“product_status”:0}
30 product30 {“product_status”:0}
31 product31 {“product_status”:0}
32 product32 {“product_status”:0}
33 product33 {“product_status”:1}
34 product34 {“product_status”:1}
35 product35 {“product_status”:0}
36 product36 {“product_status”:0}
37 product37 {“product_status”:1}
38 product38 {“product_status”:0}
39 product 香港云主机39 {“product_status”:0}
40 product40 {“product_status”:1}
41 product41 {“product_status”:1}
42 product42 {“product_status”:1}
43 product43 {“product_status”:1}
44 product44 {“product_status”:0}
45 product45 {“product_status”:1}
46 product46 {“product_status”:1}
47 product47 {“product_status”:0}
48 product48 {“product_status”:1}
49 product49 {“product_status”:1}
50 product50 {“product_status”:1}
51 product51 {“product_status”:1}
52 product52 {“product_status”:0}
53 product53 {“product_status”:0}
54 product54 {“product_status”:1}
55 product55 {“product_status”:0}
56 product56 {“product_status”:0}
57 product57 {“product_status”:1}
58 product58 {“product_status”:1}
59 product59 {“product_status”:1}
60 product60 {“product_status”:1}
61 product61 {“product_status”:0}
62 product62 {“product_status”:1}
63 product63 {“product_status”:1}
64 product64 {“product_status”:0}
65 product65 {“product_status”:0}
66 product66 {“product_status”:1}
67 product67 {“product_status”:1}
68 product68 {“product_status”:0}
69 product69 {“product_status”:1}
70 product70 {“product_status”:0}
71 product71 {“product_status”:0}
72 product72 {“product_status”:0}
73 product73 {“product_status”:1}
74 product74 {“product_status”:0}
75 product75 {“product_status”:1}
76 product76 {“product_status”:0}
77 product77 {“product_status”:0}
78 product78 {“product_status”:1}
79 product79 {“product_status”:0}
80 product80 {“product_status”:0}
81 product81 {“product_status”:0}
82 product82 {“product_status”:1}
83 product83 {“product_status”:1}
84 product84 {“product_status”:1}
85 product85 {“product_status”:0}
86 product86 {“product_status”:1}
87 product87 {“product_status”:1}
88 product88 {“product_status”:1}
89 product89 {“product_status”:1}
90 product90 {“product_status”:1}
91 product91 {“product_status”:1}
92 product92 {“product_status”:0}
93 product93 {“product_status”:0}
94 product94 {“product_status”:1}
95 product95 {“product_status”:0}
96 product96 {“product_status”:0}
97 product97 {“product_status”:1}
98 product98 {“product_status”:1}
99 product99 {“product_status”:0}
100 product100 {“product_status”:1}
Time taken: 0.141 seconds, Fetched: 100 row(s)
hive>
sqoop成功的将MySQL的city_info表和product_info表的数据导入到hive（包括创建表）

三、在hive里面对表和数据进行操作，最后在进行统计。
3.1 创建用户行为日志表user_click分区表
create table user_click
(
user_id int
,session_id string
,action_time string
,city_id int
,product_id int
)
partitioned by (date string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
;

3.2 上传user_click.txt数据文件，并导入数据到表。
上传user_click.txt数据文件到/home/hadoop/data 目录。
[hadoop@hadoop002 data]$ ll
total 62912
-rw-rw-r–. 1 hadoop hadoop 652 Jun 4 16:34 emp.txt
-rw-r–r–. 1 hadoop hadoop 84 Jun 7 09:53 hive_row_number.txt
-rw-rw-r–. 1 hadoop hadoop 34 Jun 11 15:17 hive_wc.txt
-rw-r–r–. 1 hadoop hadoop 63602280 Jun 7 09:54 rating.json
-rw-rw-r–. 1 hadoop hadoop 67 Jun 6 18:30 student.txt
-rwxrwxrwx. 1 hadoop hadoop 725264 Jun 9 21:28 user_click.txt
[hadoop@hadoop002 data]$ pwd
/home/hadoop/data
导入数据到user_click表：
LOAD DATA LOCAL INPATH ‘/home/hadoop/data/user_click.txt’ OVERWRITE INTO TABLE user_click PARTITION(date=’2018-06-20′);

3.3 在hive创建产品统计分区表product_stat，用于写入统计结果
create table product_stat
(
product_id int
,product_name string
,area string
,click_count int
,rank int
,days string
)
partitioned by (date string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘t’
;

3.4 统计各个区域下最热门的TOP3的商品，并写入到统计表product_stat
insert overwrite table product_stat partition(date=’2018-06-20′)
select
t.product_id
,t.product_name
,t.area
,t.click_count
,t.rank
,’2018-06-20′ as days
from
(
select
ci.area as area
,uc.product_id as product_id
,pd.product_name as product_name
,count(uc.product_id) as click_count
,(row_number() over(partition by ci.area order by count(uc.product_id) desc)) as rank
from city_info ci
left join user_click uc on uc.city_id = ci.city_id and uc.date=’2018-06-20′
left join product_info pd on pd.product_id = uc.product_id
group by ci.area,uc.product_id,pd.product_name
)t where t.rank;
查询统计结果
hive> select * from product_stat where date=’2018-06-20′;
OK
7 product7 CC 39 1 2018-06-20 2018-06-20
26 product26 CC 39 2 2018-06-20 2018-06-20
70 product70 CC 38 3 2018-06-20 2018-06-20
4 product4 EC 40 1 2018-06-20 2018-06-20
96 product96 EC 32 2 2018-06-20 2018-06-20
99 product99 EC 31 3 2018-06-20 2018-06-20
9 product9 NC 16 1 2018-06-20 2018-06-20
40 product40 NC 16 2 2018-06-20 2018-06-20
94 product94 NC 13 3 2018-06-20 2018-06-20
NULL NULL NE 0 1 2018-06-20 2018-06-20
67 product67 NW 20 1 2018-06-20 2018-06-20
56 product56 NW 20 2 2018-06-20 2018-06-20
48 product48 NW 19 3 2018-06-20 2018-06-20
38 product38 SC 35 1 2018-06-20 2018-06-20
88 product88 SC 34 2 2018-06-20 2018-06-20
33 product33 SC 34 3 2018-06-20 2018-06-20
16 product16 SW 20 1 2018-06-20 2018-06-20
95 product95 SW 19 2 2018-06-20 2018-06-20
60 product60 SW 19 3 2018-06-20 2018-06-20
Time taken: 0.345 seconds, Fetched: 19 row(s)
hive>

四、使用sqoop将hive的统计表product_stat数据导出到MySQL的统计表product_stat中：
./sqoop export
–connect jdbc:mysql://localhost:33066/ruozedata
–username root
–password root
–table product_stat
–export-dir /ruozedata_03/product_stat/date=2018-06-20
–input-fields-terminated-by ‘t’
–input-null-string ” –input-null-non-string 0
–columns “product_id,product_name,area,click_count,rank,days”
–update-key product_id,area –update-mode allowinsert
;

去MySQL里面查看统计数据是否有成功导出：
mysql> select * from product_stat where days=’2018-06-20′ order by area,rank;
+————+————–+——+————-+——+————+
| product_id | product_name | area | click_count | rank | days |
+————+————–+——+————-+——+————+
| 7 | product7 | CC | 39 | 1 | 2018-06-20 |
| 26 | product26 | CC | 39 | 2 | 2018-06-20 |
| 70 | product70 | CC | 38 | 3 | 2018-06-20 |
| 4 | product4 | EC | 40 | 1 | 2018-06-20 |
| 96 | product96 | EC | 32 | 2 | 2018-06-20 |
| 99 | product99 | EC | 31 | 3 | 2018-06-20 |
| 9 | product9 | NC | 16 | 1 | 2018-06-20 |
| 40 | product40 | NC | 16 | 2 | 2018-06-20 |
| 94 | product94 | NC | 13 | 3 | 2018-06-20 |
| 67 | product67 | NW | 20 | 1 | 2018-06-20 |
| 56 | product56 | NW | 20 | 2 | 2018-06-20 |
| 48 | product48 | NW | 19 | 3 | 2018-06-20 |
| 38 | product38 | SC | 35 | 1 | 2018-06-20 |
| 88 | product88 | SC | 34 | 2 | 2018-06-20 |
| 33 | product33 | SC | 34 | 3 | 2018-06-20 |
| 16 | product16 | SW | 20 | 1 | 2018-06-20 |
| 95 | product95 | SW | 19 | 2 | 2018-06-20 |
| 60 | product60 | SW | 19 | 3 | 2018-06-20 |
+————+————–+——+————-+——+————+
18 rows in set (0.00 sec)
看完了这篇文章，相信你对“HIVE如何统计各个区域下最热门的TOP3的商品”有了一定的了解，如果想了解更多相关知识，欢迎关注开发云行业资讯频道，感谢各位的阅读！

相关推荐: Anaconda在Windows 10中安装后如何配置

小编给大家分享一下Anaconda在Windows 10中安装后如何配置，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！当将Anaconda 在Windows 10中安装完成后，还不能算完成所有安装。因为这个时候直接进入cmd 是无法直接使用c…