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)