Moving Data Across Workspaces

Move a Table or a Subset of Data (ETL via Object Storage)

This method involves the following steps:

1. Export from the source workspace group: From a workspace in the source workspace group (WSG), write the table to S3/GCS/Azure Blob using SELECT ... INTO. For example:

   SQL

   Copy

   SELECT * FROM src_db.src_table
     INTO S3 's3://my-bucket/my-prefix/'
     CONFIG     '{...}'
     CREDENTIALS '{...}';

   Variants exist for INTO AZURE, INTO GCS, etc. for details refer SELECT

2. Re-create the schema in target workspace group: In a workspace in the destination WSG, create the destination table with matching DDL.

3. Load into target workspace group: In the destination WSG, use a Pipeline (recommended) or LOAD DATA from that bucket to load into the new table.

   SQL

   Copy

   CREATE PIPELINE p_move_table AS
     LOAD DATA S3 's3://my-bucket/my-prefix/'
     CONFIG     '{...}'
     CREDENTIALS '{...}'
     INTO TABLE dest_db.dest_table;
   
   START PIPELINE p_move_table;

   For details on options available refer:

   CREATE PIPELINE

   Load Data with SingleStore Flow on Helios

   LOAD DATA

Move an Entire Database

If you want to move a whole database across WSGs, you can:

Run BACKUP DATABASE from a workspace in the source workspace group to object storage.

Run RESTORE DATABASE from a workspace in the destination workspace group, pointing at that backup.