Hive SQL函数实战：从踩坑到精通的数仓必备指南

1. 为什么我坚持把Hive SQL函数当成一门“手艺”来练

刚接触Hive那会儿，我总觉得SQL函数嘛，翻翻官方文档就行了，用到哪个查哪个。结果第一次做用户行为路径分析，光是一个日期格式转换就卡了我整整一个下午——from_unixtime和unix_timestamp的参数顺序记反了，出来的结果全是1970年。后来被导师一句“你连时间戳都玩不转，还做什么数仓”给点醒了。

Hive SQL函数这东西，表面上看是工具，实际上是你和数据之间的翻译官。你脑子里想的业务逻辑，得靠这些函数精确地翻译成引擎能执行的指令。翻译错了，结果就偏了；翻译得笨拙，跑一天也出不来数。我见过太多人，窗口函数只会row_number，字符串处理只会substr，遇到复杂点的去重、拼接、行列转换就抓瞎。所以这篇内容，我想把Hive SQL里那些真正高频、真正能解决实际问题的函数，按使用场景拆开揉碎讲一遍。不管你是刚入行的数仓新人，还是写了几年SQL但总觉得不够顺手的老手，都能从里面找到能直接抄作业的写法。

这篇文章不会照搬官方文档的函数列表，那种东西你搜一下就有。我要讲的是：什么场景下该用哪个函数、参数怎么传才不出错、多个函数怎么组合起来解决一个完整问题、以及那些文档里不会写的坑。内容会覆盖日期时间处理、字符串操作、聚合与窗口、条件判断、类型转换、复杂数据类型这几大块，每一块都配上我实际踩过的坑和优化后的写法。

2. 日期时间函数：数仓里最容易翻车的地方

2.1 时间戳与字符串的互转：参数顺序是道坎

Hive里处理时间，核心就两个函数：unix_timestamp和from_unixtime。前者把时间字符串转成秒级时间戳，后者把时间戳转成指定格式的字符串。听起来简单，但参数顺序和格式串的写法，坑了无数人。

-- 把标准格式的时间字符串转成时间戳 select unix_timestamp('2024-01-15 10:30:00') as ts; -- 返回 1705300200 -- 把时间戳转成指定格式的字符串 select from_unixtime(1705300200, 'yyyy-MM-dd HH:mm:ss') as dt; -- 返回 2024-01-15 10:30:00

这里第一个坑：unix_timestamp如果不传第二个参数，它默认只认yyyy-MM-dd HH:mm:ss这种格式。如果你的源数据是2024/01/15或者20240115，不传格式串就会返回NULL。我见过有人从日志表里取日期，源数据是2024-01-15T10:30:00.000Z这种带T和毫秒的，直接扔给unix_timestamp，结果全是NULL，排查了半天才发现是格式没匹配上。

-- 处理非标准格式，必须显式指定格式串 select unix_timestamp('2024/01/15', 'yyyy/MM/dd') as ts; -- 返回 1705248000 -- 处理带毫秒和时区标识的，先截取再转换 select unix_timestamp(substr('2024-01-15T10:30:00.000Z', 1, 19), 'yyyy-MM-dd''T''HH:mm:ss') as ts;

第二个坑：格式串里的大小写。MM是月份，mm是分钟，HH是24小时制，hh是12小时制。我有个同事写了个yyyy-mm-dd，结果月份全变成了00，因为mm被当成了分钟。这种错误在代码review的时候特别难发现，因为语法上完全合法，跑起来也不报错，就是结果不对。

注意：在Hive里写格式串，建议统一用yyyy-MM-dd HH:mm:ss这种标准写法，月份用大写MM，分钟用小写mm，小时用大写HH。养成肌肉记忆，能省掉大量排查时间。

2.2 日期计算：date_add、date_sub和datediff的配合

日期加减和差值计算，是数仓日常里除了聚合之外最高频的操作。Hive提供了date_add、date_sub和datediff三个核心函数。

-- 日期加天数 select date_add('2024-01-15', 7); -- 返回 2024-01-22 -- 日期减天数 select date_sub('2024-01-15', 7); -- 返回 2024-01-08 -- 两个日期相差的天数 select datediff('2024-01-22', '2024-01-15'); -- 返回 7

