已复制
全屏展示
复制代码
登录 注册

Hive Spark 最常用函数指南


yuziyue
· 10 min read

函数文档查询

-- 查看所有的函数列表
SHOW FUNCTIONS;

-- 查看指定函数文档
DESCRIBE FUNCTION <function_name>;

-- 查看指定函数详细文档
DESCRIBE FUNCTION EXTENDED <function_name>;

一. 普通函数(UDF)

substring

substring(field, i, j)   截取字符串，从某个索引 i 开始，截取长度为 j 的子字符串，注意索引从 1 开始。

  • 当 i 为负数时，取最后一个字符
  • 当 j 超过长度时，取到最大的位置
SELECT substring("abcdefg", 1, 3) -- 从1开始，取3个字符
-- abc

SELECT substring("abcdefg", 2, 20) -- 从2开始，取20个字符
-- bcdefg

SELECT substring("abcdefg", -1, 1) -- 从最后一位开始，只能取到1个字符
-- g

SELECT substring("abcdefg", -1, 5) -- 从最后一位开始，只能取到取1个字符
-- g

substr

同 substring

substring_index

按关键字截取字符串，按照出现的第几次进行截取，substring_index(field, delimiter, times)，相当于特殊版本的 split

  • times为正：正向查找
  • times为负：反向查找
SELECT substring_index("ab_cd_ef_gx", '_', 1);  -- ab
SELECT substring_index("ab_cd_ef_gx", '_', 2);  -- ab_cd
SELECT substring_index("ab_cd_ef_gx", '_', 3);  -- ab_cd_ef
SELECT substring_index("ab_cd_ef_gx", '_', 4);  -- ab_cd_ef_gx 没有出现第4次，则取到最大长度


SELECT substring_index("ab_cd_ef_gx", '_', -1);  -- gx
SELECT substring_index("ab_cd_ef_gx", '_', -2);  -- ef_gx
SELECT substring_index("ab_cd_ef_gx", '_', -3);  -- cd_ef_gx
SELECT substring_index("ab_cd_ef_gx", '_', -4);  -- ab_cd_ef_gx 没有出现第4次，则取到最大长度

size

获取array类型数据的长度

SELECT size(split('a,b,c,d', ','))
-- 4

regexp_replace

正则搜索替换，匹配不成功则展示原内容

SELECT regexp_replace('school go to school', 'school$', 'class')
-- school go to class

SELECT regexp_replace('school go to school', 'beijing', 'class')
-- school go to school

regexp_extract

提取字符串中匹配的分组 regexp_extract(str, regexp[, idx])

  • str：待提取的字符串。
  • regexp：正则表达式。
  • idx：提取第几个分组，分组用括号()包含，可省略，默认为1，idx为0表示提取整个字符串。
-- 提取 URL 的参数部分
select regexp_extract('http://www.a.com/?name=zhang&age=10&wd=big', '.*\\?(.*)', 1)
-- name=zhang&age=10&wd=big


-- 分布提取 name age wd
select regexp_extract('http://www.a.com/?name=zhang&age=10&wd=big', '.*name=(.*?)&age=(.*?)&wd=(.*)', 1);
select regexp_extract('http://www.a.com/?name=zhang&age=10&wd=big', '.*name=(.*?)&age=(.*?)&wd=(.*)', 2);
select regexp_extract('http://www.a.com/?name=zhang&age=10&wd=big', '.*name=(.*?)&age=(.*?)&wd=(.*)', 3);

regexp

判断是否匹配

select 'bbbaaabbb' regexp  'abc'
-- false

coalesce

返回参数中的第一个非 NULL 值；如果所有值都为NULL，那么返回NULL

select COALESCE(NULL, NULL, '2')
-- 2

select COALESCE(NULL, NULL, NULL)
-- NULL

select COALESCE(NULL, '', 2)
-- ''

select COALESCE(NULL, 2)
-- 2

split

字符串切分

SELECT split('a,b,c,d,e', ',');
-- ["a","b","c","d","e"]

SELECT split('a,b,c,d,e', ',')[0];
-- a

SELECT split('a,b,c,d,e', ',')[1];
-- b

SELECT split('a,b,c,d,e', ',')[5];
-- NULL

get_json_object

当获取的字段不存在时，返回 NULL，这种方式一次只能取一个字段，如需一次取多个字段，可使用json_tuple

select get_json_object('{"a": {"a1":"1", "a2":"2"}, "b":2}', '$.a')
-- {"a1":"1","a2":"2"}

select get_json_object('{"a": {"a1":"1", "a2":"2"}, "b":2}', '$.a.a1')
-- 1

select get_json_object('{"a": {"a1":"1", "a2":"2"}, "b":2}', '$.a.a3')
-- NULL

