13.26. Teradata Connector

The Teradata Database connector allows querying and creating tables in an external Teradata database. This can be used to join data between different systems like Teradata and Hive, or between different Teradata Database instances.

Presto Teradata Connector supports Teradata 16.

Note

This connector is bundled in Presto Enterprise and requires a license from Starburst. For more information about Presto Enterprise and Teradata connector or to obtain a free trial, please contact hello@starburstdata.com.

Installation and Configuration

Prerequisites

Any of the below installation and configuration methods require the following artifacts:

  • Teradata’s JDBC driver, may be obtained from Teradata’s downloads.teradata.com
  • Connection details to connect to the Teradata DB, in form of a regular Presto connector config (e.g. teradata.properties for a Presto catalog named teradata). File should contain the following contents, replacing the connection properties as appropriate for your setup:
connector.name=teradata
connection-url=jdbc:teradata://HOST
connection-user=USERNAME
connection-password=PASSWORD

Please refer to Teradata JDBC Driver Documentation for more information about format and parameters of the JDBC URL supported by the Teradata JDBC Driver.

Plugin Configuration

To install the Teradata Database connector manually, use the following directions. You will need an installation of the Starburst Distribution of Presto and an Teradata JDBC Driver files (terajdbc4.jar and tdgssconfig.jar).

  1. Add the Teradata JDBC Driver JAR files to the Presto plugin/teradata directory.
  2. Add Presto Teradata catalog properties file (eg. teradata.properties for a Presto catalog named teradata).
  3. Perform the above steps on every Presto cluster node.
  4. Restart Presto on every node.

Querying Teradata Databases

The Teradata connector provides a schema for each Teradata database’s. Assuming the catalog name is teradata, you can see the available schemas by running SHOW SCHEMAS:

SHOW SCHEMAS FROM teradata;

If you have an Teradata database named web, you can view the tables they own by running SHOW TABLES:

SHOW TABLES FROM teradata.web;

You can see a list of the columns in the clicks table in web’s schema using either of the following:

DESCRIBE teradata.web.clicks;
SHOW COLUMNS FROM teradata.web.clicks;

Finally, you can access the clicks table in web’s schema:

SELECT * FROM teradata.web.clicks;

Your privileges in these schemas are those of the user configured in the connection properties file. If the user does not have access to these tables, you will not be able to access them.

Configuring Concurrency

Presto is reading from Teradata Database using multiple parallel JDBC connections. By default, number of parallel reads is determined automatically based on number of AMPs available in Teradata. The parallel reads be configured by using below properties.

Teradata Concurrency Configuration Properties

Property Name Description Default
teradata.concurrency-type
Determines the parallelism method. Possible values are:
  • AMP_BASED - single JDBC connection per Teradata AMP
  • MANUAL - user configures number of parallel connections
AMP_BASED
teradata.connections-count Number of parallel connections, to be used when concurrency-type=MANUAL is used. Use 1 to disable parallel read. Number of AMP in Teradata Database

User Impersonation

Teradata connector supports user impersonation. Please see User Impersonation for more details.

User impersonation can be turned with:

teradata.impersonation-enabled=true

User impersonation in Teradata connector is based on GRANT CONNECT THROUGH. For more details please visit: docs.teradata.com.

Note

Option WITH TRUST_ONLY in GRANT CONNECT THROUGH is not yet supported in Terdata connector.

Table Statistics

Teradata connector supports table and column statistics. The statistics are collected by Teradata Database and retrieved by the connector. To collect statistics for a table, execute the following statements in Teradata Database. The second statement needs to be repeated for every column you want to collect statistics for.

COLLECT SUMMARY STATISTICS ON table_schema.table_name;
COLLECT STATISTICS COLUMN(column_name) ON table_schema.table_name;

The table and column statistics can be viewed in Presto using SHOW STATS and are used for Cost based optimizations.

Table Statistics Configuration Properties

Property Name Description Default
statistics.enabled Enables table and column statistics. true
statistics.cache-ttl Duration for which table and column statistics are cached. 10m
statistics.cache-missing Cache the fact that table statistics were not available. false

Teradata Database Connector Limitations

Unsupported Statements

The following SQL statements are not yet supported: