Modify the partition-level hot and cold storage strategy

This topic describes how to use SQL statements to modify the partition-level hot and cold storage strategy (STORAGE_CACHE_POLICY).

Considerations

You can modify the partition-level hot and cold storage strategy only in shared storage mode.

Modify the partition-level hot and cold storage strategy

After you create a partitioned table, you can use the ALTER TABLE statement to specify the STORAGE_CACHE_POLICY when you add a partition and to modify the STORAGE_CACHE_POLICY of a partition. The syntax is as follows:

• Specify the STORAGE_CACHE_POLICY when you add a partition:

  ALTER TABLE table_name
      ADD PARTITION (partition_list [STORAGE_CACHE_POLICY = {"hot" | "auto" | "cold" | "none"}]);
  

• Modify the STORAGE_CACHE_POLICY of a partition:

  ALTER TABLE table_name
      ALTER PARTITION (partition_list [STORAGE_CACHE_POLICY = {"hot" | "auto" | "cold" | "none"}]);
  

Parameters

The STORAGE_CACHE_POLICY at the partition level is optional. Valid values are as follows:

• hot: specifies that all data in the partition is hot data. If the cache disk space is sufficient, all data in the partition is cached to the local cache disk.
• auto: specifies that the hot data in the partition is automatically identified by the system.
• cold: specifies that the data in the partition is cold data. It is not cached to the local cache disk. For data that has been persisted to the cache, the cache eviction is actively triggered. Subsequent table data can be stored in the macroblock memory cache, and will not be persisted to the macroblock cache and microblock cache.
• none: the default value. The hot and cold storage strategy of the partition follows the value of the STORAGE_CACHE_POLICY at the upper level.

For more information about how to modify a partitioned table, see Add a partition and Modify the partitioning rule.

Examples

• Add a partition p2 to the tbl1 table. The hot data that needs to be cached to the local disk in the p2 partition is automatically identified by the system.

  ALTER TABLE tbl1 ADD PARTITION (PARTITION p2 VALUES LESS THAN(300) STORAGE_CACHE_POLICY = "auto");
  

• Modify the data in the primary partition p1 of the tbl1 table to hot data.

  ALTER TABLE tbl1 ALTER PARTITION p1 STORAGE_CACHE_POLICY = "hot";
  

• Modify the data in the subpartition sp3 of the tbl2 table to hot data that needs to be cached to the local disk. The hot data is automatically identified by the system.

  ALTER TABLE tbl2 ALTER SUBPARTITION sp3 STORAGE_CACHE_POLICY = "auto";
  

References

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