Explore your data warehouse using the SQL Runner. This feature allows you to preview SQL queries and subsequently create workbooks and datasets directly from SQL. 

This article will show you how to use the SQL Runner to create a new workbook or dataset. Custom SQL-based workbook data elements can also be created from within an existing workbook. Both flows use the SQL Runner. You are also able to reference your existing Sigma datasets within your SQL. 

Summary of Content

Requirements
Create a Workbook or Dataset
Reference existing Sigma datasets in your SQL
Format your SQL
Query History
View Multiple Tables
Keyboard Shortcuts

Requirements

  • To use this feature, you must be a Creator or be assigned a custom account type with permission to write custom SQL.
  • To run SQL against a connection, you must have Can Use access your data at the connection level. Learn more
  • The SQL Runner will only appear if you have connection level access to at least one connection in your organization.

 

Create a Workbook or Dataset from SQL

  1. Click the Create New button in the left side navigation panel to open the Create New / Explore Data menu. 
  2. Select Write SQL.
  3. Select your connection from the dropdown menu.
  4. The data tree on the left side of the page will allow you to explore your data.
    You may choose to click on any table to preview its data.
    Screen_Shot_2021-07-29_at_3.59.22_PM.png
  5. To write SQL, begin typing keywords into the text box in the top half of the screen. Sigma will automatically provide a list of autocomplete options to guide you. Screen_Shot_2020-08-18_at_2.28.21_PM.png

    Note: You can reference Sigma parameters in your SQL by wrapping the parameter's name in curly brackets: {{my_parameter_name}}

    If the parameter's value is being output with single quotation marks, you can remove these quotation marks by prepending the special keyword "#raw" before its name: {{#raw my_parameter_name}}

  6. To run your SQL query, click the blue Run button.
    Keyboard shortcuts: CTRL-Enter on a PC or CMD-Enter on a Mac.
  7. To explore your query in a workbook, click the Explore button the top right corner of the page. 
    OR
    To create a dataset, click the dropdown icon next to Explore. Then click Create Dataset.

 

Reference existing Sigma datasets in your SQL

You can reference your existing Sigma dataset in your SQL by using its fully qualified name as a View inside the warehouse. This will be a combination of its Location and its Dataset view name.

SELECT * FROM <location>.<dataset_warehouse_view_name>

You can find these particulars within the dataset's information panel here: Screen_Shot_2022-12-29_at_12.37.12_PM.png

Example: 

SELECT * FROM SIGMASUPPORT.WRITE_BACK.VIEW_MY_SIGMA_DATASET


Format your SQL

Use the format SQL icon button to quickly format long blocks of SQL text.

Screen_Shot_2021-07-29_at_4.01.25_PM.png

Query History

When you are using the SQL runner, you will have access to a history of the queries you've recently run against your current connection. To access this history, click the ‘History’ tab in the left hand panel.
Screen_Shot_2020-08-18_at_2.41.04_PM.png

View Multiple Tables

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

Keyboard Shortcuts

Mac Shortcut PC Shortcut Command
CMD + Enter CTRL + Enter Submit query for execution
CTRL + Space CTRL + Space Open autocomplete
CMD + / CTRL + / Toggle commenting on or off for the highlighted line
CMD + Left   Go to the start of the line
Tab Tab Indent line
Shift + Tab Shift + Tab Indent less
Shift + CTRL + K Shift + CTRL + K Delete current line
CMD + L CTRL + L Select line
CMD + CTRL + Up Shift + CTRL + Up Shift line up
CMD + CTRL + Down Shift + CTRL + Down Shift line down
CMD + J CTRL + J Join Lines
Shift + CMD + D Shift + CTRL + D Duplicate line
CMD + K CMD + K CTRL + K CTRL + K Delete the portion of the line to the right of the cursor
CMD + K CMD + delete CTRL + K CTRL + Backspace Delete the portion of the line to the left of the cursor
CMD + K CMD + U CTRL + K CTRL + U Upcase at cursor
CMD + K CMD + L CTRL + K CTRL + U Downcase at cursor


Was this page helpful?
Yes No