陌陌聊天数据分析 （一）

陌陌聊天数据分析（一）

目标

  • 基于Hadoop和Hive实现聊天数据统计分析，构建聊天数据分析报表

需求

  • 统计今日总消息量
  • 统计今日每小时消息量，发送和接收用户数量
  • 统计今日各地区发送消息数据量
  • 统计今日发送消息和接收消息用户数
  • 统计今日发送消息最多的用户前几名
  • 统计今日接收消息最多的用户前几名
  • 统计发送人手机型号分布情况
  • 统计发送人设备系统分布情况

数据来源

  • 聊天业务系统导出2021/11/01一天24小时用户聊天数据，以TSV文本形式存储在文件中
    • 数据大小：两个文件共14万条数据
    • 列分隔符：\t

数据集及所需文件

  • 链接：https://pan.baidu.com/s/1ToTanDrFRhAVsFTb2uclFg
    提取码：rkun

🥇基于Hive数仓实现需求开发

⚽建库建表 加载数据

  • 建库建表
--创建数据库
create database db_msg;
--切换数据库
use db_msg;

--建表
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';
  • 加载数据
#上传数据到node1服务器本地文件系统(HS2服务所在机器)
[root@node1 hivedata]# pwd
/root/hivedata
[root@node1 hivedata]# ll
total 54104
-rw-r--r-- 1 root root 28237023 Jun 13 20:24 data1.tsv
-rw-r--r-- 1 root root 27161148 Jun 13 20:24 data2.tsv


--加载数据入表
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 5;

在这里插入图片描述

⚾ETL数据清洗

数据问题

  • 当前数据，一些数据字段为空，不是合法数据。
  • 需求需要统计每天每个小时消息量，但数据中没有天和小时字段，只有整体时间字段，不好处理。
  • 需求中，GPS对经纬度在同一字段，不好处理。

ETL需求

  • 对字段为空的不合法数据进行过滤
    • where过滤
  • 通过时间字段构建天和小时字段
    • substr函数
  • 从GPS经纬度提取经纬度
    • split函数
  • 将ETL以后的结果保存到一张新的Hive表中
    • create table …as 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;

select
    msg_time,dayinfo,hourinfo,sender_gps,sender_lng,sender_lat
from db_msg.tb_msg_etl
limit 5;
--查询数据

在这里插入图片描述

🏀需求指标SQL

  • 解读需求
  • 确定待查询数据表 from
  • 分析维度 group by
  • 找出计算指标 聚合
  • 细节 过滤 排序

  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 ;

    +------------------------------+------------------------------------+
| tb_rs_total_msg_cnt.dayinfo  | tb_rs_total_msg_cnt.total_msg_cnt  |
+------------------------------+------------------------------------+
| 2021-11-01                   | 139062                             |
+------------------------------+------------------------------------+

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

    create table 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 limit 5;

    +-----------------------------+------------------------------+-----------------------------------+------------------------------------+--------------------------------------+
| tb_rs_hour_msg_cnt.dayinfo  | tb_rs_hour_msg_cnt.hourinfo  | tb_rs_hour_msg_cnt.total_msg_cnt  | tb_rs_hour_msg_cnt.sender_usr_cnt  | tb_rs_hour_msg_cnt.receiver_usr_cnt  |
+-----------------------------+------------------------------+-----------------------------------+------------------------------------+--------------------------------------+
| 2021-11-01                  | 00                           | 4349                              | 3520                               | 3558                                 |
| 2021-11-01                  | 01                           | 2892                              | 2524                               | 2537                                 |
| 2021-11-01                  | 02                           | 882                               | 842                                | 838                                  |
| 2021-11-01                  | 03                           | 471                               | 463                                | 460                                  |
| 2021-11-01                  | 04                           | 206                               | 202                                | 205                                  |
+-----------------------------+------------------------------+-----------------------------------+------------------------------------+--------------------------------------+

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

    create table 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 tb_msg_etl
group by dayinfo, sender_gps, sender_lng,sender_lat;


select * from tb_rs_loc_cnt limit 5;


    +------------------------+---------------------------+--------------------------+-------------------------+------------------------------+
| tb_rs_loc_cnt.dayinfo  | tb_rs_loc_cnt.sender_gps  | tb_rs_loc_cnt.longitude  | tb_rs_loc_cnt.latitude  | tb_rs_loc_cnt.total_msg_cnt  |
+------------------------+---------------------------+--------------------------+-------------------------+------------------------------+
| 2021-11-01             | 100.297355,24.206808      | 100.297355               | 24.206808               | 1397                         |
| 2021-11-01             | 100.591712,24.004148      | 100.591712               | 24.004148               | 1406                         |
| 2021-11-01             | 101.62196,36.782187       | 101.62196                | 36.782187               | 1439                         |
| 2021-11-01             | 102.357852,23.801165      | 102.357852               | 23.801165               | 1399                         |
| 2021-11-01             | 102.357852,25.682909      | 102.357852               | 25.682909               | 1431                         |
+------------------------+---------------------------+--------------------------+-------------------------+------------------------------+

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

    create table 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 ;

    +------------------------+-------------------------------+---------------------------------+
