使用指导

PL/Python存储过程

当前PL/Python存储过程优先支持python2;默认版本也是python2。

PL/Python中的函数通过标准的CREATE FUNCTION声明:

  1. CREATE FUNCTION funcname (argument-list)
  2. RETURNS return-type
  3. AS $$
  4. # PL/Python function body
  5. $$ LANGUAGE plpythonu;

函数体是一个简单的Python脚本,当函数被调用的时候,它的参数作为列表args的元素传递;命名参数也会被当做普通的变量传递到Python脚本中。命名参数的使用通常更易读。 结果将使用return或yield(结果集语句的情况) 照常从Python代码中返回。如果没有提供返回值,Python返回缺省的None。 PL/Python将Python中的None认为SQL空值。

例如,返回两个整数中较大者的函数定义如下。

  1. CREATE FUNCTION pymax(a integer, b integer) RETURNS integer AS $$
  2. if a > b:
  3. return a
  4. return b
  5. $$ LANGUAGE plpythonu;

使用指导 - 图1 注意:

  • PL/Python函数中,后缀为plpythonu。‘u’说明是untrusted类型的存储过程。

  • Trusted:这个语言不能访问越权的数据。例如,数据库服务器的文件、数据库内部(包括直接访问共享内存)。

  • Untrusted:这个语言没有任何限制,允许访问任何数据(包括文件,网络,共享LIB库等,危害性较大),但是功能更加强大。

  • PL/Python属于untrusted类型的存储过程语言,当前仅允许管理员权限的用户创建和修改,普通用户仅支持使用。

  • 定义PL/Python存储过程时,注意不要定义执行诸如import os;os.system(“rm -rf /“) 等危险语句。管理员权限的用户需要小心创建此类PL/Python存储过程。

数据库Null, None和空串处理

如果向函数传递了一个SQL null值,参数值在Python中将会显示为None。在数据库中,不同的兼容性下,空串的行为会被当做NULL处理。

同一个函数,在不同的兼容性下表现不同。

  1. CREATE FUNCTION quote(t text, how text) RETURNS text AS $$
  2. if how == "literal":
  3. return plpy.quote_literal(t)
  4. elif how == "nullable":
  5. return plpy.quote_nullable(t)
  6. elif how == "ident":
  7. return plpy.quote_ident(t)
  8. else:
  9. raise plpy.Error("unrecognized quote type %s" % how)
  10. $$ LANGUAGE plpythonu;

示例1:

  1. SELECT quote(t, 'literal') FROM (VALUES ('abc'),('a''bc'),('''abc'''),(''),(''''),('xyzv')) AS v(t);

数据库不同兼容性下的结果为:

  • 兼容性为A时,返回结果如下:

    1. ERROR: TypeError: argument 1 must be string, not None
    2. CONTEXT: Traceback (most recent call last):
    3. PL/Python function "quote", line 3, in <module>
    4. return plpy.quote_literal(t)
    5. referenced column: quote
  • 兼容性为B时,返回结果如下:

    1. quote
    2. -----------
    3. 'abc'
    4. 'a''bc'
    5. '''abc'''
    6. ''
    7. ''''
    8. 'xyzv'
    9. (6 rows)

示例2:

  1. SELECT quote(t, 'nullable') FROM (VALUES ('abc'),('a''bc'),('''abc'''),(''),(''''),(NULL)) AS v(t);

数据库不同兼容性下的结果为:

  • 兼容性为A时,返回结果如下:

    1. quote
    2. -----------
    3. 'abc'
    4. 'a''bc'
    5. '''abc'''
    6. NULL
    7. ''''
    8. NULL
    9. (6 rows)
  • 兼容性为B时,返回结果如下:

    1. quote
    2. -----------
    3. 'abc'
    4. 'a''bc'
    5. '''abc'''
    6. ''
    7. ''''
    8. NULL
    9. (6 rows)

可以看到,在兼容性“A”中,空串被当为NULL了。

触发器

当前PL/Python存储过程中,不支持触发器功能。

匿名代码块

PL/Python也支持DO声明的匿名代码块:

  1. DO $$
  2. # PL/Python code
  3. $$ LANGUAGE plpythonu;

一个匿名代码块不接受参数,并且丢弃它可能返回的值。

共享数据

每个函数都在Python解释器里获得自己的执行环境。

全局字典SD在函数调用之间用于存储数据。这些变量是私有静态数据。每一个函数都有自己的SD数据空间,函数A的全局数据和函数参数是函数B不可用的。

全局字典GD是公共数据,在一个gsql会话中,所有python函数都可访问和改变,使用时需要小心。

当gsql断开或退出,共享数据就被释放。

使用指导 - 图2 注意:

  • 运行DeepSQL或者PL/Python存储过程时,需要关闭线程池相关参数。否则PL/Python存储过程中的Sharing Data(“GD”、“SD”)等功能会失效。

  • 在数据库中,当线程池功能关闭,每一个连入的gsql,数据库内会起一个新的线程去处理。在gsql中,如果调用到PL/Python存储过程,会在本线程中完成python解析器模块的初始化,其中包括初始化“GD”,“SD”等共享空间。

  • 在线程池功能开启的状态下,一个gsql执行时,由当前空闲线程执行,每次执行可能分配到不同的线程上。导致共享数据紊乱。

数据库访问

PL/Python语言模块自动import一个叫plpy的Python模块。

plpy模块提供几个函数执行数据库命令:比如plpy.execute,plpy.prepare等。

plpy模块也提供了函数plpy.debug(msg)、 plpy.log(msg)、plpy.info(msg)、 plpy.notice(msg)、plpy.warning(msg)、 plpy.error(msg)和plpy.fatal(msg)。 plpy.error和 plpy.fatal实际上抛出了一个Python异常,会导致当前事务或者子事务退出。

另一个实用函数集是plpy.quote_literal(string)、 plpy.quote_nullable(string)和 plpy.quote_ident(string)。

关于审计

PL/Python存储过程支持审计功能。具体设置可以参考审计

关于并发执行

当前PL/Python存储过程对并发执行不友好,建议串行执行。

使用指导 - 图3 说明: 由于openGauss是多线程架构,C-python中,由于GIL锁(Global Interpreter Lock)的限制,多线程在Python中只能交替执行,无法做到真正的并发。

库内算法

具体库内算法介绍和使用,可参考MADlib官方网站(MADlib文档)。

使用指导 - 图4 须知:

  • 当前仅支持机器学习算法,不支持深度学习(deep learning)模块。
  • 当前数据库不支持xml,所以pmml模块和相关功能不支持。
  • 数据库不支持jsonb模块,json格式的模型导出功能也不支持。

其他算法支持

除了MADlib提供的算法外,openGauss又额外提供了以下三个算法。

表 1 额外增加的模块列表

算法名称(中文)

算法名称(英文)

梯度提升树

gbdt

梯度提升

xgboost

时间序列预测的算法

facebook_prophet

使用时,需要安装依赖的python库:

  • 如果使用prophet算法:

    1. pip install pystan
    2. pip install holidays==0.9.8
    3. pip install fbprophet==0.3.post2
  • 如果使用xgboost算法:

    1. pip install xgboost
    2. pip install scikit-learn
  • gbdt不需要额外安装其他库。

详细操作请参考最佳实践