Use SQL in Notebooks
Noteable provides first-class support for SQL, allowing users to easily use SQL syntax within a notebook to query data from various sources. Users can use SQL cells within notebook to:
- Query local CSV files and Python data frames
- Connect to and query external data warehouses
- Use Python variables within SQL cells
- Write back to SQL tables (if database permissions allow)
- Automatically have the results of a SQL query assigned to a python variable
- Use meta commands for database introspection
- And more!
To query a data connection using a SQL cell:
- 1.Create a SQL cell.
- 2.Select the desired connection.
- 3.Run query.
There are multiple ways to create a SQL cell:
Add a new cell
Hotkeys
Cell Menu
To create a SQL cell:
- 1.Use thebutton above or below the selected cell to create a new cell.
- 2.Select SQL from the cell language drop-down menu.
- 3.Select desired connection from the cell's connection drop-down menu.
- 4.Enter and run query.
To create a SQL cell using hotkeys:
- 1.While in command mode, use hotkeys to insert a cell above (a) or below (b) the current cell.
- 2.Select SQL from the cell language drop-down menu.
- 3.Select desired connection from the cell's connection drop-down menu.
- 4.Enter and run query.
To create a SQL cell using the cell menu:
- 1.Select New SQL Cell from the Cell menu.
- 2.Pick a location for the new cell from the resulting menu.
- 3.Select SQL from the cell language drop-down menu.
- 4.Select desired connection from the cell's connection drop-down menu.
- 5.Enter and run query.
By default, the initial database that will be selected is the
CSV Files + Dataframes database, which uses DuckDB. This default data connector allows SQL querying against all CSV files within the project, or any Python dataframes currently in memory. Read more about the CSV Files + Dataframes data connector here.To create a new data connector from the notebook view page, click the database icon in the left sidebar, and click the + button to create a new data connector
That will bring up a pop-up with the list of currently supported data connectors. Select the data connector you want to use and follow the instructions to provide the required information.
Learn more about various data connectors Noteable currently supports here and contact us at [email protected] if the data connector you’d like to use is not currently available.
To use SQL against a different data connector, use the data connector dropdown menu as shown below to select the data source you’d like to query.
There are multiple ways to manage data connections:
Notebook
Resource Page
To manage data connections from the notebook, open the data connections panel by clicking on the database icon (
) in the left sidebar. From this menu, you may:
- Create new connections by selecting thesign in the menu.
- View and manage all of your privately scoped connections.
To edit or delete a connection, hover over the connection to access the (
) menu.
To manage connections from the resources page, Click on the Data Connections link in the main sidebar. This sidebar is visible from all space or project pages.
From the resources page, you may:
- Create new secrets by clicking the +Create a data connection button.
- View and manage all of your privately scoped connections.
To edit or delete a connection, hover over the connection to access the () menu
Noteable's SQL Cells incorporate JinjaSQL, allowing users to reference Python variables within SQL cells and use more advanced logic statements like if and for loops within a SQL cell.
If database permissions allow, Noteable's SQL cells can create new tables using data description language (DDL) as well as add or alter those or preexisting tables using data manipulation language (DML).
The query results of SQL cells are automatically stored as a Python variable made from converting the query results to a pandas DataFrame. This variable can then be used at any point elsewhere in the notebook. By default, a name is provided following the naming convention
sql_df_<n> where `n` is the number of SQL cells used in the notebook. The user can update the variable name at any time, and it will take effect immediately without needing to re-run the cell.SQL Cells now support backslash "meta" commands to let you discover the structure of the database, schemas, tables, or views within.
These commands were inspired by the PostgreSQL `psql` command line tool's meta commands, but are implemented using SQLAlchemy's introspection API, allowing them to be used uniformly across all of our SQL cell supported databases.
The commands can be used directly within SQL cells, and some are even available through the notebook GUI via menu items in the '...' menu next to any data connection in the notebook's left-hand "Data Connections" tray.
Last modified 1mo ago