R UDx and date types (duplicated)
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
Comments
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'