Cloudant

Cloudant is a leading distributed Database-as-a-Service for fast-growing data. Knowi enables visualization, reporting automation and analysis of Cloudant data.

If you are not a current Knowi customer, visit our Instant Cloudant Reporting page to get started.

Connecting

1. Login to Knowi and select Queries -> New Datasources from the left-hand menu.

2. Click on 'New Datasource' and select Cloudant. Follow the prompts to set up connectivity to Connectivity to your own Cloudant database (or, use the pre-configured settings into our own demo Cloudant database).

3. Save the Connection. Click on the "Configure Queries" link on the success bar.

Queries & Reports

1. This section enables you to set up automated queries that can either be run on a schedule or once.

   Unique Name: Specify a unique name for the report.

   Cloudant Index: Primary or Secondary Index name to use (defaults to primary index _all_docs). Not applicable if the new Cloudant JSON Query is used. Read more about Cloudant indexes here

   HTTP Query Params/Cloudant JSON Query: Optional HTTP Parameters, or, new Cloudant JSON Query to extract results.

   Example Cloudant Query JSON:

     {
       "selector": {
         "hits": {
           "$gt":3
         }
       },
       "limit":100
     }
   

   Example HTTP Params:

     descending=true&limit=100
   

   Cloud9QL Query: Optional syntax developed by Knowi that can be applied to the results of the Cloudant query to further transform the results. For example, the default example contains two statements:

   select sum(Hits) as Hits, date(date) as Date group by date (date)
   

   This manipulates the results from Cloudant to group the sum of hits on a daily basis.

   If you're using the Knowi demo Cloudant database, click 'Preview' to see the results

   See Cloud9QL documentation for more details.

2. Scheduling: Configure how often this should be run. Select 'None' for a one-time operation. The results are stored within Knowi

3. Overwrite Strategy (for scheduled query runs):

   Overwrite Strategies determines how the data is stored in Knowi:

   i. If empty, data will be added on to the existing data for this dataset within Knowi. Or,

   ii. "All": Any existing data for this dataset will be replaced by this results.

   iii. One or More Field Names (Example: "A,B,C"): A new record is created where the values of the combination of the field names do not exist, and, updates current records for the field grouping where it exists. For example, if this is set to say "Date, Type", existing data with the same Date and Type values will be updated with the latest data, and new records created when they do not exist.

4. Click 'Preview' to see the results.

5. Click on 'Save' to complete setting up the report. This also sets up this data extraction on a schedule, if configured.

6. Click on 'Dashboards' to access dashboards. You can drag and drop the newly created report from the bottom list into to the dashboard.

Cloud9Agent

As an alternative to the UI based approach above, you can use a Cloud9Agent to connect and process Cloudant data within your network. You can also use Cloud9Agent for advanced use cases such as multi-db joins/lookups, combining multiple datasources with your Cloudant data and others.

Highlights:

• Pull data from your Cloudant directly, using Cloudant Query, or using indexes.
• Optionally cleanse/transform that data with Cloud9QL.
• Execute queries on a schedule, or, one time.
• Join/lookup fields from one database to another.

Datasource Configuration:

Query Configuration:

Datasource Example:

[
  {
    "name":"demoCloudant",
    "url":"https://cloud9charts.cloudant.com",
    "dbName":"demo",
    "datasource":"cloudant",
    "userId":"someUser",
    "password":"somePass"
  }
]

Query Example:

[
  {
    "entityName":"Cloudant Demo",
    "dsName":"demoCloudant",
    "cloudantIndex":"_all_docs",
    "cloudantQueryParams":"descending=true&limit=1000",
    "c9QLFilter":"select sum(Hits) as Hits, date(Date) as Date group by date (date) order by date asc",
    "overrideVals":{
      "replaceAll":true
    }
  }
]

Nested Objects & Arrays

Nested objects and arrays can be queried using Cloud9QL

Query Example:

  [
    {
      "entityName":"Cloudant Nested Demo",
      "dsName":"demoCloudantNested",
      "cloudantIndex":"_all_docs",
      "c9QLFilter":"select nestedObj.a as Nested Object Val, nestedArr[0] as First Item Nested Arr",
      "overrideVals":{
        "replaceAll":true
      }
    }
  ]

Cloudant Joins

Joins enable lookups of data from other Cloudant databases to be merged in with the parent query. Example Query:

{
  "entityName":"Cloudant Join Demo",
  "dsName":"demoCloudant",
  "cloudantIndex":"_all_docs",
  "cloudantQueryParams":"descending=true&limit=1000",
  "cloudantJoin":[
    {
      "dbName":"joindbdemo",
      "lookupKeyField":"deviceId",
      "resultPrefix":"join_",
      "cloud9QLFilter":"select * limit 10"
    }
  ],
  "overrideVals":{
    "replaceAll":true
  }
}

In the above example:

1. We pull data using _all_docs from a Cloudant DB defined in demoCloudant.

2. A list of all deviceId fields in the results is used as keys to obtain data from another Cloudant DB, joindbdemo:

   i. All fields from the lookup are added on to existing results as new columns.

   ii. The lookup is key based - deviceId, in this case, must correspond to the _id field in the lookup database.

   iii. The lookup results are prefixed by "join_" in the above example.

   iv. The overall results are further manipulated by the cloud9QLFilter filter.

Multiple joins/lookups example:

  {
    "entityName":"Cloudant Join Demo",
    "dsName":"demoCloudant",
    "cloudantIndex":"_all_docs",
    "cloudantQueryParams":"descending=true&limit=1000",
    "cloudantJoin":[
      {
        "dbName":"joindbdemo",
        "lookupKeyField":"deviceId",
        "resultPrefix":"joinA_",
        "cloud9QLFilter":"select * limit 10"
      },
      {
        "dbName":"joindbdemo",
        "lookupKeyField":"joinA_ipAddress",
        "resultPrefix":"joinB_",
        "cloud9QLFilter":"select hits, joinA_ipAddress, joinB_country"
      }
    ],
    "overrideVals":{
      "replaceAll":true
    }
  }

In the above example, the results after the first lookup is passed into the second lookup section.

Parameters: