Login
Start Trial
Request Demo

Cloudant Datasource Integration

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

Connecting

  1. Log in to Knowi and select Queries from the left sidebar.

  2. Click on New Datasource + button and select Cloudant from the list of datasources.

  3. After navigating to the New Datasource page, either use the pre-configured settings into Cloud9 Chart's own demo Cloudant database or follow the prompts and configure the following details to set up connectivity to your own Cloudant database:

    a. Datasource Name: Enter a name for your datasource
    b. Cloudant Host Name: Enter the host name to connect to
    c. Database/Schema Name: Enter the Database/Schema name
    d. User ID: Enter the User ID to connect
    e. Password: Enter the password to connect to the database

  4. Click on the Test Connection to confirm a successful connection to the Cloudant database, hit the Save button, and start Querying.

adding-cloudant

Query

Step 1: Query using a query editor Query Editor: After connecting to the Cloudant datasource, Knowi will pull out a list of indexes. Using these indexes, you can write queries directly in the Query Editor, a versatile text editor that offers more advanced editing functionalities like HTTP Query Params/Cloudant JSON Query, support for multiple language modes, Cloud9QL, and more.

query-editor

Step 2: Define data execution strategy by using any of the following two options:

Non-direct execution can be put into action if you choose to run the Query once or at scheduled intervals. For more information, feel free to check out this documentation- Defining Data Execution Strategy

data-strategy

Step 3: Click on the Preview button to analyze the results of your Query and fine-tune the desired output, if required.

preview

The result of your Query is called Dataset. After reviewing the results, name your dataset and then hit the Save & Run button.

create-and-run

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:

Datasource Configuration:

Parameter Comments
name Unique Datasource Name.
datasource Set value to cloudant
url DB connect URL. Example: https://cloud9charts.cloudant.com
dbName Database name to connect to
userId DB User id to connect
Password DB password

Query Configuration:

Query Config Params Comments
entityName Dataset Name Identifier
identifier A unique identifier for the dataset. Either identifier or entityName must be specified.
dsName Name of the datasource name configured in the datasource_XXX.json file to execute the query against. Required.
cloudantIndex Cloudant Index name. Use _all_docs to query against the primary index, or use a custom secondary index name. Not required if you use Cloudant Query syntax
cloudantQueryParams Cloudant Query or HTTP Parameters to specify. For HTTP Params, multiple params can be specified using the & delimiter. Example Cloudant Query JSON:*{"selector":{"hits":{"$gt":3}},"limit":100}*. Example HTTP Params: *descending=true&limit=1000*
cloudantJoin Enables lookup values from another Cloudant database. See the Join section below for more details.
c9QLFilter Optional cleansing/transformation of the results using Cloud9QL. See Cloud9QL docs
frequencyType One of minutes, hours, days,weeks,months. If this is not specified, this is treated as a one time query, executed upon Cloud9Agent startup (or when the query is first saved)
frequency Indicates the frequency, if frequencyType is defined. For example, if this value is 10 and the frequencyType is minutes, the query will be executed every 10 minutes
startTime Optional, can be used to specify when the query should be run for the first time. If set, the the frequency will be determined from that time onwards. For example, is a weekly run is scheduled to start at 07/01/2014 13:30, the first run will run on 07/01 at 13:30, with the next run at the same time on 07/08/2014. The time is based on the local time of the machine running the Agent. Supported Date Formats: MM/dd/yyyy HH:mm, MM/dd/yy HH:mm, MM/dd/yyyy, MM/dd/yy, HH:mm:ss,HH:mm,mm
overrideVals This enables data storage strategies to be specified. If this is not defined, the results of the query is added to the existing dataset. To replace all data for this dataset within Knowi, specify {"replaceAll":true}. To upsert data specify "replaceValuesForKey":["fieldA","fieldB"]. This will replace all existing records in Knowi with the same fieldA and fieldB with the the current data and insert records where they are not present.

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:

Join Options Comments
url Cloudant URL to connect to. Optional - uses the URL of the datasource within the query, if this is not configured
dbName Database name to connect to. Uses the parent database defined in the datasource for the query if this is not configured.
userId Optional userId to connect with. Defaults to the userId in the datasource for the query if this is nt configured.
password Optional. Defaults to the parent datasource password to connect with, if empty.
lookupKeyField Required. The field name in the results to do a secondary lookup against.
resultPrefix Optional, but recommended. A prefix to add to the results of the lookup to be merged into the result.
c9QLFilter Optional cleansing/transformation of the results using Cloud9QL.