< Rocky> how to unload table into s3 StarRocks #questions-and-troubleshooting

Join Slack

<@U0A71G31CDV> how to unload table into s3

# questions-and-troubleshooting

Eraj Saliev

08/20/2026, 10:17 AM

@Rocky how to unload table into s3

Rocky

08/20/2026, 10:17 AM

You can unload data from StarRocks to AWS S3 using two primary methods: the modern INSERT INTO FILES()
(synchronous) or the traditional EXPORT
(asynchronous). Method 1:
INSERT INTO FILES()
(Recommended for v3.2+) This is the most flexible method, allowing you to export query results directly to S3. It is synchronous, meaning the client waits for the operation to complete. Example (Parquet):

Copy code

sql
INSERT INTO FILES (
    "path" = "<s3://your-bucket/unload/data_prefix_>",
    "format" = "parquet",
    "compression" = "lz4",
    "aws.s3.access_key" = "YOUR_ACCESS_KEY",
    "aws.s3.secret_key" = "YOUR_SECRET_KEY",
    "aws.s3.region" = "us-west-2"
)
SELECT * FROM your_table;

* Key Properties: * `path`: The S3 path and file prefix. * `format`: Supports

parquet

csv

, or

orc

. * `single`: (Optional) Set to

"true"

to merge the results into a single file. ────────── Method 2:
EXPORT
Command (Asynchronous) Use this for background tasks or when you want to export an entire table (or specific partitions) without keeping the session open. Example:

Copy code

sql
EXPORT TABLE your_table 
TO "<s3a://your-bucket/export/path/>"
WITH BROKER (
    "aws.s3.access_key" = "YOUR_ACCESS_KEY",
    "aws.s3.secret_key" = "YOUR_SECRET_KEY",
    "aws.s3.region" = "us-west-2"
);

* Monitoring: Use

SHOW EXPORT;

to check the progress and status of the job. * Protocol: Note the use of

s3a://

in the URI for the

EXPORT

"aws.s3.use_instance_profile" = "true"

. * IAM Role: Set

"aws.s3.iam_role_arn" = "arn:aws:iam::..."

. For more details, refer to the Unload using INSERT INTO FILES or EXPORT documentation. References • sql-reference/sql-statements/loading_unloading/unloading/EXPORT.md • unloading/Export.md • loading/s3.md

Eraj Saliev

08/20/2026, 11:01 AM

@Rocky can i do something like this SUBMIT TASK backup_fact_bet_legs PROPERTIES ('insert_timeout' = '86400') AS INSERT INTO FILES(

Rocky

08/20/2026, 11:01 AM

Yes, you can use SUBMIT TASK
to run an

INSERT INTO FILES()

operation as an asynchronous background task. This is particularly useful for large exports that might otherwise hit session timeouts. However, there is a small syntax requirement for the properties: you must prefix session variables with session.
. Corrected Syntax

Copy code

sql
SUBMIT TASK backup_fact_bet_legs
PROPERTIES (
    "session.insert_timeout" = "86400"
)
AS
INSERT INTO FILES (
    "path" = "<s3://your-bucket/backup/fact_bet_legs/>",
    "format" = "parquet",
    "aws.s3.access_key" = "YOUR_AK",
    "aws.s3.secret_key" = "YOUR_SK",
    "aws.s3.region" = "us-west-2"
)
SELECT * FROM fact_bet_legs;

Key Details: 1. Prefix: Use

"session.insert_timeout"

instead of just

"insert_timeout"

inside the

PROPERTIES

block. 2. Monitoring: Since the task runs in the background, you can check its status using:

Copy code

sql
    SELECT * FROM information_schema.task_runs
    WHERE task_name = 'backup_fact_bet_legs';

3. Version: This feature is supported from v3.2.0 onwards (when

INSERT INTO FILES

was introduced). 4. Automatic Cleanup: By default, StarRocks keeps task history for 24 hours (controlled by the FE config

task_runs_ttl_second

). Alternative: Hints You can also use a hint if you prefer not to use the

PROPERTIES

block:

Copy code

sql
SUBMIT /*+ set_var(insert_timeout=86400) */ TASK backup_fact_bet_legs
AS 
INSERT INTO FILES (...) SELECT ...;

References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • sql-reference/sql-statements/loading_unloading/INSERT.md • unloading/unload_using_insert_into_files.md References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • sql-reference/sql-statements/loading_unloading/ETL/ALTER_TASK.md

4 Views

Open in Slack

Previous Next