Datalore 2023.1 Help

SQL cells

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.

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

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

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
Last modified: 10 October 2022