Hive string functions

最新推荐文章于 2024-04-26 15:18:40 发布

sunnyGoGoGo

最新推荐文章于 2024-04-26 15:18:40 发布

阅读量186

点赞数

文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/m0_68225554/article/details/125105423

版权

String Functions

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

1.ascii(string)
返回第一个字符代表的美国信息交换标准码
返回类型：int
select ascii('A');
+------+--+
| _c0 |
+------+--+
| 65 |
+------+--+

2.base64(binary bin)
将二进制转换为base64编码
返回类型：string
select base64(encode('A','utf-8'));
+-------+--+
| _c0 |
+-------+--+
| QQ== |
+-------+--+

3.character_length(string str)
--2.2.0版本可用

4. concat(string|binary A, string|binary B...)
拼接字符串
返回类型：string
select concat('A','B','C','D');
+-------+--+
| _c0 |
+-------+--+
| ABCD |
+-------+--+

5.context_ngrams(array<array<string>>, array<string>, int K, int pf)

6.concat_ws(string SEP, string A, string B...)
concat_ws(string SEP, array<string>)
拼接字符串，用SEP连接A，B。。。
返回类型：string
select concat_ws(':','age','10');
+---------+--+
| _c0 |
+---------+--+
| age:10 |
+---------+--+
select concat_ws(',',phones) from array_test;
+-------------------------------------+--+
| _c0 |
+-------------------------------------+--+
| 1360000000,13900000000,13812345678 |
| 1360000123,13900000034 |
| 1360000034,13900000002 |
| 1360000001,13900000003 |
+-------------------------------------+--+

7.find_in_set(string str, string strList)
查找str在strList中的位置
返回类型：int
select find_in_set('apple','banana,apple');
+------+--+
| _c0 |
+------+--+
| 2 |
+------+--+

8.format_number(number x, int d)
格式化数字x为包含d个小数位的数字
返回类型：string
select format_number(12345.56,1);
+-----------+--+
| _c0 |
+-----------+--+
| 12,345.6 |
+-----------+--+

9.get_json_object(string json_string, string path)
提取json对象值
返回类型：string
select get_json_object(
'{"store":
{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"}
},
"email":"amy@only_for_json_udf_test.net",
"owner":"amy"
}',
'$.store.fruit[0].weight'
);
+------+--+
| _c0 |
+------+--+
| 8 |
+------+--+

10. json_tuple()
select json_tuple(
'{"store":
{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"}
},
"email":"amy@only_for_json_udf_test.net",
"owner":"amy"
}',
'store',
'owner'
);
+----------------------------------------------------+------+--+
| c0 | c1 |
+----------------------------------------------------+------+--+
| {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"bicycle":{"price":19.95,"color":"red"}} | amy |
+----------------------------------------------------+------+--+

11.in_file(string str, string filename)
如果str是文件filename里面的一行则返回true，否则返回false
返回类型：boolean
select in_file('47291,1960-09-09,Ulf,Flexer,M,2000-01-12','/root/employees/emp_2000/000000_0');
+-------+--+
| _c0 |
+-------+--+
| true |
+-------+--+

12.instr(string str, string substr)
查找substr在str的位置
返回类型：int
select instr('apple','p');
+------+--+
| _c0 |
+------+--+
| 2 |
+------+--+

13.field(val T,val1 T,val2 T,val3 T,...)
查找val在后面各值中的位置
返回类型：int
select field('apple','pear','banana','apple');
+------+--+
| _c0 |
+------+--+
| 3 |
+------+--+

14.length(string A)
字符串的长度
返回类型：int
select length('big data');
+------+--+
| _c0 |
+------+--+
| 8 |
+------+--+

15.locate(string substr, string str[, int pos])
查找substr在str中位置pos之后第一次出现的位置，包含pos
返回类型：int
select locate('a','big data','7');
+------+--+
| _c0 |
+------+--+
| 8 |
+------+--+

16.lower(string A) lcase(string A)
变小写
返回类型：string
select lower('BIG Data');
+-----------+--+
| _c0 |
+-----------+--+
| big data |
+-----------+--+

