Teradata R Package Function Reference | 17.00 - 17.00 - Connection and Database Management - Teradata R Package

Teradata® R Package Function Reference

prodname
Teradata R Package
vrm_release
17.00
created_date
September 2020
category
Programming Reference
featnum
B700-4007-090K

Connection overview

Connecting to Tereadata Vantage can be performed using the td_create_context function or the DBI::dbConnect function. Once the connection is established, a context must be initialized. The results of tdplyr analytic functions are stored as nonperistent tables in the SQL Engine database. The context allows the user specify a database to store these nonpersistent tables. The process of initializing a context can either be performed using the td_create_context function or the td_set_context function.

The td_remove_context function can be used to disconnect the connection and remove the context. Removing the context triggers the removal of all nonpersistent work tables, a process also known as garbage collection.

Supported drivers

TeradataNative Driver (>= 16.20.0.18)

Teradata ODBC driver (>= 16.20) - Use of tdplyr with Teradata ODBC Driver will be deprecated.

Use td_create_context to connect and initialize the context

The function td_create_context establishes the connection and initializes the context. There are two ways of creating connection.

library(tdplyr)
  1. Create context using Teradata ODBC Driver
con <- td_create_context("TeradataDSN")
  1. Create context using Teradata SQL driver for R
# host_name = Fully qualified domain name or IP address of the Teradata Vantage System.
# user_name = UserId for the connection.
# password  = Password for the connection.
con <- td_create_context(host = host_name, uid = user_name, pwd = password, dType = "native")

The class of the connection object is same for both the drivers.

class(con)[1] # The connection object is an object of class: "Teradata"
#> [1] "Teradata"

Use td_get_context to retrieve attributes of the current context

The td_get_context function returns a named list of attributes in the current context.

names(td_get_context())
#> [1] "connection"       "temp.database"    "default.database" "driver.type"

The connection object in the current context can also be retrieved using the td_get_context function.

con <- td_get_context()$connection

Use td_remove_context to disconnect the connection and remove the context

The function td_remove_context can be used to disconnect the connection and remove the context. Removing the context triggers the removal of all nonpersistent work tables, a process also known as garbage collection.

td_remove_context()

Specify a temporary database to store tables created by analytic functions

Analytic functions need to create temporary database objects (table / view) for processing data. The database where these objects are created can be specified using the temp.database argument in the td_create_context function. If the temp.database argument is not specified, then the default database of the connection is used.

  1. Specifying temporary database using Teradata ODBC driver.
td_create_context("TeradataDSN", temp.database = "DATABASE_1")
  1. Specifying temporary database using Teradata SQL driver for R.
con <- td_create_context(host = host_name, uid = user_name, pwd = password, dType = "native", temp.database = "DATABASE_1")
td_get_context()$temp.database
#> [1] "DATABASE_1"
td_remove_context()

Use td_set_context to initialize the context

If a connection is established using the DBI::dbConnect function, then use the td_set_context function to initialize the context.

  1. Set context using Teradata ODBC driver.
con <- DBI::dbConnect(odbc::odbc(), dsn = "TeradataDSN")
td_set_context(con)
  1. Set context using Teradata SQL driver for R.
con <- DBI::dbConnect(tdplyr::NativeDriver(), host = host_name, uid = user_name, pwd = password, dType = "native")
td_set_context(con)

If the temp.database argument is not specified in the td_set_context function, then the default database of the supplied connection object is used as the temporary database.

The function td_set_context can also be used to change the temporary database of the current context.

td_set_context(con, "DATABASE_1")
td_get_context()$temp.database
#> [1] "DATABASE_1"

Change the temporary database of an existing context

The temporary database in the current context can be changed using the td_set_context function without having to creating a new context.

  1. Changing temporary database using Teradata ODBC driver.
td_create_context("TeradataDSN", temp.database = "DATABASE_1")

# Change the temporary database in the current context
td_set_context(td_get_context()$connection, "DATABASE_2")
  1. Changing temporary database using Teradata SQL driver for R.
con <- td_create_context(host = host_name, uid = user_name, pwd = password, dType = "native", temp.database = "DATABASE_1")
td_get_context()$temp.database
#> [1] "DATABASE_1"

# Change the temporary database in the current context
td_set_context(td_get_context()$connection, "DATABASE_2")
td_get_context()$temp.database
#> [1] "DATABASE_2"

Database management functions

List ODBC data source names configured in your system.

odbc::odbcListDataSources()

View connection related information.

con <- td_get_context()$connection
DBI::dbGetInfo(con)

Alternatively, use the db_desc function to view the connection related information.

dplyr::db_desc(con)

The output of the db_desc function can be changed using the td.db_desc.output option. Set a named list containing db, driver, and machine to display the Advanced SQL Engine version, client driver version and the server machine name respectively.

options(td.db_desc.output = list(db = TRUE, driver = TRUE, machine = FALSE))
dplyr::db_desc(con)
#> [1] " [Teradata 16.20.50.01]  [Teradata Native Driver 16.20.0.35] "

View only the volatile tables active in the given connection.

# create a volatile table using the copy_to function
dplyr::copy_to(con, data.frame(col1 = c(1:3)), "volatile_table_1", temporary = TRUE)
# View all volatile tables active in the given connection
volatile_tables <- DBI::dbGetQuery(con, "help volatile table")
volatile_tables$"Table SQL Name"
#> [1] "volatile_table_1"

View column names for a given table.

DBI::dbListFields(con, volatile_tables$"Table SQL Name")

Execute a SQL query and retrieve the result in an R native data frame.

df <- DBI::dbGetQuery(con, "select database;")
df
#>   Database
#> 1    ALICE
td_remove_context()

View all tables in the default database of the current context. Note: This command also lists all the volatile (or temporary) tables active in the given connection. dbListTables is supported with ODBC connection only.

con1 <- td_create_context(dsn = "TeradataDSN")
tables <- DBI::dbListTables(con1, schema = td_get_context()$default.database, table_name = "%")
tables