How-tos

Using Streaming Analytics with JDBC-enabled Bluemix databases

Share this post:

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.

Add Comment
No Comments

Leave a Reply

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

More How-tos Stories

Home automation powered by Cloud Functions, Raspberry Pi, Twilio and Watson

Over the past few years, we’ve seen a significant rise in popularity for intelligent personal assistants, such as Apple’s Siri, Amazon Alexa, and Google Assistant. Though they initially appeared to be little more than a novelty, they’ve evolved to become rather useful as a convenient interface to interact with service APIs and IoT connected devices.

Continue reading

Interpreting Spring Social Twitter Data with Watson Tone Analyzer

In this post, I'll show you how to build a basic Spring app with Twitter login using Spring Social. Then we'll use Watson Tone Analyzer to determine the dominant emotion from each of the tweets on the time of the logged-in user. The project we will create will be similar to the Accessing Twitter Data Spring guide, but with a few modifications.

Continue reading

Arria brings Natural Language Generation to IBM Cloud

The Arria Natural Language Generation APIs service is an addition to the Finance category on the IBM Cloud platform. This blog post shows you how to get started with Arria’s Natural Language Generation APIs service on the IBM Cloud platform.

Continue reading