Overview

Skill Level: Beginner

This recipe will show you how to store IoT device data in Cloudant NoSQL DB using triggers, extensions and Node-RED. You will see how the data is stored, thus enabling you to choose the best option later on for a given data processing or analytic task.

Step-by-step

  1. Introduction

    The Recipe ‘Simulate an Elevator – All using Node-RED nodes‘ showed you how you can simulate a real world IoT scenario taking an Elevator as example and using Node-RED. A second recipe titled ‘Monitor Elevator events in real time to take corrective actions using Watson IoT Platform’, performed real time analytics on the Device events and implemented a closed control loop by using triggers and Node-RED to put the elevator in maintenance mode should the motor temperature go beyond 200 degree.

    In this recipe you shall go through different scenarios that can be used to store device events in a Cloudant NoSQL DB for later use by analytics and data scientist tools. Recipes for tools such as IBM Watson Analytics, IBM Cognos Analytics and IBM Data Science Experience shall then be introduced in a later point in time.

    There are basically 3 ways to store device events in Cloudant NoSQL DB using the Watson IoT Platform (WIoTP): Historical Data Storage Extension, Node-RED flows and finally WIoTP rules.  In this recipe you will try out all 3 options and observe how the events are stored in various ways in the Cloudant NoSQL DB. Some pro and cons of the options will then finally be discussed in the conclusion. It will be assumed that you have already run through the 2 recipes listed as pre-requisites, and that you are familiar with IBM Cloud and the Watson IoT Platform. With this as a starting point you will go through the following steps:

    1. Getting started by retrieving the necessary credentials for the Cloudant NoSQL DB.
    2. Configure the Historical Data Storage Extension to store device data in the Cloudant NoSQL DB.
    3. Configure a Node-RED flow to store device data in Cloudant NoSQL DB..
    4. Configure a WIoTP rule to store device data in Cloudant NoSQL DB.
  2. Architecture

    The figure below shows the architecture of the elevator simulator including the Internet of Things Platform Starter boilerplate consisting of the Watson IoT Service, Node-RED and Cloudant NoSQL DB:

    00.1-Architecture

    The elevator simulator sends the data to the Watson IoT service, which in turn stores it in the Cloudant NoSQL DB for later processing by e.g. analytic or data science tools. There are basically 3 ways to store device events in Cloudant NoSQL DB using the Watson IoT Platform (WIoTP):

    1. Historical Data Storage Extension, which can be used to store your IoT device data in multiple databases, one for each discrete time interval – the so-called bucket interval (day, week, month). To connect and configure a Cloudant NoSQL database you must select a bucket interval, timezone and database name that will be used to name the individual databases.
    2. Node-RED, by using a ‘cloudant out’ node to insert a new document into the database. You have the option to store the entire message object, the ‘msg.payload’ object or actually to compute the object that you would like to pass on to Cloudant.
    3. WIoTP rules, where you can define an action that submits a POST request to the target Cloudant NoSQL DB database with the result that a new document is created in the database. The body of the POST request will contain information about the event such as the timestamp, the device id and the message itself.
  3. Getting Started

    To get started you will need to get the Cloudant NoSQL DB credentials first and then start up the Cloudant NoSQL database and WIoTP dashboards next.

    To get the credentials of the Cloudant NoSQL database, do the following:

    1. Log into IBM Cloud.
    2. Open the WIoTP application for the elevator simulator that you created in the “Simulate an Elevator – All using Node-RED nodes” recipe.
    3. Select the Runtime tab to the left of the window, then the Environment variables tab in the top middle as shown below.

      01-VCAP-Variables

    4. In the text field named VCAP_SERVICES, scroll down to the section that defines the credentials for the Cloudant NoSQL DB service.
    5. Select the user name, password and host fields as shown above, then copy and save them for later use.

    You will also need to launch the Cloudant NoSQL DB so that you can observe how the documents are stored. Perform the following steps:

    1. In the browser window showing the Application Details of your elevator simulator WIoTP application, select the Connections tab to the left to display the services used by the application.
    2. Double click the service named CloudantNoSQLDB and wait until you see the dashboard for the service.
    3. In the dashboard, click the Launch button at the top right and wait until the CloudantDB NoSQL DB dashboard appears in a new browser window. Databases

    Next, you will need to open up Watson IoT Platform dashboard:

    1. In IBM Cloud, select the elevator simulator WIoTP application again.
    2. Select the Connections tab to the left to display the services used by the application.
    3. Double Click the iot-service to open it.
    4. When you see the ‘Welcome to the Watson IoT Platform’ page, click the Launch button to open the dashboard for the Watson IoT Platform.
      01-WatsonIoTDashboard

    The screenshot above shows a custom board of the Watson IoT Platform with 4 custom cards for Elevator01. The first chart shows the value of the motor temperature, the second when the elevator has been in maintenance mode, the third the most recent event data received and the fourth the current floor.

    You are now ready to take the first step towards storing elevator events in Cloudant NoSQL DB.

  4. Storing elevator device data using the Historical Data Storage Extension

    The Historical Data Storage Extension finds and configures compatible services that can be used to store your IoT device data. Basically, the device data are stored in buckets. One bucket is created for the current time interval (be it a day, a week or a month), another for the next time interval. The buckets persist over time and need to be either deleted or archived e.g. to Object Storage, whenever they are no longer needed in Cloudant NoSQL DB.

    The steps needed to configure the service are described in the recipe “Configure Cloudant NoSQL DB as Historian Data Storage for IBM Watson IoT” starting from step 3. We shall briefly recapture the steps here for convience. Basically this means that you need to do the following:

    1. In the Watson IoT Platform, click the Extensions tab to the left.
    2. In the Historical Data Storage section, click the button named Setting up. HistoricalDataStorage
    3. This will display a list of available Cloudant NoSQL DB services.HistoricalDataStoreList
    4. For the service named cloudantNoSQLDB, click the Select button to the right. This will open up a dialog where you can configure the extension.HistoricalDataStorageConfig
    5. Configure the Historical Data Storage extension as follows:
      1. Bucket Interval set to Month.
      2. Time Zone using the defaults settings.
      3. Database Name being set to ‘elevator_history’.
    6. Click Done.
    7. In the resulting dialog Click CONFIRM and close the window when it tells you that authorization has been successful.

    Next do the following to observe the effect of the configuration just performed in terms of the data bases created and the documents stored:

    1. Select the browser window for the Cloudant NoSQL DB dashboard.
    2. Click the Databases tab to the right and observe that 3 databases have been created: one for the current month, one for the next and one for the history configuration.HistoricalDatabasesCreated
    3. Select the database for the current month (that’s the one that contains more than one document).
    4. For one of the documents, click the Pencil in the upper right corner to open up the document.
      HistoricalDocument
    5. Click Cancel

    The Historical Data Storage Extension stores the documents as hierarchically structured JSON objects. You can consequently create indices and queries for the documents right away. The recipe named “Query and Process Watson IoT Device Data from Cloudant NoSQL DB” explains the details about the naming conventions and the structure of the historic databases and gives additional instructions on how to query and process this kind of data.

    From the perspective of a programming language this architecture – with dynamic changing database names and hierarchically structured JSON objects – pose no problem at all since the program can compute the database name at runtime and select the JSON properties using field selection. Tools for the Data Scientists, such as the IBM Data Science Experience, have no problems with changing database names or with handling connections to multiple databases either. So if a data scientist would like to analyze the history for the last 3 buckets, that could be easiliy achieved with a little bit of programming e.g. in Python.

    For traditional analytic tools the case is a bit different because database connections are usually statically defined. Changing such connection definitions may either be difficult or impractical if it happens too frequently. Moreover, such tools would usually need access to data over a specific time period that is likely to include the current time interval as well as one or more previous time intervals. Usually this could be achieved using database joins, but the connection is still, in principle, dynamic by nature.

  5. Storing elevator device data using Node-RED

    Device data can also be stored in a database by using Node-RED. This approach works out-of-the-box for Cloudant NoSQL databases as well as for IBM DB2 Warehouse on Cloud databases because IBM Node-RED offers specific nodes for sending device data to the database. You will need to do 3 things to complete this section:

    1. get hold on a Node-RED flow from GitHub which will be the starting point,
    2. import it into Node-RED and update it to add additional properties and finally
    3. check in Cloudant NoSQL DB how the documents are stored.

    You can find the Node-RED flow for saving device data to Cloudant NoSQL DB in a dedicated GitHub repository. Do the following

    1. Click the hyperlink iot-nodered-save2cloudant. This will take you to the GitHub repository which has a single document defining the flow:GitHubRep
    2. Select the entire line of text.
    3. Copy it to the clipboard.

    Next, go through the following steps to create the Node-RED flow:

    1. Open the application for the elevator simulator and select the Overview tab to the left.
    2. In the top right corner, Click View App to open up Node-RED. If this for some reason does not work, append “/red” to the URL and refresh. The URL should look like “https://<appname>.<region>.mybluemix.net/red”
    3. You should now see a Node-RED definition with 2 flow definitions, one for the elevator simulator and another for putting the elevator in maintenance mode.NRImport
    4. Invoke Import > From Clipboard to start importing a definition of the flow needed to store device data in a Cloudant NoSQL DB.
    5. The import command will open up the following import nodes dialog.
      NRImportNodes
    6. Paste the content from the clipboard into the text field, select the button named new flow and click Import. You should now see a flow looking like this.
      NRImportedFlow
    7. Click the cloudant out node named elevator_events.
      NRCloudantNode
    8. The node defines the target Cloudant NoSQL service, the target database, the operation and the object to store.
    9. Click Done to close the dialog.

    If the current definition of the flow is used as-is for saving information to Cloudant NoSQL DB, the device data would be stored in Cloudant NoSQL DB, but there would be no time parameter indicating when the event occured. Moreover, there would be no information about the device type or the device Id either. This would be quite a deficit if the data was to be used later on for analytics. We will therefore need to add these properties to the payload. Basically, this can be done by using the following piece of code:

    deviceId = msg.deviceId;
    deviceType = msg.deviceType;
    msg.payload = msg.payload.d;
    msg.payload.timestamp= new Date().toISOString();
    msg.payload.deviceId = deviceId;
    msg.payload.deviceType = deviceType;
    return msg;

    Do the following to update the definition of the function node:

    1. Click on the function node.
    2. Change the definition of the function as shown below.
      NR-Edit-Function-Node
    3. Click Done to save the changes
      NRDeploy
    4. Click Deploy and wait until the flows have been deployed.
    5. Select the Debug tab and check that commands are generated.

    Next you will need to open up the database named elevator_events and observe how the data is stored:

    1. Select the browser window for your Cloudant NoSQL DB dashboard.
    2. Refresh the window and observe that the database called elevator_events has been created:NRElevatorEventsDB
    3. Click the database named elevator_events to view the documents.
    4. For one of the documents, click the Pencil in the upper right corner to open the document.NRDBDocument-2
    5. Observe that the document is a flat JSON object containing the elevator telemetry as well as the requested timestamp, device id and device type .
    6. Click Cancel.

    The data types of the individual properties are main stream data types found in the world of traditional databases: strings, numbers, booleans and dates. This is an ideal starting point for passing the data to a relational database for reporting and analytical purposes. It does not impose the same challenges as the Historical Data Storage Extension which created a hierarchical JSON object. This does not mean that it is impossible to Extract-Transform and Load (ETL) such hierarchical data structures into a data warehouse for further reporting, it just means that the ETL jobs require more “intelligence” compared to a data source that is as flat and simple as the one above. Cloudant DB does indeed offer of-of-the-box capabilities for generating Data Warehouse tables e.g. in IBM DB2 Warehouse on Cloud from a Cloudant NoSQL database and it can handle both cases: a flat relational document as well as a hierarchical structure as the one generated by the Historical Data Storage Extension. We shall look into these aspects in an upcoming recipe.

    Notice that the documents do not contain information regarding whether the elevator is in maintenance mode or not. The motor temperature may go beyond 200 degree, causing the elevator to get into maintenance mode, and then remain in that mode even if the motor temperature falls below 200 degree. To add the relevant attributes to the event data perform the following steps:

    1. Go back to the Node-RED editor.
    2. Select the node Send Status Event.
      04.1-Send-Status-Event
    3. Double click the node to open the editor for the properties of the node.
    4. Click the Pencil to the right of the property named “Device Schema”. This will allow you to change the schema of the event data.
      04.2-Add-Attribute-to-Payload
    5. Select the properties maintenanceStop and maintenanceReason as shown above.
    6. Click Update in the upper right corner of the dialog.
    7. Deploy the changes to the node red flow by clicking the Deploy button.
    8. You can test in the debug window if the properties are now part of the payload.

    You have now added the 2 properties to the Node-RED flow. You will however also need to add the properties to the schema for the Device Type in the Watson IoT Platform:

    1. Go back to the dashboard of the Watson IoT Platform.
    2. Select Devices, then the Manage Schemas tab.
      04.3-Watson-IoT-Device-Type-Schema
    3. Select the Properties tab as shown above.
    4. Click the Add Property button.
      04.4-Device-Type-Add-Property
    5. Select d.maintenanceStop and d.maintenanceReason.
    6. Click OK.
      04.5-Watson-IoT-Device-Type-Schema-Updated
    7. You should now see the two new properties in the schema, meaning that they can from now on be used e.g. in Rules or in sending information to persistent storage.
    8. Click Finish.

     

  6. Storing elevator alerts using rules

    In this section you will use rules to store elevator alerts that occur when the motor temperature gets above 200 degree in a dedicated database in Cloudant NoSQL DB. This way we can later analyze the alerts e.g. to detect the point in time they happen, which elevator was impacted by the alert and if there are some sort of correlation in the device data when the event occurred.

    1. In the Watson IoT dashboard, click the Rules tab to the left. You will now see the rule that you created in the recipe “Monitor Elevator events in real time to take corrective actions using Watson IoT Platform“.
      01-WatsonIoTDashboardRules
    2. Check that the rule is active. If not, set the State as shown to ‘Activated’.
    3. Click +Create Cloud Rule in the upper right corner to create a new rule.
    4. Enter the following Name and Description and select Elevator as the target type for the rule:
      05.1-Add-New-Cloud-Rule
    5. Click Next.
    6. You should now see the following definition of the rule:
      05.2-Empty-Rule
    7. Set the Alert priority to high.
    8. Click the placeholder for the condition and set the condition to “motorTemp > 200!.
      05.3-Rule-Conditoin
    9. Click New action in the right part of the screen.
    10. Set the Action dialog, click + Add action at the top left corner to add a new action.
    11. In the resulting Edit Action dialog, enter a Name and a Description for the action as shown in below:ActionScreen1
    12. For the Type of the action, select Webhook action as shown above and click Next.
    13. This will take you to the Configure tab where you can enter the details for the HTTP request. ActionScreen3-1
    14. Enter the parameters for the HTTP Request:
      1. For the URL, use the host field of the Cloudant DB credentials that you copied previously suffixed with “/elevator_alerts” and prefixed by “https://”. The URL should have the form
        https://<cloudant nosql db user name>-bluemix.cloudant.com/elevator_alerts”.
      2. For the Method, select POST.
      3. For the User Name and Password, use the credentials that you noted previously.
      4. For the Content Type, select application/json.
      5. Scroll down and observe the template that defines the body of the HTTP request. It shows that the Body includes the timestamp, the device Id and the message (amongst others):
        RuleBody
      6. Click Finish. This will take you back to the Set the Action dialog. If the Watson IoT Platform complains that the URL is not well formed, check it, change it and try again.
    15. Click OK to save the changes to the rule.
    16. You should now see the following final rule definition.
      05.4-Finished-Rule
    17. Activate the rule by clicking the Activate button in the top right part of the screen.
    18. Click Save to save your work.

    When finished you will need to look at how the device data is stored in Cloudant:

    1. Select the browser window for the Cloudant NoSQL DB dashboard.
    2. Select the Databases tab. DatabaseList
    3. Click Create Database in the top right corner and enter the name of the database as shown:
      CreateDatabase
    4. Click Create. This will create an empty Cloudant NoSQL DB database.
    5. Click the Databases tab to the right and observe that the database is listed. DatabaseList2
    6. Select the database named elevator_alerts. There will probably be no documents in the database yet (unless of course the motor temperature of one of the elevators went above 200 degree since you created the database).

    To test that the configuration of the rule works, simply go to the elevator simulator window and do the following a couple of times:

    1. Increase the motor temperature above 200 degree,
    2. Wait for the elevator to go into maintenance mode.
    3. Decrease the temperature so that it gets below 200 degree.
    4. Take the elevator out of maintenance mode

    The URL of the elevator simulator is on the form: “https://<appname>.<region>.mybluemix.net/ui”

    Having triggered a number of alerts time has come to verify that the updated rule works as intended and at the same time look into how the data is stored in the Cloudant NoSQL DB.

    1. Select the browser window that contains your Cloudant NoSQL DB dashboard. You should still be in the view that shows the details of the database named elevator_alerts.
    2. Refresh the window. You should now see a list of documents, one for each alert that has been generated:AlertDocuments
    3. Select the Pencil icon in the top right corner of the first document to open it.AlertDocumentDetails
    4. Observe that the document contains the timestamp, the device id and the message, but that the message is stored as a string value rather than as a JSON object.
    5. Click Cancel.

    Storing the message as a string has some implications on how the message can be processed e.g. by analytic tools. From the point of view of a relational database, the message with all its device data would just be viewed as a simple string. In order to query the properties we would need to extract and transform the string into a set of discrete and meaningful properties. This would call for typical Extract-Transform-Load (ETL) jobs of some sort. From the point of view of a programming language, the implications would be less significant because one could just parse the string to obtain the corresponding JSON object using existing library functions.

  7. Conclusion

    There are basically 3 ways to store device events in Cloudant DB using the Watson IoT Platform:

    • Historical Data Storage Extension, which can be used to store your IoT device data in multiple databases, one for each discrete time interval – the so-called bucket interval. The data is stored in a hierarchically structured JSON object and access to the buckets must be dynamic. This solution is a good choice for archiving purposes (e.g. in Object Stores) and may also be an adequate approach for storing data that is about to be used by data science tools such as IBM Data Science Experience. It is good fit as well for generating Data Warehouses using built in capabilities of Cloudant NoSQL DB. However, since the data is stored in several databases, it would probably need to be Extracted, Transformed and Loaded (ETL’ed so to say) into a consolidated Data Warehouse for proper reporting using analytics tools. Another interesting approach is documented in the recipe “Unify Your Watson IoT Platform Data With Seamless Analytics (Part I)“, which introduces the socalled seamless storage driver, an Apache Spark driver which abstracts away the gap between real-time and historical data in the Watson IoT Platform, enabling transparent analytics over all data using native Spark SQL.
    • Node-RED flow, by using a node named “cloudant out” to insert a new document into the database. The data is stored in a single database where each document can have (if the Node-RED flow is appropriately defined) a flat structure. Node-RED provides in extend a lot of flexibility for pre-processing or enriching the messages with additional information if required. This is a good approach in preparing data for analytic tools that have a relational point of view of the data. Depending on the problem at hand, it could even be worth considering bypassing Cloudant NoSQL DB and directly sending the information to e.g. IBM DB2 Warehouse on Cloud using Node-RED – a topic that will be treated in a separate recipe. However, it is worth noting that once a document is stored in a Cloudant NoSQL DB, it is not possible to completely delete it physically. Cloudant does not completely delete the specified document. Instead, it leaves a tombstone with basic information about the document. The tombstone is required so that the delete action can be replicated to other copies of the database. Since the tombstones stay in the database indefinitely, creating new documents and deleting them increases the disk space usage of a database. The amount of data coming in over time will therefore influence whether this approach can be used for production, at least when using a single target database over time. So if you wonder why the Historical Data Storage Extension uses buckets, rather than storing everything in one database, you should know it by now.
    • WIoTP rules, where you can define an action that submits a POST request to the target Cloudant DB database with the result that a new document is created in the database representing the event. The device data is stored in a single bulk string value. This choice is adequate e.g. for stream processing or for further access using programming languages and data science tools (with programming languages under the hood) but need additional parsing operations as part of ETL jobs in order to make the data available for analytics using relationally or columnar structured data warehouses. Moreover it is worth noting that whereas the use of the Historical Data Storage Extension will not add additional costs for processing the messages, rules will (per MB data processed).

     

    With this knowledge in mind we should be prepared to start using device data in various analytic tools such as Watson Analytics or Cognos Analytics as well as in data science solutions such as IBM Data Science Experience. We will look into these aspects in subsequent recipes.

Join The Discussion