Hive综合实例——陌陌聊天数据分析_mm聊天 hive分析

基于Hive数仓实现需求开发

-- 本地(指hive服务所在的主机)加载数据
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 count(*) from db_msg.tb_msg_source;
select * from db_msg.tb_msg_source limit 10;


------------- ETL --------------

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

--如果表已存在就删除
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; --过滤字段为空的数据
-- 检查数据
select * from db_msg.tb_msg_etl limit 3;


--------------- 需求指标统计分析 ----------------
--需求：统计今日总消息量
drop table if exists tb_rs_total_msg_cnt;
create table tb_rs_total_msg_cnt
comment "今日总消息量"
as
select
    dayinfo, count(*) total_msg_cnt
from tb_msg_etl
group by dayinfo;
select * from tb_rs_total_msg_cnt; -- 验证

--需求：统计今日每小时消息量、发送和接收用户数
drop table if exists tb_rs_hour_msg_cnt;
create table tb_rs_hour_msg_cnt
comment "每小时消息量趋势"
as
    select
        dayinfo,
        hourinfo,
        count(*) total_msg_cnt,
        count(distinct sender_account) sender_usr_cnt,
        count(distinct receiver_account) receiver_usr_cnt
    from tb_msg_etl
    group by dayinfo, hourinfo;
select * from tb_rs_hour_msg_cnt;

--需求：统计今日各地区发送消息数据量
drop table if exists tb_rs_loc_cnt;
create table tb_rs_loc_cnt
comment "今日各地区发送消息总量"
as
    select
        dayinfo,
        sender_gps,
        cast(sender_lng as double) longtitude,
        cast(sender_lat as double) latitude,
        count(*) total_msg_cnt
    from tb_msg_etl
    group by dayinfo,sender_gps,sender_lng,sender_lat;

select * from tb_rs_loc_cnt; --结果验证

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


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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

基于FineBI实现可视化报表

配置数据源及数据准备

• FineBI与Hive集成的官方文档:https://help.fanruan.com/finebi/doc-view-301.html
• 驱动配置
  • 问题：如果使用FineBI连接Hive，读取Hive的数据表，需要在FineBI中添加Hive的驱动jar包
  • 解决：将Hive的驱动jar包放入FineBI的lib（webapps\webroot\WEB-INF\lib）目录下
• 插件安装
  • 问题：我们自己放的Hive驱动包会与FineBI自带的驱动包产生冲突，导致FineBI无法识别我们自己的驱动包
  • 解决：安装FineBI官方提供的驱动包隔离插件
• 数据库连接
  

展示