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

Can I open ODBC connection from within Vertica using R function?

In my R function, I am trying to connect to Vertica using RODBC.

$cat sample.R

sample <- function(test)
{
library(RODBC)
myconn <-odbcConnect("SFPDSN")
pundat <- sqlQuery(myconn, "select * from test_super;")
return(1)
}
sampleFactory <- function()
{
list(name=sample ,udxtype=c("transform"),intype=c("int"),outtype=c("int"))
}

"sqlQuery" is working when I execute these staments in R. But when I run my R function in Vertica it gives me error.

dbadmin=> \i sam.sql
Drop library sampleLib cascade;
DROP LIBRARY
CREATE LIBRARY sampleLib AS '/home/dbadmin/sample.R' LANGUAGE 'R';
CREATE LIBRARY
CREATE TRANSFORM FUNCTION sample AS NAME 'sampleFactory' LIBRARY sampleLib;
CREATE TRANSFORM FUNCTION
vsql:sam.sql:7: ERROR 3399:  Failure in UDx RPC call InvokeProcessPartition(): Error calling processPartition() in User Defined Object [sample] at [/scratch_a/release/vbuild/vertica/UDxFence/RInterface.cpp:1236], error code: 0, message: Exception in processPartitionForR: [first argument is not an open RODBC channel]
dbadmin=>


Below statement are working fine in R.
>library(RODBC)
>myconn <-odbcConnect("SFPDSN")
>pundat <- sqlQuery(myconn, "select * from test_super;")

Where "SFPDSN" is the odbc connection.

Comments


  • I suspect you cannot open ODBC connection in R-UDx. UDx are designed to run as a SQL function and data will always come from the table(s) that you are included in the FROM clause of the SELECT statement. If you need more data to process from a different table, I think you cannot use ODBC connection inside R-UDx. May be you could consider changing the SQL to include the data that you need to execute the function as R-UDx.
  • Thanks for the reply Sunil.

    I have also raised this issue with the support team. Will update once I get anything from them.
  • Error that I am getting:

    dbadmin=> Drop library sampleLib cascade;
    dbadmin=> CREATE LIBRARY sampleLib AS '/home/dbadmin/Ashok/sample.R' LANGUAGE 'R';
    dbadmin=> CREATE TRANSFORM FUNCTION sample AS NAME 'sampleFactory' LIBRARY sampleLib;
    dbadmin=> CREATE TABLE foo(a INT);
    dbadmin=> INSERT/*DIRECT*/ INTO foo VALUES(1);
    OUTPUT
    --------
        1
    (1 row)

    dbadmin=> SELECT sample(a) over() FROM foo;
    ERROR 3399:  Failure in UDx RPC call InvokeProcessPartition(): Error calling processPartition() in User Defined Object [sample] at [/scratch_a/release/vbuild/vertica/UDxFence/RInterface.cpp:1236], error code: 0, message: Exception in processPartitionForR: [first argument is not an open RODBC channel]
  • This works. Database needs to be restarted after setting VERTICAINI.

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.