Greenplum PL/R 语言扩展

本章包含以下信息：

Parent topic: Greenplum数据库参考指南

关于Greenplum数据库PL/R

PL/R是一种过程式语言。 使用Greenplum数据库PL/R扩展，您可以使用R编程语言编写数据库函数，并使用包含R函数和数据集的R包。

有关支持的PL/R版本的信息，请参阅Greenplum数据库发行说明。

启用PL/R语言支持

对于需要使用它的每个数据库，使用SQL命令CREATE EXTENSION注册PL/R语言。 由于PL/R是不可信的语言，因此只有超级用户才能将PL/R注册到数据库。 例如，以gpadmin用户身份运行此命令，以使用名为testdb的数据库注册语言：

  1. $ psql -d testdb -c 'CREATE EXTENSION plr;'

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

示例

以下是简单的PL/R示例。

示例1：将PL/R用于单行运算符

此函数使用R函数rnorm()生成具有正态分布的数字数组。

  1. CREATE OR REPLACE FUNCTION r_norm(n integer, mean float8, 
  2.   std_dev float8) RETURNS float8[ ] AS
  3. $$
  4.   x<-rnorm(n,mean,std_dev)
  5.   return(x)
  6. $$
  7. LANGUAGE 'plr';

以下CREATE TABLE命令使用r_norm函数填充表。 r_norm函数创建一个包含10个数字的数组。

  1. CREATE TABLE test_norm_var
  2.   AS SELECT id, r_norm(10,0,1) as x
  3.   FROM (SELECT generate_series(1,30:: bigint) AS ID) foo
  4.   DISTRIBUTED BY (id);

示例2：以表格形式返回PL/R data.frames

假设您的PL/R函数返回R data.frame作为其输出， 除非您想使用数组数组，否则需要做一些工作才能将PL/R中的data.frame看作一个简单的SQL表：

  • 在Greenplum数据库中创建一个与R data.frame具有相同尺寸的TYPE：

    1. CREATE TYPE t1 AS ...

  • 定义PL/R函数时使用此TYPE

    1. ... RETURNS SET OF t1 AS ...

下一个示例给出了示例SQL。

示例3：使用PL/R的分层回归

下面的SQL定义了一个TYPE并使用PL/R运行层次回归：

  1. --Create TYPE to store model results
  2. DROP TYPE IF EXISTS wj_model_results CASCADE;
  3. CREATE TYPE wj_model_results AS (
  4.   cs text, coefext float, ci_95_lower float, ci_95_upper float,
  5.   ci_90_lower float, ci_90_upper float, ci_80_lower float,
  6.   ci_80_upper float);
  8. --Create PL/R function to run model in R
  9. DROP FUNCTION IF EXISTS wj_plr_RE(float [ ], text [ ]);
  10. CREATE FUNCTION wj_plr_RE(response float [ ], cs text [ ])
  11. RETURNS SETOF wj_model_results AS
  12. $$
  13.   library(arm)
  14.   y<- log(response)
  15.   cs<- cs
  16.   d_temp<- data.frame(y,cs)
  17.   m0 <- lmer (y ~ 1 + (1 | cs), data=d_temp)
  18.   cs_unique<- sort(unique(cs))
  19.   n_cs_unique<- length(cs_unique)
  20.   temp_m0<- data.frame(matrix0,n_cs_unique, 7))
  21.   for (i in 1:n_cs_unique){temp_m0[i,]<-
  22.     c(exp(coef(m0)$cs[i,1] + c(0,-1.96,1.96,-1.65,1.65,
  23.       -1.28,1.28)*se.ranef(m0)$cs[i]))}
  24.   names(temp_m0)<- c("Coefest", "CI_95_Lower",
  25.     "CI_95_Upper", "CI_90_Lower", "CI_90_Upper",
  26.    "CI_80_Lower", "CI_80_Upper")
  27.   temp_m0_v2<- data.frames(cs_unique, temp_m0)
  28.   return(temp_m0_v2)
  29. $$
  30. LANGUAGE 'plr';
  32. --Run modeling plr function and store model results in a 
  33. --table
  34. DROP TABLE IF EXISTS wj_model_results_roi;
  35. CREATE TABLE wj_model_results_roi AS SELECT * 
  36.   FROM wj_plr_RE((SELECT wj_droi2_array),
  37.   (SELECT cs FROM wj_droi2_array));

下载和安装R包

R包是包含R函数和数据集的模块。 您可以安装R软件包以扩展Greenplum数据库中的R和PL/R功能。

