Search code examples
rodbcteradatateradata-sql-assistantr-dbi

Connecting to Teradata with R through DBI and ODBC

I connect to Teradata using the Teradata SQL Assistant. The connection parameters consist of a server address and a driver (server info changed for privacy reasons), as shown below:

Name: my_teradata_connection
Teradata Server Info: 00.11.22.333
Data Source: Teradata Database ODBC Driver 16.20
UID: My_User_ID
PWD: My_PWD

I am trying to use R to connect to Teradata, using the DBI and odbc packages. 

con <- DBI::dbConnect(odbc::odbc(),
                      Driver = "[your driver's name]",
                      Host   = "[your server's path]",
                      DBCName = "[IP Address or Alias]"
                      UID    = rstudioapi::askForPassword("Database user"),
                      PWD    = rstudioapi::askForPassword("Database password"))

It seems obvious the Driver should be Teradata Database ODBC Driver 16.20. But where do I put the Teradata Server Info that we'll say is 00.11.22.333? Should it populate the Host or the DBCName arguments? And whichever one it does not populate, what will go there?

Solution

  • Usually, in most DB-APIs including ODBC connections server and host are synonymous keywords where you will not see both together but only one (of course with exceptions). Specifically, per odbc documentation, dbConnect maintains the optional server argument:

    dbConnect(
     drv,
     dsn = NULL,
     ...,
     timezone = "UTC",
     timezone_out = "UTC",
     encoding = "",
     bigint = c("integer64", "integer", "numeric", "character"),
     timeout = 10,
     driver = NULL,
     server = NULL,
     database = NULL,
     uid = NULL,
     pwd = NULL,
     dbms.name = NULL,
     .connection_string = NULL
)

    However, the ... indicates additional ODBC driver keywords that would be specific to corresponding driver, here being Terdata ODBC driver.

    ...         Additional ODBC keywords, these will be joined with the other arguments to form the final connection string

    And from ODBC Driver for Teradata 16.20 documentation, Driver and DBCName are required keywords.DBCName appears to be synonymous to server or host given the IP address or alias indication.

    DBCName = <IP-addr-or-alias>

    | Keyword/Synonym                                             | Description                                                                             |
|-------------------------------------------------------------|-----------------------------------------------------------------------------------------|
| Driver=<driver-path>                                        | [Required] The full path to the ODBC Driver for Teradata shared objects…                |
| Description=<data-source-desc>                              | [Optional] Descriptive text about the data source.                                      |
| DBCName=<IP-addr-or-alias>                                  | [Required] The IP address or FQDN (fully qualified domain name) of the Teradata server… |
| Username=<name> or UID=<name>                               | [Optional] The default username for logging onto a Teradata server system.              |
| Password=<password>                                         | [Optional] The password required for the default Username.                              |
| DefaultDatabase=<database-name> Or Database=<database-name> | [Optional] The default database associated with the specified data source…              |
| UPTMode                                                     | Default = NOTSET…                                                                       |

    Therefore in R, use the DBCName only. Below adds in the optional Database keyword.

    # KEYWORD APPROACH
con <- DBI::dbConnect(odbc::odbc(),
                      Driver   = "Teradata Database ODBC Driver 16.20",
                      DBCName  = "00.11.22.333",
                      Database = "myDatabase",
                      UID      = rstudioapi::askForPassword("Database user"),
                      PWD      = rstudioapi::askForPassword("Database password"))

# CONNECTION STRING APPROACH
con_str = "Driver={Teradata Database ODBC Driver 16.20};DBCName=00.11.22.333;Database=myDatabase;"

con <- DBI::dbConnect(odbc::odbc(),
                      .connection_string = con_str,
                      UID = rstudioapi::askForPassword("Database user"),
                      PWD = rstudioapi::askForPassword("Database password")