Cloudant

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.

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.

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

• Direct Execution: Directly execute the Query on the original Datasource, without any storage in between. In this case, when a widget is displayed, it will fetch the data in real time from the underlying Datasource.

• Non-Direct Execution: For non-direct queries, results will be stored in Knowi?s Elastic Store. Benefits include- long-running queries, reduced load on your database, and more.

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

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

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

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:

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](https://cloudant.com/blog/introducing-cloudant-query/) or [HTTP Parameters](http://docs.cloudant.com/api/design-documents-querying-views.html#query-arguments) 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 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 are 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 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 not 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.

---