17.lpad(string str, int len, string pad)
往字符串str左边加字符串pad，直到长度为len
返回类型：string
select lpad('data',5,'big');
+--------+--+
| _c0 |
+--------+--+
| bdata |
+--------+--+

18.ltrim(string A)
修剪字符串A左边的所有空格
返回类型：string
select ltrim(' big data');
+-----------+--+
| _c0 |
+-----------+--+
| big data |
+-----------+--+

19.ngrams(array<array<string>>, int N, int K, int pf)

20.parse_url(string urlString, string partToExtract [, string keyToExtract])
从URL返回指定的部分。partToExtract的有效值包括HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE和USERINFO。
返回类型：string
select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1');
+------+--+
| _c0 |
+------+--+
| v1 |
+------+--+

21.printf(String format, Obj... args)
返回printf-style的字符串
返回类型：string
select printf('%s%d%s','现在是',6,'月');
+--------+--+
| _c0 |
+--------+--+
| 现在是6月 |
+--------+--+

22.regexp_extract(string subject,string pattern,int index)
将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符
idx是返回结果取表达式的哪一部分默认值为1。
0表示把整个正则表达式对应的结果全部返回
1表示返回正则表达式中第一个() 对应的结果以此类推。
返回类型：string
select regexp_extract('bigdata','b(.*)(data)',2);
+-------+--+
| _c0 |
+-------+--+
| data |
+-------+--+

23.regexp_replace
正则替代
返回类型：string
select regexp_replace('bigdata','d','D');
+----------+--+
| _c0 |
+----------+--+
| bigData |
+----------+--+

24.repeat(string str, int n)
重复
返回类型：string
select repeat('BigData',2);
+-----------------+--+
| _c0 |
+-----------------+--+
| BigDataBigData |
+-----------------+--+

25.reverse(string A)
反转
返回类型：string
select reverse('12345');
+--------+--+
| _c0 |
+--------+--+
| 54321 |
+--------+--+

26.rpad(string str, int len, string pad)
往字符串str右边加字符串pad，直到长度为len
返回类型：string
select rpad('apple',10,'big');
+-------------+--+
| _c0 |
+-------------+--+
| applebigbi |
+-------------+--+

27.rtrim(string A)
删除字符串右边的空格
返回类型：string
select rtrim('apple ');
+--------+--+
| _c0 |
+--------+--+
| apple |
+--------+--+

28.sentences(string str, string lang, string locale)
将每一个句子划分为单词数组
返回类型：array<array<string>>
select sentences('Life is short. Go where you feel love.');
+----------------------------------------------------+--+
| _c0 |
+----------------------------------------------------+--+
| [["Life","is","short"],["Go","where","you","feel","love"]] |
+----------------------------------------------------+--+

29.space(int n)
返回n个空格
返回类型：string
select space(5);
+--------+--+
| _c0 |
+--------+--+
| |
+--------+--+

30.split(string str, string pat)
用pat切分字符串
返回类型：array
select split('java,hadoop,python,hive',',');
+------------------------------------+--+
| _c0 |
+------------------------------------+--+
| ["java","hadoop","python","hive"] |
+------------------------------------+--+

31.str_to_map(text[, delimiter1, delimiter2])
将字符串转换成map
delimited1是map与map之间的符号,delimited2是map中k和v之间的符号
返回类型：map<string,string>
select str_to_map('cat:miao,dag:wang',',',':');
+------------------------------+--+
| _c0 |
+------------------------------+--+
| {"cat":"miao","dag":"wang"} |
+------------------------------+--+

32.substr(string|binary A, int start) substring(string|binary A, int start)
从指定位置切割字符串
返回类型：string
select substr('123456789',4);
+---------+--+
| _c0 |
+---------+--+
| 456789 |
+---------+--+

33.substr(string|binary A, int start, int len) substring(string|binary A, int start, int len)
从指定位置切割指定长度字符串
返回类型：string
select substr('123456789',4,3);
+------+--+
| _c0 |
+------+--+
| 456 |
+------+--+