Cloud9QL Basics

Cloud9QL is Knowi's SQL-like syntax to transform data in powerful ways. Knowi users have the option of either using Cloud9QL to transform their data, or the drag and drop UI. 

Uses

• Transform, clean and post-process data.

• Complement existing API's/query syntax of datastores.

  For example, if your data is in MongoDB, Cassandra or any other datasource that we support, Cloud9QL can be used as a post processor to the native query syntax.

• Generate alternate views from the same data within Knowi.

• Query file based data or datastores with no/limited query support. 

Quickstart

Select everything:

select *

Keyword 'select' is optional.

Select specific fields:

select Sent, Date

Field Aliases:

select Sent as Sent Messages, Date

Conditions:

select * 
where opened > 100000

select * 
where campaign_name like artists

select * 
where Message Type = Transactional and sent > 200000

Supported Operators:

>, >=, <, <=, !=, like, not like, and, or 

Ordering:

select * 
where opened > 100000 
order by opened desc

Supported order types: asc, desc

Limit:

select * where opened > 100000 order by opened desc limit 1

Unique records:

select distinct *
 
select distinct customer

Select everything, then add a function:

select *, sent/100000 as sent rate

Lag:

Used to access data from a previous row

select LAG([, offset[, default]])
select LAG(customer, 3)

Users can also group data in the LAG function and compare the grouped results with the previous values. Use the keyword Group by for grouping.

select LAG([, offset[, default]]) Group by (field_name)
select LAG(cuisine, 3) Group by region