hive中struct相关函数总结
目录
- hive官方函数解释
- 示例
- 实战
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"}]