Hi,
 
this is an extension of the 
http://developer.teradata.com/blog/amarek/2013/11/how-to-use-jdbc-preparedstatement-batch-insert-with-r-0
blog. 
The initial idea and code example comes from amarek!
For what ever reason I was not able to post the code below with an comment. There seems to be an issue with the developer.teradata.com page.
 
Modify dim to generate more or less data. 
 
1 mio rows had been loaded in 5 min in my environment. Apply might be faster but didn't got this working right away.
 
Next challange would be to make this more generic in a way to be able to create a TD table for a given data.frame and load the data.frame afterwards ;-).
 
library(RJDBC)
################
#def functions
################
myinsert <- function(arg1,arg2){
  .jcall(ps,"V","setInt",as.integer(1),as.integer(arg1))
  .jcall(ps,"V","setString",as.integer(2),arg2)
  .jcall(ps,"V","addBatch")
}


MHmakeRandomString <- function(n=1, lenght=12)
{
  randomString <- c(1:n)                  # initialize vector
  for (i in 1:n)
  {
    randomString[i] <- paste(sample(c(0:9, letters, LETTERS),
                                    lenght, replace=TRUE),
                             collapse="")
  }
  return(randomString)
}

################
#DB Connect
################
.jaddClassPath("/MyPath/terajdbc4.jar")
.jaddClassPath("/MyPath/tdgssconfig.jar")
drv = JDBC("com.teradata.jdbc.TeraDriver","/MyPath/tdgssconfig.jar","/MyPath/terajdbc4.jar")
conn = dbConnect(drv,"jdbc:teradata://MyServer/CHARSET=UTF8,LOG=ERROR,DBS_PORT=1025,TYPE=FASTLOAD,TMODE=TERA,SESSIONS=1","user","password") 

################
#main
################

##gen test data
dim = 1000000
i = 1:dim
s = MHmakeRandomString(dim,12)

## set up table
dbSendUpdate(conn,"drop table foo;")
dbSendUpdate(conn,"create table foo (a int, b varchar(100));")

#set autocommit false
.jcall(conn@jc,"V","setAutoCommit",FALSE)
##prepare
ps = .jcall(conn@jc,"Ljava/sql/PreparedStatement;","prepareStatement","insert into foo values(?,?)")

#start time
ptm <- proc.time()

## batch insert
for(n in 1:dim){ 
  myinsert(i[[n]],s[[n]])
}
#run time
proc.time() - ptm

#apply & commit
.jcall(ps,"[I","executeBatch")
dbCommit(conn)
.jcall(ps,"V","close")
.jcall(conn@jc,"V","setAutoCommit",TRUE)

#get some sample results
dbGetQuery(conn,"select top 100 * from foo")
dbGetQuery(conn,"select count(*) from foo")

#disconnect
dbDisconnect(conn)

 

 
Discussion
jeffreyw 1 comment Joined 11/12
06 Jan 2014

Hi, this is great!  Do you know if it's possible to pass a vector to 
.jcall(ps,"V","setInt",as.integer(1),as.integer(arg1)
that way we would be able to construct the calls programatically to export data frames

ulrich 51 comments Joined 09/09
17 Jan 2014

Hi, didn't tried it so far.
Have you in the mean time?
Maybe I find next week some time to check this...
Ulrich

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

frank.l 1 comment Joined 12/14
23 Dec 2014

Sorry, I'm not too familiar with the jcall command. In this scenario, what does 'as.integer(1)' do for
 .jcall(ps,"V","setInt",as.integer(1),as.integer(arg1))
Is it naming that column?

You must sign in to leave a comment.