Hive 数据仓库技术全解（二）：HQL高级函数（窗口函数、UDF/UDAF/UDTF、炸裂函数与行列转换，分区/分桶）

作者：Lijingang
日期：2026-07-10
标签：Hive Hadoop 大数据 SQL MapReduce 窗口函数 行列转换 分区分桶 UDF

本文为《Hive数据仓库技术全解》系列第二篇。
前篇已覆盖环境搭建、服务配置及 DDL/DML/DQL 基础。
本篇聚焦高阶 HQL 特性，包括窗口函数、UDF 自定义扩展、炸裂函数与行列转换、分区/分桶设计。
第三篇计划整理生产调优，涉及数据倾斜、小文件合并、Join 策略等

1. 内置函数

字符串函数
-- length():获取字符串长度
SELECT length('hello world');                   -- 11
-- trim():去掉首尾空格
SELECT trim('  lijingang  ');                   -- 'lijingang'
-- substring(str,start,stop):从第二个元素开始截取，包含第二个
SELECT substring('lijingang', 2);               -- 'ijingang'
-- replace():替换函数
SELECT replace('lijingang', 'jin', 'J');        -- 'liJgang'
-- regexp_replace():正则替换
SELECT regexp_replace('tel:1392902397', '\\d+', '****');  -- 'tel:****'
-- concat():拼接函数
SELECT concat('a', '-', 'b', '-', 'c');         -- 'a-b-c'
-- concat_ws():第一个参数为拼接符，其余参数拼接
SELECT concat_ws('-', 'a', 'b', 'c');           -- 'a-b-c'
-- split():分割函数，按照指定分隔符分割
SELECT split('a-b-c-d', '-');                   -- ["a","b","c","d"]
-- nvl():如果为null则填充第二个参数
SELECT nvl(NULL, 0);                             -- 0
-- get_json_object(): 解析json对象
SELECT get_json_object(json_str, '$.[0].name');  -- 解析 JSON
日期函数
SELECT unix_timestamp();                                 -- 当前时间戳
SELECT from_unixtime(1755734400,'yyyy-MM-dd');                        -- 时间戳转日期
-- datediff():两个日期相差天数，第一个参数减去第二个参数
SELECT datediff('2025-08-12', '2025-08-10');             -- 2
-- date_add(date,n):date加上n天的日期
SELECT date_add('2025-08-10', 3);                        -- '2025-08-13'
SELECT current_date();                                   -- 当前日期
SELECT year('2025-08-10 11:12:13');                      -- 2025
流程控制函数
-- 统计不同部门男女个数
/*
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE resultN]
END
*/
SELECT deptId,
    SUM(CASE sex WHEN '男' THEN 1 ELSE 0 END) AS `男`,
    SUM(CASE sex WHEN '女' THEN 1 ELSE 0 END) AS `女`
FROM emp_sex
GROUP BY deptId;

-- IF 函数
/*
if condition ,result1 , result2
condition成立 -> result1, 不成立 -> result2
*/
SELECT deptId,
    SUM(IF(sex='男', 1, 0)) AS `男`,
    SUM(IF(sex='女', 1, 0)) AS `女`
FROM emp_sex
GROUP BY deptId;

2. 高级聚合与炸裂函数

高级聚合函数（行转列，UDAF）
-- collect_list：聚合为不去重列表
SELECT sex, collect_list(job) FROM employee GROUP BY sex;

-- collect_set：聚合为去重集合
SELECT sex, collect_set(job) FROM employee GROUP BY sex;
炸裂函数（列转行，UDTF）
-- explode(array)：将数组每个元素展开为一行
SELECT explode(array(1, 3, 4, 6)) AS item;

-- explode(map)：将 map 每个键值对展开为一行
SELECT explode(map('name', 'zhangsan', 'age', '18'));

-- posexplode：展开的同时记录下标
SELECT posexplode(array('zhangsan', 'lisi', 'wangwu')) AS (index, value);

-- inline：展开 struct 数组
SELECT inline(array(named_struct('name','zhangsan','age',18)));

-- LATERAL VIEW：炸裂的同时保留原表字段的对应关系
SELECT name, fri
FROM employee
LATERAL VIEW explode(friends) tmp AS fri;

实战案例 —— 统计电影分类 Top3：

-- 表结构：name（电影名）、category（分类，逗号分隔）
/*
《疑犯追踪》,"悬疑,动作,科幻,剧情"
《Lie to me》,"悬疑,警匪,动作,心理,剧情"
《战狼2》,"战争,动作,灾难"
*/
-- 需求：统计不同分类的电影数量，取 Top3