这三个函数看起来简单，但组合起来能解决很多实际问题。比如计算用户留存：用户首次活跃日期是first_dt，某次活跃日期是log_dt，留存天数就是datediff(log_dt, first_dt)。再比如计算账期：账单日是每月10号，还款日是每月25号，免息期就是datediff(还款日, 账单日)。

但这里有个隐藏的坑：date_add和date_sub的第二个参数可以是负数。date_add('2024-01-15', -7)等价于date_sub('2024-01-15', 7)。这个特性有时候能简化代码，比如你有一个变量offset，正数表示往后推，负数表示往前推，那统一用date_add就行了，不用写if-else判断。

还有一个更隐蔽的坑：datediff只计算日期部分，忽略时间。datediff('2024-01-15 23:59:59', '2024-01-15 00:00:00')返回的是0，不是1。如果你需要精确到小时或分钟的差值，得用unix_timestamp转成秒再相减，然后除以3600或60。

-- 精确到小时的时间差 select (unix_timestamp('2024-01-16 01:00:00') - unix_timestamp('2024-01-15 23:00:00')) / 3600 as hour_diff; -- 返回 2.0

2.3 日期截取与格式化：trunc和date_format的实战用法

trunc函数用于把日期截取到指定的粒度，比如截取到月初、年初。date_format则用于把日期转成任意格式的字符串。

-- 截取到月初 select trunc('2024-01-15', 'MM'); -- 返回 2024-01-01 -- 截取到年初 select trunc('2024-01-15', 'YYYY'); -- 返回 2024-01-01 -- 格式化为年月 select date_format('2024-01-15', 'yyyy-MM'); -- 返回 2024-01

这两个函数在做月度、年度汇总的时候特别有用。比如你要按月统计订单量，但订单表里存的是精确到秒的时间戳，那就可以用date_format(from_unixtime(ts), 'yyyy-MM')作为分组字段。

但这里有个性能上的坑：date_format和from_unixtime都是运行时计算，如果在大表上直接对时间戳字段做转换再分组，会导致全表扫描且无法利用分区裁剪。更好的做法是先用trunc把日期截取到天，然后按天分组，再在外层汇总到月。或者如果表是按天分区的，直接用分区字段做过滤和分组，避免对时间戳字段做函数操作。

提示：在Hive里，对分区字段使用函数会导致分区裁剪失效。比如where date_format(dt, 'yyyy-MM') = '2024-01'会扫描所有分区，而where dt >= '2024-01-01' and dt <= '2024-01-31'才能正确裁剪。这个坑在数据量大、分区多的时候，性能差异可能是几十倍。

3. 字符串函数：数据清洗的瑞士军刀

3.1 拼接与分割：concat、concat_ws和split的三角关系

字符串拼接和分割，是数据清洗里最基础也最频繁的操作。Hive提供了concat、concat_ws和split三个核心函数。

-- concat：直接拼接，不指定分隔符 select concat('a', 'b', 'c'); -- 返回 abc -- concat_ws：用指定分隔符拼接 select concat_ws('-', 'a', 'b', 'c'); -- 返回 a-b-c -- split：按分隔符分割成数组 select split('a-b-c', '-'); -- 返回 ["a","b","c"]

这三个函数组合起来，能解决很多实际问题。比如把用户表的省、市、区三个字段拼成一个完整地址：concat_ws('', province, city, district)。再比如把日志里的URL按?分割，取参数部分：split(url, '\\?')[1]。

但这里有几个坑。第一个坑：concat如果任何一个参数是NULL，整个结果就是NULL。比如concat('a', NULL, 'c')返回NULL，而不是ac。如果你希望NULL被忽略，得用concat_ws，因为concat_ws会自动跳过NULL值。

select concat('a', NULL, 'c'); -- 返回 NULL select concat_ws('-', 'a', NULL, 'c'); -- 返回 a-c

第二个坑：split的分隔符是正则表达式，特殊字符需要转义。比如按.分割，得写成split(str, '\\.')，因为.在正则里表示任意字符。按|分割，得写成split(str, '\\|')。这个坑在解析IP地址、文件路径的时候特别常见。

