Greenplum的PL/R语言扩展

该章节包含以下信息:

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

关于Greenplum数据库的PL/R

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

更多关于支持的PL/R版本的信息，请参阅 Greenplum数据库发布说明。

启用PL/R语言支持

对于需要使用的每个数据库，使用SQL命令 CREATE LANGUAGE 或者实用程序 createlang注册PL/R语言。因为 PL/R是不可信语言，只有超级用户可以使用数据库注册PL/R。例如，作为 gpadmin 系统用户使用数据库 testdb 运行命令注册该语言如下：

  1. $ createlang plr -d testdb

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/Rdata.frame

假设用户的PL/R函数返回一个R data.frame 作为输出， 除非使用数组的数组，否则需要一些工作来将PL/R中的 data.frame作为一个简单的SQL表:

  • 创建一个和用户的 data.frame 同样维度的 TYPE 在Greenplum数据库中：

    1. CREATE TYPE t1 AS ...

  • 使用这个 TYPE 定义用户的PL/R函数

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

下载和安装R包

R 包是包含了R语言函数和数据集的模块。用户可以在Greenplum数据库中安装R包来 扩展R和PL/R的功能。

注意： 如果用户扩展Greenplum数据库和增加段主机，用户必须在新主机安装R的时候安装R包。

  1. 对于R包，识别所有依赖的R包和每个包的网址。通过以下的导航页面选择给定的包找到信息：

    http://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数据库主机：

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

  3. 使用 gpscp 实用程序和hosts_all 文件去复制 tar.gz 文件到Greenplum数据库集群的所有节点的相同目录。 该 hosts_all 文件包含所有Greenplum 数据库段主机列表。用户需要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数据库段主机 (gpssh -f all_hosts)。从命令提示符下使用 R CMD INSTALL 命令安装该包。注意这需要root权限。例如，这里是R安装命令安装arm包。

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

  5. 确保每个包都安装在了所有段主机的 $R_HOME/library d目录下 (该 gpssh 可以用来安装包)。例如，该 gpssh 命令列出了R库目录的内容。

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

  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-2.12.0/bin目录中运行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()

所有没有出现在该矩阵中的安装包必须先安装，然后才能使用包中的函数。

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

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

从R命令行加载包。

  1. > library(" package_name ")

R 包的删除可以使用 remove.packages命令

  1. > remove.packages("package_name")

用户可以使用R命令 -e 选项从命令行中运行函数。例如，该命令显示了R包 MASS的帮助信息。

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

参考

http://www.r-project.org/ - R项目主页

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

R 文档和Greenplum数据库R包一起安装：

$GPHOME/ext/R-3.3.1/doc

R 函数和参数

在R中传递数据值

R中的聚集函数