| tb_rs_usr_cnt.dayinfo  | tb_rs_usr_cnt.sender_usr_cnt  | tb_rs_usr_cnt.receiver_usr_cnt  |
+------------------------+-------------------------------+---------------------------------+
| 2021-11-01             | 10008                         | 10005                           |
+------------------------+-------------------------------+---------------------------------+

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

    create table 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;

    +---------------------------+----------------------------+----------------------------------+
| tb_rs_susr_top10.dayinfo  | tb_rs_susr_top10.username  | tb_rs_susr_top10.sender_msg_cnt  |
+---------------------------+----------------------------+----------------------------------+
| 2021-11-01                | 茹鸿晖                        | 1466                             |
| 2021-11-01                | 卢高达                        | 1464                             |
| 2021-11-01                | 犁彭祖                        | 1460                             |
| 2021-11-01                | 沐范                         | 1459                             |
| 2021-11-01                | 夫潍                         | 1452                             |
| 2021-11-01                | 烟心思                        | 1449                             |
| 2021-11-01                | 称子瑜                        | 1447                             |
| 2021-11-01                | 麻宏放                        | 1442                             |
| 2021-11-01                | 邴时                         | 1439                             |
| 2021-11-01                | 养昆颉                        | 1431                             |
+---------------------------+----------------------------+----------------------------------+

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

    create table 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 limit 3;

    +---------------------------+----------------------------+------------------------------------+
| tb_rs_rusr_top10.dayinfo  | tb_rs_rusr_top10.username  | tb_rs_rusr_top10.receiver_msg_cnt  |
+---------------------------+----------------------------+------------------------------------+
| 2021-11-01                | 畅雅柏                        | 1539                               |
| 2021-11-01                | 春纯                         | 1491                               |
| 2021-11-01                | 邝琨瑶                        | 1469                               |
+---------------------------+----------------------------+------------------------------------+

  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 limit 3;

    +-----------------------------+--------------------------------------+-------------------------+
| tb_rs_sender_phone.dayinfo  | tb_rs_sender_phone.sender_phonetype  | tb_rs_sender_phone.cnt  |
+-----------------------------+--------------------------------------+-------------------------+
| 2021-11-01                  | Apple iPhone 10                      | 6749                    |
| 2021-11-01                  | Apple iPhone 11                      | 3441                    |
| 2021-11-01                  | Apple iPhone 7                       | 2424                    |
+-----------------------------+--------------------------------------+-------------------------+

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

    create table 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;

    +--------------------------+----------------------------+----------------------+
| tb_rs_sender_os.dayinfo  | tb_rs_sender_os.sender_os  | tb_rs_sender_os.cnt  |
+--------------------------+----------------------------+----------------------+
| 2021-11-01               | Android 5.1                | 5750                 |
| 2021-11-01               | Android 6                  | 8514                 |
| 2021-11-01               | Android 6.0                | 9398                 |
| 2021-11-01               | Android 7.0                | 9181                 |
| 2021-11-01               | Android 8.0                | 8594                 |
| 2021-11-01               | IOS 10.0                   | 1289                 |
| 2021-11-01               | IOS 12.0                   | 8102                 |
| 2021-11-01               | IOS 9.0                    | 8760                 |
+--------------------------+----------------------------+----------------------+

​

🎖️FineBI实现可视化报表

官网

https://www.finebi.com/

🏐配置数据源及数据准备

官方文档

https://help.fanruan.com/finebi/doc-view-301.html

  • 使用FineBI连接Hive，读取Hive数据表，需要在FineBI中添加Hive驱动jar包

  • 将Hive驱动jar包放入FineBI的lib目录下

  • 找到提供文件的HiveConnectDrive

在这里插入图片描述

  • 放入安装路径下的 webapps\webroot\WEB-INF\lib

在这里插入图片描述

插件安装

  • 我们自己Hive驱动包会与FineBI自带驱动包冲突，导致FineBI无法识别我们自己的驱动
  • 安装FineBI官方提供驱动包隔离插件

隔离插件：fr-plugin-hive-driver-loader-3.0.zip

  • 安装插件

    在这里插入图片描述

  • 重启FineBI

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/36964.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

机器学习 day25（softmax在神经网络模型上的应用，提高数据精度的方法）

机器学习 day25（softmax在神经网络模型上的应用，提高数据精度的方法）