SELECT type, cnt
FROM (
    SELECT type, COUNT(*) cnt
    FROM (
        SELECT name, type
        FROM movie_info
        LATERAL VIEW explode(split(category, ',')) tmp AS type
    ) t1
    GROUP BY type
) t2
ORDER BY cnt DESC
LIMIT 3;

3. 窗口函数

窗口函数是 HQL 中最强大的特性之一，可以在不改变行数的前提下进行分组计算。

-- 语法骨架
SELECT 字段, 窗口函数 OVER (
    PARTITION BY 分区字段
    ORDER BY 排序字段
    [ROWS|RANGE BETWEEN start AND end]
)
FROM table_name;

窗口缺省规则：

  • 无 ORDER BY → ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • 有 ORDER BY → RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

常用窗口函数：

函数作用
SUM / COUNT / AVG / MAX / MIN聚合计算
LAG(col, n, default)取前第 n 行
LEAD(col, n, default)取后第 n 行
FIRST_VALUE / LAST_VALUE窗口内的首个/末个值
RANK / DENSE_RANK / ROW_NUMBER排名

实战案例
表结构：

order_iduser_iduser_nameorder_dateorder_amount
11001小元2022-01-0110
21002小海2022-01-0215
31001小元2022-02-0323
41002小海2022-01-0429
51001小元2022-01-0546
61001小元2022-04-0642
71002小海2022-01-0750
81001小元2022-01-0850
91003小辉2022-04-0862
101003小辉2022-04-0962
111004小猛2022-05-1012
121003小辉2022-04-1175
131004小猛2022-06-1280
141003小辉2022-04-1394
-- 1. 累计消费金额
SELECT order_id, user_id, order_amount,
    SUM(order_amount) OVER (
        PARTITION BY user_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total_amount
FROM order_info;

-- 2. 距离上次下单的天数
SELECT user_id, order_date,
    DATEDIFF(order_date,
        LAG(order_date, 1, '首次下单') OVER (
            PARTITION BY user_id, SUBSTR(order_date, 1, 7)
            ORDER BY order_date
        )
    ) AS day_num
FROM order_info;

-- 3. RANK / DENSE_RANK / ROW_NUMBER 区别
-- 成绩：100, 100, 99
-- RANK()      → 1, 1, 3    (同分同名次，后续跳过)
-- DENSE_RANK() → 1, 1, 2   (同分同名次，后续不跳过)
-- ROW_NUMBER() → 1, 2, 3   (纯行号，不关心值)

4. 自定义函数（UDF）

当内置函数无法满足需求时，可以编写自定义函数：
自己计算字符串的长度

// 自定义类继承 Hive 的 UDF 类，重写 evaluate 方法
package com.lijingang.HiveFun;

import org.apache.hadoop.hive.ql.exec.UDF;

public class MyHiveFun extends UDF {
    public int evaluate(String str) {
        return str == null ? 0 : str.length();
    }
}

避坑提示：自定义 UDF 时，evaluate 方法必须为 public，且返回值类型要和 Hive 数据类型映射一致（如 String 映射为 VARCHAR）。如果传入参数为 null，记得做空指针防御，否则上线后容易报 NullPointerException。

-- 加载 jar 包
ADD JAR /opt/module/hive-3.1.3/my_funs/HiveFun-1.0-SNAPSHOT.jar;

-- 创建临时函数（重连后失效）
CREATE TEMPORARY FUNCTION my_len AS 'com.lijingang.HiveFun.MyHiveFun';

-- 创建永久函数（需将 jar 包上传到 HDFS）
CREATE FUNCTION my_len AS 'com.lijingang.HiveFun.MyHiveFun'
USING JAR 'hdfs://hadoop102:8020/udf/HiveFun-1.0-SNAPSHOT.jar';

-- 使用
SELECT my_len('hello');     -- 5

补充
除了上述自定义 UDF（一对一），Hive 还支持自定义 UDAF（多对一聚合） 和 UDTF（一对多生成）。它们的实现需分别继承 AbstractGenericUDAFResolver 和 GenericUDTF，并实现更复杂的逻辑。生产环境中，复杂的多行处理（如自定义分组排序聚合）建议使用 UDAF，但大多数场景下，Hive 内置的 collect_set/explode 已能满足 90% 的需求，故本文不再展开源码，后续调优篇如有需要再做补充。

5. 分区表与分桶表

分区表

将海量数据按照某一字段分目录存储，查询时结合分区字段可直接锁定目录，避免全表扫描。

-- 创建静态分区表
CREATE TABLE dept_partition (
    deptno   INT,
    dname    STRING,
    location STRING
)
PARTITIONED BY (day STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- 按分区导入数据
LOAD DATA LOCAL INPATH '/path/to/data.log'
INTO TABLE dept_partition
PARTITION (day='20260403');

-- 查询时利用分区过滤（不触发全表扫描）
SELECT * FROM dept_partition WHERE day = '20260403';

-- 分区操作
SHOW PARTITIONS dept_partition;                         -- 查看分区
ALTER TABLE dept_partition ADD PARTITION (day='20260405');  -- 添加分区
ALTER TABLE dept_partition DROP PARTITION (day='20260405'); -- 删除分区
MSCK REPAIR TABLE dept_partition SYNC PARTITIONS;       -- 修复分区
动态分区
-- 开启非严格模式（动态分区必须）
SET hive.exec.dynamic.partition.mode = nonstrict;

-- 将普通表改造为动态分区表
INSERT INTO TABLE order_info_partition
PARTITION (user_id)
SELECT order_id, user_name, order_date, order_amount, user_id
FROM order_info;
分桶表

分桶是将数据按字段哈希分文件存储，适合两张大表 Join 时优化。

-- 分桶表
CREATE TABLE stu_bucket (
    id   INT,
    name STRING
)
CLUSTERED BY (id) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- 分桶有序表（桶内排序，进一步优化 Join）
CREATE TABLE stu_bucket_sort (
    id   INT,
    name STRING
)
CLUSTERED BY (id) SORTED BY (id DESC) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

分区表适用于按天/按业务维度筛选的场景（缩小扫描范围）。
分桶表适用于大表与大表 Join（避免数据倾斜，桶内数据均匀分布），以及抽样查询（TABLESAMPLE）。
如果分区后单个分区文件依然很大（>1GB），建议再建分桶。

5. 文件存储格式对比
格式类型特点使用场景
TEXTFILE行存默认格式，可读性好数据展示
ORC列存压缩比高，查询快计算密集型（推荐）
Parquet列存兼容性好，生态广泛跨平台交互
SequenceFile行存二进制格式已基本淘汰

📌 本文定位：个人学习笔记，聚焦 Hive 高级特性在面试及生产环境中的高频考点。内含大量实战 SQL 案例及踩坑总结，仅供参考。

# 数据仓库 # hive # hadoop
Logo
亚马逊云科技技术品牌专区

更多推荐

open-code-review Codex 插件教程：对话式调用评审技能指南

本文是一份面向新手的 **open-code-review Codex 插件教程**，教你在 Codex 中通过对话式方式调用 open-code-review 的 AI 代码评审技能。open-code-review 是阿里巴巴开源的 AI 代码评审工具，采用"确定性流水线 + LLM Agent"混合架构，内置多语言评审规则（NPE、线程安全、XSS、SQL 注入等），可在 Codex 里用一

avatar 亚马逊云科技技术品牌专区

OBS Studio 完整指南：免费开源录屏与直播软件从零上手

想做一场像模像样的直播，或者把屏幕内容录成教程视频，但一想到商用软件的订阅费就有些犹豫？其实你不需要花一分钱。OBS Studio 是一款免费开源的直播与录屏软件，支持 Windows、macOS 和 Linux，采集、混音、编码、推流、录制，一条流水线全包了。## 先搞清楚 OBS Studio 是什么OBS Studio 是一个"场景化"的采集合成工具：你把摄像头、窗口、浏览器、图片、

avatar 亚马逊云科技技术品牌专区

如何用MediaCrawler爬取小红书抖音数据？7大平台爬虫从安装到实战完整指南

MediaCrawler 是一个支持小红书、抖音、快手、B站、微博、百度贴吧、知乎的开源自媒体数据采集工具。它能按关键词、按帖子、按创作者主页三种方式抓取笔记/视频内容、评论甚至二级评论，并把结果存成 JSON、CSV、Excel 或数据库。适合做市场调研、竞品监控、内容选题的运营人员，以及想要现成爬虫框架而不想从零写 JS 逆向的开发者。## 从一次"手动抄数据"说起想象这个场景：你负责

avatar 亚马逊云科技技术品牌专区