HIVE-字符串相关函数的使用

UPPER:转大写

hive (default)> select upper('low');
OK
_c0
LOW
Time taken: 0.258 seconds, Fetched: 1 row(s)

LOWER:转小写

hive (default)> select lower('LOW');
OK
_c0
low
Time taken: 0.127 seconds, Fetched: 1 row(s)

Length:长度

hive (default)> select length('Facebook');
OK
_c0
8
Time taken: 0.167 seconds, Fetched: 1 row(s)

TRIM:前后去除空格

hive (default)> select trim('   tst  ww  ');
OK
_c0
tst  ww
Time taken: 0.102 seconds, Fetched: 1 row(s)

lpad,rpad:左填充/右填充

截取字符串,当长度超过字符串的时候,进行拼接。一个是从左边进行拼接,一个是从右边进行拼接

hive (default)> select lpad('facebook',2,'ww');
OK
_c0
fa
Time taken: 0.142 seconds, Fetched: 1 row(s)

hive (default)> select rpad('facebook',12,'ww');
OK
_c0
facebookwwww
Time taken: 0.116 seconds, Fetched: 1 row(s)

regexp_replace:使用正则表达式匹配目标字符串,匹配成功后替换

hive (default)> select regexp_replace('2020/10/25','/','-');
OK
_c0
2020-10-25
Time taken: 0.09 seconds, Fetched: 1 row(s)
//例如date_add函数中不识别分隔符为/,只识别-,那么可以用这个函数进行替换

SIZE:返回集合中的元素个数

hive (default)> select * from test3;
OK
test3.name	test3.friends	test3.children	test3.address
songsong	["bingbing","lili"]	{"xiao song":18,"xiaoxiao song":19} {"street":"hui long guan","city":"beijing"}
yangyang	["caicai","susu"]	{"xiao yang":18,"xiaoxiao yang":19} {"street":"chao yang","city":"beijing"}
Time taken: 0.1 seconds, Fetched: 2 row(s)
hive (default)> select size(friends) from test3;
OK
_c0
2
2
Time taken: 0.124 seconds, Fetched: 2 row(s)

MAP_KEYS:返回map当中的key

hive (default)> SELECT MAP_KEYS(children) from test3;
OK
_c0
["xiao song","xiaoxiao song"]
["xiao yang","xiaoxiao yang"]
Time taken: 0.106 seconds, Fetched: 2 row(s)

MAP_VALUES:返回map当中的VALUES

hive (default)> SELECT MAP_VALUES(children) from test3;
OK
_c0
[18,19]
[18,19]
Time taken: 0.19 seconds, Fetched: 2 row(s)

ARRAY_CONTAINS:判断array中是否包含某个元素

第一个数组中有,第二个数组中没有,所以一个true,一个false

hive (default)> select array_contains(friends,'bingbing') from test3;
OK
_c0
true
false
Time taken: 0.074 seconds, Fetched: 2 row(s)

SORT_ARRAY:按照字段顺序排序

hive (default)> select sort_array(friends) from test3;
OK
_c0
["bingbing","lili"]
["caicai","susu"]
Time taken: 0.118 seconds, Fetched: 2 row(s)

hive (default)> select * from test3;
OK
test3.name	test3.friends	test3.children	test3.address
songsong	["bingbing","lili"]	{"xiao song":18,"xiaoxiao song":19} {"street":"hui long guan","city":"beijing"}
yangyang	["caicai","susu"]	{"xiao yang":18,"xiaoxiao yang":19} {"street":"chao yang","city":"beijing"}
Time taken: 0.08 seconds, Fetched: 2 row(s)