虚拟机环境搭建及准备：三台虚拟机并配置hosts文件，搭建Hadoop集群环境并在其中一台虚拟机上安装部署Mysql和Hive，scp分发Hive并用另外两台虚拟机模拟远程连接HiveServer2
数据来源：社交平台聊天数据
环境检测：
检测集群环境保证hadoop集群和hive服务正常
连接至HiveServer2服务器以进行后续操作：
beeline方式：
beeline使用hive
datagrip工具：注意更改对应版本的jdbc驱动和在本地主机保存host与ip映射
借助datagrip使用hive
HIVE数仓建表以及数据清洗ETL
建库
-- 建库
drop database if exists momo cascade;
create database momo;
show databases;
use momo;
建表
-- 建表
drop table if exists momo.MessageSource;
create table momo.MessageSource
(
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';
上传数据
到HDFS中后通过移动到Hive中
上传数据至HDFS中
shell上传到hadoop hdfs上
将HDFS数据加载至Hive中
-- HDFS数据加载
load data inpath "/momo/data/data1.tsv" into table MessageSource;
load data inpath "/momo/data/data2.tsv" into table MessageSource;
直接从local上传到Hive中
-- Local数据加载
load data local inpath "/opt/module/data/data1.tsv" into table MessageSource;
load data local inpath "/opt/module/data/data2.tsv" into table MessageSource;ETL数据清洗
技术点分析：
CTAS：将复杂属性数据进行处理并建表，易于获取其中的有用字段
CREATE TABLE tablename AS SELECT select_expr
步骤：
提取出发送信息的时间字段信息和gps字段信息
-- ETL数据清洗
-- 提取信息的时间和GPS信息
drop table if exists etl_time_gps;
create table etl_time_gps as
select *,
substr(msg_time, 0, 10) as dayinfo,
substr(msg_time, 12, 2) as hourinfo,
split(sender_gps, ",")[0] as sender_longitude,
split(sender_gps, ",")[1] as sender_latitude
from messagesource
where length(sender_gps) > 0;
需求统计指标
统计今日总消息量
统计今日每小时消息量、发送和接收用户数
统计今日各地区发送消息数据量
统计今日发送消息和接收消息的用户数
统计今日发送消息最多的Top10用户
统计今日接收消息最多的Top10用户
统计发送人的手机型号分布情况
统计发送人的设备操作系统分布情况
-- 统计今日总消息量
create table if not exists total_msg_count comment "今日消息总量" as
select dayinfo, count(*) as totol_msg_number
from etl_time_gps
group by dayinfo;
-- 统计今日每小时消息量、发送和接收用户数
create table if not exists hour_msg_count comment "今日每小时消息量、发送和接收用户数" as
select dayinfo,
hourinfo,
count(*) as hour_total_msg_number,
count(distinct sender_account) as sender_count_number,
count(distinct receiver_account) as receiver_count_number
from etl_time_gps
group by dayinfo, hourinfo;
select *
from hour_msg_count;
-- 统计今日各地区发送消息数据量
create table if not exists location_msg_count comment "今日各地区发送消息数据量" as
select dayinfo,
sender_gps,
cast(sender_longitude as double) as longtitude,
cast(sender_latitude as double) as latitude,
count(*) as gps_count_number
from etl_time_gps
group by dayinfo, sender_gps, sender_longitude, sender_latitude;
select *
from location_msg_count
limit 10;
-- 统计今日发送消息和接收消息的用户数
create table if not exists total_user_count comment "今日发送消息和接收消息的用户数" as
select dayinfo,
count(distinct sender_account) as sender_count_number,
count(distinct receiver_account) as receiver_count_number
from etl_time_gps
group by dayinfo;
select *
from total_user_count;
-- 统计今日发送消息最多的Top10用户
create table if not exists top10_sender_info comment "今日发送消息最多的Top10用户" as
select dayinfo,
sender_name as username,
count(*) as sender_info_count_number
from etl_time_gps
group by dayinfo, sender_name
order by sender_info_count_number desc
limit 10;
select *
from top10_sender_info;
-- 统计今日接收消息最多的Top10用户
create table if not exists top10_receiver_info comment "今日接收消息最多的Top10用户" as
select dayinfo,
receiver_name as username,
count(*) as receiver_info_count_number
from etl_time_gps
group by dayinfo, receiver_name
order by receiver_info_count_number desc
limit 10;
select *
from top10_receiver_info;
-- 统计发送人的手机型号分布情况
create table if not exists sender_phonetype_info comment "发送人的手机型号分布情况" as
select dayinfo,
sender_phonetype,
count(distinct sender_account) as phone_type_count_number
from etl_time_gps
group by dayinfo, sender_phonetype;
select * from sender_phonetype_info;
-- 统计发送人的设备操作系统分布情况
create table if not exists sender_os_info comment "发送人的手机型号分布情况" as
select dayinfo,
sender_os,
count(distinct sender_account) as phone_os_count_number
from etl_time_gps
group by dayinfo, sender_os;
select * from sender_os_info
FineBI实现数据可视化报表
安装FineBI，官网领取激活码，官方安装文档写的很详细（
FineBI与Hive驱动集成配置：Hadoop Hive数据连接- FineBI帮助文档 FineBI帮助文档 (fanruan.com)
FineBI连接Hive
Hive相关信息配置
左侧数据准备列表中添加新的业务案例和业务包，选择刚刚配置好的hive驱动连接获取数据库表
FineBI连接Hive获取数据表
记得进行业务包更新把数据同步过来才能进行报表制作
更新同步数据，标题为建表时的comment
正式构建报表：
创建仪表盘：
创建仪表盘位置
根据教程和更新后的表数据自由发挥，下图为模板：
数据报表样例
其他问题：
DataGrip连接Hive服务的时候必须保证Hadoop集群是正常开启的，同时Hive服务也应该开启，通过jps查看每台虚拟机上相关的Hadoop组件和shell命令：netstat -anp|grep 10000查看Hive服务是否开启和正常运作。此外，由于Hive是基于Hadoop和Mysql的，需要先打开Hadoop集群和Mysql服务后才能启动Hive，通过systemctl status mysqld查看本机上的Mysql服务状态。
shell命令示例
可以使用nohup指令开启Hive能够在后台运行保证前台还能进行其他的操作，具体的日志信息会被保存在bin目录下的nohup.out文件中，具体shell命令如下：
-- Hive2的启动方式，需要先启动metastore再启动hive server2
cd $HIVE_HOME/bin
nohup ./hive --service metastore &
nohup ./hive --service hiveserver2 &
运行HiveSQL可能反应速度比较慢，原因是涉及到复杂的查询语句，Hive底层是将SQL语句转化为mapreduce程序并将任务提交到集群上处理，mapreduce任务运行一系列过程啥速度懂得都懂，不要因为慢就中断查询动作，后台关闭也需要时间。此外可以通过Hadoop集群上设置了ResourceManager主机的8088端口（MapReduce任务执行查看默认端口，如果更改了Hadoop配置文件以配置文件上设置的端口为主）查看由HiveSQL语句转换的mapreduce程序的运行状态。
mapreduce任务执行端口
如果运行HiveSQL出线连接服务失败或者拒绝连接的情况，建议检查Hadoop集群状态和Hive的两个Jar是否成功运行：
Hadoop集群状态：jps命令查看集群上NameNode，DataNode等是否挂掉了，或者通过Hadoop的NameNode的HTTP UI端口9870查看节点状态，毕竟Hive的数据是存储在HDFS上，HDFS里面就NameNode和DataNode。
DataNode HTTP UI
Hive的两个Jar指的是metastore和hiveserver2，也可通过jps命令查看，下图示例jpsall为自写的Shell脚本，能够一次性jps三台虚拟机上的服务来查看每台虚拟机状态保证整个集群正常。
查看每台主机上的服务情况
