A wider test case on R JDBC fastload

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Senior Supporter
Hi,

this is an extension of the 


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)

3 Comments

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

Senior Supporter

Hi, didn't tried it so far.

Have you in the mean time?

Maybe I find next week some time to check this...

Ulrich

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?