Use SQL cells to query the following data sources:
Attached databases
Dataframes
Attached .csv files
tip
SQL cells are the recommended method of work with SQL when activating reactive mode.
Query a database in an SQL cell
Add an SQL cell:
Go to Tools | Attached data and click Connect SQL cell for the attached database that you want to query.
Hover over the bottom border of a cell and select More | SQL. If there are several attached databases, in the cell, click Select database and select the required data source.
Enter an SQL statement.
note
The cell provides code completion based on the introspected database schema.
Run the query (Run icon or Ctrl,0+,Enter shortcut). The result set will be shown in the output and saved to a DataFrame. The resulting DataFrame name is shown in the cell toolbar.
tip
By clicking the Stop button or interrupting/restarting the kernel, you explicitly stop querying the database.
(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.
Query a DataFrame or .csv file in an SQL cell
Hover over the bottom border of a cell and select More | SQL.
In the added SQL cell, click Select database and select Dataframes.
Enter an SQL statement.
note
If you query a .csv file, provide only the file name if it's a notebook attached file or a full path if it's a workspace file.
Run the query. The result set will be shown in the output and saved to a DataFrame. The resulting DataFrame name is shown in the cell toolbar.
Query a DataFrame or .csv file in an SQL cell
Hover over the bottom border of a cell and select More | SQL.
In the added SQL cell, click Select database and select Dataframes.
Enter an SQL statement. Run the query. The result set will be shown in the output and saved to a DataFrame. The resulting DataFrame name is shown in the cell toolbar.
tip
By clicking the Stop button or interrupting/restarting the kernel, you explicitly stop querying the database.
(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.
note
OAuth authentication (Google BigQuery or Snowflake)
Notebook collaborators may be required to provide their credentials to access the database in an SQL cell if OAuth authentication was used for a connection.
SQL cell examples
Querying a Snowflake database
Querying a DataFrame
Querying a .csv file
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.
Table parametrisation for SQL cells
Starting from this version (2023.5), Datalore allows for table paramterization by supporting unsafe parameters for SQL cell statements. The syntax is as follows:
select*from {table_name | unsafe} limit {n}
In the code example above, the {table_name | unsafe} part will assume the value of the table_name variable.
String variables for unsafe parameters
To pass a string variable to an unsafe parameter, use quote escaping quotes. See the examples below:
Unsafe parameter defined in a code cell
product ="'Product'"
Unsafe parameter referenced in an SQL cell
SELECT*from releases
where jb_product in({product | unsafe})
Resulting SQL query
SELECT*from releases
where jb_product in('Product')
