Teradata® QueryGrid™: Teradata Database-to-Presto (henceforth known as “T2P”) provides a SQL interface to query Presto from the Teradata Database. Presto is a SQL query engine for big data that provides access to many data sources, including Hadoop ecosystem databases like Hive and traditional relational databases like MySQL and PostgreSQL.
You can issue queries from Teradata with T2P that do the following:
T2P is compatible with versions of the Teradata database in the 15.00 and 15.10 releases. The minimum version for the 15.00 release is 15.00.04.02. The minimum version for the 15.10 release is 15.10.01.01. The Teradata database must have Java 8 installed. Additionally, there are recommended changes to the Teradata JVM properties that greatly improve performance, as described here: JVM Configuration. Changing the JVM properties on the Teradata database requires a restart of the JVM in Teradata.
Only Presto version 0.167-t is supported with this version of T2P.
These installation instructions use the presto-admin utility, described here. The administrator needs sudo and ssh access to all of the Presto nodes to install a new connector with presto-admin.
Note: We assume that the Presto coordinator and workers can connect to each other on TRANSPORT_COORDINATOR_PORT (by default 21337).
Each version of T2P works with exactly one version of Presto. See the table below for the mapping:
| Presto version | Teradata-to-Presto QueryGrid version |
|---|---|
| 0.127t | 0.2 |
| 0.141-t | 0.3 |
| 0.148-t | 1.2 |
| 0.152-t | 1.3 |
| 0.157-t | 1.4 |
| 0.167-t | 1.5 |
To install T2P, you must install a connector on Presto and a driver on Teradata. To be able to run queries, you must create one or more T2P foreign servers on Teradata.
Before starting installation, upload teradata-to-presto-driver.zip to both the Presto and Teradata clusters and unzip it:
unzip teradata-to-presto-driver.zip -d teradata-to-presto-driver
cd teradata-to-presto-driver
First, install a connector on Presto. These instructions use the presto-admin tool for easier management of Presto.
In order to install T2P on Presto:
Install the teradata-to-presto-connector RPM:
./presto-admin package install teradata-to-presto-connector-*.noarch.rpm
Copy properties/querygrid.properties from the package zip file to ~/.prestoadmin/catalog.
In ~/.prestoadmin/catalog/querygrid.properties, replace <PRESTO-HOST> with the actual Presto coordinator hostname or IP reachable by Teradata. If bynet is configured, make sure to use the bynet IP or a hostname that resolves to the bynet IP so that network traffic uses the fastest possible connection.
Deploy the T2P connector configuration using presto-admin:
./presto-admin catalog add querygrid
Restart Presto:
./presto-admin server restart
Use the Presto CLI to ensure that the T2P connector is installed:
./presto --server localhost:8080 --catalog system --schema default presto:default> show catalogs; Catalog ----------- ... querygrid ...
Note
If running the CLI on any node but the Presto coordinator, substitute the Presto coordinator hostname/IP for “localhost” in the above command.
All management of T2P on the Teradata side happens via the install-teradata script.
To see a full list of supported options, type the following:
./install-teradata
T2P uses two Teradata accounts: a DBA user and a user that will run queries on the foreign server.
The DBA user (henceforth known as dba_user) is usually sysdba or a user with similar permissions, and it installs the driver in the syslib database.
The following permissions are granted to dba_user during the install task:
GRANT EXECUTE PROCEDURE ON sqlj TO dba_user;
GRANT CREATE EXTERNAL PROCEDURE ON syslib TO dba_user WITH GRANT OPTION;
GRANT CREATE FUNCTION ON syslib TO dba_user;
GRANT CREATE SERVER ON td_server_db TO dba_user;
GRANT DROP SERVER ON td_server_db TO dba_user;
The user that will be running queries (henceforth known as presto_user) can either be an existing user or a new user. That user must have sufficient spool space for the query workload: the spool space must be at least as large as the amount of data being fetched from Presto.
The following permissions are granted to presto_user during the install task:
GRANT CREATE SERVER ON td_server_db TO presto_user;
GRANT DROP SERVER ON td_server_db TO presto_user;
In addition, SELECT and EXECUTE FUNCTION privileges for the import and export functions for the foreign server are granted to presto_user.
T2P requires additional Java Virtual Machine (JVM) options. For best performance, it is recommended to use the Java G1 garbage collector and increase the JVM heap size to 30GB using cufconfig.
Warning
cufconfig -f overwrites the existing JVM properties. To list the existing properties, run cufconfig -o.
Warning
If on a VM or other low-memory environment, you will need to set -Xms and -Xmx to a value no larger than the amount of free memory.
In order to set these JVM options, run the following commands on any Teradata node:
cufconfig -o | grep JVMOptions > old_jvmoptions.txt
echo "JVMOptions: -Xms30g -Xmx30g -XX:+UseG1GC -XX:G1HeapRegionSize=32M -XX:+ExplicitGCInvokesConcurrent -XX:OnOutOfMemoryError=kill -9 %p" > /tmp/jre_config;
cufconfig -f /tmp/jre_config;
Then, restart the JVM for the Teradata Database using the restart_jvm.bteq script as dba_user:
.run file restart_jvm.bteq
You can install the driver with the following command:
./install-teradata install \
--dba_logon dba_login,dba_password \
--user_logon user_login,user_password
Note
All values used in command examples must be replaced with values corresponding to your environment.
Note
It is not possible to call install-teradata twice, and only dba_logon and user_logon will have permission to create foreign servers. However, you may grant permissions on the foreign servers to different users.
Once the driver is installed, create one or more foreign servers to be able to query data from Presto.
To create a foreign server issue the following command:
./install-teradata create_teradata_to_presto_server \
--user_logon user_login,user_password \
--presto_master_ip <presto_ip> \
--server_name presto_server \
[--presto_master_port <presto_port>] \
[--server_catalog <presto_catalog_to_query>] \
[--server_schema default]
The options --presto_master_port, --server_catalog, and --server_schema are optional. By default, --presto_master_port is 8080 and --server_catalog is hive. If you want to query one particular schema, you can specify it via --server_schema, otherwise you will need to specify it explicitly for each query. Be aware that when the schema is already specified in the foreign server, you won’t be able to specify any other schema explicitly.
It is possible to create multiple foreign servers with different configurations, as long as they have different names. For additional configuration options, see Foreign Server Configuration.
While logged into Teradata as presto_user, or some other user that has access to the foreign server you created, you can issues the following commands to access data in Presto:
To see the list of schemas (databases) of a foreign server:
HELP FOREIGN SERVER presto_server;
To see the list of tables in a schema (database), e.g. db1:
HELP FOREIGN DATABASE db1@presto_server;
The Hive connector has a schema named default. When referencing this schema in any SQL statement, it needs to be quoted because default is a keyword:
HELP FOREIGN DATABASE "default"@presto_server;
To see the list of columns in a table from a foreign server:
HELP FOREIGN TABLE db1.region@presto_server;
Note
If the schema name has been provided during foreign server creation with the dbname name value pair, then you must not prefix the table name with the schema name.
To select a table from Presto:
SELECT * from db1.region@presto_server;
To insert into a table in Presto:
INSERT INTO db1.region@presto_server SELECT * from region_on_teradata_table;
To run a pass-through query directly on Presto:
SELECT *
FROM FOREIGN TABLE(
SELECT count(*) as c
FROM nation
)@presto_server AS presto_query
To execute DDL directly on Presto:
SELECT *
FROM FOREIGN TABLE(
CREATE TABLE nation_copy AS SELECT * from nation
)@presto_server AS presto_query
When running a pass-through query, the syntax of the query is not checked at all, but the query is passed directly to Presto.
To remove a foreign server, issue the following command:
./install-teradata drop_server \
--dba_logon dba_login,dba_password \
--server_name presto_server
The driver can be uninstalled only after dropping all of the foreign servers. To uninstall the driver, execute the following command:
./install-teradata uninstall \
--dba_logon dba_login,dba_password
After uninstalling P2T, run the provided restart_jvm.bteq script to restart the JVM in Teradata to clean up resources allocated by the driver as dba_user:
.run file restart_jvm.bteq
Alternatively, you can run uninstall task with the restart parameter:
./install-teradata uninstall \
--dba_logon dba_login,dba_password
--restart
In order to upgrade T2P, you will need to update both the Presto connector and the Teradata driver. Each release of T2P works with exactly one version of Presto, so upgrading Presto necessitates upgrading T2P, and vice versa. T2P works with both Teradata 15.00 and 15.10; there is no need to upgrade Teradata.
In order to upgrade the Presto connector:
Uninstall the querygrid connector on Presto:
./presto-admin package uninstall teradata-to-presto-connector
Upgrade Presto to the version specified in the Compatibility Matrix. See the Presto documentation for the Presto upgrade itself.
./presto-admin package install teradata-to-presto-connector
./presto-admin server restart
The configuration files from the previous version of the querygrid connector will remain. You can edit them via ./presto-admin catalog add.
In order to upgrade the Teradata driver:
Drop all existing foreign servers that use the old version of T2P. In order to see all existing foreign servers, you can issue the following query as dba_user:
select * from dbc.serverv;
Before dropping foreign servers, you may want to save the foreign server text:
SHOW FOREIGN SERVER [foreign-server-name]
Uninstall the old version of T2P, using the instructions in Uninstall Driver. Make sure to use uninstall_t2p.bteq from the old version of the driver.
Restart the JVM for the Teradata Database using the restart_jvm.bteq script as dba_user:
.run file restart_jvm.bteq
Install the new version, using the instructions in Driver Installation. Make sure to use install_t2p.bteq from the new version of the driver.
Re-create the foreign servers. The release notes will mention any changes to the foreign server DDL, otherwise the old foreign server DDL can be used.
The complete command line options for create_teradata_to_presto_server are as follows:
./install-teradata create_teradata_to_presto_server \
--user_logon user_login,user_password \
--server_name presto_server \
--presto_master_ip <presto_ip> \
[--server_catalog <presto_catalog_to_query>] \
[--server_schema default] \
[--presto_master_port <presto_port>] \
[--transport_coordinator_port port number] \
[--compression compression] \
[--presto_user username] \
[--presto_password password] \
[--presto_writer_count writer_count] \
[--server_options server_options]
Note that --compression and -presto_writer_count are performance-related; they can be added to tune performance for a given workload.
See below for a description of all of the options:
The type of compression used for data transfer between Presto and Teradata. If not specified, the data is not compressed.
If the network bandwidth is the bottleneck, data compression may increase throughput at the cost of increased CPU utilization. The possible values for COMPRESSION are LZ4 and SNAPPY. Both algorithms are focused on compression and decompression speed. Compression ratio and compression speed may vary for different queries and data sets.
The number of concurrent Presto result writer threads per node. If not specified, the default is 8. It must be a power of 2.
If your workload mainly consists of running large queries with low concurrency, you will more fully use your cluster if it is set to a higher value. However, if your workload contains concurrent queries, you may need to lower it.
For example, if your Presto cluster has 64 cores per machine, to fully utilize all of those cores with 4 concurrent queries, you need to set PrestoWriterCount to at least 16. However, a PrestoWriterCount that is too high can cause excessive CPU utilization. If Presto starts to fail with timeout related issues, try to decrease PrestoWriterCount.
When increasing PrestoWriterCount, you must also change the node-scheduler settings in Presto’s config.properties file:
node-scheduler.max-splits-per-node=MAX_SPLITS_PER_NODE node-scheduler.max-pending-splits-per-node-per-task=MAX_SPLITS_PER_TASK_PER_NODE
Where MAX_SPLITS_PER_TASK_PER_NODE must be greater than or equal to PrestoWriterCount and MAX_SPLITS_PER_NODE must be greater than or equal to PrestoWriterCount multiplied by the number of queries that are intended to run concurrently. This Presto configuration change requires a Presto restart, which might require a downtime window for the Presto cluster.
The following options are valid in --server_options:
The maximum amount of time spent waiting for query results. It is recommended to increase this value for queries that require complex computation before returning any data. Timeouts must have both a number and a unit: for example, 10s, 5m, or 2h.
SELECT * FROM FOREIGN TABLE( SELECT count(*) FROM some_enormous_table )@foreign_server AS table;
If not specified, the default is 2h.
The maximum amount of time spent waiting for consumer to consume the results. It is recommended to increase this value for queries that require complex computation after consuming the data. Timeouts must have both a number and a unit: for example, 10s, 5m, or 2h.
SELECT fibonacci(bigint_column) FROM large_table@foreign_server;
If not specified, the default is 1h.
The maximum amount of time spent waiting for the remote query to finish after data transfer is finished. It is recommended to increase this value for INSERT INTO queries which insert the data into slow consuming connectors. Timeouts must have both a number and a unit: for example, 10s, 5m, or 2h.
INSERT INTO "slow_consuming_connector"."slow_consuming_table" SELECT * FROM large_table;
If not specified, the default is 1h.
The following properties control the data exchange. They can be set for both Presto and Teradata:
- data-exchange.server.buffer-size - query results write buffer size. The buffer is shared between all the queries that are in progress. If not specified, the default is 2GB.
- data-exchange.server.port - data transfer server port. If not specified, the default is 21337.
- data-exchange.client.buffer-size - query results read buffer size. The buffer is shared between all the queries that are in progress. If not specified, the default is 2GB.
Note
Buffer sizes must be specified in human readable data size notation: 100MB, 1GB, 1TB
The data-exchange.server.buffer-size and data-exchange.client.buffer-size properties may need to be changed if latency in the system causes the buffers to fill up. This will cause increased memory usage by T2P.
The data-exchange.server-port property must be changed if the port 21337 is not free. If the port on Presto is changed, TRANSPORT_COORDINATOR_PORT in the foreign server definition, as described in `Optional Name Value Pairs`_, must also be changed to match.
To change a property for Presto, edit the T2P connector configuration file (~/.prestoadmin/catalog/querygrid.properties). To apply your changes, distribute new properties file to all Presto nodes and restart the Presto server:
./presto-admin catalog add querygrid ./presto-admin server restart
For Teradata, the data exchange properties are shared across all T2P foreign servers. In order to change a static property for Teradata, you must set a JVM system property in the following format: -Dt2p.property.name=value. Properties for the Teradata JVM can be set using the cufconfig utility. To apply your changes, you must restart the Teradata database JVM using the restart_jvm.bteq script as dba_user.
It is possible to create a foreign server that connects to Presto via Kerberos. Before doing so, you must set up the Presto server to authenticate via Kerberos (see the Presto docs: http://teradata.github.io/presto/docs/current/security/server.html). A few notes about how to set up Kerberos on the Presto side:
The Presto coordinator principal (specified by the property http.server.authentication.krb5.service-name) must be HTTP.
The SSL keystore (specified by the property http-server.https.keystore.path) must contain as a common name the value below <presto_master_ip>. <presto_master_ip> can be a hostname, but that hostname must be in the /etc/hosts file of all of the Teradata nodes. When specifying a common name (CN) that is an IP address, it is necessary to also specify the subjectAltName extension. For example:
keytool -genkeypair \
-alias presto \
-keyalg RSA \
-keystore /etc/presto/keystore.jks \
-keypass password \
-storepass password \
-dname "CN=<ip address>, OU=, O=, L=, S=, C=" -ext san=ip:<ip address>
It is possible to add multiple entries to one keystore by running the above command with different aliases, common names, and IPs. You should add all of the IP addresses by which it is possible to connect to Presto from Teradata (including bynet addresses).
To set up Kerberos on the Teradata side:
Install version 1.4.3 or later of the krb5 and krb5-client packages on all of the nodes in the Teradata cluster. The krb5 packages are included on the standard Teradata Database Linux operating system DVD.
Copy the krb5.conf file from your Presto cluster to /etc/krb5.conf on all of the nodes in the Teradata cluster.
Add the hostname for the machine running the KDC to the /etc/hosts file on all of the nodes in the Teradata cluster.
Install the appropriate Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files for your Java 8 JRE. Download them from here: http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html, and follow the README to install.
Create an authorization object. This authorization object will store credentials for the remote Kerberos system to be used during execution of queries by the foreign server. For more information on authorization objects, see the Teradata database documentation. For example:
CREATE AUTHORIZATION remote_user1 AS INVOKER TRUSTED
USER '<kerberos_principal>' PASSWORD '<kerberos_password>';
Copy the SSL certificate keystore specified by the Presto property http-server.https.keystore.path to /etc/opt/teradata/querygrid/keystore.jks on all of the Teradata nodes. Make sure that the file is readable by the user tdatuser; other than that, keep the permissions on this file as restrictive as possible.
Create the foreign server:
./install-teradata create_kerberized_teradata_to_presto_server \
--user_logon <user_login>,<user_password> \
--presto_master_ip <presto_master_ip> \
--presto_https_port <https_port>
--server_name <foreign_server_name> \
--authorization_object <auth_object>
<https_port> should be set to the port specified by the Presto property http-server.https.port. As stated above, <presto_master_ip>, which can be either an IP address or a hostname, needs to be added to the keystore on Presto. <auth_object> is the authorization object created above. The user specified by <user_logon> must have access to the object.
The same optional arguments as in Foreign Server Configuration are permitted.
The following table refers to type handling/conversion between the Presto query engine and Teradata. Type handling can differ between Presto connectors or even between storage types within a connector. Please refer to the Presto documentation for specific connectors for more details.
| Type in Presto | Type in Teradata | Notes |
|---|---|---|
| BOOLEAN | BYTEINT | |
| TINYINT | BYTEINT | |
| SMALLINT | SMALLINT | |
| INTEGER | INTEGER | |
| BIGINT | BIGINT | |
| DOUBLE | REAL | |
| DECIMAL(X,Y) | DECIMAL(X,Y) | Max precision in both Presto and Teradata is 38; scale <= precision |
| CHAR(X) | CHAR(X) | |
| VARCHAR(X) | VARCHAR(X) | Presto VARCHAR length is truncated at MaximumTextLength (by default 4096 characters) |
| VARCHAR | VARCHAR(MaximumTextLength) | |
| DATE | DATE | |
| TIMESTAMP | TIMESTAMP | |
| VARBINARY | VARBYTE | Error thrown on values exceeding MaximumBinaryLength (by default 4096 bytes) |
| JSON | VARCHAR | |
| ARRAY | VARCHAR | Serialized to/from json |
| MAP | VARCHAR | Serialized to/from json |
| ROW | VARCHAR | Serialized to json in import path. Writing ROW values to Presto is not supported |
There is limited support for CHAR. CHAR columns can be exported from Teradata to Presto VARCHAR columns, since Presto does not have the CHAR type. Presto VARCHAR columns can be inserted into Teradata CHAR columns.
Complex Presto types – ARRAY, MAP, and ROW – are translated to JSON and are visible in Teradata as strings. Large, complex objects may not be able to be selected, because their JSON string representation may exceed the Teradata maximum row length of 64KB.
Native Presto queries that have aggregations without explicitly specified aliases fail with the error, “Presto query failed: Column name not specified at position 1”.
SELECT *
FROM FOREIGN TABLE(
SELECT count(*)
FROM nation
)@presto_server AS presto_query
Provide an alias explicitly to make it work:
SELECT *
FROM FOREIGN TABLE(
SELECT count(*) as some_explicit_alias
FROM nation
)@presto_server AS presto_query
Currently, T2P is limited to 20 concurrent queries. However, if your queries are long running or take a lot of resources (e.g. if they have a big result set), it is recommended to run fewer than 20 queries. Otherwise, you may get a Teradata error:
[Error 7583] [SQLState HY000] The secure mode processes had a set up error.
High presto_writer_count can cause high CPU utilization. If the Presto starts to fail with the timeout related issue try to decrease presto_writer_count.
If you get the error, “Another instance of the Querygrid driver is still running.”, please restart your Teradata database JVM with the``restart_jvm.bteq`` script.