PolarDB:cold data archiving usage guide

CSV format

ORC format

X-Engine format

Applicable scope

• Archiving in CSV format

  • If the product edition is Cluster Edition, the Milvus version must be one of the following:

    • MySQL 8.0.1, minor version 8.0.1.1.47 or later.

    • MySQL 8.0.2, revision version 8.0.2.2.10 or later.

  • For the Multi-master Cluster (Limitless) Edition, the kernel version must be 8.0.1.0.13 or later.

• Archiving in ORC format

  • For the Cluster Edition, the revision version must be 8.0.2.2.30 or later.

  • For the Multi-master Cluster (Limitless) Edition, the revision version must be 8.0.2.2.30 or later.

• Archiving in X-Engine format

  • Archiving standard tables:

    • MySQL 8.0.1 with revision 8.0.1.1.31 or later.

    • MySQL 8.0.2 with revision 8.0.2.2.12 or later.

  • Archiving partitioned tables: MySQL 8.0.2 with revision 8.0.2.2.12 or later.

  • Archiving as an X-Engine column-oriented table: MySQL 8.0.2 with revision 8.0.2.2.33 or later.

To modify cold data on OSS infrequently, use the ALTER syntax to import cold data from OSS back to PolarDB storage space for modification. After importing, the cold data on OSS is simultaneously deleted. After modifying the data, archive the modified table data to OSS again.

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));

Archive to OSS

Enable cold data archiving, then choose the archiving method based on your table type.

Standard tables

• Manually archive as CSV or ORC format

Partitioned tables

• Manually archive some partitions to OSS: Archive partitioned tables. To move partitions into a separate table, see Archive partitioned tables to OSS external tables

• Create a Data Lifecycle Management (DLM) policy to automatically archive cold data (CSV format only): Automatically archive cold data

Archive to X-Engine

Enable cold data archiving and enable the high-compression engine (X-Engine), then choose the archiving method based on your table type.

Standard tables

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

Partitioned tables

• Manually archive partitions to X-Engine: Archive as X-Engine format

• Create a DLM policy to automatically archive cold data: Archive partitioned tables as X-Engine format

Standard tables

Query archived data without changing how you access the table, regardless of the archiving format.

Partitioned tables

Query behavior depends on how the partition was archived:

• Archived to an OSS external table: Modify the table access method to query archived data. Access the specific archived table directly. For details, see Archive partitioned tables to OSS external tables.

• Archived in place (hybrid partitioned table): The original table becomes a hybrid partitioned table containing both hot and cold partitions. Query partition data across different storage engines in one of the following ways: For more details, see Query hybrid partitions.

  • Set hybrid_partition_query_mix_engine_enabled to ON to return data from InnoDB, X-Engine, and OSS partitions: ``sql -- t1 is a hybrid partitioned table. SELECT * FROM t1; ``

  • Set hybrid_partition_query_mix_engine_enabled to OFF to return data only from InnoDB and X-Engine partitions: ``sql -- t1 is a hybrid partitioned table. SELECT * FROM t1; ``

  • Specify a partition name to query data from a single partition of any storage engine type: ``sql -- t1 is a hybrid partitioned table. p1 is the partition name. SELECT * FROM t1 PARTITION (p1); ``

Archived cold data is stored in a single-table, multi-file format. Use parallel query for better performance when querying cold data. For details, see Cold data parallel query.

Standard tables

Syntax

ALTER TABLE table_name ENGINE[=]engine_name;

Parameters

Example

Import the OSS table t in the oss_test database back to PolarDB storage:

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

Modify the data of the InnoDB table t, then re-archive it to OSS:

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

Or:

ALTER TABLE t ENGINE = CSV STORAGE OSS;

Attempting to run INSERT, UPDATE, or DELETE on a read-only OSS table returns the following error: 1036 - Table 't' is read only

Partitioned tables

Syntax

ALTER TABLE table_name REORGANIZE PARTITION part_name INTO (partition_definition);

Parameters

Example

Import partition p1 from a partitioned table t archived on OSS back to PolarDB storage:

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

Standard tables and OSS external tables

1. Drop the archived table:

   DROP TABLE table_name;

2. Delete the corresponding OSS files:

   CALL dbms_oss.delete_table_file('database_name', 'table_name');

Partitioned tables

Delete the corresponding OSS files:

CALL dbms_oss.delete_table_file('database_name', 'table_name');

FORCE STORAGE OSS option

The FORCE STORAGE OSS option requires PolarDB for MySQL version 8.0.2 with minor version 8.0.2.2.29 or later.

Use FORCE STORAGE OSS to delete associated OSS files in a single step, without running dbms_oss.delete_table_file separately.

Standard tables and OSS external tables

DROP TABLE table_name [FORCE STORAGE OSS];

FORCE STORAGE OSS automatically deletes the associated OSS storage files when you drop the table schema.

Partitioned tables

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

FORCE STORAGE OSS enables synchronous deletion of the partition metadata and its corresponding OSS storage files.

CHANGE PARTITION ENGINE

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

FORCE STORAGE OSS enables force overwrite, automatically replacing OSS files with the same name.