陌陌聊天数据案例分析

背景介绍和需求分析

1. 陌陌是一个聊天平台，每天都会产生大量大聊天数据，通过对聊天数据的统计分析，可以更好的构建用户画像，为用户提供更好的服务以及实现高ROI的平台运营推广，给公司的决策提供精准的数据支撑。
2. 基于Hadoop和hive实现聊天数据统计分析，构建聊天数据分析报表。
3. 统计今日总消息量、每小时消息量、发送和接受用户数、各地区发送消息数据量、发送消息和接收消息的用户数、发送消息最多的top10用户、接收消息最多的top10用户、发送人手机型号的分布情况、发送人的设备操作系统的分布情况。

基于hive数仓实现需求开发

根据聊天数据建库建表

-- 建立数据库
create database db_msg;
-- 使用数据库
use db_msg;
-- 查看已有数据库
show databases;
-- 建表
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';

加载数据

从本地文件系统加载数据到表中

-- 从本地文件系统加载数据到表中
load data local inpath '/root/hivedata/data1.tsv' into table db_msg.tb_msg_source;
load data local inpath '/root/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数据清洗

背景

在使用原始数据做分析时，有些数据的格式等规范对我们的分析不友好。为了让数据对分析具有更强的支撑作用，出现了数据清晰流程。
ETL数据清洗，即数据的抽取、转换、加载。抽取有效合法的数据、把格式调整转换一下。
ETL数据清洗是针对原始数据中出现的一些问题进行针对性的操作。
数据转换的标准：向支撑数据分析的方向调整。

分析原始数据出现的问题

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

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

-- 问题3：需求中需要精度和维度构建可视化地图，但是数据中只有经纬度一个字段，不好处理
select sender_gps from tb_msg_source limit 10;

ETL实现

-- ETl实现
-- 将select语句的结果保存到新表中
create table tb_msg_etl as
select
       *,
       substr(msg_time,0,10) as dayinfo, --获取天
       substr(msg_time,10,2) as hourinfo, --获取小时
       split(sender_gps,',')[0] as sender_lng, --获取精度
       split(sender_gps,',')[1] as sender_lat --获取纬度
from tb_msg_source
-- 过滤字段值为空的数据
where length(sender_gps) > 0;

-- 验证ETL处理之后的结果
select dayinfo,hourinfo,sender_lng,sender_lat from tb_msg_etl limit 10;

需求指标统计

思路

1. 正确解读需求
2. 确定要查询的表——>from 表
3. 找出分析的维度——>group by 分组的字段
4. 找出计算的指标——>聚合的字段
5. 其他细节点(过滤、排序等)

需求开发

------需求指标统计分析
-- 需求1：统计今日总消息量
create table if not exists tb_rs_total_msg_cnt
comment '每日消息总量'
as -- 以查询结果建表
select
    dayinfo,
    count(*) as total_msg_cnt -- 指标
from 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_user_cnt, -- 每小时发送用户数
    count(distinct receiver_account) as receiver_user_cnt--每小时接收用户数
from 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(*)
from tb_msg_etl
group by dayinfo, sender_gps, cast(sender_lng as double), cast(sender_lat as double); -- 维度：今日、地区、经纬度

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 tb_msg_etl
group by dayinfo;

select * from tb_rs_usr_cnt;

-- 需求5：统计今日发送消息最多的Top10用户
create table if not exists tb_rs_usr_top10
comment '今日发送消息最多的top10用户'
as
select
    dayinfo,
    sender_name as username,
    count(*) as sender_msg_cnt
from tb_msg_etl
group by dayinfo, sender_name
order by sender_msg_cnt desc --按发送消息降序的顺序排列
limit 10;

select * from tb_rs_usr_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 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 '发送人设备操作系统分布情况'
as
select
    dayinfo,
    sender_os,
    count(distinct sender_account) cnt
from tb_msg_etl
group by dayinfo, sender_os;

select * from tb_rs_sender_os;

基于FineBI实现可视化报表

配置流程

1. 官网下载、注册用户、获取激活码
2. 给Fine BI添加驱动、并安装hive隔离插件隔离驱动
3. 添加数据连接
4. 数据准备，添加分组和业务包
5. 使用仪表板构建可视化报表

构建可视化报表

1. 制作方法：选定图标类型后，把指定的维度和指标拖入指定的位置即可
2. 最多使用：图标类型、图标属性、组件样式、维度、指标

总结

1. 一些基础：
   到这里初步学习了大数据的一部分技术栈：Hadoop的HDFS、Yarn、MapReduce，Hive，了解了这些组件的原理和安装配置。
   在这之前，也大致熟悉了一些基础知识：Linux操作系统的一些指令、sql语言的一些语法。
2. 业务场景中的重点
   在Hive中写一些sql的建库、建表、查询语句
3. 数据的可视化
   使用Fine BI进行了数据的可视化，将hive中统计好的数据制作成可视化的报表。