Querying data

Overview

The report is where analysis happens in Mode. Navigate to the Mode home page and click the

New

button in the upper right corner of the window to create a new report and get started. When you create a new report, it is automatically placed into your personal Collection.

When you create a new report in Mode, you will be taken to the query editor, where you can compose SQL code.

Query editor

Workflow Basics

The query editor contains a number of sections that should be familiar to you if you have worked with other SQL clients before:

  1. Database dropdown:

    Tells Mode which of your connected databases you want to query and which database's schemas and tables to display in the schema browser.
  2. Schema browser:

    A visual representation of the schemas, tables and columns that are available in the selected database.
  3. Code editor:

    Compose your SQL code here. The code editor is powered by Ace and supports most Ace keyboard shortcuts and context-aware autocomplete for database objects (e.g., column and table names), SQL keywords, and Definitions. Across the top of the query editor are a number of controls:

    • Run:

      Executes all code in the editor. Select a portion of your code before clicking

      Run

      to execute only that portion.
    • Limit 100:

      Checked by default. Automatically appends LIMIT 100 to your query so no more than 100 rows are returned in the result set. This speeds up exploratory querying but should be unchecked once you want to see a complete result set.
    • Format SQL:

      Automatically indents and formats your SQL code for you. If you don't like how it formats your code, click it again to revert.
    • View History:

      The running history of each query execution, including the raw code (as written), rendered code (SQL sent to the database after processing all Liquid code), and any data that was returned. Select a historical query run and click

      Open

      to replace the code currently in the query editor with the code from that run.
    • Editor Settings:

      Toggle on/off settings for the code editor. These options allow you to personalize the editor to fit your preferences. Choose between light and dark modes, side-by-side or stacked panels, Limit 100 on/off by default, and other helpful autocomplete settings. To turn autocomplete on or off, you can toggle the switch in the Autocomplete Settings or you can disable (and re-enable) it by hitting CMD + Option + a (Control + ALT + a on a PC) in the editor.
  4. Results table:

    After you successfully execute a SQL query, the results are displayed here. Across the top are a number of actions you can take with these results:

    • Export:

      Exports the query results to a CSV and downloads it to your computer.
    • Copy:

      Copies the query results to the clipboard.
    • Chart:

      Creates a new chart using the query results.
    • Pivot:

      Creates a new pivot table using the query results.
  5. Report navigation panel

    - All of the queries that power your report will be listed here under the Queries tab. Click on the

    New

    button next to the Queries tab to add a new query to your report. Under each query you will see:

    • SQL:

      Click to change focus and edit that query's code.
    • Display Table:

      A customizable display table of the results returned by the most recent query execution.
    • Each visualization that you build using this query's data with Mode's built-in charts will be listed separately. Click the

      New

      button to add a new one.

The query editor accepts any valid SQL code for the selected database and valid Liquid template code. Use Liquid to extend the functionality of your SQL code or add parameter input forms to your report to make it more interactive and extensible.

Schema browser

The schema browser lets you explore the schemas, tables and columns in the selected database.

Schema Browser

  1. Show/hide:

    Click to hide or reveal the schema browser.
  2. Selected data connection:

    Click to reveal and select from a list of data connections. The schema browser will show tables for the database displayed here. The query will also execute against this connection.
  3. Search:

    Type to search for table names in the selected database.
  4. Pinned:

    Tables you "pin" are shown at the top of the schema browser so you can easily find them. Tables are automatically pinned as you query them. To pin a table, hover over its name and click the

    pin

    icon on the right.
  5. Tables:

    All the tables that you have access to for a given data connection are listed here, organized by schema. Click the

    play

    button next to any table name to quickly return a sampling of 100 rows from that table.
  6. Columns:

    A list of all the columns in a selected table. The symbol next to the column name refers to the type of data in that column (e.g., varchar, float, date, etc.).

NOTE:

The

play

button is not available for certain database types, including those that charge on a per-query basis (e.g., Google BigQuery, Amazon Athena, etc.).

Querying multiple data sources

Mode reports can contain multiple queries, and each individual query can retrieve data from any one connected database. Different visualizations, each with data from different databases, can exist side-by-side within the same report.

You cannot JOIN data sets between different connections in a single query. However, you can combine result sets from multiple databases and visualize the combined data using Python or R in the Notebook, or using JavaScript in the HTML editor.

Complex and multi-statement queries

Mode performs very little translation or manipulation of your code before sending it to your database for execution. This means that Mode will be able to execute any code that's valid for your database. Given the right database permissions, you aren't limited to SELECT statements; you can do anything your database lets you do, including:

  • Creating, dropping, and altering tables and views
  • INSERT, DELETE and UPDATE operations
  • Creating, modifying, or employing user-defined functions

Mode will execute code containing multiple SQL statements, separated by semi-colons. For example, the following code is valid in Mode:

SET TIME ZONE 'UTC';

SET search_path TO schema_name;

CREATE TEMP TABLE temp1 AS
(
  SELECT email, company, LOCALTIME AS date FROM customers
);

