2.10 - SELECT Syntax and the Teradata Connector - Teradata QueryGrid

Teradata® QueryGrid™ Installation and User Guide

prodname
Teradata QueryGrid
vrm_release
2.10
created_date
September 2019
category
Administration
Configuration
Installation
User Guide
featnum
B035-5991-099K

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 Database version being used.

For a list of the supported data types, see Data Type Mapping for Teradata 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
A select from foreign table request can only be initiated by Teradata.

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;
A select from foreign table request with an export clause can only be initiated by Teradata.

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;
A select from foreign table request with an export clause can only be initiated by Teradata.

Example: SELECT with a RETURNS Clause

SELECT supports the use of a RETURNS clause to define expected output. 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. Arrays used in the RETURNS clause bring the column back, based on the Teradata array type. 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.

When bringing the array type from Hive or Presto target connectors, any JSON-based Hadoop array format is implicitly converted to the Teradata VARCHAR array format.
CREATE TYPE SUSUDTLIB.STRARRAY AS VARCHAR(2) CHARACTER SET UNICODE ARRACY [3];
SELECT * FROM datatypes1@fs1 RETURNS (varchar1 STRARRAY)
SELECT * FROM datatypes1@fs1 RETURNS (varchar1 VARCHAR(5) CHARACTER SET LATIN) WHERE bigint1 >10000 AND double1 < 10000;

Example: SELECT Foreign Function

During Foreign Function execution, tables are exported from the initiating system to the target system, the function is run on 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