Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Pass varchar parameter to R UDx function

I am having trouble sorting out how to pass a varchar parameter to an R UDx function.  Here are some details.

predictFactory <- function() {
list(name=vpredict,
udxtype=c("transform"),
# intype=c("varchar","float"),
intype=c("any"),
parametertypecallback=predictParams,
outtype=c("any"),
outtypecallback=predictReturnType)
}
predictParams <- function() {
param = data.frame(datatype=rep(NA,4),length=rep(NA,4),scale=rep(NA,4),name=rep(NA,4))
param[1,1] = "int"
param[1,4] = "fmths"
param[2,1] = "int"
param[2,4] = "etsOK"
param[3,1] = "varchar"
param[3,2] = 12
param[3,4] = "calcDate"
param[4,1] = "int"
param[4,4] = "test"
param
}

I call the 'R' function using

select predict(MonthDate, Qty using parameters fmths=12, etsOK=0, calcDate='1/3/2015') over(partition auto) into table fcast_data from hist_data;

Inside the 'R' function I try to recover the parameters using:

	# Handle parameters in the list
if(exists('fmths', where=y)) {
fMths = as.numeric(y[['fmths']])
} else {
fMths = 12 # default
}
if(exists('etsOK', where=y)) {
etsOK = as.logical(y[['etsOK']])
} else {
etsOK = TRUE # default
}
if(exists('calcDate', where=y)) {
calcDate = dmy(y[['calcDate']]) # in d/m/y or d-m-y format
} else {
calcDate = today() # default
}
if(exists('test', where=y)) {
testmode = as.logical(y[['test']])
} else {
testmode = FALSE # default
}

and I am using lubridate package to handle dates within 'R' so I can be flexible with the format of the date string passed in.

 

The trouble is calcDate inside 'R' never gets assigned the parameter value and so always gets set to the current date.

 

I have tried casting to a varchar using cast('1/3/2015' as varchar) in the parameter list but this does not work either.

 

Is anyone able to help me sort out what I am doing wrong?

 

I know I could pass the date as an int and convert that back to a date inside 'R', but would rather like to work out why using varchar does not work.

Comments

  • Well this is a surprise.  It appears that only lowercase characters are allowed in the parameter names.

    When I change the code to:

    predictParams <- function() {
    param = data.frame(datatype=rep(NA,4),length=rep(NA,4),scale=rep(NA,4),name=rep(NA,4))
    param[1,1] = "int"
    param[1,4] = "fmths"
    param[2,1] = "int"
    param[2,4] = "etsok"
    param[3,1] = "varchar"
    param[3,2] = 12
    param[3,4] = "calcdate"
    param[4,1] = "int"
    param[4,4] = "test"
    param
    }

    and

        if(exists('fmths', where=y)) {
    fMths = as.numeric(y[['fmths']])
    } else {
    fMths = 12 # default
    }
    if (debug == 2) vertica_log(toString(c("vpredict: parameter etsok: ", y[['etsok']])))
    if(exists('etsok', where=y)) {
    etsok = as.logical(y[['etsok']])
    } else {
    etsok = TRUE # default
    }
    if (debug == 2) vertica_log(toString(c("vpredict: parameter calcdate: ", y[['calcdate']])))
    if(exists('calcdate', where=y)) {
    calcdate = dmy(y[['calcdate']]) # in d/m/y or d-m-y format
    } else {
    calcdate = today() # default
    }
    if(exists('test', where=y)) {
    testmode = as.logical(y[['test']])
    } else {
    testmode = FALSE # default
    }

    and, of course, change the calling function to:

    select predict(MonthDate, Qty using parameters fmths=12,etsok=1,calcdate='1/3/2015', test=0)
    over(partition auto)
    into table fcastData
    from histData

    It now all works.

     

    HP what are you doing?? and why is it not documented!!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.