json_tuple

只能取最外的一层，跨层的话返回 NULL

  • json_tuple 单独使用
SELECT JSON_TUPLE('{"a": {"a1":"1", "a2":"2"}, "b":2}', 'a'); -- {"a1":"1","a2":"2"}
SELECT JSON_TUPLE('{"a": {"a1":"1", "a2":"2"}, "b":2}', 'b'); -- 2
SELECT JSON_TUPLE('{"a": {"a1":"1", "a2":"2"}, "b":2}', 'a.a1'); -- null

SELECT JSON_TUPLE('{"a": {"a1":"1", "a2":"2"}, "b":2}', 'a', 'b'); -- 同时取多个字段，结果需要配合LATERAL VIEW使用
  • json_tuple 与 LATERAL VIEW 混用，可以同时取多个字段
SELECT value_a, value_b
FROM (
         SELECT '{"a": {"a1":"1", "a2":"2"}, "b":2}' AS info
     ) t
         LATERAL VIEW JSON_TUPLE(info, 'a', 'b') fields AS value_a, value_b
-- {"a1":"1","a2":"2"},2


SELECT value_a_1, value_a_2, value_b
FROM (
         SELECT '{"a": {"a1":"1", "a2":"2"}, "b":2}' AS info
     ) t
         LATERAL VIEW JSON_TUPLE(info, 'a', 'b') tmp1 AS value_a, value_b
         LATERAL VIEW JSON_TUPLE(value_a, 'a1', 'a2') tmp2 AS value_a_1, value_a_2
-- 1,2,2


SELECT value_a_info, value_b
FROM (
         SELECT '{"a": "a,b,c,d,e", "b":2}' AS info
     ) t
         LATERAL VIEW JSON_TUPLE(info, 'a', 'b') tmp1 AS value_a, value_b
         LATERAL VIEW EXPLODE(split(value_a, ',')) tmp2 AS value_a_info
-- result
a,2
b,2
c,2
d,2
e,2

instr

返回字符串中 子字符串 出现的位置，位置从 1 开始，没有找到 子字符串 则返回0

select instr("abcde",'b')
-- 2

select instr("abcde",'x')
-- 0

select instr("abcde",'ce')
-- 0

select instr("abcde",'cd')
-- 3

lpad

字符串左边补足多少个字符

SELECT LPAD('12', 5, 'x') -- xxx12

rpad

字符串右边补足多少个字符

SELECT RPAD('12', 5, 'x') -- 12xxx

pmod

语法: pmod(int a, int b), pmod(double a, double b)

返回值: int double

说明: 返回正的 a 除以 b 的余数

SELECT PMOD(210, 3) -- 0
SELECT PMOD(2.10, 0.03) -- 0.0
SELECT PMOD(5, 10) -- 5

sort_array

  • 只能是升序
SELECT sort_array(COLLECT_SET(score))
FROM (SELECT NULL) t
         LATERAL VIEW EXPLODE(
                 ARRAY(10, 20, 20, 30, 10)
             ) t AS score;
-- [10,20,30]


SELECT sort_array(collect_list(score))
FROM (SELECT NULL) t
         LATERAL VIEW EXPLODE(
                 ARRAY(10, 20, 20, 30, 10)
             ) t AS score;
-- [10,10,20,20,30]

sort_array_by

  • 可以倒序：倒序需要引入struct，然后指定struct的第哪个字段来排序
-- 把属性包装到struct里面，默认生成的struct名称依次为 col1 col2 ...
SELECT sort_array_by(collect_list(score), 'col1', 'DESC')
FROM (SELECT NULL) t
         LATERAL VIEW EXPLODE(
                 ARRAY(struct(10), struct(20), struct(20), struct(30), struct(10))
             ) t AS score;
-- [{"col1":30},{"col1":20},{"col1":20},{"col1":10},{"col1":10}]

printf

格式化字段

SELECT PRINTF("%s---%s", score, score)
FROM (SELECT NULL) t
         LATERAL VIEW EXPLODE(
                 ARRAY(10, 20, 20, 30, 10)
             ) t AS score;
-- 结果
 10---10 
 20---20 
 20---20 
 30---30 
 10---10

nvl

NVL(expr1, expr2)

如果 expr1 不为 null，返回 expr1

如果 expr1 为 null，返回 expr2

二. 聚合函数(UDAF)

collect_set

将 colname 指定的列值聚合为一个无重复元素的数组。

  • 返回值说明：返回 ARRAY 类型。colname 值为 NULL 时，该行不参与计算。
SELECT COLLECT_SET(score)
FROM (SELECT NULL) t
         LATERAL VIEW EXPLODE(
                 ARRAY(10, 20, 20, 30, 10)
             ) t AS score;

