Modify the cold and hot storage policy at the table level

This topic describes how to use SQL statements to modify the cold and hot storage policy (STORAGE_CACHE_POLICY) at the table level:

• Modify the cold and hot storage policy of a regular table.
• Modify the cold and hot storage policy of an index table.

Considerations

You can modify the cold and hot storage strategy of a table/index only in shared storage mode.

Modify the cold and hot storage strategy of a regular table

After a table is created, you can use the ALTER TABLE statement to modify the cold and hot storage strategy (STORAGE_CACHE_POLICY) of the table. Syntax:

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" | "block"}

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

Parameter description

The storage_cache_policy_option parameter defines different attributes in the key-value format. The attributes are described as follows:

• GLOBAL = {"hot" | "auto" | "cold"}: specifies the cold and hot storage strategy for all data in the table. Valid values:

  • hot: indicates that all data in the table is hot data. If the cache disk space is sufficient, all data in the table will be cached to the local cache disk.
  • auto: indicates that the hot data in the table is automatically identified by the system.
  • cold: indicates that the data in the table is cold data, which will not be cached to the local cache disk. For data that has been persisted to the cache, the system will actively trigger cache eviction. Subsequent accesses to the table data can be stored in the macroblock memory cache, which will not be persisted to the macroblock cache and microblock cache.

• timeline_strategy_list: specifies the list of time-axis strategy parameters. Parameters are separated by commas. The time-axis strategy supports a mechanism to determine the cold and hot storage of partitions/macros based on time. The system automatically adjusts the data on the local cache disk based on the configured strategy.

  Note

  A time-axis: caches hot data based on the partition range defined by the RANGE partition. When a certain time condition is met, the data in the partition is determined to be hot data. When using the time-axis strategy, please note the following:

  • Only RANGE partitioned tables (RANGE partitions can be at the first or second level) are supported, because time is needed to determine whether the data has expired. Double RANGE partitions are not supported, as it is unclear which RANGE partition's time to use.
  • When using the time-axis strategy, the partition expression can only contain column names and does not support expression conversion. For example, PARTITION BY RANGE COLUMNS(expr(col3)) is not supported.
  • BOUNDARY_COLUMN must be a partition key. If the partition key consists of multiple columns, BOUNDARY_COLUMN must be the first column to determine whether the partition has expired.

  • BOUNDARY_COLUMN = column_name: specifies the column used to determine hot data. Supported types: integer (BIGINT or INT type, in the Unix timestamp format) and time (TIMESTAMP, DATE, DATETIME, or YEAR type).

    • If BOUNDARY_COLUMN is of the integer type, the table can be partitioned by RANGE or RANGE COLUMNS.
    • If BOUNDARY_COLUMN is of the time type, the table can only be partitioned by RANGE COLUMNS.

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: specifies the time unit for the BOUNDARY_COLUMN parameter. This parameter is only supported when BOUNDARY_COLUMN is of the integer type. When using an integer value as a timestamp, you must specify the timestamp unit to avoid incorrect parsing of the timestamp. Valid values:

    • If the partition column is of the INT type, BOUNDARY_COLUMN_UNIT can only be s.

    • If the partition column is of the BIGINT type, BOUNDARY_COLUMN_UNIT can be s or ms.

      Notice

      If the format is not a Unix timestamp, the system cannot correctly identify the time represented by the INT type.

  • HOT_RETENTION = intnum retention_time_unit: specifies the time range for hot data.

    • intnum: specifies an integer.

    • retention_time_unit: specifies the time unit. Valid values:

      • YEAR: specifies years.
      • MONTH: specifies months.
      • WEEK: specifies weeks.
      • DAY: specifies days.
      • HOUR: specifies hours.
      • MINUTE: specifies minutes.

  • MIXED_RETENTION = intnum retention_time_unit:

    • If MIXED_RETENTION is specified, data after HOT_RETENTION + MIXED_RETENTION is considered cold data.
    • If MIXED_RETENTION is not specified, data after HOT_RETENTION is considered auto data.
    • If neither HOT_RETENTION nor MIXED_RETENTION is specified, data in the range [0, MIXED_RETENTION] is considered auto data, and data after MIXED_RETENTION is considered cold data.

  • GRANULARITY = {"partition" | "block"}: optional. Specifies the granularity of the cold and hot storage strategy. Valid values:

    • partition: specifies that the cache strategy is applied at the partition level.

    • block: specifies that the cache strategy is applied at the macroblock level.

      Note

      If the table is not partitioned, you can specify block to manually mark data within a certain time period as hot data. For more information about the macroblock-level cold and hot storage strategy, see Create a macroblock-level cold and hot storage strategy.

Examples

• Modify the hot cache strategy of table tbl1 to be automatically identified by the system.

  obclient> ALTER TABLE tbl1 SET STORAGE_CACHE_POLICY (GLOBAL = "auto");
  

• Modify the hot cache strategy of table tbl2_date to be maintained for 2 months and then directly become cold data based on the col3 column.

  obclient> ALTER TABLE tbl2_date SET STORAGE_CACHE_POLICY (
      BOUNDARY_COLUMN = col3,
      HOT_RETENTION = 2 MONTH,
      MIXED_RETENTION = 0 HOUR);
  

