Greenplum PL/Python语言扩展

Greenplum PL/Python语言扩展

本节概述了Greenplum数据库PL/Python语言。

关于Greenplum PL/Python
启用和删除PL/Python支持
用PL/Python开发函数
安装Python模块
示例
参考

关于Greenplum PL/Python

PL/Python是一种可加载的过程语言。使用Greenplum数据库PL/Python扩展，您可以在Python中编写Greenplum数据库用户定义函数，利用Python功能和模块快速构建强大的数据库应用程序。

您可以将PL/Python代码块作为匿名代码块运行。请参阅Greenplum数据库参考指南中的DO命令。

Greenplum数据库PL/Python扩展默认安装在Greenplum数据库中。 Greenplum数据库安装了Python和PL/Python的一个版本。这是Greenplum数据库使用的Python安装的位置：

$GPHOME/ext/python/

Greenplum数据库PL/Python限制

Greenplum数据库不支持PL/Python触发器。
PL/Python仅作为Greenplum数据库不可信语言提供。
不支持可更新游标（UPDATE … WHERE CURRENT OF和DELETE … WHERE CURRENT OF）。

启用和删除PL/Python支持

PL/Python语言随Greenplum数据库一起安装。要在数据库中创建和运行PL/Python用户定义函数（UDF），必须在数据库中注册PL/Python语言。

启用PL/Python支持

对于需要使用它的每个数据库，使用SQL命令CREATE EXTENSION注册PL/Python语言。由于PL/Python是一种不受信任的语言，因此只有超级用户才能将PL/Python注册到数据库中。例如，当gpadmin用户使用名为testdb的数据库注册PL/Python时，运行此命令：

$ psql -d testdb -c 'CREATE EXTENSION plpythonu;'

PL/Python注册为不受信任的语言。

删除PL/Python支持

对于不再需要PL/Python语言的数据库，请使用SQL命令DROP EXTENSION删除对PL/Python的支持。由于PL/Python是一种不受信任的语言，因此只有超级用户才能从数据库中删除对PL/Python语言的支持。例如，以gpadmin用户身份运行此命令将从名为testdb的数据库中删除对PL/Python的支持：

$ psql -d testdb -c 'DROP EXTENSION plpythonu;'

如果任何现有对象（如函数）依赖于语言，则默认命令将失败。指定CASCADE选项也可以删除所有依赖对象，包括使用PL/Python创建的函数。

用PL/Python开发函数

PL/Python用户定义函数的主体是Python脚本。调用该函数时，其参数将作为数组args[]的元素传递。命名参数也作为普通变量传递给Python脚本。结果从带有return语句的PL/Python函数返回，或者在结果集语句的情况下从yield语句返回。

数组和列表

使用Python列表将SQL数组值传递到PL/Python函数中。类似地，PL/Python函数将SQL数组值作为Python列表返回。在典型的PL/Python使用模式中，您将使用[]指定数组。

以下示例创建一个返回整数数组的PL/Python函数：

CREATE FUNCTION return_py_int_array()
  RETURNS int[]
AS $$
  return [1, 11, 21, 31]
$$ LANGUAGE plpythonu;
SELECT return_py_int_array();
 return_py_int_array 
---------------------
 {1,11,21,31}
(1 row)

PL/Python将多维数组视为列表列表。使用嵌套的Python列表将多维数组传递给PL/Python函数。当PL/Python函数返回一个多维数组时，每个级别的内部列表必须都具有相同的大小。

以下示例创建一个PL/Python函数，该函数将多维数组的整数作为输入。该函数显示提供的参数的类型，并返回多维数组：

CREATE FUNCTION return_multidim_py_array(x int4[]) 
  RETURNS int4[]
AS $$
  plpy.info(x, type(x))
  return x