-- [10,20,30]

collect_list

将指定的列聚合为一个数组。

  • 返回值说明：返回 ARRAY 类型。colname 值为 NULL 时，该行不参与计算。
SELECT collect_list(score)
FROM (SELECT NULL) t
         LATERAL VIEW EXPLODE(
                 ARRAY(10, 20, 20, 30, 10)
             ) t AS score;
-- [10,20,20,30,10]

percentile

中位数

求中位数又称中值，是按顺序排列的一组数据中居于中间位置的数，可以通过把所有观察值高低排序后找出正中间的一个作为中位数。如果观察值有偶数个，通常取最中间的两个数值(n1 n1)的平均数作为中位数=n1+(n2-n1)*p。参考：https://www.zhihu.com/question/67763556?sort=created

p位数

中位数表示中间位，此时p值为0.5，其实还有其他0~1之间的位数值分界点，叫做p位数。

命令说明

计算精确百分位数，适用于小数据量。先对指定列（int类型）升序排列，然后取精确的第p位百分数。p必须在0和1之间。percentile是从编号0开始计算，例如某列数据为100、200、300，列数据的编号顺序为0、1、2，计算该列的0.3百分位点，percentile结果是2×0.3=0.6，即值位于编号0和1之间，结果为100+(200-100)×0.6=160。

参数说明
  • colname：必填。值为BIGINT类型的列。
  • p：必填。需要精确的百分位数。取值为[0.0,1.0]。
返回值说明
  • 返回 DOUBLE 或 ARRAY 类型。
SELECT percentile(score, ARRAY(0.1, 0.5, 0.8))
FROM (SELECT NULL) t
         LATERAL VIEW EXPLODE(
                 ARRAY(10, 20, 60, 70, 80, 90, 30, 40, 50, 100)
             ) t AS score

-- 结果: [19.0,55.0,82.0]
-- 计算0.1分位举例: 10到100升序排序，然后编号0到9，0.1*9=0.9，表示要取编号为0到1之间的数即 10+(20-10)*(0.9-0)=19.0
-- 计算0.8分位举例: 10到100升序排序，然后编号0到9，0.8*9=7.2，表示要取编号为7到8之间的数即 70+(80-70)*(7.2-7)=82.0

SELECT percentile(score, 0.5)
FROM (SELECT NULL) t
         LATERAL VIEW EXPLODE(
                 ARRAY(10, 20, 60, 70, 80, 90, 30, 40, 50, 100)
             ) t AS score

-- 结果: 55.0

percentile_approx

命令说明

计算近似百分位数，适用于大数据量。先对指定列升序排列，然后取第p位百分数对应的值。percentile_approx是从编号1开始计算，例如某列数据为100、200、300，列数据的编号顺序为1、2、3，计算该列的0.6百分位点，percentile_approx结果是3×0.6=1.8，即值位于编号1和2之间，结果为100+(200-100)×0.8=180。

percentile_approx与percentile的区别

percentile_approx用于计算近似的百分位数，percentile用于计算精确的百分位数。在数据量较大时，percentile可能会因内存限制而执行失败，而percentile_approx无此问题。

命令格式
-- 返回浮点数
double percentile_approx (double <colname>, <p> [, <B>])) 

-- 以数组形式返回多个百分位近似计算结果。
array<double> percentile_approx (double <colname>, array(<p1> [, <p2>...]) [, <B>])
参数说明
  • colname：必填。值为DOUBLE类型的列。
  • p：必填。需要近似的百分位数。取值为[0.0,1.0]。
  • B：精度参数。数字越大精度越高产生的近似值误差越小。如果不设置该参数，默认值为10000。

三. 扩展函数(UDTF)

即扩展函数的输出是一个新的表。

explode

功能：将 array 或者 map 炸开，通常和 lateral view 混合使用。

注意：在 select 里面只能出现一个 EXPLODE 字段，如果要同时列出其他字段，则需要使用 lateral view。

  • array
SELECT EXPLODE(detail) as info
FROM (
         SELECT "zhang" AS username,
                SPLIT('a,b,c,d,e,', ',') AS detail
     ) t
-- 结果
a
b
c
d
e
""

-- 如下 SQL 会报错
SELECT username,
       EXPLODE(detail)
FROM (
         SELECT "zhang" AS username,
                SPLIT('a,b,c,d,e,', ',') AS detail
     ) t
  • map
SELECT key as mykey, value as myvalue
FROM (
         SELECT EXPLODE(STR_TO_MAP('name:zhang,age:12,address:beijing gu gong'))
     ) t

posexplode

功能：将array炸开，同时返回下标

注意：不能炸开map，因为map是无序的，返回下标无意义。

