HIVE_HIVE中的内置UDTF_EXPLODE 和 LATERAL VIEW

参考文章：

1.【学习笔记】hive 之行拆列explode

https://yq.aliyun.com/articles/654743

   HIVE 中内置了 EXPLODE 和 LATERAL VIEW，这两个 Function 非常的有用。尤其是在行转列的情形下。

 

2.hive中的lateral view 与 explode函数的使用

https://blog.csdn.net/guodong2k/article/details/79459282

 

 

本篇文章我们也是用了 str_to_map 函数，不熟悉的同学，可以先看下我的 str_to_map 详解。

https://blog.csdn.net/u010003835/article/details/106632517

 

构建测试数据

USE data_warehouse_test;
 
CREATE TABLE IF NOT EXISTS explode_laterview_org(
	day1_num BIGINT
	,day2_num BIGINT
	,day3_num BIGINT
	,day4_num BIGINT
	,day5_num BIGINT
	,day6_num BIGINT
	,day7_num BIGINT
	,campaign_name STRING
	,campaign_id BIGINT
);
 
 
INSERT OVERWRITE TABLE explode_laterview_org VALUES 
(40, 20, 10, 4, 4, 2, 1, 'zoo', 2 )
,(100, 80, 53, 40, 7, 6, 5, 'moji', 3)
;

 

EXPLODE 

EXPLODE 主要用来拆解列或者行的，下面我们看下函数介绍：

desc function explode;

+----------------------------------------------------+
|                      tab_name                      |
+----------------------------------------------------+
| explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns  |
+----------------------------------------------------+
 

 

函数解释 ：

   可以看到EXPLODE 函数一共处理两种情况，可以把 Array 切分成 单列多行，也可以把 Map 切分成 key，value 组成的多列多行。

 

案例

   下面我们就上面的数据做一个测试。SQL 如下

SELECT EXPLODE(
			STR_TO_MAP(
					CONCAT(
						'day1_num=',CAST (day1_num AS STRING),
						'&day2_num=',CAST (day2_num AS STRING),
						'&day3_num=',CAST (day3_num AS STRING),
						'&day4_num=',CAST (day4_num AS STRING),
						'&day5_num=',CAST (day5_num AS STRING),
						'&day6_num=',CAST (day6_num AS STRING),
						'&day7_num=',CAST (day7_num AS STRING)
					)
				,'&', '=')
		)
FROM explode_laterview_org
;


SELECT EXPLODE(
			SPLIT(
					CONCAT(
						CAST (day1_num AS STRING),
						',',CAST (day2_num AS STRING),
						',',CAST (day3_num AS STRING),
						',',CAST (day4_num AS STRING),
						',',CAST (day5_num AS STRING),
						',',CAST (day6_num AS STRING),
						',',CAST (day7_num AS STRING)
					)
				,',')
		)
FROM explode_laterview_org
;

 

输出

SQL1

+-----------+--------+
|    key    | value  |
+-----------+--------+
| day1_num  | 40     |
| day2_num  | 20     |
| day3_num  | 10     |
| day4_num  | 4      |
| day5_num  | 4      |
| day6_num  | 2      |
| day7_num  | 1      |
| day1_num  | 100    |
| day2_num  | 80     |
| day3_num  | 53     |
| day4_num  | 40     |
| day5_num  | 7      |
| day6_num  | 6      |
| day7_num  | 5      |
+-----------+--------+

 

SQL2

+------+
| col  |
+------+
| 40   |
| 20   |
| 10   |
| 4    |
| 4    |
| 2    |
| 1    |
| 100  |
| 80   |
| 53   |
| 40   |
| 7    |
| 6    |
| 5    |
+------+

 

 

注意

  使用 EXPLODE ，需要注意一下几点

1、No other expressions are allowed in SELECT

如果只是单一使用了EXPLODE, SELECT 不可以使用其他的表达式

SELECT pageid, explode(adid_list) AS myCol... is not supported

 

2、UDTF's can't be nested

EXPLODE 类似的 UDTF 函数不能嵌套

SELECT explode(explode(adid_list)) AS myCol... is not supported

 

3.GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported

EXPLODE 不可以聚合函数，排序等组合

SELECT explode(adid_list) AS myCol ... GROUP BY myCol is not supported

 

LATERAL VIEW

    既然EXPLODE 限制这么多，那么很多情况下是不能满足我们需求的。 这个时候使用 LATERAL VIEW 可以去除 EXPLODE 的这些限制。

    LATERAL VIEW 被解释为 侧视图，先看下LATERAL VIEW的介绍

    LATERAL 按照我的理解，其实是和原来的其他列做JOIN。 因为原来的一行还有其他列，对其中一行进行了拆分，还希望带着其他列的信息，这个时候就可以使用 LATERAL VIEW。

 

比如针对上面的测试数据，想转换为以下的数据形式

campagin_id, campagin_name, type, num

1,'zoo','day1_num ',40

1,'zoo' ,'day2_num',30

...

 

这个时候，我们就可以结合上面的 EXPLODE 写出以下的语句

SELECT 
	campaign_id, campaign_name, type, num
FROM explode_laterview_org
LATERAL VIEW
	EXPLODE(
			STR_TO_MAP(
					CONCAT(
						'day1_num=',CAST (day1_num AS STRING),
						'&day2_num=',CAST (day2_num AS STRING),
						'&day3_num=',CAST (day3_num AS STRING),
						'&day4_num=',CAST (day4_num AS STRING),
						'&day5_num=',CAST (day5_num AS STRING),
						'&day6_num=',CAST (day6_num AS STRING),
						'&day7_num=',CAST (day7_num AS STRING)
					)
				,'&', '=')
		) lateral_table AS type, num
;

结果

+--------------+----------------+-----------+------+
| campaign_id  | campaign_name  |   type    | num  |
+--------------+----------------+-----------+------+
| 2            | zoo            | day1_num  | 40   |
| 2            | zoo            | day2_num  | 20   |
| 2            | zoo            | day3_num  | 10   |
| 2            | zoo            | day4_num  | 4    |
| 2            | zoo            | day5_num  | 4    |
| 2            | zoo            | day6_num  | 2    |
| 2            | zoo            | day7_num  | 1    |
| 3            | moji           | day1_num  | 100  |
| 3            | moji           | day2_num  | 80   |
| 3            | moji           | day3_num  | 53   |
| 3            | moji           | day4_num  | 40   |
| 3            | moji           | day5_num  | 7    |
| 3            | moji           | day6_num  | 6    |
| 3            | moji           | day7_num  | 5    |
+--------------+----------------+-----------+------+
 

 

额外提示：

如果有多个拆分列，需要进行笛卡尔积的组合。 可以多重使用 LATERAL VIEW

select goods_id2,sale_info,area2
from explode_lateral_view 
LATERAL VIEW explode(split(goods_id,','))goods as goods_id2 
LATERAL VIEW explode(split(area,','))area as area2;