今天来小结一下工作中经常会使用到的一些Hive函数。关于Hive函数的总结,网上早有十分全面的版本。参考:https://blog.csdn.net/doveyoung8/article/details/80014442。本文主要从最常用和实用的角度出发,说明几个使用频率较高的函数,更注重使用函数组合来解决实际问题而不局限于单个函数的使用。所有数据都是虚构,代码均在本地的Hive环境上都通过测试。本文代码较多,需要各位看官耐心学习,可以收藏备查,欢迎补充和讨论。由于公众号对代码的支持不太友好,您可以在后台回复“hive函数”获取本文的PDF版本,方便阅读。
1.json字符串处理:get_json_object
,lateral view
,explode
,substr
,json_tuple
先简要说明下几个函数的用法:
语法: get_json_object(string json_string, string path) 返回值: string 说明:解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效,那么返回NULL。 语法: explode(ARRAY),经常和lateral view一起使用 返回值: 多行 说明: 将数组中的元素拆分成多行显示 语法: substr(string A, int start, int len),substring(string A, int start, int len) 返回值: string 说明:返回字符串 A 从 start 位置开始,长度为 len 的字符串 语法: json_tuple(string json_string, col1, col2, …) ,经常和lateral view一起使用 返回值: string 说明:同时解析多个json字符串中的多个字段
然后我们看实例:
--我们虚构的数据,jsondata.txt
1 {"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"}
2 {"store":{"fruit":[{"weight":8.1,"type":"apple"}, {"weight":9.2,"type":"pear"}], "bicycle":{"price":20.01,"color":"blue"}}, "email":"abc@example.com", "owner":"bob"}
hive> create table json_data(id int, data string) row format delimited fields terminated by '\t';
hive> load data local inpath 'jsondata.txt' into table json_data;
hive> select id, get_json_object(data, '$.owner') from json_data;
1 amy
2 bob
#注意bicycle子串的格式同样是json格式
hive> select id, get_json_object(data, '$.store.bicycle.price') from json_data;
1 19.95
2 20.01
#注意fruit子串的的格式是数组(带有方括号),不是标准的json格式,下面语句取出fruit的值
hive> select id, get_json_object(data, '$.store.fruit') from json_data;
1 [{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}]
2 [{"weight":8.1,"type":"apple"},{"weight":9.2,"type":"pear"}]
#可以使用索引访问数据里的值,如得到第一个fruit的数据,结果是一个json格式的
hive> select id, get_json_object(data, '$.store.fruit[0]') from json_data;
1 {"weight":8,"type":"apple"}
2 {"weight":8.1,"type":"apple"}
#在上面语句的基础上,可以获得weight和type值。
hive> select id, get_json_object(data, '$.store.fruit[0].weight') from json_data;
1 8
2 8.1
hive> select id, get_json_object(data, '$.store.fruit[1].weight') from json_data;
1 9
2 9.2
hive> select id, get_json_object(data, '$.store.fruit[0].weight'), get_json_object(data, '$.store.fruit[0].type')
> from json_data;
1 8 apple
2 8.1 apple
hive> select id,
> get_json_object(substr(get_json_object(data, '$.store.fruit'), 2, length(get_json_object(data, '$.store.fruit')) - 2), '$.weight'),
> get_json_object(substr(get_json_object(data, '$.store.fruit'), 2, length(get_json_object(data, '$.store.fruit')) - 2), '$.type')
> from json_data;
1 8 apple
2 8.1 apple
#步骤1:截掉方括号,并用'},'分割,注意此时一三行不是完整的json,缺了右括号
hive> select id, fruit
> from json_data
> lateral view explode(split(substr(get_json_object(data, '$.store.fruit'), 2, length(get_json_object(data, '$.store.fruit')) - 2), '},')) t as fruit;
1 {"weight":8,"type":"apple"
1 {"weight":9,"type":"pear"}
2 {"weight":8.1,"type":"apple"
2 {"weight":9.2,"type":"pear"}
#步骤2:case when 补全json,可以看到一三行结果是json格式了
hive> select a.id, case when substr(a.fruit, length(fruit), 1) = "}" then a.fruit else concat(a.fruit, '}') end as fruit_info
> from
> (
> select id, fruit
> from json_data
> lateral view explode(split(substr(get_json_object(data, '$.store.fruit'), 2, length(get_json_object(data, '$.store.fruit')) - 2), '},')) t as fruit
> ) a;
1 {"weight":8,"type":"apple"}
1 {"weight":9,"type":"pear"}
2 {"weight":8.1,"type":"apple"}
2 {"weight":9.2,"type":"pear"}
#步骤3:提取weight,type数据
hive> select b.id, get_json_object(b.fruit_info, '$.weight'), get_json_object(b.fruit_info, '$.type')
> from
> (
> select a.id, case when substr(a.fruit, length(fruit), 1) = "}" then a.fruit else concat(a.fruit, '}') end as fruit_info
> from
> (
> select id, fruit
> from json_data
> lateral view explode(split(substr(get_json_object(data, '$.store.fruit'), 2, length(get_json_object(data, '$.store.fruit')) - 2), '},')) t as fruit
> ) a
> ) b;
1 8 apple
1 9 pear
2 8.1 apple
2 9.2 pear
上面的步骤3,由于要获取两个字段,可以使用json_tuple函数代替,与later view连用,写法如下:
hive> select b.id, c.weight, c.type
> from
> (
> select a.id, case when substr(a.fruit, length(fruit), 1) = "}" then a.fruit else concat(a.fruit, '}') end as fruit_info
> from
> (
> select id, fruit
> from json_data
> lateral view explode(split(substr(get_json_object(data, '$.store.fruit'), 2, length(get_json_object(data, '$.store.fruit')) - 2), '},')) t as fruit
> ) a
> ) b
> lateral view json_tuple(b.fruit_info,'weight', 'type') c as weight, type;
1 8 apple
1 9 pear
2 8.1 apple
2 9.2 pear
2.parse_url
,regexp_replace
,regexp_extract
语法: parse_url(string urlString, string partToExtract , string keyToExtract) 返回值: string 说明:返回 URL 中指定的部分。 partToExtract 的有效值为: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO. 语法: regexp_replace(string A, string B, string C) 返回值: string 说明:将字符串 A 中的符合正则表达式 B 的部分替换为 C。 语法: regexp_extract(string subject, string pattern, int index) 返回值: string 说明:将字符串 subject 按照 pattern 正则表达式的规则拆分,返回 index 指定的字符。
下面看实例:
--我们虚构的数据,urldata.txt
1 https://ty.facebook.com/dwd/social?type=1&query=abc&id=1234
2 http://qq.tencent.com/dwd/category?type=2&query=def&id=5678#title1
hive> create table url_data(id int, data string) row format delimited fields terminated by '\t';
hive> load data local inpath 'urldata.txt' into table url_data;
#获取url协议
hive> select id, parse_url(data, 'PROTOCOL') from url_data;
1 https
2 http
#获取主机名
hive> select id, parse_url(data, 'HOST') from url_data;
1 ty.facebook.com
2 qq.tencent.com
#获取path
hive> select id, parse_url(data, 'PATH') from url_data;
1 /dwd/social
2 /dwd/category
#获取所有参数的序列
hive> select id, parse_url(data, 'QUERY') from url_data;
1 type=1&query=abc&id=1234
2 type=2&query=def&id=5678
#获取完整文件路径
hive> select id, parse_url(data, 'FILE') from url_data;
1 /dwd/social?type=1&query=abc&id=1234
2 /dwd/category?type=2&query=def&id=5678
#获取REF,没有的返回NULL值
hive> select id, parse_url(data, 'REF') from url_data;
1 NULL
2 title1
插曲:获取的参数序列是键值对的形式,能否将其拆分开呢?可以使用str_to_map
函数.
语法: str_to_map(text, delimiter1, delimiter2) 返回值: map 说明:将字符串按照给定的分隔符转换成 map 结构。第一个分隔符在K-V之间分割,第二个分隔符分割K-V本身
hive> select id, parse_url(data, 'PROTOCOL'), parse_url(data, 'HOST'), parse_url(data, 'PATH'), str_to_map(parse_url(data, 'QUERY'), '&', '=')['type'],
> str_to_map(parse_url(data, 'QUERY'), '&', '=')['query'], str_to_map(parse_url(data, 'QUERY'), '&', '=')['id']
> from url_data;
1 https ty.facebook.com /dwd/social 1 abc 1234
2 http qq.tencent.com /dwd/category 2 def 5678
如果不使用parse_url,能否对相应的url子串进行截取,可以借助于regexp_extract
,regexp_replace
,不过可能调正则表达式需要用点功夫。
hive> select id, regexp_replace(data, 'dwd.+', "")
> from url_data;
1 https://ty.facebook.com/
2 http://qq.tencent.com/
hive> select id, regexp_extract(data, 'query=(.*)', 1)
> from url_data;
1 abc&id=1234
2 def&id=5678#title1
hive> select id, regexp_extract(data, 'query=(.*)&', 1)
> from url_data;
1 abc
2 def
3.collect_set
,collect_list
,concat
,concat_ws
语法: collect_set (col) 返回值: array 说明: 将 col 字段进行去重, 合并成一个数组。 语法: collect_list (col) 返回值: array 说明: 将 col 字段合并成一个数组,不去重 语法: concat(string A, string B…) 返回值: string 说明:返回输入字符串连接后的结果,支持任意个输入字符串 语法: concat_ws(string SEP, string A, string B…) 返回值: string 说明:返回输入字符串连接后的结果, SEP 表示各个字符串间的分隔符
--虚构的数据,fruitdata.txt
1001 apple
1001 pear
1001 banana
1001 pear
1002 blueberry
1002 bayberry
hive> create table fruit_data(id int, data string) row format delimited fields terminated by '\t';
hive> load data local inpath 'fruitdata.txt' into table fruit_data;
hive> select id, collect_set(data)
> from fruit_data
> group by id;
1001 ["apple","pear","banana"]
1002 ["blueberry","bayberry"]
hive> select id, collect_list(data)
> from fruit_data
> group by id;
1001 ["apple","pear","banana","pear"]
1002 ["blueberry","bayberry"]
--虚构的数据,userdata.txt。想想一个用户的粉丝在各个地域的分布情况
1001 area1 5%
1001 area2 20%
1001 area3 25%
1001 area4 50%
2001 area1 20%
2001 area2 50%
2001 area3 30%
hive> create table user_data(id int, area string, data string) row format delimited fields terminated by '\t';
hive> load data local inpath 'userdata.txt' into table user_data;
#按照每个用户一行进行排列
hive> select id, collect_set(concat_ws(':', area, data))
> from user_data
> group by id;
1001 ["area1:5%","area2:20%","area3:25%","area4:50%"]
2001 ["area1:20%","area2:50%","area3:30%"]
#下面使用concat能得到同样效果
hive> select id, collect_set(concat(area, ':', data))
> from user_data
> group by id;
1001 ["area1:5%","area2:20%","area3:25%","area4:50%"]
2001 ["area1:20%","area2:50%","area3:30%"]
#我们可以看到结果中,collect_set函数为我们加上了中括号和双引号,能不能去掉它们,我们来看下面的效果:
hive> select id, concat_ws(',', collect_set(concat( area, ':', data)))
> from user_data
> group by id;
1001 area1:5%,area2:20%,area3:25%,area4:50%
2001 area1:20%,area2:50%,area3:30%
#如果想变成map的格式,在此基础上可以再调用一下str_to_map即可
hive> select id, str_to_map(concat_ws(',', collect_set(concat( area, ':', data))), ",", ":")
> from user_data
> group by id;
1001 {"area1":"5%","area2":"20%","area3":"25%","area4":"50%"}
2001 {"area1":"20%","area2":"50%","area3":"30%"}
4.datediff
,from_unixtime
,unix_timestamp
,to_date
语法: datediff(string enddate, string startdate) 返回值: int 说明: 返回结束日期减去开始日期的天数。日期的格式需要是yyyy-MM-dd,或者yyyy-MM-dd HH:mm:ss 语法: from_unixtime(bigint unixtime[, string format]) 返回值: string 说明: 转化 UNIX 时间戳(从 1970-01-01 00:00:00 UTC 到指定时间的秒数)到当前时区的时间格式,默认的format是yyyy-MM-dd HH:mm:ss,可以指定别的 语法: unix_timestamp(string date[, string format]) 返回值: bigint 说明: 转换 pattern 格式的日期到 UNIX 时间戳。如果转化失败,则返回 0。默认的format是yyyy-MM-dd HH:mm:ss,可以指定别的。 语法: to_date(string timestamp) 返回值: string 说明: 返回日期时间字段中的日期部分。
下面看实例:
#虚构的数据datedata.txt,一共有10列,后9列是各种日期。
1 2019-02-03 20190203 2019-02-05 20190205 2019-03-03 10:38:24 2019-03-23 10:36:54 20190323 10:36:54 1551763940 1551763940267
2 2019-02-08 20190208 2019-02-18 20190218 2019-03-19 10:32:04 2019-03-31 10:39:15 20190331 10:39:15 1552632321 1551763940654
hive> create table date_data(id int, d1 string, d2 string, d3 string, d4 string, d5 string, d6 string, d7 string, d8 string, d9 string) row format delimited fields terminated by '\t';
hive> load data local inpath 'datedata.txt' into table date_data;
先看datediff
的用法:
#yyyy-MM-dd的日期差
hive> select id, datediff(d3, d1)from date_data;
1 2
2 10
hive> select id, datediff(to_date(d3), to_date(d1)) from date_data;
1 2
2 10
#yyyyMMdd的日期差
hive> select id, datediff(d4, d2) from date_data;
1 NULL
2 NULL
#上面的写法不行,我们需要将日期转换为yyyy-MM-dd格式,使用截取拼接的套路进行
hive> select datediff(concat_ws('-', substr(d4, 1, 4), substr(d4, 5, 2), substr(d4, 7, 2)), concat_ws('-', substr(d2, 1, 4), substr(d2, 5, 2), substr(d2, 7, 2)))
> from date_data;
2
10
#yyyy-MM-dd HH:mm:ss与yyyy-MM-dd的日期差
hive> select datediff(d5, d1) from date_data;
28
39
hive> select datediff(to_date(d5), d1) from date_data;
28
39
#yyyy-MM-dd HH:mm:ss与yyyy-MM-dd HH:mm:ss的日期差
hive> select datediff(d6, d5) from date_data;
20
12
hive> select datediff(to_date(d6), d5) from date_data;
20
12
再来看unix_timestamp
的用法:
#yyyy-MM-dd HH:mm:ss转换为时间戳
hive> select unix_timestamp(d5) from date_data;
1551580704
1552962724
hive> select unix_timestamp(d5, 'yyyy-MM-dd HH:mm:ss') from date_data;
1551580704
1552962724
#yyyyMMdd HH:mm:ss转换为时间戳
hive> select unix_timestamp(d7, 'yyyyMMdd HH:mm:ss') from date_data;
1553308614
1553999955
最后看from_unixtime
的用法:
#由于我们的表是string格式的,在转换之前需要转为bigint型
hive> select from_unixtime(cast(d8 as bigint)) from date_data;
2019-03-05 13:32:20
2019-03-15 14:45:21
hive> select from_unixtime(cast(d8 as bigint), 'yyyy-MM-dd HH:mm:ss') from date_data;
2019-03-05 13:32:20
2019-03-15 14:45:21
hive> select from_unixtime(cast(d8 as bigint), 'yyyyMMdd HH:mm:ss') from date_data;
20190305 13:32:20
20190315 14:45:21
hive> select from_unixtime(cast(d8 as bigint), 'yyyy-MM-dd') from date_data;
2019-03-05
2019-03-15
hive> select from_unixtime(cast(d8 as bigint), 'yyyyMMdd') from date_data;
20190305
20190315
我们经常会在业务中遇到13位的时间戳,10位的时间戳是精确到秒的,13位则是精确到毫秒的。这时只需除以1000并转化为整数即可。
hive> select from_unixtime(cast(d9/1000 as bigint)) from date_data;
2019-03-05 13:32:20
2019-03-05 13:32:20
hive> select from_unixtime(cast(d9/1000 as bigint), 'yyyy-MM-dd HH:mm:ss') from date_data;
2019-03-05 13:32:20
2019-03-05 13:32:20
hive> select from_unixtime(cast(d9/1000 as bigint), 'yyyyMMdd HH:mm:ss') from date_data;
20190305 13:32:20
20190305 13:32:20
hive> select from_unixtime(cast(d9/1000 as bigint), 'yyyy-MM-dd') from date_data;
2019-03-05
2019-03-05
hive> select from_unixtime(cast(d9/1000 as bigint), 'yyyyMMdd') from date_data;
20190305
20190305
5.coalesce
语法: COALESCE(T v1, T v2, …) 返回值: T 说明: 返回参数中的第一个非空值;如果所有值都为 NULL,那么返回 NULL
1 https://ty.facebook.com/dwd/social?type=1&query=abc&id=1234&task_id=1111
2 https://ty.facebook.com/dwd/social?type=1&query=abc&id=1234&taskid=2222
hive> create table exp_data(id int, data string) row format delimited fields terminated by '\t';
hive> load data local inpath 'expdata.txt' into table exp_data;
如果我们想提取出1111和2222这两个值,但一个是task_id,一个是taskid。如果直接用str_to_map,直接写的话,结果总会有一个空值:
hive> select str_to_map(data, '&', '=')['taskid'], str_to_map(data, '&', '=')['task_id']
> from exp_data;
NULL 1111
2222 NULL
这个时候就可以用到coalesce
hive> select coalesce(str_to_map(data, '&', '=')['taskid'], str_to_map(data, '&', '=')['task_id'], "")
> from exp_data;
1111
2222
以上我们总结了一些比较常用的hive函数,重点在于学习这些函数组合使用的情况,经常出现在工作中需要进行数据清洗或者格式转化的时候。例子都不是很复杂,有条件大家可以尝试实践一下,希望对你有用,如果感觉公众号代码阅读起来费劲,可以在后台回复“hive函数”获取本文的pdf版本。当然也有很多函数都没有涉及到,比如工作中也经常会用到窗口函数,后续如果有机会我们可以专门进行学习~