• Modify the time-axis strategy of table tbl2_int to be automatically identified by the system 3 years later.

  obclient> ALTER TABLE tbl2_int STORAGE_CACHE_POLICY (
      BOUNDARY_COLUMN = col3,
      BOUNDARY_COLUMN_UNIT = 'ms',
      HOT_RETENTION = 3 YEAR);
  

Modify the cold and hot storage strategy of an index table

You can use the ALTER TABLE statement to modify the cold and hot storage strategy (STORAGE_CACHE_POLICY) of an existing index table. The syntax is as follows:

ALTER TABLE table_name ALTER INDEX 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" | "block"}

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

Parameters

• GLOBAL = {"hot" | "auto" | "cold" | "none"}: specifies the cold and hot storage strategy for the index data. Valid values:

  • hot: indicates that the index data is stored in the cache. If the cache space is sufficient, all data in the index table is cached to the local cache disk.
  • auto: indicates that the system automatically identifies the hot data in the index table.
  • cold: indicates that the data in the index table is cold data and will not be cached to the local cache disk. For data that has been persisted to the cache, the system actively triggers cache eviction. Subsequent accesses to the table data can be stored in the macroblock memory cache, without being persisted to the macroblock cache or microblock cache.
  • none: default value. The strategy of the index table follows that of the primary table STORAGE_CACHE_POLICY.

• timeline_strategy_list: specifies the timeline strategy parameters. Parameters are separated by commas. The timeline strategy allows the system to automatically adjust the data on the local cache disk based on the configured strategy. The system determines whether to cache data to the local cache disk based on the specified time conditions.

  Note

  A timeline is a range partition defined by the partition range. The system caches hot data in the partition range. When a certain time condition is met, the data in the partition is determined to be hot data. When you use the timeline strategy, note the following points:

  • Only range-partitioned tables are supported. The range partition can be a primary or secondary partition. This is because the system needs to determine whether the data has expired based on the time. However, double range partitions are not supported. This is because the system cannot determine which range partition to use for time-based expiration.
  • When you use the timeline strategy, the partition expression can contain only column names. Expressions are not supported. For example, PARTITION BY RANGE COLUMNS(expr(col3)) is not supported.
  • BOUNDARY_COLUMN must be a partition key. If the partition key contains multiple columns, BOUNDARY_COLUMN must be the first column to determine whether the partition has expired.

  • BOUNDARY_COLUMN = column_name: specifies the column used to determine the hot data. Supported data types: integer (BIGINT or INT, in the Unix timestamp format) and time (TIMESTAMP, DATE, DATETIME, or YEAR).

    • If the BOUNDARY_COLUMN is of the integer type, the table can be partitioned by range or range columns.
    • If the BOUNDARY_COLUMN is of the time type, the table can be partitioned only by range columns.

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: specifies the time unit of the BOUNDARY_COLUMN. This parameter is supported only when the BOUNDARY_COLUMN is of the integer type. When the integer value is used as a timestamp, you must specify the timestamp unit to avoid incorrect parsing of the timestamp. Valid values:

    • When the partition column is of the INT type, the BOUNDARY_COLUMN_UNIT can be only s.
    • When the partition column is of the BIGINT type, the BOUNDARY_COLUMN_UNIT can be s or ms.

  • HOT_RETENTION = intnum retention_time_unit: specifies the time range for hot data.

    • intnum: specifies an integer.

    • retention_time_unit: specifies the time unit. Valid values:

      • YEAR: year.
      • MONTH: month.
      • WEEK: week.
      • DAY: day.
      • HOUR: hour.
      • MINUTE: minute.

  • MIXED_RETENTION = intnum retention_time_unit:

    • If you set MIXED_RETENTION, data that is older than HOT_RETENTION + MIXED_RETENTION is considered cold data.
    • If you do not set MIXED_RETENTION, data that is older than HOT_RETENTION is considered auto data.
    • If you do not set HOT_RETENTION, data in the [0, MIXED_RETENTION] time range is considered auto data, and data that is older than MIXED_RETENTION is considered cold data.

  • GRANULARITY = {"partition" | "block"}: optional. Specifies the granularity of the cold and hot storage strategy. Valid values:

    • partition: specifies that the cache strategy is applied at the partition level.

    • block: specifies that the cache strategy is applied at the macroblock level.

      Note

      If you do not specify a partitioning strategy for a table, you can specify block to manually identify hot data within a specified time period. For more information about how to use the macroblock-level cold and hot storage strategy, see Create a macroblock-level cold and hot storage strategy.

Examples

Set the hot data of the idx2 index table to be automatically identified by the system.

ALTER TABLE test_tbl2 ALTER INDEX idx2
    STORAGE_CACHE_POLICY (GLOBAL = "auto");

References

• Overview of custom cold and hot storage strategies
• Create a table-level cold and hot storage strategy
• Create a partition-level cold and hot storage strategy
• Modify a partition-level cold and hot storage strategy
• ALTER TABLE
• Operations for custom cold and hot storage strategies