Cloud9QL is an optional SQL-like syntax to transform data in powerful ways when needed.
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.
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)