PolarDB:Usage of cold data archiving

Archive cold data

You must first enable cold data archiving. After the feature is enabled, you can archive data from standard tables or partitioned tables.

• Standard tables

  Manually archive standard tables in CSV or ORC format.

• Partitioned tables

  • You can manually archive one or more partitions of a partitioned table or archive them to an OSS external table. For more information, see Archive partitioned tables or Archive partitioned tables to OSS external tables.

  • You can create a Data Lifecycle Management (DLM) policy to automatically archive cold data. For more information, see Automatically archive cold data.

Query cold data

• Standard tables

  Regardless of the archive format you choose, you can query the archived data without changing the way you access the table.

• Partitioned tables

  • Archive to an OSS external table: To query the archived data, you must change your access method. This means you must query the specified archive table. For more information, see Archive partitioned tables to OSS external tables.

  • Archive partitioned tables: After archiving, the table becomes a hybrid partitioned table. For more information, see Query hybrid partitions.

    You can query data of partitions that use different engines by using one of the following methods:

    • If you set the hybrid_partition_query_mix_engine_enabled parameter to ON in the console, query results contain data in the partitions that do not use the InnoDB engine. Example:

      SELECT * FROM t1;

      t1 is the hybrid partitioned table.

    • If you set the hybrid_partition_query_mix_engine_enabled parameter to OFF in the console, you can query data in a specified partition. Example:

      SELECT * FROM t1 partition (p1);

      t1 is the hybrid partitioned table. p1 is the partition.

Modify cold data

ALTER TABLE table_name ENGINE[=]engine_name;

1036 - Table 't' is read only

ALTER TABLE `oss_test`.`t` ENGINE = InnoDB;

ALTER TABLE t ENGINE = CSV CONNECTION = 'default_oss_server';

ALTER TABLE t ENGINE = CSV STORAGE OSS;

ALTER TABLE table_name REORGANIZE PARTITION part_name INTO (partition_definition);

ALTER TABLE t REORGANIZE PARTITION p1 INTO(PARTITION p1 values less than(100));

Delete cold data

After you delete a table from OSS or move a table from OSS back to PolarDB, the corresponding file in OSS is not automatically deleted. If you confirm that the data is no longer needed, you can delete the corresponding file from OSS. For more information, see Delete the corresponding files from OSS.

• Standard tables and OSS external tables

  Delete the archived table using the DROP TABLE statement, and then delete the corresponding file in OSS using the CALL dbms_oss.delete_table_file('database_name', 'table_name'); command.

• Partitioned tables

  You can use the CALL dbms_oss.delete_table_file('database_name', 'table_name'); command to delete the corresponding file in OSS.

Deleting the corresponding file in OSS is an asynchronous operation. The file is completely deleted only after all nodes in the cluster no longer depend on it. In addition, this process may experience latency during periods of high traffic.

The FORCE STORAGE OSS option

• Standard tables and OSS external tables

  DROP TABLE table_name [FORCE STORAGE OSS];

  Note

  The FORCE STORAGE OSS option in the DROP TABLE command provides a one-step cleanup method. It automatically deletes the associated OSS storage files when you delete the table schema.

• Partitioned tables

  ALTER TABLE table_name DROP PARTITION part_name [FORCE STORAGE OSS];

  Note

  You can use the FORCE STORAGE OSS option with the DROP PARTITION clause of the ALTER TABLE statement to synchronously delete partition metadata and the corresponding OSS storage files.

• CHANGE PARTITION ENGINE

    ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV|ORC [FORCE STORAGE OSS]

  Note

  The CHANGE PARTITION ENGINE cold data archiving feature includes a forced overwrite mechanism that automatically replaces files with the same name.