Using the JDBC Driver for Federation
Using the SQLstream JDBC driver, you can federate (link together) multiple instances of s-Server. You do so using similar DDL to that used for SQL/MED connection to other databases. See the topic Reading Data from RDBMS Sources in this guide for more details.
Once you federate multiple instances of s-Server, you can query from and insert into streams and tables in the federated instances of s-Server.
Setting up federation requires three steps:
|1.||Creating a foreign data wrapper for the other instance of s-Server that references the JDBC driver.|
|2.||Creating a server object that references this foreign data wrapper and includes connection information for the server.|
|3.||Creating a foreign stream that references the server object.|
To install the JDBC driver, you create a foreign data wrapper along the following lines:
CREATE OR REPLACE FOREIGN DATA WRAPPER CLIENT_JDBC
LIBRARY 'class com.sqlstream.aspen.namespace.jdbc.AspenMedJdbcForeignDataWrapper'
OPTIONS (DRIVER_CLASS 'com.sqlstream.jdbc.Driver');
For more detail, see the topic CREATE FOREIGN DATA WRAPPER in the Streaming SQL Reference Guide.
In the defining server section, you create a data server using the same options as a MED/JDBC foreign data server. See the subtopic "JDBC Foreign Server Definition" in the topic Writing to RDBMS systems in this guide for details on the options defined below.
CREATE OR REPLACE SERVER "my-federation-server" FOREIGN DATA WRAPPER CLIENT_JDBC
For more detail, see the topic CREATE SERVER in the Streaming SQL Reference Guide.
In order to access data from the federated server, you need to create a special kind of stream called a foreign stream. Options in the stream specify the file format, character separators, whether or not a header should be written, and so on, as well as any options specific to the format type.
Like all streams, foreign streams must be created within a schema. The example below creates and sets a schema called "FederationData," and creates a foreign stream called "my-federation-stream."
In addition to a column list, this foreign stream sets two options:
|•||SCHEMA_NAME, which is the schema name in the foreign s-Server.|
|•||TABLE_NAME, which is the name of the table in the foreign s-Server|
|•||STREAM_NAME, which is the name of the stream in the foreign s-Server.|
CREATE OR REPLACE SCHEMA "FederationData";
SET SCHEMA '"FederationData"';
CREATE OR REPLACE FOREIGN STREAM "my-federation-stream"
"ts" TIMESTAMP NOT NULL,
OPTIONS ("SCHEMA_NAME" 'remote-schema-name',
Once you have set up the foreign data wrapper and server above, you query streams and tables in the 'remote-schema-name' schema in the server on remote-host as if they were on the local server, using the SCHEMA_NAME as a qualifier, as in the following code:
SELECT STREAM * FROM "FederationData"."my-federation-stream";
The data will be pulled from "remote-schema-name"."remote-stream" on the remote s-Server by the foreign data wrapper.
Note: You cannot insert into a stream on the remote s-Server. You also cannot create or drop streams and tables in the remote schema.