输出层采用softmax 在识别手写数字的模型中，预测y只有两个结果，所以输出层采用sigmoid激活函数且只有一个神经元。若预测y有10个结果（0-9），该模型的前向传播计算方式与识别数字的模型完全相同，即隐藏层的…
阅读更多...
符号化的正确姿势

符号化的正确姿势

GUI方式 将 .ips crash report 文件拖放到 Xcode > Window > Devices and Simulators > View Device Logs中, 然后导出 .crash 符号化文件. 使用条件: crash report 对应的 Archive 包是在本机构建的 symbolicatecrash symbolicatecrash 是一个 exec (可执行文件), …
阅读更多...
Stepper, Slider 的使用

Stepper, Slider 的使用

1. Stepper 步进器的使用 1.1 实现 /// 步进器 /加减控件 struct StepperBootcamp: View {State var stepperValue: Int 10State var widthIncrement: CGFloat 0var body: some View {VStack {Stepper("Stepper: \(stepperValue)", value: $stepperValue).padding…
阅读更多...
【MATLAB第53期】基于MATLAB的TSK模糊神经网络时间序列预测模型，含短期预测未来功能

【MATLAB第53期】基于MATLAB的TSK模糊神经网络时间序列预测模型，含短期预测未来功能

【MATLAB第53期】基于MATLAB的TSK模糊神经网络时间序列预测模型，含短期预测未来功能 一、效果展示 二、数据设置 数据采用一列数据滑动窗口设置为5 ，可自行设置70%训练30%测试预测未来值为10 ，可自行设置，控制10以内 三、模型…
阅读更多...
Spring MVC相关注解运用 —— 中篇

Spring MVC相关注解运用 —— 中篇

目录 一、RESTful风格支持 1.1 RESTful风格介绍 1.2 postman使用 二、PathVariable 2.1 实例程序 2.2 测试结果 三、PostMapping、GetMapping、PutMapping、DeleteMapping 四、HiddenHttpMethodFilter 4.1 在web.xml配置过滤器 4.2 控制器方法 4.3 JSP页面 4.4 测…
阅读更多...
论文笔记--SentEval: An Evaluation Toolkit for Universal Sentence Representations

论文笔记--SentEval: An Evaluation Toolkit for Universal Sentence Representations

论文笔记--SentEval: An Evaluation Toolkit for Universal Sentence Representations 1. 文章简介2. 文章概括3 文章重点技术3.1 evaluation pipeline3.2 使用 4. 代码4.1 数据下载4.2 句子嵌入4.3 句子嵌入评估 5. 文章亮点6. 原文传送门7. References 1. 文章简介 标题&…
阅读更多...
96、基于STM32单片机的温湿度DHT11 烟雾火灾报警器蓝牙物联网APP远程控制设计(程序+原理图+任务书+参考论文+开题报告+流程图+元器件清单等)

96、基于STM32单片机的温湿度DHT11 烟雾火灾报警器蓝牙物联网APP远程控制设计(程序+原理图+任务书+参考论文+开题报告+流程图+元器件清单等)

单片机及温湿度、烟雾传感器是烟雾报警器系统的两大核心。单片机好比一个桥梁，联系着传感器和报警电路设备。近几年来，单片机已逐步深入应用到工农业生产各部门及人们生活的各个方面。各种类型的单片机也根据社会的需求而开发出来。单片机是器件级计算机…
阅读更多...
Redis - 附近商铺、用户签到、UV统计

Redis - 附近商铺、用户签到、UV统计

文章目录 附近商铺、用户签到、UV统计一、附近商铺1.1 GEO数据结构1.2 导入店铺数据到GEO1.3 实现附近商户功能 二、用户签到2.1 BitMap2.2 签到功能2.3 统计连续签到2.3.1 分析2.3.2 代码实现 三、UV统计3.1 HyperLogLog用法3.2 测试百万数据的统计 附近商铺、用户签到、UV统计…
阅读更多...
LRU 缓存

LRU 缓存

题目链接 LRU 缓存 题目描述 注意点 如果插入操作导致关键字数量超过 capacity ，则应该 逐出 最久未使用的关键字函数 get 和 put 必须以 O(1) 的平均时间复杂度运行 解答思路 如果想以O(1)的速度进行get，则需要将对应的key、value存到map中如果想…
阅读更多...
李子转债上市价格预测

李子转债上市价格预测

李子转债 基本信息 转债名称：李子转债，评级：AA，发行规模：6.0亿元。 正股名称：李子园，今日收盘价：18.06元，转股价格：19.47元。 当前转股价值 转债面值 / 转股…
阅读更多...
RabbitMQ笔记--消息中间件，rabbitmq安装及简单使用

RabbitMQ笔记--消息中间件，rabbitmq安装及简单使用

