Greenplum的PL/Python语言扩展

• 关于Greenplum的PL/Python
• 使用PL/Python开发函数
• 示例

上一个话题: Greenplum数据库参考指南

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

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

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

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

启用和删除PL/Python支持

PL/Python语言与Greenplum数据库一起安装。为了在数据库中创建和运行PL/Python 用户定义的函数（UDF）,用户必须使用数据库注册PL/Python语言。

启用PL/Python支持

对于每个需要使用的数据库，请使用 SQL 命令 CREATE LANGUAGE 或 Greenplum数据库功能 createlang 注册PL/Python语言。因为PL/Python是不可信的语言，只有超级用户可以使用数据库注册 PL/Python。 例如， 作为gpadmin 系统用户使用名为 testdb 的数据库注册 PL/Python时运行以下命令:

PL/Python 被注册了成一种不可信语言。

删除PL/Python支持

对于一个不再需要PL/Python语言支持的数据库来说, 可以使用 SQL 命令 DROP LANGUAGE 或者 Greenplum数据库 droplang 功能删除对PL/Python的支持。 因为PL/Python是一种不可信语言, 只有超级用户能够从数据库中删除对PL/Python语言的支持。例如，作为 gpadmin 系统用户使用名为testdb的数据库删除对PL/Python语言的支持时运行以下命令:

$ droplang plpythonu -d testdb

当用户删除对PL/Python语言的支持时, 用户在数据库中创建的PL/Python用户自定义函数将不再有效。

PL/Python 用户定义函数的主体是Python脚本。 当函数被调用时，其参数被作为数组args[]的元素传递。命名参数也作为普通变量传递给Python脚本。 结果是通过PL/Python函数中的 return 语句返回的, 或者是通过 yield 语句返回结果集。

数组和列表

用户将 SQL 列表的值传递给具有Python列表的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/Pythonfunction "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)

参考 PostgreSQL 中的Arrays, Lists 文档寻求关于PL/Python处理数组和复合类型的附加信息。

执行和准别 SQL 查询

PL/Python中的plpy 模块提供了两个Python函数去为一个查询语句执行SQL查询和准备执行计划, plpy.execute and plpy.prepare。 准备查询的执行计划是有用的，如果用户从多个Python函数中运行查询。

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行。 结果集储存在the 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/8.3/static/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:
    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 and plpy.fatal 抛出一个Python异常，该异常如果没有被捕获, 则会被传播到调用查询, 导致当前事务和子事务终止。 该函数 raise plpy.ERROR(msg) 和 raise plpy.FATAL(msg) 分别等效于调用 plpy.error 和 plpy.fatal。 其他消息函数仅仅生成不同优先级的消息。

使用字典 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 环境必须将该模块添加到集群中所有的段主机和镜像主机上。当扩展 Greenplum数据库时，用户必须添加该Python模块到新的段主机中。用户可以使用 Greenplum数据库实用程序 gpssh 和 gpscp 在 Greenplum数据库主机上运行命令并将文件复制到主机。 更多关于实用程序的信息，请参阅 Greenplum数据库实用程序指南 。

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

为了检查Python环境，用户可以使用 which 命令：

该命令返回Python的安装路径。和 Greenplum数据库一起安装的Python 实在 Greenplum数据库中 ext/python 目录下。

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

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

在构建一个安装的模块之前，请确保安装并正确配置相应的构建模块的软件。构建环境仅在构建模块的主机上需要。

安装和测试Python模块的列子：

• 简单Python模块安装例子 (setuptools)
• 测试安装的Python模块

此例从Python包索引储存库手动安装Python setuptools 模块。该模块可让用户轻松下载，构建，安装，升级和卸载Python包。

该示例首先从一个程序包生成模块，并将该模块安装到在一单个主机上，然后该模块将被构建并安装在段主机上。

1. 从Python包索引站点获取模块包，例如，以 gpadmin 用户身份在 Greenplum数据库主机上运行 wget 命令，来获得tar文件

   wget --no-check-certificate https://pypi.python.org/packages/source/s/setuptools/setuptools-18.4.tar.gz

2. 从tar文件中提取文件。

   tar -xzvf distribute-0.6.21.tar.gz

3. 转到包含包文件的目录，并运行Python脚本来构建和安装Python包

   cd setuptools-18.4
   python setup.py build && python setup.py install

4. 如果模块已经安装好，则接下来的Python命令将不返回错误。

   python -c "import setuptools"

5. 使用 gpscp 工具将包复制到 Greenplum数据库主机。例如，这个命令从当前主机复制tar文件到文件 remote-hosts中所列的主机中。

6. 使用gpssh 在 remote-hosts文件列出的主机中运行命令构建, 安装和测试该软件包。该文件 remote-hosts 列出了所有Greenplum数据库的段主机：

   gpssh -f remote_hosts
   >>> tar -xzvf distribute-0.6.21.tar.gz
   >>> cd setuptools-18.4
   >>> python setup.py build && python setup.py install
   >>> python -c "import setuptools"
   >>> exit

