http://starrocks.io logo
Join Slack
Powered by
l
@Rocky hello
r
Hello! I'm Rocky, your StarRocks assistant. How can I help you today?
l
@Rocky i have a big table, which have partion by month like below: CREATE TABLE 
dtm_customer_ld_summary
( 
sys_run_date
date NOT NULL COMMENT "", 
ld_no
varchar(20) NOT NULL COMMENT "", 
ld_di_no
varchar(5) NOT NULL COMMENT "", 
branch_no
varchar(6) NOT NULL COMMENT "", 
ld_di_key
bigint(20) NULL COMMENT "", 
lms_cif_no
varchar(12) NULL COMMENT "", 
los_cif_no
varchar(20) NULL COMMENT "", 
lms_prod_group
varchar(3) NULL COMMENT "", .... PARTITION p202611 VALUES [("2026-11-01"), ("2026-12-01")), PARTITION p202612 VALUES [("2026-12-01"), ("2027-01-01")), PARTITION p202701 VALUES [("2027-01-01"), ("2027-02-01")), PARTITION p202702 VALUES [("2027-02-01"), ("2027-03-01")), PARTITION p202703 VALUES [("2027-03-01"), ("2027-04-01")), PARTITION p202704 VALUES [("2027-04-01"), ("2027-05-01"))) DISTRIBUTED BY HASH(
ld_no
) BUCKETS 32 PROPERTIES ( "bloom_filter_columns" = "account_number, contract_no", "compression" = "LZ4", "dynamic_partition.buckets" = "32", "dynamic_partition.enable" = "true", "dynamic_partition.end" = "6", "dynamic_partition.history_partition_num" = "72", "dynamic_partition.prefix" = "p", "dynamic_partition.replication_num" = "2", "dynamic_partition.start" = "-96", "dynamic_partition.start_day_of_month" = "1", "dynamic_partition.time_unit" = "MONTH", "dynamic_partition.time_zone" = "Asia/Ho_Chi_Minh", "enable_persistent_index" = "true", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" ); now i a new s3 repo which is call cool storage as below, can i alter table dtm_customer_ld_summary table to have 6 recent partition, as 6 recents months in current ssd hot storage, and from 7 month later save to cool storage CREATE REPOSITORY starrocks_backup_repo WITH BROKER ON LOCATION "s3a://starrocks-backup" PROPERTIES( "aws.s3.endpoint" = "10.0.27.75/starrocks-backup",
r
To achieve your goal of moving older data to "cool" storage (S3) while keeping the 6 most recent partitions on "hot" storage (SSD), you should use StarRocks' Data Tiering (also known as Hot/Cold separation) feature. Note that the 
CREATE REPOSITORY
command you mentioned is used for Backup and Restore (creating snapshots). For transparent data tiering where the database automatically moves data to S3, you must use Storage Volumes. Here are the steps to configure this: 1. Create a Storage Volume First, define the S3-compatible storage as a "Storage Volume". Since you are using a custom endpoint, make sure to include the region and credentials.
Copy code
sql
CREATE STORAGE VOLUME cool_s3_storage
TYPE = S3
LOCATIONS = ("<s3a://starrocks-backup/>")
PROPERTIES(
    "aws.s3.endpoint" = "<http://10.0.27.75:8333>",
    "aws.s3.region" = "us-east-1", -- Required, even for MinIO/Private S3
    "aws.s3.access_key" = "your_access_key",
    "aws.s3.secret_key" = "your_secret_key",
    "enabled" = "true"
);
2. Alter the Table to Enable Data Tiering You can then alter your table to use this volume and set a 
storage_cooldown_ttl
. StarRocks calculates the "cooling" time using this formula: 
Cooldown Time = Partition Upper Bound + storage_cooldown_ttl
To keep roughly 6 months of data in hot storage, you can set the TTL to 
5 MONTH
(since the current active month is also in SSD).
Copy code
sql
ALTER TABLE dtm_customer_ld_summary SET (
    "storage_medium" = "SSD",
    "storage_volume" = "cool_s3_storage",
    "storage_cooldown_ttl" = "5 MONTH"
);
How it works: * Automatic Movement: StarRocks will automatically check your partitions. If a partition's end date (upper bound) plus 5 months is in the past, the BE nodes will migrate those data files from the local SSD to the S3 bucket. * Transparency: The table remains fully queryable. StarRocks will automatically fetch data from S3 if a query hits an old partition. * SSD Requirement: The 
storage_cooldown_ttl
property only works if the 
storage_medium
is set to 
SSD
. * Dynamic Partitions: This setting will automatically apply to any new partitions created by your 
dynamic_partition
configuration. To Verify: You can check which partitions have been moved or when they are scheduled to move by running:
Copy code
sql
SHOW PARTITIONS FROM dtm_customer_ld_summary;
Look at the 
StorageMedium
and 
CooldownTime
columns. If 
StorageMedium
shows 
HDD
(in older versions) or the 
StoragePath
points to S3, the data has cooled down. References • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • knowledge/admin_and_operation/shared_data_compaction.md
l
@Rocky BACKUP SNAPSHOT pcgl.backup_20261001 TO starrocks_backup_repo PROPERTIES("type" = "full"); how to restore from above snapshot
r
To restore from the snapshot you created, follow these steps: 1. Find the Backup Timestamp StarRocks requires a specific timestamp to identify the backup within the repository. Run the following command to find the 
Timestamp
for your backup named `backup_20261001`:
Copy code
sql
SHOW SNAPSHOT ON starrocks_backup_repo;
_Look for the row where 
Snapshot
is 
backup_20261001
and copy the value in the 
Timestamp
column (e.g., 
2026-10-01-08-16-00
)._ 2. Run the Restore Command Use the 
RESTORE SNAPSHOT
command. You can restore the entire database or just the specific table. To restore the entire 
pcgl
database:
Copy code
sql
RESTORE SNAPSHOT pcgl.backup_20261001
FROM starrocks_backup_repo
PROPERTIES (
    "backup_timestamp" = "2026-10-01-08-16-00" -- Replace with the actual timestamp
);
To restore only the specific table `dtm_customer_ld_summary`:
Copy code
sql
RESTORE SNAPSHOT pcgl.backup_20261001
FROM starrocks_backup_repo
ON (dtm_customer_ld_summary)
PROPERTIES (
    "backup_timestamp" = "2026-10-01-08-16-00"
);
Important Considerations: * Target Database: If the database 
pcgl
already exists, the restore will attempt to add the tables to it. If the table already exists, the restore task will fail unless you restore it with a different name using `AS`: * Example: 
ON (dtm_customer_ld_summary AS dtm_customer_ld_summary_restored)
* Replication Num: By default, StarRocks tries to restore with 3 replicas. Since your original table used 
2
, you might need to specify it in properties: 
"replication_num" = "2"
. * Status: You can check the progress of the restore by running:
Copy code
sql
    SHOW RESTORE FROM pcgl;
``` References • sql-reference/sql-statements/backup_restore/RESTORE.md
l
@Rocky CREATE STORAGE VOLUME cool_seaweed_storage TYPE = S3 LOCATIONS = ("s3a://starrocks-uat/cool_storage") PROPERTIES( "aws.s3.endpoint" = "10.0.26.73", "aws.s3.use_aws_sdk_default_behavior" = "false", "aws.s3.use_instance_profile" = "false", "aws.s3.access_key" = "E1FC8FA6C4232C920C7E", "aws.s3.secret_key" = "pGz8Le7bTc0MD5CLg53DtVcIdLjXK8DnGM2qVtN", "aws.s3.enable_path_style_access" = "true", "aws.s3.enable_ssl" = "true", "enabled" = "true" ); SQL Error [1064] [42000]: Invalid location s3a://starrocks-uat/cool_storage Invalid location s3a://starrocks-uat/cool_storage
r
The error is caused by several syntax and formatting issues in your 
CREATE STORAGE VOLUME
statement. Key Issues: 1. Protocol: For StarRocks Storage Volumes, use the 
s3://
 protocol. 
s3a://
is specifically for Hadoop environments and is not supported here. 2. Literal Brackets: You included literal 
<
and 
>
characters inside your strings. These are documentation placeholders and must be removed. 3. SSL Mismatch: You specified 
http://
in the endpoint but set 
"aws.s3.enable_ssl" = "true"
. If you are using HTTP, SSL should be 
false
. 4. Missing Region: The S3 SDK requires a region (e.g., 
us-east-1
) even for S3-compatible storage like SeaweedFS. Corrected SQL:
Copy code
sql
CREATE STORAGE VOLUME cool_seaweed_storage
TYPE = S3
LOCATIONS = ("<s3://starrocks-uat/cool_storage>")
PROPERTIES(
    "aws.s3.endpoint" = "<http://10.0.26.73:8333>",
    "aws.s3.region" = "us-east-1",
    "aws.s3.access_key" = "E1FC8FA6C4232C920C7E",
    "aws.s3.secret_key" = "pGz8Le7bTc0MD5CLg53DtVcIdLjXK8DnGM2qVtN",
    "aws.s3.enable_path_style_access" = "true",
    "aws.s3.enable_ssl" = "false",
    "enabled" = "true"
);
Next Steps: After creating the volume, you can check its status with:
Copy code
sql
DESC STORAGE VOLUME cool_seaweed_storage;
Then, to apply it to your table for data tiering as discussed previously:
Copy code
sql
ALTER TABLE dtm_customer_ld_summary SET (
    "storage_volume" = "cool_seaweed_storage",
    "storage_cooldown_ttl" = "5 MONTH"
);
References • deployment/shared_data/s3.md • quick_start/shared-data.md • sql-reference/sql-statements/cluster-management/storage_volume/CREATE_STORAGE_VOLUME.md
l
@Rocky current starrocks is share nothing, so can storage_ volume apply?
r
In a Shared-Nothing architecture (using BE nodes and local storage), you can still use 
STORAGE VOLUME
for Data Tiering (Hot/Cold separation). While Shared-Data clusters use Storage Volumes for all their data, Shared-Nothing clusters use them as a "cold" tier to offload older partitions from local disks to S3. How it works in Shared-Nothing: * Storage Volume as a Cold Tier: You define the S3/SeaweedFS destination as a Storage Volume. * Automatic Migration: When you set 
storage_cooldown_ttl
on a table, the BE nodes (which manage the local "hot" data) will automatically upload older data to the specified Storage Volume and then delete the local copies to free up SSD space. * Transparent Queries: Your BE nodes will still handle the queries. If a query needs "cold" data, the BE will fetch it from S3. Requirements: 1. StarRocks Version: This feature using 
STORAGE VOLUME
for tiering is available from StarRocks 3.1 onwards. (In versions 2.5 to 3.0, a similar feature existed but used 
CREATE RESOURCE
). 2. Storage Medium: Your table must have 
"storage_medium" = "SSD"
for the cooldown timer to trigger. Example Configuration for Shared-Nothing: If you have already created the volume 
cool_seaweed_storage
, apply it to your existing shared-nothing table like this:
Copy code
sql
ALTER TABLE dtm_customer_ld_summary SET (
    "storage_medium" = "SSD",
    "storage_volume" = "cool_seaweed_storage",
    "storage_cooldown_ttl" = "6 MONTH"
);
Verification: After the "cooldown" period passes for a partition, you can verify the status:
Copy code
sql
SHOW PARTITIONS FROM dtm_customer_ld_summary;
Check the 
StorageMedium
 column. It will change from 
SSD
to 
HDD
(which StarRocks uses as a label for remote/cold storage in these reports) once the migration is complete. Summary of Differences: Feature | Shared-Nothing Cluster | Shared-Data Cluster Primary Storage | Local Disks (BE) | S3 / Remote (CN) Storage Volume Role | Optional (Cold Tier only) | Required (Primary Tier) Data Migration | BE moves data from local $\rightarrow$ S3 | Data is always on S3 Rocky's Advice: Even though you are in "Shared-Nothing" mode, using 
STORAGE VOLUME
is the modern and recommended way to save costs on large historical datasets like yours. References • introduction/Architecture.md • deployment/shared_data/feature-support-shared-data.md • administration/management/resource_management/spill_to_disk.md • knowledge/admin_and_operation/shared_data_compaction.md
l
@Rocky how to view or show current storage in starrocks be, how many storage, ssd, hđ
r
To view and manage storage in a StarRocks Shared-Nothing cluster, you can use the following methods to see total capacity, disk-level details, and the medium types (SSD/HDD). 1. Cluster-Wide Summary (Backends) To see a high-level overview of storage usage across all Backend (BE) nodes, run:
Copy code
sql
SHOW BACKENDS;
-- OR
SHOW PROC '/backends';
Key Columns to look for: * DataUsedCapacity: Space occupied by StarRocks data files. * AvailCapacity: Remaining available space on the disks. * TotalCapacity: The total capacity of the disks StarRocks is using. * MaxDiskUsedPct: If a BE has multiple disks, this shows the usage percentage of the most full disk. 2. Disk-Level Breakdown (SSD vs HDD) To see the specific disks attached to a particular BE and whether they are labeled as SSD or HDD, follow these steps: 1. Run 
SHOW BACKENDS;
and copy the BackendId for the node you want to inspect. 2. Run the following command (replace 
<BackendId>
with your ID, e.g., 
10001
):
Copy code
sql
SHOW PROC '/backends/10001';
Output will show: * RootPath: The local directory path (e.g., 
/data1/storage
). * TotalCapacity / UsedCapacity / AvailableCapacity: Usage per specific disk. * StorageMedium: This will explicitly show SSD or HDD. 3. How the Medium is Defined The "SSD" or "HDD" label is defined in your BE configuration file (
be.conf
) using the 
storage_root_path
parameter. * Example configuration: 
storage_root_path=/data1,medium:ssd;/data2,medium:hdd
* If no medium is specified, StarRocks defaults to HDD. 4. Viewing Tablet-Level Storage If you want to see which specific tables/tablets are currently sitting on SSD vs HDD:
Copy code
sql
SELECT 
    BE_ID, 
    TABLET_ID, 
    MEDIUM_TYPE, 
    DATA_SIZE 
FROM information_schema.be_tablets 
LIMIT 10;
Summary of Commands Goal | Command Total storage per node | 
SHOW BACKENDS;
Specific disk types (SSD/HDD) | 
SHOW PROC '/backends/<BackendId>';
Table-specific storage type | 
SHOW PARTITIONS FROM <table_name>;
References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_BACKENDS.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • administration/management/BE_configuration.md • sql-reference/information_schema/be_tablets.md References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_BACKENDS.md
l
@Rocky current only having HDD, so how to change from HDD to cool storage StorageMedium: HDD
r
In StarRocks Shared-Nothing, the automatic "cooldown" process is designed to move data from a Hot tier to a Cold tier. By default, StarRocks treats SSD as Hot and HDD (or S3) as Cold. Because your disks are currently labeled as 
HDD
, StarRocks considers your data to already be in the "Cold" state, so it won't automatically trigger a migration to your S3 
STORAGE VOLUME
. To fix this and move data from your local HDDs to S3, you have two options: Option 1: The "Label Trick" (Recommended) You can "trick" StarRocks into treating your local HDD as the "Hot" tier by relabeling it in the configuration. Even if the physical disk is an HDD, StarRocks will use it as the primary storage and then cool data to S3. 1. Modify 
be.conf
 on every Backend node: Find your 
storage_root_path
and explicitly add 
,medium:ssd
to the path. Example:
Copy code
properties
    storage_root_path = /data1/starrocks/storage,medium:ssd
2. Restart the BEs. 3. Check Status: Run 
SHOW PROC '/backends/<ID>';
again. The 
StorageMedium
should now show SSD. 4. Apply to Table:
Copy code
sql
    ALTER TABLE dtm_customer_ld_summary SET (
        "storage_medium" = "SSD",
        "storage_volume" = "cool_seaweed_storage",
        "storage_cooldown_ttl" = "1 MONTH"
    );
────────── Option 2: Bypass the Strict Check (SQL Only) If you don't want to restart your BEs, you can force StarRocks to accept an "SSD" setting on a table even if the physical disks are HDDs. 1. Disable the strict check (run as admin):
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("enable_strict_storage_medium_check" = "false");
2. Force the table to SSD and link the volume:
Copy code
sql
    ALTER TABLE dtm_customer_ld_summary SET (
        "storage_medium" = "SSD",
        "storage_volume" = "cool_seaweed_storage",
        "storage_cooldown_ttl" = "1 MONTH"
    );
How to verify it's working: After applying the change, wait for the 
storage_cooldown_ttl
period to pass for your older partitions. Then run:
Copy code
sql
SHOW PARTITIONS FROM dtm_customer_ld_summary;
* StorageMedium: Should eventually show HDD (StarRocks uses this label for "Remote/S3" in partition reports for Shared-Nothing). * RemoteKey: If this column has a value, it means the data has successfully moved to your S3 storage volume. Important Note: Data tiering to 
STORAGE VOLUME
in Shared-Nothing mode requires StarRocks v3.1 or higher. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/BE_configuration.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/FE_configuration.md • table_design/table_design.md • deployment/plan_cluster.md • data_source/catalog/iceberg/iceberg_catalog.md
Open in Slack
PreviousNext
Powered by