hive中struct相关函数总结

目录

hive官方函数解释

hive官网函数大全地址：添加链接描述
Return TypeNameDescriptionstructstruct(val1, val2, val3, …)Creates a struct with the given field values. Struct field names will be col1, col2, …structnamed_struct(name1, val1, name2, val2, …)Creates a struct with the given field names and values. (As of Hive 0.8.0.)array<struct {‘x’,‘y’}>array(struct(, ), struct(, ), struct(, ))Creates a array of struct typeT1,…,Tninline(ARRAYSTRUCTf1:T1,...,fn:Tn a)Explodes an array of structs to multiple rows. Returns a row-set with N columns (N = number of top level elements in the struct), one row per struct from the array. (As of Hive 0.10.)

示例

1、struct(field1, field2, …)：创建一个 Struct 对象，由多个字段组成。

SELECT struct('张三',20,'男','2022-09-01')AS student_info;---结果
student_info
{"col1":"张三","col2":20,"col3":"男","col4":"2022-09-01"}

2、named_struct(name1, value1, name2, value2, …)：创建一个 Named Struct 对象，由多个名称和对应的值组成。

SELECT named_struct('name','张三','age',20,'gender','男','enrollment_date','2022-09-01')AS student_info;---结果
student_info
{"name":"张三","age":20,"gender":"男","enrollment_date":"2022-09-01"}

3、array(struct(, ), struct(, ), struct(, ))

sql1:
select array(struct('张三',20,'男','2022-09-01'),struct('李四',18,'男','2022-10-01'))as student_info
----结果
student_info
[{"col1":"张三","col2":20,"col3":"男","col4":"2022-09-01"},{"col1":"李四","col2":18,"col3":"男","col4":"2022-10-01"}]
sql2:
select array(named_struct('name','张三','age',20,'gender','男','enrollment_date','2022-09-01'),named_struct('name','李四','age',18,'gender','男','enrollment_date','2022-10-01'))as  student_info
----结果
student_info
[{"name":"张三","age":20,"gender":"男","enrollment_date":"2022-09-01"},{"name":"李四","age":18,"gender":"男","enrollment_date":"2022-10-01"}]

4、inline(ARRAYSTRUCTf1:T1,...,fn:Tn

sql1:
select inline(array(struct('A',10,date'2015-01-01'),struct('B',20,date'2016-02-02')));select inline(array(struct('A',10,date'2015-01-01'),struct('B',20,date'2016-02-02')))as(col1,col2,col3);select tf.*from(select0) t lateral view inline(array(struct('A',10,date'2015-01-01'),struct('B',20,date'2016-02-02'))) tf;select tf.*from(select0) t lateral view inline(array(struct('A',10,date'2015-01-01'),struct('B',20,date'2016-02-02'))) tf as col1,col2,col3;----以上四个结果均为：
col1                col2                 col3    
A                   102015-01-01
B                   202016-02-02

sql2:
select inline(array(named_struct('name','张三','age',20,'gender','男','enrollment_date','2022-09-01'),named_struct('name','李四','age',18,'gender','男','enrollment_date','2022-10-01')))as(name,age,gender,enrollment_date);----结果为：
name         age            gender           enrollment_date    
张三         20               男               2022-09-01
李四         18               男               2022-10-01

实战

如何将上述struct类型的数据转换为string格式？

select
concat('[',
                concat_ws(',',collect_set(
                                concat('{','"name":"',nvl(temp.name,''),'",','"age":"',nvl(temp.age,''),'",','"gender":"',nvl(temp.gender,''),'",','"enrollment_date":"',nvl(temp.enrollment_date,''),'"','}'))),']')as student_info1      
from(select array(struct('张三',20,'男','2022-09-01'),struct('李四',18,'男','2022-10-01'))as  student_info
) t 
lateral view inline(t.student_info)tempas name,age,gender,enrollment_date;------结果为
student_info1    
[{"name":"李四","age":"18","gender":"男","enrollment_date":"2022-10-01"},{"name":"张三","age":"20","gender":"男","enrollment_date":"2022-09-01"}]