hive中struct相关函数总结

hive官方函数解释

hive官网函数大全地址：添加链接描述

Return Type	Name	Description
struct	struct(val1, val2, val3, …)	Creates a struct with the given field values. Struct field names will be col1, col2, …
struct	named_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 type
T1,…,Tn	inline(ARRAY<STRUCTf1: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(ARRAY<STRUCTf1: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 (select 0) t lateral view inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) tf;
select tf.* from (select 0) 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                   10                 2015-01-01
B                   20                 2016-02-02sql2:
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)  temp as 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"}]