0

I am trying to read from an R dataframe into a table in Teadata with the FastLoad utility, using RJDBC. Is it possible to write a prepared statement and use .jcall to read directly from dataframe?

Some things I have noted/tried, but which do not seem to read directly from dataframe, from what I can tell:

http://developer.teradata.com/blog/amarek/2013/11/how-to-use-jdbc-preparedstatement-batch-insert-with-r-0

Teradata-jdbc: What's the point of using Fastload if java has memory limitations?

http://developer.teradata.com/connectivity/articles/speed-up-your-jdbcodbc-applications

https://downloads.teradata.com/blog/ulrich/2013/11/a-wider-test-case-on-r-jdbc-fastload

UPDATE .... the below suggestion by Parfait works for me:

library(RJDBC)

con <- dbConnect(... connection details ...)

dbSendUpdate (con, "Drop Table Dl_ho_test.Iris_R")

dbSendUpdate (con, "Create Multiset Table Dl_Ho_Test.Iris_R (
                      Sepal_Length float
                    , Sepal_Width float
                    , Petal_Length float
                    , Petal_Width float
                    , Species varchar(10)
                    ) No Primary Index;"
)

## def functions
myinsert <- function(col1, col2, col3, col4, col5){
  .jcall(ps, "V", "setDouble", as.integer(1), col1)
  .jcall(ps, "V", "setDouble", as.integer(2), col2)
  .jcall(ps, "V", "setDouble", as.integer(3), col3)
  .jcall(ps, "V", "setDouble", as.integer(4), col4)
  .jcall(ps, "V", "setString", as.integer(5), as.character(col5))
  .jcall(ps, "V", "addBatch")
}

## prepare
ps = .jcall(con@jc, "Ljava/sql/PreparedStatement;", "prepareStatement", "insert into Dl_Ho_Test.Iris_R(?,?,?,?,?)")

## batch insert
for(n in 1:nrow(iris)) {
  myinsert(iris$Sepal.Length[n], iris$Sepal.Width[n], iris$Petal.Length[n], iris$Petal.Width[n], iris$Species[n])
}

## apply & commit
.jcall(ps, "[I", "executeBatch")
dbCommit(con)
.jcall(ps, "V", "close")
.jcall(con@jc, "V", "setAutoCommit", TRUE)
Brent Gunderson
  • 173
  • 1
  • 8
  • @Parfait, thank you for the help, good suggestion. I edited the above, now I am iris dataset to make sharing easier. I get error "Error in .jcall (ps, "V", "setFloat, 1, col1): method setFloat with signature (DD)v not found. I tend to feel that the for(n in 1:nrow(iris)) is issue .... can JDBC recognize the data frame like that? – Brent Gunderson Apr 24 '19 at 19:09
  • Please include this comment under my answer. I would have totally missed it had your edit not bumped up on main page. And it appears the `setFloat` method is the issue here. Try `setDouble` with *double* column types. – Parfait Apr 24 '19 at 20:28

1 Answers1

0

Following last link, consider keeping the functional form and loop through rows of data frame:

## def functions
myinsert <- function(col1, col2, col3){
  .jcall(ps, "V", "setInt", 1, col1)
  .jcall(ps, "V", "setInt", 2, col2)
  .jcall(ps, "V", "setString", 3, col3)

  .jcall(ps, "V", "addBatch")
}

## prepare
ps = .jcall(con@jc, "Ljava/sql/PreparedStatement;", "prepareStatement", 
            "insert into Some_Test_Table(?,?,?)")

## batch insert
for(n in 1:nrow(my.data.frame)) { 
  myinsert(my.data.frame$col1[n], my.data.frame$col2[n], my.data.frame$col3[n])
}

## apply & commit
.jcall(ps, "[I", "executeBatch")
dbCommit(con)
.jcall(ps, "V", "close")
.jcall(conn@jc, "V", "setAutoCommit", TRUE)
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Sorry, am new to Stack Overflow, (not to mention R and JDBC). Your suggestion worked great for all those numeric fields! But now I have problem with column #5, which is a string type field. Modified code and error message is above. I also tried setAsciiStream, but that seemed to have error too – Brent Gunderson Apr 24 '19 at 21:20
  • The *Species* column in *iris* is a factor column. Try casting with `as.character()`. – Parfait Apr 24 '19 at 21:37
  • Above code works great! Many thanks for the quick help! – Brent Gunderson Apr 24 '19 at 21:49