大数据Hadoop教程-学习笔记06【Hadoop生态综合案例：陌陌聊天数据分析】

• 视频教程：哔哩哔哩网站：黑马大数据Hadoop入门视频教程，总时长：14:22:04
• 教程资源：https://pan.baidu.com/s/1WYgyI3KgbzKzFD639lA-_g，提取码：6666

1. 【P001-P017】大数据Hadoop教程-学习笔记01【大数据导论与Linux基础】【17p】
2. 【P018-P037】大数据Hadoop教程-学习笔记02【Apache Hadoop、HDFS】【20p】
3. 【P038-P050】大数据Hadoop教程-学习笔记03【Hadoop MapReduce与Hadoop YARN】【13p】
4. 【P051-P068】大数据Hadoop教程-学习笔记04【数据仓库基础与Apache Hive入门】【18p】
5. 【P069-P083】大数据Hadoop教程-学习笔记05【Apache Hive DML语句与函数使用】【15p】
6. 【P084-P096】大数据Hadoop教程-学习笔记06【Hadoop生态综合案例：陌陌聊天数据分析】【13p】

01【陌陌聊天数据分析案例需求】

P084【01-课程内容的大纲与学习目标】

目录

1. 陌陌聊天数据分析案例需求
2. 基于Hive数仓使用SQL实现需求开发
3. 基于FineBI实现可视化报表

学习目标

1. 了解案例背景、目标需求、原始数据内容
2. 掌握Hive数仓建库建表、ETL操作
3. 掌握Hive SQL查询统计分析的基本操作
4. 知道FineBI基本报表图形的使用与构建

P085【02-陌陌聊天数据分析案例需求】

数据内容

1. 数据大小：两个文件共14万条数据
2. 列分隔符：制表符 \t
3. 数据字典及样例数据

02【基于Hive数仓使用SQL实现需求开发】

P086【03-基于Hive数仓实现需求开发--建库建表与加载数据】

node1

start-all.sh

nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore

nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service hiveserver2 &

jps

node3

/export/server/apache-hive-3.1.2-bin/bin/beeline

! connect jdbc:hive2://node1:10000

root

--------------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、加载数据-------------------
--上传数据文件到node1服务器本地文件系统（HS2服务所在机器）
--shell：mkdir -p /root/hivedata

--加载数据到表中
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;

P087【04-基于Hive数仓实现需求开发--ETL需求分析与技术支撑】

ETL，是英文Extract-Transform-Load的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。ETL一词较常用在数据仓库，但其对象并不限于数据仓库。

CTAS：Create table …… as select ……

create table t_test as select msg_time, sender_name from tb_msg_source limit 5;

select * from t_test;

P088【05-基于Hive数仓实现需求开发--ETL SQL实现】

--------------4、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实现

select msg_time from tb_msg_source limit 2;

select msg_time, substr(msg_time, 1, 10) as dayinfo
from tb_msg_source limit 2;

select msg_time, substr(msg_time, 1, 10) as dayinfo, substr(msg_time, 12, 2) as hourinfo
from tb_msg_source limit 2;

select msg_time, substr(msg_time, 1, 10) as dayinfo, substr(msg_time, 12, 2) as hourinfo,
       sender_gps, split(sender_gps, ",")[0] as sender_lng, split(sender_gps, ",")[1] as sender_lat
from tb_msg_source limit 2;

select msg_time, substr(msg_time, 1, 10) as dayinfo, substr(msg_time, 12, 2) as hourinfo,
       sender_gps, split(sender_gps, ",")[0] as sender_lng, split(sender_gps, ",")[1] as sender_lat
from tb_msg_source where length(sender_gps) > 0 --过滤为空的非法数据
limit 2;

--如果表已存在就删除
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;

P089【06-基于Hive数仓实现需求开发--sql编写思路与指标计算part1】

--------------5、需求指标统计分析-------------------
--需求：统计今日总消息量
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;--结果验证

--需求：统计今日每小时消息量、发送和接收用户数
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;--结果验证

P090【07-基于Hive数仓实现需求开发--指标计算part2】

--需求：统计今日各地区发送消息数据量
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; --结果验证

select sender_gps, sender_lng, sender_lat from tb_msg_etl limit 10;

--需求：统计今日发送消息和接收消息的用户数
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; --结果验证

P091【08-基于Hive数仓实现需求开发--指标计算part3】

思路

1. 正确解读业务需求，避免歧义
2. 确定待查询的数据表-->from 表
3. 找出分析的维度-->group by 分组的字段
4. 找出计算的指标-->聚合的字段
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; --结果验证

--需求：统计今日接收消息最多的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; --结果验证

--需求：统计发送人的手机型号分布情况
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; --结果验证

--需求：统计发送人的设备操作系统分布情况
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; --结果验证

03【FineBI实现可视化报表】

P092【09-基于FineBI实现可视化报表--FineBI介绍与安装】

FineBI的介绍及安装

FineBI的介绍：https://www.finebi.com/

FineBI 是帆软软件有限公司推出的一款商业智能（Business Intelligence）产品。FineBI 是定位于自助大数据分析的BI工具，能够帮助企业的业务人员和数据分析师，开展以问题导向的探索式分析。

P093【10-基于FineBI实现可视化报表--配置数据源及数据准备】

P094【11-基于FineBI实现可视化报表--标题配置与文本框使用】

P095【12-基于FineBI实现可视化报表--地图、雷达图、柱状图构建】

P096【13-基于FineBI实现可视化报表--饼图、词云、趋势图构建】

总结：了解FineBI的功能及其基本使用，实现可视化报表的构建。