hive函数用法以及场景

灯火落星桥

1105人浏览 · 2023-05-17 16:02:26

灯火落星桥 · 2023-05-17 16:02:26 发布

函数总览

Generic UDF

abs
add_months
aes_decrypt
aes_encrypt
array
array_contains
asserttrue
asserttrueoom
basearithmetic
basebinary
basecompare
basedti
basenumeric
basenwaycompare
basepad
basetrim
baseunary
between
bridge
bround
cardinalityviolation
case
cbrt
ceil
characterlength
coalesce
concat
concatws
currentauthorizer
currentdate
currentgroups
currenttimestamp
currentuser
date
dateadd
datediff
dateformat
datesub
decode
elt
encode
enforceconstraint
epochmilli
extractunion
factorial
field
floor
floorceilbase
formatnumber
fromutctimestamp
greatest
grouping
hash
if
in
inbloomfilter
index
infile
initcap
instr
internalinterval
lag
lastday
lead
leadlag
least
length
levenshtein
likeall
likeany
locate
loggedinuser
lower
lpad
ltrim
macro
map
mapkeys
mapvalues
mask.java
maskfirstn.java
maskhash.java
masklastn.java
maskshowfirstn.java
maskshowlastn.java
monthsbetween
murmurhash
namedstruct
nextday
nullif
nvl
octetlength
opand
opdivide
opdtiminus
opdtiplus
opequal
opequalns
opequalorgreaterthan
opequalorlessthan
opfalse
opgreaterthan
oplessthan
opminus
opmod
opmultiply
opnegative
opnot
opnotequal
opnotequalns
opnotfalse
opnotnull
opnottrue
opnull
opnumericminus
opnumericplus
opor
opplus
oppositive
optrue
paramutils
posmod
power
printf
quarter
reflect
reflect2
regexp
restrictinformationschema
round
rpad
rtrim
sentences
sha2
size
sortarray
sortarraybyfield
soundex
split
sqcountcheck
stringtomap
struct
structfield
substringindex
timestamp
tobinary
tochar
todate
todecimal
tointervaldaytime
tointervalyearmonth
totimestamplocaltz
tounixtimestamp
toutctimestamp
tovarchar
translate
trim
trunc
union
unixtimestamp
upper
utils
when
widthbucket

Generic UDTF

explode
getsplits
inline
jsontuple
parseurltuple
posexplode
replicaterows
stack

Generic UDAF

average
binarysetfunctions
bloomfilter
bridge
collectlist
collectset
computestats
contextngrams
correlation
count
covariance
covariancesample
cumedist
denserank
evaluator
firstvalue
histogramnumeric
lag
lastvalue
lead
leadlag
max
min
mkcollectionevaluator
ngrams
ntile
parameterinfo
percentileapprox
percentrank
rank
resolver
resolver2
rownumber
std
stdsample
streamingevaluator
sum
sumemptyiszero
variance
variancesample

1、hive查找函数的语法

desc function (extended) xxx;--函数描述

2、字符串处理

json字符串

json_tuple

SELECT json_tuple('{"name":"John", "age":30, "city":"New York"}', 'name', 'age', 'city')
这个SQL查询语句会从JSON字符串中提取出指定的键值对，例如在这个例子中，它会提取出"name"、"age"和"city"这三个键对应的值，分别是"John"、30和"New York"。

map字符串

str_to_map

该函数包含三个参数：

第一个参数待转换得字符串
第二个参数为键值对分隔符
第三个参数为键值分隔符

select str_to_map('key1=value1;key2=value2;key3=value3',';','=');
输出结果：{"key1":"value1","key2":"value2","key3":"value3"}

map

select map('key1',1,'222','2');
输出：{"key1":"1","222":"2"}

select map('key1',1,'222','2')['222'];
输出：2

数组

array

FUNC(n0, n1…)

select array(123,'43ds','few21');
结果：["123","43ds","few21"]
select array(123,345,3456);
结果：[123,345,3456]

array_contains

FUNC(array, value)
判断value是否在array中

select array_contains(array(1,2,3),'1');
结果：Argument type mismatch ''1'': "int" expected at function ARRAY_CONTAINS, but "string" is found

select array_contains(array('1','2','3'),1);
结果：Argument type mismatch '1': "string" expected at function ARRAY_CONTAINS, but "int" is found

select array_contains(array('1','2','3'),'1');
结果：true

加解密

加密 aes_encrypt

FUNC(input string/binary, key string/binary)。
可以使用 128、192 或 256 位的密钥长度。如果任一参数为 NULL 或键长度不是允许的值之一，则返回值为 NULL。

select aes_encrypt('ABC','1234567890123456');--输出一个二进制值
结果：展示为乱码
select base64(aes_encrypt('ABC','1234567890123456'));--将二进制数转换为字符串
结果：y6Ss+zCYObpCbgfWfyNWTw==

