Hive 函数
·
内置函数
查看系统自带函数
hive> show functions;
-- 显示自带函数的描述：
hive> desc function size;
-- 函数详细信息，如何使用：
hive> desc function extended nvl;数学函数
|
函数 |
说明 |
|
round(double a, int d) |
小数部分d位之后数字四舍五入，例如round(21.263,2),返回21.26 |
|
floor(double a) |
对给定数据进行向下舍入最接近的整数。例如floor(21.2),返回21。 |
|
ceil(double a), ceiling(double a) |
将参数向上舍入为最接近的整数。例如ceil(21.2),返回22. |
|
rand(), rand(int seed) |
返回大于或等于0且小于1的平均分布随机数（依重新计算而变） |
|
exp(double a) |
返回e的n次方 |
|
pow(double a, double p) |
返回某数的乘幂 |
|
sqrt(double a) |
返回数值的平方根 |
|
bin(BIGINT a) |
返回二进制格式 |
|
hex(BIGINT a) hex(string a) |
将整数或字符转换为十六进制格式 |
|
conv(BIGINT num, int from_base, int to_base) |
将指定数值由原来的度量体系转化为指定的度量体系。例如CONV(‘a’,16,2),返回 ‘1010’ |
收集函数
|
函数 |
说明 |
|
size(Map<K.V>) |
返回的map类型的元素的数量 |
|
size(Array) |
返回数组类型的元素数量 |
类型转换函数
Int->bigint自动转换， bigint->int需要强制类型
|
函数 |
说明 |
|
cast(expr as type) |
类型转换。例如将字符”1″转换为整数:cast(’1′ as bigint)，如果转换失败返回NULL。 |
日期函数
| 函数名 | 语法 | 功能说明 | 示例 |
|---|---|---|---|
| 获取当前时间 | current_date() | 返回当前日期（YYYY-MM-DD） | current_date() → 2026-01-21 |
| current_timestamp() | 返回当前日期时间（YYYY-MM-DD HH:MM:SS.fff） | current_timestamp() → 2026-01-21 15:30:45.123 | |
| unix_timestamp() | 返回当前时间戳（从1970-01-01到现在的秒数） | unix_timestamp() → 1737459045 | |
| 日期与时间戳转换 |
unix_timestamp(date_str [, format]) | 将字符串按指定格式转为时间戳 | unix_timestamp('20260121', 'yyyyMMdd') → 1737427200 |
| from_unixtime(timestamp [, format]) | 将时间戳转为指定格式的日期字符串 | from_unixtime(1737427200, 'yyyy-MM-dd') → 2026-01-21 | |
| 日期加减 | date_add(start_date, days) | 日期加 N 天 | date_add('2026-01-21', 7) → 2026-01-28 |
| date_sub(start_date, days) | 日期减 N 天 | date_sub('2026-01-21', 7) → 2026-01-14 | |
| 日期差计算 | datediff(end_date, start_date) | 计算两个日期的天数差（end - start） | datediff('2026-01-28', '2026-01-21') → 7 |
| 日期截断 | trunc(date, unit) | 按指定单位截断日期（YYYY/MM/DD等），把日期精准定位到指定单位的第一个时间点 | trunc('2026-01-21', 'MM') → 2026-01-01，保留年、月，日置为 1 |
| 日期提取 |
year(date) | 提取年份 | year('2026-01-21') → 2026 |
| month(date) | 提取月份 | month('2026-01-21') → 1 | |
| day(date) / dayofmonth(date) | 提取日期 | day('2026-01-21') → 21 | |
| hour(timestamp) | 提取小时 | hour('2026-01-21 15:30:45') → 15 | |
| minute(timestamp) | 提取分钟 | minute('2026-01-21 15:30:45') → 30 | |
| second(timestamp) | 提取秒 | second('2026-01-21 15:30:45') → 45 | |
| weekofyear(date) | 提取日期所在的周数（1-53） | weekofyear('2026-01-21') → 4 | |
| 日期格式化 | date_format(date, format) | 将日期按指定格式转为字符串 | date_format('2026-01-21', 'yyyyMMdd') → 20260121 |
| 字符串转日期 | to_date(date_str) | 将字符串转为日期类型（默认 YYYY-MM-DD） | to_date('2026-01-21 15:30:45') → 2026-01-21 |
条件函数
| 函数名 | 语法格式 | 核心功能 | 实操示例 |
|---|---|---|---|
| IF 函数 | if(condition, true_val, false_val) | 三元判断：条件为真返回true_val，否则返回false_val；任意参数为NULL则返回NULL | if(10>5, '大于', '小于') |
| CASE WHEN（简单版） | case expr when val1 then res1 [when val2 then res2] [else default] end | 匹配表达式值：按顺序匹配expr的值，命中则返回对应结果，无匹配返回default（默认NULL） | case gender when '1' then '男' when '2' then '女' else '未知' end |
| CASE WHEN（通用版） | case when cond1 then res1 [when cond2 then res2] [else default] end | 通用多条件：按顺序判断条件，第一个满足的条件返回对应结果，无满足则返回default | case when score>=90 then '优秀' when score>=60 then '及格' else '不及格' end |
| NVL 函数 | nvl(expr1, expr2) | 空值替换：expr1为NULL则返回expr2，否则返回expr1 | nvl(salary, 0)，薪资为NULL返回0，否则返回原薪资 |
| NVL2 函数 | nvl2(expr1, expr2, expr3) | 空值分支：expr1非NULL返回expr2，expr1为NULL返回expr3 | nvl2(email, '有邮箱', '无邮箱')，邮箱非空返回'有邮箱'，否则返回'无邮箱' |
| COALESCE 函数 | coalesce(expr1, expr2, ..., exprN) | 多值兜底：返回第一个非NULL的参数，全NULL则返回NULL | coalesce(phone, email, id_card)，优先返回手机号，无则返回邮箱，无则返回身份证号 |
| ISNULL 函数 | isnull(expr) | 判断空值：expr为NULL返回true，否则返回false | isnull(salary) |
| ISNOTNULL 函数 | isnotnull(expr) | 判断非NULL：expr非NULL返回true，否则返回false | isnotnull(name) |
字符串函数
| 函数名 | 语法格式 | 核心功能 | 示例&结果 |
|---|---|---|---|
| length | length(str) | 计算字符串长度（含空格，NULL返回NULL） | length('Hive 字符串') -> 8 |
| concat | concat(str1, str2, ...) | 拼接多个字符串（任意参数为NULL则返回NULL） | concat('Hive-', '2.3', '-SQL') -> Hive-2.3-SQL |
| concat_ws | concat_ws(sep, str1, str2, ...) | 按分隔符拼接字符串（忽略NULL值） | concat_ws(',', '张三', NULL, '18') -> 张三,18 |
| substr/substring | substr(str, pos[, len]) | 截取字符串：pos为起始位置（从1开始），len为截取长度（可选） | substr('HiveSQL', 1, 4) -> Hive，substr('HiveSQL', 5) -> SQL |
| upper | upper(str) | 将字符串转为大写 | upper('hive') -> HIVE |
| lower | lower(str) | 将字符串转为小写 | lower('HIVE') -> hive |
| trim | trim(str) | 去除字符串首尾空格 | trim(' Hive ') |
| ltrim | ltrim(str) | 去除字符串左侧空格 | ltrim(' Hive ') |
| rtrim | rtrim(str) | 去除字符串右侧空格 | rtrim(' Hive ') |
| replace | replace(str, old, new) | 替换字符串中指定子串 | replace('HiveSQL', 'SQL', '函数') -> Hive函数 |
| regexp_replace | regexp_replace(str, regex, rep) | 按正则表达式替换子串 | regexp_replace('Hive123', '[0-9]', '') -> Hive |
| split | split(str, sep) | 按分隔符拆分字符串为数组 | split('a,b,c', ',') -> ["a","b","c"] |
| instr | instr(str, substr) | 查找子串首次出现的位置（无则返回0，从1开始） | instr('HiveSQL', 'SQL') -> 5 |
| locate | locate(substr, str[, pos]) | 从pos位置开始查找子串（无则返回0） | locate('e', 'HiveSQL', 3) -> 4 |
| lpad | lpad(str, len, pad) | 左侧补字符至指定长度 | lpad('Hive', 6, '*') -> **Hive |
| rpad | rpad(str, len, pad) | 右侧补字符至指定长度 | rpad('Hive', 6, '*') -> Hive** |
| nvl | nvl(str, default) | 字符串空值替换 | nvl(NULL, '空值') -> 空值 |
| regexp_extract | regexp_extract(str, regex, idx) | 按正则提取子串（idx为分组索引，1开始） | regexp_extract('Hive123SQL', '(\\d+)', 1) -> 123 |
内置的聚合函数（UDAF）
多进一出：进来多个值，聚合后变成一个值。
| 函数名 | 语法格式 | 核心功能 | 示例&结果 |
|---|---|---|---|
| count | count([DISTINCT] expr) | 统计行数/非NULL值数量；加DISTINCT去重统计 | count(*) -> 统计总行数；count(DISTINCT user_id) -> 统计去重用户数 |
| sum | sum([DISTINCT] expr) | 计算数值列的总和；加DISTINCT去重求和 | sum(salary) -> 所有薪资总和；sum(DISTINCT score) -> 去重分数总和 |
| avg | avg([DISTINCT] expr) | 计算数值列的平均值；加DISTINCT去重求平均 | avg(age) -> 年龄平均值；avg(DISTINCT amount) -> 去重金额平均值 |
| max | max(expr) | 获取列的最大值（支持数值/字符串/日期类型） | max(score) -> 最高分数；max(create_time) -> 最新创建时间 |
| min | min(expr) | 获取列的最小值（支持数值/字符串/日期类型） | min(price) -> 最低价格；min(name) -> 按字符串排序的最小姓名 |
| sum_distinct | sum(DISTINCT expr) | 专用去重求和（等价于sum(DISTINCT expr)） | sum_distinct(sales) -> 去重销售额总和 |
| avg_distinct | avg(DISTINCT expr) | 专用去重求平均（等价于avg(DISTINCT expr)） | avg_distinct(score) -> 去重分数平均值 |
| collect_list | collect_list(expr) | 将分组后的列值聚合为数组（不去重，保留顺序） | collect_list(imei) -> ["123","456","123"] |
| collect_set | collect_set(expr) | 将分组后的列值聚合为数组（去重） | collect_set(imei) -> ["123","456"] |
| variance | variance(expr) | 计算数值列的方差 | variance(score) -> 分数方差值 |
| stddev | stddev(expr) | 计算数值列的标准差（等价于stddev_samp） | stddev(salary) -> 薪资标准差 |
| stddev_pop | stddev_pop(expr) | 计算数值列的总体标准差 | stddev_pop(age) -> 年龄总体标准差 |
| stddev_samp | stddev_samp(expr) | 计算数值列的样本标准差 | stddev_samp(amount) -> 金额样本标准差 |
| var_pop | var_pop(expr) | 计算数值列的总体方差 | var_pop(score) -> 分数总体方差 |
| var_samp | var_samp(expr) | 计算数值列的样本方差 | var_samp(price) -> 价格样本方差 |
| percentile | percentile(expr, p) | 计算数值列的p分位数（p取值0~1） | percentile(salary, 0.5) -> 薪资中位数（50分位数） |
| percentile_approx | percentile_approx(expr, p[, B]) | 近似计算分位数（适合大数据集，B为精度参数） | percentile_approx(score, 0.9, 10000) -> 90分位数近似值 |
| corr | corr(expr1, expr2) | 计算两列的皮尔逊相关系数 | corr(sales, cost) -> 销售额与成本的相关系数 |
| covar_pop | covar_pop(expr1, expr2) | 计算两列的总体协方差 | covar_pop(age, salary) -> 年龄与薪资的总体协方差 |
| covar_samp | covar_samp(expr1, expr2) | 计算两列的样本协方差 | covar_samp(score, study_time) -> 分数与学习时长的样本协方差 |
内置表生成函数（UDTF）
| 函数名 | 语法格式 | 核心功能 | 示例&结果 |
|---|---|---|---|
| explode | explode(array/map) | 将数组/Map类型字段拆分为多行（UDTF）；数组拆分为单值行，Map拆分为key-value行 | explode(array('a','b','c')) -> 生成3行：a、b、c；explode(map('name','张三','age','18')) -> 生成2行：name-张三、age-18 |
| posexplode | posexplode(array) | 拆分数组并返回元素位置（索引从0开始）+元素值，生成两列（pos, val） | posexplode(array('a','b','c')) -> 生成3行：(0,a)、(1,b)、(2,c) |
| inline | inline(array<struct>) | 将结构体数组拆分为多行多列，结构体的每个字段对应一列 | inline(array(named_struct('id',1,'name','张三'), named_struct('id',2,'name','李四'))) -> 生成2行，每行含id/name两列：1-张三、2-李四 |
| stack | stack(n, col1, col2, ..., coln) | 将单行多列数据拆分为多行，n指定拆分后行数，需保证列数是n的整数倍 | stack(2, '张三',18,'李四',20) -> 拆分为2行，每行2列：(张三,18)、(李四,20) |
| json_tuple | json_tuple(json_str, k1, k2, ..., kn) | 一次性解析JSON字符串的多个字段，返回多列（替代get_json_object，效率更高） | json_tuple('{"name":"张三","age":18,"city":"北京"}', 'name','age') -> 返回两列：张三、18 |
| parse_url_tuple | parse_url_tuple(url, p1, p2, ..., pn) | 一次性解析URL的多个部分（PROTOCOL/HOST/PATH等），返回多列 | parse_url_tuple('https://www.baidu.com/s?wd=hive', 'PROTOCOL','HOST','PATH') -> 返回三列：https、www.baidu.com、/s |
实战：hive实现wordcount
1. 数据准备
[root@node4 data]# vim wc.txt
hello tom
andy joy
hello rose
hello joy
mark andy
hello tom
andy rose
hello joy2. 创建原始数据表
create table words(line string);3. 将数据文件wc.txt load到words表中：
hive> load data local inpath '/root/data/wc.txt' into table words;4. 编写sql语句，先将每行内容按照空格拆分
hive> select split(line,' ') from words;
OK
["hello","tom"]
["andy","joy"]
["hello","rose"]
["hello","joy"]
["mark","andy"]
["hello","tom"]
["andy","rose"]
["hello","joy"]
Time taken: 0.352 seconds, Fetched: 8 row(s)5. 将数组的元素再次进行拆解
hive> select explode(split(line,' ')) from words;
OK
hello
tom
andy
joy
hello
rose
hello
joy
......6. 按照单词进行分组，并统计数量
select word,count(word)
from (select explode(split(line,' ')) word from words) tmp
group by word;7. 创建结果表,并将结果添加到结果表中
hive>create table wc_count(word string,count int);
hive>from (select explode(split(line,' ')) word from words) tmp
insert into wc_count
select word,count(word)
group by word;UDF开发
UDF（User-Defined Function）就是用户自定义函数，是 Hive 为了满足内置函数无法覆盖的业务需求，允许开发者自定义的、可像内置函数一样调用的函数。
实战：对手机号进行数据脱敏
对手机号码列进行数据脱敏 13812345678->138****5678
1、 UDF函数可以直接应用于select语句，对查询结构做格式化处理后，再输出内容。
2、编写UDF函数的时候需要注意一下几点：
a）自定义UDF需要继承org.apache.hadoop.hive.ql.UDF。
b）需要实现evaluate函数， evaluate函数支持重载。
具体实现：
1. 创建一个maven项目hivedemo
2. 添加hive的依赖
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>3. 创建类PhoneDataMasking
package com.wusen.hadoop;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
public class PhoneDataMasking extends UDF {
//final用于禁止方法内修改入参
public Text evaluate(final Text tt){
if (tt == null || tt.toString().length() != 11){
return tt;
}
//获取手机号码
String phone = tt.toString();
//脱敏处理
String result = phone.substring(0,3) + "****" + phone.substring(7);
return new Text(result);
}
}
4. 将项目打成jar包，将jar包改名为hive1.jar
5. 将hive1.jar上传到node4的/root/data目录下
6. 进入hive客户端，添加jar
hive> add jar /root/data/hive1.jar;
hive> list jar;
/root/data/hive1.jar7. 创建函数
hive> create temporary function tuomin as 'com.wusen.hadoop.PhoneDataMasking';
8.测试
-- 创建虚拟表，并插入数据，如果数据表为空的话，后续Hive 只会输出 OK 和耗时，不会显示任何结果行。
hive> create table dual(id string);
hive> insert into dual values(" ");
-- 测试函数
hive> select tuomin('13812345678') from dual;
OK
138****56789.删除临时函数
hive> drop temporary function tuomin;10. 将hive1.jar上传到hdfs文件系统中的/usr
[root@node4 data]# hdfs dfs -put /root/data/hive1.jar /usr11. 使用hdfs文件系统/usr/hive1.jar创建临时函数
hive> create temporary function tuomin as 'com.wusen.hadoop.PhoneDataMasking' using jar 'hdfs://mycluster/usr/hive1.jar';12.测试
hive> select tuomin('13912345678') from dual;
OK
139****5678
汇聚全球AI编程工具，助力开发者即刻编程。
更多推荐
21
0- 0
已为社区贡献4条内容
所有评论(0)