Data Analytics

Using Streaming Analytics with JDBC-enabled Bluemix databases

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.

Setup

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:

  • Import JDBCSample into Streams Studio from the toolkit’s sample directory streamsx.jdbc/samples/JDBCSample
  • Open JDBCRunSample.spl file in Streams Studio.

JDBCRun Streams Graph

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 opt directory inside the imported JDBCSample directory
  • Move the extracted db2jcc4.jar file to the new opt directory.

Before running the JDBCSample application, enter the following information into the parameters of each JDBCRun operator:

Name Value
jdbcDriverLib the jdbc driver library (use opt/db2jcc4.jar for this sample)
jdbcClassName the class name for jdbc driver (use com.ibm.db2.jcc.DB2Driver for this sample)
jdbcUrl the database URL. (jdbcurl from the service credentials)
jdbcUser the database user. (username from the service credentials)
jdbcPassword jdbcUser’s password. (password from the service credentials)

jdbcDriverLib and jdbcClassName come from the driver you added, and the values given in the table are correct. jdbcUser, jdbcPassword, and jdbcUrl correspond with the username, password, and 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:

The 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 printer.

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
View SQLDB table

dashDB table view
View dashDB table

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
    Streaming Analytics play button
  • Browse for the .sab file in your workspace’s /JDBCSample/output/com.ibm.streamsx.jdbc.sample.jdbcrun.JDBCRunSample directory and click Submit
    Streaming Analytics submit job
  • 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
    Streaming Analytics logs

Conclusion

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:

Share on LinkedIn

Add Comment
No Comments

Leave a Reply

Your email address will not be published.Required fields are marked *

More Data Analytics Stories

Chasing down data and stronger performance

Threshold tests, max aerobic tests, and sprint tests became a regular part of my mornings. I knew my average watts. This attention to data paid off on the cycling course of the Ironman Lake Placid when I finished the bike portion 23 minutes faster. I was finally learning to embrace data to drive my performance.

Streaming Analytics + Message Hub: You’ll need more data

It’s never been easier to use IBM Streams and Kafka Messaging! Kafka-based Message Hub service is now available and fully integrates with the IBM Streams-based Streaming Analytics service. If your data can be streamed to Kafka, it is now trivial to process that data in real time with Streaming Analytics. Both IBM Streams and Apache Kafka are built for speed. Can your data keep up? More...

Databases on tap with Compose on Bluemix

Compose databases are now available from the Bluemix catalog. Find out how you can use Compose for MongoDB, PostgreSQL, Redis, Elasticsearch, RethinkDB and RabbitMQ with your applications now.