Klustron Extended DDL Syntax

CREATE TABLE

Starting from Klustron-1.2, the following 1, 2 and 3 features are supported. The fourth feature has been supported in all versions since Klustron 1.0.

1. Set storage node table options

Klustron supports setting the following options in the WITH clause of the CREATE TABLE statement. These options will be added to the CREATE TABLE statement sent to the storage node, allowing the table built on the storage node to have the properties specified by the user. Users can specify any one or more storage options, and the legal option values for each option are completely identical to those defined by MySQL-8.0.26, with the option syntax being case-insensitive. Refer to the MySQL documentation for more information on these options.

ENGINE, storage engine type, legal values: Innodb, RocksDB.
COMPRESSION, Innodb independent table file page-level compression method, legal values: zlib, lz4, none
ROW_FORMAT, InnoDB engine row format, legal values: DYNAMIC, COMPRESSED, COMPACT
ENCRYPTION, Innodb table file page-level encryption, legal values: 'Y', 'N'
AUTOEXTEND_SIZE, dynamic expansion size of InnoDB table file, legal values refer to MySQL documentation. The setting rules of this option are more complicated, and it is not recommended to explicitly set it unless you are a professional user. The default value should suffice.

create table t(a int, b text) with (
  engine=innodb,
  compression=lz4,
  row_format= COMPACT,
  ENCRYPTION = 'N',
  AUTOEXTEND_SIZE = '128M')

If encryption is to be enabled, the encryption plugin needs to be installed in the storage node.

2. Modify the table options of the above storage node

It is not currently possible to modify the ENGINE option, i.e., the ALTER TABLE statement cannot be used to replace the storage engine. If you want to replace the storage engine, you need to use Klustron's online DDL function. After modifying these options, the new option values will not take effect immediately, but will take effect after OPTIMIZE TABLE.

alter table t set( compression=none,
 row_format=DYNAMIC,
 encryption='N', -- Encryption requires the storage node to load the encryption plugin, which can only be N temporarily
 autoextend_size='8M');

3. Optimize table

Execute this statement to rebuild the table shard on the storage node, so that the modified storage option values are truly applied.

optimize table t;

4. Arrange table shards to specific storage shards

The shard=N option in the WITH clause of the CREATE TABLE statement places this table shard or single table in shard ID=N. This ID value is the ID value of a shard in the pg_shard table of the computing node, and it is also the shard ID value of a shard in the kunlun_metadata_db.shards table of the metadata cluster.

create table t(a int, b text) with (
  engine=innodb, shard=3)

This shard value cannot be modified in ALTER TABLE. If you want to move the table shard to another shard, you need to use Klustron's scale-out function.