大数据Hadoop学习八

博客三：HiveQL查询实战与BI可视化完整项目指南

摘要： 本文是Hive入门系列的收官之作，将综合运用前两周知识，深入HiveQL查询细节，并通过一个完整的聊天数据分析项目，演示从数据ETL、指标统计到BI可视化展现的全流程。

一、HiveQL查询进阶

除了标准的SQL语法，HiveQL还提供了一些扩展功能。

1. 正则匹配（RLIKE）
   使用RLIKE关键字可以进行强大的正则表达式匹配。

   -- 查找地址中包含‘广东’的记录
   SELECT * FROM orders WHERE useraddress RLIKE '.*广东.*';
   
   -- 查找手机号符合188****0***模式
   SELECT * FROM users WHERE phone RLIKE '^188\\d{4}0\\d{3}$';
   

2. 数据抽样（TABLESAMPLE）
   对于海量表，抽样查看是常用操作。

   -- 桶抽样：随机抽取约1/10的数据（可基于某列或随机数）
   SELECT * FROM orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand());
   -- 块抽样：按大小或行数快速抽样（非随机，按顺序）
   SELECT * FROM orders TABLESAMPLE(100000 ROWS);
   SELECT * FROM orders TABLESAMPLE(30 PERCENT);
   

3. 虚拟列（Virtual Columns）
   Hive提供了访问数据物理信息的虚拟列。

   SET hive.exec.rowoffset=true; -- 开启ROW__OFFSET（默认通常关闭）
   SELECT *,
          INPUT__FILE__NAME,                -- 数据所在HDFS文件路径
          BLOCK__OFFSET__INSIDE__FILE       -- 行在文件中的偏移量
   FROM orders
   LIMIT 10;
   

二、实战项目：聊天数据分析报表

背景：分析某社交平台App的匿名聊天数据，构建用户画像和运营看板。

目标指标：

1. 今日总消息量、每小时消息量趋势
2. 发送/接收消息最多的Top10用户
3. 用户地域分布（地图可视化）
4. 用户设备（手机型号、操作系统）分布

步骤一：数据清洗（ETL）

原始表tb_msg_source存在GPS信息未拆分、时间字段未拆解等问题。

-- 1. 创建ETL目标表，添加清洗后的列
CREATE TABLE tb_msg_etl(
  ...所有原字段...,
  msg_day STRING COMMENT '消息日',
  msg_hour STRING COMMENT '消息小时',
  sender_lng DOUBLE COMMENT '经度',
  sender_lat DOUBLE COMMENT '纬度'
);

-- 2. 执行ETL清洗作业
INSERT OVERWRITE TABLE tb_msg_etl
SELECT
  *,
  DAY(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; -- 过滤掉GPS为空的不合法数据

步骤二：指标统计

使用HiveSQL计算各个指标，并将结果存入新表，便于BI工具连接。

1. 统计每小时消息量及用户数

   CREATE TABLE tb_rs_hour_msg_cnt 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 tb_msg_etl
   GROUP BY msg_hour;
   

2. 统计发送消息最多的Top10用户

   CREATE TABLE tb_rs_s_user_top10 AS
   SELECT
     sender_name AS username,
     COUNT(*) AS sender_msg_cnt
   FROM tb_msg_etl
   GROUP BY sender_name
   ORDER BY sender_msg_cnt DESC
   LIMIT 10;
   

3. 统计用户地域分布（为地图可视化准备）

   CREATE TABLE tb_rs_loc_cnt AS
   SELECT
     msg_day,
     sender_lng,
     sender_lat,
     COUNT(*) AS total_msg_cnt
   FROM tb_msg_etl
   GROUP BY msg_day, sender_lng, sender_lat;
   

   (其他指标类似，略)

三、BI可视化：FineBI入门与集成

1. 安装与配置

   • 从官网下载安装FineBI。
   • 将Hive的JDBC驱动JAR包放入FineBI的webapps/webroot/WEB-INF/lib目录。
   • 安装官方「驱动隔离插件」，避免驱动冲突，并重启FineBI。

2. 连接Hive数据源

   • 在FineBI的「管理系统」->「数据连接」中，新建一个「Hive」数据连接。
   • 正确填写HiveServer2的服务器地址、端口（通常是10000）、数据库名、用户名和密码。
   • 点击「测试连接」，成功后方可保存。

3. 制作可视化仪表板

   • 在「数据准备」中，新建一个业务包，并将Hive中我们创建好的结果表（如tb_rs_hour_msg_cnt, tb_rs_s_user_top10）添加进来。
   • 新建一个「仪表板」。
   • 拖拽组件，轻松绘图：
     • 折线图：拖拽msg_hour为横轴，`total_msg_cnt