怎样在各大网站做有效的宣传,广州市网络广告推广,网站开发详细报价单,免费网站空间免备案目录 需求分析背景介绍目标需求数据内容DBeaver连接到Hive建库建表加载数据 ETL数据清洗数据问题需求实现查看结果扩展 指标计算需求需求指标统计 可视化展示BIFineBI的介绍及安装FineBI配置数据源及数据准备 可视化展示 P73~77 https://www.bilibili.com/video/BV1WY4y197g7?… 目录 需求分析背景介绍目标需求数据内容DBeaver连接到Hive建库建表加载数据 ETL数据清洗数据问题需求实现查看结果扩展 指标计算需求需求指标统计 可视化展示BIFineBI的介绍及安装FineBI配置数据源及数据准备 可视化展示 P73~77 https://www.bilibili.com/video/BV1WY4y197g7?p73
需求分析
背景介绍
聊天平台每天都会有大量的用户在线会出现大量的聊天数据通过对聊天数据的统计分析可以更好的对用户构建精准的用户画像为用户提供更好的服务以及实现高ROI的平台运营推广给公司的发展决策提供精确的数据支撑。 我们将基于一个社交平台App的用户数据完成相关指标的统计分析并结合BI工具对指标进行可视化展现。
目标
基于Hadoop和Hive实现聊天数据统计分析构建聊天数据分析报表
需求
统计今日总消息量统计今日每小时消息量、发送和接收用户数统计今日各地区发送消息数据量统计今日发送消息和接收消息的用户数统计今日发送消息最多的Top10用户统计今日接收消息最多的Top10用户统计发送人的手机型号分布情况统计发送人的设备操作系统分布情况 数据内容
数据大小30万条数据列分隔符Hive默认分隔符’\001’数据字典及样例数据 DBeaver连接到Hive 建库建表
--如果数据库已存在就删除
drop database if exists db_msg cascade ;--创建数据库
create database db_msg ;--切换数据库
use db_msg ;--列举数据库
show databases ;--如果表已存在就删除
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 消息内容
);加载数据
上传文件到Linux系统 直接拖拽上传
创建文件夹
hadoop fs -mkdir -p /chatdemo/data放到指定目录
hadoop fs -put chat_data-30W.csv /chatdemo/data/load数据到表
load data inpath /chatdemo/data/chat_data-30W.csv into table tb_msg_source;验证结果
select msg_time, sender_name, sender_ip, sender_phonetype, receiver_name, receiver_network
from tb_msg_source limit 10;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;需求
需求1对字段为空的不合法数据进行过滤 where过滤 需求2通过时间字段构建天和小时字段 date hour函数 需求3从GPS的经纬度中提取经度和维度 split函数 需求4将ETL以后的结果保存到一张新的Hive表中
create table db_msg.tb_msg_etl(
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 消息内容,
msg_day string comment 消息日,
msg_hour string comment 消息小时,
sender_lng double comment 经度,
sender_lat double comment 纬度
);实现
INSERT OVERWRITE TABLE db_msg.tb_msg_etl
SELECT *, DATE(msg_time) as msg_day, HOUR(msg_time) as msg_hour, split(sender_gps, ,)[0] AS sender_lng,split(sender_gps, ,)[1] AS sender_lat
FROM tb_msg_source WHERE LENGTH(sender_gps) 0;查看结果
select msg_time, msg_day, msg_hour, sender_gps, sender_lng, sender_lat from db_msg.tb_msg_etl limit 10;扩展
其实我们刚刚完成了 从表tb_msg_source 查询数据进行数据过滤和转换并将结果写入到tb_msg_etl表中的操作 这种操作本质上是一种简单的ETL行为。
ETL
EExtract抽取TTransform转换LLoad加载
从A抽取数据(E)进行数据转换过滤(T)将结果加载到B(L)就是ETL。 ETL在大数据系统中是非常常见的后续还会继续接触到它。 目前简单了解一下即可。
指标计算
需求
统计今日总消息量统计今日每小时消息量、发送和接收用户数统计今日各地区发送消息数据量统计今日发送消息和接收消息的用户数统计今日发送消息最多的Top10用户统计今日接收消息最多的Top10用户统计发送人的手机型号分布情况统计发送人的设备操作系统分布情况
需求指标统计
指标1统计今日消息总量
--保存结果表
CREATE TABLE IF NOT EXISTS tb_rs_total_msg_cnt
COMMENT 每日消息总量 AS
SELECT msg_day, COUNT(*) AS total_msg_cnt
FROM db_msg.tb_msg_etl
GROUP BY msg_day; 指标2统计每小时消息量、发送和接收用户数
--保存结果表
CREATE TABLE IF NOT EXISTS tb_rs_hour_msg_cnt
COMMENT 每小时消息量趋势 AS
SELECT msg_hour, 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 msg_hour; 指标3统计今日各地区发送消息总量
CREATE TABLE IF NOT EXISTS tb_rs_loc_cnt
COMMENT 今日各地区发送消息总量 AS
SELECT msg_day, sender_lng, sender_lat, COUNT(*) AS total_msg_cnt
FROM db_msg.tb_msg_etl
GROUP BY msg_day, sender_lng, sender_lat指标4统计今日发送和接收用户人数
--保存结果表
CREATE TABLE IF NOT EXISTS tb_rs_usr_cnt
COMMENT 今日发送消息人数、接受消息人数 AS
SELECT
msg_day,
COUNT(DISTINCT sender_account) AS sender_usr_cnt,
COUNT(DISTINCT receiver_account) AS receiver_usr_cnt
FROM db_msg.tb_msg_etl
GROUP BY msg_day; 指标5统计发送消息条数最多的Top10用户
--保存结果表
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_s_user_top10
COMMENT 发送消息条数最多的Top10用户 AS
SELECT sender_name AS username, COUNT(*) AS sender_msg_cnt
FROM db_msg.tb_msg_etl
GROUP BY sender_name
ORDER BY sender_msg_cnt DESC
LIMIT 10; 指标6统计接收消息条数最多的Top10用户
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_r_user_top10
COMMENT 接收消息条数最多的Top10用户 AS
SELECT
receiver_name AS username,
COUNT(*) AS receiver_msg_cnt
FROM db_msg.tb_msg_etl
GROUP BY receiver_name
ORDER BY receiver_msg_cnt DESC
LIMIT 10; 指标7统计发送人的手机型号分布情况
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_sender_phone
COMMENT 发送人的手机型号分布 AS
SELECT sender_phonetype, COUNT(sender_account) AS cnt
FROM db_msg.tb_msg_etl
GROUP BY sender_phonetype指标8统计发送人的手机操作系统分布
--保存结果表
CREATE TABLE IF NOT EXISTS db_msg.tb_rs_sender_os
COMMENT 发送人的OS分布 AS
SELECTsender_os, COUNT(sender_account) AS cnt
FROM db_msg.tb_msg_etl
GROUP BY sender_os 可视化展示
BI
BIBusiness Intelligence商业智能。 指用现代数据仓库技术、线上分析处理技术、数据挖掘和数据展现技术进行数据分析以实现商业价值。
简单来说就是借助BI工具可以完成复杂的数据分析、数据统计等需求为公司决策带来巨大的价值。
所以一般提到BI我们指代的就是工具软件。常见的BI软件很多比如 FineBI、SuperSet、PowerBI、TableAu等。
FineBI的介绍及安装
FineBI的介绍https://www.finebi.com/
FineBI 是帆软软件有限公司推出的一款商业智能Business Intelligence产品。FineBI 是定位于自助大数据分析的 BI 工具能够帮助企业的业务人员和数据分析师开展以问题导向的探索式分析。
FineBI的特点 通过多人协作来实现最终的可视化构建 不需要通过复杂代码来实现开发通过可视化操作实现开发 适合于各种数据可视化的应用场景 支持各种常见的分析图表和各种数据源 支持处理大数据
FineBI的界面 启动登陆,选内置数据看。
目录首页大屏及帮助文档
仪表盘用于构建所有可视化报表
数据准备用于配置各种报表的数据来源
管理系统用于管理整个FineBI的使用用户管理、数据源管理、插件管理、权限管理等
FineBI配置数据源及数据准备
FineBI与Hive集成的官方文档https://help.fanruan.com/finebi/doc-view-301.html
驱动配置
问题如果使用FineBI连接Hive读取Hive的数据表需要在FineBI中添加Hive的驱动jar包 解决将Hive的驱动jar包放入FineBI的lib目录下
step1找到提供的【Hive连接驱动】
step2将这些文件放入FineBI的安装目录下的webapps\webroot\WEB-INF\lib目录中
插件安装
问题我们自己放的Hive驱动包会与FineBI自带的驱动包产生冲突导致FineBI无法识别我们自己的驱动包 解决安装FineBI官方提供的驱动包隔离插件
step1找到隔离插件
step2安装插件
step3重启FineBI
新建连接
测试连接,保存连接
数据准备 选中8个分析的结果表确定然后更新数据。 新建文件夹之后选中新建分析主题。
可视化展示
基于FineBI完成指标的可视化展现
选中数据 添加组件 修改黑色字 重命名 选择仪表板拖进去取消显示标题调整大小位置颜色。 类似操作添加数据然后完成标题展示。 添加雷达图 取消图例 拖拽到仪表板调整大小 添加环饼状图
添加地图 添加柱状图
添加词汇云图
添加趋势曲线图
报表预览
