Write custom SQL (Beta)

🚩

The new SQL editor replaces a previous, more limited SQL editing feature.

This is a public beta feature that’s subject to quick, iterative changes. As a result, the latest product version may differ from the contents of this document.

When you explore your data warehouse using the SQL editor, you can preview SQL queries and create workbooks and datasets directly from SQL.

This document describes how to:

Requirements

  • To use this feature, you must be assigned an account type with the permission to write custom SQL.
  • To run custom SQL, you must have the Can use data permission for the entire connection. See Data permissions.
  • The SQL editor only appears if you have connection-level access to at least one connection in your organization.
  • To reference existing Sigma workbook elements within your SQL, you must have write access configured on your connection.

Create a workbook from SQL

  1. Open Sigma Home.

  2. In the navigation panel, click Add element Create New, then select Write SQL to open the SQL editor.

    Image of the options available when you open Create New. The options shown are Workbook, Dataset, Data Model, Write SQL, and Upload CSV.

  3. In the side panel, click Select a Connection and select the connection you want to query.

  4. In the query editor, enter your custom SQL. Sigma provides autocomplete suggestions to guide you.

    Image typing select * from EXAMPLE., with an autocomplete dropdown suggesting multiple available tables.

    πŸ’‘

    You can reference workbook controls in your SQL by wrapping the control id in curly brackets. See Reference workbook controls.

  5. To run your SQL query, click Run, or use the keyboard shortcuts ⌘ + return on a Mac or ctrl + enter on a PC.

  6. To save your exploration as a workbook, click Save As.

Create a SQL workbook element

  1. From within a workbook, click Edit in the top right corner of the page.

  2. Select Add element to open the workbook's ADD NEW ELEMENT panel.

  3. Under DATA ELEMENTS, select the type of element you want to add: TABLE, VIZ, or PIVOT TABLE.

  4. Select SQL to write custom SQL to retrieve data from a CDW.

  5. Select the connection you want to query.

    An element appears in your workbook, prompting you to enter a SELECT statement to query the connection.

  6. In the query editor, enter your custom SQL. Sigma provides autocomplete suggestions to guide you.

Image of the SQL editor element with an autocomplete suggestion appearing for a SELECT statement

πŸ’‘

You can reference workbook controls in your SQL by wrapping the control id in curly brackets. See Reference workbook controls.

Reference workbook controls

You can reference workbook controls in your SQL by wrapping the control’s id name in curly brackets:
{{my-control-id}}

You can find the control ID in the Settings panel for a control. See Parameters in workbooks.

If the control's value is output with single quotation marks, you can remove these quotation marks by prepending the keyword #raw before the control id:

