SQL cells

Last modified: 10 October 2022

Use SQL cells to query attached databases and retrieve data as tabular data objects. SQL cells are the recommended method of work with SQL when activating reactive mode.

Query a database in an SQL cell

  1. Create an SQL cell:

    • Go to Tools | Attached data and click New SQL cell for the attached database that you want to use.

      Opening an SQL cell from Attached data

    • Hover over the bottom border of a cell and select More cell types | SQL cell. You will be prompted to select the required database if you you have more than one database attached.

      Selecting a database for querying

  2. Enter an SQL statement.

    note

    The cell provides code completion based on the introspected database schema.

  3. Run the query (Run icon or shortcut). The result set will be shown in the output and saved to a tabular data objects (for example, DataFrame).

    tip

    By clicking the Stop button or interrupting/restarting the kernel, you explicitly stop querying the database.

  4. (Optional) To rename the data object, click the object name next to RESULT SAVED TO on the SQL toolbar.

tip

To get a visual representation of the retrieved data, switch to the Visualize tab. Find more details in Automatic plotting.

SQL cells: example

Below is an example of two SQL cells used to work with a Snowflake database.

Quering a Snowflake database

Parameterized SQL queries

You can use Python variables in your SQL queries in Datalore. Such queries can be reused repeatedly with different values, which helps make your reports more interactive.

Supported variable types are: strings, numbers, booleans, and lists. Make sure you place your variable inside {} brackets.

Parameterized SQL query example

In the image below, you can see two cells:

  • Dropdown interactive control cell using the method variable.

  • SQL cell where the value in payment column equals the method variable value selected from the dropdown list.

Using a parameterized SQL query

SQL cells in trial mode

For Community plan users, SQL cells are available in trial mode for a period of 30 days. Within the trial period, you can create and attach an unlimited number of database connections and use an unlimited number of SQL cells to query them.

You can start the trial in one of the following ways:

  • Click Start trial in the notification window that you can see when you choose to add an SQL cell.

    Trial notification

  • Click Start trial on the banner that you can see when you open Attached data.

    Database and SQL notification banner

In the Attached data pane, you are notified when your trial period expires.

Trial period notification

note

SQL cell usage restrictions for Community plan users

  • After the trial period expires, Community plan users can keep working on notebooks containing SQL cells; but to keep using the feature, it is required to subscribe to the Professional plan.

  • Community plan users cannot execute SQL cells in notebooks shared by Professional plan users, unless the feature is available in trial mode.