第三个坑：split返回的是数组，取元素用[index]，索引从0开始。如果索引越界，返回NULL，不会报错。这个特性有时候能用来做安全取值，比如split(str, ',')[0]，如果str为空，返回NULL，不会抛异常。

3.2 截取与替换：substr、substring和regexp_replace的选用逻辑

字符串截取和替换，Hive提供了substr、substring和regexp_replace等函数。substr和substring功能完全一样，只是名字不同，用哪个看个人习惯。

-- 从第1个字符开始，截取3个字符 select substr('abcdef', 1, 3); -- 返回 abc -- 从第3个字符开始，截取到末尾 select substr('abcdef', 3); -- 返回 cdef -- 从倒数第2个字符开始，截取2个字符 select substr('abcdef', -2, 2); -- 返回 ef

substr的第三个参数是长度，不是结束位置。这个和Java的substring不一样，Java的substring(start, end)是左闭右开区间。我见过从Java转过来的人，写substr(str, 1, 3)以为取的是第1到第3个字符，实际上取的是从第1个字符开始的3个字符，也就是第1、2、3个字符。结果碰巧一样，但逻辑理解错了，换个参数就出问题。

regexp_replace用于正则替换，功能比replace强大得多。replace只能做固定字符串替换，regexp_replace可以做模式匹配替换。

-- 把所有的数字替换成空 select regexp_replace('a1b2c3', '[0-9]', ''); -- 返回 abc -- 把连续的空格替换成单个空格 select regexp_replace('a b c', '\\s+', ' '); -- 返回 a b c -- 提取字符串中的数字部分 select regexp_extract('abc123def', '[0-9]+', 0); -- 返回 123

regexp_extract是提取而非替换，第三个参数是分组索引，0表示整个匹配，1表示第一个括号分组。这个函数在从非结构化文本里提取信息时特别有用，比如从日志里提取订单号、从URL里提取商品ID。

注意：正则表达式在Hive里是Java正则，和Python、JavaScript的正则有些细微差别。比如\d在Java正则里是支持的，但在Hive里建议用[0-9]，兼容性更好。另外，正则匹配是CPU密集型操作，在大表上慎用，能先用like过滤就先过滤。

3.3 大小写与去空格：trim、ltrim、rtrim和upper、lower

数据清洗里，大小写不一致和首尾空格是最常见的脏数据问题。Hive提供了trim、ltrim、rtrim、upper、lower等函数来处理。

-- 去除首尾空格 select trim(' abc '); -- 返回 abc -- 去除左侧空格 select ltrim(' abc '); -- 返回 abc -- 去除右侧空格 select rtrim(' abc '); -- 返回 abc -- 转大写 select upper('abc'); -- 返回 ABC -- 转小写 select lower('ABC'); -- 返回 abc

这些函数看起来简单，但在实际数据清洗里，组合使用能解决大问题。比如用户输入的邮箱，可能有大写有小写，有首尾空格，那就可以用lower(trim(email))来标准化。再比如从外部系统导入的编码，可能左侧有不可见字符，trim去不掉，得用regexp_replace把非打印字符替换掉。

-- 去除所有不可见字符（包括制表符、换行符等） select regexp_replace(str, '[\\u0000-\\u001F\\u007F]', '');

这个写法在处理从Excel或CSV导入的数据时特别有用，因为那些文件里经常混入不可见字符，导致join不上或者group by出错。我遇到过最诡异的一次，两个表join，肉眼看起来字段值完全一样，就是join不上，最后发现其中一个表的字段末尾有个\r，是Windows换行符残留。用regexp_replace清洗一遍就好了。

4. 聚合与窗口函数：从“会写”到“写得好”的分水岭

4.1 基础聚合的进阶用法：collect_list、collect_set和percentile

除了count、sum、avg、max、min这些基础聚合，Hive还提供了collect_list、collect_set、percentile等进阶聚合函数，能解决很多复杂问题。

-- 把分组内的值收集成列表（不去重，保留顺序） select collect_list(user_id) from user_behavior group by dt; -- 把分组内的值收集成集合（去重，不保证顺序） select collect_set(user_id) from user_behavior group by dt; -- 计算百分位数 select percentile(amount, 0.5) from orders; -- 中位数 select percentile(amount, array(0.25, 0.5, 0.75)) from orders; -- 四分位数

