SELECT | Teradata Connector | QueryGrid - SELECT Syntax and the Teradata Connector - Teradata QueryGrid

QueryGrid™ Installation and User Guide - 3.06

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Teradata QueryGrid
Release Number
3.06
Published
December 2024
ft:locale
en-US
ft:lastEdition
2024-12-07
dita:mapPath
ndp1726122159943.ditamap
dita:ditavalPath
ft:empty
dita:id
lxg1591800469257
lifecycle
latest
Product Category
Analytical Ecosystem

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
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 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.

The RETURNS clause is effective when the importing and return types belong to the following group:
  • CHAR types: Char, Varchar, Clob, Array of Char/Varchar
  • Byte types: Byte, Varbyte, Blob
RETURNS clause considerations:
  • 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.
Examples:
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