Reading Data from RDBMS Sources
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.
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
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"
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.
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"
--schema name in the foreign database
--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.
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.
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 PostgreSQL_DB_1.properties 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:
CREATE OR REPLACE SERVER "PostgreSQL_DB_1"
FOREIGN DATA WRAPPER "SYS_JDBC"
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.