collect_list和collect_set在做用户行为路径分析、商品关联分析的时候特别有用。比如你要看每个用户一天内访问了哪些页面，就可以用collect_list(page_id)把页面收集起来，然后在外层用concat_ws拼成路径字符串。

但这里有个坑：collect_list和collect_set的结果是无序的（collect_list在单reduce任务下可能有序，但多reduce下不保证）。如果你需要有序的结果，得先用sort_array排序，或者用collect_list配合distribute by和sort by。

-- 按时间排序后收集页面路径 select user_id, concat_ws('->', collect_list(page_id)) as path from ( select user_id, page_id from user_behavior distribute by user_id sort by user_id, visit_time ) t group by user_id;

percentile函数在计算分位数的时候特别有用，比如算订单金额的中位数、算响应时间的P95。但要注意，percentile是精确计算，数据量大时性能较差。如果对精度要求不高，可以用percentile_approx，它使用近似算法，性能好很多。

4.2 窗口函数的五种典型场景：排名、累计、滑动、偏移、分桶

窗口函数是Hive SQL里最强大的功能之一，也是区分SQL水平的分水岭。我把它归纳为五种典型场景：排名、累计、滑动、偏移、分桶。

排名场景：row_number、rank、dense_rank。这三个的区别是：row_number不管并列，1、2、3、4；rank并列跳号，1、1、3、4；dense_rank并列不跳号，1、1、2、3。

-- 每个品类下按销量排名 select category, product, sales, row_number() over (partition by category order by sales desc) as rn, rank() over (partition by category order by sales desc) as rk, dense_rank() over (partition by category order by sales desc) as drk from product_sales;

累计场景：sum、count、avg配合over子句，可以做累计求和、累计计数。

-- 按天累计销售额 select dt, daily_sales, sum(daily_sales) over (order by dt rows between unbounded preceding and current row) as cumulative_sales from daily_sales_table;

滑动场景：rows between N preceding and M following，可以做滑动窗口计算，比如近7天平均。

-- 近7天平均销售额 select dt, daily_sales, avg(daily_sales) over (order by dt rows between 6 preceding and current row) as ma7 from daily_sales_table;

偏移场景：lag和lead，取上一行或下一行的值，用于计算同比、环比、时间间隔。

-- 计算环比增长率 select dt, sales, lag(sales, 1) over (order by dt) as prev_sales, (sales - lag(sales, 1) over (order by dt)) / lag(sales, 1) over (order by dt) as growth_rate from daily_sales_table;

分桶场景：ntile，把数据分成N个桶，用于做数据分片、分层抽样。

-- 把用户按消费金额分成10个等级 select user_id, total_amount, ntile(10) over (order by total_amount desc) as amount_level from user_total_amount;

窗口函数的坑主要集中在partition by和order by的组合上。如果只写order by不写partition by，窗口是全表；如果只写partition by不写order by，窗口是分组内全部数据。另外，rows between和range between的区别也很关键：rows是按行数偏移，range是按值偏移。大多数场景用rows就够了，range在特定场景下才需要。

提示：窗口函数在Hive里是通过reduce阶段的partition by和sort by实现的，所以partition by的字段会决定数据分发。如果partition by的字段基数很大，会导致大量小文件，影响性能。建议在partition by之前先用distribute by控制数据分布。

4.3 聚合与窗口的性能差异：什么时候该用group by，什么时候该用over

很多人会困惑：同样是对分组做聚合，group by和over(partition by)有什么区别？什么时候该用哪个？

核心区别在于：group by会改变结果集的行数，每个分组只输出一行；over(partition by)不改变行数，每行都保留，聚合结果作为新列附加。

-- group by：每个品类输出一行 select category, sum(sales) as total_sales from product_sales group by category; -- over：每行都保留，附加品类总销售额 select category, product, sales, sum(sales) over (partition by category) as category_total_sales from product_sales;

性能上，group by通常比over快，因为group by可以在map端做部分聚合（combiner），减少shuffle数据量。而over需要把分组内所有数据发到同一个reducer，数据倾斜的风险更大。

