Reading Data from RDBMS Sources

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Reading Data into s-Server >

Reading Data from RDBMS Sources

Previous pageReturn to chapter overviewNext page

You can read data from RDBMS sources using the SQL/MED plugin for s-Server. This plugin is pre-installed with s-Server.

In order to read data from an RDBMS system, you need a JDBC driver. Currently, these are preinstalled for Microsoft SQL Server, Oracle, PostgreSQL, and Teradata, and can be installed for MySQL.

Note:  In order to use other JDBC drivers with SQLstream s-Server, you need to 1) copy all required jar files to the $SQLSTREAM_HOME/corelib directory, 2) adjust their permissions so that they are readable by the user who runs s-Server (typically the sqlstream user), and 3) restart s-Server.

To read data, you first define a server object with connection information, including the URL, user name, and password. Once you define this server object, you can read from the database's tables by either:

Defining a foreign table to map onto a table in the foreign database (the preferred method)

Using the SQL-standard three-level qualifier scheme for the names of tables: catalog.schema.table. In this case, the catalog name will be the name given to the foreign server in your CREATE SERVER command. See the topic CREATE SERVER in the s-Server Streaming SQL Reference Guide for more details.

The CREATE SERVER topic in the Streaming SQL Reference Guide has a complete list of options for creating SQL/MED server objects.

You can also configure SQL/MED options with a properties file. See Configuring MED/JDBC Objects With a Properties File below.

Foreign Server Definition

You define a foreign server in SQL using the CREATE OR REPLACE SERVER command. (You can also define servers through s-Studio.) This foreign server always uses a foreign data wrapper called SYS_JDBC. This wrapper is preinstalled with s-Server.

For example, the following code block defines a connection for a MySQL database.




       DRIVER_CLASS 'com.mysql.jdbc.Driver',

       URL 'jdbc:mysql://localhost:3306/sample',

       USER_NAME 'sqlstream',

       PASSWORD 'sqlstream'




Once defined, you can reference this table as mysql_reader.schema.table, where "schema" is a schema defined in the MySQL database and "table" is a table defined in this schema.

The following code block defines a connection for a Teradata database:




       URL 'jdbc:teradata://localhost/',

       USER_NAME 'sqlstream',

       PASSWORD '',

       DIALECT 'Teradata',

       TERADATA_QUERY_BAND 'org=Finance;report=EndOfYear;universe=west;',

       DRIVER_CLASS 'com.teradata.jdbc.TeraDriver'

        "JNDI_WRITEBACK" 'true',




Note the presence of a TERADATA_QUERY_BAND option. This option is specific to Teradata and defined in the CREATE SERVER topic of the Streaming SQL Reference Guide.

You reference tables in the Teradata database defined above using the following qualifier: Teradata_DB.schema.table, where "schema" is a schema defined in the Teradata database and "table" is a table defined in this schema.

Defining a Foreign Table

When you define a foreign table, you

1.Indicate a table and schema in the foreign database.

2.Declare columns that match those in the target database table.

You can set these options when you declare a server, and override these options in a table definition.




Name of the remote table.


Name of the remote schema.

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




   SERVER "Postgres_DB"


       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';

Querying RDBMS Tables

You can query JDBC foreign tables in one of two ways:

By defining foreign tables within s-Server that map onto tables in the foreign database, such as "my_s-ServerSchema"."foreign_customers". This has the advantage of hiding connection details. You can also restrict privileges to this table. This is the recommended technique for accessing foreign tables.

By using the three name qualifier scheme, such as "Oracle_DB"."<tableschamename>"."customers". Merge currently does not function for PostgreSQL and MySQL.

Using a Foreign Table

See the topic CREATE FOREIGN TABLE in the Streaming SQL Reference Guide for more details. Once you create a foreign table, you can select from it like any other table in s-Server, with the contents of the foreign table automatically fetched.


Using the Three Name Qualifier Schema

To read from a foreign table use the schema/table as you would for any other table and preface the server name as the catalog.


JDBC Foreign Data Wrapper Definition

Because this plugin is included as part of the standard s-Server distribution, a corresponding foreign data wrapper instance named SYS_JDBC is predefined by SQLstream s-Server's initialization scripts. Normally, there is no need to define additional instances.

However, it may be useful to define additional wrapper instances corresponding to specific DBMS types. For the JDBC plugin, all SQL/MED options declared on the wrapper are propagated to the server, allowing common option settings to be factored out of individual server definitions. (Note that this is not necessarily true for other plugins.) Options specifically set on a server definition always take precedence over settings inherited from a wrapper.

Configuring MED/JDBC Objects With a Properties File

When a MED/JDBC object (a server, foreign table or foreign stream) is created, it supports reading some properties from a properties file.

For example, you might create a file called $SQLSTREAM_HOME/plugin/jndi/<FullyQualifiedMedJdbcObjectName>.properties and save some default options to that file. These options are inherited by the MedJDBC object if those are not overriden by explicitly specifying those options in the OPTIONS clause of the DDL statement. This feature is useful to save connection credentials to RDBMSes so that the application developer does not need to know/type those connection credentials in the DDL.

For the CREATE SERVER definition above, a sysadmin could save connection credentials to file under $SQLSTREAM_HOME/plugin/jndi folder as follows:







Once the sysadmin saves these credentials to the properties file under the jndi folder, the application developer may simply create the server as:




   "SCHEMA_NAME" 'public',

   "pollingInterval" '1000',

   "txInterval" '1000'



The CREATE SERVER statement above simply inherits some options from the .properties file. Any option specified in the OPTIONS clause overrides the option inherited from the file.

Using the JNDI Writeback Option

You can specify a JNDI_WRITEBACK option in CREATE [ SERVER | FOREIGN TABLE | FOREIGN STREAM statements. The default value for this option is 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.