setuptools 包安装了 easy_install 使用程序，该程序可以使用从Python包索引存储库安装Python包。例如，这个命令从Python索引站点安装Python PIP 实用程序。

easy_install pip

用户可以使用 gpssh 实用程序在所有Greenplum数据库段主机中运行 easy-install 命令。

复杂的Python安装例子 (NumPy)

这个例子构建和安装了Python模块 NumPy。NumPy是Python的一个科学计算模块。更多关于NumPy的信息，请参阅 http://www.numpy.org/。

构建 NumPy 包所需的软件：

• OpenBLAS 库,一个开源的 BLAS 实现(基础线性代数子程序)。
• gcc 编译器: gcc, gcc-gfortran, and gcc-c++。这些编译器需要来构建OpenBLAS 库。参阅

这个例子过程假定 yum 安装在Greenplum数据库段主机上，而且 gpadmin 用户是主机上具有root权限的 sudoers 成员。

下载 OpenBLAS 和 NumPy 源文件。例如，这些 wget命令下载tar文件到目录packages中：

wget --directory-prefix=packages http://github.com/xianyi/OpenBLAS/tarball/v0.2.8
wget --directory-prefix=packages http://sourceforge.net/projects/numpy/files/NumPy/1.8.0/numpy-1.8.0.tar.gz/download

将软件分发到Greenplum数据库主机。例如，如果用户将软件下载到/home/gpadmin/packages 这些命令在主机中创建文件夹并且将软件复制到 gpdb_remotes 文件所列的主机中。

