使用Hive进行数据分析以及将分析后的数据导入MySQL数据库

前段时间写了一个用pandas进行数据预处理的一个博客，这篇博客是接着上一篇，数据预处理后的数据分析阶段，用的是Hive进行数据分析，再使用Sqoop把分析后的数据导入MySQL。

数据预处理的链接：使用pandas进行数据预处理
顺便给一下我写的环境配置的文章吧：MySQL、Hive本地模式及Sqoop的安装配置
如果只是想做一下这个数据分析的实验的话分享一下我处理过后的数据，网盘链接：https://pan.baidu.com/s/1_62my9QABEtIOuhkN_jd7w
提取码：lfse

---

******** 本文已进行修改 ********

这两天做可视化的时候发先导入MySQL的数据月份和天没有按序排列，稍有些乱。本想在查询的时候直接再进行排序，发现当时定义的类型是字符类型，导致排序不正确。又引起很多的麻烦，所以在这里提醒大家，创建MySQL表的时候要为以后的需求和操作做好准备，这样可以避免很多不必要的麻烦。本文已经把代码进行修改过了。

2020-8-25 00:08

可视化也已经做完了，可视化博客链接： 利用Python和Echarts制作“用户消费行为分析”可视化大屏

2020-8-29 23:35

---

注意

1. 文章里的表名、路径、主机名、表名、数据库名都是我自己环境里的，需要根据自己的环境进行改写。
2. 博客用来记录学习，里边的hive分析等语句不一定是最好的方法，如果有更好的方法或哪里有错，欢迎提出来指正。
3. 一切都是理想不出错的状态下进行，实际执行过程中可能会遇到各种各样的问题，可以评论如果我知道我会解答，不知道我可以和你一起找解决办法。
4. 因为数据预处理阶段插入的省份是随机的，所以如果是做了上一篇数据预处理之后拿来的数据做这个数据分析的话因为省份随机，所以关于省份的分析语句出来的结果不一样。

一、 将处理完成的数据上传至HDFS保存

1. 首先打开虚拟机；
2. 利用VMtools或者xftp等工具把处理完的数据先上传到虚拟机内；
3. 启动Hadoop：start-all.sh；
4. 将数据上传至HDFS；

hdfs dfs -put /本地路径 /hdfs路径

5. 查看是否上传成功：

hdfs dfs -ls /hdfs上传路径

二、 根据上传至HDFS的文件构建数据仓库Hive

1. 进入hive： hive
2. 构建数据仓库hive，把数据从hdfs上传至Hive：

# 查看数据库
hive>show databases;

# 构建数据库
hive>create database hive;

# 使用hive数据库
hive>use hive;

# 创建表（自定义分隔符）
hive>create table small_user_out (id int,user_id int,item_id int,behavior_type int,item_category int,time date,province string) row format delimited fields terminated by ',';

#上传数据
hive>load data inpath ‘/hive/data/small_user_out.csv’ into table small_user_out;

三、 Hive数据分析阶段

（1）根据user_id查询不重复的数据有多少行。

hive>select count(distinct user_id) 
	>from small_user_out;

（2）查询不重复的数据有多少行。

hive>select count(*) 
	>from(select distinct user_id,item_id,behavior_type,item_category,time,province from small_user_out)as M;

（3）统计时间在2014-12-11和2014-12-12这两天商品售出总和。

hive>select count(behavior_type)
	>from small_user_out
	>where (time='2014-12-11' and time='2014-12-12') and behavior_type=4;

（4）以月的第n天为统计单位，依次显示第n天网站卖出去的商品的个数。

hive>select count(*),month(time),day(time)
	>from small_user_out 
	>where behavior_type=4 
	>group by month(time),day(time), behavior_type;

（5）取给定时间和给定地点，我们可以求当天发出到该地点的货物的数量。（如查看2014-12-12发货到江西的数量。）

hive>select time,province,count(*)
	>from small_user_out
	>where behavior_type=4
	>group by time,province;

我这里直接做了每天各地点的发货数量，如果想单独查某一个可以：

hive>select time,province,count(*)
	>from samll_user_out
	>where behavior_type=4 and time='你想查询的时间' and province='你想查询的地点'
	>group by time,province;

（6）统计出2014-12-11的购买数、浏览数，分析购买率。

