how to write a data frame to a volatile table in Teradata via R

Database
Enthusiast

how to write a data frame to a volatile table in Teradata via R

Hi all,

 

I'm using RJDBC in R to communicate with Teradata all the time, usually using 'dbSendQuery' to create table and 'dbGetQuery' to extract data from database.

 

However I cannot find a way to upload local dataframe to a volatile mutliset table. I tried 'dbWriteTable' but failed, the code and the error message is below:

dbWriteTable(conn_hopper, "table_name", table, row.names = FALSE, temporary = TRUE)
Error in .local(conn, statement, ...) : 
  execute JDBC update query failed in dbSendUpdate ([Teradata Database] [TeraJDBC 15.10.00.22] [Error 3524] [SQLState 42000] The user does not have CREATE TABLE access to database ACCESS_VIEWS.

I'm not sure why it says 'the user does not have the create table access'. I can create volatile table to this database.

 

Does anyone know how to deal with this issue? Thanks a lot!

 

Tags (1)
9 REPLIES
Supporter

Re: how to write a data frame to a volatile table in Teradata via R

Hi Iorriane_wang,

 

As far I see your error its a grant issue. Basically the user name that you login with should have access to create table in database. 

 

GRANT CREATE TABLE ON database TO user; --replace database and user with your database and username

 

I would recommend granting ALL in case you need to perform other DDL-DML operations as well.

 

Thanks,

Rohan Sawant

 

Enthusiast

Re: how to write a data frame to a volatile table in Teradata via R

Hi Rohan,

 

Thanks for your reply. I could create volatile table to this database, but I guess it because volatile table stays in my own session and does not require create table access to some database.

 

Unfortunately I cannot be assigned to this database that's why I am looking for a method to create volatile table rathe than permanent table. Do you have any idea on it?

 

Best,

Lorraine

Supporter

Re: how to write a data frame to a volatile table in Teradata via R

Hi Lorraine,

 

I missed out the fact that its a volatile table ( My bad!! :-( ). And yes volatile table are session specific not database specific. I just saw the error and it looked like an access issue and replied to it.

 

Secondly what exactly you are planning to implement. May be if we get to know the ultimate goal we may find a workaround.

 

Thanks,

Rohan Sawant

 

 

Enthusiast

Re: how to write a data frame to a volatile table in Teradata via R

Hi Rohan,

 

What I am trying to do is to upload the local data frame in R workspace to teradata, make the dataframe into a volatile table which will be joined with other volatile tables later.

 

Thanks!

Apprentice

Re: how to write a data frame to a volatile table in Teradata via R

Hi Lorraine,

 

Unless 'access_views' is your username then you will NOT be able to create a volatile table in that database.

 

Volatile tables can ONLY be created under your username.

 

Where you specify 'table_name' in your dbWriteTable code, does that include a databasename (i.e. 'Access_Views')? If it does then remove that databasename. Either replace it with your username or just leave it out.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: how to write a data frame to a volatile table in Teradata via R

Hi Dave,

 

Thanks for your reply. 'Access_Views' is the name of the database and I've checked that I have access to it.

 

The table name in my code is simply the name of the table I want to creat without 'Access_Views.', it does not work. Could you elaborate more on where to put my username in this command?


Thanks!

Apprentice

Re: how to write a data frame to a volatile table in Teradata via R

Hi Lorraine,

 

Firstly a disclaimer: I know nothing about using R. I'm coming at this from the Teradata end and can tell what is needed and/or not allowed from a Teradata SQL perspective. If the library that you're using adds in 'other' processing then we may need to work round that.

 

From a Teradata SQL perspective, in order to create a volatile table your syntax needs to be either:

CREATE VOLATILE TABLE t1...

or

CREATE VOLATILE TABLE username.t1...

(where 'username' is the name of the Teradata user issuing this command).

 

If you put any other name in front of the table name ('t1' in my example) then the command will fail. (although interestingly when I just tried this I got a different error message. There may be something else happening here...).

 

I've just googled the dbWriteTable command and that doesn't seem to allow the specification of a databasename of any sort.

 

Have you tried coding your username in the 'table' parameter? Something like "lorraine.table"?  where 'lorraine' is your Teradata username.(I've no idea if that will work).

 

Earlier in your code, do you set a 'default database', perhaps when you create the connection object?

 

Have a look at those, but I'm not sure I can take you much further forward.

 

Cheers,

Dave

 

 

 

 

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Supporter

Re: how to write a data frame to a volatile table in Teradata via R

Hi,

 

RJDBC has some issues with transaction handling etc.

below code is a bit ugly and needs a permanent table as interims but it might be a workaround....

 

library(RJDBC)
library(uuid)

.jaddClassPath("YourPath/terajdbc4.jar")
.jaddClassPath("YourPath/tdgssconfig.jar")
drv = JDBC("com.teradata.jdbc.TeraDriver",)

conn = dbConnect(drv,"jdbc:teradata://YourSystem/TMODE=ANSI","YourUser","YourPassword")

df = data.frame(matrix(rnorm(20), nrow=10))
df

loadToVolatileTable = function(dataframe,vtTableName,PI,connection,tempDB) {
  
  emptyDataFrame = dataframe[0,]
  currentDb = dbGetQuery(connection, "select database")
  user = dbGetQuery(connection, "select user")
  r = dbSendQuery(connection, paste("database " , tempDB))
  tmpTableNamme = gsub("-","_",paste("tmp_",UUIDgenerate(), sep = ""))

  dbWriteTable(connection, tmpTableNamme, emptyDataFrame, row.names = FALSE, temporary = FALSE)
  dbWriteTable(connection, tmpTableNamme, dataframe, row.names = FALSE,overwrite=FALSE,append = TRUE)
  
  dbSendUpdate(connection, paste("create volatile table ",user, ".", vtTableName," as (select * from ",tmpTableNamme,") with data primary index (",PI,") on commit preserve rows", sep = ""))
  dbSendUpdate(connection, paste("drop table ",tmpTableNamme, sep = ""))
  r = dbSendQuery(connection, paste("database " , currentDb, sep = ""))
  
}

loadToVolatileTable(df,"vt_test3","x1",conn,"YourWriteableDB")

r = dbGetQuery(conn, "select database")
r

dbReadTable(conn, "vt_test3")
r = dbGetQuery(conn, paste("show table vt_test3"))
r

dbDisconnect(conn)

What it does:

1. It changes the default DB to the DB you can write to

2. I creates an empty data frame and is using it to create a permanent but empty table

3. It loads the data into the table

4. It creates a VT table

5. it drops the VT table

6. It sets the default db back

 

ugly but works

 

Senior Supporter

Re: how to write a data frame to a volatile table in Teradata via R

5. it drops the permanent table ;-)