To get started, the user or DBA must define a Foreign Server object that identifies, among other things, the import and export SQL-Map Reduce functions 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 analyses that support this special syntax include ADS and Reorganization. The syntax is not supported in graphical drill-down, although drill-down and the Scatter Plot analysis may be performed with a foreign host through the use of a view that selects from a foreign table.
Support is provided for the FOREIGN SERVER syntax or push-down query via a SQL element in the Other category called Foreign Table. The user may drop this element on the (Function) Tables tab, set the values for the arguments as Text elements (i.e., Foreign Host and Foreign Query) and select columns from the foreign table by setting the input source to Function Tables and then selecting the appropriate Function Table and columns. The purpose of the FOREIGN SERVER 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 SERVER clause might look like the following:
SELECT * FROM FOREIGN SERVER ($$ SELECT cust_id, income, age FROM twm_customer WHERE cust_id < 1362490 $$)@hadoop1
In addition to supporting the @server syntax, the Variable Creation analysis supports the direct invocation of the import and export SQL-Map Reduce functions associated with QueryGrid, namely load_from_teradata, load_to_teradata, load_from_hcatalog and load_to_hcatalog.