SingleStore Notebooks
New
Getting Started with Fusion SQL
Notebook
In this notebook, we introduce Fusion SQL. Fusion SQL are SQL statements that can be used to manage workspace groups, workspaces, files in workspace stages, and other resources that could previously only be managed in the portal user interface or the Management REST API.
Displaying available Fusion SQL commands
We can use the SHOW FUSION COMMANDS statement to get all of the available commands.
In [1]:
1commands = %sql SHOW FUSION COMMANDS2for cmd in commands:3 print(*cmd, '\n')
The SHOW FUSION COMMANDS also has a LIKE option that can be used to filter the displayed commands.
In [2]:
1commands = %sql SHOW FUSION COMMANDS LIKE '%stage%'2for cmd in commands:3 print(*cmd, '\n')
Let's try a workflow that goes through the entire process of creating a workspace group, workspace, and Stage files.
Working with workspace groups
In this example, we will create a new workspace group, add workspaces, and demonstrate how to suspend and resume a workspace. We will then terminate the workspaces and workspace groups all from SQL!
Looking above at our list of printed commands, we see that the CREATE WORKSPACE GROUP
command has the following options:
CREATE WORKSPACE GROUP [ IF NOT EXISTS ] '<group-name>'
IN REGION { ID '<region-id>' | '<region-name>' }
[ WITH PASSWORD '<password>' ]
[ EXPIRES AT '<iso-datetime-or-interval>' ]
[ WITH FIREWALL RANGES '<ip-range>',... ];
We need a region ID or name to create a workspace group, but luckily there is a Fusion command
that can give us all of the region information. We can use this to get a region from the US
by using the LIKE parameter.
SHOW REGIONS [ LIKE '<pattern>' ] [ ORDER BY '<key>' [ ASC | DESC ],... ] [ LIMIT <integer> ];
In [3]:
1us_regions = %sql SHOW REGIONS LIKE '%US%'2us_regions
Let's use the random package to choose a US region for us.
In [4]:
1import random2 3region_id = random.choice(us_regions).ID4region_id
Now that we have a region ID, we can create our workspace. We'll open the firewall so it can be accessed from anywhere and set a password.
In [5]:
1import secrets2 3wsg_name = 'Fusion Notebook'4password = secrets.token_urlsafe(20) + '-x&'
In [6]:
1%%sql2CREATE WORKSPACE GROUP '{{ wsg_name }}' IN REGION ID '{{ region_id }}'3 WITH FIREWALL RANGES '0.0.0.0/0' WITH PASSWORD '{{ password }}'
If you are in the SingleStore Cloud portal, you should see the workpace group displayed in a few seconds.
You can also use the SHOW WORKSPACE GROUPS command to list them.
In [7]:
1%%sql2SHOW WORKSPACE GROUPS LIKE 'Fusion%'
Creating workspaces
Now that we have a workspace group, we can create workspaces within it. Let's create two workspaces of different sizes. Here is the syntax for that operation.
CREATE WORKSPACE [ IF NOT EXISTS ] '<workspace-name>' [ IN GROUP { ID '<group-id>' | '<group-name>' } ]
WITH SIZE '<size>' [ WAIT ON ACTIVE ];
In [8]:
1%%sql2CREATE WORKSPACE 'workspace-1' IN GROUP '{{ wsg_name }}' WITH SIZE 'S-00';3CREATE WORKSPACE 'workspace-2' IN GROUP '{{ wsg_name }}' WITH SIZE 'S-1';
The workspaces will take some time to become available. We can write a small wait loop to
block until they are both ready. You could use the WAIT ON ACTIVE option for CREATE WORKSPACE,
but that would cause them to run sequentially. We are using an external loop so that the
two commands above can run in parallel.
In [9]:
1def wait_on_attr(cmd: str, **kwargs) -> None:2 """Loop until the given attribute of every result row matches."""3 import time4 import singlestoredb as s25 6 name, value = list(kwargs.items())[0]7 8 n_tries = 209 while n_tries > 0:10 workspaces = %sql {{ cmd }}11 active = [x for x in workspaces if getattr(x, name) == value]12 if len(active) == len(workspaces):13 n_tries = 114 break15 time.sleep(20)16 n_tries -= 117 18 if n_tries == 0:19 raise RuntimeError('waiting for workspaces timed out')20 21 22# Wait for all workspaces to be active23wait_on_attr(f'SHOW WORKSPACES IN GROUP "{ wsg_name }"', State='ACTIVE')
We can now display the information about the workspaces using the SHOW WORKSPACES command.
In [10]:
1%%sql2SHOW WORKSPACES IN GROUP '{{ wsg_name }}' ORDER BY Name EXTENDED
Suspending and resuming workspaces
It is possible to suspend and resume workspaces from Fusion SQL as well.
RESUME WORKSPACE { ID '<workspace-id>' | '<workspace-name>' } [ IN GROUP { ID '<group-id>' | '<group-name>' } ] [ WAIT ON RESUMED ];
SUSPEND WORKSPACE { ID '<workspace-id>' | '<workspace-name>' } [ IN GROUP { ID '<group-id>' | '<group-name>' } ] [ WAIT ON SUSPENDED ];
In [11]:
1%%sql2SUSPEND WORKSPACE 'workspace-1' IN GROUP '{{ wsg_name }}'
The workspace should have a state of 'SUSPENDED' shortly after running the above command.
In [12]:
1%%sql2SHOW WORKSPACES IN GROUP '{{ wsg_name}}'
To resume the workspace, you use the RESUME WORKSPACE command.
In [13]:
1%%sql2RESUME WORKSPACE 'workspace-1' IN GROUP '{{ wsg_name }}' WAIT ON RESUMED
Display the information about the workspaces again.
In [14]:
1workspaces = %sql SHOW WORKSPACES IN GROUP '{{ wsg_name}}' EXTENDED2workspaces
Accessing the database endpoint of a workspace
As you saw above, we have access to the database endpoint in the workspace information. We can use that to create a connection to that workspace for database operations.
In [15]:
1import singlestoredb as s22 3with s2.connect(f'admin:{ password }@{ workspaces[0].Endpoint }:3306') as conn:4 with conn.cursor() as cur:5 cur.execute('show databases')6 for row in cur:7 print(*row)
Terminating workspaces and workspace groups
You can terminate workspaces and workspace groups from Fusion SQL commands as well.
DROP WORKSPACE [ IF EXISTS ] { ID '<workspace-id>' | '<workspace-name>' } [ IN GROUP { ID '<group-id>' | '<group-name>' } ] [ WAIT ON TERMINATED ];
DROP WORKSPACE GROUP [ IF EXISTS ] { ID '<group-id>' | '<group-name>' } [ WAIT ON TERMINATED ] [ FORCE ];
Let's drop workspace-2 and leave workspace-1 in place.
In [16]:
1%%sql2DROP WORKSPACE 'workspace-2' IN GROUP '{{ wsg_name }}'
The above operation may take a few seconds. Once it has completed, the following output will show just one workspace remaining.
In [17]:
1%%sql2SHOW WORKSPACES IN GROUP '{{ wsg_name }}'
It is possible to terminate a workspace group even if it has workspaces in it
by using the FORCE option. Let's remove our workspace group with workspace-1
still in it.
In [18]:
1%%sql2DROP WORKSPACE GROUP '{{ wsg_name }}' FORCE
In [19]:
1%%sql2SHOW WORKSPACE GROUPS LIKE 'Fusion%'
We can attempt to list the workspaces in the group again, but this time you will get a KeyError saying that the workspace group is not found.
In [20]:
1try:2 %sql SHOW WORKSPACES IN GROUP '{{ wsg_name }}'3except KeyError:4 print('no workspace group was found')
Conclusion
We have covered the Fusion SQL commands for creating and terminating both workspace groups and workspaces. We also demonstrated how to suspend and resume workspaces. Fusion SQL can also manage your Stage files. That topic is covered in another example notebook, and more Fusion SQL commands will be added as features are added to SingleStoreDB Cloud.
Details
About this Template
Fusion SQL allows you to manage your SingleStoreDB Cloud resources such as workspace groups, workspaces, and Stage files all from SQL.
This Notebook can be run in Standard and Enterprise deployments.
Tags
starterfusionpython
License
This Notebook has been released under the Apache 2.0 open source license.
See Notebook in action
Launch this notebook in SingleStore and start executing queries instantly.