當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

【SQL编程】Greenplum 实现树结构+自定义函数+避免函数重复调用+ function cannot execute on a QE slice 问题处理（优化过程全记录）

發(fā)布時間：2024/10/6 数据库 38 豆豆

生活随笔收集整理的這篇文章主要介紹了【SQL编程】Greenplum 实现树结构+自定义函数+避免函数重复调用+ function cannot execute on a QE slice 问题处理（优化过程全记录）小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

1.需求說明

這是一個關(guān)于POI的應(yīng)用，數(shù)據(jù)從水經(jīng)微圖下載而來，需要處理的是街道層級的數(shù)據(jù)，但是最終的POI信息要有省、市、縣數(shù)據(jù)，所有需要用到行政區(qū)劃表來補(bǔ)全數(shù)據(jù)。

2.編程實例

2.1 實現(xiàn)樹結(jié)構(gòu)

首先看一下具有樹結(jié)構(gòu)的數(shù)據(jù)：

通過 WITH RECURSIVE table_name AS 實現(xiàn)遞歸查詢樹結(jié)構(gòu)數(shù)據(jù)【這里要特別注意一下 t0 和 t1 表】：

WITH RECURSIVE t1 AS (SELECT "level", parent_code, area_code, "name" FROM data_divisions WHERE "name" = '楓楊街道' UNION ALLSELECT 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

結(jié)果驗證：

2.2 自定義函數(shù)

使用STRING_AGG把省市縣數(shù)據(jù)拼接成一個字段【函數(shù)等價于GROUP_CONCAT】：

SELECTSTRING_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 ALLSELECT 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

創(chuàng)建自定義函數(shù)：

CREATE OR REPLACE FUNCTION getdivisionsbyname ( TEXT ) RETURNS TEXT AS $BODY$ SELECTSTRING_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 ALLSELECT 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;

函數(shù)調(diào)用測試：

SELECT getDivisionsByName('楓楊街道');

2.3 函數(shù)使用

data_address_point 表的記錄數(shù)是261條，執(zhí)行耗時119.451s，這效率明顯是由于多次調(diào)用自定義函數(shù)導(dǎo)致的 😢

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;

避免多次調(diào)用相同的自定義函數(shù)，優(yōu)化后耗時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" FROMt1

3.報錯問題

實際上，上邊的函數(shù)使用并是非順利的，第一次進(jìn)行查詢時報錯function cannot execute on a QE slice because it accesses relation

UDF（User Defined Function）用戶自定義函數(shù)在 segment 上不能訪問任何表。由于 MPP 的特性，任何 segment 僅僅包含部分?jǐn)?shù)據(jù)，因而在 segment 執(zhí)行的 UDF 不能訪問任何表，否則數(shù)據(jù)計算錯誤。Greenplum 支持另一種分布策略：復(fù)制表，即整張表在每個節(jié)點上都有一個完整的拷貝。可使用以下命令進(jìn)行設(shè)置：

ALTER TABLE table_name SET DISTRIBUTED REPLICATED;

數(shù)據(jù)量大的表不適合使用復(fù)制表模式，一些不經(jīng)常變動的數(shù)據(jù)量比較小的比如碼表可以使用DISTRIBUTED REPLICATED模式，查詢性能也會有明顯的提升。