Detect anomalies in streaming IoT data using EventStreams and SingleStore database on Cloud Pak for Data
In the Internet of Things (IoT) world, the sensors send events periodically. This event data is stored, and the data scientist builds machine learning models for actionable insights on the data. The machine learning model can be a prediction model for the equipment parameter going outside of a certain threshhold.
The following scenario is considered for this code pattern. In a chemical research plant, the containers containing various chemicals under study are required to be maintained within certain threshold. In our case the minimum temperature threshold is 27°F and maximum threshold is 30°F. If container temperatures are too low or if container temperatures are too high, the consequences could be fatal. Hence, a swift action must be taken when the container temperature crosses the defined threshold.
This code pattern will cover anomaly detection in IoT event streams using IBM Event Streams and IBM Cloud Pak for Data with SingleStore database. It will also cover prediction of the container temperatures for future days and detect on which day would the container cross the threshold.
Once you complete the code pattern, you will learn to:
- Send IoT events to IBM Event Streams.
- Create SingleStore KAFKA pipelines to consume event data from EventStreams and store the data in a SingleStore table inside IBM Cloud Pak for Data.
- Create a machine learning model to predict temperature events on IBM Cloud Pak for Data.
- Create a Cognos dashboard to visualize the insights on IBM Cloud Pak for Data.
- IoT temperature sensors send data to IBM Event Streams.
- Kafka pipelines in SingleStore consume data from IBM Event Streams and store data into tables.
- The AI model on IBM Cloud Pak for Data takes the sensor data from singlestore at regular intervals as input. The model forecasts the sensor data, predicts anomalies and stores back the anomaly data to singlestore.
- A dashboard is displayed by Cognos on IBM Cloud Pak for Data using the AI model output in the SingleStore database.
- IBM Cloud Account
- IBM Cloud Pak for Data
- Install SingleStore database on Cloud Pak for Data
- Java
- Gradle
- Create an instance of IBM Event Streams
- Note broker urls
- Create credentials
- Download the SSL certificate from Event Streams
- Upload the certificate to the SingleStore cluster
- Create a database, table and pipeline
- Clone the repo
- Build and run the application
- Deploy and run the AI model on Cloud Pak for Data
- Create dashboard using IBM Cognos Analytics on Cloud Pak for Data
Click here to create an instance of Event Streams.
Select Lite plan and click Create.
Select Home on the menu and click on the paste icon to copy the comma separated broker urls.
Note: The URLs will be needed to download the Event Streams SSL certificate and also to run the producer application
Select Credentials on the menu and click New Credential.
In the newly created credential, note the api key:
Open a terminal and run the below command after replacing [broker-xxx-eventstreams.cloud.ibm.com:9093] with one of the broker URLs noted earlier:
openssl s_client -connect [broker-xxx-eventstreams.cloud.ibm.com:9093] -servername [broker-xxx-eventstreams.cloud.ibm.com:9093] -showcerts > evtstreams.pem
The Event Streams certificate will be stored in a file evtstreams.pem.
Login to the Cloud Pak For Data cluster:
oc login --token=[token] --server=[url]
Create a secret for the Event Streams certificate after specifying the path to the directory that contains the evtstreams.pem that you downloaded earlier:
kubectl create secret generic node-memsql-cluster-master-0-additional-secrets --from-file=[Path to the directory where evtstreams.pem is downloaded]
Run the below command to open the SingleStore container shell:
kubectl exec -it node-memsql-cluster-master-0 /bin/bash
Login to the SingleStore DB with the credentials noted earlier:
memsql -u [username] -p[password]
Run the below command to create a new database - iotprediction.
create database iotprediction;
use iotprediction;
Run the below command to create the table:
CREATE TABLE `data` (`id` int(11) DEFAULT NULL,`read_dt` datetime DEFAULT NULL,`temperature` int(4) DEFAULT NULL);
Run the below command to create the pipeline after replacing the [Broker 0 URL] with the broker-0 url we noted earlier(For example: broker-0-xxx.kafka.svc08.us-south.eventstreams.cloud.ibm.com:9093) and [API Key] with the api_key we noted earlier from credential:
CREATE PIPELINE `temperature_pipeline` AS LOAD DATA KAFKA '[Broker 0 URL]/tempreads' CONFIG '{"security.protocol": "sasl_ssl","sasl.mechanism": "PLAIN","ssl.certificate.location": "/etc/memsql/extra-secret/evtstreams.pem","sasl.username":"token"}' CREDENTIALS '{"ssl.key.password": "null","sasl.password": "[API Key]"}' INTO table data;
Open a terminal and run the below command to clone the repo:
git clone https://github.com/IBM/anamoly-prediction-streams-singlestore-cp4d
Change the directory:
cd sources/java/event-streams-producer-client/
Run the below command to build the application:
gradle clean build
Run the below command after replacing the [kafka_brokers_sasl] and [api_key] you noted earlier:
java -jar ./build/libs/event-streams-producer-client-2.0.jar [kafka_brokers_sasl] [api_key] -producer
Note: The kafka_brokers_sasl must be formatted as "host:port,host2:port2". If not, format the contents of kafka_brokers_sasl in a text editor before entering it in the command line.
-
Log into your
IBM Cloud Pak for Dataconsole. -
Click on the menu tab and select all projects.
-
Create on
New projecttab. -
Select
Analytics projectand click onNext. -
Select
Create an empty project. -
Give a name to the project and click on
Create. -
Once the project is created you will be redirected to the project that you created. If you are not redirected to the project then navigate to the projects tab from main menu and then select the project that you created in step above.
-
Click on
Add to projectand SelectNotebookas shown below.
- Goto
From filetab and upload the notebookNotebook/IoT-Forecasting.ipynband click onCreateas shown below.
- Once the notebook loads, fill the singlestore credentials as shown below.
-
Click on
Celltab and clickRun All. -
Click on
Filetab and clickSave Versionas shown below.
- Go back to
Assetsand create a job by following the gif in the dropdown below or alternatively you can follow the steps shown below.
-
Click on the three dots at the corner of the your notebook under
Notebookstab and selectCreate Job. -
Under
Define detailsgive a name for the job underNamesection and clickNext. -
Under
Configurekeep the default values and clickNext. -
Under
Scheduleenable the schedule toggle switch. Check theRepeatcheckbox. From the dropdown selectminutesand write 4 beside it. Click onNext. -
Click on
Create. -
Your job is now scheduled. Wait for sometime and then goto
Jobstab and check if the jobs are running.
- The results of the AI model will be stored into
predictionstable on singlestore database.
There are 3 major steps to build a dashboard on IBM Cognos Analytics:
-
- Create a connection to singlestore database
-
- Create a module to load and publish the metadata to the cognos public folder section.
-
- Access the module and build the dashboard.
Launch IBM Cognos Analytics from IBM Cloud Pak for Data instance console or ask the administrator to provide the Cognos Analytics url.
- Firstly, launch IBM Cloud Pak for Data instance by using url provided by your adminstrator.
See the below screenshot of the IBM Cloud Pak for Data login page. Provide appropriate credentials given by your administrator.

