R UDx and date types

mgv12 · July 2019

Hi all,

I am writting a UDTF in Vertica using the R API, and I am struggling with a date column.

The test I am running uses a mock-up table which I have created:

CREATE TABLE  dataset(key VARCHAR, ds DATE,  y FLOAT);

Then I introduce values, and finally, if I do a SELECT ds,y FROM dataset LIMIT 5 I get:

ds          y
2015-09-01  357692.2
2015-09-09  434024.13
2015-09-13  340050.08
2015-09-14  368415.6
2015-09-17  405689.05

Afterwards, I write the following code in R:

myUDx <- function(input.data.frame, parameters.data.frame) {
    # assert that the number of period is defined
    if ( is.null(parameters.data.frame[['parameter1']]) ) {
    stop("NULL value for future parameter1! parameter1 cannot be NULL.")
    } else {
    parameter1 <- parameters.data.frame[['parameter1']]
    }

    wd <- weekdays(as.Date(input.data.frame$ds))
    y <- input.data.frame[,2]*parameter1
    final.output <- data.frame(wd,y)
    return(final.output)
}

myUDxFactory <- function() {
list(name    = myUDx,
    udxtype = c("transform"),
    # Since this is a polymorphic function the intype must be any
    intype  = c("date", "float"),
    outtype = c("date", "float"),
    parametertypecallback=myUDxParameters)
}


myUDxParameters <- function() {
    parameters <- list(datatype = c("int"),
                     length   = c("NA"),
                     scale    = c("NA"),
                     name     = c("parameter1"))
    return(parameters)
}

And I call it using the SQL script:

-- Sales Tax Calculator SQL Test
CREATE OR REPLACE LIBRARY rLib AS '/home/dbadmin/R/weekdays.R' LANGUAGE 'R';
CREATE OR REPLACE TRANSFORM FUNCTION myUDX AS LANGUAGE 'R' NAME 'myUDxFactory' LIBRARY rLib FENCED;

-- Show content
DROP TABLE IF EXISTS forecast;
SELECT key, myUDx(ds, y USING PARAMETERS parameter1=0.5) OVER (PARTITION BY key) FROM dataset;

The error I get is:
vsql:weekdays_call.sql:7: ERROR 3399: Failure in UDx RPC call InvokeProcessPartition(): Error calling processPartition() in User Defined Object [myUDX] at [/data/qb_workspaces/jenkins2/ReleaseBuilds/Grader/REL-9_2_1-x_grader/build/vertica/OSS/UDxFence/RInterface.cpp:1401], error code: 0, message: Exception in processPartitionForR: [Evaluation error: 'origin' must be supplied.]

The problem seems to be in the date data. If I set a stop(paste("class ",class(input.data.frame$ds))) before the crash I get that the class is numeric, ds values are something like 1441065600. instead of a real date like '2015-09-01'.

Does anybody know how to deal with date columns? Or why the values are changing inside of my UDx?
Thanks

R UDx and date types

Leave a Comment