SELECT * FROM temp1;

Extending SQL with Liquid

Overview

You can extend the power of your SQL queries in many interesting ways by using the open source Liquid template language. Using Liquid, the SQL behind your Mode reports can be manipulated at report run time using loops, if/then statements, and other advanced structures that might be difficult or impossible to do in SQL alone. Several examples of these methods are shown below.

Whenever a query is executed in a Mode report, Liquid code (if present) is evaluated first before the code is sent to your database for execution as SQL. Liquid code is composed of:

Objects

contain attributes that are used to render dynamic content into your SQL query at run time. Objects are wrapped in double curly brackets {{...}}.

Filters

are simple methods that modify the output of numbers, strings, variables and objects. They are placed inside Object tags {{ }} and denoted with a | character.

Tags

make up the programming logic (e.g., if/else, for, etc.) that tells your code what to do. They are wrapped in a single curly bracket and a percent sign {%...%}. Tags don't themselves produce output that gets rendered into your query, but they may instruct Mode to render, ignore, repeat or otherwise modify specific lines of SQL code.

Full documentation on what's possible with Liquid is available on the Shopify help site and documentation for the Liquid GitHub repo.

Common techniques

Variables

Use variables in Liquid to make your code more extensible and maintainable. Declare a variable using the assign method. For example:

SELECT * FROM employee_table WHERE favorite_food = '{{ fav_food }}'

{% assign fav_food = 'peaches' %}

The above code would render into the following code for execution against the database:

SELECT * FROM employee_table WHERE favorite_food = 'peaches'

NOTE

: Variables are scoped only to the query in which they are declared using assign. They cannot be referenced across reports or across queries within the same report.

If/else

Use if/else statements and other control flow tags to change your SQL code dynamically in response to inputs from things like variables or parameters. In the following example, the query that is executed against the database will be different depending on the value of the car_type variable:

{% assign car_type = 'trucks' %}

SELECT *
{% if car_type == 'trucks' %}
  FROM truck_table
{% elsif parameter == 'cars' %}
  FROM car_table
{% endif %}

If car_type = 'trucks', the following code is executed:

SELECT * FROM truck_table

If car_type = 'cars', the following code is executed:

SELECT * FROM car_table
Loops

Loops and other Liquid iteration tags can be used to programmatically generate lists of variables, join statements, columns to select, unions and other things. The query below shows a simple example of a For loop:

SELECT *
  FROM sports_teams

{% for i in (1..4) %}
  LEFT JOIN draft_picks d{{i}}
    ON d{{i}}.team_name = sports_teams.team_name
  AND d{{i}}.round = {{i}}
{% endfor %}

The above code joins the draft_picks table to the teams table four times. Each join is assigned a distinct alias (d1 through d4) and a different condition (the round number of the draft pick). The rendered code that is actually sent to the database for execution is:

SELECT *
  FROM sports_teams
  LEFT JOIN draft_picks d1 ON d1.team_name = sports_teams.team_name AND d1.round = 1
  LEFT JOIN draft_picks d2 ON d2.team_name = sports_teams.team_name AND d2.round = 2
  LEFT JOIN draft_picks d3 ON d3.team_name = sports_teams.team_name AND d3.round = 3
  LEFT JOIN draft_picks d4 ON d4.team_name = sports_teams.team_name AND d4.round = 4

In some cases you may want the last iteration of the loop to produce a different result than other iterations. For example, if you're creating a list of strings separated by commas, you might want a comma after every value except the last one. Liquid includes a forloop.last statement that makes this easy:

WHERE name IN (
  {% for name in list_of_names %}
    '{{name}}'
    {% unless forloop.last %}
      ,
    {% endunless %}
  {% endfor %}
)

For every iteration of the loop except the last one, forloop.last returns false. Therefore, the value in the unless statement---a comma---gets added to your query after every name except the last one.

This query contains two examples of a loop. This query, which uses the assign method below, shows one example.

Array Variables

Typically, for loops cycle through collections of values, such as iterable objects in Python or vectors in R. Liquid doesn't allow you to create arrays of values the same way you would in most languages (e.g. list = ['candy','beans']). To create an array that you can iterate over in a for loop, you have to use the split filter on a delimited string and assign the result to a variable. For example:

{% assign food = 'candy,beans,pizza' | split: ","  %}

{% for item in food %}
  LEFT JOIN types_of_food {{ item }}
    ON {{ item }}.type = '{{ item }}'
{% endfor %}

The above code converts the comma delimited string 'candy,beans,pizza' to an array and assigns that array to the variable food. The for loop then iterates over each value in the array variable food.

Comments

Use {% comment %} and {% endcomment %} tags to instruct Mode to ignore whatever text or code is written between them.

Parameters

Parameters allow you to define forms that are configurable by viewers of your report and which return Liquid objects in your report's code. Parameters are a great way to make reports more extensible, maintainable, and scalable.

Query headers

Liquid templates can be used when defining custom query headers in data sources connected to your Mode organization. A custom query header is prepended to every query run against that data source and is a great way to increase logging fidelity in your database.

SQL keyboard shortcuts

Mode's SQL Editor runs using the Ace Editor library, and we have enabled most of the default keyboard shortcuts for things like commenting or indenting blocks of text. We've also added some Mode-specific keyboard shortcuts:

General

ActionMacPC
Run query ReturnCtrl Enter
Save query SCtrl S
Switch to Report BuilderCtrl IAlt I
IndentTabTab
OutdentShift TabShift Tab
Add multi-cursor aboveCtrl Option Ctrl Alt
Add multi-cursor belowCtrl Option Ctrl Alt
Undo ZCtrl Z
Redo YCtrl Y
Toggle comment /Ctrl /
Change to lower caseCtrl Shift UCtrl Shift U
Change to upper caseCtrl UCtrl U
Fold selection F1Ctrl F1
Unfold Shift F1Ctrl Shift F1
Find FCtrl F
Replace Option FCtrl H
Find next GCtrl K
Find previous⌘ Shift GCtrl Shift K
Open autocompleteCtrl SpaceCtrl Space

Selection

ActionMacPC
Select All ACtrl A
Select leftShift Shift
Select rightShift Shift
Select word leftOption Shift Ctrl Shift
Select word rightOption Shift Ctrl Shift
Select to line start Shift Alt Shift
Select to line end Shift Alt Shift
Select upShift Shift
Select downShift Shift
Duplicate selection Shift DCtrl Shift D

Go to

ActionMacPC
Go to word leftOption Ctrl
Go to word rightOption Ctrl
Go line upCtrl P
Go line downCtrl N
Go to line start Alt
Go to line end Shift Alt
Go to start Ctrl Home
Go to end Ctrl End

Line operations

ActionMacPC
Remove line DCtrl D
Copy lines downOption Shift Alt Shift
Copy lines upOption Shift Alt Shift
Move lines downOption Alt
Move lines upOption Alt
Remove to line endCtrl K
Remove to line start BackspaceAlt Backspace
Remove word leftOption BackspaceCtrl Backspace
Remove word rightOption DeleteCtrl Delete

FAQs

The schema browser is empty or missing tables I know to be in the database

The tables listed in Mode's schema browser may differ from what you expect for a number of reasons:

  • The database was recently connected or updated

    Mode's schema browser updates roughly every 30 minutes. If you recently connected a new database, the schema browser may appear blank for up to 30 minutes. Similarly, if new tables were added to an existing database, they may not appear for up to 30 minutes. To instruct Mode to perform a schema browser refresh, click on the button in the upper right corner of the schema browser and click

    Refresh

    .

    New tables and databases, however, may be queried immediately regardless of whether or not they appear in the schema browser.

  • You don't have permission to see the missing tables

    Mode connects to your database as a database user. This user, which is defined by your database, may not have access to all of the tables in your database. If you think this might be the case, try querying one of the tables that's missing from the schema browser. If the query returns an error saying you don't have permission to access that table, this is likely the issue.

    Resolve this issue by granting the database user access to the missing tables. These configurations are defined by the database and typically managed by database admins. These permissions cannot be changed directly in Mode.

  • Your database connection is Google BigQuery

    Updating schema information for Google's BigQuery requires Mode to make a request to your BigQuery database. Because Google charges money per request, Mode does not automatically update the schema for BigQuery databases. To manually instruct Mode to perform a schema browser refresh, click on the button in the upper right corner of the schema browser and click

    Refresh

    .

Does Mode time-out long running queries or reports?

Mode will cancel any incomplete queries or report runs after a certain period of time to prevent long-running queries from degrading the performance of Mode or your database. Note that your database may be configured to time-out queries sooner than the times listed below:

ScenarioTime-out after
Manual query / report run12 hours
Scheduled run (daily / weekly / monthly)12 hours
Scheduled run (hourly)1 hour
Scheduled run (every 30 minutes)30 minutes
Scheduled run (every 15 minutes)15 minutes

Error messages

Sorry, this data is larger than your limit

Mode limits the size of query results that you can access depending on whether you're using Mode Studio, or which paid plan you've chosen.

For Mode Business and Enterprise customers, we offer different plans that support increased capacity up to 10 GB.

Query result is too large. Please try adding a LIMIT clause

Query results over 10 GB cannot be returned to Mode from a database. If your results exceed this limit, add a LIMIT statement to your query to return a smaller set of results.

Sorry, we can't return your data for the following reasons

When your query would return results violating one or more of the following limitations, you should still be able to access your data in Mode's Notebook or export your data to a CSV file:

  • Maximum number of columns: 1,000
  • Size limit for data within any single column: 1MB
  • Size limit for data across columns, within any single row: 2MB
  • Number of digits for DECIMAL columns: 38 total, and/or 12 to the right of the decimal
decorative particle

Get our weekly data newsletter

Work-related distractions for every data enthusiast.