How to use the Teradata JDBC Driver with R

Connectivity
Connectivity covers the mechanisms for connecting to the Teradata Database, including driver connectivity via JDBC or ODBC.
Teradata Employee

How to use the Teradata JDBC Driver with R

The RJDBC package enables an R application to use the Teradata JDBC Driver to access the Teradata Database.

DBI is the standard database interface for R, and RJDBC is an implementation of DBI that serves as an adapter to translate DBI actions into the corresponding JDBC actions. This combination enables R to use a JDBC Driver, such as the Teradata JDBC Driver.

R application RJDBC Teradata JDBC Driver Teradata Database

Configuring R to use the Teradata JDBC Driver

    1. Ensure that you have a Java Runtime Environment (JRE) installed on your client system.  Set the environment variable JAVA_HOME to the directory of the JRE. 
    1. Download and extract the Teradata JDBC Driver to a directory of your choice.  Note the directory of the extracted files as this will be used later to connect to the Teradata database as the ClasspathForTeradataJDBCDriverFiles.
    1. Download and install R[1] from http://www.r-project.org/
    1. Bring up RGui.
    1. Install RJDBC by selecting Packages ⇒ Install package(s)… from the RGui menu.

NOTE: Installing RJDBC will also install the dependencies “DBI” and “rJava”.

Connect to the Teradata Database using RJDBC

    1. Load RJDBC by selecting Packages ⇒ Load package… from the RGui menu

NOTE: Loading RJDBC will also load the required packages “DBI” and “rJava”.

The Teradata JDBC driver is ready to connect to the Teradata database.

Using the R Console, enter the following steps below to make a Teradata connection, submit a SQL query, and disconnect:

    1. drv = JDBC("DriverClassName","DirectoryofTeradataJDBCDriverFiles") <enter

Example:

drv = JDBC("com.teradata.jdbc.TeraDriver","c:\terajdbc\terajdbc4.jar;c:\terajdbc\tdgssconfig.jar")

NOTE: A path on a UNIX machine would use forward slashes to separate its components and a semicolon between files.

    1. conn = dbConnect(drv,"jdbc:teradata://DatabaseServerName/ParameterName=Value","User","Password") <enter

Example:

conn = dbConnect(drv,"jdbc:teradata://system/TMODE=ANSI","user1","password1")

NOTE: Connection parameters are optional. The first ParameterName is separated from the DatabaseServerName by a forward slash character.

    1. dbGetQuery(conn,"SQLquery") 

Example:

dbGetQuery(conn,"select * from dbc.dbcinfo")

 

    1. dbDisconnect(conn) 

Example:

dbDisconnect(conn)

Below is an example of an R script using the Teradata JDBC Driver to connect to a Teradata Database and submit queries.

library(RJDBC)
drv=JDBC("com.teradata.jdbc.TeraDriver","c:\terajdbc\terajdbc4.jar;c:\terajdbc\tdgssconfig.jar")
conn=dbConnect(drv,"jdbc:teradata://system","user1","password1")
dbSendUpdate(conn,"drop table employee")
dbSendUpdate(conn,"create table employee
   (
    empID INTEGER NOT NULL,
    empName VARCHAR(30) NOT NULL,
    empDept VARCHAR(50) NOT NULL,
    empJob VARCHAR(300),
    PRIMARY KEY(empID)
    )")
dbSendQuery(conn,"INSERT INTO employee VALUES(100001,'Mike Smith','Product Development','QA engineer')")
dbSendQuery(conn,"INSERT INTO employee VALUES(100002,'James Parker','Marketing','Manager')")
dbGetQuery(conn, "select * from employee order by 1")
dbDisconnect(conn)

The output from the R Console shows:





This article is written using R 3.3.0 (2016-05-03) for Windows.

Tags (3)
10 REPLIES
Enthusiast

Re: How to use the Teradata JDBC Driver with R

 

I am trying RJDBC but getting error on below command:

conn=dbConnect(drv,"jdbc:teradata://teradatatest","sysyser","pass123")

Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1], :

java.lang.NoClassDefFoundError: Could not initialize class com.teradata.tdgss.jtdgss.TdgssManager

 

I have verified that the system has TDGss installed already.

 

Any help you can provide with this error would be appreciated. Thanks.

-Suhail

Teradata Employee

Re: How to use the Teradata JDBC Driver with R

The Teradata JDBC Driver does not use the C-based TDGSS or TeraGSS packages, so it is not relevant whether or not they are installed.

 

The exception that you got is due to tdgssconfig.jar not being available on the classpath.

 

Please refer to the following links for more information, and a remedy.

 

https://community.teradata.com/t5/Connectivity/Unable-to-establish-connection-between-R-studio-and-T...

https://github.com/s-u/RJDBC/issues/35

 

Teradata Employee

Re: How to use the Teradata JDBC Driver with R

When you created the "drv" object, did you concatenate paths to both terajdbc4.jar and tdgssconfig.jar in the classPath specification?

Enthusiast

Re: How to use the Teradata JDBC Driver with R

The code from the link: ttps://github.com/s-u/RJDBC/issues/35 worked!! Thanks guys. Here is the code snipped we used:

 

 

library(RJDBC)
library(DBI)
library(rJava)

JAR_DIR <- "path/to/drivers/"
drv <- try(JDBC("com.teradata.jdbc.TeraDriver", paste0(JAR_DIR, 'terajdbc4.jar')))
.jaddClassPath(paste(JAR_DIR, "terajdbc4.jar", sep=""))
.jaddClassPath(paste(JAR_DIR, "tdgssconfig.jar", sep=""))
drv <- JDBC(driverClass="com.teradata.jdbc.TeraDriver") databse <- dbConnect(drv,"jdbc:teradata://tdsystemname/TMODE=ANSI,charset=UTF8,USER=sysuser,PASSWORD=pass123")

 

Now nxt question. Where do I specify the connection mechanism as LDAP? How do i make it use my LDAP password instead of my Teradata password?

 

-Suhail

 

Teradata Employee

Re: How to use the Teradata JDBC Driver with R

Documentation for the Teradata JDBC Driver connection parameters is available here:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BABJIHBJ

 

You must specify the LOGMECH=LDAP connection parameter in order to use LDAP.

 

You must specify your LDAP password as the value for the PASSWORD= connection parameter.

Enthusiast

Re: How to use the Teradata JDBC Driver with R

Thanks for the quick response Tom but unfortunately I am getting error when i use LOGMECH=LDAP in my jdbc url. here is my code:

 

databse <- dbConnect(drv,"jdbc:teradata://sysname/TMODE=ANSI,charset=UTF8,LOGMECH=LDAP,USER=sysuser,PASSWORD=myldappwd123")

Error:

2017-08-09.11:39:42.741 TERAJDBC4 ERROR [main] com.teradata.jdbc.jdk6.JDK6_SQL_Connection@5158b42f com.teradata.tdgss.jtdgss.TdgssCredential.<init>(Unknown Source) (Thread # main) : Invalid name provided (Mechanism level: [LDAPV3_ERR_INVALID_ARGUMENT] An argument was not valid.)

 

-Suhail

Teradata Employee

Re: How to use the Teradata JDBC Driver with R


Suhail,

The format of your call is correct. Could you please add the LOG=DEBUG parameter to the JDBC URL and provide the output it generates.  We need the more detailed infromation to determine what is failing. This article has information on collecting that information in R:

https://downloads.teradata.com/blog/amarek/2013/12/how-to-capture-jdbc-connection-parameter-logdebug...

 

Enthusiast

Re: How to use the Teradata JDBC Driver with R

i have the debug log but it won't fit as is in my reply and i can't figure out a way to attach it as a file to the forum. can you share your email address so that i can email it to you? Alternatively you can email me at suhailmemon@overstock.com and I'll be glad to share the file with you. Thanks.

-Suhail

Teradata Employee

Re: How to use the Teradata JDBC Driver with R

Suhail,

I sent you an email. Please reply and attach the debug output.

   Dave