Hive 行列转换

Byyyi耀

已于 2024-01-24 10:01:57 修改

阅读量839

点赞数 12

分类专栏： Hive 文章标签： hive 数据仓库行列转换 explode inline 行转列列传行

于 2024-01-23 19:14:06 首次发布

本文链接：https://blog.csdn.net/m0_74120525/article/details/135781159

版权

Hive 专栏收录该内容

9 篇文章 4 订阅

订阅专栏

本文介绍了在HiveSQL中使用lateralview和explode、inline等函数进行数据转换，如将单列转多行、多列转多行以及行转列的条件聚合操作。示例展示了如何处理数组、映射和结构体数据的转换过程。

摘要由CSDN通过智能技术生成

在这里插入图片描述

行列转换

列转行

使用 lateral view + explode(array|map) 或 lateral view + inline(array_struct) 可以将列转换为行。

单列转多行，降维（单列数组或键值对）

示例1：explode(array(…))

select ..., A
from T
lateral view explode(ARRAY_FIELD) as A;

select explode(`array`(88.2,98.3,67.1)) AS (price);

示例2：explode(map(…))

select ..., K, V
from T
lateral view explode(MAP_FIELD) as K, V;

select explode(`map`("java",56,"mysql",88,"javascript",66)) AS (subject, score);

示例3：inline(array_struct)

select ..., 
from T
lateral view inline(STRUCT_ARRAY_FIELD)V as F1,...,FN;

with tmp as (
select array(
	named_struct('name','henry','age',22,'is_member','true'),
	named_struct('name','pola','age',20,'is_member','true'),
	named_struct('name','ariel','age',19,'is_member','true')
   ) AS array_struct
)
select name,age,is_member
from tmp
lateral view inline(array_struct)V as name,age,is_member;

lateral view inline(array_struct)将结构体数组的每个元素都转化为一行,每一行都包含结构体字段的值.
前：
在这里插入图片描述

后：
在这里插入图片描述

多列转多行

select ..., A
from T
lateral view explode(array|map(F1,...,FN))V as A;

示例：

SELECT name, class, Scores.subject, Scores.score
FROM Students
LATERAL VIEW EXPLODE(ARRAY(
	named_struct('subject','math','score',math_score),
	named_struct('subject','science','score',science_score)
	)
) V AS Scores;

前：
在这里插入图片描述
后：

行转列

多行转多列
条件聚合，通常用于将多行数据中满足条件的某个值聚合到单个行中。

select
		F1,...,
		sum(if(C1,0,V1)) as A1,
		sum(if(C2,0,V2)) as A2,
		sum(if(C3,0,V3)) as A3
	from TABLE_NAME
	group by F1,...
	
	drop table if exists lateral_view_stack_test1w;
	create table lateral_view_stack_test1w as
	select year,
		   sum(if(month(order_time)=1,order_amount,0)) as sum_jan,
		   sum(if(month(order_time)=2,order_amount,0)) as sum_feb,
		   sum(if(month(order_time)=3,order_amount,0)) as sum_mar,
		   sum(if(month(order_time)=4,order_amount,0)) as sum_apr,
		   sum(if(month(order_time)=5,order_amount,0)) as sum_may,
		   sum(if(month(order_time)=6,order_amount,0)) as sum_jun,
		   sum(if(month(order_time)=7,order_amount,0)) as sum_jul,
		   sum(if(month(order_time)=8,order_amount,0)) as sum_aug,
		   sum(if(month(order_time)=9,order_amount,0)) as sum_sep,
		   sum(if(month(order_time)=10,order_amount,0)) as sum_oct,
		   sum(if(month(order_time)=11,order_amount,0)) as sum_nov,
		   sum(if(month(order_time)=12,order_amount,0)) as sum_dec
	from hive_internal_par_regex_test1w
	where year>=2014
	group by year;