gpssh -f gpdb_remotes mkdir packages 
gpscp -f gpdb_remotes packages/* =:/home/gpadmin/packages

OpenBLAS 先决条件

如果需要的话，使用 yum 从系统仓库安装 gcc 编译器。所有用户编译OpenBLAS的主机都需要该编译器：

sudo yum -y install gcc gcc-gfortran gcc-c++

注意： 如果用户不能使用yum安装正确的编译器版本，用户可以从源文件下载gcc编译器（包括gfortran），并进行安装。这两个命令下载并安装该编译器：

wget http://gfortran.com/download/x86_64/snapshots/gcc-4.4.tar.xz
tar xf gcc-4.4.tar.xz -C /usr/local/

如果用户是从tar文件手动安装的gcc编译器，添加新的gcc库到 PATH 和 LD_LIBRARY_PATH中：

export PATH=$PATH:/usr/local/gcc-4.4/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/gcc-4.4/lib

创建一个符号链接到 g++ 并称其为 gxx

sudo ln -s /usr/bin/g++ /usr/bin/gxx

用户可能也需要创建一个符号链接到具有不同版本的库例如 libppl_c.so.4 到 libppl_c.so.2.

如果需要的话，用户可以使用 gpscp 实用工具来复制文件到 Greenplum 数据库的主机中，并使用 gpssh 实用工具在主机上运行命令。

构建和安装 OpenBLAS 库

在构建和安装 NumPy 模块之前，用户应安装 OpenBLAS 库。本节介绍如何在单个主机上构建和安装该库。

1. 这些命令从 OpenBLAS tar文件中提取文件，并简化了包含OpenBLAS文件的目录名。

   tar -xzf packages/v0.2.8 -C /home/gpadmin/packages
   mv /home/gpadmin/packages/xianyi-OpenBLAS-9c51cdf /home/gpadmin/packages/OpenBLAS

2. 编译 OpenBLAS。这些命令设置 LIBRARY_PATH 环境变量并运行make命令构建OpenBLAS库。

   cd /home/gpadmin/packages/OpenBLAS
   export LIBRARY_PATH=$LD_LIBRARY_PATH
   make FC=gfortran USE_THREAD=0

3. 这些命令作为root用户在 /usr/local 目录下安装OpenBLAS库并且将文件的所有者改为gpadmin用户。

   cd /home/gpadmin/packages/OpenBLAS/
   sudo make PREFIX=/usr/local install
   sudo ldconfig
   sudo chown -R gpadmin /usr/local/lib

这些是安装的库和创建的符号链接：

libopenblas.a -> libopenblas_sandybridge-r0.2.8.a
libopenblas_sandybridge-r0.2.8.a
libopenblas_sandybridge-r0.2.8.so
libopenblas.so -> libopenblas_sandybridge-r0.2.8.so
libopenblas.so.0 -> libopenblas_sandybridge-r0.2.8.so

所有的Greenplum数据库主机 (主机和段主机) 都有相同的配置。用户可以从构建它们的系统中复制 OpenBLAS库，而不是在所有主机上构建OpenBlas库。例如，这些 gpssh 和 gpscp 命令在 gpdb_remotes 文件列出的主机中复制并且安装了OpenBLAS库。

gpssh -f gpdb_remotes -e 'sudo yum -y install gcc gcc-gfortran gcc-c++'
gpssh -f gpdb_remotes -e 'ln -s /usr/bin/g++ /usr/bin/gxx'
gpssh -f gpdb_remotes -e sudo chown gpadmin /usr/local/lib
gpscp -f gpdb_remotes /usr/local/lib/libopen*sandy* =:/usr/local/lib
gpssh -f gpdb_remotes
>>> cd /usr/local/lib
>>> ln -s libopenblas_sandybridge-r0.2.8.a libopenblas.a
>>> ln -s libopenblas_sandybridge-r0.2.8.so libopenblas.so
>>> ln -s libopenblas_sandybridge-r0.2.8.so libopenblas.so.0
>>> sudo ldconfig

构建和安装 NumPy

在用户安装完OpenBLAS库之后，用户可以构建和安装NumPy模块。 这些步骤可以在单个主机中安装NumPy模块。用户可以使用 gpssh 工具在多主机中构建和安装NumPy模块。

1. 转到 packages 子目录并且获取NumPy模块源码并解压该文件。

   cd /home/gpadmin/packages
   tar -xzf numpy-1.8.0.tar.gz

2. 转到NumPy目录并构建和安装NumPy。构建NumPy包可能需要一些时间。

   cd numpy-1.8.0
   python setup.py build
   python setup.py install

   注意： 如果NumPy模块没有成功构建，那NumPy构建过程可能需要一个指定OpenBLAS库位置的 site.cfg 。 创建文件 site.cfg 在NumPy包目录中：

   cd ~/packages/numpy-1.8.0
   touch site.cfg

   添加以下脚本到 site.cfg 文件中并再次运行NumPy构建命令：

3. 以下的Python命令确保该模块可用于在主机系统上由Python导入。

   python -c "import numpy"

如同在简单的模块安装中一样，用户可以使用 gpssh 工具来构建，安装和测试Greenplum数据库中段主机中的模块。

在构建NumPy时所需的环境变量，在 gpadmin 用户环境中运行Python NumPy函数时同样需要。用户可以使用 gpssh 工具 和 echo 命令将环境变量添加到 .bashrc 文件中。例如，这些 echo 命令添加环境变量到用户家（user home）目录下的 .bashrc 文件中。

echo -e '\n#Needed for NumPy' >> ~/.bashrc
echo -e 'export BLAS=/usr/local/lib/libopenblas.a' >> ~/.bashrc
echo -e 'export LAPACK=/usr/local/lib/libopenblas.a' >> ~/.bashrc
echo -e 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib' >> ~/.bashrc

用户可以创建一个简单的PL/Python用户自定义函数 (UDF) 来验证Greenplum数据库中的Python模块可用。这个例子测试的是 NumPy 模块。

这个PL/PythonUDF 导入 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数据库段实例上的数据的表。根据用户的Greenplum数据库安装的大小，用户可能需要生成更多数据，以确保数据分发到所有段实例。

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

该 SELECT 命令在数据存储在主段实例中的段主机上运行 UDF。

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

如果在Greenplum数据库段实例中UDF成功导入Python模块，该 SELECT 命令返回 SUCCESS 。如果该 SELECT 命令返回 FAILURE，用户可以找到段实例主机的段主机。Greenplum数据库系统表 gp_segment_configuration 包含镜像和段配置信息。该命令返回段ID的主机名。

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

如果返回 FAILURE，有以下几种可能：

• 访问所需库时遇到问题。对于NumPy例子，Greenplum数据库可能在访问段主机上的OpenBLAS库或者Python库时遇到问题。

  请确保用户以 gpadmin 用户在端主机上运行命令时没有返回错误。该 gpssh 命令测试在段主机 mdw1上导入numpy模块。

  gpssh -h mdw1 python -c "import numpy"

• 如果Python import 命令没有返回错误， 则环境变量可能没有在Greenplum数据库环境中配置。例如，变量不在 .bashrc 文件中，或者Greenplum数据库在向 .bashrc 文件中添加环境变量之后没有重启。

  确保环境变量被正确设置，然后重新启动Greenplum数据库。对于NumPy例子，确保被列在 末尾部分的环境变量被定义在主机和段主机上gpadmin 用户的.bashrc文件中。

  注意: 在 Greenplum数据库主机和段主机上，gpadmin用户的.bashrc 文件必须来源于文件 $GPHOME/greenplum_path.sh。

该PL/PythonUDF（用户自定义函数）返回两个整数的较大者：

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 ;

用户可以运行用户自定义函数 pymax 通过用 SELECT 命令。该例子运行用户自定义函数并且显示了输出。

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/PythonUDF 执行了一个从表中返回5行的 SELECT 命令。Python函数从输入值指定的行返回 REGION 值。在Python函数中，行标从0开始。该函数有效的输入是0到4之间的整数。

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

运行该 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文档 。

更多关于Python包索引（PyPI）的信息，请参阅 https://pypi.python.org/pypi。

这些是一些可以下载的Python模块：

• SciPy 库提供用户友好和高效的数值例程，例如数值积分和优化的例程 。该 wget 命令可以下载 SciPy 包的tar文件。

• 注意： 对nltk来说，Python Distribute包 https://pypi.python.org/pypi/distribute是必须的。该 Distribute 模块应该安装ntlk包。该 wget 命令可以下载Distribute包的tar文件。