hiveSql冷门但好用函数 --持续更新-Toy模板网

这篇具有很好参考价值的文章主要介绍了hiveSql冷门但好用函数 --持续更新。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

字符串函数

to_json

将STRUCT类型的数据转化为json格式字符串，此处需要另外学习一个named_struct()函数:自定义结构化数据的KV
named_struct(k1,v1,k2,v2…)
示例：

select named_struct("name","csdn","addr","beijing","age",12)

结果
to_json(结构化参数)
示例

select to_json(named_struct("name","csdn","addr","beijing","age",12))

结果
注意
1、需要注意的是，当结构化数据K-V中Value有null值时，有null值的K-V对在to_json中丢失

2、当结构化数据K-V中Key有null值时，named_struct函数报错

str_to_map

作用：使用两个分隔符将文本拆分为键值对。
语法：str_to_map(字符串参数, 分隔符1, 分隔符2)
分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 ‘,’，对于分隔符2默认分隔符是 ‘=’
示例：

select str_to_map('a=1,b=2,c=3',',','=');

hiveSql冷门但好用函数 --持续更新
配合to_json

select to_json(str_to_map('a=1,b=2,c=3',',','='));

hiveSql冷门但好用函数 --持续更新

json_tuple

上面说了如何生成json数据，接下来学习一个解析使用json数据的函数，例如遇到json数据中有多对kv值，想取出每个key，你还在写多个
select get_json_object(json_str,$.key1) as v1, get_json_object(json_str,$.key2) as v2
这样吗？漏漏漏！快使用json_tuple()函数，非常的哇塞！

作用：快速解析出json中多个key值
语法：json_tuple(json格式字符串, key1, key2, key3…)
示例：

select json_tuple('{"name":"i_mycode","age":18,"addr":"beijing"}','name','age','addr')

hiveSql冷门但好用函数 --持续更新

与lateral view配合使用：

with tmp as (
    select 'test' as flag, '{"name":"i_mycode","age":18,"addr":"beijing"}' as json_str 
    union all 
    select 'test1' as flag, '{"name":"i_mycode1","age":181,"addr":"beijing1"}' as json_str 
)
select a.*,b.* from tmp a 
lateral view json_tuple(json_str,'name','age','addr') b as name, age, addr;

结果：
注意：
区别于explode（）、posexplode（）函数，不会与原字段产生笛卡尔积效果

with tmp as (
    select 'test' as flag, '{"name":"i_mycode","age":18,"addr":"beijing"}' as json_str 
    union all 
    select 'test1' as flag, '{"name":"i_mycode1","age":181,"addr":"beijing1"}' as json_str 
)
select a.*,b.*,c.* from tmp a 
lateral view json_tuple(json_str,'name','age','addr') b as name, age, addr
lateral view posexplode(split(json_str,',')) c as num,json_s;

hiveSql冷门但好用函数 --持续更新

translate

对于字符串的替换需求，你是否还在replace(),regexp_replace()。替换多个目标的时候，是否还在嵌套使用regexp_replace(regexp_replace(srcStr, ‘A’,‘a’), ‘B’,‘b’)这样吗？
漏漏漏！快使用translate()函数，非常的奈斯！

作用：按照替换字符与被替换字符位置一一替换源字符中匹配到的被替换字符（有点难以理解，看举例）
语法：translate(input, from, to)
input：输入字符串【集是要被替换的字符串】
from：需要匹配的字符【即需要被替换的字符】，这里一定要注意是字符不是字符串
to ：用哪些字符来替换被匹配到的字符
注意点：这里from的字符与to字符在位置上存在一一对应关系，也就是from中每个位置上的字符用to中对应位置的字符替换。
示例：

select translate("abcdef abcdef","abcdef","123456");

结果为：123456 123456
解释：替换不是说把"abcdef"替换成"123456"，而是把a替换成1，把b替换成2，把c替换成3，把d替换成4，e替换成5，f替换成6，一一对应位置的去替换源字符串中匹配到的字符
注意：这时候就很容易想到，from长度等于to长度很好理解，一一对应字符去替换即可。
但是当from与to的长度不相等时，会怎么样呢？让我们逐一来看
1、当from长度大于 to长度

示例：

select translate("abcdef abcdef","abcdef","1234");

结果：1234 1234
解释：还是一一对应，a对应1，b对应2，c对应3，d对应4，e和f在"to"中没有字符可对应，则源字符串中匹配上ef时会被删除，其实也可以理解为对应的是空字符。

2、当from长度小于 to长度
示例：

select translate("abcdef abcdef","abcd","123456");

结果：1234ef 1234ef
解释：还是一一对应，a对应1，b对应2，c对应3，d对应4，"to"中的56在"from"中无字符对应，相当于没用，不用管它。

3、其实还能想到，如果from中有重复字符，to中对应的字符又不一样怎么办？
示例：

select translate("abcdef abcdef","aacd","1234");

结果：1b34ef 1b34ef
解释：可以看到from是’aacd’，to是’1234’。其中a有对应1 也对应2。这种情况只需要看第一个对应即可，后续对应皆不生效，但是占to中的一个字符位置。
因为占一个to中的位置，所以对应
不是：a对应1，c对应2，d对应3
而是：a对应1，c对应3，d对应4

find_in_set

说明：集合查找函数，返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0
语法: find_in_set(string str, string strList)
举例：

 select find_in_set('ab','ef,ab,de') ;

