Skip to main content

Develop with Notebooks

Prototyping applications or analyzing via notebooks in SingleStoreDB Cloud follows the same general principles as developing with notebooks in general.

To get started, connect to a datasource.

Connect to Datasources

SingleStoreDB Cloud supports internal and external datasources. Internal datasources are databases that exist within your workspace. An external datasource could be an AWS S3 bucket, for example.

Connect to a SingleStoreDB Datasource

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:

notebook_defaultdbunset.png

Default database specified:

notebook_defaultdbset.png

Connecting via SQL

To connect to the default database, you do not need pass any database context to query that database:

%%sql
SELECT * FROM mytable;

To connect to a non-default database, you will need to specify the database in your query:

%%sql
USE mydatabase;
SELECT * FROM mytable;

You can also use "dot" notation:

%%sql
SELECT * FROM mydatabase.mytable;

Connecting via Python

When connecting via Python to the default database, use the predefined connection string variable, connection_url:

from sqlalchemy import *
db_connection = create_engine(connection_url)

You can then use that connection string (db_connection above) to connect to SingleStoreDB. Here's an example of creating a table using that connection string:

query1 = 'create table people (filename varchar(255), vector blob, shard(filename))'
db_connection.execute(query1)

When connecting to other databases, use the following method where user (connection_user), password (connection_password), host (connection_host), and port (connection_port) are already defined based on the workspace you selected.

from sqlalchemy import *
database_name = 'mydatabase'
db_connection_str = "mysql+pymysql://"+connection_user+":"+connection_password+"@"+connection_host+":"+connection_port+"/"+database_name+"?ssl_cipher=HIGH"
db_connection = create_engine(db_connection_str)

You can then use that connection string (db_connection above) to connect to SingleStoreDB:

query1 = 'create table people (filename varchar(255), vector blob, shard(filename))'
db_connection.execute(query1)

Connect to an External Datasource

SingleStoreDB Cloud lets you control which endpoint to access from notebooks to provide secure outbound access to trusted resources. 

By default, connections are limited to SingleStoreDB databases; however, you can enable and disable connections to other external endpoints via the allowlist.

To add or remove endpoints from the allowlist:

notebooks_edit_fqdn3.png
  1. In the left navigation, select Notebooks.

  2. Select the Firewall tab in the main window.

  3. Select Edit to add new endpoints:

    The Edit FQDN Allowlist dialog with Add FQDN button and Add Suggested FQDN button, with Cancel and Save buttons.
  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. 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.

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.

  • Code: by default the code supported is Python code. By using the SQL magic you can change the language to SQL.

  • Raw: can be used to render different code formats into HTML or LaTeX by Sphinx.

Specify the cell type by selecting a cell and the choosing a type in the drop down menu:

Toolbar with "Markdown" pulldown option selected and circled.

Run a cell

Execute or run a cell, or multiple selected cells, via keyboard shortcut or by selecting the run (play button) icon in the toolbar at the top of the notebook window.

Toolbar with "Play" icon circled. Hover text: "Run the selected cells and advance."

Manipulating Cells

When you select a cell, some icons for basic cell manipulation appear:

Action

Icon

Description

Duplicate

Duplicate Icon

Duplicate the current cell and place it immediately below the current cell.

Move

Arrow up and down icons.

Move the selected cell up or down.

Add a cell

Icons for adding a cell above or below the current cell.

Add a new cell above or below the selected cell.

Delete

Trash Can icon for Delete.

Delete the selected cell.

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.

notebook_context2.png

Manage Cell Inputs and Output

Right-click/control-click on a cell to open the cell context menu.

notebook_output2.png

An additional option Format SQL Cell is available when the selected cell is using the SQL language (via the %%sql magic command).

You can also show/hide cell output by clicking on the vertical bar (purple) next to the output. Before:

Example of a vertical "bar" alongside a cell's output, expanded below the cell.

After clicking the bar:

An example of the truncated vertical "bar" after the cell output is collapsed.

Use Multiple Languages

The default language for SingleStoreDB Cloud notebooks is Python 3. To change a cell's language to SQL, use the SQL magic command:

%%sql

Python Cells

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

SQL Cells

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, etc.:

%%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 use the output from the calculation, here it's called result1, in another cell with the following command:

%%sql result1 <<- switch the cell to SQL and specify the output as result1

use mydatabase; -- the database to use by default
select * from mytable mt1
inner join mydatabase2.mytable2 mt2 on mt1.mycolumnid1 = mt2.mycolumnid2;

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

df = pd.DataFrame(result1) 

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 customer
df = pd.DataFrame(result)

Manage Libraries

SingleStoreDB Cloud notebooks come with preinstalled libraries, and you can install additional libraries as needed.

Preinstalled Libraries

Run the following command in a Python cell to see the list of preinstalled libraries:

!pip list

Install and Import Libraries

SingleStoreDB 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 preinstalled 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. You can then collapse the two cells to remove clutter.

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.

%timeit

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 mode is activated by pressing ESC. Edit mode is activated by pressing Enter.

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

Up

Select cell below

Down

Down

Extend selected cells above

Shift + Up

Shift + Up

Extend selected cells below

Shift + Down

Shift + Down

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

Delete selected cells

D, D (press twice)

D, D (press twice)

Undo cell deletion

Z

Z

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

Edit Mode Shortcuts

Task

Mac

Windows

Go to command mode

Esc

Esc

Select all

Command + A

Ctrl + A

Undo

Command + Z

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