Skip to main content

R UDx and date types (duplicated)

  • July 17, 2019
  • 1 reply
  • 5 views

mgv12

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

1 reply

Bryan_H
Forum|alt.badge.img+2
  • Participating Frequently
  • July 18, 2019

Hi, Vertica converts SQL data types to R data types as shown in the documentation:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ExtendingVertica/R/RAndVerticaDataTypes.htm
In this case, '2015-09-01' is converted from SQL DATE to 'numeric' 1441065600, which according to epochconverter.com is UNIX epoch seconds for 'September 1, 2015 12:00:00 AM UTC'