The groupedapply mode

This mode is equivalent to the tapply mode, but it can be executed without the nzLibrary for R client package. It exhibits similar behavior, but enables you to run an aggregating table function and use an arbitrary SQL query, so long as it conforms with some basic requirements.

The R file must contain:

nz.mode: Set to groupedapply.
nz.fun: The function run on processed groups.

The R file might additionally contain:

nz.shaper: The shaper function; required if output set to TABLE(ANY).
nz.shaper.args: Optional.
nz.shaper.list: Required if shaper is set to std.

This R source file might undergo the usual compilation and registration steps, or can be used directly as described In the Communication Channels section.

When a selection is made, move to the next step, which is the SQL query:

SELECT ae_output_t.* FROM (SELECT
ROW_NUMBER() OVER (PARTITION BY pp ORDER BY oo) AS rn,
COUNT(*) OVER (PARTITION BY pp) AS ct,
tt.*
FROM tt) as input_t,
TABLE WITH FINAL (nzr..r_udtf_any(rn, ct, tt.cc,...,
'<R code>')) as ae_output_t;

The noteworthy elements are:

tt: The input table.
rn and ct: The control columns, which are processed by Netezza R code that is invoked when the groupedapply mode is chosen.
r_udtf_any: The R Adapter UTDF, which is a standard UDTF that internally invokes R and returns TABLE(ANY).

The control columns are used to determine when a new partition starts and ends. The internal R loop looks similar to:

while (getNext()) {
rn <- getInputColumn(0)
ct <- getInputColumn(1)
if (rn == 1) {
# create a new data frame for this partition
}
# fetch the current row
if (ct == rn) {
# process the frame calling the user-provided
# function and return the function result
}
}

The R loop assumes that the first two columns are the row number and the row count. Based on their values, it creates a data.frame for each new data partition. When the number of rows reaches the total count, the user function is called and the frame is passed as its argument under the name of x.

As an example, consider the contents of grpdapp.R, which should then be saved under /nz/export/ae/workspace:

nz.fun <- function(x)return(mean(x))
nz.mode <- 'groupedapply'
nz.shaper <- 'std'
nz.shaper.list <- list(a=NZ.DOUBLE, b=NZ.DOUBLE,
c=NZ.DOUBLE, d=NZ.DOUBLE)

Given there is a table named iris (that is a copy of the standard R data set), you can call the following query:

SELECT ae_output_t.* FROM (SELECT
ROW_NUMBER() OVER (PARTITION BY species ORDER BY sepal_length) AS rn,
COUNT(*) OVER (PARTITION BY species) AS ct,
iris.*
FROM iris) AS input_t,
TABLE WITH FINAL
(nzr..r_udtf_any(rn, ct, sepal_length, sepal_width,
petal_length, petal_width,
CAST('PLAIN_PATH=/nz/export/ae/workspace/grpdapp.R' AS
VARCHAR(64)))) AS ae_output_t;

which produces output similar to:

A | B | C | D
-------+-------+-------+-------
6.588 | 2.974 | 5.552 | 2.026
5.006 | 3.428 | 1.462 | 0.246
5.936 | 2.77 | 4.26 | 1.326
(3 rows)

The same results can be achieved with the following query:

SELECT ae_output_t.* FROM (SELECT
ROW_NUMBER() OVER (PARTITION BY species ORDER BY sepal_length) AS rn,
COUNT(*) OVER (PARTITION BY species) AS ct,
iris.*
FROM iris) AS input_t,
TABLE WITH FINAL
(nzr..r_udtf_any(rn, ct, sepal_length, sepal_width, petal_length, petal_width,
CAST('CODE_PLAIN="return(mean(x))", MODE=groupedapply,
SHAPER_LIST="a=NZ.DOUBLE,b=NZ.DOUBLE,c=NZ.DOUBLE,
d=NZ.DOUBLE"'
AS VARCHAR(128)))) AS ae_output_t;