hive>select time,sum(case behavior_type when 1 then 1 else 0 end),
	>sum(case behavior_type when 4 then 1 else 0 end),
	>sum(case behavior_type when 4 then 1 else 0 end)/sum(case when behavior_type=1 then 1 when behavior_type=2 then 1 when behavior_type=3 then 1 when behavior_type=4 then 1 else 0 end)
	>from small_user_out
	>where time='2014-12-11'
	>group by time;

（7）在2014-12-12这天,用户10001082的所有点击行为数、所有用户的点击行为数、并获取用户10001082的所有点击行为数占所有用户的点击行为数的比例。

hive>select time,sum(case user_id when 10001082 then 1 else 0 end),
	>count(*),
	>sum(case behavior_type when 4 then 1 else 0 end)/sum(case when behavior_type=1 then 1 when behavior_type=2 then 1 when behavior_type=3 then 1 when behavior_type=4 then 1 else 0 end)
	>from small_user_out
	>where time='2014-12-12'
	>group by time;

（8）查询取时间为2014-12-12每个地区当天的浏览数。

hive>select time,province,count(*)
	>from small_user_out
	>where time='2014-12-12' and behavior_type=1
	>group by time,behavior_type,province;

（9）从时间维度统计商品售出明细。

hive>select time,count(*)
	>from small_user_out
	>where behavior_type=4
	>group by time;

四、Hive、MySql数据互导

将以下三题中的内容导入到Mysql中
在将数据导入MySQL之前一定要先查看一下MySQL的编码格式，如果编码格式不是utf-8需要把编码格式设置为utf-8,否则中文显示可能是乱码

（1）查询2014-12-12当天购买数超过5的id及购买商品数量，并以购买的商品数降序排列查询结果。

1. 建表Hive：

hive>create table fx_2 (user_id int,time date,buy_count int) row format delimited fields terminated by ','; 

2. 分析存储：

hive>insert into table fx_2 
	>select user_id,time,count(*) count 
	>from small_user_out 
	>where time='2014-12-12' and behavior_type=4
	>group by user_id,time,behavior_type 
	>having count(*)>5  
	>order by count desc;

3. 创建MySQL表：

# 再打开一个终端进入MySQL
mysql -uhive -p123123

# 使用hive库
mysql>use hive;

# 创建MySQL表
mysql>create table fx_2 (user_id int(10),time varchar(12),buy_count int);

4. 导入MySQL：

sqoop export --connect jdbc:mysql://localhost:3306/hive --username hive -password 123123 --table fx_2 --export-dir hdfs://hostname:8020/user/hive/warehouse/hive.db/fx_2 --input-fields-terminated-by ‘,’

（2）以月的第n天为统计单位，依次显示第n天网站的购买数、浏览数，分析购买率。

1. Hive建表：

hive>create table fx_4 (month int,day int,v_count int,b_count int,b_all double) row format delimited fields terminated by ',';

2. 分析存储：

hive>insert into table fx_4
	>select month(time),day(time),
	>sum(case behavior_type when 1 then 1 else 0 end),
	>sum(case behavior_type when 4 then 1 else 0 end),
	>sum(case behavior_type when 4 then 1 else 0 end)/sum(case when behavior_type=1 then 1 when behavior_type=2 then 1 when behavior_type=3 then 1 when behavior_type=4 then 1 else 0 end)
	>from small_user_out
	>group by time;

3. MySQL建表：

# 同上
mysql>create table fx_4 (month int(8),day int(8),v_count int,b_count int,b_u double);

4. 将数据导入MySQL：

sqoop export --connect jdbc:mysql://localhost:3306/hive --username hive -password 123123 --table fx_4 --export-dir hdfs://hostname:8020/user/hive/warehouse/hive.db/fx_4 --input-fields-terminated-by ', ’

（3）取时间为2014-12-12求当天各地区的购买货物的数量。

1. 在hive建表：

hive>create table fx_3 (time date,province string,buy_count int) row format delimited fields terminated by ',';

2. 分析存储：

hive>insert into table fx_3
	>select time, province,count(province)
	>from small_user_out
	>where time='2014-12-12' and behavior_type=4
	>group by time,province;

3. 在MySQL建表：

# 同上
mysql>create table fx_3 (time varchar(12),province varchar(12),buy_count int);

4. 将数据导入MySQL：

sqoop export --connect jdbc:mysql://localhost:3306/hive --username hive -password 123123 --table fx_3 --export-dir hdfs://master-slave:8020/user/hive/warehouse/hive.db/fx_3 --input-fields-terminated-by ', ’