目录
官网;Spark SQL, Built-in Functions
字符串处理函数
1. 针对 k : v 类型字符串常用 keyvalue()
# 以 ; 或者 \; 分割的字符串
SELECT KEYVALUE('index:1;type:1;','xcard') ## 1
2.1 json字符串处理函数 get_json_object()
## 取值时必须用 $. 开头
SELECT get_json_object('{"email":"amy@_test.net","owner":"amy"}','$.owner') ## amy
2.2 字符串(json)转对象/固定结构 from_json(str,schema)
--和java的json转对象用法基本相同
--当然这里你可以转为json数组,或者固定字段都可以
--返回值是一个结构,其字段名称和类型与架构定义匹配
SELECT from_json('{"a":1, "b":0.8,"c":8}', 'a INT, b DOUBLE')
--"{\"a\":1,\"b\":0.8}"
--也可以取json数组生成新的json数组
SELECT from_json('[{"id":1,"name":"aa","age":16},{"id":2,"name":"bb","age":18}]'
, 'ARRAY<STRUCT<id:int,name:string>>')
--"[{\"id\":1,\"name\":\"aa\"},{\"id\":2,\"name\":\"bb\"}]"
2.3 转为json格式的字符串
select to_json(map('uid','12','name','小明'))
2.4 json一次取多个key转为元祖,元祖直接可以多列,多行转多列
SELECT json_tuple('{"1":1, "2":2, "3":3}', '1', '2','3') as (a,b,c)
2.5 判断是否是json数组
--Returns the number of elements in the outermost JSON array
--只判断最外层,里面是否包含json数组不处理,另外数组也是json数组?
select json_array_length('[{"col":1}]') -- 1
select json_array_length('[1,2,3,4]') --4
select json_array_length('{"col":1}') --null
select json_array_length('{"col":[1,2,3,4]}') --NULL
3. 解析 url 链接专用函数 parse_url()
## 这个很简单理解,就不实战了,看下官网文档吧
url = file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose
parse_url('url', 'HOST') = "example.com"
parse_url('url', 'PATH') = "/over/there/index.dtb"
parse_url('url', 'QUERY') = "type=animal&name=narwhal"
parse_url('url', 'QUERY', 'name') = "narwhal"
parse_url('url', 'REF') = "nose"
parse_url('url', 'PROTOCOL') = "file"
parse_url('url', 'AUTHORITY') = "username:password@example.com:8042"
parse_url('url', 'FILE') = "/over/there/index.dtb?type=animal&name=narwhal"
parse_url('url', 'USERINFO') = "username:password"
4.1 字符串中的转义字符
select 'https:\\\/\\\/www.baidu.com' --https:\/\/www.baidu.com
select 'https:\/\/www.baidu.com' --https://www.baidu.com
4.2 万能的正则匹配字符串处理 regexp_extract()
返回和正则可以匹配上的数据,从匹配上开始到匹配不上截断
## 截取出来匹配到的第 n 个字符串,下面的 1 代表第一个字符串
SELECT regexp_extract('foothebar', 'foo(.*?)(bar)', 1);
SELECT regexp_extract('index=7,url=www://plugin/LivePlugin?liveRoom=1000001,index=1,type=3,appkitContentId=', 'url=([^,]*)',1);
--[^,]* 看正则应该是非,的任意组合
SELECT regexp_extract('index=7,url=1000001,index=1,type=3,appkitContentId=', 'url=([0-9]*)',1);
需要注意的是regexp_extract的1并不是匹配到的第几个
而是匹配到的第一个数据的第一个(即每个匹配返回的数据类似数组)
如果要取全部可匹配上的数据
SELECT regexp_extract('100-200&&300-400', '(\\d+)-(\\d+)', 2); --返回值是200
SELECT regexp_extract_all('url=1000001,url=1000002', 'url=([0-9]*)',1);
4.2.1 正则表达式多个 () 表示多组匹配
SELECT regexp_extract('100-200', '(\\d+)-(\\d+)', 1); -- 100
SELECT regexp_extract('100-200', '(\\d+-\\d+)', 1); -- 100-200
4.3 模糊匹配的关键字 like 和 rlike,regexp
-- 两个函数功能上类似,like支持%匹配,rlike支持正则匹配
--regexp=rilke=oracle的regex_like,Hive官网描述same as rlike
-- 用法 A LIKE/RLIKE B,LIKE/RLIKE(A,B)
select 'football' like 'foot%';
select 'football' rlike '^foot';
4.4 正则匹配替换 REGEXP_REPLACE
--用法 regexp_replace(string A, string B, string C)
--将字符串 A 中的符合 java正则表达式B 的部分替换为 C
select regexp_replace('h234ney', '\\d+', 'o'); --返回honey
4.5 REGEXP_REPLACE 和 REPLACE
select replace('https:\\\/\\\/www.baidu.com','\\','')--https://www.baidu.com
select regexp_replace('https:\\\/\\\/www.baidu.com','\\','')--报错
select regexp_replace('https:\\\/\\\/www.baidu.com','\\\\','')--https://www.baidu.com
select regexp_replace('https:\\\/\\\/www.baidu.com','\\\\/','')--https:www.baidu.com
5. 字符切割函数:substr
select substr('abcde',1,3) ##得到abc,取下标从1开始数共3个字符
select substr('abcde',1) ## abcde
6. 字符串拼接:concat
注意:concat使用时任意字段为null返回结果都是null
select concat('aa', 11, 2.2); ## aa112.2
7. 字符串按分隔符拼接:concat_ws
select concat_ws('_','aa','11','bb'); ## aa_11_bb,参数只能为String类型
## 常和聚合函数 / 数组集合类型联合使用
select concat_ws('|',collect_set(uid)) ; ## 把uid聚合去重拼接
8. 字符串分割:split
select split('a,b,c',','); ## 返回值是数组 ["a","b","c"]
9. 字符串替换:regexp_replace
select regexp_replace ( 'a,b,c' , ',' , '|' ); ## a|b|c
10. 查找字符串出现的位置
--需要注意下,HIVE有些版本只支持两个参数
SELECT instr('say hello','s') -- 1,查不到返回0
--有些数据库可以,从第一个位置开始,找到第二个 l 的位置
SELECT instr('say hello','l',1,2) FROM dual; -- 8
11. Unicode 等编码的转换
select decode('\u0026','utf-8')--&
select url_decode('https:\u002f\u002fblog.csdn.net\u002fweixin_43283487\u002farticle\u002fdetails\u002f123815935') --https://blog.csdn.net/weixin_43283487/article/details/123815935
日期处理函数
特别注意:yyyy-MM-dd HH:mm:ss 标识符,每个字符的大小写其含义都会变化
固定日期转换成时间戳
select unix_timestamp('2016-08-16','yyyy-MM-dd') --1471276800
select unix_timestamp('20160816','yyyyMMdd') --1471276800
select unix_timestamp('2016-08-16T10:02:41Z', "yyyy-MM-dd'T'HH:mm:ss'Z'") --1471312961
16/Mar/2017:12:25:01 +0800 转成正常格式(yyyy-MM-dd hh:mm:ss)
select from_unixtime(to_unix_timestamp('16/Mar/2017:12:25:01 +0800', 'dd/MMM/yyy:HH:mm:ss Z'))
时间戳转换程固定日期
select from_unixtime(1471276800,'yyyy-MM-dd') --2016-08-16
select from_unixtime(1471276800,'yyyyMMdd') --20160816
select from_unixtime(1471312961) -- 2016-08-16 10:02:41
select from_unixtime( unix_timestamp('20160816','yyyyMMdd'),'yyyy-MM-dd') --2016-08-16
select date_format('2016-08-16','yyyyMMdd') --20160816
返回日期时间字段中的日期部分
select to_date('2016-08-16 10:03:01') --2016-08-16
类似sql 中的date
取当前时间
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')
--特别注意,hh只能处理到12,HH才能处理24h
select from_unixtime(unix_timestamp(),'yyyy-MM-dd')
返回日期中的年
select year('2016-08-16 10:03:01') --2016
返回日期中的月
select month('2016-08-16 10:03:01') --8
返回日期中的日
select day('2016-08-16 10:03:01') --16
返回日期中的时
select hour('2016-08-16 10:03:01') --10
返回日期中的分
select minute('2016-08-16 10:03:01') --3
返回日期中的秒
select second('2016-08-16 10:03:01') --1
返回日期在当前的周数
select weekofyear('2016-08-16 10:03:01') --33
返回结束日期减去开始日期的天数
select datediff('2016-08-16','2016-08-11')
返回开始日期startdate增加days天后的日期
select date_add('2016-08-16',10)
返回开始日期startdate减少days天后的日期
select date_sub('2016-08-16',10)
返回当天三种方式
SELECT CURRENT_DATE;
--2017-06-15
SELECT CURRENT_TIMESTAMP;--返回时分秒
--2017-06-15 19:54:44
SELECT from_unixtime(unix_timestamp());
--2017-06-15 19:55:04
返回当前时间戳
Select current_timestamp--2018-06-18 10:37:53.278
返回当月的第一天
select trunc('2016-08-16','MM') --2016-08-01
返回当年的第一天
select trunc('2016-08-16','YEAR') --2016-01-01
NUll 值处理
1. 返回第一个非空值:COALESCE
## 需要注意的是这是一个 UDF 函数,不是聚合函数
select coalesce(null,2,null) ##2
select coalesce(c1,c2,c3) ##返回3个字段中第一个非空数据
2. 空值转换函数:NVL
select NVL(c1,'非法数据') ## 字段从c1不为空为c1的值,否则返回'非法数据'
select NVL('a','b') ## a
select NVL(null,'b') ## b
3.控制判断 isnull
select isnull(null) ; ## 判断是否为null,返回boolean
select a is null ; ##两个sql一样,需要注意sql不能用 = 判断 null
数学函数
1. 四舍五入和取小数点:round
select round(1.4) ## 1
round(1.23454,2) ## 1.23 此种方式较为容易出现问题
2. 取整函数
select floor(1.99) ## 向下取整 1
select ceil(1.0001) ## 向上取整 2
3. 次幂函数
select pow(2,3) ## 8.0 ,2的3次方
4. 随机数函数
select rand(100) ##返回一个100以内的数字,常用于数据倾斜打散
5. 保留小数
select cast(180213781293.12312312312312 as decimal(10,2))
## 需要注意decimal(10,2) 代表一共有10位,后2位是小数点,看情况修改
数组处理函数
collect_list 和 collect_set 两个返回的都是数组
split返回也是数组
数组取值 arr[0],arr[1]
数组处理函数
select
parent_set [0] --数组取值
,sort_array(parent_set) --对array进行排序,只能升序,不能指定降序
,size(parent_set) --数组有几个元素
,array_contains(parent_set, node) --检索array中是否有当前node
from
(
select
split('1,2,3,4', ',') as arr
)
组合处理,筛选出数组前3个数据
select
if(
size(arr) < 3, --数据长度要 > 3
str,
--切割第一位到底3个分隔符前一位的字符串即可
substr(str, 1, instr(str, concat(',', arr [ 3 ])) -1)
)
from
(
select
'1,2,3,4' as str,
split('1,2,3,4', ',') as arr
) t
map
## 构造一个map
select map('uid','12','name','小明')
## map添加字段/多个map合并
select map_concat(map('uid','001','name','小明'),map('age', '18'))
## map强转string是这样的结构:[uid -> 001, name -> 小明]
select cast(map('uid','001','name','小明') as string)
## map转多行,会自动转为两个字段,且必须加()
explode(myMap) AS (myMapKey, myMapKey)
## lateral
lateral view explode(myMap) t AS myMapKey, myMapKey
常见的正则
整数regexp '^[0-9]+$'
小数或者整数 '^[0-9]+(.?[0-9]+)?$'
多维度聚合语法
Grouping Sets、CUBE、ROLLUP 3种语法
Grouping Sets:自定义多维分析聚合
--会对组合内的数据分别group,可以理解为,分别按照grouping的规则分组后再union所有的结果
SELECT a,b,c
FROM table
GROUP BY a,b,c GROUPING SETS((a,b,c),(a),());
等价于
SELECT a,b,c
FROM table
GROUP BY a,b,c
union all
SELECT a,null as b,null as c
FROM table
GROUP BY a
union all
SELECT null as a,null as b,null as c
FROM table
CUBE会枚举指定列的所有可能组合作为Grouping Sets
ROLLUP会以按层级聚合的方式产生Grouping Sets
GROUP BY a, b, c WITH CUBE 等价于
GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())
GROUP BY a, b, c WITH ROLLUP
等价于 GROUPING SETS((a,b,c),(a,b),(a), ())
另外还可以这样写(以前不支持的,spark3.2的官网已经支持了)
group by uid,cube(age,sex) 等价于
grouping sets((uid),(uid,age,sex),(uid,age),(uid,sex))
group by uid,ROLLUP(age,sex) 等价于
grouping sets((uid),(uid,age,sex),(uid,age))
grouping 函数,如果该字段是聚合函数非聚合时返回1
select if(grouping(sex) = 1,-9999,age) as age,sex from t group by age,sex with cube
高阶函数
tranform ,可以用lamda表达式,但是hive好像不支持,spark可以,后续待补充