You can use the foreign server grammar in the form of database_name.table_name@server_name in the SELECT statements of your queries. If you do not specify a database, the default is the current database for Teradata-to-Teradata and is used as the default or overridden database provided in the connector or link properties for Teradata-to-Presto. You can use this foreign server grammar in joins and any other place that you reference a normal table, including views, macros, and stored procedures.
Logical expressions such as AND, OR, NOT, >, <, and so on, are supported. Standard Teradata limits, such as returning up to 2048 columns, apply. Row size limit depends on the Teradata system version being used.
For a list of the supported data types, see Data Type Mapping for QueryGrid Connectors.
Query processing is pushed down to the target system, based on the capabilities of the target system, and the optimization of the local system. For example, if you query a target Presto system from a Teradata system, the Teradata optimizer decides the pushdown predicates, which are then evaluated for final target query pushdown based on what is supported by the target system.
Example: Teradata to Presto SELECT
The example initiates a request from Teradata to select data from a table on the target Presto system, using the foreign server object (presto1) and the target Presto table (cardata):
SELECT CAST(Price AS DECIMAL (8,2)) , mileage , CAST(make AS VARCHAR(20)) , CAST(model AS VARCHAR(20)) FROM cardata@presto1 WHERE make=’Buick’;
Result:
price mileage make model -------- ------- ------ -------------------- 17314.10 8221 Buick Century 17542.04 9135 Buick Enclave
Example: Teradata to Presto SELECT from Foreign Table
The example initiates a request on Teradata using the foreign table push down query to fetch data from a target Presto system. This is a pass through query and is not parsed on the Teradata end, but regenerating on the target server may be necessary, for example, to qualify a table name with catalog name:
SELECT * FROM FOREIGN TABLE (SELECT make, model FROM default.cardata where make = 'Buick')@QG_presto1 AS dt;
Result:
make model ------ -------------------- Buick Century Buick Enclave
Example: Teradata to Presto SELECT from Foreign Table with an EXPORT Clause
The example shows a foreign table query with an additional export clause that combines the export operation and subsequent join and import operation in a single operator. This example first creates a temporary table (temp) in the target database, exports the data from the local ut2.datatypes2 table to the temporary table, then runs the query on the foreign table to join the temporary table and the target datatypes1.bigint1 table, and then the result set is brought back Teradata.
SELECT * FROM FOREIGN TABLE (SELECT datatypes1.bigint1, temp.double1 FROM datatypes1, temp WHERE datatypes1.bigint1 > 1)@test EXPORT((select * from ut2.datatypes2) as temp) AS ft;
Example: Teradata to Presto SELECT from Foreign Table with an EXPORT Clause (Multiple Input Streams)
Multiple tables can be exported to the target system. A maximum of 16 input streams are supported. An error is displayed when the limit is exceeded.
SELECT * FROM FOREIGN TABLE(SELECT types_numeric.bigint1,temp1.integer1 , temp2.byteint2 FROM types_numeric, temp1,temp2 WHERE types_numeric.bigint1 > 1 AND temp2.bigint2 = types_numeric.bigint1)@td1 EXPORT( (SELECT 127 as byteint1, 32767 as smallint1, 2147483647 as integer1, 9223372036854775807 as bigint1) as temp1, localdb.table1 as temp2) AS ft;
Example: SELECT with a RETURNS Clause
SELECT supports the use of a RETURNS clause to define expected output when using SQL Engine 16.20 or later. The RETURNS clause supports column lists or table definitions and is typically used to return a column with a specific type, character set length, and string format. When fetching an unbounded or bounded string type, a RETURNS clause helps map these types to CHAR, VARCHAR, or CLOB with a specific length or character set. BYTE and VARBYTE can also be mapped to a specific length.
- CHAR types: Char, Varchar, Clob, Array of Char/Varchar
- Byte types: Byte, Varbyte, Blob
- The return data type must be compatible with the importing type.
- Only one-dimensial Array of Char/Varchar is supported.
- If either of the importing or return types are not supported, then the RETURNS clause has no effect.
SELECT c_vchar FROM testtabl@fs1 RETURNS(c_vchar VARCHAR(2)); SELECT * FROM testtab2@fs1 RETURNS(c_vchar2 VARCHAR(5) CHARACTER SET LATIN);
Example: SELECT Foreign Function
Running the Foreign Function allows the functions defined on the target system to use the tables from the initiating system as input. When running the Foreign Function feature, tables are exported from the initiating system to the target system; the remote function on the target system is initiated by the target system; and the result is imported into the initiating system.
SELECT clicktime, custname(char(10)), productname, pagetype, productprice, sessionid FROM SESSIONIZE@remoteTD ( ON sess_data PARTITION BY partition_id ORDER BY clicktime USING TIMECOLUMN('clicktime') TIMEOUT(60) RAPIDFIRE(0.2) ) AS S, cust_data where S.userid = custid ORDER BY S.partition_id, S.clicktime; *** Query completed. 19 rows found. 6 columns returned. *** Total elapsed time was 3 seconds. CLICKTIME custname PRODUCTNAME PAGETYPE PRODUCTPRICE SESSIONID -------------------------- -------- ------------ --------- ------------ --------- 2013-12-03 22:03:02.540000 Sri ? Home ? 0 2013-12-03 22:03:32.190000 Sri mytablet checkout 45000.00 0 2013-12-03 22:03:46.210000 Sri myphone 3 checkout 46000.00 0 2013-12-03 22:04:13.820000 Sri B LED TV checkout 86000.00 0 2013-12-03 22:04:25.520000 Sri Z LED TV checkout 91000.00 0 2013-12-18 22:08:07.800000 Walkar ? Home ? 1 2013-12-18 22:08:38.890000 Walkar A Laptop checkout 58500.00 1 2013-12-19 22:09:26.930000 Walkar ? Home ? 2 2013-12-19 22:10:09.180000 Walkar XYZ Laptop checkout 56500.00 2 2013-12-19 22:10:41.860000 Walkar Solar A4 checkout 36500.00 2 2013-12-19 22:11:00.040000 Walkar ABC Tru checkout 41500.00 2 2013-12-24 22:15:13.600000 Pam ? Home ? 3 2013-12-24 22:15:42.170000 Pam home theater checkout 800.00 3 2013-12-24 22:16:03.520000 Pam TV 52inch checkout 700.00 3 2013-12-25 22:12:20.890000 Julia ? Home ? 4 2013-12-25 22:12:52.820000 Julia ABC Tru checkout 41000.00 4 2013-12-25 22:13:11.720000 Julia myphone 3 checkout 47000.00 4 2013-12-26 22:13:54.150000 Doug ? Home ? 5 2013-12-26 22:14:13.000000 Doug myphone 3 checkout 250.00 5