解密 aes_decrypt

FUNC(input binary, key string/binary)

select aes_decrypt(unbase64('y6Ss+zCYObpCbgfWfyNWTw=='),'1234567890123456');

固定分隔符拆分的字符串

substring_index(str, delimiter,result_count)

用于提取字符串中指定分隔符出现的位置之前或之后的子字符串。

select substring_index('a/b/c'，'/',1),substring_index('a/b/c'，'/',2),substring_index('a/b/c'，'/',3),substring_index('a/b/c'，'/',4),substring_index('a/b/c'，'/',-1)
输出：a
a/b
a/b/c
a/b/c
c

3、窗口函数

查找窗口内各种位置的值函数

last_value

SELECT id, name, age, last_value(city) OVER (ORDER BY id) as current_city FROM users;
这个SQL查询语句使用了Hive的last value函数，它可以返回一个窗口中最后一个非空值。
在这个例子中，使用了last value函数来获取每个用户的当前城市，它会根据id的顺序来确定每个用户的城市。

PIVOT函数（行转列函数）

在Hive中，可以使用PIVOT函数将同一个学生的不同学科成绩转换为一行记录，各个学科作为列。具体操作步骤如下：
创建包含学生学科成绩的表，假设表名为scores，包含以下列：

stu_id	subject	score
1	math	80
1	english	90
1	science	85
2	math	75
2	english	80
2	science	90

使用PIVOT函数将不同的学科转换为列，SQL语句如下：

SELECT stu_id, 
    MAX(CASE WHEN subject = 'math' THEN score ELSE NULL END) AS math_score, 
    MAX(CASE WHEN subject = 'english' THEN score ELSE NULL END) AS english_score, 
    MAX(CASE WHEN subject = 'science' THEN score ELSE NULL END) AS science_score 
FROM scores 
GROUP BY stu_id;

上述SQL语句中，MAX(CASE WHEN subject = ‘math’ THEN score ELSE NULL END)表示将math科目的成绩转换为列，MAX(CASE WHEN subject = ‘english’ THEN score ELSE NULL END)表示将english科目的成绩转换为列，以此类推。GROUP BY stu_id表示按照学生ID进行分组，将同一个学生的成绩转换为一行记录。

执行上述SQL语句，将不同学科的成绩转换为列，最终得到的结果如下：

stu_id	math_score	english_score	science_score
1	80	90	85
2	75	80	90

上述结果中，每个学生对应一行记录，不同学科的成绩分别对应一列，可以方便地进行数据分析和统计。

stack

按指定需求，将array转多列多行，灵活输出：stack(int num,array())
num 可以指定数组的几列要转行，但是 num 必须能被 array() 的长度 length 整除

select stack(1,'a','b','c','d');
+-------+-------+-------+-------+--+
| col0  | col1  | col2  | col3  |
+-------+-------+-------+-------+--+
| a     | b     | c     | d     |
+-------+-------+-------+-------+--+


select stack(2,'a','b','c','d');
+-------+-------+--+
| col0  | col1  |
+-------+-------+--+
| a     | b     |
| c     | d     |
+-------+-------+--+

select stack(4,'a','b','c','d');
+-------+--+
| col0  |
+-------+--+
| a     |
| b     |
| c     |
| d     |
+-------+--+

group_concat

用于将一列中多行的值通过指定的分隔符拼接为一个字符串输出

SELECT column1, group_concat(column2, ',') as concatenated_values
FROM table_name
GROUP BY column1;

collect_list和collect_set

用于将一列的多行值去重或者不去重合并为一个集合

--set 去重
SELECT column1, collect_set(column2) as unique_values
FROM table_name
GROUP BY column1;
--list 不去重
SELECT column1, collect_list(column2) as all_values
FROM table_name
GROUP BY column1;

求非数值字段空值比例

select avg(case when name is not null then 1 else 0 end) as fraction

lead

用于统计窗口内往下n行。lead(col1, n, default)。若往下n行为null，则取默认值

select lead(date,1,'2023-06-21');

lag

用于统计窗口内往上n行。lag(col1, n, default)。若往上n行为null，则取默认值

select lag(date,1,'2023-06-21');

4、时间相关的函数

时间格式转换

from_unixtime：将UNIX时间戳转换为日期/时间。

参数：时间戳 [,格式化字符串]

SELECT from_unixtime(1640995200,'yyyy-MM-dd HH:mm:ss.SSS');
结果：2022-01-01 00:00:00.000

yyyy-MM-dd HH:mm:ss：年-月-日时:分:秒
yyyy-MM-dd：年-月-日
HH:mm:ss：时:分:秒
yyyy-MM-dd HH:mm:ss.SSS：年-月-日时:分:秒.毫秒
yyyy-MM-dd’T’HH:mm:ss.SSS’Z’：ISO 8601格式，例如2019-01-01T00:00:00.000Z

unix_timestamp：将日期/时间转换为UNIX时间戳。

参数：时间 [,格式化字符串]

SELECT unix_timestamp('20220101','yyyyMMdd') FROM table_name;
结果：1640995200

yyyyMMdd、yyyy-MM-dd、yyyy-MM-dd HH:mm:ss

date：提取字符串中的日期部分，输出类型为字符串。

SELECT date('2022-01-01 00:00:00'),date('2022-01-01 00:00');
结果：2022-01-01，2022-01-01

to_date：将时间戳类型转换为日期类型。

SELECT to_date('2022-01-01 00:00:00'),to_date('2022-01-01 00:00');
结果：2022-01-01，2022-01-01

date_format：将日期/时间格式化为指定的字符串。

SELECT date_format('20220101','yyyy-MM-dd'),date_format('2022-01-01','yyyy/MM/dd');
结果：null，2022/01/01
格式有：
yyyy-MM-dd：年-月-日
yyyy-MM-dd HH:mm:ss：年-月-日 时:分:秒
yyyy-MM-dd HH:mm:ss.SSS：年-月-日 时:分:秒.毫秒
yyyy/MM/dd：年/月/日
yyyy/MM/dd HH:mm:ss：年/月/日 时:分:秒
yyyy/MM/dd HH:mm:ss.SSS：年/月/日 时:分:秒.毫秒
MM/dd/yyyy：月/日/年
MM/dd/yyyy HH:mm:ss：月/日/年 时:分:秒
MM/dd/yyyy HH:mm:ss.SSS：月/日/年 时:分:秒.毫秒
yyyy：4 位数的年份，如 2023。
yy：2 位数的年份，如 23。
MM：月份，范围从 01 到 12。
MMM：月份的缩写形式，如 Jan。
MMMM：月份的全名形式，如 January。
dd：天数，范围从 01 到 31。
d：天数，范围从 1 到 31。
HH：24 小时制的小时数，范围从 00 到 23。
hh：12 小时制的小时数，范围从 01 到 12。
mm：分钟数，范围从 00 到 59。
ss：秒数，范围从 00 到 59。
S：毫秒数。
EEE：星期的缩写形式，如 Mon。
EEEE：星期的全名形式，如 Monday。

trunc：将日期截断为指定的时间单位。

SELECT trunc('2022-02-05','YYYY'),trunc('2022-02-05','YY'),trunc('2022-02-05','MM'),trunc('2022-04-05 12:20','Q');
结果：2022-01-01,2022-02-01,2022-04-01
SELECT trunc(123.321,1),trunc(123.321,2),trunc(123.321,-1),trunc(123.321,-2)
结果：123.3 , 123.32 , 120 , 100
--格式
YY/YYYY：年份
Q：季度
MM：月份
--注意
截取数字时候第二个参数为正时从小数点后开始截取，为负时从个位向十位截取，并且不做四舍五入

直接提取日期

year：

提取日期的年份。

SELECT year(date_column) FROM table_name;

quarter：提取日期的季度。

SELECT quarter(date_column) FROM table_name;

month：提取日期的月份。

SELECT month(date_column) FROM table_name;
SELECT month('2022-01-01');

weekofyear：提取日期所在的周数。

select weekofyear('20230613'),weekofyear('2023-06-13');
结果：null,24

day：提取日期的天数。

SELECT day(date_column) FROM table_name;

dayofweek：提取日期所在的星期几。

select dayofweek('2023-06-13'); --输出3  当前为星期二
注意：该函数以星期天为起始 1

dayofmonth：提取日期的月份中的天数。

select dayofmonth('2023-06-13'); --输出13

hour：提取时间的小时数。

SELECT hour(time_column) FROM table_name;

minute：提取时间的分钟数。

SELECT minute(time_column) FROM table_name;
SELECT minute('12:34:56');

second：提取时间的秒数。

SELECT second(time_column) FROM table_name;
SELECT second('12:34:56');

current_date：返回当前日期。

SELECT current_date();

current_timestamp：返回当前时间戳。

SELECT current_timestamp();

last_day：返回指定日期所在月份的最后一天。

SELECT last_day('2022-01-15');
2022-01-31

next_day：返回指定日期之后的第一个指定星期几的日期。

SELECT next_day('2022-01-01', 'Monday');
2022-01-03
参数：
'MON' 或 'MONDAY'：表示星期一
'TUE' 或 'TUESDAY'：表示星期二
'WED' 或 'WEDNESDAY'：表示星期三
'THU' 或 'THURSDAY'：表示星期四
'FRI' 或 'FRIDAY'：表示星期五
'SAT' 或 'SATURDAY'：表示星期六
'SUN' 或 'SUNDAY'：表示星期日

