SQL blocks

Getting started with SQL blocks

To make it easier to query databases, Deepnote includes so-called "SQL blocks". After connecting one of the database integrations to Deepnote (PostgreSQL, Redshift, BigQuery, or Snowflake), you can create SQL blocks and begin writing SQL queries. When you run an SQL query, Deepnote displays a the results in a Pandas DataFrame.

SQL blocks and Pandas DataFrames

• Deepnote saves the result into a Pandas DataFrame which you can name. In the above example, it's df_1. You can use this variable for further processing in normal Python code blocks below.
• You can even issue SQL queries against a DataFrame or CSV file. Click "+ Block" and choose "DataFrame SQL" (shown below). You can then query a DataFrame or CSV file as follows:

SELECT * 
FROM df

SELECT * 
FROM 'path/to/my_data.csv'

SQL blocks can also be created by drag and dropping a CSV file from your filesystem (this also works for already uploaded files in the right sidebar). By executing that SQL block, the content of the CSV file will be loaded into a dataframe.

SQL autocomplete

When writing SQL, the built-in Intellisense will offer relevant suggestions for your cursor position. This includes entities in your schema such as databases, tables or columns but also aliases or CTEs that you may have defined in your query. The autocomplete will open automatically as you type. In addition to that, you can trigger it manually by using one of these keyboard shortcuts Control + Space, Option + Space or ⌘ + I

Using Python and SQL

Deepnote uses jinjasql templating which allows you to pass variables, functions, and control structures (e.g., if statements and for loops) into your SQL queries.

• To inject a Python variable inside your SQL query use the{{ variable_name }} syntax. For example:

SELECT date, name
FROM fh-bigquery.weather_gsod.all
WHERE name = {{ station_name  }}
LIMIT 10

• Passing lists or tuples into your SQL queries requires the inclause keyword from jinjasql. As you can see below, we use a similar syntax as before but with this new keyword preceded by the | symbol.

SELECT date, name
FROM fh-bigquery.weather_gsod.all
WHERE name in {{ station_list | inclause}}
ORDER BY date DESC

• To inject column names and table names, use the sqlsafe keyword as follows:

SELECT *
FROM {{ table_name | sqlsafe }}

• A common use-case is searching for a wildcard pattern containing (e.g., the % character to represent optional substrings). To combine this with a variable value, use the following syntax:

SELECT *
FROM users
WHERE name LIKE {{ '%' + first_name + '%' }}

• You can also use more advanced templating features like {% if condition } %{ endif } , conditional blocks, or anything else that's supported by jinjasql. For example, the following block loops through a Python list (column_names) to contruct the desired SQL fields.

SELECT date, name,
{% raw %}
{% for col in column_names %}
    {% if not loop.last %}
        {{ col | sqlsafe }},
    {% else %}
        {{ col | sqlsafe }}
    {% endif %}
{% endfor %}
{% endraw %}
FROM fh-bigquery.weather_gsod.all
WHERE date > '2015-12-31'  and name = {{ station_name }}
ORDER BY date DESC
LIMIT 5 

For more information, please check out the jinjasql project.