@Rocky Can

SUBMIT TASK ...

be used to automate backups every x period? Or does it only support CTAS statements?

No,

SUBMIT TASK

cannot be used to automate backups. It is designed specifically for asynchronous ETL tasks and currently only supports a limited set of statements. Supported Statements for

SUBMIT TASK

As of StarRocks v3.3+, the

SUBMIT TASK

command is restricted to: *

INSERT

(including

INSERT INTO

and

INSERT OVERWRITE

) *

CREATE TABLE AS SELECT

(CTAS) *

CACHE SELECT

(v3.3 onwards, used for data cache warmup) Why it doesn't work for Backups 1. Syntax Restriction: The

BACKUP

statement is not part of the supported "ETL statements" list for the task scheduler. 2. Unique Snapshot Names: The

BACKUP

command requires a unique snapshot name for each execution (e.g.,

BACKUP SNAPSHOT db.snapshot_20231027 ...

).

SUBMIT TASK

currently executes a static SQL string and does not support dynamic variables or timestamps to generate unique snapshot names automatically. How to Automate Backups Depending on your cluster architecture, you have two main options: * For Shared-Data (Cloud-Native) Clusters: You can use the built-in Automated Cluster Snapshot feature. This automatically backs up the entire cluster state (metadata and data) to object storage at regular intervals.

sql
    -- Enable automated snapshots (defaults to every 10 minutes)
    ADMIN SET AUTOMATED CLUSTER SNAPSHOT ON;

You can adjust the interval using the FE configuration

automated_cluster_snapshot_interval_seconds

. * For Shared-Nothing (Standard) Clusters: There is no native SQL-based scheduler for the

BACKUP

command. You must use an external scheduler: * Cron Jobs / Scripts: A simple Bash or Python script that generates a timestamped snapshot name and executes the

BACKUP

command via the MySQL client. * Workflow Orchestrators: Tools like Apache Airflow or DolphinScheduler are recommended for managing the backup lifecycle (submission, status monitoring with

SHOW BACKUP

, and cleanup). * Community Tools: There are community-driven projects like

starrocks-backup-and-restore

designed specifically to handle incremental and scheduled backups for StarRocks. References • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md

@Rocky Do I need to be root user do backups ?

No, you do not need to be the root user to perform backups, but the user must have specific administrative privileges. The requirements depend on your StarRocks version: For StarRocks v3.0 and later StarRocks uses a fine-grained Access Control (RBAC) system. To perform a backup, a user or role needs two main privileges: 1.

REPOSITORY

privilege at the

SYSTEM

level (to interact with the backup repository). 2.

EXPORT

privilege for the specific tables or databases you want to back up. Example: Creating a dedicated backup role If you want to create a non-root user for backups, you can run:

sql
-- 1. Create a role
CREATE ROLE backup_admin;

-- 2. Grant permission to use the backup repository
GRANT REPOSITORY ON SYSTEM TO ROLE backup_admin;

-- 3. Grant permission to export data from a specific database
GRANT EXPORT ON ALL TABLES IN DATABASE my_db TO ROLE backup_admin;

-- 4. Assign the role to your user
GRANT backup_admin TO USER 'backup_user'@'%';

For StarRocks versions earlier than v3.0 In older versions, the privilege system was less granular. You generally needed: *

ADMIN_PRIV

: This is a high-level administrative privilege (similar to root but can be granted to other accounts). *

LOAD_PRIV

: In some early versions, having

LOAD_PRIV

on the database was sufficient to initiate backups. Summary While root has these permissions by default, it is a security best practice to create a dedicated user with only the

REPOSITORY

and

EXPORT

privileges for automated backup scripts or third-party backup tools. References • sql-reference/sql-statements/backup_restore/BACKUP.md • administration/user_privs/authorization/privilege_item.md • administration/user_privs/authorization/User_privilege.md • administration/management/Backup_and_restore.md

@Rocky Let's say I have incremental backups of the last 2 partitions of a table, which I backup every 8 hours. I was thinking of restoring the latest back up, then go to the second most recent to get the data that is farthest. However, the data that was in the second most recent backup might have already been restored using the most recent backup. StarRocks won't override the existing rows (based on the primary key) correct? or will it change?

For StarRocks, the

RESTORE

command behaves as an overwrite operation at the partition or table level, not a row-level "upsert" or merge. Does it override existing rows? Yes, but not at the row level. If you restore a backup into a table (or partition) that already exists, StarRocks will replace the entire partition/table with the data from the backup snapshot. * No Row-Level Merging: Even for Primary Key tables, StarRocks does not check individual primary keys to see if a row already exists and skip it. Instead, it swaps out the physical data files (rowsets) and metadata for the targeted partition. * Result of your scenario: If you restore the latest backup (which contains partitions P2 and P3) and then restore the second most recent backup (which contains P1 and P2): 1. The second restore will add partition P1 to your cluster. 2. Crucially, it will overwrite partition P2 with the older version of P2 from that second backup. Any data that arrived in P2 between the two backups will be lost. Recommended Approach: Restore and Merge If you need to "fill gaps" from multiple backups without losing newer data, you should not restore them directly onto the same table. Instead, follow this pattern: 1. Restore to a Temporary Table: Use the

AS

keyword in your

RESTORE

command to restore the older backup to a different name.

sql
    RESTORE SNAPSHOT db.old_snapshot FROM repo
    ON (my_table PARTITION (p1, p2))
    AS my_table_temp;

2. Manual Merge: Use an

INSERT INTO ... SELECT

statement to move data from the temporary table to the production table. * For Primary Key tables, this

INSERT

will act as an upsert, ensuring the latest version of a row (based on your logic or loading order) is preserved.

sql
    INSERT INTO my_table SELECT * FROM my_table_temp;

3. Cleanup: Drop the temporary table once the data is merged. Summary of Key Constraints * Schema Check: StarRocks first checks if the schema of the existing table matches the snapshot. If they don't match, the

RESTORE

job fails. * Overwrite Warning: The documentation explicitly warns that data overwritten during the

COMMIT

phase of a restore cannot be recovered. Always restore to a temporary table if you are unsure about the data overlap. References • sql-reference/sql-statements/backup_restore/RESTORE.md • administration/management/Backup_and_restore.md • sql-reference/sql-statements/backup_restore/BACKUP.md References • sql-reference/sql-statements/backup_restore/RESTORE.md • administration/management/Backup_and_restore.md