1.消息中间件 消息：指在应用间传送的数据。 消息队列中间件：指利用高效可靠的消息传递机制进行与平台无关的数据交流，并基于数据通信来进行分布式系统的集成。通过提供消息传递和消息排队模型，可以在分布式环境下扩展进程间的通…
阅读更多...
Elasticsearch【全文检索、倒排索引、应用场景、对比Solr、数据结构】(一)-全面详解（学习总结---从入门到深化）

Elasticsearch【全文检索、倒排索引、应用场景、对比Solr、数据结构】(一)-全面详解（学习总结---从入门到深化）

目录 Elasticsearch介绍_全文检索 Elasticsearch介绍_倒排索引 Elasticsearch介绍_Elasticsearch的出现 Elasticsearch介绍_Elasticsearch应用场景 Elasticsearch介绍_Elasticsearch对比Solr Elasticsearch介绍_Elasticsearch数据结构 Elasticsearch介绍_全文检索 Elasti…
阅读更多...
UILabel左上角对齐

UILabel左上角对齐

设计有个需求，需要文字两行显示，一行的时候左上角对齐，比较常见的需求。 老的办法一般来说是根据宽计算好frame大小，然后重新设置frame。不过感觉这种方式比较麻烦，想了想能不能通过约束来完成这个事情呢。 本着这个思…
阅读更多...
（论文翻译）PRUNING FILTER IN FILTER《滤波器中的剪枝滤波器》

（论文翻译）PRUNING FILTER IN FILTER《滤波器中的剪枝滤波器》

公式不清楚的地方请对照英文原文进行查看：原文链接 ABSTRACT 剪枝已成为现代神经网络压缩和加速的一种非常有效的技术。现有的剪枝方法可分为两大类:滤波器剪枝(FP)和权重剪枝(WP)。与WP相比，FP在硬件兼容性方面胜出，但在压缩比方面失败。为了…
阅读更多...
springboot开发PC端桌面应用

springboot开发PC端桌面应用

一、需求描述： 1、要求桌面能在window、Linux和macos系统上运行 2、用户自定义数据筛选策略，策略可通过excel导入导出 3、选择多个excel文件通过策略过滤生成新的excel 二、技术选型及集成环境配置： 1、PC端跨平台直接选用javafx来作为桌…
阅读更多...
SpringBoot + Vue前后端分离项目实战 || 四：用户管理功能实现

SpringBoot + Vue前后端分离项目实战 || 四：用户管理功能实现

系列文章： SpringBoot Vue前后端分离项目实战 || 一：Vue前端设计 SpringBoot Vue前后端分离项目实战 || 二：Spring Boot后端与数据库连接 SpringBoot Vue前后端分离项目实战 || 三：Spring Boot后端与Vue前端连接 SpringBoot V…
阅读更多...
从零开始制作一个Web蜜罐扫描器(5)

从零开始制作一个Web蜜罐扫描器(5)

从零开始制作一个Web蜜罐扫描器(3)_luozhonghua2000的博客-CSDN博客 打开一个蜜罐: 查看源码: 这个./js/portraitjs非常引人注入，点进去看一下 很明显是被混淆过了，结合语义来理解，这是portrait=画像，那么可以大胆猜测这段ison是黑客画像用的.猜测了就要进行验证，这里在…
阅读更多...
Kafka request.log中RequestQueueTimeMs、LocalTimeMs、RemoteTimeMs、ThrottleTimeMs、含义

Kafka request.log中RequestQueueTimeMs、LocalTimeMs、RemoteTimeMs、ThrottleTimeMs、含义

Kafka request.log中RequestQueueTimeMs、LocalTimeMs、RemoteTimeMs、ThrottleTimeMs、含义 要理解各个延时项的含义，必须从Kafka收到TCP请求、处理请求到返回TCP包整个流程开始梳理 RequestQueueTimeMs Processor 执行processNewResponses() 方法，不…
阅读更多...
软件工程师，学习下JavaScript ES6新特性吧

软件工程师，学习下JavaScript ES6新特性吧

概述 作为一名软件工程师，不管你是不是前端开发的岗位，工作中或多或少都会用到一点JavaScript。JavaScript是大家所了解的语言名称，但是这个语言名称是Oracle公司注册的商标。JavaScript的正式名称是ECMAScript。1996年11月，JavaS…
阅读更多...
RT-Thread 互补滤波器 (STM32 + 6 轴 IMU)

RT-Thread 互补滤波器 (STM32 + 6 轴 IMU)

作者：wuhanstudio 原文链接：https://zhuanlan.zhihu.com/p/611568999 最近在看无人驾驶的 Prediction 部分，可以利用 EKF (Extended Kalman Filter) 融合不同传感器的数据，例如 IMU, Lidar 和 GNSS，从而给出更加准确的…
阅读更多...
最新文章

Copyright @ 2022~2023