Teradata connector#

The Teradata 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 instances.

Requirements#

To connect to Teradata, you need:

Teradata Database
Network access from the Trino coordinator and workers to Teradata. Port 1025 is the default port

Installation#

The Teradata connector is distributed as a plugin archive through the GitHub Releases of the Teradata Trino repository. Use a published release rather than building from the development branch.

Download the plugin .zip for the release that matches your Trino version.
Unpack it into the plugin directory of every Trino coordinator and worker.
Create a catalog properties file as described in the following section, and restart Trino.

Configuration#

To configure the Teradata connector, create a catalog properties file in etc/catalog named, for example, teradata.properties, to mount the Teradata connector as the teradata catalog. Create the file with the following contents, replacing the connection properties as appropriate for your setup:

connector.name=teradata
connection-url=jdbc:teradata://example.teradata.com/CHARSET=UTF8,TMODE=ANSI,LOGMECH=TD2
connection-user=***
connection-password=***

The connection-url defines the connection information and parameters to pass to the Teradata JDBC driver. The supported parameters for the URL are available in the Teradata JDBC documentation.

For example, the following connection-url configures character encoding, transaction mode, and authentication.

connection-url=jdbc:teradata://example.teradata.com/CHARSET=UTF8,TMODE=ANSI,LOGMECH=TD2

The connection-user and connection-password are typically required and determine the user credentials for the connection, often a service user.

Connection security#

If you have TLS configured with a globally-trusted certificate installed on your data source, you can enable TLS between your cluster and the data source by appending parameters to the JDBC connection string set in the connection-url catalog configuration property.

For example, to specify SSLMODE:

connection-url=jdbc:teradata://example.teradata.com/SSLMODE=REQUIRED

For more information on TLS configuration options, see the Teradata JDBC documentation.

Data source authentication#

The connector can provide credentials for the data source connection in multiple ways:

inline, in the connector configuration file
in a separate properties file
in a key store file
as extra credentials set when connecting to Trino

You can use secrets to avoid storing sensitive values in the catalog properties files.

The following table describes configuration properties for connection credentials:

Property name	Description
`credential-provider.type`	Type of the credential provider. Must be one of `INLINE`, `FILE`, or `KEYSTORE`; defaults to `INLINE`.
`connection-user`	Connection user name.
`connection-password`	Connection password.
`user-credential-name`	Name of the extra credentials property, whose value to use as the user name. See `extraCredentials` in Parameter reference.
`password-credential-name`	Name of the extra credentials property, whose value to use as the password.
`connection-credential-file`	Location of the properties file where credentials are present. It must contain the `connection-user` and `connection-password` properties.
`keystore-file-path`	The location of the Java Keystore file, from which to read credentials.
`keystore-type`	File format of the keystore file, for example `JKS` or `PEM`.
`keystore-password`	Password for the key store.
`keystore-user-credential-name`	Name of the key store entity to use as the user name.
`keystore-user-credential-password`	Password for the user name key store entity.
`keystore-password-credential-name`	Name of the key store entity to use as the password.
`keystore-password-credential-password`	Password for the password key store entity.

Multiple Teradata databases#

You can have as many catalogs as you need, so if you have additional Teradata databases, simply add another properties file to etc/catalog with a different name, making sure it ends in .properties. For example, if you name the property file sales.properties, Trino creates a catalog named sales using the configured connector.

Type mapping#

Because Trino and Teradata each support types that the other does not, this connector modifies some types when reading data. Refer to the following sections for type mapping in when reading data from Teradata to Trino.

Teradata type to Trino type mapping#

The connector maps Teradata types to the corresponding Trino types following this table:

Teradata type to Trino type mapping#
Teradata type	Trino type	Notes
`TINYINT`	`TINYINT`
`SMALLINT`	`SMALLINT`
`INTEGER`	`INTEGER`
`BIGINT`	`BIGINT`
`REAL`	`DOUBLE`
`DOUBLE`	`DOUBLE`
`FLOAT`	`DOUBLE`
`NUMBER(p, s)`	`DECIMAL(p, s)`	`DECIMAL(p, s)` is an alias of `NUMERIC(p, s)`.
`NUMERIC(p, s)`	`DECIMAL(p, s)`	`DECIMAL(p, s)` is an alias of `NUMERIC(p, s)`.
`DECIMAL(p, s)`	`DECIMAL(p, s)`	`DECIMAL(p, s)` is an alias of `NUMERIC(p, s)`.
`CHAR(n)`	`CHAR(n)`
`CHARACTER(n)`	`CHAR(n)`
`VARCHAR(n)`	`VARCHAR(n)`
`BINARY`	`VARBINARY`
`VARBINARY`	`VARBINARY`
`BLOB`	`VARBINARY`
`DATE`	`DATE`
`TIME(n)`	`TIME(n)`
`TIMESTAMP(n)`	`TIMESTAMP(n)`
`TIMESTAMP(n) WITH TIME ZONE`	`TIMESTAMP(n) WITH TIME ZONE`
`TIME(n) WITH TIME ZONE`	`TIME(n) WITH TIME ZONE`
`JSON`	`JSON`

