SQL ON Hadoop-Hive（六）-字符串函数+行列转换

一.hive常用字符串函数

1.字符串反转函数reverse(string A)  --返回值类型string
   select reverse('gian') from iteblog;	--'naig'

2.带分隔符字符串连接函数concat_ws(string sep,string a,string b,...) --返回值类型string
select concat_ws('.','www','iteblog','com') from iteblog
concat(string sep, array<string>)		--参数为字符串数组
concat(object1,object2,...)			--默认连接符是''


3.正则表达式替换函数regexp_replace(string a,string b,string c) --返回值类型string
将字符串a中符合Java正则表达式b的部分替换为c

4.正则表达式解析函数regexp_extract(string subject,string pattern,int index)将字符串subject按照pattern正则表达式规则拆分,返回index指定的字符(0,显示与之匹配的整个字符串,1显示第一个括号里面的字段,...)
select
regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',0),  -- x=18abc
regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([a-z]+)([0-9]+)',0),  -- x=a3
regexp_extract('https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive','id=([0-9]+)',0),    -- id=522228774076
regexp_extract('https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive','id=([0-9]+)',1)   -- 522228774076
from test.dual;

5.url解析函数parse_url(string urlString, string partToExtract[,stringkeyToExtract])     --返回值类型string,返回url中指定的部分
--partToExtract的有效值为：HOST,PATH,QUERY,REF,PROTOCOL,AUTHORITY,FILE,USERINFO
hive>select parse_url('http://iteblog.com?weixin=iteblog_hadoop','HOST') from iteblog;
iteblog.com
hive>select parse_url('http://iteblog.com?weixin=iteblog_hadoop','QUERY','weixin') from iteblog;
iteblog_hadoop

6.json解析函数get_json_object(string json_string, string path)  --返回值类型string
解析json字符串json_string返回path指定内容,如果输入的json字符串无效返回NULL
hive>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"}','$.owner') from iteblog;
>amy

7.首字符ascii(string str)  --返回值int,返回字符串str第一个字符ascii码

8.左补足函数lpad(string str, int len, string pad)   --返回值类型string,将str用pad进行左补足到len位
hive>select lpad('abc',10,'td') from iteblog;
tdtdtdtabc

9.集合查找函数:find_in_set(string str, string strList) --返回值类型int,返回str在strlist第一次出现的位置,strlist是用逗号分割的字符串.如果没有找该str字符返回0
hive>select find_in_set('ab','ef,ab,de') from iteblog;
2
hive>select find_in_set('at','ef,ab,de') from iteblog;
0

9.encode(string src, string charset)  --用指定的字符集将第一个参数编码成binary,如果任一参数为null,则返回null
--可选字符集'US_ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'

10.format_number(number x, int d)   --将数字x格式化为'#,###,###.##',四舍五入为d位小数位,将结果作为字符串返回.如果d=0,结果不包含小数点或小数部分

11.locate(string substr, string str[, int pos]) --返回substr在str的位置pos后第一次出现位置,返回值类型int

12.base64(binary bin)    --将二进制参数转换为base64字符集

13.unbase64(string str)  --将base64字符串转换为二进制

14.translate(string input, string form, string to)  --将input中出现在from中的字符替换为to中的字符串，如果任何参数为null，结果为null

15.str_to_map(text	[,delimiter1,delimiter2])   --使用两个分隔符将文本分割为键值对.第一个分隔符将文本分割为K-V 对,第二个分隔符分隔每个K-V 对.默认第一个分隔符为','，第二个分隔符为'='				--返回值类型为map<string,string>
hive>select str_to_map('aaa:11&bbb:22', '&', ':') from tmp.tmp_jzl_test11;
{"bbb":"22","aaa":"11"}

16.split(string str, string pat)   --用pat分隔字符串str,pat为正则表达式,返回值类型为array

二.hive的行列转换

1.行转列--多行转多列

--collect_set:去重/变数组
select id,collect_set(value) as a from temp.lateral_test group by id

数组前加序号访问对应元素,从0开始(子查询表的别名必须要有)
select id,a[0] a0,a[1] a1   
from (select id,collect_set(value) as a from temp.lateral_test group by id) b  

--或者Pivot using Hivemall to_map function
SELECT uid, kv['c1'] AS c1, kv['c2'] AS c2, kv['c3'] AS c3 FROM 
(SELECT uid, to_map(key,value) kv FROM vtable GROUP BY uid) t

 2.explode可以将单行数组类型数据转化为列形式

select explode(split(concat_ws(',','1','2','3','4'),','))
col
1
2
3
4

3.利用lateral view explode实现列转行

select t.id, k.hh from   
(select id,collect_set(value) as a from temp.lateral_test group by id) t  
lateral view explode(a) k as hh


SELECT t1.uid, t2.key, t2.value FROM htable t1 
LATERAL VIEW explode (map('c1', c1, 'c2', c2, 'c3', c3)) t2 as key, value

uid key value
101 c1 11 
101 c2 12 
101 c3 13 
102 c1 21 
102 c2 22 
102 c3 23