R language aggregates 1

This example uses the following file name: maxae.R.

This example creates a UDA that finds the longest string and the highest numeric value from a set of input values and that returns the sum of these values.

Concept

The R Language Adapter defines the following functions that must be defined in the input source file.
  • nz.init
  • nz.accum
  • nz.merge
  • nz.final
These functions are called at run time in subsequent steps. To access the data, the functions should use the same API function as functions and table functions.

The C-Analytic-Executable API defines a special API for processing data in all states. In R, however, the processing has been simplified, and you can use the standard functions to access input data, output data, and state data. Two additional functions that can be called only in aggregation mode are the getState function and the getOutputColumn function.

The input, output, and state have the following meaning in the defined states:
Processing state Input API Output API

Initialize

None State
Accumulate Input State
Merge State Merge state
Finalize Merge state Output
According to the table, the following conditions apply:
  • In the initialize state, there is no input, and the output API is used to set the initial state values.
  • In the accumulate state, the input API accesses the input data from the input table, while the output API controls the state variables.
  • In the merge state, for each invocation of this function, another set of state variables is accessible on the input coming from the nodes and or dataslices performing data processing, while the output API controls the merge state variables.
  • In the finalize state, the merge state variables are accessible through the input API, and the output API controls the UDA output, which is then reported as the result of the SQL query/

Code

This example describes a UDA that finds the maximum value in the specified group.

Enter the following code in the /tmp/maxae.R file:
nz.mode <- 'aggregate'
nz.init <- function(){
setOutputNull(0)
}
nz.accum <- function(){
input <- getInputColumn(0)
if (is.null(input))
return()
state <- getOutputColumn(0)
if (is.null(state) || input > state)
setOutputInt32(0, input)
}
nz.merge <- function(){
input <- getInputColumn(0)
# if NULL is encountered, we can skip this value set
# since it must to be coming from an empty data slice
if (is.null(input))
return()
state <- getOutputColumn(0)
if (is.null(state) || input > state)
setOutputInt32(0, input)
}
nz.final <- function () {
input <- as.integer(getInputColumn(0))
if (is.null(input))
setOutputNull(0)
else
setOutputInt32(0, input)
}
Note: For the compilation step, you must add another object , the nz.mode variable to which you assign the given value: 'aggregate'. If you do not add this variable, you get an error during the compilation that the nz.fun object is not present. Setting nz.mode tells the compilation script that the AE is an aggregate. The script then looks for the functions that are listed in Concepts.

Compilation

Compile the code:
/nz/export/ae/utilities/bin/compile_ae --language r --template compile \
--version 3 --db dev --user nz /tmp/maxae.R

Registration

In this example, the --template switch takes the new value uda. In addition, the --state switch appears to define the aggregate state variables.
/nz/export/ae/utilities/bin/register_ae --language r --template uda \
--version 3 --db dev --user nz --sig 'maxae(INT4)' --return 'INT4' \
--state '(INT4)' --exe maxae.R

Running

Note: When you execute a UDA in the merge state, the set of input state variables include state variables from data slices with no data that is relevant to the aggregation query. As a result, there might be NULL input state values that must be handled in the merge function.
The following example includes special comments to emphasize this scenario. Before you run the aggregate, you must create a dummy table. After you run the aggregate, you get the following results:
CREATE TABLE grp_test (grp int4, val int4);
CREATE TABLE
INSERT INTO grp_test VALUES (1, 1);
INSERT 0 1
INSERT INTO grp_test VALUES (1, 2);
INSERT 0 1
INSERT INTO grp_test VALUES (1, 3);
INSERT 0 1
INSERT INTO grp_test VALUES (2, 4);
INSERT 0 1
SELECT maxae(val) FROM grp_test;
MAXAE
-------
4
(1 row)
SELECT grp, maxae(val) FROM grp_test GROUP BY grp;
GRP | MAXAE
-----+-------
2 | 4
1 | 3
(2 rows)
SELECT grp, maxae(val) over (partition BY grp) FROM grp_test;
GRP | MAXAE
-----+-------
1 | 3
1 | 3
1 | 3
2 | 4
(4 rows)