$$ LANGUAGE plpythonu;
SELECT * FROM return_multidim_py_array(ARRAY[[1,2,3], [4,5,6]]);
INFO:  ([[1, 2, 3], [4, 5, 6]], <type 'list'>)
CONTEXT:  PL/Python function "return_multidim_py_type"
 return_multidim_py_array 
--------------------------
 {{1,2,3},{4,5,6}}
(1 row)

PL/Python还接受其他Python序列（如元组）作为函数参数，以便向后兼容不支持多维数组的Greenplum版本。在这种情况下，Python序列始终被视为一维数组，因为它们与复合类型不一致。

复合类型

使用Python映射将复合类型参数传递给PL/Python函数。映射的元素名称是复合类型的属性名称。如果属性具有空值，则其映射值为None。

您可以将复合类型结果作为序列类型（元组或列表）返回。在多维数组中使用复合类型时，必须将复合类型指定为元组，而不是列表。您不能将复合类型数组作为列表返回，因为确定列表是表示复合类型还是其他数组维度是不明确的。在典型的使用模式中，您将使用()指定复合类型元组。

在以下示例中，您将创建一个复合类型和一个返回复合类型数组的PL/Python函数：

CREATE TYPE type_record AS (
  first text,
  second int4
);
CREATE FUNCTION composite_type_as_list()
  RETURNS type_record[]
AS $$              
  return [[('first', 1), ('second', 1)], [('first', 2), ('second', 2)], [('first', 3), ('second', 3)]];
$$ LANGUAGE plpythonu;
SELECT * FROM composite_type_as_list();
                               composite_type_as_list                           
------------------------------------------------------------------------------------
 {{"(first,1)","(second,1)"},{"(first,2)","(second,2)"},{"(first,3)","(second,3)"}}
(1 row)

有关PL/Python处理数组和复合类型的其他信息，请参阅PostgreSQL数组，列表文档。

执行和准备SQL查询

PL/Python plpy模块提供了两个Python函数来执行SQL查询并为查询plpy.execute和plpy.prepare准备执行计划。如果从多个Python函数运行查询，则为查询准备执行计划很有用。

PL/Python还支持plpy.subtransaction()函数，以帮助在显式子事务中管理plpy.execute调用。有关plpy.subtransaction()的其他信息，请参阅PostgreSQL文档中的显式子事务。

plpy.execute

使用查询字符串和可选的限制参数调用plpy.execute会导致运行查询并在Python结果对象中返回结果。结果对象模拟列表或字典对象。可以通过行号和列名访问结果对象中返回的行。结果集行编号从0（零）开始。可以修改结果对象。结果对象具有以下附加方法：

nrows 返回查询返回的行数。
status 这是SPI_execute()返回值。

例如，PL/Python用户定义函数中的此Python语句执行查询。

rv = plpy.execute("SELECT * FROM my_table", 5)

plpy.execute函数最多可从my_table返回5行。结果集存储在rv对象中。如果my_table有一个列my_column，它将被访问为：

my_col_data = rv[i]["my_column"]

由于函数最多返回5行，因此索引i可以是0到4之间的整数。

plpy.prepare

函数plpy.prepare为查询准备执行计划。如果在查询中有参数引用，则使用查询字符串和参数类型列表调用它。例如，此语句可以在PL/Python用户定义的函数中：

