PL/R Language

PL/R Language

This chapter contains the following information:

About Greenplum Database PL/R

PL/R is a procedural language. With the Greenplum Database PL/R extension you can write database functions in the R programming language and use R packages that contain R functions and data sets.

For information about supported PL/R versions, see the Greenplum Database Release Notes.

Installing R

For RHEL and CentOS, installing the PL/R package installs R in $GPHOME/ext/R-<version> and updates $GPHOME/greenplum_path.sh for Greenplum Database to use R.

To use PL/R on Ubuntu host systems, you must install and configure R on all Greenplum Database host systems before installing PL/R.

Note: You can use the gpssh utility to run bash shell commands on multiple remote hosts.

  1. To install R, run these apt commands on all host systems.

    1. $ sudo apt update && sudo apt install r-base

    Installing r-base also installs dependent packages including r-base-core.

  2. To configure Greenplum Database to use R, add the R_HOME environment variable to $GPHOME/greenplum_path.sh on all hosts. This example command returns the R home directory.

    1. $ R RHOME
    2. /usr/lib/R

    Using the previous R home directory as an example, add this line to the file on all hosts.

    1. export R_HOME=/usr/lib/R

  3. Source $GPHOME/greenplum_path.sh and restart Greenplum Database. For example, run these commands on the Greenplum Database master host.

    1. $ source $GPHOME/greenplum_path.sh
    2. $ gpstop -r

Enabling PL/R Language Support

For each database that requires its use, register the PL/R language with the SQL command CREATE EXTENSION. Because PL/R is an untrusted language, only superusers can register PL/R with a database. For example, run this command as the gpadmin user to register the language with the database named testdb:

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

PL/R is registered as an untrusted language.

Note: Using the deprecated createlang command to enable PL/R generates an error.

Examples

The following are simple PL/R examples.

Example 1: Using PL/R for single row operators

This function generates an array of numbers with a normal distribution using the R function 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';

The following CREATE TABLE command uses the r_norm() function to populate the table. The r_norm() function creates an array of 10 numbers.

  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);

Example 2: Returning PL/R data.frames in Tabular Form

Assuming your PL/R function returns an R data.frame as its output, unless you want to use arrays of arrays, some work is required to see your data.frame from PL/R as a simple SQL table:

  • Create a TYPE in a Greenplum database with the same dimensions as your R data.frame:

    1. CREATE TYPE t1 AS ...

  • Use this TYPE when defining your PL/R function

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

Sample SQL for this is given in the next example.

Example 3: Hierarchical Regression using PL/R

The SQL below defines a TYPE and runs hierarchical regression using 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));

Downloading and Installing R Packages

R packages are modules that contain R functions and data sets. You can install R packages to extend R and PL/R functionality in Greenplum Database.

Note: If you expand Greenplum Database and add segment hosts, you must install the R packages in the R installation of the new hosts.

  1. For an R package, identify all dependent R packages and each package web URL. The information can be found by selecting the given package from the following navigation page:

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

    As an example, the page for the R package arm indicates that the package requires the following R libraries: Matrix, lattice, lme4, R2WinBUGS, coda, abind, foreign, and MASS.

    You can also try installing the package with R CMD INSTALL command to determine the dependent packages.

    For the R installation included with the Greenplum Database PL/R extension, the required R packages are installed with the PL/R extension. However, the Matrix package requires a newer version.

  2. From the command line, use the wget utility to download the tar.gz files for the arm package to the Greenplum Database master host:

    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. Use the gpscp utility and the hosts_all file to copy the tar.gz files to the same directory on all nodes of the Greenplum Database cluster. The hosts_all file contains a list of all the Greenplum Database segment hosts. You might require root access to do this.

    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. Use the gpssh utility in interactive mode to log into each Greenplum Database segment host (gpssh -f all_hosts). Install the packages from the command prompt using the R CMD INSTALL command. Note that this may require root access. For example, this R install command installs the packages for the arm package.

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

  5. Ensure that the package is installed in the $R_HOME/library directory on all the segments (the gpssh can be use to install the package). For example, this gpssh command list the contents of the R library directory.

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

    The gpssh option -s sources the greenplum_path.sh file before running commands on the remote hosts.

  6. Test if the R package can be loaded.

    This function performs a simple test to if an R package can be loaded:

    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';

    This SQL command checks if the R package arm can be loaded:

    1. SELECT R_test_require('arm');

Displaying R Library Information

You can use the R command line to display information about the installed libraries and functions on the Greenplum Database host. You can also add and remove libraries from the R installation. To start the R command line on the host, log into the host as the gadmin user and run the script R from the directory $GPHOME/ext/R-3.3.3/bin.

This R function lists the available R packages from the R command line:

  1. > library()

Display the documentation for a particular R package

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

Display the help file for an R function:

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

To see what packages are installed, use the R command installed.packages(). This will return a matrix with a row for each package that has been installed. Below, we look at the first 5 rows of this matrix.

  1. > installed.packages()

Any package that does not appear in the installed packages matrix must be installed and loaded before its functions can be used.

An R package can be installed with install.packages():

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

Load a package from the R command line.

  1. > library(" package_name ")

An R package can be removed with remove.packages

  1. > remove.packages("package_name")

You can use the R command -e option to run functions from the command line. For example, this command displays help on the R package MASS.

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

Loading R Modules at Startup

PL/R can automatically load saved R code during interpreter initialization. To use this feature, you create the plr_modules database table and then insert the R modules you want to auto-load into the table. If the table exists, PL/R will load the code it contains into the interpreter.

In a Greenplum Database system, table rows are usually distributed so that each row exists at only one segment instance. The R interpreter at each segment instance, however, needs to load all of the modules, so a normally distributed table will not work. You must create the plr_modules table as a replicated table in the default schema so that all rows in the table are present at every segment instance. For example:

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

See https://www.joeconway.com/plr/doc/plr-module-funcs.html for more information about using the PL/R auto-load feature.

References

https://www.r-project.org/ - The R Project home page

https://cran.r-project.org/web/packages/PivotalR/ - The home page for PivotalR, a package that provides an R interface to operate on Greenplum Database tables and views that is similar to the R data.frame. PivotalR also supports using the machine learning package MADlib directly from R.

The following links highlight key topics from the R documentation.