CREATE FOREIGN TABLE

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.

Syntax

Foreign table options for SQL-MED

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.
URI Both JDBC URI
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=;' FOR SESSION
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 select URI, "SCHEMA", USERNAME, PASSWORD from TEST.rdbms_options. For more details see the topic Using the Options Query Property. This is usually applied to the FOREIGN TABLE rather than the SERVER



Defining a Foreign Table

When you define an RDBMS foreign table, you

  • Optionally declare columns that match those in the target database table.
  • Indicate the (foreign) SERVER with which this foreign table is associated
  • Indicate a table and schema in the foreign database by setting options for the foreign table.
  • Define any other options that may be needed, or inherit them from the SERVER.

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.

Sample SQL

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 .