进阶指南：在 DWS 中利用 PL/Python 解锁数据库无限可能

前言：
本功能适用版本：9.1.1.200及以上，仅支持混合云、纯软部署形态，其他形态陆续规划，敬请期待~

为什么选择 PL/Python？

在传统的 SQL 处理中，面对复杂的数学运算、文本解析或机器学习推理时，SQL 语句往往变得臃肿且难以维护。DWS 提供的 PL/Python 扩展，将 Python 强大的生态系统直接引入数据库内部。

• 逻辑下沉：直接在数据存储处进行逻辑处理，减少数据在网络和应用层之间的频繁传输。
• 生态复用：直接使用 Pandas、Numpy 等成熟库，无需重复造轮子。
• 开发效率：相比存储过程（PL/pgSQL），Python 的语法更简洁，对于熟悉 AI 和大数据开发的工程师几乎零门槛。

核心原理与架构：什么是 Fenced 模式？

在 DWS 中，Python UDF 函数强制要求使用 Fenced 模式。

补充说明：

• 隔离安全性：Fenced 模式意味着 Python 解释器运行在数据库核心进程之外的独立守护进程中。即便 Python 代码因为逻辑问题（如内存溢出或调用了不稳定的第三方库）崩溃，也不会影响数据库内核的稳定性。
• 资源控制：可以更好地监控和限制 Python 进程占用的 CPU 和内存资源。

语法深度解析

参数映射关系

DWS 会自动完成 SQL 类型到 Python 类型的转换。

• INT/BIGINT $\rightarrow$ int
• TEXT/VARCHAR $\rightarrow$ str
• BOOL $\rightarrow$ bool
• ARRAY $\rightarrow$ list

空值处理 (STRICT 关键字)

在 add_int_arr 示例中用到了 STRICT：

• STRICT：意味着如果输入参数中有任何一个为 NULL，函数将直接返回 NULL，而不进入 Python 内部逻辑。这能有效避免 Python 抛出 TypeError。

python基本语法规则

Python UDF函数必须严格遵循Python基本语法规则，例如：

1. 使用缩进表示代码块，默认每层缩进4个空格；
2. 以冒号开始新代码块，if/for/while/def/class/try/with 等后面跟冒号
3. 区分大小写：var与VAR表示不同的变量
4. 合法标识符：变量名开头为字母/数字/下划线，不能以数字开头

实战场景：从基础到高阶

创建示例表temp并插入数据。
CREATE TABLE temp (a INT ,b INT);
INSERT INTO temp VALUES (1,2),(2,3),(3,4),(4,5),(5,6);

场景 A：基础数值比较 (pymax 示例)

CREATE OR REPLACE FUNCTION pymax(a INT, b INT)
RETURNS INT
LANGUAGE plpython3u FENCED
AS $$
if a > b:
    return a;
else:
    return b;
$$;

使用函数pymax比较表temp的a列和b列并返回最大值。

SELECT pymax(a, b) FROM temp order by 1;
pymax
-------
2
3
4
5
6
(5 rows)

场景 B：科学计算与向量化处理

DWS-PythonUDF通过内置支持的 Numpy、scipy等库， 可以处理复杂的线性代数运算。

• 创建Python UDF函数add_int_arr使用第三方库numpy，计算a数组和b数组中第一个元素相加的和

CREATE OR REPLACE FUNCTION add_int_arr(a int8[], b int2[])
RETURNS int8[] AS $$
import numpy
return [a[0]+b[0]]
$$ LANGUAGE plpython3u strict shippable;

您可以尝试查看 pg_proc 表来确认你的函数是否已经成功注册，并观察 fencedmode 字段是否为 t，sql见附录

• 使用Python UDF函数add_int_arr，计算出数组第一个元素11和2的和为13。

使用Python UDF函数add_int_arr，计算出数组第一个元素11和2的和为13。
SELECT add_int_arr(ARRAY[11,2,3,4],ARRAY[2,4,5,5]);
add_int_arr
-------------
{13}
(1 row)

场景 C：大模型特征算子

这是 PL/Python 最具应用价值的场景，大模型特征算子以extension形式封装为文件置于DWS系统，通过CREATE EXTENSION命令创建，大模型直接调用封装好的Python UDF函数。（该功能仅9.1.1.200及以上集群版本支持）

• 1. 用户创建EXTENSION，加载大模型特征算子bq_ops。创建完成后，Python UDF函数自动完成加载，大模型特征算子函数及功能见附录。函数类型均为pythonUDF，返回值类型均为double，反映当前信号的某些特征值。
     CREATE EXTENSION bq_ops;
• 2. 创建表bq_col_table，device_code表示当前设备号，measuring_point_code表示测量点编码，date_time表示信号采集日期，high_array列表示当前接收的信号