所以选择原则是：如果只需要分组后的聚合结果，用group by；如果需要在保留明细的同时附加聚合信息，用over。如果既需要明细又需要聚合，但数据量很大，可以考虑先用group by算出聚合结果，再join回明细表，这样比直接用over性能更好。

5. 条件判断与类型转换：让SQL更健壮的细节

5.1 case when、if和coalesce的分工

条件判断是SQL里除了聚合之外最常用的逻辑。Hive提供了case when、if和coalesce三个核心函数。

-- case when：多条件判断 select case when score >= 90 then 'A' when score >= 80 then 'B' when score >= 60 then 'C' else 'D' end as grade from student_scores; -- if：二选一 select if(score >= 60, 'pass', 'fail') as result from student_scores; -- coalesce：取第一个非NULL值 select coalesce(phone, email, 'unknown') as contact from user_info;

case when是最通用的，可以处理任意多条件。if是case when的简化版，只处理两个分支。coalesce专门用于NULL值处理，返回参数列表中第一个非NULL的值。

这里有个常见的坑：case when的判断顺序是从上到下，一旦匹配就停止。所以条件的顺序很重要。比如上面成绩的例子，如果先写when score >= 60 then 'C'，那90分也会被判定为C。这个逻辑在写复杂条件的时候特别容易出错，建议按条件的严格程度从高到低排列。

另一个坑：coalesce虽然好用，但如果参数里有子查询或复杂表达式，每个参数都会被计算。比如coalesce(a, b, c)，如果a不为NULL，b和c也会被计算，只是结果被丢弃。如果b或c是耗时的子查询，就会浪费计算资源。这种情况下，用case when a is not null then a when b is not null then b else c end更高效，因为case when是短路求值。

5.2 cast和类型隐式转换：那些让你半夜被叫起来修数据的坑

Hive是强类型但支持隐式转换的，这意味着很多类型错误不会在编译时报错，而是在运行时才暴露。cast函数用于显式类型转换。

-- 字符串转整数 select cast('123' as int); -- 返回 123 -- 整数转字符串 select cast(123 as string); -- 返回 '123' -- 字符串转日期 select cast('2024-01-15' as date); -- 返回 2024-01-15

隐式转换的坑在于：Hive会在某些场景下自动做类型转换，但转换规则可能和你的预期不一致。比如字符串和数字比较时，Hive会把字符串转成数字。'123' = 123返回true，'abc' = 123返回false（因为'abc'转数字失败，变成NULL，NULL和任何值比较都是NULL，在where里被当作false）。

更隐蔽的坑是：大整数和浮点数的转换。cast(1234567890123 as float)会丢失精度，因为float只有24位有效数字。如果这个数字是订单号或用户ID，精度丢失会导致join不上或数据错乱。所以涉及ID类的字段，永远用string或bigint，不要用float或double。

还有一个坑：cast转换失败时返回NULL，不会报错。比如cast('abc' as int)返回NULL。这个特性有时候能用来做安全转换，但更多时候是bug的来源。如果你期望转换失败时抛异常，得自己写判断逻辑。

-- 安全转换：先判断再转换 select case when str rlike '^[0-9]+$' then cast(str as int) else null end as safe_int from source_table;

注意：在Hive里做join的时候，如果两边的字段类型不一致，Hive会尝试隐式转换。如果转换规则不明确，可能导致数据分发错误，进而导致join结果错误。所以最佳实践是：join的字段类型必须显式一致，不要依赖隐式转换。

5.3 复杂数据类型：array、map、struct的构造与访问

Hive支持array、map、struct三种复杂数据类型，在处理嵌套数据的时候特别有用。

-- 构造array select array('a', 'b', 'c'); -- 返回 ["a","b","c"] -- 构造map select map('k1', 'v1', 'k2', 'v2'); -- 返回 {"k1":"v1","k2":"v2"} -- 构造struct select struct('a', 1, 2.0); -- 返回 {"col1":"a","col2":1,"col3":2.0} -- 访问array元素 select array('a', 'b', 'c')[0]; -- 返回 a -- 访问map值 select map('k1', 'v1', 'k2', 'v2')['k1']; -- 返回 v1 -- 访问struct字段 select struct('a', 1, 2.0).col1; -- 返回 a

