案例:陌陌聊天数据分析

 背景分析:     

        陌陌作为聊天平台每天都会有大量的用户在线,会出现大量的聊天数据,通过对 聊天数据的统计分析 ,可以更好的 对用户构建精准的 用户画像 ,为用户提供更好的服务以及实现 ROI 的平台运营推广,给公司的发展决策提供精确的数据支撑。
需求:
  • 统计今日总消息量
  • 统计今日每小时消息量、发送和接收用户数
  • 统计今日各地区发送消息数据量
  • 统计今日发送消息和接收消息的用户数
  • 统计今日发送消息最多的Top10用户
  • 统计今日接收消息最多的Top10用户
  • 统计发送人的手机型号分布情况
  • 统计发送人的设备操作系统分布情况

目标:

基于 Hadoop Hive 实现聊天数据统计分析,构建 聊天数据分析报表
数据分析的前提条件:
1. hadoop集群正常启动
2. hive库相关服务启动

一、建库建表、加载数据

1. 建库

--如果数据库已存在就删除
drop database if exists db_msg cascade;
--创建数据库
create database db_msg;
--切换数据库
use db_msg;

2. 建原始数据表 

--如果表已存在就删除
drop table if exists db_msg.tb_msg_source;
--建表
create table db_msg.tb_msg_source(
  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 "消息内容"
)
--指定分隔符为制表符
row format delimited fields terminated by '\t';

3. 加载原始数据到hive库

--上传数据文件到node1服务器本地文件系统(HS2服务所在机器)
--shell:  mkdir -p /export/hivedata

--加载数据到表中
load data local inpath '/export/hivedata/data1.tsv' into table db_msg.tb_msg_source;
load data local inpath '/export/hivedata/data2.tsv' into table db_msg.tb_msg_source;

--查询表 验证数据文件是否映射成功
select * from tb_msg_source limit 10;

--统计行数
select count(*) as cnt from tb_msg_source;

二、ETL数据清洗

--问题1:当前数据中,有一些数据的字段为空,不是合法数据
select
   msg_time,
   sender_name,
   sender_gps
from db_msg.tb_msg_source
where length(sender_gps) = 0
limit 10;

--问题2:需求中,需要统计每天、每个小时的消息量,但是数据中没有天和小时字段,只有整体时间字段,不好处理
select
   msg_time
from db_msg.tb_msg_source
limit 10;

--问题3:需求中,需要对经度和维度构建地区的可视化地图,但是数据中GPS经纬度为一个字段,不好处理
select
   sender_gps
from db_msg.tb_msg_source
limit 10;

--ETL实现
--如果表已存在就删除

drop table if exists db_msg.tb_msg_etl;
--将Select语句的结果保存到新表中
create table db_msg.tb_msg_etl as
select
  *,
  substr(msg_time,0,10) as dayinfo, --获取天
  substr(msg_time,12,2) as hourinfo, --获取小时
  split(sender_gps,",")[0] as sender_lng, --提取经度
  split(sender_gps,",")[1] as sender_lat --提取纬度
from db_msg.tb_msg_source

--过滤字段为空的数据
where length(sender_gps) > 0 ;

--验证ETL结果
select
    msg_time,dayinfo,hourinfo,sender_gps,sender_lng,sender_lat
from db_msg.tb_msg_etl
limit 10;

三、需求指标统计

1. 统计今日总消息量

create table if not exists tb_rs_total_msg_cnt
comment "今日消息总量"
as
select
  dayinfo,
  count(*) as total_msg_cnt
from db_msg.tb_msg_etl
group by dayinfo;

--结果验证

select * from tb_rs_total_msg_cnt;

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

create table if not exists tb_rs_hour_msg_cnt
comment "每小时消息量趋势"
as
select
  dayinfo,
  hourinfo,
  count(*) as total_msg_cnt,
  count(distinct sender_account) as sender_usr_cnt,
  count(distinct receiver_account) as receiver_usr_cnt
from db_msg.tb_msg_etl
group by dayinfo,hourinfo;

--结果验证

select * from tb_rs_hour_msg_cnt;

3. 统计今日各地区发送消息数据量

create table if not exists tb_rs_loc_cnt
comment "今日各地区发送消息总量"
as
select
  dayinfo,
  sender_gps,
  cast(sender_lng as double) as longitude,
  cast(sender_lat as double) as latitude,
  count(*) as total_msg_cnt
from db_msg.tb_msg_etl
group by dayinfo,sender_gps,sender_lng,sender_lat;

--结果验证

select * from tb_rs_loc_cnt;

4. 统计今日发送消息和接收消息的用户数

create table if not exists tb_rs_usr_cnt
comment "今日发送消息人数、接受消息人数"
as
select
  dayinfo,
  count(distinct sender_account) as sender_usr_cnt,
  count(distinct receiver_account) as receiver_usr_cnt
from db_msg.tb_msg_etl
group by dayinfo;

--结果验证

select * from tb_rs_usr_cnt;

5. 统计今日发送消息最多的Top10用户

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

--结果验证

select * from tb_rs_susr_top10;

6. 统计今日接收消息最多的Top10用户

create table if not exists tb_rs_rusr_top10
comment "接受消息条数最多的Top10用户"
as
select
  dayinfo,
  receiver_name as username,
  count(*) as receiver_msg_cnt
from db_msg.tb_msg_etl
group by dayinfo,receiver_name
order by receiver_msg_cnt desc
limit 10;

--结果验证

select * from tb_rs_rusr_top10;  

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

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

--结果验证

select * from tb_rs_sender_phone;

8. 统计发送人的设备操作系统分布情况

create table if not exists tb_rs_sender_os
comment "发送人的OS分布"
as
select
  dayinfo,
  sender_os,
  count(distinct sender_account) as cnt
from tb_msg_etl
group by dayinfo,sender_os;

--结果验证

select * from tb_rs_sender_os;  

四、FineBI实现可视化报表

1. FineBI安装激活

1)官网下载安装包,傻瓜式安装

 官方网站:FineBI - BI数据分析软件_企业级大数据分析平台

2)配置jvm内存

3)在官网获取激活码激活产品

在接下来的页面中复制粘贴激活码到相应输入框中,产品就可以激活了。

4)设置管理员账号登录

2. FineBI配置数据源及数据准备

FineBI与Hive集成的官方文档: https://help.fanruan.com/finebi/doc-view-301.html
驱动配置
问题:如果使用FineBI连接Hive,读取Hive的数据表,需要在FineBI中添加Hive的驱动jar包
解决:将Hive的驱动jar包放入FineBI的lib目录下

1)找到提供的【Hive连接驱动】

 

2)将这些文件放入FineBI的安装目录下的:webapps\webroot\WEB-INF\lib目录中

3)插件安装

问题:我们 自己放的Hive驱动包会与FineBI自带的驱动包产生冲突 ,导致FineBI无法识别我们自己的驱动包
解决:安装FineBI官方提供的 驱动包隔离插件

 点击【确定】,重启FineBI(必须!!!

 

可以看到,这里已经存在隔离插件

4)构建连接

新建连接

配置连接

测试连接

保存连接

5)数据准备

新建分组

添加业务包

添加表

挨个选中需要图表展示的数据库表,点击【确定】

 当hive库数据有变动时,点击【业务包更新】更新数据

3. FineBI构建可视化报表

1)新建仪表盘

2)选择仪表盘样式

3)添加标题

4)文本类型

同理添加 总发送消息人数 总接收消息人数

5)地图

6)雷达图

7)柱状图

8)环饼状图

9)词汇云图

10)趋势曲线图

11)报表预览

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值