@Rocky I just learned about temporary partitions from the starrocks docs, could you explain a use case for them?

Hello! I'm Rocky. Temporary partitions are a powerful feature in StarRocks that allow you to perform maintenance and data updates on a table without interrupting your users' queries. Think of them as a "staging area" for specific partitions where you can prepare data or change settings before swapping them into the live table. Here are the three most common use cases: 1. Atomic Data Overwrite (Zero Downtime) This is the most popular use case. If you need to refresh all the data in a specific time range (e.g., re-running a batch job for "yesterday's data"), you don't want to delete the old data first because the table would be empty for a few minutes. * The Workflow: Create a temporary partition for that date range, load the new corrected data into it, and then use the

REPLACE

command. * The Result: The switch happens instantly and atomically. Users querying the table will see either the old data or the new data, but never an empty partition. 2. Adjusting Bucket Counts (Rescaling) As your data grows, the initial number of buckets (shards) you chose might become too small, leading to large tablets and poor performance. * The Workflow: Create a temporary partition with the same range as your existing one but specify a higher bucket count. You can then

INSERT INTO ... SELECT

from the formal partition to the temporary one. * The Result: Once the data is redistributed into the new bucket structure, you replace the old partition. This is how you "re-bucket" data without recreating the entire table. 3. Modifying Partition Rules (Merging or Splitting) Sometimes your partitioning strategy needs to change—for example, merging several daily partitions into one monthly partition, or splitting a giant partition into smaller ones. * The Workflow: You create temporary partitions with the new ranges you want. You then load the data from the old partitions into these new temporary ones. * The Result: You can replace multiple formal partitions with your new temporary ones in a single command, effectively reshaping your table's layout. Quick Example Once you've loaded your data into a temporary partition named

tp1

, you can swap it into the live table like this:

sql
ALTER TABLE site_access 
REPLACE PARTITION (p1) 
WITH TEMPORARY PARTITION (tp1);

Key Advantages: * Query Isolation: You can even run

SELECT

directly against a temporary partition to verify the data before making it "live." * Flexibility: Temporary partitions can have different storage mediums (SSD/HDD) or different replication numbers than the formal ones. Do you have a specific data maintenance task in mind that you're looking to solve with these? References • table_design/data_distribution/Temporary_partition.md

@Rocky can I pipe to a temp partition?

Yes, you can certainly "pipe" data into a temporary partition in StarRocks using several different methods, depending on whether you are referring to a shell pipe, the StarRocks Pipe feature (v3.2+), or a general data load. 1. Using Stream Load (Shell Pipe) If you are piping data from your terminal or a script (e.g.,

cat data.csv | curl ...

), you can target a temporary partition by using the

temporary_partitions

HTTP header. Example:

bash
cat data.csv | curl --location-trusted -u <username>:<password> \
    -H "label:load_label_01" \
    -H "temporary_partitions: tp1, tp2" \
    -T - \
    http://<FE_HOST>:<FE_HTTP_PORT>/api/<db_name>/<table_name>/_stream_load

2. Using the StarRocks "Pipe" Feature (v3.2+) The Pipe feature in StarRocks (used for continuous loading from S3, HDFS, etc.) uses an

INSERT INTO

statement as its definition. Since

INSERT INTO

supports temporary partitions, you can define a pipe that targets them. Example:

sql
CREATE PIPE my_pipe
AS 
INSERT INTO my_table TEMPORARY PARTITION (tp1)
SELECT * FROM FILES (
    "path" = "<s3://my-bucket/data/*>",
    "format" = "parquet",
    ...
);

Note: This is useful for staging data in a temporary partition before performing an atomic swap. 3. Using Standard SQL

INSERT INTO

You can also use a standard

INSERT INTO

statement to load data from another table or a file into a temporary partition. Example:

sql
INSERT INTO my_table TEMPORARY PARTITION (tp1) 
SELECT * FROM source_table;

4. Other Loading Methods * Routine Load (Kafka): You can specify

TEMPORARY PARTITIONS(tp1, ...)

in the

CREATE ROUTINE LOAD

statement. * Broker Load: You can specify

TEMPORARY PARTITION (tp1, ...)

in the

DATA INFILE

clause. Common Workflow Usually, the reason to load into a temporary partition is to perform an atomic overwrite of existing data. The workflow looks like this: 1. Create the temporary partition:

sql
    ALTER TABLE my_table ADD TEMPORARY PARTITION tp1 VALUES [('2023-01-01'), ('2023-02-01'));

2. Load data into it (using one of the methods above). 3. Replace the formal partition with the temporary one:

sql
    ALTER TABLE my_table REPLACE PARTITION (p1) WITH TEMPORARY PARTITION (tp1);

This ensures that queries against the table remain uninterrupted while you are "piping" or loading the new data. References • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md • table_design/data_distribution/Temporary_partition.md