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, and Teradata, and can be installed for MySQL, Paraccel, and PostgreSQL.

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.

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.

CREATE OR REPLACE SERVER mysql_reader

   FOREIGN DATA WRAPPER SYS_JDBC

   OPTIONS (

       DRIVER_CLASS 'com.mysql.jdbc.Driver',

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

       USER_NAME 'sqlstream',

       PASSWORD 'sqlstream'

 DIALECT 'MYSQL',

       );

 

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:

CREATE OR REPLACE SERVER "Teradata_DB"

   FOREIGN DATA WRAPPER "SYS_JDBC"

   OPTIONS (

       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'

   );

 

 

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

Description

TABLE_NAME

Name of the remote table.

SCHEMA_NAME

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

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

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.

select_rdbms_source_for_table

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.

select_rdbms_source_direct

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.