No other types are supported.

Type mapping configuration properties#

The following properties can be used to configure how data types from the connected data source are mapped to Trino data types and how the metadata is cached in Trino.

Property name

Description

Default value

unsupported-type-handling

Configure how unsupported column data types are handled:

IGNORE, column is not accessible.
CONVERT_TO_VARCHAR, column is converted to unbounded VARCHAR.

The respective catalog session property is unsupported_type_handling.

IGNORE

jdbc-types-mapped-to-varchar

Allow forced mapping of comma separated lists of data types to convert to unbounded VARCHAR

Querying Teradata#

The Teradata connector provides a schema for every Teradata database. You can see the available Teradata databases by running SHOW SCHEMAS:

SHOW SCHEMAS FROM teradata;

If you have a Teradata database named sales, you can view the tables in this database by running SHOW TABLES:

SHOW TABLES FROM teradata.sales;

You can see a list of the columns in the orders table in the sales database using either of the following:

DESCRIBE teradata.sales.orders;
SHOW COLUMNS FROM teradata.sales.orders;

Finally, you can access the orders table in the sales database:

SELECT * FROM teradata.sales.orders;

SQL support#

The connector provides read access to data and metadata in the Teradata database. In addition to the globally available and read operation statements, the connector supports the following features:

CREATE SCHEMA
CREATE TABLE
CREATE TABLE AS
DROP TABLE
DROP SCHEMA
Schema and table management
View management, see details for View management
MERGE, see also Non-transactional MERGE

Non-transactional MERGE#

The connector supports adding, updating, and deleting rows using MERGE statements, if the merge.non-transactional-merge.enabled catalog property or the corresponding non_transactional_merge_enabled catalog session property is set to true. Merge is only supported for directly modifying target tables.

In rare cases, exceptions may occur during the merge operation, potentially resulting in a partial update.

View management#

The connector fully supports Trino view management for Teradata views.

View storage#

Trino views are not stored as native Teradata views. Instead, view definitions are stored as rows in a metadata table called trino_views inside a dedicated Teradata database. The default metadata database is trino_metadata, which Trino creates automatically on first use.

You can change the metadata database using the teradata.view-metadata-schema catalog configuration property:

teradata.view-metadata-schema=my_trino_metadata

The Teradata user specified in connection-user must have privileges to create databases and tables in the metadata database. The trino_views table has the following structure:

catalog_name, schema_name, view_name, original_sql,
view_owner, run_as_invoker, columns_data, view_comment

Note

Because view definitions are stored in a Trino-managed table rather than Teradata’s native view catalog, Teradata tools such as BTEQ or Teradata Studio will not show these views as native database objects.

Create a view#

Use CREATE VIEW to create a view. Trino maps CREATE OR REPLACE VIEW to Teradata’s native REPLACE VIEW syntax so that existing views are replaced without error:

CREATE OR REPLACE VIEW example.sales.revenue_by_region AS
SELECT
    region,
    sum(amount) AS total
FROM example.sales.orders
GROUP BY region;

Drop a view#

Use DROP VIEW:

DROP VIEW example.sales.revenue_by_region;

Rename a view#

Use ALTER VIEW to rename a view:

ALTER VIEW example.sales.revenue_by_region RENAME TO example.sales.revenue_summary;

Show the view definition#

Use SHOW CREATE VIEW to display the SQL that defines a view:

SHOW CREATE VIEW example.sales.revenue_summary;

Comment on a view or view column#

Use the COMMENT statement to set or clear comments on views and view columns:

-- Set a comment on the view
COMMENT ON TABLE example.sales.revenue_summary IS 'Aggregated revenue per region';

-- Set a comment on a view column
COMMENT ON COLUMN example.sales.revenue_summary.total IS 'Sum of order amounts';

-- Remove a comment
COMMENT ON TABLE example.sales.revenue_summary IS NULL;

Procedures#

`system.flush_metadata_cache()`#

Flush JDBC metadata caches. For example, the following system call flushes the metadata caches for all schemas in the example catalog

