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)
- Create context using Teradata ODBC Driver
con <- td_create_context("TeradataDSN")
- 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.
- Specifying temporary database using Teradata ODBC driver.
td_create_context("TeradataDSN", temp.database = "DATABASE_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.
- Set context using Teradata ODBC driver.
con <- DBI::dbConnect(odbc::odbc(), dsn = "TeradataDSN")
td_set_context(con)
- 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.
- 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")
- 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