计算日期

months_between：计算两个日期之间的月数差异。

SELECT months_between('2022-01-01', '2021-12-01'),months_between('2022-01-01', '2021-12-02');
1.0 , 0.96774

date_add：将指定的天数添加到日期。

SELECT date_add('2023-06-13', 7),date_add('2023-06-13', -7);
2023-06-20, 2023-06-06

date_sub：将指定的天数从日期中减去。

SELECT date_sub('2023-06-13', 7),date_sub('2023-06-13', -7);
2023-06-06, 2023-06-20

datediff：计算两个日期之间的天数差异。

SELECT datediff('2023-06-13', '2023-06-20'),datediff('2023-06-13', '2023-06-06');
-7 , 7

add_months：计算某个日期的前 n 月的日期

 select add_months('2023-06-23',1), add_months('2023-06-23',-1)
 结果：2023-07-23, 2023-05-23

5、数值类函数

abs

返回数字的绝对值

select abs(-3.14),abs(3.14);
结果：3.14,3.14

天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

deck.gl TripsLayer 出租车轨迹动画示例实战：从示例运行到着色器原理

TripsLayer 是 deck.gl 中用于渲染**随时间流动的轨迹动画**（如出租车行程、物流路径、迁徙路线）的核心图层。本文以仓库中的最小独立示例 [examples/website/trips](https://link.gitcode.com/i/1442af8f442f4cff39ff546e050fceb5) 为骨架，带你完成从安装依赖、启动应用、理解曼哈顿出租车数据格式，到深入

天工造物局

Foundry Anvil 中 eth_simulateV1 的 Blob 交易与 Blob Gas 字段规范化：原理、实现与测试验证

本篇技术指南以 [anvil-simulate-blob-transactions.md](https://link.gitcode.com/i/3d2ebe227262de169335eb290af80da8) 这条 changelog 条目为起点，深入剖析 Foundry Anvil 对 `eth_simulateV1` RPC 返回结果中 blob（EIP-4844）交易与 blob gas

天工造物局

LMCache CLI 实战：用 `lmcache kvcache clear` 管理运行中服务的 KV Cache 状态

本文是一份针对 LMCache 官方 CLI 命令 `lmcache kvcache` 的完整技术指南。该命令用于在**运行中的 LMCache 服务（MP HTTP server）**上管理 KV cache 状态，当前提供 `clear` 子命令，用于清空 L1（CPU 内存）层中缓存的全部 KV 数据。读完本文，你将掌握该命令的完整参数体系、退出码语义、终端/JSON/静默三种输出模式，并能

天工造物局

所有评论(0)

查看更多评论

灯火落星桥

@weixin_43581375

已为社区贡献1条内容

hive函数用法以及场景

灯火落星桥

函数总览

Generic UDF

Generic UDTF

Generic UDAF

1、hive查找函数的语法

2、字符串处理

json字符串

json_tuple

map字符串

str_to_map

map

数组

array

array_contains

加解密

加密 aes_encrypt

解密 aes_decrypt

固定分隔符拆分的字符串

substring_index(str, delimiter,result_count)

3、窗口函数

查找窗口内各种位置的值函数

last_value

PIVOT函数（行转列函数）

stack

group_concat

collect_list和collect_set

求非数值字段空值比例

lead

lag

4、时间相关的函数

时间格式转换

from_unixtime：将UNIX时间戳转换为日期/时间。

unix_timestamp：将日期/时间转换为UNIX时间戳。

date：提取字符串中的日期部分，输出类型为字符串。

to_date：将时间戳类型转换为日期类型。

date_format：将日期/时间格式化为指定的字符串。

trunc：将日期截断为指定的时间单位。

直接提取日期

year：

quarter：提取日期的季度。

month：提取日期的月份。

weekofyear：提取日期所在的周数。

day：提取日期的天数。

dayofweek：提取日期所在的星期几。

dayofmonth：提取日期的月份中的天数。

hour：提取时间的小时数。

minute：提取时间的分钟数。

second：提取时间的秒数。

current_date：返回当前日期。

current_timestamp：返回当前时间戳。

last_day：返回指定日期所在月份的最后一天。

next_day：返回指定日期之后的第一个指定星期几的日期。

计算日期

months_between：计算两个日期之间的月数差异。

date_add：将指定的天数添加到日期。

date_sub：将指定的天数从日期中减去。

datediff：计算两个日期之间的天数差异。

add_months：计算某个日期的前 n 月的日期

5、数值类函数

abs

所有评论(0)

温馨提示：您尚未绑定手机号

灯火落星桥