{{#raw my-control-id}}

If the control is a Date range control, you can extract the start and end date:

{{my-control-id}}:start and {{my-control-id}}:end

Limitations and warnings

  • If you change the value of a control to target a table with a different schema, the query produces different columns than expected, resulting in errors.
  • If you use the #raw configuration value, row-level security can be bypassed in the worksheet, creating a security vulnerability.

Reference multi-select list parameters

Multi-select list controls pass the multi-selected data as an array.

For more information see the following articles in Sigma Community:

Reference existing Sigma workbook elements

You can reference an existing Sigma workbook element in your SQL by creating a Warehouse View of your element and using the path of the view as the query's source table in the SQL editor. A Warehouse View transforms your published workbook element into a view inside the cloud data warehouse.

πŸ“˜

The Warehouse View represents the published state of the workbook. To promote new changes in your workbook to the Warehouse View, publish your changes.

The path of the Warehouse View in your cloud data warehouse is comprised of the Location and Workbook Warehouse View name.

If you already created a warehouse view for your workbook element, find the details of the view so that you can reference it in SQL.

  1. Navigate to the workbook containing the element.

  2. Hover over the workbook element.

  3. Select info View warehouse view info.

    Image showing the dropdown panel visible after selecting info, with the location and view name visible.

  4. Click Copy path to copy the full path to your clipboard.

For example, to query the warehouse view shown above, use the following SQL:

select * from SAMPLE_CONNECTION_PRODUCTION.ORG_S_CF2DE26E9A2C4C58BA1C91A0955DF7EE.SALES_BY_MONTH_AND_STORE_REGION

Shortcuts

Toggle to SQL editor in the element menu

Click Toggle SQL Editor icon Toggle SQL Editor to switch back and forth between your data element and the inline SQL editor to update an element by modifying its SQL query.

GIF showing the mouse click the SQL icon to open the SQL editor for an element.

You can also use the space bar or click (Maximize icon Maximize element to expand the element to full screen mode.

GIF of fullscreen toggle

Format SQL

Click Left align iconΒ Format SQL to quickly format long blocks of SQL text.

GIF of SQL editor showing a block of SQL being formatted with the Format SQL button.

Find and replace

Click Search SQL icon Search SQL or use the keyboard shortcut ⌘ + F to open up a tool that allows you to search within the SQL query and optionally replace instances of your searched term.

Image of SQL editor with find and replace modal open.

Access custom SQL from the Lineage view

View and edit custom SQL from the Lineage view of a workbook. Copy the full query to your clipboard or click Edit SQL to go to the inline SQL editor for the element.

Image of lineage view with Custom SQL node selected and Custom SQL summary displayed

View the query history

When you use the SQL editor, you can access the history of the queries recently run against the current connection. To access this history, click the History tab in the left panel.

Image of the History tab selected, showing 2 recent queries, both SELECT * from EXAMPLES.COMMERCE... queries, one run a few seconds ago and one run 9 minutes ago.

View multiple tables

When writing your query, you can still use the left panel to explore data tables. You can switch between the Table Preview and SQL results, letting you preview columns you might want to use when joining data tables.‍

Examples

For more advanced custom SQL cases, refer to these examples.

Change output based on a user attribute

If you specify a schema_name user attribute, you can write custom SQL to switch schemas depending on the value of that attribute for a user. For example:

SELECT * FROM test.{{#raw system::CurrentUserAttributeText::schema_name}}.orders

For more details, see User Attributes.

Return output for a specific date range control

To return rows only when a date column matches the value specified in a date range control, refer to the following example SQL:

SELECT * FROM table
WHERE
CASE WHEN date({{Date-Range-Control}}:start) IS NULL
THEN DATE <= date({{Date-Range-Control}}:end)
WHEN date({{Date-Range-Control}}:end) IS NULL
THEN DATE >= date({{Date-Range-Control}}:start)
ELSE DATE BETWEEN date({{Date-Range-Control}}:start) AND date({{Date-Range-Control}}:end)
END

This SQL returns all rows where the DATE column matches the value specified in Date-Range-Control. The exact syntax might be different for your connection depending on how your particular cloud data warehouse (CDW) interprets the control.

For example, to extract the start date of the date range control in custom SQL:

  • In Snowflake, the control is a VARIANT data type, so you can use the following syntax:
    to_timestamp({{Date-Range-Control}}:start)
  • In BigQuery or Databricks, the control is a STRUCT data type, so you can use the following syntax:
    {{Date-Range-Control}}.start
  • In Amazon Redshift, the control is a SUPER data type, so you can use the following syntax:
    select date_range.start start from (select {{Date-Range-Control}} date_range)
  • In PostgreSQL, the control is a JSONB data type, so you can use the following syntax:
    ({{Date-Range-Control}}->>β€˜start’)::timestamptz

Return rows depending on the value of a multi-select control

If you have a multi-select control called City and you run this SQL on a Snowflake connection, the following example query returns all rows where the CITY column value is listed in the selection for the City control. If there are no cities selected in the City control, all rows are returned:

SELECT *
FROM EXAMPLES.BIKES.STATIONS
WHERE
CASE WHEN
LEN(ARRAY_TO_STRING(ARRAY_CONSTRUCT{{City}},',') ) = 0
THEN True
ELSE CITY in {{City}} END

Return rows based on a schema text selector

If you have a single-select or text control called schema-param that has a valid selection (a schema in your connected CDW), and the STATIONS table exists in the specified schema, the following example SQL returns all columns from the STATIONS column in the specified schema:

SELECT *
FROM {{#raw schema-param}}.STATIONS