Streaming Analytics can now communicate with additional Bluemix database services such as SQL Database, dashDB, and many others. The new Streams JDBC Toolkit allows Streams apps to read from and write to any database that can be accessed using JDBC. This post shows how to integrate with SQL Database and dashDB using the streamsx.jdbc toolkit.
- Download and extract the JDBC toolkit from GitHub
- Install the JDBC toolkit to Streams Studio by following the procedure in Adding toolkit locations.
If using SQL Database:
- Create a SQL Database service in Bluemix
- Create a Bluemix Application using the Liberty for Java buildpack – follow the first four steps in Creating Cloud Foundry apps
- Bind your SQL Database service to your Bluemix application
- Find service credentials in the “Environment Variables” tab of your Bluemix application’s dashboard.
If using dashDB:
- Create a dashDB service in Bluemix
- Find service credentials in the “Service Credentials” tab of your dashDB service dashboard.
Configuring the sample Streams application
The JDBC toolkit comes with a sample Streams application called JDBCSample:
JDBCSampleinto Streams Studio from the toolkit’s sample directory
JDBCRunSample.splfile in Streams Studio.
This application creates a table, inserts a record, queries the record, writes the record to the console, and finally drops the table.
In order for Streams to talk to SQL Database, you will need a JDBC driver jar file:
- Download the DB2 driver and extract it
- Create an
optdirectory inside the imported
- Move the extracted
db2jcc4.jarfile to the new
Before running the JDBCSample application, enter the following information into the parameters of each JDBCRun operator:
|jdbcDriverLib||the jdbc driver library (use
|jdbcClassName||the class name for jdbc driver (use
|jdbcUrl||the database URL. (
|jdbcUser||the database user. (
|jdbcPassword||jdbcUser’s password. (
jdbcClassName come from the driver you added, and the values given in the table are correct.
jdbcUrl correspond with the
jdbcurl fields in the service credentials retrieved in the last steps of the Setup section above.
Running the sample Streams application
After entering the values to each JDBCRun operator, run the Streams app and view the result:
printer operator should print the retrieved database record to its PE console:
1,Mike ,Ward ,31,M,33.3,912.3
We have now shown that we can successfully store and retrieve data using a Bluemix database service.
To show the database growing, remove the
drop JDBCRun operator. Now each time you run JDBCSample an extra row will be added to the database and printed by
View the database contents from the Bluemix service dashboard:
- Run JDBCSample a few times after removing the `drop` operator
- Navigate to the SQL Database or dashDB service dashboard
- Click “Launch” to go to the service console
- Click “Work with Tables” for SQL Database or “Tables” for dashDB
- Select the `JDBCRUN_SAMPLE` table and select the “Browse Data” tab.
SQL Database table view
dashDB table view
Deploying the sample application to work with Streaming Analytics
JDBCSample and its use of the JDBCRun operator will work unmodified on the Streaming Analytics Bluemix service.
- Create a Streaming Analytics service in Bluemix – see “Finding the service” in Introduction to Bluemix Streaming Analytics
- Press “Launch” from the Bluemix service dashboard to launch the Streams console
- Press the “play” icon to submit a job
- Browse for the .sab file in your workspace’s
/JDBCSample/output/com.ibm.streamsx.jdbc.sample.jdbcrun.JDBCRunSampledirectory and click Submit
- The Streams app will start. If the operators in the “Streams Graph” panel show green circles, the Streams app is running successfully
- View the logs in the log viewer as shown below
This article has shown how to use Bluemix database services from the Streaming Analytics service. You can use parts of JDBCSample in your own projects to easily integrate with Bluemix databases.
Share this post: