Writing Data to RBDMS Destinations

<< Click to Display Table of Contents >>

Navigation:  Integrating Blaze with Other Systems > Writing Data Out of s-Server >

Writing Data to RBDMS Destinations

Previous pageReturn to chapter overviewNext page

To write data, you first define a server object with connection information, including the URL, user name, and password.

In order to write datat 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.

JDBC Foreign Server Definition

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

  OPTIONS (

      DRIVER_CLASS 'com.mysql.jdbc.Driver',

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

      USER_NAME 'sqlstream',

      PASSWORD 'sqlstream'

 DIALECT 'MYSQL',

      );

 

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

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

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.

insert_rdbms_source_for_table

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.

insert_rdbms_source_direct

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;

Managing Commits

In defining the foreign server or table, you can set parameters that manage how frequently the foreign database table is updated.

Option

Defnition

TRANSACTION_ROWTIME_LIMIT

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.

TRANSACTION_ROW_LIMIT

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.

 

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.