Overview

Skill Level: Intermediate

Recipe expects usage skills on IBM Watson IoT Platform and Cloudant NoSQL DB Service on IBM Bluemix

Recipe describes about how Cloudant NoSQL DB stores the device data into different bucket sizes and creates Map Views to query the device data using cURL

Step-by-step

  1. Introduction

     

    In this recipe, we are going to describe:

    • How IBM Watson IoT utilises Cloudant NoSQL DB to store historical device events in different databases
    • The different map views available for users to query the historical device data
  2. Device Data Details stored in Cloudant NoSQL Database

    After configuring Cloudant NoSQL DB as Historian Data Storage for IBM Watson IoT Platform, and sending some device data you may be thinking “now what can I do with that data?”  This tutorial explains some basics for querying historical device data forwarded to Cloudant by IBM Watson ioT Platform.

    Watson IoT Platform Historian Connector for Cloudant automatically creates the NoSQL DB using database naming scheme:

    iotp_<orgid>_<choice>_<bucket_name>

    where:

    iotp: Fixed and begins the NoSQL Database Name

    orgid: Organization Id which we get from our Watson IoT Platform instance

    choice: User given option to provide custom choice for NoSQL Database name. By default, it’s “default”

    bucket_name: The bucket size is configured as daily, weekly or monthly. Bucket name is derived from configured value as described below:

    • YYYY-MM-DD for daily buckets
    • YYYY-wWW for weekly (01 <= WW <= 53)
    • YYYY-MM (01 <= mm <= 12) for monthly

    We can use database naming scheme to dynamically derive the Cloudant NoSQL Database names in data processing code.

    Watson IoT Platform Historian Connector for Cloudant automatically creates Map views based on the device data stored in Cloudant NoSQL DB. We use these views to query the device data from the Cloudant database. Each Map view is indexed using a key, so that we can use that key for our queries. Following are the different Map views created automatically by the Connector for Cloudant using different keys as indexes:

    • by-date : Timestamp value is used as key
    • by-deviceId : Device Id is used as key
    • by-deviceType : Device Type is used as key
    • by-eventType : Event Type is used as key
    • by-milliseconds : Number of milliseconds since January 1, 1970, 00:00:00 GMT are used as key

    Launch the Cloudant Service Dashboard from BlueMix:

    cloudant_launch1

    Select Databases tab to see the views defined by Cloudant Connector:

    cloudant_dbs1

    Select the required database to view the Map views created on the device data stored in Cloudant Database as marked in above image:

    cloudant_views1

     

    In this section, we have touched upon the Map views that automatically get created by the Cloudant Connector and how to view them in the Cloudant Dashboard. We’ll see how to query and process the device data in coming sections below.

  3. Querying Device Data stored in Cloudant NoSQL DB using cURL

    Cloudant NoSQL DB provides ReST interface to query the data stored in the databases. We need to frame the URL to be used and the parameters for the HTTP GET Method. The URL consists of Cloudant Host, Cloudant Database, Cloudant Design Document and the Cloudant View. The Cloudant Host, User Name and Password can be obtained from Bluemix Cloudant Service Credentials:

    cloudant_credentials1

    In our case, the Cloudant Host is b59f1c15-6c1a-44f2-bc79-e9375297739d-bluemix.cloudant.com.

    The Cloudant Database name can be obtained from Cloudant Dashboard:

    cloudant_dbs1

    In our case, the Cloudant Database name is iotp_20wyks_default_2016-11.

    The Cloudant Design Document name is fixed and it’s get added, created by Cloudant Connector Module:

    cloudant_design_doc1

     

    The Cloudant View can be any one of the view created by the Cloudant Connector Module as described under previous section. We need to specify based on the key we want to use as part of our query.

    The URL format for Cloudant Query looks as shown below:

    https://cloudant-host/cloudant-database-name/_design/iotp/_view/view-name

    Example URL to get records using the view by-deviceId looks as shown below:

    https://b59f1c15-6c1a-44f2-bc79-e9375297739d-bluemix.cloudant.com/iotp_20wyks_default_2016-11/_design/iotp/_view/by-deviceId

     

    To query the device data using cURL statement, we need to specify the Cloudant-Username and Cloudant-Password as part of URL for authentication. The URL format for Cloudant Query as part of cURL statement:

           https://cloudant-username:cloudantpassword@cloudant-host/cloudant-database-name/_design/iotp/_view/view-name

    Example cURL statement to get records using the view by-deviceId looks as shown below:

    curl https://b59f1c15-6c1a-44f2-bc79-e9375297739d-bluemix:<cloudant-password>@b59f1c15-6c1a-44f2-bc79-e9375297739d-bluemix.cloudant.com/iotp_20wyks_default_2016-11/_design/iotp/_view/by-deviceId

     

    The Cloudant defines certain set of key words to be used as parameters to queries. Here provided are some of them which we’ll use in our queries in cURL and python statements:

    • limit
    • skip
    • descending
    • key
    • keys
    • startkey and endkey

    For complete details about Cloudant and how to use Cloudant Views with parameters, refer to Bluemix Cloudant API Documentation on Using Views.

    Here are some of the sample cURL statements to retrieve device data from Cloudant Database:

    Query-1: Example of retrieving a list of the first five documents from a database, applying the view by-deviceId.

    Solution:

    • Frame URL with Cloudant user, password, host and database name
    • View Name to use : by-deviceId
    • Parameters to HTTP GET request : limit=5

     

     

     

     

    Query-2: Example of retrieving third to seventh record for the device “piCam-4”.

    Solution:

    • Frame URL with Cloudant user, password, host and database name
    • View Name to use : by-deviceId
    • Parameters to HTTP GET request : key=”piCam-4″ , skip=2 and limit=5

     

     

     

     

     

    Query-3: Example of retrieving most recent device event using view by-date.

    Solution:

    • Frame URL with Cloudant user, password, host and database name
    • View Name to use : by-date
    • Parameters to HTTP GET request : descending=true and limit=1

     

     

     

     

    Query-4: Example of retrieving the device events for 2 device types – elevator and egType using view by-deviceType.

    Solution:

    • Frame URL with Cloudant user, password, host and database name
    • View Name to use : by-deviceType
    • Parameters to HTTP GET request : keys=[“elevator”,”egType”]

     

     

     

     

    Query-5: Example of retrieving the device events between 2 milliseconds values using view by-milliseconds.

    Solution:

    • Frame URL with Cloudant user, password, host and database name
    • View Name to use : by-milliseconds
    • Parameters to HTTP GET request : startkey=1478249932915 and endkey=1478257809438

     

     

     

     

     

    In this section, we described about how to frame the URL to use in Cloudant Query.

  4. Conclusion

    You should now have a good idea how historical device events from IBM Watson IoT Platform are stored in Cloudant, specifically how to access and process those events.

     

2 comments on"Query and Process Watson IoT Device Data from Cloudant NoSQL DB"

  1. How to query by deviceId and by date descending in one query?

  2. venkatanishi June 20, 2017

    sir, how to change the name of the document that appears in the database sir

Join The Discussion