PolarDB:Cold data archiving

Archive cold data

• Archive to OSS: You must first enable cold data archiving. When archiving cold data, you can choose to archive common tables or partitioned tables.

  • Common tables

    • Manually archive common tables in CSV/ORC format.

  • Partitioned tables

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

    • Create a data lifecycle management (DLM) policy to automatically archive cold data. For more information, see Automatically archive cold data.

• Archive to X-Engine: You must first enable cold data archiving and the high-compression engine (X-Engine). When archiving cold data, you can choose to archive common tables or partitioned tables.

  • Common tables

    • Manually archive data in X-Engine row-store or column-store format.

  • Partitioned tables

    • You can manually archive specific partitions of a partitioned table to X-Engine partitions. For more information, see Archive data to X-Engine.

    • Create a data lifecycle management (DLM) policy to automatically archive cold data. For more information, see Archive partitioned tables to X-Engine.

Query cold data

• Common tables

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

• Partitioned tables

  • Archive to an OSS external table: Querying this data requires a different access method: you must query the specified archive table. For more information, see Archive partitions to an OSS external table.

  • Archive partitions in place: The table becomes a hybrid partitioned table after archiving. For more information, see Query hybrid partitions.

    You can query partition data stored across different engines by using one of the following three methods.

    • Set the hybrid_partition_query_mix_engine_enabled parameter to ON. When you run a query, the result set includes partition data from the InnoDB engine, X-Engine, and OSS. Example:

      -- t1 is a hybrid partitioned table
      SELECT * FROM t1;

    • Set the hybrid_partition_query_mix_engine_enabled parameter to OFF. When you run a query, the result set includes only partition data from the InnoDB engine and X-Engine. Example:

      -- t1 is a hybrid partitioned table
      SELECT * FROM t1;

    • Specify a partition name: You can retrieve partition data from any engine type by specifying the partition name in your query. Example:

      -- t1 is a hybrid partitioned table, and p1 is the name of the partition to query.
      SELECT * FROM t1 partition (p1);

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 on OSS or import it back to PolarDB storage space, the files on OSS are not automatically deleted. After you confirm that the data is no longer needed, delete the corresponding files on OSS as follows. For more information, see Delete the corresponding files on OSS.

• Common tables and OSS external tables

  Use DROP TABLE to delete the archived table. Then, run the CALL dbms_oss.delete_table_file('database_name', 'table_name'); command to delete the corresponding files on OSS.

• Partitioned tables

  Run the CALL dbms_oss.delete_table_file('database_name', 'table_name'); command to delete the corresponding files on OSS.

The operation to delete the corresponding files on OSS is performed asynchronously. The files are completely deleted only after all nodes in the cluster no longer depend on them. This process may be delayed when traffic is heavy.