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.
$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.
0
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.
I have also raised this issue with the support team. Will update once I get anything from them.
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]