【SQL编程】Greenplum 实现树结构+自定义函数+避免函数重复调用+ cannot execute on a QE slice问题处理

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

1.需求说明

这是一个关于POI的应用，数据从水经微图下载而来，需要处理的是街道层级的数据，但是最终的POI信息要有省、市、县数据，所有需要用到行政区划表来补全数据。

2.编程实例

2.1 实现树结构

首先看一下具有树结构的数据：

通过 WITH RECURSIVE table_name AS 实现递归查询树结构数据【这里要特别注意一下 t0 和 t1 表】：

WITH RECURSIVE t1 AS (
	SELECT "level", parent_code, area_code, "name" 
	FROM data_divisions 
	WHERE "name" = '枫杨街道' 
	UNION ALL
	SELECT t0."level", t0.parent_code, t0.area_code, t0."name" 
	FROM data_divisions t0, t1 
	WHERE t0.area_code = t1.parent_code 
	) 
	SELECT "level", "name" FROM t1

结果验证：

2.2 自定义函数

使用STRING_AGG把省市县数据拼接成一个字段【函数等价于GROUP_CONCAT】：

SELECT
	STRING_AGG ( "name", ',' ORDER BY "level" ) AS "divisions" FROM   
( WITH RECURSIVE t1 AS (
	SELECT "level", parent_code, area_code, "name" 
	FROM data_divisions 
	WHERE "name" = '枫杨街道' 
	UNION ALL
	SELECT t0."level", t0.parent_code, t0.area_code, t0."name" 
	FROM data_divisions t0, t1 
	WHERE t0.area_code = t1.parent_code 
	) 
	SELECT "level", "name" FROM t1 ) t2

创建自定义函数：

CREATE OR REPLACE FUNCTION getdivisionsbyname ( TEXT ) RETURNS TEXT AS $BODY$ 
	
	SELECT
	STRING_AGG ( "name", ',' ORDER BY "level" ) AS "divisions" FROM   
( WITH RECURSIVE t1 AS (
	SELECT "level", parent_code, area_code, "name" 
	FROM data_divisions 
	WHERE "name" = '枫杨街道' 
	UNION ALL
	SELECT t0."level", t0.parent_code, t0.area_code, t0."name" 
	FROM data_divisions t0, t1 
	WHERE t0.area_code = t1.parent_code 
	) 
	SELECT "level", "name" FROM t1 ) t2;

$BODY$ LANGUAGE SQL IMMUTABLE STRICT COST 100;

函数调用测试：

SELECT getDivisionsByName('枫杨街道');

2.3 函数使用

data_address_point 表的记录数是261条，执行耗时119.451s，这效率明显是由于多次调用自定义函数导致的 :cry:

SELECT  
	getdivisionsbyname(zone_name) || NAME AS "poi",
	SPLIT_PART( coordinates, ',', 1 ) AS "longitude",
	SPLIT_PART( coordinates, ',', 2 ) AS "latitude",
	NAME AS "address",
	SPLIT_PART( getdivisionsbyname(zone_name), ',', 1 ) AS "prov",
	SPLIT_PART( getdivisionsbyname(zone_name), ',', 2 ) AS "city",
	SPLIT_PART( getdivisionsbyname(zone_name), ',', 3 ) AS "district",
	SPLIT_PART( getdivisionsbyname(zone_name), ',', 4 ) AS "town" 
FROM data_address_point;

避免多次调用相同的自定义函数，优化后耗时23.634s，是之前的5分之1：

WITH t1 AS ( SELECT getdivisionsbyname ( zone_name ) AS "divisions", coordinates, "name", poi_type FROM data_address_point ) 
SELECT 
ROW_NUMBER ( ) OVER ( ORDER BY "name" ) AS "id",
REPLACE ( divisions, ',', '' ) || "name" AS "poi",
poi_type,
SPLIT_PART( coordinates, ',', 1 ) AS "longitude",
SPLIT_PART( coordinates, ',', 2 ) AS "latitude",
NAME AS "address",
SPLIT_PART( divisions, ',', 1 ) AS "prov",
SPLIT_PART( divisions, ',', 2 ) AS "city",
SPLIT_PART( divisions, ',', 3 ) AS "district",
SPLIT_PART( divisions, ',', 4 ) AS "town" 
FROM
	t1

3.报错问题

实际上，上边的函数使用并是非顺利的，第一次进行查询时报错function cannot execute on a QE slice because it accesses relation

WITH t1 AS ( SELECT getdivisionsbyname ( zone_name ) AS "divisions", coordinates, "name", poi_type FROM data_address_point ) 
SELECT 
ROW_NUMBER ( ) OVER ( ORDER BY "name" ) AS "id",
REPLACE ( divisions, ',', '' ) || "name" AS "poi",
poi_type,
SPLIT_PART( coordinates, ',', 1 ) AS "longitude",
SPLIT_PART( coordinates, ',', 2 ) AS "latitude",
NAME AS "address",
SPLIT_PART( divisions, ',', 1 ) AS "prov",
SPLIT_PART( divisions, ',', 2 ) AS "city",
SPLIT_PART( divisions, ',', 3 ) AS "district",
SPLIT_PART( divisions, ',', 4 ) AS "town" 
FROM
	t1
> ERROR:  function cannot execute on a QE slice because it accesses relation "public.data_divisions"  (seg0 slice1 192.168.0.123:6000 pid=168995)
CONTEXT:  SQL function "getdivisionsbyname" during startup

UDF（User Defined Function）用户自定义函数在 segment 上不能访问任何表。由于 MPP 的特性，任何 segment 仅仅包含部分数据，因而在 segment 执行的 UDF 不能访问任何表，否则数据计算错误。Greenplum 支持另一种分布策略：复制表，即整张表在每个节点上都有一个完整的拷贝。可使用以下命令进行设置：

ALTER TABLE table_name SET DISTRIBUTED REPLICATED;

数据量大的表不适合使用复制表模式，一些不经常变动的数据量比较小的比如码表可以使用DISTRIBUTED REPLICATED模式，查询性能也会有明显的提升。