plan = plpy.prepare("SELECT last_name FROM my_users WHERE 
  first_name = $1", [ "text" ])

字符串text是为变量$1传递的变量的数据类型。准备好语句后，使用plpy.execute函数运行它：

rv = plpy.execute(plan, [ "Fred" ], 5)

第三个参数是返回的行数限制，是可选的。

使用PL/Python模块准备执行计划时，计划会自动保存。有关执行计划的信息，请参阅Postgres服务器编程接口（SPI）文档https://www.postgresql.org/docs/9.4/spi.html。

要在函数调用中有效使用已保存的计划，请使用Python持久存储字典SD或GD之一。

全局字典SD可用于在函数调用之间存储数据。此变量是私有静态数据。全局字典GD是公共数据，可用于会话中的所有Python函数。小心使用GD。

每个函数在Python解释器中都有自己的执行环境，因此myfunc的全局数据和函数参数不可用于myfunc2。如前所述，GD词典中的数据是例外。

此示例使用SD字典：

CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
  if SD.has_key("plan"):
    plan = SD["plan"]
  else:
    plan = plpy.prepare("SELECT 1")
    SD["plan"] = plan
  # rest of function
$$ LANGUAGE plpythonu;

处理Python错误和消息

Python模块plpy实现了这些函数来管理错误和消息：

plpy.debug
plpy.log
plpy.info
plpy.notice
plpy.warning
plpy.error
plpy.fatal
plpy.debug

消息函数plpy.error和plpy.fatal引发一个Python异常，如果未被捕获，则传播到调用查询，导致当前事务或子事务中止。函数raise plpy.ERROR(msg)和raise plpy.FATAL(msg)分别与plpy.error和plpy.fatal等价。其他消息函数仅生成不同优先级的消息。

是否向客户端报告特定优先级的消息，写入服务器日志，还是两者都由Greenplum数据库服务器配置参数log_min_messages和client_min_messages控制。有关参数的信息，请参阅Greenplum数据库参考指南。

使用字典GD提高PL/Python性能

在性能方面，导入Python模块是一项昂贵的操作，可能会影响性能。如果要经常导入同一模块，则可以使用Python全局变量在第一次调用时加载模块，而不需要在后续调用中导入模块。以下PL/Python函数使用GD持久存储字典来避免导入模块（如果模块已经导入并且在GD中）。

psql=#
   CREATE FUNCTION pytest() returns text as $$ 
      if 'mymodule' not in GD:
        import mymodule
        GD['mymodule'] = mymodule
    return GD['mymodule'].sumd([1,2,3])
$$;

安装Python模块

在Greenplum数据库上安装Python模块时，Greenplum数据库Python环境必须在群集中的所有segment主机和mirror主机上添加模块。扩容Greenplum数据库时，必须将Python模块添加到新的segment主机中。您可以在Greenplum数据库主机上使用Greenplum数据库实用程序gpssh和gpscp运行命令，并将文件复制到主机。有关实用程序的信息，请参阅Greenplum数据库实用程序指南。

作为Greenplum数据库安装的一部分，gpadmin用户环境配置为使用随Greenplum数据库一起安装的Python。

要检查Python环境，可以使用which命令：

which python

该命令返回Python安装的位置。与Greenplum数据库一起安装的Python位于Greenplum数据库ext/python目录中。

/path_to_greenplum-db/ext/python/bin/python

使用gpssh在远程主机上运行shell命令时，可以指定-s选项。指定该选项后，gpssh会在远程主机上运行命令之前加载greenplum_path.sh文件。例如，此命令应显示在每台主机上使用Greenplum数据库安装的Python。

gpssh -f gpdb_hosts which python

如果没有，则可以在运行命令之前在远程主机上添加-s到源greenplum_path.sh。

gpssh -s -f gpdb_hosts which python

要显示当前安装的Python模块列表，请运行此命令。

python -c "help('modules')"

以交互模式运行gpssh以在远程主机上显示Python模块。此示例以交互模式启动gpssh，并在Greenplum数据库主机sdw1上列出Python模块。

$ gpssh -s -h sdw1
=> python -c "help('modules')"
. . . 
=> exit
$

这些部分描述了安装和测试Python模块：

安装Python pip
使用pip安装Python包
在本地构建和安装Python模块
测试已安装的Python模块

安装Python pip

Python实用程序pip安装Python包，其中包含Python模块和来自版本化归档文件的其他资源文件。

运行此命令以安装pip。

python -m ensurepip --default-pip

该命令运行ensurepip模块以从本地Python安装引导（安装和配置）pip实用程序。

您可以运行此命令以确保pip，setuptools和wheel项目是最新的。当前的Python项目确保您可以从源代码分发或预构建的分发（wheels）安装Python包。

python -m pip install --upgrade pip setuptools wheel

您可以使用gpssh在Greenplum数据库主机上运行命令。此示例以交互模式运行gpssh，以在文件gpdb_hosts中列出的主机上安装pip。

$ gpssh -s -f gpdb_hosts
=> python -m ensurepip --default-pip
[centos6-mdw1] Ignoring indexes: https://pypi.python.org/simple
[centos6-mdw1] Collecting setuptools
[centos6-mdw1] Collecting pip
[centos6-mdw1] Installing collected packages: setuptools, pip
[centos6-mdw1] Successfully installed pip-8.1.1 setuptools-20.10.1
[centos6-sdw1] Ignoring indexes: https://pypi.python.org/simple
[centos6-sdw1] Collecting setuptools
[centos6-sdw1] Collecting pip
[centos6-sdw1] Installing collected packages: setuptools, pip
[centos6-sdw1] Successfully installed pip-8.1.1 setuptools-20.10.1
=> exit
$

\=>是gpssh的非活动提示。该实用程序显示每个主机的输出。 exit命令退出gpssh交互模式。

此gpssh命令在文件gpdb_hosts中列出的所有主机上运行单个命令。

gpssh -s -f gpdb_hosts python -m pip install --upgrade pip setuptools wheel

该实用程序显示每个主机的输出。

有关安装Python包的更多信息，参考https://packaging.python.org/tutorials/installing-packages/。

使用pip安装Python包

安装pip后，您可以安装Python包。此命令安装numpy和scipy包。

python -m pip install --user numpy scipy

--user选项尝试在安装Python包时避免冲突。

您可以使用gpssh在Greenplum数据库主机上运行该命令。

有关这些和其他Python包的信息，请参阅参考。

在本地构建和安装Python模块

如果要构建Python模块，则必须确保构建创建正确的可执行文件。例如，在Linux系统上，构建应该创建一个64位可执行文件。

在构建要安装的Python模块之前，请确保已安装并正确配置构建模块的相应软件。只有在构建模块的主机上才需要构建环境。

您可以使用Greenplum数据库实用程序gpssh和gpscp在Greenplum数据库主机上运行命令并将文件复制到主机。

测试已安装的Python模块

您可以创建一个简单的PL/Python用户定义函数（UDF）来验证Python模块在Greenplum数据库中是否可用。此示例测试NumPy模块。

此PL/Python UDF导入NumPy模块。如果导入模块成功，则函数返回SUCCESS;如果发生导入错误，则返回FAILURE。

CREATE OR REPLACE FUNCTION plpy_test(x int)
returns text
as $$
  try:
      from numpy import *
      return 'SUCCESS'
  except ImportError, e:
      return 'FAILURE'
$$ language plpythonu;

创建一个包含每个Greenplum数据库segment实例上的数据的表。根据Greenplum数据库安装的大小，您可能需要生成更多数据以确保将数据分发到所有segment实例。

CREATE TABLE DIST AS (SELECT x FROM generate_series(1,50) x ) DISTRIBUTED RANDOMLY ;

此SELECT命令在segment主机上运行UDF，其中数据存储在primary实例中。

SELECT gp_segment_id, plpy_test(x) AS status
  FROM dist
  GROUP BY gp_segment_id, status
  ORDER BY gp_segment_id, status;

如果UDF在Greenplum数据库segment实例上成功导入Python模块，则SELECT命令返回SUCCESS。如果SELECT命令返回FAILURE，则可以找到segment实例主机的segment主机。 Greenplum数据库系统表gp_segment_configuration包含有关mirror和segment配置的信息。此命令返回segment ID的主机名。

SELECT hostname, content AS seg_ID FROM gp_segment_configuration
  WHERE content = seg_id ;

如果返回FAILURE，则可能是以下原因：

访问所需库时出现问题。对于NumPy示例，Greenplum数据库可能在访问OpenBLAS库或segment主机上的Python库时遇到问题。

在segment主机上以gpadmin用户身份运行命令时，请确保没有错误。此gpssh命令测试在segment主机mdw1上导入numpy模块。
```
gpssh -s -h mdw1 python -c "import numpy"
```
如果Python import命令未返回错误，则可能未在Greenplum数据库环境中配置环境变量。例如，在主机系统上安装Python包之后，可能尚未重新启动Greenplum数据库。

示例

此PL/Python UDF返回最多两个整数：

CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
  if (a is None) or (b is None):
      return None
  if a > b:
     return a
  return b
$$ LANGUAGE plpythonu;

您可以使用STRICT属性执行空值处理，而不是使用两个条件语句。

CREATE FUNCTION pymax (a integer, b integer) 
  RETURNS integer AS $$ 
return max(a,b) 
$$ LANGUAGE plpythonu STRICT ;

您可以使用SELECT命令运行用户定义的函数pymax。此示例运行UDF并显示输出。

SELECT ( pymax(123, 43));
column1
---------
     123
(1 row)

此示例返回针对表运行的SQL查询中的数据。这两个命令创建一个简单的表并向表中添加数据。

CREATE TABLE sales (id int, year int, qtr int, day int, region text)
  DISTRIBUTED BY (id) ;
INSERT INTO sales VALUES
 (1, 2014, 1,1, 'usa'),
 (2, 2002, 2,2, 'europe'),
 (3, 2014, 3,3, 'asia'),
 (4, 2014, 4,4, 'usa'),
 (5, 2014, 1,5, 'europe'),
 (6, 2014, 2,6, 'asia'),
 (7, 2002, 3,7, 'usa') ;

此PL/Python UDF执行SELECT命令，该命令从表中返回5行。 Python函数从输入值指定的行返回REGION值。在Python函数中，行编号从0开始。函数的有效输入是0到4之间的整数。

CREATE OR REPLACE FUNCTION mypytest(a integer) 
  RETURNS setof text 
AS $$ 
  rv = plpy.execute("SELECT * FROM sales ORDER BY id", 5)
  region =[]
  region.append(rv[a]["region"])
  return region
$$ language plpythonu EXECUTE ON MASTER;

运行此SELECT语句将从结果集的第三行返回REGION列值。

SELECT mypytest(2) ;

此命令从数据库中删除UDF。

DROP FUNCTION mypytest(integer) ;

此示例使用DO命令将前一示例中的PL/Python函数作为匿名块执行。在该示例中，匿名块从临时表中检索输入值。

CREATE TEMP TABLE mytemp AS VALUES (2) DISTRIBUTED RANDOMLY;
DO $$ 
  temprow = plpy.execute("SELECT * FROM mytemp", 1)
  myval = temprow[0]["column1"]
  rv = plpy.execute("SELECT * FROM sales ORDER BY id", 5)
  region = rv[myval]["region"]
  plpy.notice("region is %s" % region)
$$ language plpythonu;

参考

技术参考

有关Python语言的信息，参考https://www.python.org/。

有关PL/Python的信息，请参阅PostgreSQL文档https://www.postgresql.org/docs/9.4/plpython.html。

有关Python包索引（PyPI）的信息，参考https://pypi.python.org/pypi。

这些是可以安装的一些Python模块：

SciPy库提供用户友好且高效的数值例程，例如用于数值积分和优化的例程。 SciPy站点包括其他类似的Python库 http://www.scipy.org/index.html。
Natural Language Toolkit（nltk）是一个用于构建Python程序以处理人类语言数据的平台。 http://www.nltk.org/。有关安装工具包的信息，请参阅http://www.nltk.org/install.html。