This topic describes how to modify the hot/cold storage policy (STORAGE_CACHE_POLICY) at the table level by using SQL statements:
- Modify the hot/cold storage policy for a regular table.
- Modify the hot/cold storage policy for an index table.
Modify a hot/cold storage policy for a regular table
After a table is created, you can use the ALTER TABLE statement to modify its hot/cold storage policy (STORAGE_CACHE_POLICY). The syntax is as follows:
ALTER TABLE table_name SET STORAGE_CACHE_POLICY (storage_cache_policy_option);
storage_cache_policy_option:
GLOBAL = {"hot" | "auto" | "cold"}
| timeline_strategy_list
timeline_strategy_list:
BOUNDARY_COLUMN = column_name
| BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
| HOT_RETENTION = intnum retention_time_unit
| MIXED_RETENTION = intnum retention_time_unit
| GRANULARITY = "partition"
retention_time_unit:
YEAR
| MONTH
| WEEK
| DAY
| HOUR
| MINUTE
Related parameters
The attributes in storage_cache_policy_option are defined in Key-Value format. The semantics of each attribute are as follows:
GLOBAL = {"hot" | "auto" | "cold"}: Specifies the hot/cold storage policy for all data in the table. Valid values:hot: Indicates that all data in the specified table is hot data. If sufficient cache disk space is available, all data under the table will be cached to the local cache disk.auto: Indicates that the system automatically identifies the hot data for the specified table.cold: Indicates that the data in the specified table is cold data and will not be cached to the local cache disk. For data already persisted to the cache, cache eviction is actively triggered. Subsequent accesses to the table data can be stored in the macroblock memory cache and will not be persisted to the macroblock cache.
timeline_strategy_list: Indicates the list of timeline strategy parameters, separated by commas. The timeline strategy determines whether partition data is hot or cold based on time, and the system automatically adjusts the data on the local cache disk according to the configured strategy.Note
Timeline: Hot data caching is performed based on the partition range defined by the RANGE partitioning. When certain time conditions are met, the data in that partition is identified as hot data. When using the timeline strategy, note the following considerations:
- Only supported for RANGE-partitioned tables (RANGE partitioning can be at the primary or secondary level), because time is needed to determine if it has expired. However, it cannot be a dual RANGE partition, as it would be unclear which RANGE partition time to use.
- When using the timeline strategy, the partition expression can only contain column names; expression conversion is not supported. For example,
PARTITION BY RANGE COLUMNS(expr(col3)). BOUNDARY_COLUMNmust be the partitioning key. If the partitioning key consists of multiple columns,BOUNDARY_COLUMNmust be the first column, used to determine whether the partition has expired.
BOUNDARY_COLUMN = column_name: Specifies the column for identifying hot data. Supports integer types (BIGINTorINT, in Unix timestamp format) and time types (TIMESTAMP,DATE,DATETIME, orYEAR).- If
BOUNDARY_COLUMNis of an integer type, the table's partitioning method supports RANGE or RANGE COLUMNS partitioning. - If
BOUNDARY_COLUMNis of a time type, the table's partitioning method can only use RANGE COLUMNS partitioning.
- If
BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: Specifies the time unit for theBOUNDARY_COLUMNparameter. This parameter is only supported whenBOUNDARY_COLUMNis an integer. When an integer value is used as a timestamp, a timestamp unit must be provided; otherwise, it may lead to incorrect time parsing. Valid values:When the partitioning column is of type
INT, the value forBOUNDARY_COLUMN_UNITcan only bes.When the partitioning column is of type
BIGINT, the value forBOUNDARY_COLUMN_UNITcan besorms.Notice
If the format is not a Unix timestamp, the time represented by
INTtype will not be correctly identified.
HOT_RETENTION = intnum retention_time_unit: Configures the time range for hot data.intnum: Represents an integer.retention_time_unit: Represents a time unit. Valid values:YEAR: Represents years.MONTH: Represents months.WEEK: Represents weeks.DAY: Represents days.HOUR: Represents hours.MINUTE: Represents minutes.
MIXED_RETENTION = intnum retention_time_unit:- If
MIXED_RETENTIONis set, data within the time range ofHOT_RETENTION + MIXED_RETENTIONis classified ascolddata. - If
MIXED_RETENTIONis not set, data remainsautodata after reachingHOT_RETENTION. - If
HOT_RETENTIONis not set, the time range [0, MIXED_RETENTION] is classified asautodata, and data afterMIXED_RETENTIONis classified ascolddata.
- If
GRANULARITY = "partition": Optional. Specifies the operation granularity for classifying data as hot or cold, indicating that the caching strategy is processed at the partition level.
Examples
Modify table
tbl1so that the system automatically identifies hot data.obclient> ALTER TABLE tbl1 SET STORAGE_CACHE_POLICY (GLOBAL = "auto");Modify table
tbl2_dateso that hot data identified bycol3becomes cold data directly after being considered hot for 2 months.obclient> ALTER TABLE tbl2_date SET STORAGE_CACHE_POLICY ( BOUNDARY_COLUMN = col3, HOT_RETENTION = 2 MONTH, MIXED_RETENTION = 0 HOUR);Modify the timeline strategy for table
tbl2_intso that hot data becomesAUTOafter 3 years.obclient> ALTER TABLE tbl2_int STORAGE_CACHE_POLICY ( BOUNDARY_COLUMN = col3, BOUNDARY_COLUMN_UNIT = 'ms', HOT_RETENTION = 3 YEAR);
Modify a hot/cold storage policy for an index table
You can use the ALTER TABLE statement to modify the hot/cold storage policy (STORAGE_CACHE_POLICY) of an existing index. The syntax is as follows:
ALTER TABLE table_name ALTER INDEX index_name STORAGE_CACHE_POLICY (storage_cache_policy_option);
storage_cache_policy_option:
GLOBAL = {"hot" | "auto" | "cold" | "none"}
| timeline_strategy_list
timeline_strategy_list:
| BOUNDARY_COLUMN = column_name
| BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
| HOT_RETENTION = intnum retention_time_unit
| MIXED_RETENTION = intnum retention_time_unit
| GRANULARITY = "partition"
retention_time_unit:
YEAR
| MONTH
| WEEK
| DAY
| HOUR
| MINUTE
Related parameters
GLOBAL = {"hot" | "auto" | "cold" | "none"}: Specifies the hot/cold storage policy for index data. Valid values:hot: Specifies the index for hot storage. If sufficient cache disk space is available, all data under the index table will be cached to the local cache disk.auto: Indicates that the system automatically identifies hot data for the index table.cold: Indicates that the index table data is cold data, which will not be cached to the local cache disk. For data already persisted to the cache, cache eviction is actively triggered. Subsequent accesses to the index data can be stored in the macroblock memory cache and will not be persisted to the macroblock cache.none: The default value, indicating that the index's policy follows the primary table'sSTORAGE_CACHE_POLICYvalue.
timeline_strategy_list: Indicates the list of timeline strategy parameters, separated by commas. The timeline strategy determines whether partition data is hot or cold based on time, and the system automatically adjusts the data on the local cache disk according to the configured strategy.Note
Timeline: Hot data caching is performed based on the partition range defined by the RANGE partitioning. When certain time conditions are met, the data in that partition is identified as hot data. When using the timeline strategy, note the following:
- Only supported for RANGE-partitioned tables (RANGE partitioning can be at the primary or secondary level), as time is needed to determine expiration. However, it cannot be a dual RANGE partition, as it would be unclear which RANGE partition time to use.
- When using the timeline strategy, the partition expression can only contain column names; expression conversion is not supported. For example,
PARTITION BY RANGE COLUMNS(expr(col3)). BOUNDARY_COLUMNmust be the partitioning key. If the partitioning key consists of multiple columns,BOUNDARY_COLUMNmust be the first column, used to determine whether the partition has expired.
BOUNDARY_COLUMN = column_name: Specifies the column for identifying hot data. Supports integer types (BIGINTorINT, in Unix timestamp format) and time types (TIMESTAMP,DATE,DATETIME, orYEAR).- If
BOUNDARY_COLUMNis of an integer type, the table's partitioning method supports RANGE/RANGE COLUMNS partitioning. - If
BOUNDARY_COLUMNis of a time type, the table's partitioning method can only use RANGE COLUMNS partitioning.
- If
BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: Specifies the time unit for theBOUNDARY_COLUMNparameter. This parameter is only supported whenBOUNDARY_COLUMNis an integer. When an integer value is used as a timestamp, a timestamp unit must be provided; otherwise, it may lead to incorrect time parsing. Valid values:- When the partitioning column is of type
INT, the value forBOUNDARY_COLUMN_UNITcan only bes. - When the partitioning column is of type
BIGINT, the value forBOUNDARY_COLUMN_UNITcan besorms.
- When the partitioning column is of type
HOT_RETENTION = intnum retention_time_unit: Configures the time range for hot data.intnum: Represents an integer.retention_time_unit: Represents a time unit. Valid values:YEAR: Represents a year.MONTH: Represents a month.WEEK: Represents a week.DAY: Represents a day.HOUR: Represents an hour.MINUTE: Represents a minute.
MIXED_RETENTION = intnum retention_time_unit:- If
MIXED_RETENTIONis set, data beyond the time range ofHOT_RETENTION + MIXED_RETENTIONis consideredcolddata. - If
MIXED_RETENTIONis not set, data becomesautodata after reachingHOT_RETENTION. - If
HOT_RETENTIONis not set, the time range [0, MIXED_RETENTION] isautodata, and data afterMIXED_RETENTIONiscolddata.
- If
GRANULARITY = "partition": Optional. Specifies the operation granularity for hot/cold data storage, indicating that the caching strategy processes data at the partition level.
Examples
Modify index idx2 so that the system automatically identifies its hot data.
ALTER TABLE test_tbl2 ALTER INDEX idx2
STORAGE_CACHE_POLICY (GLOBAL = "auto");