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.152-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.
The following ports must be opened on the Teradata and Presto nodes: * The Presto coordinator port (DB_PORT) and TRANSPORT_COORDINATOR_PORT (by default 21337) on the coordinator for all Teradata nodes. * The TRANSPORT_COORDINATOR_PORT (by default 21337) on all the Presto workers for all Teradata nodes. * data-exchange.server.port (by default 21337) on all the Teradata nodes for all Presto workers.
Note: We assume that the Presto coordinator and workers can connect to each other on TRANSPORT_COORDINATOR_PORT (by default 21337).
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:
sudo ./presto-admin package install teradata-to-presto-connector-*.noarch.rpm
Copy properties/querygrid.properties from the package zip file to /etc/opt/prestoadmin/connectors.
In /etc/opt/prestoadmin/connectors/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:
sudo ./presto-admin connector add querygrid
Restart Presto:
sudo ./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.
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 dbc or a user with similar permissions, and it installs the driver in the syslib database.
Grant the following permissions to dba_user:
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.
Grant the following permissions to presto_user:
GRANT INSERT, SELECT ON td_server_db TO presto_user;
Alternatively, you can grant INSERT and SELECT privileges for the particular T2P foreign server you create, rather than granting permission for all of the foreign server objects.
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. Note that you need to have 30GB of memory free on the Teradata machine to do this; if not, you will need to set -Xms and -Xmx to a lower value. 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" > /tmp/jre_config;
cufconfig -f /tmp/jre_config;
Note
cufconfig -f overwrites the existing JVM properties. To list the existing properties, run cufconfig -o.
Then, restart the JVM for the Teradata Database using the restart_jvm.bteq script as dba_user:
.run file restart_jvm.bteq
To install the driver, execute the following commands as dba_user, also available in the installer package in a script called install_t2p.bteq. Replace ${DBA_USER} with the DBA user, ${PRESTO_USER} with the user that will query from the foreign server, and ${PATH_TO_T2P_DRIVER_JAR} with the local path to the teradata-to-presto-driver.jar file:
DATABASE syslib;
-- Execute following commands as an admin user (${DBA_USER})
-- Upload driver...
call sqlj.install_jar('CJ!${PATH_TO_T2P_DRIVER_JAR}', 'presto_teradata_driver_t2p', 0);
-- Create transport functions...
CREATE FUNCTION teradata_to_presto_import()
RETURNS TABLE VARYING USING FUNCTION td_to_presto_import_contract
LANGUAGE JAVA NO SQL
PARAMETER STYLE SQLTABLE
READS EXTERNAL DATA
EXTERNAL NAME 'presto_teradata_driver_t2p:com.teradata.ptl.T2PImportDriver.execute';
GRANT EXECUTE FUNCTION ON syslib.teradata_to_presto_import TO ${PRESTO_USER};
GRANT SELECT ON syslib.teradata_to_presto_import TO ${PRESTO_USER};
CREATE FUNCTION teradata_to_presto_export()
RETURNS TABLE VARYING USING FUNCTION td_to_presto_export_contract
LANGUAGE JAVA NO SQL
PARAMETER STYLE SQLTABLE
MODIFIES EXTERNAL DATA
EXTERNAL NAME 'presto_teradata_driver_t2p:com.teradata.ptl.T2PExportDriver.execute';
GRANT EXECUTE FUNCTION ON syslib.teradata_to_presto_export TO ${PRESTO_USER};
GRANT SELECT ON syslib.teradata_to_presto_export TO ${PRESTO_USER};
Once the driver is installed, create one or more foreign servers as dba_user to be able to query data from Presto.
The CREATE FOREIGN SERVER command has the following syntax:
CREATE FOREIGN SERVER ${FOREIGN_SERVER_NAME}
[EXTERNAL SECURITY INVOKER TRUSTED ${AUTH_OBJECT}]
USING
PARAM1('value1')
PARAM2('value2')
...
PARAMN('valuen')
DO IMPORT WITH syslib.teradata_to_presto_import,
DO EXPORT WITH syslib.teradata_to_presto_export;
${FOREIGN_SERVER_NAME} must be a valid object name in Teradata. PARAM1 to PARAMN are name value pairs (NVPs). Some NVPs are required to create a functional foreign server – see Required Name Value Pairs – while others are necessary for connecting to particular Presto servers – see Optional Name Value Pairs. Other NVPs can be used to tune T2P performance given your expected workload – see Performance Tuning Name Value Pairs.
EXTERNAL SECURITY INVOKER TRUSTED ${AUTH_OBJECT} is used only for Kerberos authentication: for a detailed description of how to configure Kerberos, see Kerberos Support.
See the sections below for more information on the required and optional configuration parameters.
The following required name value pairs (NVPs) must be added to the USING clause. NVPs are specified with the form NVP_NAME(‘value’) – e.g. HOSTTYPE(‘NVP’).
Additional name value pairs (NVPs) may be needed to connect the Teradata and Presto servers. These NVPs can be specified after the required NVPs with the form NVP_NAME(‘value’) – e.g. DB_PORT(‘8081’).
When connecting to Presto on port 8080 of host 192.0.2.3 with the default TRANSPORT_COORDINATOR_PORT, a minimal foreign server definition is:
CREATE FOREIGN SERVER presto_server
USING
HOSTTYPE('NVP')
TemporaryTableCatalog('querygrid')
DB_HOST('192.0.2.3')
DO IMPORT WITH syslib.teradata_to_presto_import,
DO EXPORT WITH syslib.teradata_to_presto_export;
When connecting to Presto on a port other than 8080, also specify DB_PORT.
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, execute the following command as dba_user, or some user with DROP FOREIGN SERVER privileges on the server:
DROP FOREIGN SERVER ${FOREIGN_SERVER_NAME};
The driver can be uninstalled only after dropping all of the foreign servers. To uninstall the driver, execute the following command as dba_user:
DATABASE syslib;
DROP FUNCTION teradata_to_presto_import;
DROP FUNCTION teradata_to_presto_export;
CALL sqlj.remove_jar('presto_teradata_driver_t2p',0);
In the installer package there is a script called uninstall_t2p.bteq that contains these commands.
After uninstalling T2P, run the provided restart_jvm.bteq script as dba_user to restart the JVM in Teradata to clean up resources allocated by the driver:
.run file restart_jvm.bteq
There are additional name value pairs that can be added to the CREATE FOREIGN SERVER definition to tune performance for a given workload. They must be added to the CREATE FOREIGN SERVER definition after the required NVPs with the form NVP_NAME(‘value’) – e.g. PrestoWriterCount(‘2’).
The number of concurrent Presto result writer threads per node. If not specified, the default is 8.
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 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 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 (/etc/opt/prestoadmin/connectors/querygrid.properties). To apply your changes, distribute new properties file to all Presto nodes and restart the Presto server:
sudo ./presto-admin connector add querygrid sudo ./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:
-- Execute following commands as presto_user
CREATE FOREIGN SERVER ${FOREIGN_SERVER_NAME}
EXTERNAL SECURITY INVOKER TRUSTED ${AUTH_OBJECT}
USING
HOSTTYPE('NVP')
TemporaryTableCatalog('querygrid')
DB_HOST('${PRESTO_HOST}')
DB_PORT('${PRESTO_HTTPS_PORT}')
security('kerberos')
DO IMPORT WITH syslib.teradata_to_presto_import,
DO EXPORT WITH syslib.teradata_to_presto_export;
${PRESTO_HTTPS_PORT} should be set to the port specified by the Presto property http-server.https.port. As stated above, ${PRESTO_HOST}, 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 presto_user must have access to that object.
Additional name value pairs as described in Optional Name Value Pairs or Performance Tuning Name Value Pairs may also be added to a server configured with Kerberos.
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.