http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> how can I merge partitions of multi...
# questions-and-troubleshooting
m
@Rocky how can I merge partitions of multiple months (3-4 months) or multiple weeks?
r
In StarRocks, the method for merging partitions depends on your StarRocks version and whether you are using Expression Partitioning (recommended) or traditional Range Partitioning. 1. Expression Partitioning (Recommended, v3.4+) Starting from StarRocks v3.4, you can merge partitions of a finer granularity (like days or weeks) into a coarser granularity (like months or quarters) using a single command. Syntax:
Copy code
sql
ALTER TABLE <table_name> 
PARTITION BY <time_expr> 
[WHERE <time_range_column>] BETWEEN <start_time> AND <end_time>;
Examples: * Merge Days/Weeks into Months: If you have daily partitions and want to merge them into monthly ones for the first quarter of 2024:
Copy code
sql
    ALTER TABLE my_table
    PARTITION BY date_trunc('month', event_date)
    BETWEEN '2024-01-01' AND '2024-03-31';
* Merge Months into Quarters (3 months):
Copy code
sql
    ALTER TABLE my_table
    PARTITION BY date_trunc('quarter', event_date)
    BETWEEN '2024-01-01' AND '2024-12-31';
Notes: * Supported 
date_trunc
units: 
day
, 
week
, 
month
, 
quarter
, 
year
. * This is currently only supported for tables using expression partitioning. ────────── 2. Range Partitioning (Manual / Non-standard Intervals) If you are on an older version or need to merge a non-standard range (like exactly 4 months, which isn't a standard 
date_trunc
unit), you must use the Temporary Partition replacement method. This allows you to "merge" data without taking the table offline. Steps to merge partitions (e.g., merging 
p1
, 
p2
, 
p3
, 
p4
into one): 1. Create a Temporary Partition: Add a temporary partition that covers the entire range of the partitions you want to merge.
Copy code
sql
    ALTER TABLE my_table
    ADD TEMPORARY PARTITION tp_merged VALUES [('2024-01-01'), ('2024-05-01'));
2. Move the Data: Insert data from the original partitions into the temporary one.
Copy code
sql
    INSERT INTO my_table TEMPORARY PARTITION (tp_merged)
    SELECT * FROM my_table PARTITION (p1, p2, p3, p4);
3. Replace Partitions: Atomically replace the old small partitions with the new merged temporary partition.
Copy code
sql
    ALTER TABLE my_table
    REPLACE PARTITION (p1, p2, p3, p4)
    WITH TEMPORARY PARTITION (tp_merged)
    PROPERTIES ("strict_range" = "false");
