基于Hadoop和Hive的聊天数据（FineBI）可视化分析_数据分析

1. 准备工作

2. 新建数据库连接

3. 在hive数据库中创建存放数据的表

4. etl数据清洗

5. 指标

6. 进入fine bi数据中心

参考内容https://www.bilibili.com/read/cv15490959/

数据文件、jar包、插件

https://pan.baidu.com/s/1mpquo0egkyztlhrcpik2qg?pwd=7w0k

1. 准备工作

在finebi6.0\webapps\webroot\web-inf\lib下放置jar包

启动finebi服务器

安装hive隔离插件

选择该文件

重启服务器

2. 新建数据库连接

在虚拟机后台启动metastore和hiveserver2服务（在hive目录下）

进入beeline客户端

--hive2://后可以是主机名--
!connect jdbc:hive2://192.168.224.112:10000

回车然后输入用户名，我的是root，再回车

密码根据自己的填（我没有），回车

如果不成功，就先配置虚拟机中/hadoop父文件夹/hadoop/etc/hadoop/core-site.xml文件

和/hive父文件夹/hive/conf/hive-site.xml文件

然后重启sh，后台挂起metastore，hiveserver2，启动beeline。

在fine bi上新建hive数据库连接

数据库名称为自己在hive中创建的数据库，主机为虚拟机ip，端口10000，用户名root

3. 在hive数据库中创建存放数据的表

创建dgy_30w表（myhive为我自己的数据库），操作在hive和beeline中都可以

create table myhive.dgy_30w (
    msg_time string comment "消息发送时间",
    sender_name string comment "发送人昵称",
    sender_account string comment "发送人账号",
    sender_sex string comment "发送人性别",
    sender_ip string comment "发送人ip地址",
    sender_os string comment "发送人操作系统",
    sender_phonetype string comment "发送人手机型号",
    sender_network string comment "发送人网络类型",
    sender_gps string comment "发送人的gps定位",
    receiver_name string comment "接收人呢称",
    receiver_ip string comment "接收人ip",
    receiver_account string comment "接收人账号",
    receiver_os string comment "接收人操作系统",
    receiver_phonetype string comment"接收人手机型号",
    receiver_network string comment "接收人网络类型",
    receiver_gps string comment"接收人的gps定位",
    receiver_sex string comment"接收人性别",
    msg_type string comment"消息类型",
    distance string comment"双方距离",
    message string comment"消息内容"
    );

上传数据

方法一：

通过xshell的xftp把csv文件上传到虚拟机opt目录下

把csv文件数据上传到dgy_30w表中

load data local inpath '/opt/chat_data-30w.csv' overwrite into table dgy_30w;

方法二：

hdfs数据加载

将csv文件上传到hdfs /data下

hdfs dfs -put /opt/chat_data-30w.csv /data

在终端beeline中输入load data inpath '/data/chat_data-30w.csv' into table dgy_30w;

load data inpath '/data/chat_data-30w.csv' overwrite into table dgy_30w;

导入成功。

4. etl数据清洗

建立dgy_30w_etl表

create table myhive.dgy_30w_etl (
    msg_time string comment "消息发送时间",
    sender_name string comment "发送人昵称",
    sender_account string comment "发送人账号",
    sender_sex string comment "发送人性别",
    sender_ip string comment "发送人ip地址",
    sender_os string comment "发送人操作系统",
    sender_phonetype string comment "发送人手机型号",
    sender_network string comment "发送人网络类型",
    sender_gps string comment "发送人的gps定位",
    receiver_name string comment "接收人呢称",
    receiver_ip string comment "接收人ip",
    receiver_account string comment "接收人账号",
    receiver_os string comment "接收人操作系统",
    receiver_phonetype string comment"接收人手机型号",
    receiver_network string comment "接收人网络类型",
    receiver_gps string comment"接收人的gps定位",
    receiver_sex string comment"接收人性别",
    msg_type string comment"消息类型",
    distance string comment"双方距离",
    message string comment"消息内容",
    msg_day string comment"消息日期(日)",
    msg_hour string comment"消息时间(小时)",
    sender_lng double comment"经度",
    sender_lat double comment"纬度"
    );

开始清洗

insert overwrite table myhive.dgy_30w_etl
select *,
to_date(msg_time) as msg_day,
hour(msg_time) as msg_hour,
split(sender_gps,',')[0] as sender_lng,
split(sender_gps,',')[1] as sender_lat
from myhive. dgy_30w
where length(sender_gps)>0；

运行成功，查询

5. 指标

统计今日消息总量

create table if not exists myhive.tb_rs_total_msg_cnt 
comment"每日消息总量" as
select msg_day,count(*) as total_msg_cnt
from myhive.dgy_30w_etl
group by msg_day;

统计每小时消息量、发送和接收用户数

create table if not exists myhive.tb_rs_hour_msg_cnt 
comment"每小时消息量趋势" as
select msg_hour,
count(*)as total_msg_cnt,
count(distinct sender_account)as sender_user_cnt,
count(distinct receiver_account)as receiver_user_cnt
from myhive.dgy_30w_etl group by msg_hour;

统计今日各地区发送消息总量

create table if not exists myhive.tb_rs_loc_cnt
comment"今日各地区发送消息总量"as
select
msg_day,sender_lng,sender_lat,sender_gps,
count(*)as total_msg_cnt from myhive.dgy_30w_etl 
group by msg_day,sender_lng,sender_lat,sender_gps;

统计今日发送和接收用户人数

create table if not exists myhive.tb_rs_user_cnt
comment"今日发送消息人数、接收消息人数"as
select msg_day,
count(distinct sender_account)as sender_user_cnt,
count(distinct receiver_account)as receiver_user_cnt 
from myhive.dgy_30w_etl
group by msg_day;

统计发送消息条数最多的top10用户

create table if not exists myhive.tb_rs_s_user_top10
comment"发送消息条数最多的top10用户"as 
select sender_name as username,
count(*)as sender_msg_cnt 
from myhive.dgy_30w_etl
group by sender_name
order by sender_msg_cnt desc
limit 10;

统计接收消息条数最多的top10用户

create table if not exists myhive.tb_rs_r_user_top10 
comment"接收消息条数最多的top10用户" as 
select receiver_name as username,
count(*)as receiver_msg_cnt
from myhive.dgy_30w_etl
group by receiver_name
order by receiver_msg_cnt desc
limit 10;

统计发送人的手机型号分布情况

create table if not exists myhive.tb_rs_sender_phone 
comment"发送人的手机型号分布"as
select sender_phonetype,
count(sender_account)as cnt 
from myhive.dgy_30w_etl
group by sender_phonetype;

统计发送人的手机操作系统分布

create table if not exists myhive.tb_rs_sender_os
comment"发送人的手机操作系统分布"as
select sender_os,
count(sender_account)as cnt
from myhive.dgy_30w_etl
group by sender_os;

进入myhive数据库，查看创建的十个表