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.
Text Literal SQL elements can be used to specify the Foreign Host and Foreign Query, as shown below.
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.
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" ;