This topic describes how to create a table-level hot/cold storage policy (STORAGE_CACHE_POLICY) by using SQL statements:
- Create a hot/cold storage policy for a regular table.
- Create a hot/cold storage policy for an index table.
Create a hot/cold storage policy for a regular table
To specify a table-level hot/cold storage policy when you create a table, add the STORAGE_CACHE_POLICY option after the CREATE TABLE statement. The syntax is as follows:
CREATE TABLE table_name (table_definition_list)
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
STORAGE_CACHE_POLICY is optional. If it is not specified, the value of the tenant-level parameter default_storage_cache_policy is used. 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 table data. 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 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:
- Only supported for RANGE-partitioned tables (RANGE partitioning can be at the primary or secondary level), as 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: The column used to identify hot data. Supports integer types (BIGINTorINT, in Unix timestamp format) and time types (TIMESTAMP,DATE,DATETIME, orYEAR).- If
BOUNDARY_COLUMNis an integer, the table's partitioning method supports RANGE/RANGE COLUMNS partitioning. - If
BOUNDARY_COLUMNis 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 cannot 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 a year.MONTH: Represents a month.WEEK: Represents a week.DAY: Represents a day.HOUR: Represents an hour.MINUTE: Represents a minute.
Notice
You must set at least one of the parameters
HOT_RETENTIONandMIXED_RETENTION.MIXED_RETENTION = intnum retention_time_unit:- If you set
MIXED_RETENTION, data beyond the time range ofHOT_RETENTION + MIXED_RETENTIONis consideredcolddata. - If you do not set
MIXED_RETENTION, data becomesautodata after reachingHOT_RETENTION. - If
HOT_RETENTIONis not set, the time range [0, MIXED_RETENTION] is consideredautodata, and data afterMIXED_RETENTIONis consideredcolddata.
- If you set
GRANULARITY = "partition": Optional. Specifies the operation granularity for hot/cold data storage, indicating that the caching strategy processes data at the partition level.
Examples
Manually specify hot data.
obclient> CREATE TABLE tbl1 ( col1 INT, col2 INT) STORAGE_CACHE_POLICY (GLOBAL = "hot");Create a table
tbl2_datewhose partitioning column is of theDATE(DATE/DATETIME/TIMESTAMP/YEAR) type. After hot data is retained for 1 month, it directly becomes cold data.obclient> CREATE TABLE tbl2_date ( col1 BIGINT NOT NULL, col2 VARCHAR(50), col3 DATE NOT NULL) STORAGE_CACHE_POLICY ( BOUNDARY_COLUMN = col3, HOT_RETENTION = 1 MONTH, MIXED_RETENTION = 0 HOUR) PARTITION BY RANGE COLUMNS(col3) ( PARTITION M202001 VALUES LESS THAN('2020/02/01'), PARTITION M202002 VALUES LESS THAN('2020/03/01'), PARTITION M202003 VALUES LESS THAN('2020/04/01'), PARTITION MMAX VALUES LESS THAN MAXVALUE);Create a table
tbl2_intwhose partitioning column is of theBIGINT(INT/BIGINT, etc.) type. You must specifyBOUNDARY_COLUMN_UNIT; by default, data is processed based on the UTC timestamp. After hot data is retained for 1 month, it becomesautodata, and after another week, it becomes cold data.obclient> CREATE TABLE tbl2_int ( col1 BIGINT NOT NULL, col2 VARCHAR(50), col3 BIGINT NOT NULL) STORAGE_CACHE_POLICY ( BOUNDARY_COLUMN = col3, BOUNDARY_COLUMN_UNIT = "ms", HOT_RETENTION = 1 MONTH, MIXED_RETENTION = 1 WEEK) PARTITION BY RANGE COLUMNS(col3) ( PARTITION M202001 VALUES LESS THAN(1580515200000), PARTITION M202002 VALUES LESS THAN(1583020800000), PARTITION M202003 VALUES LESS THAN(1585699200000), PARTITION MMAX VALUES LESS THAN MAXVALUE);
Specify a hot/cold storage policy for an index table
You can specify a hot/cold storage policy for an index in the following ways:
- Specify the table-level hot/cold storage policy of the index when you create an index.
- Specify the table-level hot/cold storage policy of the index when you create an index along with a table.
- Specify the table-level hot/cold storage policy of the index when you add an index to an existing table.
For more information about creating an index, see Create an index.
Specify a table-level hot/cold storage policy when you create an index
The syntax is as follows:
CREATE INDEX index_name ON table_name (column_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
Specify a table-level hot/cold storage policy when you create an index along with a table
The syntax is as follows:
CREATE TABLE table_name (
column_name column_definition,
[column_name column_definition,...],
INDEX index_name(column_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
Specify a table-level hot/cold storage policy when you add an index to an existing table
The syntax is as follows:
ALTER TABLE table_name ADD INDEX index_name(column_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
You can configure a hot/cold storage policy for an index table:
For a local index, you can specify the table-level
STORAGE_CACHE_POLICY. If the table-levelSTORAGE_CACHE_POLICYis not specified, the default isnone, which means theSTORAGE_CACHE_POLICYconfiguration of the data table is used. The hot/cold storage policy for the partitions of the local index follows the partitioning rules of the data table.For a global index, in addition to specifying the table-level
STORAGE_CACHE_POLICY, if the index table has partitions, you can also specify the partition-levelSTORAGE_CACHE_POLICYfor the index table. If the table-levelSTORAGE_CACHE_POLICYis not specified for a global index, the default isnone.Note
If the table-level
STORAGE_CACHE_POLICYis not specified for a global index, and the primary table uses the Global strategy, the index follows the primary table'sSTORAGE_CACHE_POLICYby default. If the primary table uses the timeline strategy, the index defaults to thehotstrategy.
When you set the table-level STORAGE_CACHE_POLICY, the attributes in the storage_cache_policy_option parameter are defined in Key-Value format. The semantics of each attribute are as follows:
GLOBAL = {"hot" | "auto" | "cold" | "none"}: Specifies the hot/cold storage policy for index data. Valid values:hot: Specifies the index as hot storage. If sufficient cache disk space is available, all data under the index table will be cached to the local cache disk.auto: Specifies 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 value of the primary table'sSTORAGE_CACHE_POLICY.
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 is cached 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 primary or secondary). This is because time is needed to determine if a partition has expired. However, it cannot be a dual RANGE partition, as it would be impossible to determine which RANGE partition's 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: The column used to identify 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.
- 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 within 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
Specify the table-level
STORAGE_CACHE_POLICYof the index when you create the index:Create table
tbl3.CREATE TABLE tbl3 (col1 INT, col2 INT, col3 INT);Create index
idx1_tbl3and specify its data as hot data.CREATE INDEX idx1_tbl3 ON tbl3 (col1) LOCAL STORAGE_CACHE_POLICY (GLOBAL = "hot");
Create an index when you create a table, and specify the index data as hot data.
CREATE TABLE tbl4 (col1 INT, col2 INT, INDEX idx2 ((col1 + 1)) STORAGE_CACHE_POLICY (GLOBAL = "hot"), UNIQUE KEY ((col1 + col2)));Specify the index data as hot data when you add an index to an existing table.
ALTER TABLE tbl4 ADD INDEX idx3(col1, col2) GLOBAL STORAGE_CACHE_POLICY (GLOBAL = "hot");