Cloud9QL Basics

What is Cloud9QL?

Cloud9QL is Knowi's in-house version of SQL, designed to simplify data transformation and manipulation directly within the platform. By leveraging Cloud9QL, users can effortlessly clean, filter, and manipulate their data in multiple stages of the analysis process without needing to depend on native query languages of the underlying datastore.

Here’s a basic Cloud9QL query:

select * 
where sales > 1000 and region = 'North America' 
order by sales desc

This query retrieves all records where sales exceed 1,000 in the North America region and orders them by sales in descending order. Notice that we don’t need a FROM statement—the Cloud9QL query is already associated with a table. 

• Quickstart Gude
• Key Differences Between Cloud9QL and Other Versions of SQL
• Where Can I Write Cloud9QL?

Quickstart Guide

Select All Data

select *

Keyword 'select' is optional.

Select Specific Fields

select Sent, Date

Select All, Except Specific Fields

Use the ~ symbol before a field name to exclude that field. 

select *, ~Message_Type, ~Customer_Name

Aliasing Fields

You can rename fields on the fly to make the output more meaningful:

select Sent as Sent Messages, Date

Applying Conditions (Filters)

Cloud9QL supports logical operators like:

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

select * where opened > 100000

select * where campaign_name like 'artists'

select * where Message_Type = 'Transactional' and Sent > 200000

Sorting Data

Order results easily with asc or desc:

select * where opened > 100000 order by opened desc

Limiting Results

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

Unique Records

select distinct *

select distinct customer

Counting Rows

Use count to see how many rows are in your dataset:

select count(*)

select count(*) where department = 'HR'

CLoud9QL supports the following aggregation functions. Explore them here. 

• count, sum, avg, distinct, max, min, sd, median

Accessing Nested Values

You can access nested values, including elements within arrays, from non-relational databases like MongoDB or JSON files using dot notation and array indexing. Learn more about working with nested arrays and objects here. 

select 
    nestedObj.a, nestedArr[0], 
    nestedObj.secondLevel.x as SecondLevelObject, 
    nestedObj.secondLevel.y[1] as SecondLevelArray;

select customer, nestedObj.secondLevel.y as Nested;
select expand(Nested);

select expand_arrays(nestedArr1, nestedArr2);

select expand_arrays_with_defaults(nestedArr1, null, nestedArr2, 0);

Learn more about Cloud9QL's expand functions here. 

Writing More Complex Queries and Adding Comments

Multiple Statements

You can run multiple SQL statements in a row to create a more complex, multi-step transformation. Each statement uses the output of the previous one as its starting point, and every statement must end with a semicolon.

select department 
group by department 
having count(*) > 5;

select department, AVG(salary) AS average_salary 
group by department;

Comments

Single-Line Comments

• Use -- to comment out a single line of text. Everything following -- on that line is ignored.

SELECT * FROM employees;  -- This retrieves all employees

Multi-Line or Block Comments

• Use /* */ to comment out multiple lines or a block of text. Everything between /* and */ is ignored

/* This block of code retrieves employee names and departments. 
We are ignoring other fields for now. */ 

select name, department;

Key Differences Between Cloud9QL and Other Versions of SQL

• No FROM Statement Required: In Cloud9QL, queries are always associated with a specific dataset, so there’s no need to explicitly include a FROM clause.

• Case Insensitivity: SQL keywords (e.g., SELECT, WHERE) and field names are not case-sensitive. However, string values in conditions remain case-sensitive. For example: select * where region = 'North America'

• Spaces Allowed in Field Names: You can include spaces in field names without needing to surround them with quotes. For example: select Messages Sent

• Optional SELECT Statement: The SELECT keyword is optional and can be omitted for simplicity.

• Subqueries Not Supported: Although Cloud9QL doesn't support subqueries, you can write multiple statements by separating them with a semicolon. Additionally, you can use the append function to combine different aggregations from the same dataset.

Where Can I Write Cloud9QL?

Cloud9QL can be applied at the query level or the widget level.

At the Query level

You can use Cloud9QL transformations on datasets both before and after joins, directly within the Queries section.

Select the Editor tab.

You can use Cloud9QL to transform data either alongside or instead of the native query language of the underlying datastore. In this example, the Cloud9QL transformations will be applied after the Mongo Query is executed.

After the data is joined you can apply a Cloud9QL Post Query to your dataset. 

At the Widget level

Once data is presented in a widget, further transformations can be made through the Filters button. 

Find the filters button at the top right corner of any widget.

At the bottom of the tab you will find Cloud9QL Transformation.

Here you can apply your Cloud9QL transformations. Press the preview button to see the changes applied to the dataset. 

What other functions does Cloud9QL support?

To further explore the functionality of Cloud9QL, check out our documentation here.