Getting Started with Fusion SQL

In this notebook, we introduce Fusion SQL. Fusion SQL is a set of SQL statements that can be used to manage clusters, files in cluster Stage, 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.

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

Let's try a workflow that goes through the entire process of creating clusters, connecting to one, and terminating them.

Working with clusters

In this example, we will create two clusters, demonstrate how to suspend and resume one of them, and then terminate them all from SQL!

A cluster carries both its compute settings and its deployment-wide settings, such as the firewall, on the one object, and every command that operates on a cluster names it directly.

Shared-tier deployments have their own set of commands — SHOW STARTER CLUSTERS, CREATE STARTER CLUSTER and DROP STARTER CLUSTER — which are not covered here.

Looking above at our list of printed commands, we see that the CREATE CLUSTER command has the following options:

CREATE CLUSTER [ IF NOT EXISTS ] '<cluster-name>'
    IN REGION '<region-name>'
    [ USING PROVIDER '<provider>' ]
    [ IN PROJECT { ID '<project-id>' | '<project-name>' } ]
    [ WITH SIZE '<size>' ]
    [ USING SCALE FACTOR <number> ]
    [ WITH FIREWALL RANGES '<ip-range>',... ]
    [ ALLOW ALL TRAFFIC ]
    [ EXPIRES AT '<iso-datetime-or-interval>' ]
    [ WAIT ON ACTIVE ];

We need a region to create a cluster in, and there is a Fusion command that gives us all of the region information. We can use this to get a region from the US by using the LIKE parameter.

SHOW CLUSTER REGIONS [ LIKE '<pattern>' ] [ ORDER BY '<key>' [ ASC | DESC ],... ] [ LIMIT <integer> ];

A region is identified by its cloud provider and its name. Name is the display name, for example US East 1 (N. Virginia), and RegionName is the cloud provider's own name for it, for example us-east-1. Either may be given to IN REGION, and USING PROVIDER picks between providers that offer a region under the same name.

Let's use the random package to choose a US region for us. Since a region name can be ambiguous across cloud providers, we keep the provider as well and pass both to CREATE CLUSTER.

Creating clusters

Now that we have a region, we can create our clusters. Let's create two of different sizes and open the firewall so they can be accessed from anywhere. The size and the firewall are both clauses of CREATE CLUSTER, along with the scale factor, the expiration and the project, as shown in the syntax above.

Two things to know before running this. A cluster name is restricted to 1-32 characters of lowercase letters, digits and hyphens, and must start and end with a letter or digit. And the admin password is generated by the API and reported only at the moment the cluster is created — CREATE CLUSTER returns it in a row, so we capture that row. A cluster created without capturing it has no reachable admin user.

If you are in the SingleStore Cloud portal, you should see the clusters displayed in a few seconds. You can also use the SHOW CLUSTERS command to list them.

Waiting for the clusters to become active

The clusters 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 CLUSTER, but that would cause the two creations to run one after the other. We are using an external loop so that the two commands above can run in parallel.

The loop matches on the cluster IDs we captured rather than on the LIKE pattern alone. Terminating a cluster does not remove it from the listing, so a leftover TERMINATED cluster from an earlier run would otherwise keep the loop from ever finishing.

We can now display the information about the clusters using the SHOW CLUSTERS command. The EXTENDED clause adds the endpoint, the cloud provider, the firewall ranges and the project the cluster belongs to.

Suspending and resuming clusters

It is possible to suspend and resume clusters from Fusion SQL as well.

SUSPEND CLUSTER { ID '<cluster-id>' | '<cluster-name>' } [ WAIT ON SUSPENDED ];

RESUME CLUSTER { ID '<cluster-id>' | '<cluster-name>' } [ DISABLE AUTO SUSPEND ] [ WAIT ON RESUMED ];

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

To resume the cluster, you use the RESUME CLUSTER command.

Display the information about the clusters again.

Accessing the database endpoint of a cluster

As you saw above, we have access to the database endpoint in the cluster information. Together with the admin password that CREATE CLUSTER reported, we can use that to create a connection to the cluster for database operations.

The connection parameters are passed as keyword arguments rather than interpolated into a connection string. The generated admin password is not URL-safe — it can contain ?, /, @, #, : and % — and any of those change how a URL is read: a ? starts the query string, so everything after it drops out of the host, and a % starts an escape sequence. Keyword arguments are taken literally, so the password arrives as issued.

Terminating clusters

You can terminate clusters from Fusion SQL commands as well.

DROP CLUSTER [ IF EXISTS ] { ID '<cluster-id>' | '<cluster-name>' } [ WAIT ON TERMINATED ];

Dropping the cluster is the whole of the cleanup. All databases attached to the cluster are detached when it is deleted, and nothing is left behind to remove afterwards.

Let's drop fusion-cluster-2 and leave fusion-cluster-1 in place.

The above operation may take a few seconds. Once it has completed, fusion-cluster-2 will report a state of TERMINATED. Note that a terminated cluster is not removed from the listing, so it keeps showing up in SHOW CLUSTERS with that state.

Now let's remove the remaining cluster, this time waiting for the termination to finish before continuing.

Naming a cluster that does not exist raises a KeyError. Add the IF EXISTS clause when you would rather the command do nothing.

Conclusion

We have covered the Fusion SQL commands for creating and terminating clusters, and we demonstrated how to suspend and resume one. 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 SingleStore Cloud.