结果为：2

select find_in_set('at','ef,ab,de');

结果为：0

instr 和 locate

instr()

说明：字符串查找函数，返回字符串 substr 在 str 中首次出现的位置，找不到返回0
语法：
instr(string str, string substr) hive语法
instr( string str, string substr [, bigint position [, bingint occurrence ] ] ) impala语法
instr(sourceString , destString , start , appearPosition)
instr(‘源字符串’ , ‘目标字符串’ , ‘开始位置’ , ‘第几次出现’)
举例：

select 
 instr('abcd','c',1,1)       
,instr('abcdctc','c',1,2)    
,instr('abcdctc','c',1,3)
> 3  5  7

locate

说明：返回字符串 substr 在 str 中从 pos 后查找 , 首次出现的位置，找不到返回0
语法：locate(string substr, string str[, int pos])
举例：

select locate('uzi','Rnguzi') , locate('uzi','RngUzi') from test;
>4   0
 
select 
locate('a','abcd') 
,locate('a','abcda',2) 
,locate('b','abcd') 
,locate('f','abcd') 
,locate('20','222002');
>1   5   2   0   3

repeat

说明：重复字符串函数
语法：repeat(string str, int n)
举例：

select repeat('abc',5);
> abcabcabcabcabc

常与posexplode 或 explode联合使用，用于构建动态计算的行数或者索引，例如：

SELECT posexplode(split(repeat('1,', 3), ',')) as pos,index

pos	index
0	1
1	1
2	1

如果上述sql中的重复数字3是动态计算出的，比如两个日期的天数差等，就可以动态的得到不同数据的转换行了

array_distinct

说明：去重array中的元素
语法：array_distinct(array数据)
举例：

-- 自己经常用在sql优化中使用
-- 例如：在join后需要计算去重指标，往往join的数据量很大，这时可以先将需要去重的指标concat到字段中，
-- 降低join表的数据量，在join后split连接字段，再利用array_distinct()方法计算去重量，计算size即可。
-- 举个具体示例：
-- 流量表：每篇文章的pvuv
-- 文章维度表：文章多级标签，一级标签包含多个二级标签

-- 在流量表中先将每篇文章的pv和用户id concat连接
with view_tmp as (
select 
	articleid,
	sum(pv) as pv,
	wm_concat(',',userid) as userids
from
	(select
		articleid,
		userid,
		count(1) as pv
	from tmp_view_event
	group by articleid,userid
	) t
)

-- 标签表：tmp_article_label
-- articleid:文章id
-- label1:文章一级标签
-- label2:文章二级标签

-- 关联计算每个一级标签和二级标签的pvuv
select 
	b.label1, 
	nvl(b.label2,'ALL') as label2,
	sum(a.pv) as pv,
	size(array_distinct(split(wm_concat(',',userids),','))) as uv
from tmp_article_label a left join view_tmp b
on a.articleid = b.articleid
group by b.label1, b.label2
grouping sets ((b.label1),(b.label1, b.label2))

其中size(array_distinct(split(wm_concat(‘,’,userids),‘,’)))
将group by维度下的同一组中的每篇文章连接好的用户id再链接起来，得到同维度下的所有文章的用户id连接字符串，虽然在流量计算中view_tmp 将每篇文章的cid去重连接了，但是同一个用户可能看相同标签的不同文章，这样这多篇文章的用户id链接可能存在重复的用户id。
此时可以利用array_distinct()方法，将同标签下的不同文章的userids连接后，split()构造array,再array_distinct()去重这个array，size()计算长度，见下表：

view_tmp 中有

articleid	pv	userids
1	10	A,B,C,D
2	20	A,B,C,E

tmp_article_label有

articleid	label1	label1
1	编程语言	java
2	编程语言	python

在计算label1为编程语言的UV时候，group by label1，此时文章id：1和2都在编程语言分组内，
将他们的userids按照,concat得到：

label1	userids
编程语言	A,B,C,D,A,B,C,E

此时的A,B,C在这个维度分组中是重复的，split(userids ,‘,’)后再array_distinct()，再计算array的长度size()即是"编程语言"这个维度下的UV值。

label1	array_distinct(split(userids ,‘,’))	size(array_distinct(split(userids ,‘,’)))
编程语言	A,B,C,D,E	5

日期函数

next_day

这个函数属于是冷知识系列了，感觉很少能用上。文章来源地址https://www.toymoban.com/news/detail-500116.html

作用：返回指定日期的下一个最近的指定星期几的日期，返回的格式为yyyy-mm-dd。
语法：next_day(指定日期, 星期N)：
参数“星期N”可以是:
Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday
或
Mo,Tu,We,Th,Fr,Sa,Su
示例

select next_day('2022-07-28','Monday') as c1,next_day('2022-07-28','Mo') as c2 union all
select next_day('2022-07-28','Tuesday') as c1,next_day('2022-07-28','Tu') as c2 union all
select next_day('2022-07-28','Wednesday') as c1,next_day('2022-07-28','We') as c2 union all
select next_day('2022-07-28','Thursday') as c1,next_day('2022-07-28','Th') as c2 union all
select next_day('2022-07-28','Friday') as c1,next_day('2022-07-28','Fr') as c2 union all
select next_day('2022-07-28','Saturday') as c1,next_day('2022-07-28','Sa') as c2 union all
select next_day('2022-07-28','Sunday') as c1,next_day('2022-07-28','Su') as c2;