Develop with Notebooks

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))'
db_connection.execute(query1)

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)

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

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:

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

A notebook cell can be one of these types:

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

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.

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

Action	Icon	Description
Duplicate		Duplicate the current cell and place it immediately below the current cell.
Move		Move the selected cell up or down.
Add a cell		Add a new cell above or below the selected cell.
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.

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).

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

After clicking the bar:

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

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) 

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)

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

!pip list

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