SELECT POSEXPLODE(detail)
FROM (
         SELECT "zhang" AS username,
                SPLIT('a,b,c,d,e,', ',') AS detail
     ) t
-- 结果
0,a
1,b
2,c
3,d
4,e
5,""

lateral view

功能：能够将一行数据拆成多行数据，通常配合split、explode、json_tuple使用

原理：LATERAL VIEW 对遇到的每一行，首先会按 UDTF 表达式进行处理，展开成若干行（可能是零行），然后将这些输出行与输入行 INNER JOIN。如果要保留输出为 NULL 的行，则需使用 LATERAL VIEW OUTER 执行 OUTER JOIN。

  • 命令格式
from <baseTable> lateral view [outer] <udtf_name>(<expression>) <table_alias> as <columnAlias> (',' <columnAlias>)
  • 使用示例
SELECT username,
       info
FROM (
         SELECT "zhang" AS username,
                SPLIT('a,b,c,d,e,', ',') AS detail
     ) t LATERAL VIEW EXPLODE(detail) tmp AS info
-- 结果
zhang,a
zhang,b
zhang,c
zhang,d
zhang,e
zhang,""


SELECT username,  -- POSEXPLODE 同时返回下标
       indexOf,
       info
FROM (
         SELECT "zhang" AS username,
                SPLIT('a,b,c,d,e,', ',') AS detail
     ) t LATERAL VIEW POSEXPLODE(detail) tmp AS indexOf, info
-- 结果
zhang,0,a
zhang,1,b
zhang,2,c
zhang,3,d
zhang,4,e
zhang,5,""

  • 参考资料 hive官方文档

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

🔗
阅读原文：https://yuchaoshui.com/0a77ce60ed48c7b37a946c077a42725f/
上一篇

Airflow任务与DAG执行监控实践

当某个 task 执行完毕后，如果 task 失败则发送报警，在 default_args 里面配 on_failure_callback。 当某个 dag 执行完毕后，如果 dag 失败则发送报警，在 DAG 参数里面配置 on_failure_callback。如果某个 dag 一直运行着且不报错，这时我们怎么知道它是什么状态呢？
下一篇

CentOS7搭建DHCP服务

查看MAC地址方法：启动dhcpd服务程序，为主机动态分配一个 IP 地址，这时在DHCP 服务器的日志文件(/var/log/messages)中会保存分配记录，可以从该文件中获取 MAC 地址。编辑配置文件/etc/dhcp/dhcpd.conf，MAC地址绑定配置文件如下。如果需要IP地址和MAC地址绑定，使用如下配置。

文章推荐

到底什么是ChatGPT的函数调用function calling

function calling 这个词一听还以为是 GPT 帮我去调用函数，实际上并不是这样。 使用场景 我们在和 GPT 聊天的过程中，可能会涉及一些特定的问题，比如在聊天时，我突然问 GPT ：北京今天天气怎么样？ 很显然 GPT 是不知道实时的天气的，那怎么办呢，用...

· 3 min read

OpenAI ChatGPT总结上下

一. 总结聊天标题 * 在用户发起一次聊天后总结对话标题 { "messages": [ { "role": "user", "content": "Python中，两个字典如何合并到一个字典里面" }, { "role": "assistant", "content": "在Python中，你可以使用`update()`。。。这两种方法都可以将两个字典合并到一个字典中。" }, { "role": "user", "content": "使用6到7个字直接返回这句话的简要主题，...

· 6 min read

Gradio如何获取客户端信息

原始app.py应用 我编写了一个简单Gradio应用，代码如下（server_app.py）： import gradio as gr def gradio_submit_fn(arg1, arg2): return f"arg1:...

· 4 min read

Airflow删除taskinstance实例

Airflow删除taskinstance实例，当Airflow运行很久后（或者你有分钟级别的dag），你会发现无法打开 task instances页面，那是因为 task instances 数量太多了，需要手动删除过去无用的task instances，Airflow提供了api来实现，将其加入定时任务每天删除就行，最后不要手动操作数据库。...

· 1 min read

openai API 接口 ChatGPT 提示词优化

openai API 接口 ChatGPT 提示词优化，给出更清晰更具体的提示词，使用分隔符delimiters，避免用户输入的变量中包含了不可预测的提示词，所以最好将用户的输入用分隔符包裹着，返回结构化数据：html、 Markdown、 json 等。...

· 5 min read

NVIDIA显卡驱动、cuda、cudnn概念梳理

一. 显卡驱动 很简单，显卡驱动是告诉系统如何调用显卡这个硬件。通常指NVIDIA Driver，一个驱动软件。下载地址：https://www.nvidia.com/Download/index.aspx CUDA（见后文） 有两个主要的 API，分别是runtime API 和 driver API。...

· 7 min read