Note: 如果扩容Greenplum数据库并添加segment主机，则必须在新主机的R安装中安装R软件包。 I

  1. 对于R包，标识所有相关R包和每个包Web URL。 通过从以下导航页面中选择给定的包，可以找到该信息：

    https://cran.r-project.org/web/packages/available_packages_by_name.html

    例如，R包arm的页面表明该包需要以下R库：Matrix，lattice，lme4，R2WinBUGS，coda，abind，foreign和MASS。

    您还可以尝试使用R CMD INSTALL命令安装软件包以确定依赖软件包。

    对于Greenplum数据库PL/R扩展中包含的R安装，所需的R软件包随PL/R扩展一起安装。 但是，Matrix包需要更新的版本。

  2. 从命令行，使用wget实用程序将arm包的tar.gz文件下载到Greenplum数据库master主机：

    1. wget https://cran.r-project.org/src/contrib/Archive/arm/arm_1.5-03.tar.gz
    1. wget https://cran.r-project.org/src/contrib/Archive/Matrix/Matrix_0.9996875-1.tar.gz

  3. 使用gpscp实用程序和hosts_all文件将tar.gz文件复制到Greenplum数据库集群的所有节点上的同一目录。 hosts_all文件包含所有Greenplum数据库segment主机的列表。 您可能需要root访问权限才能执行此操作。

    1. gpscp -f hosts_all Matrix_0.9996875-1.tar.gz =:/home/gpadmin
    1. gpscp -f /hosts_all arm_1.5-03.tar.gz =:/home/gpadmin

  4. 在交互模式下使用gpssh实用程序登录每个Greenplum数据库segment主机（gpssh -f all_hosts）。 使用R CMD INSTALL命令从命令提示符安装软件包。 请注意，这可能需要root访问权限。 例如，此R install命令安装arm包的包。

    1. $R_HOME/bin/R CMD INSTALL Matrix_0.9996875-1.tar.gz   arm_1.5-03.tar.gz

  5. 确保软件包安装在所有segment的$R_HOME/library目录中（gpssh可用于安装软件包）。 例如，此gpssh命令列出R库目录的内容。

    1. gpssh -s -f all_hosts "ls $R_HOME/library"

    gpssh选项-s在远程主机上运行命令之前获取greenplum_path.sh文件。

  6. 测试是否可以加载R包。

    如果可以加载R包，则此函数执行简单测试：

    1. CREATE OR REPLACE FUNCTION R_test_require(fname text)
    2. RETURNS boolean AS
    3. $BODY$
    4.     return(require(fname,character.only=T))
    5. $BODY$
    6. LANGUAGE 'plr';

    此SQL命令检查是否可以加载R包arm：

    1. SELECT R_test_require('arm');

显示R库信息

您可以使用R命令行显示有关Greenplum数据库主机上已安装的库和函数的信息。 您还可以在R安装中添加和删除库。 要在主机上启动R命令行，请以gadmin用户身份登录主机， 并从$GPHOME/ext/R-3.3.3/bin目录运行脚本R。

此R函数列出R命令行中的可用R包：

  1. > library()

显示特定R包的文档

  1. > library(help="package_name")
  2. > help(package="package_name")

显示R函数的帮助文件：

  1. > help("function_name")
  2. > ?function_name

要查看已安装的软件包，请使用R命令installed.packages()。 这将返回一个矩阵，其中包含已安装的每个包的行。 下面，我们来看看这个矩阵的前5行。

  1. > installed.packages()

必须先安装并加载任何未出现在已安装的软件包矩阵中的软件包，然后才能使用其函数。

可以使用install.packages()安装R包：

  1. > install.packages("package_name") 
  2. > install.packages("mypkg", dependencies = TRUE, type="source")

从R命令行加载包。

  1. > library(" package_name ")

可以使用remove.packages删除R包

  1. > remove.packages("package_name")

您可以使用R命令-e选项从命令行运行函数。 例如，此命令在R包MASS上显示帮助。

  1. $ R -e 'help("MASS")'

在启动时加载R模块

PL/R可以在解释器初始化期间自动加载保存的R代码。 要使用此功能，请创建plr_modules数据库表，然后将要自动加载的R模块插入表中。 如果表存在，PL/R会将其包含的代码加载到解释器中。

在Greenplum数据库系统中，表行通常是分布式的，因此每行只存在于一个segment实例中。 但是，每个segment实例的R解释器需要加载所有模块，因此正态分布的表将不起作用。 必须将plr_modules表创建为默认架构中的复制表，以便表中的所有行都出现在每个segment实例中。 例如：

  1. CREATE TABLE public.plr_modules {
  2.   modseq int4,
  3.   modsrc text
  4. ) DISTRIBUTED REPLICATED;

有关使用PL/R自动加载功能的更多信息，请参阅 https://www.joeconway.com/plr/doc/plr-module-funcs.html。

参考

https://www.r-project.org/ - R Project主页

https://cran.r-project.org/web/packages/PivotalR/ - PivotalR的主页，提供了一个R接口，用于对Greenplum数据库表和视图进行操作，类似于R data.frame。 PivotalR还支持直接从R使用机器学习包MADlib。

R文档随Greenplum R软件包一起安装：

$GPHOME/ext/R-3.3.3/doc

R函数和参数

在R中传递数据值

R中的聚合函数