案例:陌陌聊天数据分析

 背景分析:     

        陌陌作为聊天平台每天都会有大量的用户在线,会出现大量的聊天数据,通过对 聊天数据的统计分析 ,可以更好的 对用户构建精准的 用户画像 ,为用户提供更好的服务以及实现 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)报表预览

### 使用 FineBI 进行陌陌数据分析报表可视化 #### 构建数据连接 为了在FineBI中进行有效的数据分析,首先需要建立与Hive数据库的连接。这一步骤确保了能够访问存储在Hive中的结构化聊天记录和其他相关信息[^2]。 ```sql CREATE EXTERNAL TABLE IF NOT EXISTS db_msg.tb_msg_etl ( msg_id STRING, user_id STRING, receiver_id STRING, content STRING, dayinfo STRING, hourinfo STRING, sender_lng DOUBLE, sender_lat DOUBLE ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE; ``` 此SQL语句创建了一个外部表`tb_msg_etl`用于后续的数据处理和分析工作[^3]。 #### 数据准备与预处理 通过ETL过程已经完成了对原始聊天数据的基本清理和转换操作,包括但不限于时间戳解析成日期和小时字段、GPS坐标拆分等。这些准备工作极大地简化了之后在FineBI内的数据探索流程[^1]。 #### 创建可视化仪表板 一旦确认数据源无误并成功导入至FineBI平台内,则可以着手设计直观易懂的信息展示界面: - **选择图表类型**:依据具体业务场景选取最合适的视觉表达形式,比如柱状图适合比较不同时间段的消息量变化趋势;地图可用于呈现发送者地理位置分布情况。 - **定义维度与度量**:设置好各个轴所对应的属性值以及想要聚合统计的关键绩效指标(KPI),例如按日汇总每个小时段内的活跃人数比例或是特定区域内用户的互动频率等。 - **应用筛选器**:为了让最终产出物更具灵活性及交互性,在页面上添加必要的参数控件让用户可以根据个人兴趣定制查看范围,像限定某一天或某个城市的交流概况。 - **优化布局样式**:调整组件排列顺序使整体构架更加紧凑合理,并适当运用颜色区分重要程度不同的区域板块来增强可读性和吸引力。 #### 实现案例效果示例 假设现在要制作一张反映各城市间每日平均在线时长对比的地图视图,那么可以在FineBI里按照如下方式配置: - 设置地理编码为经纬度列(`sender_lng`, `sender_lat`) - 计算每个地点的日均停留分钟数作为衡量标准 - 利用地形填充色阶映射热度等级差异 这样不仅能让管理层快速把握全局态势特征,也能帮助运营团队定位潜在增长点从而制定精准营销策略。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值