Develop with Notebooks

Connect to a SingleStore Data Source

Once you select a workspace, you can access all of the databases attached to that workspace. You cannot connect to databases that are not attached to the workspace you are using.

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. To set a default database, select a database from the drop-down menu.

No default database specified:

Default database specified:

%%sql
SELECT * FROM mytable;

%%sql
USE mydatabase;
SELECT * FROM mytable;

%%sql
SELECT * FROM mydatabase.mytable;

from sqlalchemy import *
db_connection = create_engine(connection_url)

query1 = 'create table people (filename varchar(255), vector blob, shard(filename))'
with db_connection.connect() as conn:
    conn.execute(text(query1))

from sqlalchemy import *
database_name = 'mydatabase'
db_connection_str = "singlestoredb://"+connection_user+":"+connection_password+"@"+connection_host+":"+connection_port+"/"+database_name
db_connection = create_engine(db_connection_str)

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. The external connection is automatically added into the allowlist. The notebook cell will be executed.

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:

1. In the left navigation, under Develop select Data Studio.

2. Select the Firewall tab in the main window.

3. Select Edit to add new endpoints:

   

4. 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 '.'.

   *.singlestore.com matches docs.singlestore.com but not singlestore.com because it only matches up to the '.' character. ex*le.com matches example.com but not examp-site.com.

   For example, to access any AWS S3 endpoints, you can use the following syntax:  *.s3.*.amazonaws.com.

5. Select Save.

To remove a connection select the Trash icon next to the connection in the Edit FQDN Allowlist dialog.

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. Before:

After selecting the bar:

Python Cells

Python 3 is the default language for Python cells. See the Python documentation for more information about using Python.

SQL Cells

There are two options to write SQL in SingleStore Notebooks. The first involves selecting a cell type to be SQL. The following example shows how to specify a default database (mydatabase) and the syntax used to connect to another database (mydatabase2) for use in joins with an SQL cell:

USE mydatabase; -- the database to use by default
SELECT * from mytable mt1
INNER JOIN mydatabase2.mytable2 mt2 ON mt1.mycolumnid1 = mt2.mycolumnid2;

The next option is to use the SQL magic from within a Python cell. This gives you the ability to write SQL and Python code in a single block:

%%sql - switch the cell to SQL
USE mydatabase; -- the database to use by default
SELECT * FROM mytable mt1
INNER 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_reviews 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_reviews

SELECT * FROM reviews

This output can then be used in other cells, etc. For example, in this example, output_reviews is used with Python as a dataframe:

df = pd.DataFrame(output_reviews) 

result = %sql USE s2_dataset_martech; SELECT * FROM offers GROUP BY customer LIMIT 10

result = %sql USE s2_dataset_martech; SELECT * FROM offers GROUP BY customer
df = pd.DataFrame(result)

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.org/ . This example shows how to install a library from the Kaggle open dataset.

!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

Command and Edit Mode Shortcuts

Command Mode Shortcuts

Edit Mode Shortcuts