How to create a hybrid partitioned table to access data - PolarDB

You can create a hybrid partitioned table to access data stored in different engines.

The following figure shows how hybrid partitioning works. 混合分区

After you create a hybrid partitioned table, data in different partitions in the partitioned table is stored on different storage media to separate hot data from cold data. For example, you can store hot data in the local PolarFileSystem and store cold data in OSS. This greatly reduces the storage costs of cold data without affecting the query performance of hot data or affecting DML operations on hot data.

Note

This feature is in the canary release phase. To use this feature, go to Quota Center. Find the Quota Name value corresponding to the Quota ID value of polardb_mysql_hybrid_partition. Click Apply in the Actions column.
For more information about creating a hybrid partitioned table, join the DingTalk group 24490017825.

Prerequisites

A cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.5 or later is used. You can check the cluster version as specified in Query the engine version.

The data file of the partition to be created already exists in OSS. The naming rules for the data file:
- The extension and partition sign must be in uppercase.
- The table name and partition name are the same as those in the database.
Example: If the p1 partition of the t1 table uses the CSV engine, the data file for the partition is named t1#P#p1.CSV.

Precautions

A hybrid partitioned table must contain at least one partition that uses the InnoDB engine.
When you create a hybrid partitioned table that contains subpartitions, the partition must use the same engine as that of the table and at least one subpartition must use the InnoDB engine.
Hybrid partitioned tables support only RANGE and LIST data types.
The following DML statements are not supported on a hybrid partitioned table with partitions that use the OSS engine: INSERT, UPDATE, DELETE, and LOAD.
The following statements are supported on a hybrid partitioned table in a cluster of PolarDB for MySQL 8.0.2.2.17 or later: ADD and DROP. These statements are not supported on a hybrid partitioned table in a cluster of other PolarDB for MySQL versions.

Parameters

You can set the following parameters on the Parameters page of the PolarDB cluster based on your business requirements.

Parameter

Description

hybrid_partition_query_mix_engine_enabled

Specifies whether to query data of the partitions that do not use the InnoDB engine in a hybrid partitioned table. Valid values:

ON
OFF (default)

Create a hybrid partitioned table

You can create a hybrid partitioned table by using an existing OSS server. If no OSS server exists, you can execute the following statement to create one:

CREATE SERVER oss_server_name
FOREIGN DATA WRAPPER oss
OPTIONS(EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>",
"oss_bucket": "<my_oss_bucket>", "oss_access_key_id": "<my_oss_access_key_id>",
"oss_access_key_secret": "<my_oss_access_key_secret>", "oss_prefix":"<my_oss_prefix>", "oss_sts_token": "<my_oss_sts_token>"}');

Note

The my_oss_sts_token parameter is supported on a cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.6 or later.

The following table describes the parameters in the statement.

Parameter	Type	Description
oss_server_name	String	The name of the OSS server. Note The name must be globally unique. It is not case-sensitive. The name can be up to 64 characters in length. A name that contains more than 64 characters is automatically truncated. You can specify the OSS server name as a quoted string.
my_oss_endpoint	String	The endpoint of the OSS server. Note If you access your database from an Alibaba Cloud server, use an internal endpoint to avoid incurring Internet traffic. An internal endpoint contains keyword internal.
my_oss_bucket	String	The bucket where the data file is stored. You must create OSS buckets before you import data.
my_oss_access_key_id	String	The AccessKey ID of the account used to access OSS.
my_oss_access_key_secret	String	The AccessKey secret of the account used to access OSS.
my_oss_prefix	String	The prefix of the OSS path.
my_oss_sts_token	String	The temporary credential used to access OSS. For information about how to obtain temporary credentials used to access OSS, see Use temporary credentials provided by STS to access OSS. Note The `my_oss_sts_token` value has a default expiration time. If the `my_oss_sts_token` value expires, you must execute the following statement to reset all `EXTRA_SERVER_INFO` values: `ALTER SERVER server_name OPTIONS(EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>", "oss_bucket": "<my_oss_bucket>", "oss_access_key_id": "<my_oss_access_key_id>", "oss_access_key_secret": "<my_oss_access_key_secret>", "oss_prefix":"<my_oss_prefix>", "oss_sts_token": "<my_oss_sts_token>"}');`

Create a hybrid partition table. Example:

CREATE TABLE t2(a1 INT, a2 VARCHAR(30), a3 VARCHAR(256))
CONNECTION = "oss_server_name"
PARTITION BY RANGE(a1)
(
   PARTITION p1 values less than (1000) ENGINE = CSV,
   PARTITION p2 values less than (2000) ENGINE = CSV,
   PARTITION p3 values less than (3000) ENGINE = INNODB
);

Query data in a hybrid partition 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.