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 Data Analytics Stories

Cloudant available on dedicated environments and with an improved SLA in Bluemix Public

The IBM Cloudant team is happy to announce two big changes to the Cloudant offering available on Bluemix Public. First, the ability to provision Cloudant instances on a dedicated environment is now live via the Dedicated Hardware plan in the catalog. This is in addition to the provisioned throughput capacity based Lite and Standard plans that have been available since September last year. With the introduction of the Dedicated Hardware plan, the full complement of Cloudant offerings is now available on Bluemix Public. Second, all Cloudant Standard plan instances now come with a 99.95% SLA. Let's dive a bit more into what's new!

Continue reading

Put your data to work faster with the right data preparation tools

Data is widely seen as the new source of competitive advantage, driving smarter decisions and helping enterprises out-think their rivals. But opportunities are often missed because it takes too long for business analysts, data scientists and application developers to get the data they need from multiple underlying systems, while going through cumbersome IT processes.

Continue reading

Data Science Experience is now available in London

Data Science Experience IBM As IBM continues to expand its global cloud data center footprint this year, the Data Science Experience (DSX) team has made it possible for enterprise customers to run their data science and machine learning workloads in different geographic regions. Now Data Science Experience (DSX) is available through IBM's London data center.

Continue reading