复杂数据类型在解析JSON、处理嵌套日志的时候特别有用。比如日志里有个字段是JSON数组，你可以用get_json_object提取出来，然后用split转成array，再用explode展开成多行。

-- 解析JSON数组并展开 select id, item from ( select id, split(regexp_replace(get_json_object(json_str, '$.items'), '\\[|\\]|"', ''), ',') as items from source_table ) t lateral view explode(items) tmp as item;

这里用到了lateral view explode，这是Hive里处理数组和map的利器。explode把数组或map的每个元素展开成一行，lateral view把展开的结果和原表做笛卡尔积。这个组合在行转列、列转行的场景里几乎是必用的。

但lateral view explode有个坑：如果数组为空或NULL，explode不会产生任何行，导致原表的这行数据丢失。如果你希望保留原行，得用lateral view outer explode，outer关键字会保留原行，数组为空时对应字段为NULL。

-- 保留原行，数组为空时字段为NULL select id, item from source_table lateral view outer explode(items) tmp as item;

6. 那些文档里不会写的实战心得

6.1 函数嵌套的阅读性：用CTE代替多层嵌套

Hive SQL里函数可以无限嵌套，但嵌套层数多了之后，代码几乎没法读。我见过最夸张的一个SQL，嵌套了十几层，括号对都数不清。这种代码维护起来是灾难，改一个地方得从头理一遍。

更好的做法是用CTE（Common Table Expression），也就是with子句，把每一步的计算拆开，每一步的结果给一个有意义的名字。

-- 不好的写法：多层嵌套 select user_id, sum(order_amount) as total_amount from ( select user_id, order_id, order_amount from ( select user_id, order_id, cast(order_amount as decimal(10,2)) as order_amount from raw_orders where dt = '2024-01-15' ) t1 where order_amount > 0 ) t2 group by user_id; -- 好的写法：用CTE拆开 with cleaned_orders as ( select user_id, order_id, cast(order_amount as decimal(10,2)) as order_amount from raw_orders where dt = '2024-01-15' ), valid_orders as ( select user_id, order_id, order_amount from cleaned_orders where order_amount > 0 ) select user_id, sum(order_amount) as total_amount from valid_orders group by user_id;

CTE的好处是：每一步的逻辑独立，可以单独测试；命名有意义，读代码的时候能快速理解每一步在做什么；调试的时候可以把中间步骤单独select出来看结果。

6.2 数据倾斜的识别与缓解：从函数层面能做什么

数据倾斜是Hive里最让人头疼的问题之一。表现是：大部分reduce任务很快跑完，少数几个reduce任务跑得特别慢，甚至卡死。根本原因是：某个key的数据量远大于其他key。

从函数层面，能做的主要是两件事：一是避免在倾斜key上做聚合，二是把倾斜key打散。

-- 倾斜key打散：给倾斜key加随机前缀 select concat(key, '_', cast(rand() * 10 as int)) as salted_key, value from source_table; -- 聚合后再合并 select split(salted_key, '_')[0] as key, sum(value) as total_value from ( select concat(key, '_', cast(rand() * 10 as int)) as salted_key, value from source_table ) t group by split(salted_key, '_')[0];

这个思路是：先把倾斜key加上随机后缀，打散到多个reduce任务里做局部聚合，然后再把结果合并。代价是需要两轮聚合，但能有效缓解倾斜。

另一个思路是：如果倾斜key是NULL或空值，可以在聚合前过滤掉，或者用coalesce替换成随机值。很多倾斜问题其实是因为大量NULL值集中到了同一个reduce任务。

提示：在Hive里，count(distinct)是导致数据倾斜的常见原因，因为它需要把所有数据发到一个reduce任务去重。如果数据量大，建议改成group by+count的两步写法，或者用approx_count_distinct做近似去重。

6.3 函数选择的性能考量：什么时候该用内置函数，什么时候该用UDF

Hive内置函数已经覆盖了绝大多数场景，但总有一些特殊需求需要自定义UDF。我的原则是：能用内置函数就用内置函数，实在不行再写UDF。

原因有三：第一，内置函数是原生实现，性能通常比UDF好；第二，内置函数经过充分测试，稳定性有保障；第三，UDF需要额外部署和维护，增加了运维成本。

但有些场景确实需要UDF，比如：复杂的加密解密、特定的业务规则计算、调用外部服务。写UDF的时候，尽量用Java而不是Python，因为Java UDF的性能更好。另外，UDF的输入输出类型要明确，避免用Object这种泛型，否则Hive无法做类型推断，会影响执行计划优化。

// Java UDF示例：计算字符串的MD5 package com.example.hive.udf; import org.apache.hadoop.hive.ql.exec.UDF; import org.apache.hadoop.io.Text; import java.security.MessageDigest; public class Md5UDF extends UDF { public Text evaluate(Text input) { if (input == null) return null; try { MessageDigest md = MessageDigest.getInstance("MD5"); byte[] digest = md.digest(input.toString().getBytes()); StringBuilder sb = new StringBuilder(); for (byte b : digest) { sb.append(String.format("%02x", b)); } return new Text(sb.toString()); } catch (Exception e) { return null; } } }

注册UDF用create temporary function，只在当前会话有效。如果要永久注册，得用create function，但需要管理员权限。

-- 临时注册 add jar /path/to/udf.jar; create temporary function md5_udf as 'com.example.hive.udf.Md5UDF'; -- 使用 select md5_udf(user_id) from user_table;

6.4 函数组合的实战案例：一个完整的用户留存分析

最后用一个完整的案例，把前面讲的函数串起来。需求是：计算每日新增用户的次日、3日、7日留存率。

with user_first_visit as ( -- 每个用户的首次访问日期 select user_id, min(dt) as first_dt from user_behavior where dt >= '2024-01-01' and dt <= '2024-01-31' group by user_id ), user_visit_detail as ( -- 每个用户每天的访问记录 select user_id, dt from user_behavior where dt >= '2024-01-01' and dt <= '2024-02-07' group by user_id, dt ), retention_calc as ( -- 计算留存天数 select f.user_id, f.first_dt, v.dt as visit_dt, datediff(v.dt, f.first_dt) as retention_day from user_first_visit f join user_visit_detail v on f.user_id = v.user_id where v.dt >= f.first_dt ), retention_summary as ( -- 按首次访问日期和留存天数汇总 select first_dt, retention_day, count(distinct user_id) as retained_users from retention_calc where retention_day in (1, 3, 7) group by first_dt, retention_day ), new_user_count as ( -- 每日新增用户数 select first_dt, count(distinct user_id) as new_users from user_first_visit group by first_dt ) -- 计算留存率 select n.first_dt, n.new_users, max(case when r.retention_day = 1 then r.retained_users else 0 end) as day1_retained, max(case when r.retention_day = 1 then r.retained_users / n.new_users else 0 end) as day1_rate, max(case when r.retention_day = 3 then r.retained_users else 0 end) as day3_retained, max(case when r.retention_day = 3 then r.retained_users / n.new_users else 0 end) as day3_rate, max(case when r.retention_day = 7 then r.retained_users else 0 end) as day7_retained, max(case when r.retention_day = 7 then r.retained_users / n.new_users else 0 end) as day7_rate from new_user_count n left join retention_summary r on n.first_dt = r.first_dt group by n.first_dt, n.new_users order by n.first_dt;

这个案例用到了min、datediff、count(distinct)、case when、max、join、group by、CTE等几乎所有核心函数。每一步都有明确的业务含义，拆开看逻辑清晰，合起来看是一个完整的分析链路。

实际跑的时候，如果数据量大，count(distinct)可能会成为瓶颈。可以考虑用approx_count_distinct替代，或者先用group by去重再count。另外，join的时候要注意数据倾斜，如果某个用户访问记录特别多，可以考虑加随机前缀打散。

我在实际使用中发现，留存分析这类需求，最难的不是写SQL，而是理解业务口径。比如“次日留存”的定义是：首次访问后的第二天访问了就算，还是首次访问后的24小时内访问了才算？不同口径算出来的结果可能差很多。所以写SQL之前，一定要和业务方对齐口径，把定义写进注释里，避免后面扯皮。

最后再分享一个小技巧：Hive SQL里可以用explain命令查看执行计划，看看有没有全表扫描、有没有数据倾斜、有没有不必要的shuffle。养成写复杂SQL之前先explain一下的习惯，能提前发现很多性能问题。