A foreign table is a schema objectthat records the metadata necessary for SQLstream s-Server to be able to access a table (or similar data structure) either in a remote database (using SQL/MED - see Integrating RDBMS Systems), or in some other system (using the ECD Framework). A SQL/MED foreign table can support SELECT, INSERT, UPDATE and MERGE like a local table; ECD-based foreign tables support either read-only (SELECT), or write-only (INSERT) access.
For a SQL-MED foreign table there are these options which may be defined explicitly against the foreign table, or may be inherited from the corresponding SERVER:
|Name||Input / Output||Description|
|DRIVER||Both||Class name of the JDBC Driver for the remote database. Fully-qualified name of the JDBC driver class to load. This must be available on the classpath.|
|USER_NAME||Both||Remote database user.|
|PASSWORD||Both||Remote database password|
|DIALECT||Both||Type of database, generally auto detected. Valid values are: Oracle, POSTGRES, MYSQL, TERADATA, SQL SERVER|
|TERADATA_QUERY_BAND||Input||Optional, and for Teradata connections only. Teradata query bands "tag" the query. For more information, see the Teradata website. This is run before the actual query. The submitted SQL looks like the following: SET QUERY BAND = 'ApplicationName=SQLstream_s-Server;Version=
|queryCol||Tailing Input||Name of the column to use for a highwater mark, such as ROWTIME. No default. See Tailing tables.|
|pollingInterval||Tailing Input||In milliseconds, how often to sleep when now new rows are available. Default is 1000. See Tailing tables.|
|txInterval||Tailing Input||How many rows to wait before reading a row, in order to ensure that no rows are missed as a result of rollbacks or dirty reads. If queryCol is ROWTIME, you should consider setting txInterval to 1000. Larger values add latency to the query, but make the stream more consistent (safer) in the face of crashes and multiple inserters to the remote database's table. Smaller values add less latency, may result in a missed row, in the remote chance of a dirty read. See Tailing tables.|
|TRANSACTION_ROWTIME_LIMIT||Output||The number of milliseconds which may elapse between commits. Defaults to 1000 ms (1 second). See Managing Commits. May also be overridden by an INSERT or MERGE ... WITH OPTIONS clause.|
|TRANSACTION_ROW_LIMIT||Output||The number of rows to batch up before committing. Defaults to 0. See Managing Commits. May also be overridden by an INSERT or MERGE ... WITH OPTIONS clause.|
|JNDI_WRITEBACK||Both||True/False. Defaults to "false". When you specify the JNDI_WRITEBACK option as "true" in the DDL or in the .properties file, s-Server writes back consolidated options (including options inherited from the parent as well as from the properties file) to the same properties file under $SQLSTREAM_HOME/plugin/jndi. This lets you configure the JNDI file using DDL.|
|SCHEMA_NAME||Both||Name of the remote schema.|
|TABLE_NAME||Both||Name of the remote table. Normally this is specified only at FOREIGN TABLE / FOREIGN STREAM level, not against the SERVER.|
|OPTIONS_QUERY||Both||Optional. Lets you query a table to set one or more of the required options at runtime. You could use this, for example, to set suitable connection options using
When you define an RDBMS foreign table, you
You can set these options when you declare a server, and override these options in the table definition (or by using the OPTIONS_QUERY option to set options at run time).
For more information about RDBMS (SQL/MED) foreign tables see Integrating RDBMS Systems; for the options for ECD adapters, see the adapter documentation in Integrating Guavus SQLstream with Other Systems.
Like all tables, foreign tables need to be created within schemas. The following code first creates and sets a schema called "MOCHI", then creates a foreign table called "regional_activity" which uses the server defined above and indicates a schema in the foreign database called "public" and a table in the foreign database called "regional_activity".
We assume that the server "Postgres_DB" defines the options needed to access the remote schema.
NOTE there is no need to define the foreign table columns explicitly - they are established by reading from the remote RDBMS's catalog.
CREATE OR REPLACE SCHEMA "MOCHI" SET SCHEMA 'MOCHI'; CREATE OR REPLACE FOREIGN TABLE "regional_activity" SERVER "Postgres_DB" OPTIONS ( ( SCHEMA_NAME 'public' --schema name in the foreign database , TABLE_NAME 'regional_activity' --table name in the foreign database ) DESCRIPTION 'per-city summary of suspicious activity';
See also IMPORT FOREIGN SCHEMA .