New

Getting Started with Fusion SQL

Notebook

SingleStore Notebooks

Getting Started with Fusion SQL

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]:

!pip uninstall -y singlestoredb
!pip install singlestoredb

In [2]:

commands = %sql SHOW FUSION COMMANDS
for cmd in commands:
    print(*cmd, '\n')

The SHOW FUSION COMMANDS also has a LIKE option that can be used to filter the displayed commands.

In [3]:

commands = %sql SHOW FUSION COMMANDS LIKE '%stage%'
for cmd in commands:
    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 [4]:

us_regions = %sql SHOW REGIONS LIKE '%US%'
us_regions

Let's use the random package to choose a US region for us.

In [5]:

import random

region_id = random.choice(us_regions).ID
region_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 [6]:

import secrets

wsg_name = 'Fusion Notebook'
password = secrets.token_urlsafe(20) + '-x&'

In [7]:

%%sql
CREATE WORKSPACE GROUP '{{ wsg_name }}' IN REGION ID '{{ region_id }}'
       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 [8]:

%%sql
SHOW 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 [9]:

%%sql
CREATE WORKSPACE 'workspace-1' IN GROUP '{{ wsg_name }}' WITH SIZE 'S-00';
CREATE 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 [10]:

def wait_on_attr(cmd: str, **kwargs) -> None:
    """Loop until the given attribute of every result row matches."""
    import time
    import singlestoredb as s2

    name, value = list(kwargs.items())[0]

    n_tries = 20
    while n_tries > 0:
        workspaces = %sql {{ cmd }}
        active = [x for x in workspaces if getattr(x, name) == value]
        if len(active) == len(workspaces):
            n_tries = 1
            break
        time.sleep(20)
        n_tries -= 1

    if n_tries == 0:
        raise RuntimeError('waiting for workspaces timed out')


# Wait for all workspaces to be active
wait_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 [11]:

%%sql
SHOW 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 [12]:

%%sql
SUSPEND WORKSPACE 'workspace-1' IN GROUP '{{ wsg_name }}'

The workspace should have a state of 'SUSPENDED' shortly after running the above command.

In [13]:

%%sql
SHOW WORKSPACES IN GROUP '{{ wsg_name}}'

To resume the workspace, you use the RESUME WORKSPACE command.

In [14]:

%%sql
RESUME WORKSPACE 'workspace-1' IN GROUP '{{ wsg_name }}' WAIT ON RESUMED

Display the information about the workspaces again.

In [15]:

workspaces = %sql SHOW WORKSPACES IN GROUP '{{ wsg_name}}' EXTENDED
workspaces

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 [16]:

import singlestoredb as s2

with s2.connect(f'admin:{ password }@{ workspaces[0].Endpoint }:3306') as conn:
    with conn.cursor() as cur:
        cur.execute('show databases')
        for row in cur:
            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 [17]:

%%sql
DROP 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 [18]:

%%sql
SHOW 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 [19]:

%%sql
DROP WORKSPACE GROUP '{{ wsg_name }}' FORCE

In [20]:

%%sql
SHOW 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 [21]:

try:
    %sql SHOW WORKSPACES IN GROUP '{{ wsg_name }}'
except KeyError:
    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

License

This Notebook has been released under the Apache 2.0 open source license.

Getting Started with Fusion SQL

Notebook

Getting Started with Fusion SQL

Displaying available Fusion SQL commands

Working with workspace groups

Creating workspaces

Suspending and resuming workspaces

Accessing the database endpoint of a workspace

Terminating workspaces and workspace groups

Conclusion

Details

Tags

License