PolarDB:Use the cold data archiving feature

Archive cold data

To archive cold data, you must first enable cold data archiving. You can archive common tables or partitioned tables based on your business requirements.

• Common tables

  You can manually archive common tables in the CSV format. For more information, see Archive common tables.

• Partitioned tables

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

  • You can create a data lifecycle management (DLM) policy for automatic cold data archiving. For more information, see Automatically archive cold data.

Query archived cold data

• Common tables

  Regardless of the cold data archive format you use, you can query the archived data of a common table by specifying the name of the table.

• Partitioned tables

  • Archive table partitions to an OSS foreign table: You can query the archived data only by specifying the archive table name. For more information, see Archive table partitions to an OSS foreign table.

  • Archive partitioned tables: After specific partitions of a table are archived to OSS, the table becomes a hybrid partitioned table. For information about how to query the archived data, see Query data in a hybrid partitioned table.

    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 archived 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 archived cold data

After you delete a table from OSS or move a table from OSS back to the PolarDB storage space, the corresponding file in OSS is retained. If you no longer use the data, you can perform the following steps to delete the corresponding file in OSS. For more information, see Delete the corresponding file in OSS.

• Common tables and OSS foreign tables

  First execute the DROP TABLE statement to delete the archive table, and then execute the CALL dbms_oss.delete_table_file('database_name', 'table_name'); statement to delete the corresponding file in OSS.

• Partitioned tables

  Execute the CALL dbms_oss.delete_table_file('database_name', 'table_name'); statement to delete the corresponding file in OSS.

The deletion of files from OSS is an asynchronous operation. The deletion process is completed only after all nodes in the cluster no longer depend on the OSS files. During periods of high traffic, the deletion process may experience delays.