Teradata® QueryGrid™: Presto-To-Teradata Database (henceforth known as “P2T”) provides a SQL interface to query the Teradata Database from Presto. 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 Presto with P2T that do the following:
P2T additionally performs push-down of simple predicates and selects only the columns needed for the SQL statement, in order to reduce the amount of data that needs to be retrieved. For a list of supported pushdown, see Predicate Pushdown.
P2T 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 P2T.
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.
To install P2T, you must install a driver on Teradata, a P2T foreign server on Teradata, and a connector on Presto.
Before starting installation, upload presto-to-teradata-driver.zip to both the Teradata and Presto clusters and unzip it:
unzip presto-to-teradata-driver.zip -d presto-to-teradata-driver
cd presto-to-teradata-driver
P2T 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 used in the query.
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 P2T foreign server you create, rather than granting permission for all of the foreign server objects.
P2T foreign servers should not be queried directly by Teradata users, so it is recommended to limit access to foreign servers to presto_user only.
P2T 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.
For more information about available JVM options and restrictions for -Xms and -Xmx values, please refer to the Java Documentation.
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_p2t.bteq. Replace ${DBA_USER} with the DBA user, ${PRESTO_USER} with the user that will query from the foreign server, and ${PATH_TO_P2T_DRIVER_JAR} with the local path to the presto-to-teradata-driver.jar file:
DATABASE syslib;
-- Execute following commands as an admin user (${DBA_USER})
-- Upload driver...
call sqlj.install_jar('CJ!${PATH_TO_P2T_DRIVER_JAR}', 'presto_teradata_driver_p2t', 0);
-- Create transport functions...
CREATE FUNCTION presto_to_teradata_import()
RETURNS TABLE VARYING USING FUNCTION presto_to_td_import_contract
LANGUAGE JAVA NO SQL
PARAMETER STYLE SQLTABLE
READS EXTERNAL DATA
EXTERNAL NAME 'presto_teradata_driver_p2t:com.teradata.ptl.P2TImportDriver.execute';
GRANT EXECUTE FUNCTION ON syslib.presto_to_teradata_import TO ${PRESTO_USER};
GRANT SELECT ON syslib.presto_to_teradata_import TO ${PRESTO_USER};
CREATE FUNCTION presto_to_teradata_export()
RETURNS TABLE VARYING USING FUNCTION presto_to_td_export_contract
LANGUAGE JAVA NO SQL
PARAMETER STYLE SQLTABLE
MODIFIES EXTERNAL DATA
EXTERNAL NAME 'presto_teradata_driver_p2t:com.teradata.ptl.P2TExportDriver.execute';
GRANT EXECUTE FUNCTION ON syslib.presto_to_teradata_export TO ${PRESTO_USER};
GRANT SELECT ON syslib.presto_to_teradata_export TO ${PRESTO_USER};
After installing the driver, create one or more foreign servers as dba_user. The Presto connector(s) connect to the foreign server(s) on Teradata.
The CREATE FOREIGN SERVER command has the following syntax:
CREATE FOREIGN SERVER ${FOREIGN_SERVER_NAME}
USING
PARAM1('value1')
PARAM2('value2')
...
PARAMN('valuen')
DO IMPORT WITH syslib.presto_to_teradata_import,
DO EXPORT WITH syslib.presto_to_teradata_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 P2T performance given your expected workload – see the Configuration section.
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. TRANSPORT_COORDINATOR_PORT(‘21313’).
When connecting to Presto located on host 192.0.2.3 with the default TRANSPORT_COORDINATOR_PORT, a minimal foreign server definition is:
CREATE FOREIGN SERVER p2t
USING
HOSTTYPE('NVP')
TRANSPORT_COORDINATOR_IP('192.0.2.3')
DO IMPORT WITH syslib.presto_to_teradata_import,
DO EXPORT WITH syslib.presto_to_teradata_export;
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 presto_to_teradata_import;
DROP FUNCTION presto_to_teradata_export;
CALL sqlj.remove_jar('presto_teradata_driver_p2t',0);
In the installer package there is a script called uninstall_p2t.bteq that contains these commands.
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
All users of Presto have access to the P2T connector. The connector respects the privileges in Teradata for the tables that it reads from and writes to. If the teradata.connection.user property is set in Presto to presto_user, SHOW SCHEMAS, SHOW TABLES, and DESCRIBE <TABLE> will show schemas, tables, and columns for which presto_user has INSERT and/or SELECT privileges. To issue an INSERT INTO or SELECT query on a table, the user needs INSERT or SELECT privileges respectively. On Teradata, the user needs SELECT privileges for the dbc system tables.
To install P2T on Presto:
Install the presto-to-teradata-connector RPM:
sudo ./presto-admin package install presto-to-teradata-connector-*.noarch.rpm
Copy properties/teradata.properties from the package zip file to /etc/opt/prestoadmin/connectors. Your /etc/opt/prestoadmin/connectors/teradata.properties file will be as follows:
connector.name=teradata
data-exchange.server.port=21338
data-exchange.coordinator.host-and-port=<PRESTO-HOST>:21338
teradata.connection.url=jdbc:teradata://<TERADATA-HOST>
teradata.foreign-server-name=p2t
teradata.connection.user=<USERNAME_ON_TERADATA>
teradata.connection.password=<PASSWORD_FOR_ABOVE_USER>
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.
Replace <TERADATA-HOST> with the hostname or IP of Teradata reachable by Presto.
If the foreign server created above in Foreign Server Creation is not named p2t, replace p2t with that foreign server name in teradata.foreign-server-name.
Replace <USERNAME_ON_TERADATA> and <PASSWORD_FOR_ABOVE_USER> with a user that has SELECT and INSERT privileges for the foreign server.
Additional configuration properties may be found in the Configuration section.
Deploy the P2T connector configuration using presto-admin:
sudo ./presto-admin connector add teradata
Restart Presto:
sudo ./presto-admin server restart
Use the Presto CLI to ensure that the P2T connector is installed:
./presto --server localhost:8080 --catalog system --schema default presto:default> show catalogs; Catalog ----------- ... teradata ...
Note
If running the CLI on any node but the Presto coordinator, substitute the Presto coordinator hostname/IP for “localhost” in the above command.
It is possible to add multiple Teradata connectors with different properties by having a different name. For example, you can add a connector specified in other_teradata.properties which connects to a different Teradata Database, for which the connector name will be other_teradata. When adding another Teradata connector, it is necessary to use a different port value for data-exchange.server.port.
Additional configuration is necessary in order to run P2T with Kerberos authentication. For a detailed description of how to configure Kerberos, see Kerberos Support.
In Presto, connectors allow access to different data sources. P2T uses a Teradata connector in Presto. The connector is configured via a configuration file placed in Presto’s catalog directory (see Installation on Presto). For example, a connector specified in teradata.properties can be queried via the following syntax:
SELECT * FROM teradata.<schema-name>.<table-name>;
INSERT INTO teradata.<schema-name>.<table-name> SELECT * FROM hive.default.nation;
To show the schemas in Teradata:
SHOW SCHEMAS IN teradata;
To show tables in Teradata:
SHOW TABLES IN teradata.<schema-name>;
To show table metadata for a given Teradata table:
presto:test_presto_user> describe teradata.test_presto_user.nation;
Column | Type | Null | Partition Key | Comment
-------------+---------+------+---------------+---------
n_nationkey | bigint | true | false |
n_name | varchar | true | false |
n_regionkey | bigint | true | false |
n_comment | varchar | true | false |
(4 rows)
For extensive documentation of the syntax available in Presto, see the Presto documentation.
There are several Presto configuration variables that should be modified for QueryGrid to work with better performance. Add the following properties to the /etc/opt/prestoadmin/workers/config.properties and /etc/opt/prestoadmin/coordinator/config.properties files for Presto:
redistribute-writes=false
task.writer-count=X
Where X is an integer around (CORES_COUNT / EXPECTED_CONCURRENT_QUERIES_COUNT). If you expect a lot of variance in the number of concurrent queries, X can be set as high as CORES_COUNT.
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. This buffer is shared between all the queries that are in progress. When set on Presto, this property sets the buffer size when exporting data from Presto to Teradata (e.g. INSERT INTO teradata...). When set on Teradata, it indicates the import buffer size (e.g. SELECT * FROM teradata..). If not specified, the default is 2GB.
- data-exchange.server.port - data exchange server port. If not specified, the default is 21338.
- data-exchange.client.buffer-size - query results read buffer size. This buffer is shared between all the queries that are in progress. When set on Presto, this property sets the buffer size when importing data from Teradata to Presto (e.g. SELECT * FROM teradata..). When set on Teradata, it indicates the export buffer size (e.g. INSERT INTO teradata...). 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 P2T.
The data-exchange.server-port property must be changed if the port 21338 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 P2T connector configuration file (/etc/opt/prestoadmin/connectors/teradata.properties). To apply your changes, distribute the new properties file to all Presto nodes and restart the Presto server::
sudo ./presto-admin connector add teradata
sudo ./presto-admin server restart
For Teradata, the data exchange properties are shared across all P2T foreign servers. In order to change a static property for Teradata, you must set a JVM system property in the following format: -Dp2t.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 using the tpareset command.
If the network bandwidth is the bottleneck, data compression may increase throughput at the cost of increased CPU utilization. Data compression can be enabled for a particular foreign server/Presto connector pair using the teradata.compression property. If it is not specified, the data is not compressed.
On Presto, add the property to teradata.properties, and then restart the server:
teradata.compression=<compression_algorithm>
The two options for compression_algorithm 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.
P2T contains several query timeout mechanisms.
The read timeout (default: 2h) determines the maximum amount of time waiting for query results. It is recommended to increase this value for queries that require complex computation before returning any data.
INSERT INTO teradata.schema.table SELECT count(*) from some_huge_table;
To create an appropriate foreign server with a read timeout of 3 days:
CREATE FOREIGN SERVER p2t_read_timeout
USING
HOSTTYPE('NVP')
TRANSPORT_COORDINATOR_IP('192.0.2.3')
ReadTimeout('3d')
DO IMPORT WITH syslib.presto_to_teradata_import,
DO EXPORT WITH syslib.presto_to_teradata_export;
Optionally, additional name value pairs like TRANSPORT_COORDINATOR_PORT or WriteTimeout may be specified.
Set the read timeout on Presto to 3 days as well, by adding the following properties to teradata.properties, and then restarting the server:
teradata.read_timeout=3d
teradata.foreign-server-name=<presto_server>
Timeouts must have both a number and a unit: for example, 10s, 5m, or 2h.
The write timeout (default: 1h) determines maximum amount of time waiting for consumer to consume the results. It can be updated by adding a P2T name value pair WriteTimeout (see below) and setting the Presto session variable teradata.write_timeout. It is recommended to increase this value for queries for which Presto will have to wait a long time for Teradata to consume the data, e.g. queries that require complex computation after consuming the data or pass a lot of data.
INSERT INTO teradata.schema.table SELECT * FROM some_large_table;
To create an appropriate foreign server with a write timeout of 3 days:
CREATE FOREIGN SERVER p2t_write_timeout
USING
HOSTTYPE('NVP')
TRANSPORT_COORDINATOR_IP('192.0.2.3')
WriteTimeout('3d')
DO IMPORT WITH syslib.presto_to_teradata_import,
DO EXPORT WITH syslib.presto_to_teradata_export;
Optionally, additional name value pairs like TRANSPORT_COORDINATOR_PORT or ReadTimeout may be specified.
Set the write timeout on Presto to 3 days as well, by adding the following properties to teradata.properties, and then restarting the server:
teradata.write_timeout=3d
teradata.foreign-server-name=<presto_server>
Timeouts must have both a number and a unit: for example, 10s, 5m, or 2h.
In order to turn on diagnostics/debug logging on Teradata, add the following property to teradata.properties and restart the Presto server:
teradata.debug-enabled=true
When enabled, the logs are in /tmp/jlog-CURRENT_DATE.txt on the Teradata node.
Warning
There is a large amount of output, so in most cases debugging should not be enabled.
To adjust the log debug levels on Presto, follow the instructions about adjusting the log level in the Presto documentation.
In Presto you can change JVM properties by editing the jvm.config file. In particular, if you face frequent out-of-memory errors on the Presto workers, you should consider adding additional memory to Presto (by increasing -Xmx). jvm.config can be easily distributed across the cluster using the presto-admin utility. Please refer to the presto-admin documentation for more details.
It is possible to connect to Teradata via Kerberos. Before doing so, you need to set up Teradata to authenticate via Kerberos. To do that, see the documentation for Teradata 15.00 or 15.10.
In order to connect to Teradata via Kerberos:
Create a Kerberos principal with a password that you will use to connect to Teradata. All queries from Presto to Teradata will be run via this principal.
Create a Teradata user with the same name as the above Kerberos principal. That user needs to be granted WITH NULL PASSWORD permissions. For example, given a Kerberos principal Test_User:
CREATE USER Test_User AS PERMANENT=<permsize> SPOOL=<spoolsize> TEMPORARY=<tempsize> PASSWORD="<password>";
GRANT LOGON ON ALL TO Test_User WITH NULL PASSWORD;
Grant INSERT access to the P2T foreign server, if the foreign server was created with a different user than the Kerberos principal:
GRANT INSERT ON TD_SERVER_DB.p2t TO <principal>
Ensure that the /etc/krb5.conf file specifies a restricted keyset. The following lines must be included in /etc/krb5.conf on both the Presto cluster and the Teradata cluster:
default_tgs_enctypes = arcfour-hmac des3-cbc-sha1 des-cbc-md5
default_tkt_enctypes = arcfour-hmac des3-cbc-sha1 des-cbc-md5
On Presto, add the following additional configuration properties to teradata.properties, using the principal created above as the user and the Kerberos password as the password, and restart the Presto server:
teradata.authentication.type=KERBEROS
teradata.connection.user=<principal>
teradata.connection.password=<password>
teradata.connection.kerberos.realm=<realm>
Note
Please note that though usernames in the Teradata database are not case sensitive, all Kerberos entities, including principals and realms, are case sensitive. Thus, using the above example, the principal must be Test_User.
Add the following property to the jvm.config for both the coordinator and the workers:
-Djava.security.auth.login.config=/usr/lib/presto/lib/plugin/teradata/TeraJDBC.conf
jvm.config can be easily distributed across the cluster using the presto-admin utility. Please refer to the presto-admin documentation for more details.
The following table refers to type handling/conversion between Teradata and Presto. Type handling can differ between Presto connectors or even between storage types within a connector. Please refer to the documentation for specific Presto connectors for more details.
Type conversions from Teradata to Presto:
| Type in Teradata | Type in Presto | Notes |
|---|---|---|
| BYTEINT | TINYINT | |
| SMALLINT | SMALLINT | |
| INTEGER | INTEGER | |
| BIGINT | BIGINT | |
| REAL | DOUBLE | |
| 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) | |
| DATE | DATE | |
| TIMESTAMP | TIMESTAMP | |
| BYTE | VARBINARY | |
| VARBYTE | VARBINARY | |
| JSON | VARCHAR |
Note
Presto’s TIMESTAMP type is not SQL standard. When given a timestamp literal like TIMESTAMP ‘1988-04-08 00:00:00’, Presto interprets and stores it in the user time zone, rather than without a time zone as is the SQL standard. Teradata follows the SQL standard. Thus, in order to ensure that the TIMESTAMP values are interpreted properly, it is best to run the Presto server in UTC (by either setting the JVM time zone or setting the time zone via the JDBC driver).
Note
For DECIMAL, Presto does not perform any implicit conversions on INSERT INTO, so the values being inserted must match the type of the column exactly, including precision and scale. Constant decimal values have a precision of the number of digits in the number and scale of the number of fractional digits, so they may need a cast. e.g. INSERT INTO decimal_8_2 VALUES (CAST(1.0 AS DECIMAL(8, 2)))
P2T can push all projections and some predicates down to Teradata, to limit data transfer. The supported predicates are: