What can we help you with?

e.g. Getting Started, Build Dashboards

Top

Cloudant

Follow

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 Settings -> 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:

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.
 
Was this article helpful?
0 out of 0 found this helpful
Return to top

Related articles