Foreign Table - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.4
Published
July 2017
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
fcf1492702067123.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

The Foreign Table element can be used to create a derived table that uses the FOREIGN TABLE syntax for executing a pass-through query on a foreign server (this might be useful in limiting the amount of data returned, perhaps through the use of a WHERE clause). The following example of a derived table query depends on whether you are connected to a Teradata or Aster database.

Teradata Database:

SELECT cust_id, income, age FROM FOREIGN TABLE
(SELECT cust_id, income, age FROM twm_customer)@hadoop1 T1

Aster Database:

SELECT * FROM FOREIGN SERVER
($$ SELECT cust_id, income, age FROM twm_customer WHERE cust_id < 1362490 $$)@hadoop1

When a Foreign Table element is dragged or otherwise selected onto the Tables tab, the following tree elements are created.

Variable Creation > Input > Variables: SQL Elements pane - Other > Foreign Table

Text Literal SQL elements can be used to specify the Foreign Host and Foreign Query, as shown below.

Variable Creation > Input > Variables: SQL Elements pane - Other > Foreign Table (Specified)

Looking back at the target derived table query, the text fields correspond to the phrase “@hadoop1” (hadoop1 is the foreign server name) and the SELECT statement in parentheses.

The outer SELECT statement is built by switching to the Variables tab, setting the Input Source to Function Table and selecting the appropriate Function Table. After some time, the columns returned by the pass-through query in parentheses should be visible and may be selected as desired. Note that SELECT * is not an available option for the outer query; the columns must be selected individually.

Variable Creation > Input > Variables: SQL Elements pane - Other > Foreign Table (Columns/Values)

The following example of a resultant query depends on whether you are connected to a Teradata or Aster database.

Teradata Database:

SELECT
	 "_twmVC0"."cust_id" AS "cust_id"
	,"_twmVC0"."income" AS "income"
	,"_twmVC0"."age" AS "age"
FROM
FOREIGN TABLE(SELECT cust_id, income, age FROM twm_customer) @hadoop1
 AS "_twmVC0"
;

Aster Database:

SELECT
	 "_twmVC0"."cust_id" AS "cust_id"
	,"_twmVC0"."income" AS "income"
	,"_twmVC0"."age" AS "age"
FROM
FOREIGN SERVER($$ SELECT cust_id, income, age FROM twm_customer $$) @hadoop1
 AS "_twmVC0"
;