About Custom SQL

You can write extensive SQL statements from within CocoSQL. These statements are linked directly to the UI. This means that any changes performed in the UI are available by referencing the Table Name from within the query.

The SQL dialect used is CocoSQL. CocoSQL is a “universal” SQL dialect which automatically translates your query into the underlying dialect. E.g. BigQuery or Athena.

CocoSQL is based on DuckDB, and very closely follows it’s semantics, with only a few differences. For more information, please see the CocoSQL Reference.

Open the Editor

You can open the editor for a frame by pressing the “Show SQL Editor” button on the bottom of your frame. This button is available in both the canvas and full-screen view.

You’ll be presented with an editor view. As you start typing, you’ll get full autocomplete with function and parameter completion.

Writing a Query

The autocomplete will show available functions, keywords, types, table names and columns as you type.

You can reference the current frame by name, as well as other frames in your project. Just start typing their name and they will show up.

Where are the “Tables”?

The notion of a table doesn’t exist in Coco Alemana. We think of everything as frames. A frame could be raw data, or could be raw data with changes, or queries applied to it.

A frame is similar to a VIEW in SQL. This helps you reduce the amount of SQL you have to write, and can even allow you to create frames iteratively.

Errors and Warnings

We’ve improved the SQL experience to include common “gotcha” checks. This includes identifying errors, and providing concrete error messages in the location where the error or warning occurred.

Location information is not always available for errors, but when it is, we show it.

The image below shows subtle warnings about index offsets, and the use of NULL comparisons.

Running a Query to Preview

You can run your query without overriding your main frame. This allows you to check your results before committing to your query and overriding the current frame’s contents.

Press the Run Query button at the bottom right of the editor, or hit ⌘ Return to preview your results. Preview results show in the same area as your original frame’s data, but with a blue dashed border, indicating that it’s temporal.

Note

Only preview records are available during a query preview. We do not re-calculate distributions until the query is applied.

Clearing the Preview

You can clear the preview by clicking the Clear, next to the Apply button. You can also hit ⌘ K on your keyboard.

If you re-run the query, the results will be instant, so this can be a good way to jump back-and-forth between real results, and your query.

Apply Query Results to the Frame

Apply query results to the current frame by pressing Apply, or hitting Shift ⌘ Return on your keyboard.

This will override the current frame with your query results.

Note

It’s important to remember where you’re writing your query…

If you write a query in frame “A”, but only reference another frame, “B”, then frame “A” will be overridden with frame “B“‘s contents.

If you don’t want to override a frame with your query, it’s best to make a Duplicate before querying.

Mixing Interface Actions and Queries

While SQL can be extremely flexible, it can also be extremely verbose, and taxing to write. Coco Alemana treats frames as bi-directional. This means that UI changes applied are available when you start querying, and applied queries can be modified via the UI.

This powerful pairing allows you to, say, filter a dataset using the interface filters, but doing a complex calculation in SQL.

So, before you write a long query, think to yourself “What can I do in the interface”?

See More

For a list of function definitions to use within Coco SQL, see the CocoSQL Reference.

For how to create custom columns within the interface, see Custom Columns.