Greenplum的PL/R语言扩展

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

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

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

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

    PL/R 被注册为可不信的语言。

    接下来是简单的PL/R例子。

    例子 1: 对单行操作符使用 PL/R。

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

    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函数

      下面的列子给出了示例的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
      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
      34.   FROM wj.plr_RE((SELECT wj.droi2_array), 
      35.   (SELECT cs FROM wj.droi2_array));

      注意： 如果用户扩展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

      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包：

      显示某个指定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命令

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

      - R项目主页

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

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

      $GPHOME/ext/R-3.3.1/doc

      R 函数和参数

      • 请参阅

      在R中传递数据值

      • 请参阅

      R中的聚集函数