CREATE TABLE bq_col_table(
    device_code varchar,
    measuring_point_code text,
    date_time timestamp with time zone,
    high_array double precision[]
    ) 
with (orientation=column, enable_hstore_opt=true);

每过1小时采集10秒钟信号，假设当前入库时数据如下。

INSERT INTO bq_col_table VALUES
('10098819','3a138131-344a-af96-9e9d-da049656d905','2024-07-13 17:59:59+08:00','{0.527995824813842,-0.62188184261322,-0.332374721765518,-0.139671847224235,-0.308928370475769,-0.165734529495239,0.137558653950691,-0.923967480659484,-0.398990541696548,0.620271801948547,0.366085141897201,-0.873452186584472,-1.00577819347381,-0.581831872463226,0.0675214752554893,0.789226412773132,-0.643114387989044,-0.779465496540069,0.913703441619873,1.33372521400451,-0.0830182060599327,0.621579945087432,1.48476803302764}');

• 3. 调用算子get_rms()计算信号均方根值，当前样本信号的振动信号能量强度。上述样例数据结果为：

SELECT device_code, measuring_point_code, date_time, get_rms(high_array) FROM bq_col_table;
 device_code |         measuring_point_code         |       date_time        |     get_rms
-------------+--------------------------------------+------------------------+------------------
 10098819    | 3a138131-344a-af96-9e9d-da049656d905 | 2024-07-13 17:59:59+08 | .705324261533061
(1 row)

• 4. 重复上述步骤，采集10天信号数据为样本，设定rms的正常取值区间，假设为[0.5, 0.8]。
     若出现异常信号入库，其rms值约为1.43

INSERT INTO bq_col_table VALUES
('10098828','3a138131-344a-af96-9e9d-da049656d905','2024-07-13 07:59:59+08:00','{0.544054210186004,-0.769003570079803,-1.79972970485687,0.659896433353424,1.65061652660369,-0.221043065190315,-1.83933162689208,-2.58152985572814,-0.627029538154602,2.1537218093872,2.14685225486755,-0.0429693721234798,-1.21243667602539,-1.02749335765838,-0.526543200016021,-0.0408141687512397,1.96406400203704,2.1080584526062,0.257277429103851,-1.36532151699066,-2.31293749809265,-0.803890943527221,1.13646578788757}');

SELECT device_code, measuring_point_code, date_time, get_rms(high_array) FROM bq_col_table;
 device_code |         measuring_point_code         |       date_time        |     get_rms
-------------+--------------------------------------+------------------------+------------------
 10098819    | 3a138131-344a-af96-9e9d-da049656d905 | 2024-07-13 17:59:59+08 | .705324261533061
 ……
 10098828    | 3a138131-344a-af96-9e9d-da049656d905 | 2024-07-13 07:59:59+08 | 1.43480152874657

• 5. 异常值大于rms的正常取值区间[0.5, 0.8]，据此，工程师将根据其设备号与入库时间排查此异常场景。

PL/Python 使用限制与避坑指南

1. 版本要求：必须在 9.1.1.200 及以上集群版本使用。
2. Fenced 模式强制要求：确保在定义时加上 FENCED 关键字。
3. 网络开销：由于 Fenced 模式涉及进程间通信（IPC），对于执行时间极短（纳秒级）的微型函数，频繁调用可能会产生一定开销。建议将复杂逻辑整合在单个 UDF 中处理批量数据。

总结

PL/Python 为 DWS 注入了处理非结构化数据和高级算法的能力。它不仅是一个语法扩展，更是将“数据仓库”升级为“算法中心”的关键桥梁。无论你是想做智能风控、时序分析，还是简单的复杂字符串清洗，PL/Python 都是你的首选利器。

附录

• 1.查看pymax函数

SELECT * FROM pg_proc where proname='pymax';
-[ RECORD 1 ]----+--------------
proname          | pymax
pronamespace     | 2200
proowner         | 10
prolang          | 16616
procost          | 100
prorows          | 0
provariadic      | 0
protransform     | -
proisagg         | f
proiswindow      | f
prosecdef        | f
proleakproof     | f
proisstrict      | f
proretset        | f
provolatile      | v
pronargs         | 2
pronargdefaults  | 0
prorettype       | 23
proargtypes      | 23 23
proallargtypes   |
proargmodes      |
proargnames      | {a,b}
proargdefaults   |
prosrc           |
                 | if a > b:
                 |     return a;
                 | else:
                 |     return b;
                 |
probin           |
proconfig        |
proacl           |
prodefaultargpos |
fencedmode       | t
proshippable     | f
propackage       | f
prokind          | f

• 2.大模型相关算子列表
  
  
  表1 函数列表
• 基础时域特征指标

• 频域及声学特征指标

• 带通滤波分段指标 (BPF)