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 by
、order by
、limit
等语句中,不需要再单独嵌套一层子查询。
一般只要使用UDTF,就会固定搭配lateral view
使用。
语法和效果
1 | select ... from tabelA a lateral view UDTF(xxx) b as |
栗子:
1 | select a.team_name, b.year |
行列转换
多行转单列
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 | select |
相关函数
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 | select |
json解析
Hive中为了实现JSON格式的数据解析,提供了两种解析JSON数据的方式,在实际工作场景下,可以根据不同数据,不同的需求来选择合适的方式对JSON格式数据进行处理。
- 方式一:使用JSON函数进行处理
Hive中提供了两个专门用于解析JSON字符串的函数:get_json_object
、json_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 | select |
运行上面的语句你将会得到下表。
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 | -- 1. 建表 |
1 | 2. 加载数据 |