PolarDB:cold data archiving usage guide

Archive Cold Data

• Archive to OSS: First, enable cold data archiving. When archiving cold data, archive standard tables or partitioned tables as needed.

  • Standard tables

    • Manually archive standard tables as CSV or ORC format.

  • Partitioned tables

    • Manually archive some partitioned data or archive it to OSS external tables. For more information, see Archive partitioned tables or Archive partitioned tables to OSS external tables.

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

• Archive to X-Engine: First, enable cold data archiving and high-compression engine (X-Engine). When archiving cold data, archive standard tables or partitioned tables as needed.

  • Standard tables

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

  • Partitioned tables

    • Manually archive some partitioned data to X-Engine partitions. For more information, see Archive as X-Engine engine format.

    • Create a DLM policy to automatically archive cold data. For more information, see Archive partitioned tables as X-Engine engine format.

Query Cold Data

• Standard tables

  You can query archived data without changing the table access method, regardless of the archiving format.

• Partitioned tables

  • Archive to OSS external tables: Modify the table access method to query archived data. This means querying the specified archived table. For more information, see Archive partitioned tables to OSS external tables.

  • Archive partitioned tables: The archived table is a hybrid partitioned table. For more information, see Query hybrid partitions.

    You can query partition data across different storage engines in one of the following three ways:

    • Set the hybrid_partition_query_mix_engine_enabled parameter to ON. This allows queries to return data from InnoDB, X-Engine, and OSS partitions. For example:

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

    • Set the hybrid_partition_query_mix_engine_enabled parameter to OFF. Queries will then return data only from InnoDB and X-Engine partitions. For example:

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

    • Specify a partition name. This lets you query data from a partition that uses any type of storage engine. For example:

      -- t1 is a hybrid partitioned table. p1 is the partition name.
      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 tables on OSS or import them back to PolarDB storage, files on OSS are not automatically deleted. After confirming that the data is no longer in use, delete the corresponding files on OSS as follows. For more information, see Delete corresponding files on OSS.

• Standard tables and OSS external tables

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

• Partitioned tables

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

Deleting corresponding files on OSS is an asynchronous operation. Complete deletion occurs only after all nodes in the cluster no longer depend on the OSS file. Additionally, this process might experience some delay during high traffic.

FORCE STORAGE OSS Option

• Standard tables and OSS external tables

  DROP TABLE table_name [FORCE STORAGE OSS];

  Note

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

• Partitioned tables

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

  Note

  The DROP PARTITION clause of the ALTER TABLE statement, combined with the FORCE STORAGE OSS option, enables synchronous deletion of partitioned table metadata and 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 provides a force overwrite mechanism, allowing automatic replacement of files with the same name.