导航：首页 > 互联网科技 >

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

发表于：2024-10-22 作者：千家信息网编辑

千家信息网最后更新 2024年10月22日，小编给大家分享一下HIVE如何统计各个区域下最热门的TOP3的商品，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！环境：hadoop-2.6.0-cdh6.7.0hive-1.1.0-

千家信息网最后更新 2024年10月22日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 product39 {"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<=3
;
查询统计结果
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)