Develop with Notebooks
On this page
Prototyping applications or analyzing via notebooks in SingleStore Helios follows the same general principles as developing with notebooks in general.
To get started, connect to a data source.
Connect to Data Sources
SingleStore Helios supports internal and external data sources.
Connect to a SingleStore Data Source
Once you select a workspace, you can access all of the databases attached to that workspace.
You can specify a default database for your notebook, eliminating the need to specify the database context every time you make a query on that default database.
No default database specified:
Default database specified:
Connecting via SQL
To connect to the default database, you do not need to pass any database context to query that database:
%%sqlSELECT * FROM mytable;
To connect to a non-default database, you will need to specify the database in your query:
%%sqlUSE mydatabase;SELECT * FROM mytable;
You can also use "dot" notation:
%%sqlSELECT * FROM mydatabase.mytable;
Connecting via Python
When connecting via Python to the default database, use the predefined connection string variable, connection_
:
from sqlalchemy import *db_connection = create_engine(connection_url)
You can then use that connection string (db_
above) to connect to SingleStore.
query1 = 'create table people (filename varchar(255), vector blob, shard(filename))'with db_connection.connect() as conn:conn.execute(text(query1))
When connecting to other databases, use the following method where user (connection_
), password (connection_
), host (connection_
), and port (connection_
) are already defined based on the workspace you selected.
from sqlalchemy import *database_name = 'mydatabase'db_connection_str = "singlestoredb://"+connection_user+":"+connection_password+"@"+connection_host+":"+connection_port+"/"+database_namedb_connection = create_engine(db_connection_str)
You can then use that connection string (db_
above) to connect to SingleStore:
query1 = 'create table people (filename varchar(255), vector blob, shard(filename))'with db_connection.connect() as conn:conn.execute(text(query1))
Connect to an External Data Source
SingleStore Helios lets you control which endpoint to access from notebooks to provide secure outbound access to trusted resources.
By default, connections are limited to SingleStore databases; however, you can enable and disable connections to other external endpoints via the allowlist.
Only users with the Organization Owners role can add external connections.
When trying to access an external endpoint from a notebook that is not in the allowlist, you should see the following warning:
To fix this issue, select Add to Firewall and then select Save.
If you do not have access due to your role, your organization owner can manually add or remove the external endpoint using the following steps:
-
In the left navigation, under Develop select Data Studio.
-
Select the Firewall tab in the main window.
-
Select Edit to add new endpoints:
-
In the Edit Allowlist dialog, you can add a Fully Qualified Domain Name (FQDN) or select from a list of suggested FQDNs (for example pypi.
org, github. com, or *. s3. *. amazonaws. com). You can provide wildcard access to an endpoint by using the
*
character.The wildcard character represents 0 or more valid DNS characters, except for '. '. *.
matchessinglestore. com docs.
but notsinglestore. com singlestore.
because it only matches up to the '.com ' character. ex*le.
matches example.com com but not examp-site.
.com For example, to access any AWS S3 endpoints, you can use the following syntax: *.
s3. *. amazonaws. com. -
Select Save.
To remove a connection select the Trash icon next to the connection in the Edit FQDN Allowlist dialog.
Manage Cells
Use the same commands and techniques you use with most other Python based cells in notebooks.
Cell Types
A notebook cell can be one of these types:
-
Markdown: lightweight markup language used to add formatting to plain text.
More information here. -
Python: language supported is Python 3.
-
SQL: language supported is SQL (for SingleStore).
Specify the cell type by selecting a cell and choosing a type from the drop-down menu:
Run a cell
Execute/run a cell via keyboard shortcut (Shift+Return/Enter) or by selecting the run (play button) icon at the left of the notebook cell, or by selecting Run Selected Cell option from the Run menu in the toolbar.
Run Multiple Cells
Execute/run multiple selected cells using the following options from the Run menu in the toolbar:
-
Run Selected Cell and All Below
-
Run All Above and Selected Cell
-
Run All Cells
-
Restart and Run All Cells
Manipulating Cells
When you select a cell, some icons for basic cell manipulation appear:
Action |
Icon |
Description |
---|---|---|
Add a cell |
Add a new cell above or below the selected cell. |
|
Move |
Move the selected cell up or down. |
|
Copy |
Copy the selected cell. |
|
Duplicate |
Duplicate the current cell and place it immediately below the current cell. |
|
Delete |
Delete the selected cell. |
|
Ask SQrL |
An AI-powered co-pilot (SQrL) answers questions. |
For more options, right-click/control-click on a cell to open the cell context menu.
From the context menu, you can perform additional cell functions, such as split and merge.
Keyboard shortcuts exist for most common tasks as well.
Manage Cell Inputs and Output
Right-click/control-click on a cell to open the cell context menu.
An additional option Format SQL Cell is available when the selected cell is using the SQL language (via the %%sql
magic command or the SQL cell).
You can also show/hide cell output by selecting the vertical bar (purple) next to the output.
After selecting the bar:
Use Multiple Languages
You can set the default language for SingleStore Helios notebooks to either SQL or Python3.
You can select the language for a cell via the toggle located above the cell:
Python Cells
Python 3 is the default language for Python cells.
SQL Cells
There are two options to write SQL in SingleStore Notebooks.
USE mydatabase; -- the database to use by defaultSELECT * from mytable mt1INNER JOIN mydatabase2.mytable2 mt2 ON mt1.mycolumnid1 = mt2.mycolumnid2;
The next option is to use the SQL magic from within a Python cell.
%%sql - switch the cell to SQLUSE mydatabase; -- the database to use by defaultSELECT * FROM mytable mt1INNER JOIN mydatabase2.mytable2 mt2 ON mt1.mycolumnid1 = mt2.mycolumnid2;
The results are displayed as a table.
You can also save and use the output from the calculation in an SQL cell (called output_
in this example) in another Python cell with the following options:
Using cell output - Save result to:
Or, using SQL magic:
%%sql output_reviews << -- switch the cell to SQL and specify the output as output_reviewsSELECT * FROM reviews
This output can then be used in other cells, etc.output_
is used with Python as a dataframe:
df = pd.DataFrame(output_reviews)
SQL Line
Use %sql
to enable a single line of SQL in a Python cell:
result = %sql USE s2_dataset_martech; SELECT * FROM offers GROUP BY customer LIMIT 10
Combine SQL and Python in the same cell:
result = %sql USE s2_dataset_martech; SELECT * FROM offers GROUP BY customerdf = pd.DataFrame(result)
Manage Libraries
SingleStore Helios notebooks come with pre-installed libraries, and you can install additional libraries as needed.
Pre-installed Libraries
Run the following command in a Python cell to see the list of pre-installed libraries:
!pip list
Install and Import Libraries
SingleStore supports libraries available from https://pypi.
!pip3 install opendatasets
To update the version of a pre-installed library:
!pip3 install plotly --upgrade
Once a library is installed, you can import library components and add an alias:
import opendatasets as od
Tip
For better clarity, have one cell at the beginning of the notebook with all additional libraries to install and have a second cell listing the libraries to import.
Magic Commands
For a specific cell, to see all the supported magic commands:
%lsmagic
For information about the full list of available magic commands:
%quickref
Some helpful magic commands:
Magic Command |
Description |
---|---|
%history |
View the log of the session activity for the notebook. |
%pinfo <variable> |
Details of the object stored in the specified variable. |
%time |
Show the time of execution of a Python or SQL statement. |
%who |
List all of the currently defined variables within the notebook. |
Useful Shortcuts
There are two modes in a notebook: command and edit.
Command and Edit Mode Shortcuts
Task |
Mac |
Windows |
---|---|---|
Run the current cell, select below |
Shift + Enter |
Shift + Enter |
Run selected cells |
Command + Enter |
Ctrl + Enter |
Run the current cell, insert below |
Option + Enter |
Alt + Enter |
Save and checkpoint |
Command + S |
Ctrl + S |
Command Mode Shortcuts
Task |
Mac |
Windows |
---|---|---|
Enter edit mode |
Enter |
Enter |
Select cell above |
Up |
Home |
Select cell below |
Down |
End |
Extend selected cells above |
Shift + Up |
Shift + Home |
Extend selected cells below |
Shift + Down |
Shift + End |
Insert cell above |
A |
A |
Insert cell below |
B |
B |
Cut selected cells |
X |
X |
Copy selected cells |
C |
C |
Paste cells below |
V |
V |
Paste cells above |
Shift + V |
Shift + V |
Move cell up |
Ctrl + Shift + Up |
Ctrl + Shift + Home |
Move cell down |
Ctrl + Shift + Down |
Ctrl + Shift + End |
Split cell |
Ctrl + Shift + Minus (-) |
Ctrl + Shift + Minus (-) |
Delete selected cells |
D, D (press twice) |
D, D (press twice) |
Undo cell operation |
Z |
Z |
Redo cell operation |
Shift + Z |
Shift + Z |
Merge selected cells |
Shift + M |
Shift + M |
Merge cell above |
Ctrl + Delete |
Ctrl + Backspace |
Merge cell below |
Ctrl + Shift + M |
Ctrl + Shift + M |
Save and checkpoint |
S |
S |
Change the cell type to Code |
Y |
Y |
Change the cell type to Markdown |
M |
M |
Scroll notebook up |
Shift + Space |
Shift + Space |
Scroll notebook down |
Space |
Space |
Find |
Command + F |
Ctrl + F |
Find next |
Command + G |
Ctrl + G |
Find previous |
Shift + Command + G |
Shift + Ctrl + G |
Show line numbers |
Shift + L |
Shift + L |
Render side-by-side |
Shift + R |
Shift + R |
Edit Mode Shortcuts
Task |
Mac |
Windows |
---|---|---|
Go to command mode |
Esc |
Esc |
Select all cells |
Command + A |
Ctrl + A |
Undo |
Command + Z |
Ctrl + Z |
Redo |
Shift + Command + Z |
Shift + Ctrl + Z |
Go to cell start |
Command + Up |
Ctrl + Home |
Go to cell end |
Command + Down |
Ctrl + End |
Go one word left |
Command + Left |
Ctrl + Left |
Go one word right |
Command + Right |
Ctrl + Right |
Last modified: November 26, 2024