Doris：使用表函数explode实现array字段列转行

使用场景

• 我们的大数据数据库，由clickhouse换成了doris
• 我们有一张路口指标表，安全指标表，记录整个路口周期级别的安全指标，包括路口级的指标和各个进口的部分指标，都在一条记录里
• clickhouse原有的array字段，可以使用arrayJoin函数实现列转行，有兴趣的可以看下我的这篇博文：ClickHouse嵌套类型使用案例：使用Array和Tuple（或Nested）表示交通干线指标场景
• doris没有arrayJoin函数，但也有实现列转行的方法

相关知识点介绍

• 一句话总结：Doris宽表怎么转成高表，就是借助Lateral view

explode

• 官方文档地址：主页 > SQL 手册 > SQL 函数 > 表函数 > EXPLODE
• 表函数，需配合 Lateral View 使用。将 array 列展开成多行。当 array 为NULL或者为空时，explode_outer 返回NULL。 explode 和 explode_outer 均会返回 array 内部的NULL元素。
• 如果不支持向量化引擎，先设置一下set enable_vectorized_engine = true

split_by_string

• 官方文档地址：主页 > SQL 手册 > SQL 函数 > 字符串函数 > SPLIT_BY_STRING
• 此函数将字符串分隔，转为数组
• 如果存储的是数组array格式，可以直接使用
• 如果存储的是字符串格式（符号分隔），可以使用函数split_by_string将字符串分隔为数组

SELECT split_by_string('1,2,3,4',',') 
1

lateral view

• 官方文档地址：主页 > 进阶使用 > 行转列
• 与生成器函数（例如 EXPLODE）结合使用，将生成包含一个或多个行的虚拟表。 LATERAL VIEW 将行应用于每个原始输出行
• 使用lateral view explode分隔数组

SELECT e1 FROM (SELECT * FROM dwd_signal_securityindex_ri  order by time_stamp desc limit 1) as t lateral view explode(approach_index) tmp1 as e1;
1

具体实现和SQL

• 原始数据
  

lateral view explode列转行

SELECT time_stamp, intersection_number, safety_factor,sub FROM dwd_signal_securityindex_ri as t lateral view explode ( approach_index ) tbl1 AS sub;
1
2

SPLIT_BY_STRING拆分字符串为数组

SELECT
	time_stamp,
	intersection_number,
	safety_factor,
	SPLIT_BY_STRING ( sub, '-' ) arr 
FROM
	( SELECT time_stamp, intersection_number, safety_factor, sub FROM dwd_signal_securityindex_ri AS t lateral VIEW explode ( approach_index ) tbl1 AS sub ) aaa;
1
2
3
4
5
6
7
8

element_at获取数据

SELECT
	time_stamp,
	intersection_number,
	element_at ( arr, 1 ) AS approach,
	element_at ( arr, 2 ) AS pedestrianTimeGuaranteeRate ,
	element_at ( arr, 3 ) AS pedestrianIllegalRate ,
	element_at ( arr, 4 ) AS trafficConflict 
FROM
	(
	SELECT
		time_stamp,
		intersection_number,
		SPLIT_BY_STRING ( sub, '-' ) arr 
	FROM
		( SELECT time_stamp, intersection_number, safety_factor, sub FROM dwd_signal_securityindex_ri AS t lateral VIEW explode ( approach_index ) tbl1 AS sub ) aaa 
	) bbb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

创建视图

• 查询SQL写好后，可以创建视图，后续直接查询该视图即可

-- 路口进口级别，安全指标
DROP VIEW IF EXISTS `signal`.dwd_signal_securityindex_ri_view;
CREATE VIEW `signal`.dwd_signal_securityindex_ri_view AS
SELECT
	time_stamp,
	intersection_number,
	CAST(element_at ( arr, 1 ) AS DECIMAL(2,0)) AS approach,
	CAST(element_at ( arr, 2 ) AS DECIMAL(10,2)) AS pedestrian_time_guarantee_rate ,
	CAST(element_at ( arr, 3 ) AS DECIMAL(10,2)) AS pedestrian_illegal_rate ,
	CAST(element_at ( arr, 4 ) AS DECIMAL(5,0)) AS traffic_conflict 
FROM
	(
	SELECT
		time_stamp,
		intersection_number,
		SPLIT_BY_STRING ( sub, '-' ) arr 
	FROM
		( SELECT time_stamp, intersection_number, safety_factor, sub FROM dwd_signal_securityindex_ri AS t lateral VIEW explode ( approach_index ) tbl1 AS sub ) aaa 
	) bbb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19