Creating JDBC Database – Streaming Input Data Source

The JDBC Database -Streaming connector allows subscription to a set of data, upserting existing received values in a JDBC SQL Database, by running micro batched queries.

The database must have the appropriate JDBC driver .jar files and JNDI connections.

Steps:

1.     In the New Data Source page, select Input > JDBC Database – Streaming in the Connector drop-down list.

 

2.     You can either select:

·         JNDI Name

Enter the JNDI resource name to be used, then the User Id and Password.

Check the Show Characters box to display the entered characters.

 

NOTE	The JNDI resource name needs to be on the form: java:/comp/env/jdbc/[resourcename]

 

·         URL

Enter the URL specific to the database’s JDBC driver, the Driver Class Name specific to the driver, and the User Id and Password.

Check the Show Characters box to display the entered characters.

3.     Enter the Timeout or the length of time to wait for the server response. Default is 60.

4.     Enter the Query, which can contain parameters in a similar manner to the database connector.

5.     Select whether the parameters should be automatically enclosed in quotes, by checking the Enclose parameters in quotes box.

6.     Click  to retrieve the schema of the configured subscription.

This populates the Id Column with the set of columns from the schema of type sym and the text array such as Character/Boolean/GUID, etc. The selected Id Column can be used to select a key column to manage data updates and inserts.

NOTE: Every message definition needs a text column to be defined as the Id column. By default, only the latest data will be loaded into memory.

Furthermore, a streaming time series window can be generated by creating a compound key with the Id Column, plus a separately specified Time ID column. This Time ID column can be from the source dataset, or alternatively automatically generated.

If the Time Id column is selected, then a scrolling time window can be specified.

For Automatic Time Id, define the Time Id Column Name.

As new data arrives from the subscription, new time slices will automatically be added, and old ones will be deleted.

If a new Id is received, a new row is added to the in-memory data set representing the JDBC Database - Streaming topic subscription. While if an existing ID is received, an existing row is updated.

7.     The time zone of input parameters and output data is by default unchanged. Changing the time zone is supported through the Show in Timezone list box, based on the assumption that the data is stored in UTC time and outputs are presented in the selected time zone.

8.      Define the Real-time Settings.

9.   Click . The new data source is added in the Data Sources list.