SingleStore Notebooks
New
Managing Stage files with Fusion SQL
Notebook
Fusion SQL can be used to manage your clusters, but it can also be used to upload, download, and manage files in your cluster or starter cluster Stage. We'll show you how to work with files in Stage in this notebook.
Displaying the Stage Fusion SQL commands
The SHOW FUSION COMMANDS displays the commands that are handled by the Fusion
engine. You can use the LIKE to filter the commands.
In [1]:
1commands = %sql SHOW FUSION COMMANDS LIKE '%stage%'2for cmd in commands:3 print(*cmd, '\n')
Creating a cluster
We'll start by creating a cluster, whose Stage we will work with for the rest of the notebook.
We can get a region in the US by using the SHOW CLUSTER REGIONS command and the random
package. A region is identified by its name and cloud provider, so we keep both and pass them
to IN REGION and USING PROVIDER.
In [2]:
1import random2 3us_regions = %sql SHOW CLUSTER REGIONS LIKE '%us%'4 5region = random.choice(us_regions)6 7region_name = region.Name8provider = region.Provider9 10region_name, provider
In [3]:
1# Cluster names are limited to 1-32 characters of lowercase letters, digits2# and hyphens, and must start and end with a letter or digit.3cluster_name = 'fusion-notebook'
In [4]:
1%%sql2CREATE CLUSTER '{{ cluster_name }}'3 IN REGION '{{ region_name }}' USING PROVIDER '{{ provider }}'4 WITH SIZE 'S-00' ALLOW ALL TRAFFIC WAIT ON ACTIVE
The row returned above includes the admin password for the new cluster. The API generates it
and reports it only at creation time, so if you intend to connect to the cluster as admin
later on, keep it.
Uploading and downloading Stage files
Uploading and downloading files to your Stage is easy with Fusion SQL. The commands are shown below.
DOWNLOAD STAGE FILE '<stage-path>' [ IN { ID '<deployment-id>' | '<deployment-name>' } ] [ TO '<local-path>' ]
[ OVERWRITE ] [ ENCODING '<encoding>' ];
UPLOAD FILE TO STAGE '<stage-path>' [ IN { ID '<deployment-id>' | '<deployment-name>' } ] FROM '<local-path>' [ OVERWRITE ];
The IN clause names a deployment, which is either a cluster or a starter cluster. Left off,
the commands work on the Stage of the deployment the notebook is attached to.
First we'll create a data file locally that we can work with.
In [5]:
1%%writefile mydata.csv2name,age,height3Sue,27,654Joe,32,705Max,44,696Ann,33,64
We can now upload our data file to our cluster Stage.
In [6]:
1%%sql2UPLOAD FILE TO STAGE 'stats.csv' IN '{{ cluster_name }}' FROM 'mydata.csv'
We can list the files in a Stage with the SHOW STAGE FILES command.
In [7]:
1%%sql2SHOW STAGE FILES IN '{{ cluster_name }}'
Downloading the file is just as easy as uploading.
In [8]:
1%%sql2DOWNLOAD STAGE FILE 'stats.csv' IN '{{ cluster_name }}' TO 'stats.csv' OVERWRITE
In [9]:
1!cat stats.csv
If you just want to display the contents of the Stage file without saving it to a local
file, you simply leave the TO option off the DOWNLOAD STAGE FILE.
In [10]:
1%%sql2DOWNLOAD STAGE FILE 'stats.csv' IN '{{ cluster_name }}' ENCODING 'utf-8'
Creating folders
Up to this point we have just worked with files at the root of our Stage. We can use Fusion SQL
to create folder structures as well. This is done with the CREATE STAGE FOLDER command.
CREATE STAGE FOLDER '<stage-path>' [ IN { ID '<deployment-id>' | '<deployment-name>' } ] [ OVERWRITE ];
The following code will create this folder structure:
project-1/
project-1/data/
project-2/
project-2/data/
In [11]:
1for name in ['project-1', 'project-1/data', 'project-2', 'project-2/data']:2 %sql CREATE STAGE FOLDER '{{ name }}' IN '{{ cluster_name }}';
In [12]:
1%%sql2SHOW STAGE FILES IN '{{ cluster_name }}' RECURSIVE
Now that we have a folder structure we can put files into those folders.
In [13]:
1%%sql2UPLOAD FILE TO STAGE 'project-1/data/stats.csv' IN '{{ cluster_name }}' FROM 'mydata.csv';3UPLOAD FILE TO STAGE 'project-2/data/stats.csv' IN '{{ cluster_name }}' FROM 'mydata.csv';
Now when we do a recursive listing of our Stage, we'll see the newly created files.
In [14]:
1%%sql2SHOW STAGE FILES IN '{{ cluster_name }}' RECURSIVE
We can list the files at a specific path as well.
In [15]:
1%%sql2SHOW STAGE FILES IN '{{ cluster_name }}' AT 'project-2/data'
Loading data from Stage
We are going to load data from a Stage into a database table. A cluster is both the Stage and the compute that reads from it, so all we need now is a database on the cluster we created above.
In [16]:
1%%sql2SHOW CLUSTERS LIKE '{{ cluster_name }}'
Action Required
Make sure to select the fusion-notebook cluster from the drop-down menu at the top of this notebook.
In [17]:
1%%sql2CREATE DATABASE IF NOT EXISTS stage_loader
Action Required
Make sure to select the stage_loader database from the drop-down menu at the top of this notebook. It updates the connection_url to connect to that database.
In [18]:
1%%sql2DROP TABLE IF EXISTS stats;3CREATE TABLE stats (4 name TEXT,5 age INT,6 height INT7);
Load the data from the Stage using a pipeline.
In [19]:
1%%sql2CREATE PIPELINE IF NOT EXISTS stage_test3 AS LOAD DATA STAGE 'project-2/data/stats.csv'4 BATCH_INTERVAL 25005 SKIP DUPLICATE KEY ERRORS6 INTO TABLE stats7 IGNORE 1 LINES8 FIELDS TERMINATED BY ','9 LINES TERMINATED BY '\n'10 FORMAT CSV;11START PIPELINE stage_test FOREGROUND;12DROP PIPELINE stage_test;
We can now query the table and select the output into a Stage. Note that the
GROUP BY 1 is used here to combine the outputs from all of the database partitions
into a single file. If you don't use that, you'll get multiple output files,
each with a portion of the result set.
In [20]:
1%%sql2SELECT * FROM stats GROUP BY 1 INTO STAGE 'project-3/data/stats.csv'3 FIELDS TERMINATED BY ','4 LINES TERMINATED BY '\n'
In [21]:
1%%sql2SHOW STAGE FILES IN '{{ cluster_name }}' AT 'project-3' RECURSIVE
In [22]:
1%%sql2DOWNLOAD STAGE FILE 'project-3/data/stats.csv' ENCODING 'utf-8'
Deleting Stage files and folders
Files and folders can be deleted from a cluster Stage as well.
This is done with the DROP STAGE FILE and DROP STAGE FOLDER commands.
DROP STAGE FILE '<stage-path>' [ IN { ID '<deployment-id>' | '<deployment-name>' } ];
DROP STAGE FOLDER '<stage-path>' [ IN { ID '<deployment-id>' | '<deployment-name>' } ] [ RECURSIVE ];
Let's delete the stats.csv file at the root of our Stage.
In [23]:
1%%sql2DROP STAGE FILE 'stats.csv' IN '{{ cluster_name }}'
In [24]:
1%%sql2SHOW STAGE FILES IN '{{ cluster_name }}'
Now let's delete the project-2 folder including all of the files in it.
In [25]:
1%%sql2DROP STAGE FOLDER 'project-2' IN '{{ cluster_name }}' RECURSIVE
In [26]:
1%%sql2SHOW STAGE FILES IN '{{ cluster_name }}' RECURSIVE
In [27]:
1%%sql2DROP STAGE FOLDER 'project-1' IN '{{ cluster_name }}' RECURSIVE;3DROP STAGE FOLDER 'project-3' IN '{{ cluster_name }}' RECURSIVE;
Cleanup
Finally, terminate the cluster we created at the start of the notebook. This is the last
step for a reason: the notebook session is attached to that cluster, so its database
connection goes away with it. The DROP CLUSTER command itself is unaffected — Fusion SQL
sends it to the management API rather than to the cluster.
In [28]:
1%%sql2DROP CLUSTER '{{ cluster_name }}' WAIT ON TERMINATED
Conclusion
We have demonstrated how to create and delete files and folders in a cluster Stage using Fusion SQL. Note that it also supports managing Stage for starter clusters. It is also possible to work with Stage files using the SingleStoreDB Python SDK, see the API documentation for more details.
Details
About this Template
Learn how to manage your Stage files in SingleStoreDB Cloud using Fusion SQL.
This Notebook can be run in Standard and Enterprise deployments.
Tags
starterfusionpython
See Notebook in action
Launch this notebook in SingleStore and start executing queries instantly.
License
This Notebook has been released under the Apache 2.0 open source license.