USE example.example_schema;
CALL system.flush_metadata_cache();

`system.execute('query')`#

The execute procedure allows you to execute a query in the underlying data source directly. The query must use supported syntax of the connected data source. Use the procedure to access features which are not available in Trino or to execute queries that return no result set and therefore can not be used with the query or raw_query pass-through table function. Typical use cases are statements that create or alter objects, and require native feature such as constraints, default values, automatic identifier creation, or indexes. Queries can also invoke statements that insert, update, or delete data, and do not return any data as a result.

The query text is not parsed by Trino, only passed through, and therefore only subject to any security or access control of the underlying data source.

The following example sets the current database to the example_schema of the example catalog. Then it calls the procedure in that schema to drop the default value from your_column on your_table table using the standard SQL syntax in the parameter value assigned for query:

USE example.example_schema;
CALL system.execute(query => 'ALTER TABLE your_table ALTER COLUMN your_column DROP DEFAULT');

Verify that the specific database supports this syntax, and adapt as necessary based on the documentation for the specific connected database and database version.

Table functions#

The connector provides specific table functions to access Teradata.

`query(varchar) -> table`#

The query function allows you to query the underlying database directly. It requires syntax native to Teradata, because the full query is pushed down and processed in Teradata. This can be useful for accessing native features which are not available in Trino or for improving query performance in situations where running a query natively may be faster.

The native query passed to the underlying data source is required to return a table as a result set. Only the data source performs validation or security checks for these queries using its own configuration. Trino does not perform these tasks. Only use passthrough queries to read data.

As a simple example, query the example catalog and select an entire table:

SELECT
  *
FROM
  TABLE(
    example.system.query(
      query => 'SELECT
        *
      FROM
        tpch.nation'
    )
  );

As a practical example, you can leverage Teradata-specific syntax such as the QUALIFY clause for window function filtering:

SELECT
  *
FROM
  TABLE(
    example.system.query(
      query => 'SELECT
        name,
        salary,
        RANK() OVER (ORDER BY salary DESC) AS rnk
      FROM
        mydb.employees
      QUALIFY rnk <= 10'
    )
  );

Note

The query engine does not preserve the order of the results of this function. If the passed query contains an ORDER BY clause, the function result may not be ordered as expected.

Performance#

The connector includes a number of performance improvements, detailed in the following sections.

Table statistics#

The Teradata connector can use table and column statistics for cost based optimizations, to improve query processing performance based on the actual data in the data source. The statistics are collected by Teradata and retrieved by the connector. The table and column statistics are based on Teradata’s Data Dictionary views.

You can update statistics in Teradata by running:

COLLECT STATISTICS COLUMN (regionkey), COLUMN (name) ON trino_test_teradatajdbcconnect.nation;

Please refer to Statistics for more information on Table Statistics.

Pushdown#

The connector supports pushdown for a number of operations:

Aggregate pushdown for the following functions:

Cost-based join pushdown#

The connector supports cost-based Join pushdown to make intelligent decisions about whether to push down a join operation to the data source.

When cost-based join pushdown is enabled, the connector only pushes down join operations if the available Table statistics suggest that doing so improves performance. Note that if no table statistics are available, join operation pushdown does not occur to avoid a potential decrease in query performance.

The following table describes catalog configuration properties for join pushdown:

Property name	Description	Default value
`join-pushdown.enabled`	Enable join pushdown. Equivalent catalog session property is `join_pushdown_enabled`.	`true`
`join-pushdown.strategy`	Strategy used to evaluate whether join operations are pushed down. Set to `AUTOMATIC` to enable cost-based join pushdown, or `EAGER` to push down joins whenever possible. Note that `EAGER` can push down joins even when table statistics are unavailable, which may result in degraded query performance. Because of this, `EAGER` is only recommended for testing and troubleshooting purposes.	`AUTOMATIC`

Predicate pushdown support#

Predicates are pushed down for most types, including UUID and temporal types, such as DATE.

The connector does not support pushdown of range predicates, such as >, <, or BETWEEN, on columns with character string types like CHAR or VARCHAR. Equality predicates, such as IN or =, and inequality predicates, such as != on columns with textual types are pushed down. This ensures correctness of results since the remote data source may sort strings differently than Trino.

In the following example, the predicate of the first query is not pushed down since name is a column of type VARCHAR and > is a range predicate. The other queries are pushed down.

-- Not pushed down
SELECT * FROM nation WHERE name > 'CANADA';
-- Pushed down
SELECT * FROM nation WHERE name != 'CANADA';
SELECT * FROM nation WHERE name = 'CANADA';