To get started, the user or DBA must define a Foreign Server object that identifies, among other things, the host type to connect to and the import and export stored procedures to use in receiving data from or sending data to the remote host. Using a special syntax that adds an ‘@’ symbol followed by a Foreign Server name to a database name, the user may refer in selectors to a table on a remote system.
The name of a foreign database using the @server syntax can be manually typed into the databases pull-down selector, or added to the source databases on the databases tab of the Connection Properties dialog, and then selected using the pull-down selector.
When columns from such tables are selected for certain types of analysis, the tables that contain them appear in generated SQL with the trailing ‘@’ symbol and server name. For example, SELECT * FROM twm_customer@hadoop1. The types of analysis that support this special syntax include Descriptive Statistics (except Correlation Matrix and Scatter Plot), ADS, Reorganization and Statistical Tests. The syntax is not supported in analytic algorithms, scoring analyses, matrix analyses and graphical drill-down, although these types of analysis may be performed through the use of a view that selects from a foreign table.
The Teradata-to-Hadoop QueryGrid feature allows inclusion of a RETURNS clause following the name of a foreign table that includes the @server syntax. The query with the RETURNS clause may only be specified however using a SQL text element representing a derived table (not a table function or operator) on the Tables tab in a Variable Creation analysis. Once specified on the Tables tab, the column selector on the left side of the screen can be utilized after setting the input source to Function Table and selecting the appropriate Function Table.
A query with a RETURNS clause might look like the following:
SELECT make, model, price FROM tdsqlh_test@hadoop1 RETURNS (make VARCHAR(2), model VARCHAR(50))
Support is also provided for the FOREIGN TABLE syntax via a SQL element in the Other category called ForeignTable. The user may drop this element on the Tables tab, set the values for the arguments (i.e., foreign host and query), and select columns from the foreign table by setting the input source to Function Tables and performing the steps described earlier. The purpose of the FOREIGN TABLE syntax is to provide a way to execute a query on the foreign host and limit the amount of data transferred over the network by including an appropriate WHERE clause.
A query defined with the FOREIGN TABLE clause might look like the following:
SELECT cust_id, income, age FROM FOREIGN TABLE (SELECT cust_id, income, age FROM twm_customer)@hadoop1 T1
Note that tables on foreign servers may not be specified as output tables (for example, on the Output tab), and foreign databases may not be specified as containing metadata tables on the databases tab of the Connection Properties dialog. Further, foreign tables do not offer right-click options to SHOW TABLE or SHOW TYPE in the list of available columns as part of input selectors, although a SHOW SERVER option is provided. Although inserting into a foreign table is allowed in some cases, INSERT statements may only be used in Free Form SQL analyses and post-processing fields.
In addition to supporting the @server syntax, the Variable Creation analysis supports the direct invocation of the import and export table operators associated with QueryGrid where allowed (the Teradata-to-Teradata connector does not allow this, for example). It also provides access to various stored procedures associated with the QueryGrid feature as Run Units. These initially include HCTAS, which creates a table on a Hadoop system, HDROP, which drops a table on a Hadoop system, and ExecuteForeignSQL, which executes SQL on a foreign Teradata system using the Teradata-to-Teradata connector, or on a foreign Aster system using the Teradata-to-Aster connector. Also, a table operator called AsterExecute can be used to export data from a Teradata system to an Aster server, execute a remote function and return the results. AsterExecute can be found in the Table Operators category of SQL Elements, in the Foreign Server sub-category.