黑洞

这里藏着一些独特的想法

0%

Hive函数高阶应用

explode 函数

简介

explode函数属于UDTF函数(表生成),输入一行,输出多行。从英文直译过来是“爆炸”的意思,因此俗称爆炸函数。

语法和效果

explode函数接收map或者array类型的数据作为参数。在生成的表中,每个元素将单独成为一行数据。

  • explode(array)array列表里的每个元素生成一行;
  • explode(map)map里的每一对元素作为一行,其中key为一列,value为一列;

栗子:

1
select explode(`array`(11,22,33)) as item;

得到结果

item
11
22
33

或者

1
select explode(`map`("id",10086,"name","zhangsan","age",18));

得到结果

key value
id 10086
name zhangsan
age 18

explode函数可以单独使用,但如果需要查看原表的其他字段,请结合lateral view侧视
图使用,否则将报错。

lateral view 侧视图

简介

lateral view是一种特殊的语法,主要用于搭配UDTF类型的函数一起使用,用于解决UDTF函数的一些查询限制问题。

侧视图的原理是将UDTF的结果构建成一个类似于视图的表,然后将原表中的每一行和
UDTF函数输出的每一行进行连接,生成一张新的虚拟表。

使用lateral view时也可以对UDTF生成的列字段设置别名,生成的字段可以用于group byorder bylimit等语句中,不需要再单独嵌套一层子查询。

一般只要使用UDTF,就会固定搭配lateral view使用。

语法和效果

1
2
select ... from tabelA a lateral view UDTF(xxx) b as
col1, col2, col3 ...;

栗子:

1
2
3
4
select a.team_name, b.year
from the_nba_championship a
lateral view explode(champion_year) b as year
order by b.year desc;

行列转换

多行转单列

col1 col2 col3
a b 1
a b 2
c d 4
a b 3
c d 5
c d 6

需求:将上表转换为如下所示。

col1 col2 col3
a b 1,2,3
c d 4,5,6

实现方法:先进行分组,再用collect_list函数收集每组的多个值,由于col3是数值型,还需转换为字符串类型,最终使用concat_ws拼接。

1
2
3
4
5
6
7
8
select
col1,
col2,
concat_ws(',', collect_list(cast(col3 as string))) as col3
from
table_A
group by
col1, col2;

相关函数

  • concat:用于实现字符串拼接,不可指定分隔符;如果任意一个元素为NULL,结果就为NULL
  • concat_ws:用于实现字符串拼接,可以指定分隔符;全部元素都为NULL,结果才会为NULL
  • collect_list:用于将一列中的多行合并为一行,不进行去重。
  • collect_set:用于将一列中的多行合并为一行,进行去重。

单列转多行

col1 col2 col3
a b 1,2,3
c d 4,5,6

需求:将上表转换为如下所示。

col1 col2 col3
a b 1
a b 2
c d 4
a b 3
c d 5
c d 6

实现方法:由于col3并非集合,因此需要先使用split进行拆分得到一个集合,再使用爆炸函数和侧视图将结果与原表关联。

1
2
3
4
5
6
select
a.col1,
a.col2,
b.col3
from table_A a
lateral view explode(split(col3, ',')) b as col3

json解析

Hive中为了实现JSON格式的数据解析,提供了两种解析JSON数据的方式,在实际工作场景下,可以根据不同数据,不同的需求来选择合适的方式对JSON格式数据进行处理。

  • 方式一:使用JSON函数进行处理
    Hive中提供了两个专门用于解析JSON字符串的函数:get_json_objectjson_tuple,这两个函数都可以实现将JSON数据中的每个字段独立解析出来,构建成表。
  • 方式二:使用Hive内置的JSON Serde加载数据
    Hive中除了提供JSON的解析函数以外,还提供了一种专门用于加载JSON文件的Serde来实现对JSON文件中数据的解析,在创建表时指定Serde,加载文件到表中,会自动解析为对应的表格式。

JSON 函数:get_json_object

用于解析JSON字符串,可以从JSON字符串中返回指定的某个对象列的值。

  • 语法
    1
    get_json_object(json_txt, path)
  • 参数
    • 第一个参数:指定要解析的JSON字符串,JSON所在的列名。
    • 第二个参数:指定要返回的字段,通过$.key的方式来指定path。
    • 特点:每次只能返回JSON对象中一列的值。

栗子:获取所有请求的状态码。

response
{“code”: “200”, “url”: “/index.html”}
{“code”: “403”, “url”: “/login.php”}
{“code”: “500”, “url”: “/test.html”}
1
select get_json_object(response, "$.code") as code from table_A

运行上面的语句你将会得到下表。

code
200
404
500

JSON 函数:json_tuple

用于实现JSON字符串的解析,可以通过指定多个参数来解析JSON返回多列的值。

  • 语法
    1
    json_tuple(jsonStr, p1, p2, ..., pn)
  • 参数
    • 第一个参数:指定要解析的JSON字符串,JSON所在的列名。
    • 第二个参数:指定要返回的第1个字段。
    • 第N+1个参数:指定要返回的第N个字段。
  • 特点
    • 功能类似于get_json_object,但是可以调用一次返回多列的值。属于UDTF类型函数。
    • 返回的每一列都是字符串类型。
    • 一般搭配lateral view使用。

栗子:还是刚才的表,我们获取状态码和请求的文件试试。

1
2
3
4
5
6
7
select
a.response,
b.code,
b.url
from table_A a
lateral view json_tuple(response, "code", "url") b
as code, url

运行上面的语句你将会得到下表。

response code url
{“code”: “200”, “url”: “/index.html”} 200 /index.html
{“code”: “403”, “url”: “/login.php”} 403 /login.php
{“code”: “500”, “url”: “/test.html”} 500 /test.html

JSONSerde

上述解析JSON的过程中是将数据作为一个JSON字符串加载到表中,再通过JSON解析函数对JSON字符串进行解析,灵活性比较高,但是对于如果整个文件就是一个JSON文件,在使用起来就相对比较麻烦。Hive中为了简化对于JSON文件的处理,内置了一种专门用于解析JSON文件的Serde解析器,在创建表时,只要指定使用JSONSerde解析表的文件,就会自动将JSON文件中的每一列进行解析。

使用方法:

1
2
3
4
5
6
7
8
9
-- 1. 建表
create table t_json (
device string,
deviceType string,
signal double,
`time` string
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;
1
2
# 2. 加载数据
load data local inpath '/export/data/device.json' into table t_json;
如果觉得文章写得不错或对您有帮助,请我喝杯柠檬茶吧!