Using the JDBC Driver for Federation

<< Click to Display Table of Contents >>

Navigation:  Integrating Blaze with Other Systems > SQLstream JDBC Driver >

Using the JDBC Driver for Federation

Previous pageReturn to chapter overviewNext page

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 two 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.

Installing Section

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'

   LANGUAGE JAVA

   OPTIONS (DRIVER_CLASS 'com.sqlstream.jdbc.Driver');

 

For more detail, see the topic CREATE FOREIGN DATA WRAPPER in the Streaming SQL Reference Guide.

Defining Section

In the defining server section, you create a data server like this with 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

OPTIONS(

   URL 'jdbc:vjdbc:sdp://remote-host:5570',

   USER_NAME 'sa',

   PASSWORD '',

   STREAMING 'true',

   SCHEMA_NAME 'remote-schema-name'

);

 

For more detail, see the topic CREATE SERVER in the Streaming SQL Reference Guide.

Calling Section

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 stream or table in the foreign s-Server.

CREATE OR REPLACE SCHEMA "FederationData";

SET SCHEMA '"FederationData"';

 

CREATE OR REPLACE FOREIGN STREAM "my-federation-stream"

("recNo" INTEGER,

"ts" TIMESTAMP NOT NULL,

"accountNumber" INTEGER,

"loginSuccessful" BOOLEAN,

"sourceIP" VARCHAR(32),

"destIP" VARCHAR(32),

"customerId" INTEGER,)

SERVER "my-federation-server"

OPTIONS ("SCHEMA_NAME" 'remote-schema-name',

        'TABLE_NAME" 'remote-stream');

 

Querying Streams

Once you have set up the foreign data wrapper and server above, you query the 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.