How to create a hybrid partitioned table - PolarDB

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

The following figure shows how hybrid partitioning works.

A hybrid partitioned table stores partitions on different storage medium to separate hot data from cold data. For example, you can store hot data in the on-premises PolarFileSystem and store cold data in Object Storage Service (OSS). This greatly reduces the storage costs of cold data without compromising the query performance for 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, search for the corresponding quota by using the quota ID polardb_mysql_hybrid_partition, and then click Apply in the Actions column.

Prerequisites

Your cluster runs PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.5 or later. For information about how to check the cluster version, see the "Query the engine version" section of the Engine versions topic.

The data files of the partitions that you want to create already exist in OSS. The names of the data files comply with the following requirements:
- The file name extensions and partition markers are in uppercase.
- The table name and partition names are the same as those in the database.
Example: If partition p1 of table t1 uses the CSV engine, the name of the data file for the partition is 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 the 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.
If your cluster runs PolarDB for MySQL 8.0.2.2.17 or later, you can perform ADD and DROP operations on a hybrid partitioned table. If your cluster runs another version, you cannot perform ADD or DROP operations on a hybrid partitioned table.

Parameters

You can configure the parameters on the Parameters page of the PolarDB cluster based on your business requirements. The following table describes the parameters.

Parameter

Description

loose_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 (default)
OFF

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 an OSS server:

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

Only clusters that run PolarDB for MySQL V8.0.2 whose revision version is 8.0.2.2.6 or later support the my_oss_sts_token parameter.

The following table describes the parameters in the preceding statement.

Parameter	Data type	Description
oss_server_name	String	The name of the OSS server. Note The name must be globally unique. The name can be up to 64 characters in length and is case-insensitive. 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 files are stored. Before you import data, you must create OSS buckets.
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. This parameter specifies the storage path of the data files. The parameter value cannot contain special characters. You cannot leave the parameter empty.
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 temporary credential has a default expiration time. If the temporary credential expires, execute the following statement to reset all parameters in `EXTRA_SERVER_INFO`: `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 partitioned 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 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.