Writing Data to RBDMS Destinations
To write data, you first define a server object with connection information, including the URL, user name, and password.
In order to write data to 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.
Once you define this server object, you can insert into 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.
Note: Because of the nature of streaming data, you will need to set up a pump in order to move rows continually from an s-Server stream to another stream, file, Kafka topic, RDBMS table or other location. Pumps are INSERT macros that continually pass data from one point in a streaming pipeline to the other. A model for setting up a pump is provided below. See the topic CREATE PUMP in the s-Server Streaming SQL Reference Guide for more details.
Like all plugins, in order to use the SQL/MED plugin for JDBC, you need to set up a foreign server SQL object to handle connection parameters for the foreign DBMS. This foreign server always uses a foreign data wrapper called SYS_JDBC. This wrapper is preinstalled with s-Server.
You can reference tables in the foreign DBMS in one of two ways:
•By defining a foreign table that matches a table in the MySQL database, as described below.
•By using the following qualifier: mysql_reader.schema.table, where "schema" is a schema defined in the MySQL database and "table" is a table defined in this schema.
For example, the following code block defines a connection for a MySQL database.
CREATE OR REPLACE SERVER mysql_reader
FOREIGN DATA WRAPPER SYS_JDBC
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 is explained below.
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';
Inserting into Foreign Tables
You can insert into 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.
Using the Three Name Qualifier Schema
To write to a foreign table use the schema/table as you would for any other table and preface the server name as the catalog.
The following code creates a pump that inserts all columns from a stream called "MyStream" into a table called "my_table" in a schema called "my_schema" in the PostgreSQL database defined above.
CREATE OR REPLACE SCHEMA "RDBMS_schema";
SET SCHEMA "RDBMS_schema";
CREATE OR REPLACE PUMP "writerPump" STOPPED AS
INSERT INTO "PostgresDB".my_schema.my_table
SELECT STREAM * FROM "MyStream";
--where "MyStream" is a currently existing stream
To start writing data, use the following code:
ALTER PUMP 'writerPump' START;
In defining the foreign server or table, you can set parameters that manage how frequently the foreign database table is updated.
The number of milliseconds which may elapse between commits. The default value of 0 means that elapsed time is not used to determine when to commit a batch of updates.
The number of updates to batch up before committing. The default value is 65536/maxSize, where maxSize is the maximum row size of the input stream which is driving the update.
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.