Now you will see a IBM Cloud Pak for Data Home page as follows:

-
Click on burger menu bar and click on
Instancesunder services tab to see the installed services. Here we want to see if Cognos Analytics is installed. See below screenshot for details.
-
Now click on Cognos Analytics to launch the instance. See below screenshot for details.

See the Cognos Analytics home page as below.

-
Now to create a SingleStore database connection, click on
Manage. See below screenshot for details.
-
Ensure you select either
Maria db connection or MySqlas database connection type and provide the SignleStore credentails. See the below screenshot for details.
-
In this code pattern, we have used MariaDB connections. See the below screenshot for details.

-
Next, ensure to load the metadata of the tables that you want to consume from the Cognos Dashboard. In this code pattern to build the dashboard. we will use 2 tables(Data & Predictions). Data table is used to show historical data and the Predictions table will show data related predictions of the temperature of the containers from the AI model that we built and loaded in the SingleStore databse instance in the previous step. See below screenshot to load metadata of the tables.

-
See below screenshot for the sample data snapshot(SingleStore database) of the 2 tables that we have used in the Cognos Dashboard.
- Launch Data module to create a metadata package, which is used as in input source to build a Cognos Dashboard.
- Connect to SingleStore
iotpredictionsdatabase. See below screenshot for details.
- Select the tables required for the dashboard and drag to the datamodule project pane. See below screenshot for details.
-
To create a dashboard using IBM Cognos Analytics, refer to the this tutorial.
-
Below is the dashboard which will represent the Historical and Average Temperature trends.
-
Historical Temperature trends. We have used Date, and min & max temperature fields in x & Y axis respectively to build the below widget.

-
Average Temperature trends by date. We have used Date, and min & max temperature fields in x & Y axis respectively to build the below widget.

-
Temperature Predictions for the next 7 days. We have used Date, and min & max Predicted temperature fields in x & Y axis respectively to build the below widget.

In this code pattern, you consumed streaming data from IBM Event Streams in a SingleStore database. It is a secure connection from SingleStore to EventStreams using a pipeline which is the most efficient way to ingest into SingleStore database. The SingleStore pipelines use parallel processors and can handle high volumes of data. You learnt how to build a machine learning model in Cloud Pak for Data environment using SingleStore as a persistent storage to build,deploy and execute the model realtime. Next, you also built the visualisation Dashboard of not only historical data but also predictions data showcasing in Cognos dashboards which is again on IBM Cloud Pak for Data.
