_Note: Setting 
"strict_range" = "false"
is required if the new partition range is different from the sum of the individual old ranges._ ────────── 3. Comparison of Granularities Target Granularity | Best Tool | Supported Unit Weekly | 
ALTER TABLE ... PARTITION BY
| 
date_trunc('week', ...)
Monthly | 
ALTER TABLE ... PARTITION BY
| 
date_trunc('month', ...)
Quarterly (3mo) | 
ALTER TABLE ... PARTITION BY
| 
date_trunc('quarter', ...)
Custom (e.g. 4mo) | Temporary Partition Method | Manual Range Recommendation: If your use case fits into months or quarters, upgrade to v3.4+ and use Expression Partitioning, as it automates the background data movement and metadata updates. metadata cleanup and data movement. For older versions or custom 4-month logic, use the Temporary Partition swap.*.Temporary Partitions* are the safest way to avoid data-to-go. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/data_distribution/Temporary_partition.md
m
@Rocky If I'm trying to create table with datetime as partition column but different ways date_trunc('month',<datetime col>), similary year, day, week, quarter. I could see month, year, day partitionKey is same <datetime col> but for week & quarter, it created 
__generated_partition_column_0
. Is this expected behaviour? and does it still prune partitions properly if it is generated column?
r
Yes, what you are seeing is expected behavior in StarRocks versions 3.4 and later. The difference in behavior between granularities (Day/Month vs. Week/Quarter) is due to how StarRocks handles "Simple" vs. "Complex" expression partitioning internally. 1. Why the generated column exists * Simple Granularities (Day, Month, Year, Hour): StarRocks maps these directly to traditional Range Partitioning on your original column. Because the boundaries for these units are standard, StarRocks uses the original column as the 
PartitionKey
. * Complex/Custom Granularities (Week, Quarter, etc.): Historically, 
week
and 
quarter
were not supported in the "simple" automatic partitioning logic. In v3.4+, StarRocks introduced Complex Expression Partitioning. For these units (and any custom expressions), StarRocks creates a hidden shadow column (like 
__generated_partition_column_0
) to store the pre-computed result of the expression. This value is used as the internal partition key. 2. Does partition pruning still work? Yes, but with a version requirement for the best experience: * StarRocks v3.4.4 and later: The optimizer is specifically designed to support partition pruning for most 
DATETIME
functions. Even if you query using the original datetime column, the engine will automatically derive the corresponding values for the generated column and skip irrelevant partitions. * Pre-v3.4.4: Pruning might only work if your query filter matches the expression exactly (e.g., 
WHERE date_trunc('week', col) = ...
) or if you filter on the generated column directly (not recommended as it's an internal name). 3. Recommendation If you want to avoid the generated column for weekly partitions and keep the original column as the partition key, you can use the 
time_slice
function instead:
Copy code
sql
-- This typically uses the original column as the partition key (Simple Expression)
PARTITION BY time_slice(your_col, INTERVAL 1 WEEK)
Summary Table: Function / Unit | Internal Key Type | Pruning Method 
date_trunc
(day/month/year) | Original Column | Native Range Pruning 
date_trunc
(week/quarter) | Generated Column | Expression-based Pruning (v3.4.4+) 
time_slice
| Original Column | Native Range Pruning To verify pruning is working for your specific query: Run 
EXPLAIN SELECT ...
and look for the 
partitions=
line. It should show a fraction (e.g., 
partitions=1/100
) rather than scanning all partitions. References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-functions/date-time-functions/date_trunc.md • sql-reference/sql-functions/date-time-functions/time_slice.md References • unloading/Spark_connector.md • table_design/data_distribution/expression_partitioning.md
m
@Rocky I have a week partitioning and I could see generated column as partitionKey, if I have to merge 4weeks to 1month partition, how should I give, do I need to pass _generated column only or actual partition datetime field when merge from alter?
r
When merging partitions in StarRocks (v3.4+), you should always use the actual datetime column from your table schema, not the internal 
__generated_partition_column_0
. StarRocks handles the mapping between your business column and the internal generated partition keys automatically. Even if the current partitioning (e.g., weekly) relies on a hidden generated column, the 
ALTER TABLE
syntax is designed to work with your original column name. Correct Merge Syntax To merge weekly partitions into monthly ones, use the following structure:
Copy code
sql
ALTER TABLE <your_table> 
PARTITION BY date_trunc('month', <actual_datetime_col>)
WHERE <actual_datetime_col> BETWEEN '2024-01-01' AND '2024-03-31';
Why you use the actual column: 1. Abstraction: The 
__generated_partition_column_0
is an implementation detail. Using it in DDL commands can lead to errors because StarRocks expects the source column to recalculate the new partition boundaries. 2. Logic: When you run the 
ALTER
command, StarRocks analyzes the original data and the new expression (
date_trunc('month', ...)
). It then creates the new monthly partitions and moves the data accordingly. 3. Cleanliness: After the merge is complete, if the new monthly partitioning strategy is "simple" (which Month/Day/Year usually are), StarRocks might even remove the need for that specific generated column for the merged partitions, mapping them back to the original column. Summary of parameters: * 
PARTITION BY
: Use the original column inside the 
date_trunc
or 
time_slice
function. * 
WHERE
: Use the original column to define the time range of partitions you want to target for the merge. Verification: After running the command, you can run 
SHOW PARTITIONS FROM <table_name>
to see the new monthly partitions. You will notice the 
PartitionKey
for the new partitions will now reflect the monthly values derived from your original datetime column. References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
m
@Rocky if my table is partitioned and it is pk table, partition by created_on month and id is pk primary key(created_on,id) partition by date_trunc('month', created_on) and created_on is datetime in distributed by should I give both created_on,id or only id, which is recommended?
r
For a Primary Key table in StarRocks where you are partitioned by 
created_on
(via 
date_trunc
) and your primary key is 
(created_on, id)
, it is highly recommended to distribute by 
id
only. Why recommend 
DISTRIBUTED BY HASH(id)
? 1. Even Data Distribution: The bucketing column should have the highest possible cardinality to prevent data skew. While 
created_on
(datetime) has high cardinality overall, it is already used for the first level of distribution (partitioning). Distributing by 
id
(usually a unique or near-unique identifier) ensures that rows within each month's partition are spread evenly across all buckets. 2. Query Performance (Bucket Pruning): If you distribute by both 
(created_on, id)
, StarRocks can only perform bucket pruning if your 
WHERE
clause contains both columns. If you frequently query by 
id
alone (e.g., 
SELECT * FROM table WHERE id = 123
), StarRocks would have to scan all buckets in the partition. By distributing by 
id
alone, a query on 
id
can immediately target a single bucket. 3. Simpler Hashing: Adding more columns to the hash distribution key increases the complexity of the hash calculation and makes it harder to hit the "Colocate Join" or "Bucket Shuffle Join" optimizations in the future, as those require matching distribution keys between tables. Requirements Check For Primary Key tables, StarRocks has a specific rule: The Primary Key must include all Partitioning columns and all Bucketing columns. * Your Partition Col: 
created_on
(Included in your PK) * Your Bucketing Col: 
id
(Included in your PK) * Result: 
PRIMARY KEY (created_on, id) DISTRIBUTED BY HASH(id)
is a valid and optimal configuration. Summary Recommendation
Copy code
sql
CREATE TABLE my_pk_table (
    created_on DATETIME NOT NULL,
    id BIGINT NOT NULL,
    ...
)
PRIMARY KEY (created_on, id)
PARTITION BY date_trunc('month', created_on)
DISTRIBUTED BY HASH(id) -- Recommended
PROPERTIES (
    "enable_persistent_index" = "true"
);
Note: If your 
id
is not unique across the whole table and you expect the same 
id
to appear in different months, distributing by 
id
is still fine—it just means all records for that specific 
id
(across all months) will land in the same bucket "index" within their respective monthly partitions. References • table_design/table_types/primary_key_table.md • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md References • table_design/table_types/primary_key_table.md • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • table_design/table_types/primary_key_table.md • table_design/data_distribution/Data_distribution.md • best_practices/bucketing.md • best_practices/partitioning.md References • table_design/table_types/primary_key_table.md • table_design/data_distribution/Data_distribution.md
2 Views
Open in Slack
PreviousNext
Powered by