AnalyticDB MySQL CREATE TABLE syntax-AnalyticDB(AnalyticDB)-阿里云帮助中心

Quick start guide

Select a table creation template below based on your business scenario. Each scenario provides a minimal CREATE TABLE statement and key notes. For detailed parameter descriptions, see the Parameters section below.

Scenario 1: Date-partitioned fact table (most common)

Suitable for scenarios where data is continuously written by date and needs to be queried and managed by date lifecycle, such as order tables, log tables, and event data tables.

CREATE TABLE sales (
  sale_id BIGINT NOT NULL COMMENT 'Order ID',
  customer_id VARCHAR NOT NULL COMMENT 'Customer ID',
  revenue DECIMAL(15, 2) COMMENT 'Order amount',
  sale_time TIMESTAMP NOT NULL COMMENT 'Order time',
  PRIMARY KEY (sale_time, sale_id)
)
DISTRIBUTED BY HASH(sale_id)
PARTITION BY VALUE(DATE_FORMAT(sale_time, '%Y%m%d'))
LIFECYCLE 365;

Important

The primary key must include the distribution key and partition key. In the example above, sale_id (distribution key) and sale_time (partition key) are both in the primary key. See PRIMARY KEY.
TIMESTAMP or DATE types are recommended for the partition key. See PARTITION BY.
When INSERT encounters duplicate primary key values, the system silently ignores the duplicate records without raising an error. Ensure the primary key uniquely identifies each record.

Scenario 2: Hot/cold tiered partitioned table (reduce storage cost)

Suitable for scenarios where historical data is queried infrequently and cold data should be stored on OSS to reduce costs while maintaining query performance for recent data.

CREATE TABLE order_history (
  order_id BIGINT NOT NULL,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  amount DECIMAL(15, 2),
  PRIMARY KEY (order_date, order_id)
)
DISTRIBUTED BY HASH(order_id)
PARTITION BY VALUE(DATE_FORMAT(order_date, '%Y%m')) LIFECYCLE 120
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=3;

Important

COLD and MIXED policies only take effect on partitioned tables. For non-partitioned tables, data remains on SSD (equivalent to HOT) even if COLD or MIXED is set. See storage_policy.
Hot/cold tiering can only be set at the table level, not at the database level.
HOT_PARTITION_COUNT=3 means the 3 most recent partitions are stored on hot storage (SSD), and the rest on cold storage (OSS). In the example above, data is partitioned by month, so the most recent 3 months are hot storage and data older than 3 months is cold storage.

Scenario 3: High-performance query table with clustered index

Suitable for tables with large data volumes and frequent range queries that need clustered indexes to optimize read performance, such as SaaS multi-tenant tables queried by tenant_id.

CREATE TABLE user_events (
  tenant_id VARCHAR NOT NULL COMMENT 'Tenant ID',
  event_id BIGINT NOT NULL,
  event_time TIMESTAMP NOT NULL,
  event_type VARCHAR,
  CLUSTERED KEY idx_tenant(tenant_id, event_time DESC),
  PRIMARY KEY (event_time, tenant_id, event_id)
)
DISTRIBUTED BY HASH(tenant_id)
PARTITION BY VALUE(DATE_FORMAT(event_time, '%Y%m%d'))
LIFECYCLE 90;

Important

The clustered index takes effect only after BUILD completes. After creating a table or adding a clustered index via ALTER TABLE, wait for the BUILD task to complete (or manually execute BUILD TABLE table_name). See CLUSTERED KEY.
When query conditions do not include the distribution key, the query scans all shards. If business queries cannot cover the distribution key, consider creating a clustered index (CLUSTERED KEY) for frequently queried columns.
Each table can have only one clustered index. The clustered index defaults to ascending order; for descending queries, set the clustered index to DESC.

Scenario 4: Non-partitioned table (small/dimension table)

Suitable for tables with small data volumes (under tens of millions of rows) that do not require time-based lifecycle management.

CREATE TABLE product (
  product_id BIGINT NOT NULL PRIMARY KEY,
  product_name VARCHAR,
  category VARCHAR,
  price DECIMAL(10, 2)
)
DISTRIBUTED BY HASH(product_id);

Important

When no primary key or distribution key is defined, the system automatically adds the __adb_auto_id__ column as the primary key and distribution key.
All data in a non-partitioned table resides in a single partition. Index scan efficiency degrades when data exceeds tens of millions of rows. Partitioned tables are recommended for large tables.

Scenario 5: Broadcast table (small lookup table)

Suitable for dimension tables with small data volumes (recommended no more than 20,000 rows) that need to frequently JOIN with large tables.

CREATE TABLE dim_city (
  city_id INT NOT NULL PRIMARY KEY,
  city_name VARCHAR,
  province VARCHAR
)
DISTRIBUTED BY BROADCAST;

Important

Broadcast tables store a full copy of data on each node, eliminating cross-node data transfer during JOINs, but writes are broadcast to all nodes.
Data volume should not exceed 20,000 rows. Frequent inserts, updates, or deletes are not recommended.

Important notes

The following properties cannot be modified after table creation. Plan carefully before creating the table:

Primary key: Primary key columns cannot be added, removed, or changed.
Distribution key: Distribution key columns cannot be added, removed, or changed. Table recreation and data migration are required.
Partition key: A partition key cannot be added (a non-partitioned table cannot be converted to a partitioned table), and partition key columns cannot be added, removed, or modified. Table recreation is required.
Storage engine: The storage engine cannot be switched between XUANWU and XUANWU_V2. Table recreation is required.

Syntax

CREATE TABLE [IF NOT EXISTS] table_name
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'column_comment']
  | table_constraints}
  [, ... ])
  [table_attribute]
  [partition_options]
  [index_all]
  [storage_policy]
  [block_size]
  [engine]
  [table_properties]
  [AS query_expr]
  [COMMENT 'table_comment']

column_attributes:
  [DEFAULT {constant | CURRENT_TIMESTAMP}]
  [AUTO_INCREMENT]

column_constraints:
  [{NOT NULL|NULL} ]
  [PRIMARY KEY]

table_constraints:
  [{INDEX|KEY} [index_name] (column_name|column_name->'$.json_path'|column_name->'$[*]')][,...]
  [FULLTEXT [INDEX|KEY] [index_name] (column_name) [index_option]] [,...]
  [PRIMARY KEY [index_name] (column_name,...)]
  [CLUSTERED KEY [index_name] (column_name[ASC|DESC],...) ]
  [[CONSTRAINT [symbol]] FOREIGN KEY (fk_column_name) REFERENCES pk_table_name (pk_column_name)][,...]
  [ANN INDEX [index_name] (column_name,...) [index_option]] [,...]

table_attribute:
  DISTRIBUTED BY HASH(column_name,...) | DISTRIBUTED BY BROADCAST

partition_options:
  PARTITION BY 
        {VALUE(column_name) | VALUE(DATE_FORMAT(column_name, 'format')) | VALUE(FROM_UNIXTIME(column_name, 'format'))}
  LIFECYCLE N
  
 index_all:
 INDEX_ALL= 'Y|N'

storage_policy:
  STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' {hot_partition_count=N}}

block_size:
  BLOCK_SIZE= VALUE

engine:
  ENGINE= 'XUANWU|XUANWU_V2'

Parameters

table_name, column_name, column_type, COMMENT

Parameter	Description
table_name	Table name. Must start with a letter or underscore (_). Can contain letters, digits, and underscores (_). Maximum length is 127 characters. You can use `db_name.table_name` to create a table in a specific database.
column_name	Column name. Must start with a letter or underscore (_). Can contain letters, digits, and underscores (_). Maximum length is 127 characters.
column_type	Column data type. AnalyticDB for MySQL For supported data types, see Basic data types and Complex data types.
COMMENT	Adds comment information to a column or table.

column_attributes (default values and auto-increment)

DEFAULT {constant | CURRENT_TIMESTAMP}

Defines the default value for a column. Only constants or the CURRENT_TIMESTAMP function are supported. Other functions and variable expressions are not supported.

If no default value is specified, the column default value is NULL.

AUTO_INCREMENT

Defines an auto-increment column. The data type of an auto-increment column must be BIGINT.

AnalyticDB for MySQL provides unique values for auto-increment columns, but the values are not sequentially incremented and do not start from 1.

Important

When inserting data into a table with an auto-increment column, it is recommended to explicitly specify column names, e.g., INSERT INTO table (column1,column2) VALUES (value1,value2). This helps avoid errors caused by mismatched column counts or column order, such as Insert query has mismatched column sizes.
Due to the distributed system implementation, when using INSERT INTO SELECT statements during ETL processes, auto-increment column values are unique only within a single ETL task and cannot be guaranteed to be unique across multiple ETL tasks.

column_constraints (NOT NULL and primary key)

NOT NULL

Defines that the column does not allow NULL values. If not defined, the column allows NULL by default.

PRIMARY KEY

Defines a single-column primary key, e.g., id BIGINT NOT NULL PRIMARY KEY. For composite primary keys, define them in table constraints (table_constraints).

table_constraints (indexes)

Defines table-level indexes and constraints. Supports regular indexes, primary keys, clustered indexes, full-text indexes, vector indexes, foreign keys, and more. These can be used in combination.

INDEX | KEY

Defines a regular index. INDEX and KEY function identically.

XUANWU_V2 tables do not create full-column indexes by default. If the table has a primary key, only the primary key gets a regular index by default.
XUANWU tables create full-column indexes by default. However, if you manually create indexes for specific columns when creating a XUANWU table (e.g., INDEX (id)), AnalyticDB for MySQL will no longer automatically create indexes for other columns in the table.

Note: Multi-column composite indexes are not supported, i.e., INDEX (column1,column2) is not supported.

PRIMARY KEY

Defines a single-column or composite primary key. The primary key is used for data deduplication and uniquely identifying each row.

Basic usage:

Each table can have only one primary key.
A primary key can be a single column or a combination of multiple columns, e.g., PRIMARY KEY (id) or PRIMARY KEY (id,name).
The primary key must include the distribution key and partition key, and it is recommended to place the distribution key and partition key at the front of the primary key.

Notes:

Tables without a primary key cannot perform DELETE or UPDATE operations.
When no primary key is defined, the following behavior occurs:
- If neither a primary key nor a distribution key is defined, AnalyticDB for MySQL automatically adds a column __adb_auto_id__as the primary key and distribution key.
- If no primary key is defined but a distribution key is specified, AnalyticDB for MySQLdoes not automatically add a primary key.
After table creation, primary key columns cannot be added, removed, or changed. For more immutable properties, see Quick start guide.
When INSERT INTO encounters a duplicate primary key, the system silently ignores the duplicate record (equivalent to INSERT IGNORE INTO) without raising an error or writing the data. To preserve all data, ensure the primary key combination uniquely identifies each record. To update existing data on primary key conflicts, use INSERT ON DUPLICATE KEY UPDATE.

Tuning recommendation: Use numeric columns for the primary key and minimize the number of primary key columns for better performance.

Note

Too many primary key columns may cause:

During data writes, AnalyticDB for MySQL checks for primary key duplicates, consuming more CPU and I/O resources.
The primary key index occupies more disk space. You can use Storage Analysis feature to view the disk space occupied by the primary key index.
The more primary key columns, the slower the BUILD task.

CLUSTERED KEY

Defines a clustered index. A clustered index determines the physical storage order of data within each partition (ascending by default), keeping data with similar key values stored contiguously, thereby accelerating range queries and equality queries.

Clustered index diagram

Applicable scenarios:

Columns that frequently appear in query conditions are suitable as clustered index keys. For example, in SaaS scenarios, using tenant_id as the clustered index stores data for the same tenant contiguously, accelerating queries.

Basic usage:

A clustered index requires the BUILD task to complete before taking effect. You can manually execute BUILD TABLE table_name to accelerate this. A BUILD is also required after adding a clustered index via ALTER TABLE.
Each table can have only one clustered index.
A clustered index can be created on a single column (e.g., CLUSTERED KEY index(id)) or multiple columns (e.g., CLUSTERED KEY index(id,name)). When a clustered index involves multiple columns, data is sorted first by the first column, then by the second column for ties. Therefore, CLUSTERED KEY index(id,name) and CLUSTERED KEY index(name,id) are different clustered indexes.
The clustered index defaults to ascending order, suitable for ascending queries. If your queries are in descending order, set the clustered index to descending when creating the table, e.g., CLUSTERED KEY index(id) DESC. If the table is already created, you can Drop a clustered index, and recreate a descending clustered index.
If the field values are long, such as strings of tens or hundreds of KB, it is not recommended to use that field for the clustered index, as it may impact sort performance.

FULLTEXT INDEX | FULLTEXT KEY

Defines a full-text index.

Syntax and parameter description

Syntax: [FULLTEXT [INDEX|KEY] [index_name] (column_name) [index_option]] [,...]

Parameter description:

index_name: The full-text index name.
column_name: The column for the full-text index. The column type must be VARCHAR, or a JSON path expression (e.g., column_name->'$.key').
index_option: Specifies the tokenizer and custom dictionary for the full-text index. Optional.
- WITH ANALYZER analyzer_name: Specifies the Analyzers for full-text indexes.
- WITH DICT tbl_dict_name: Specifies the Custom dictionaries for full-text indexes.

FOREIGN KEY

Defines a foreign key index. Foreign key indexes are used to eliminate unnecessary JOINs.

Syntax and parameter description

Version requirement:

AnalyticDB for MySQL Cluster kernel version must be 3.1.10 or later.

Note

To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

Syntax: [[CONSTRAINT [symbol]] FOREIGN KEY (fk_column_name) REFERENCES pk_table_name (pk_column_name)][,...]

Parameter description:

symbol: Optional. The foreign key constraint name, unique within the table. If not specified, the parser automatically appends the suffix _fk to the foreign key column name.
fk_column_name: Specifies the foreign key column. The foreign key column must be defined in the CREATE TABLE statement.
pk_table_name: Specifies the referenced table name. The referenced table must already exist.
pk_column_name: Specifies the referenced column, which must exist and be a primary key column of the referenced table.

Basic usage:

Each table can have multiple foreign key indexes.
Composite foreign key indexes are not supported, meaning foreign key indexes composed of multiple columns are not supported, e.g., :FOREIGN KEY (sr_item_sk, sr_ticket_number) REFERENCES store_sales(ss_item_sk,d_date_sk).
AnalyticDB for MySQL does not perform data constraint checking. You need to ensure the data constraint relationship between the primary key of the referenced table and the foreign key of the referencing table.
External tables do not support creating foreign key constraints.

ANN INDEX

Defines a vector index.

Note: Both XUANWU and XUANWU_V2 engines support creating vector indexes.

Syntax and parameter description

Syntax: [ANN INDEX [index_name] (column_name,...) [index_option]] [,...]

Parameter description:

index_name: The vector index name.
column_name: The name of the vector column. The column type must be array<float>, array<smallint>, or array<byte>, and the dimension must be specified. Example: feature array<float>(4).
index_option: Vector index properties.
- algorithm: The algorithm used for vector distance calculation. Only HNSW_PQ is supported, suitable for medium-scale data scenarios with single-table data volumes between millions and tens of millions, sensitive to vector dimensions.
- dis_function: The vector distance formula. Only SquaredL2 is supported. Formula: (x1-y1)^2+(x2-y2)^2+….

JSON INDEX

Defines a JSON indexes or JSON Array index.

Syntax and parameter description

JSON Index

Version requirement:

For clusters running V3.1.5.10 or later, JSON indexes are not created automatically when you create a table. Create them manually using the syntax below.
For clusters running versions earlier than V3.1.5.10, JSON indexes are created automatically for JSON columns after table creation.

Note

To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

Syntax: [INDEX [index_name] (column_name|column_name->'$.json_path')]

Parameter description:

index_name: The index name.
column_name|column_name->'$.json_path':
- column_name: The name of the JSON column to index. Creates an index on the entire JSON document.
- column_name->'$.json_path': The JSON column and a specific property key. Each index covers one property key.
  Important
  
  Only clusters with kernel version V3.1.6.8 or later support column_name->'$.json_path.
  
  If a JSON column already has an index, delete it before creating a property-key index on the same column.

JSON Array Index

Version requirement:

Only clusters with kernel version 3.1.10.6 or later support creating JSON Array indexes.

Note

To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

Syntax: [INDEX [index_name] (column_name->'$[*]')]

Parameter description:

index_name: The index name.
column_name->'$[*]': column_name is the column for the JSON Array index. For example, vj->'$[*]' creates a JSON Array index on the vj column.

table_attribute (distribution key)

table_attribute determines whether the table is a regular table or a broadcast table.

DISTRIBUTED BY HASH defines a regular table. Regular tables fully leverage the query advantages of the distributed system, improving query efficiency. Regular tables can store large volumes of data, typically from tens of millions to hundreds of billions of rows.
DISTRIBUTED BY BROADCAST defines a broadcast table. See DISTRIBUTED BY BROADCAST below for details.

DISTRIBUTED BY HASH (column_name,...)

Defines the distribution key. The system performs HASH calculations on distribution key values to distribute data across different Terms, improving query performance and scalability.

Data sharding diagram

Basic usage:

Each table can have only one distribution key.
A distribution key can contain one or more columns.
Columns in the distribution key must be included in the primary key. See PRIMARY KEY.

Notes:

If no distribution key is defined during table creation, the system handles it automatically. See PRIMARY KEY notes.
After table creation, distribution key columns cannot be added, removed, or changed. To modify the distribution key, recreate the table and migrate data. For more immutable properties, see Quick start guide.

Tuning recommendations:

It is recommended that the distribution key contain as few columns as possible to make it more versatile across various complex queries.
Choose columns that frequently appear in query conditions and have evenly distributed values as the distribution key, such as transaction ID, device ID, user ID, or auto-increment columns. However, if query conditions are very limited — for example, column a has evenly distributed values and frequently appears in query conditions but always as a=3 — using column a as the distribution key creates a data hotspot, making it unsuitable.
When the query condition does not include the distribution key, the query must scan all shards, significantly degrading performance. If business query conditions cannot cover the distribution key, consider creating a clustered index (CLUSTERED KEY) to optimize query performance.
Use JOIN columns as the distribution key whenever possible. When two tables are distributed by the same key (JOIN column), rows with matching key values are co-located on the same shard, enabling local JOINs without cross-node data transfer. This reduces data redistribution during queries and improves performance. For example, to query historical orders by customer, choose customer_id as the distribution key.
Avoid using date, time, or timestamp columns as the distribution key. These columns tend to cause data skew during writes, affecting write performance. Most queries typically filter by date or time range (e.g., querying data from the last day or month), which may cause the queried data to reside on only one node, preventing full utilization of all nodes. It is recommended to use date/time columns as partition key.
You can use the storage diagnostics feature to check whether the distribution key is appropriate and whether the data is skewed.

DISTRIBUTED BY BROADCAST

Defines a broadcast table. Each node stores a full copy of the data, eliminating cross-node transfer during JOINs. Write changes are broadcast to all nodes, so broadcast tables are suitable for small dimension tables with infrequent writes.

partition_options (partition key and lifecycle)

If data volume on a single shard is large after setting the distribution key, you can define a partition key to divide data on each shard into different partitions, accelerating data filtering and improving query performance.

Why define partitions

Partitions speed up data filtering and improve query performance.
- Partition pruning: Queries only access relevant partitions, skip irrelevant ones, reduce data scans, and improve query speed.
- Better index scan performance: When the number of indexed rows is too large (e.g., exceeding 50 million), index scan efficiency degrades. Indexes are at the partition level, meaning each partition has its own index. Without partitions, all data is in one partition, and index scan efficiency drops when data exceeds tens of millions of rows. With partitions, the number of rows per partition index stays manageable, ensuring scan performance.
- Improved BUILD efficiency: BUILD converts real-time data into historical data by constructing partitions, building indexes, and cleaning redundant data. New indexes take effect only after BUILD completes. Without partitions, each BUILD processes the entire table — more data means slower BUILD and delayed index effectiveness. With partitions, each BUILD only processes changed partitions, reducing BUILD time.
Partitions combined with lifecycle (LIFECYCLE) enable data lifecycle management.
Partitions combined with storage policy (storage_policy) enable hot/cold data tiering.

Data partitioning and lifecycle diagram

PARTITION BY

Specifies the partition key.

Syntax: PARTITION BY VALUE {(column_name)|(DATE_FORMAT(column_name, 'format'))|(FROM_UNIXTIME(column_name, 'format'))} LIFECYCLE n

Parameters:

column_name: Partition key. PARTITION BY VALUE(column_name) indicates that column_name column values are used for partitioning. The partition key data type can be numeric, datetime, or string representing numbers.
DATE_FORMAT(column_name, 'format'))|FROM_UNIXTIME(column_name, 'format'): Uses the DATE_FORMAT or FROM_UNIXTIME function to convert datetime columns to the specified date format for partitioning. format only supports year, month, and day: %Y, %y, %Y%m, %y%m, %Y%m%d, %y%m%d. After table creation, the format can be modified using ALTER TABLE.
- When column is of BIGINT, TIMESTAMP, DATETIME, or VARCHAR type, use the DATE_FORMAT function. For BIGINT columns, values are millisecond UNIX timestamps (e.g., 1734278400000). For TIMESTAMP, DATETIME, and VARCHAR columns, values are like "2024-11-26 00:01:02".
- When column is of INT type, use the FROM_UNIXTIME function. Values are second-level UNIX timestamps (e.g., 1696266000).

Notes:

For clusters with kernel versions below 3.2.1.0, when using PARTITION BYthe lifecycle must also be defined when defining partitions(LIFECYCLE n); otherwise, an error occurs.
For clusters with kernel versions 3.2.1.0 and later, when using PARTITION BYthe lifecycle(LIFECYCLE n)is optional when defining partitions. If not configured, partition data is not cleaned up.
After table creation, partition keys cannot be added, and columns in the partition key cannot be added, removed, or modified. To add or modify the partition key, recreate the table and migrate data. For details, see ALTER TABLE. For more immutable properties, see Quick start guide.
When a DATETIME column uses DATE_FORMAT as the partition function, partitioning may not take effect in some scenarios (all data goes into the default partition). DATE or TIMESTAMP columns are recommended for partition keys. If you have already used a DATETIME column, write test data after table creation and execute BUILD TABLE to verify that partitions are created correctly.

Tuning recommendations:

It is recommended to use datetime fields as the partition key.
Partitions that are too large or too small affect both query and write performance and may even affect cluster stability. For recommended row counts per partition, see Partitioned table diagnostics.
Frequent updates to historical partition data are not recommended. For example, if multiple historical partitions are frequently updated daily, reconsider whether the partition key is appropriate.

LIFECYCLE n

Used with PARTITION BY to manage partition lifecycle. The system sorts partitions by key values from largest to smallest, retains the latest n partitions, and automatically deletes excess partitions.

For kernel versions below 3.2.1.1, LIFECYCLE n defines a maximum of n partitions retained per shard. When managing partition lifecycle at the shard level, the total retained partitions may exceed n if data distribution is uneven or data volume is very small.
For kernel versions 3.2.1.1 and later, for XUANWU engine tables: tables created after upgrade manage partition lifecycle at the table level. LIFECYCLE n defines a maximum of n partitions per table. Tables created before upgrade still manage lifecycle at the shard level. LIFECYCLE n defines a maximum of n partitions per shard.
XUANWU_V2 engine tables still manage partition lifecycle at the shard level. LIFECYCLE n defines a maximum of n partitions per shard. Table-level management is not currently supported.

Example:

For example, PARTITION BY VALUE (DATE_FORMAT(date, '%Y%m%d')) LIFECYCLE 30 means the date column is converted to yyyyMMdd format for partitioning, retaining a maximum of 30 partitions. Suppose day 1 data is written to partition 20231201, day 2 to 20231202, and so on, with day 30 to 20231230. When day 31 data is written to partition 20231231, the smallest partition (20231201) is automatically deleted because only 30 can be retained.

index_all (full-column index)

Specifies whether to create INDEX indexes for all columns.

Values:

Y: Creates INDEX indexes for all columns. Default for XUANWU tables is Y.
N: Creates INDEX indexes only for the primary key. Default for XUANWU_V2 tables is N.

storage_policy (storage policy)

Enterprise Edition, Basic Edition, and Data Lakehouse Edition and Data Warehouse Edition Elastic mode cluster edition (new version) support specifying data storage policies. Different storage policies result in different read/write performance and storage costs.

Important

STORAGE_POLICY='COLD' and STORAGE_POLICY='MIXED'only take effect on partitioned tables. For non-partitioned tables, data remains on SSD even if COLD or MIXED is set, equivalent to HOT. To use cold storage, ensure the table has PARTITION BY.

Values:

hot (default): Hot storage. All partition data is stored on SSD. Best performance but highest storage cost.
cold: Cold storage. All partition data is stored on OSS. Lower performance than hot storage but lowest storage cost.
mixed: Hot/cold mixed storage (tiered storage). Frequently queried partition data (hot data) is on SSD, while infrequently queried data (cold data) is on OSS. This reduces storage costs while maintaining query performance. When selecting mixed, you must also use PARTITION BY to define partitions and hot_partition_count to specify the number of hot partitions. Without partitions, mixed does not take effect and data is stored on SSD.

Hot/cold mixed storage diagram

hot_partition_count (hot partitions)

When STORAGE_POLICY='mixed', you need to use hot_partition_count=n (where n is a positive integer) to define the number of hot partitions. AnalyticDB for MySQL sorts partitions by key values from largest to smallest. The largest n partitions are hot partitions; the rest are cold partitions.

Note

If the storage policy (STORAGE_POLICY) is not set to mixed, specifying hot_partition_count=n is not supported and causes an error.

block_size (data block)

Specifies the number of rows per data block in columnar storage, affecting the amount of data read per I/O operation. For point query scenarios, reducing block_size can improve efficiency.

Default values:

Default block_size for broadcast tables is 4096.
For Elastic Mode Cluster Edition (new version) single-node instances (fewer than 32 compute cores), default block_size is 8192.
In other cases, default block_size is 32760. When block_size is 32760, SHOW CREATE TABLE does not display block_size.

Important

If you are not familiar with columnar storage principles, we recommend not changing block_size.

engine (storage engine)

Specifies the storage engine for internal tables.

For kernel versions below 3.2.2.0, the value is XUANWU. If ENGINE is not explicitly specified during table creation, this is the default.

Important
For kernel versions below 3.1.9.5, if ENGINE='XUANWU' is explicitly specified, you must also specify table_properties='{"format":"columnstore"}'; otherwise, table creation fails.
For kernel versions 3.2.2.0 and later:
- When RC_DDL_ENGINE_REWRITE_XUANWUV2=true, only XUANWU_V2 is supported.
- When RC_DDL_ENGINE_REWRITE_XUANWUV2=false, both XUANWU_V2 and XUANWU are supported.
You can use SHOW ADB_CONFIG KEY=RC_DDL_ENGINE_REWRITE_XUANWUV2; to view the parameter value. You can also modify the value of RC_DDL_ENGINE_REWRITE_XUANWUV2 at the cluster or table level.

AS query_expr (CTAS)

CREATE TABLE AS query_expr creates a table and writes SELECT query results into the newly created table. For usage details, see CREATE TABLE AS SELECT (CTAS).

Important

When using CTAS to create a cold storage table (STORAGE_POLICY='COLD'), note that PARTITION BY in CTAS only supports direct column references — function expressions such as NOW() cannot be used as partition keys. For details, see CTAS.

Examples

For complete table creation scenarios and templates, see Quick start guide. The following examples demonstrate specific features.

Create a non-partitioned table

No distribution or partition key defined; primary key used as distribution key

The table has a primary key but no distribution key. AnalyticDB for MySQL uses the primary key as the distribution key by default.

CREATE TABLE orders (
  order_id BIGINT NOT NULL COMMENT 'Order ID',
  customer_id INT NOT NULL COMMENT 'Customer ID',
  order_status VARCHAR(1) NOT NULL COMMENT 'Order status',
  total_price DECIMAL(15, 2) NOT NULL COMMENT 'Order amount',
  order_date DATE NOT NULL COMMENT 'Order date',
  PRIMARY KEY(order_id,order_date)
);

Query the CREATE TABLE statement. The primary key columns order_id and order_date are used as the distribution key.

SHOW CREATE TABLE orders;

+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                  | 
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| orders  | CREATE TABLE `orders` (                                                                                                                       |
|         | `order_id` bigint NOT NULL COMMENT 'Order ID',                                                                                                   |
|         | `customer_id` int NOT NULL COMMENT 'Customer ID',                                                                                                   |
|         | `order_status` varchar(1) NOT NULL COMMENT 'Order status',                                                                                         | 
|         | `total_price` decimal(15, 2) NOT NULL COMMENT 'Order amount',                                                                                      |
|         | `order_date` date NOT NULL COMMENT 'Order date',                                                                                                 |
|         | PRIMARY KEY (`order_id`,`order_date`)                                                                                                         |
|         | ) DISTRIBUTED BY HASH(`order_id`,`order_date`) INDEX_ALL='Y' STORAGE_POLICY='HOT' ENGINE='XUANWU' TABLE_PROPERTIES='{"format":"columnstore"}'  |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

No primary key or distribution key defined; system auto-generates both

The table has no primary key or distribution key. AnalyticDB for MySQL adds a column __adb_auto_id__ as both the primary key and distribution key.

CREATE TABLE orders_new (
  order_id BIGINT NOT NULL COMMENT 'Order ID',
  customer_id INT NOT NULL COMMENT 'Customer ID',
  order_status VARCHAR(1) NOT NULL COMMENT 'Order status',
  total_price DECIMAL(15, 2) NOT NULL COMMENT 'Order amount',
  order_date DATE NOT NULL COMMENT 'Order date'
);

Query the CREATE TABLE statement. An auto-increment column __adb_auto_id__ is automatically added, serving as both the primary key and distribution key.

SHOW CREATE TABLE orders_new;

+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                  | 
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| orders_new  | CREATE TABLE `orders_new` (                                                                                                                   |
|             | `__adb_auto_id__` bigint AUTO_INCREMENT,                                                                                                      |
|             | `order_id` bigint NOT NULL COMMENT 'Order ID',                                                                                                   |
|             | `customer_id` int NOT NULL COMMENT 'Customer ID',                                                                                                   |
|             | `order_status` varchar(1) NOT NULL COMMENT 'Order status',                                                                                         | 
|             | `total_price` decimal(15, 2) NOT NULL COMMENT 'Order amount',                                                                                      |
|             | `order_date` date NOT NULL COMMENT 'Order date',                                                                                                 |
|             | PRIMARY KEY (`__adb_auto_id__`)                                                                                                               |
|             | ) DISTRIBUTED BY HASH(`__adb_auto_id__`) INDEX_ALL='Y' STORAGE_POLICY='HOT' ENGINE='XUANWU' TABLE_PROPERTIES='{"format":"columnstore"}'        |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

Primary and distribution key defined, no partition key

Create table supplier. supplier_id is an auto-increment column. The distribution key is supplier_id. Data is HASH-distributed based on supplier_id values.

CREATE TABLE supplier (
  supplier_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  supplier_name VARCHAR,
  address INT,
  phone VARCHAR
) 
DISTRIBUTED BY HASH(supplier_id);

Create indexes on specific columns

Create regular indexes on the id and date columns only; other columns are not indexed.

CREATE TABLE index_tb (
  id INT,
  sales DECIMAL(15, 2),
  date DATE,
  INDEX (id),
  INDEX (date),
  PRIMARY KEY (id)
) 
DISTRIBUTED BY HASH(id);

Define a full-text index

Create a full-text index on the content column with the name fidx_c.

CREATE TABLE fulltext_tb (
  id INT,
  content VARCHAR,
  keyword VARCHAR,
  FULLTEXT INDEX fidx_c(content),
  PRIMARY KEY (id)
) 
DISTRIBUTED BY HASH(id);

For more information about creating and modifying full-text indexes, see Create a full-text index.

For information about full-text search, see Full-text search.

Define a vector index

Define short_feature and float_feature as vector columns of type array<float> with a dimension of 4.

Create vector index short_feature_index on short_feature, and vector index float_feature_index on float_feature.

CREATE TABLE fact_tb (  
  xid BIGINT NOT NULL,  
  cid BIGINT NOT NULL,  
  uid VARCHAR NOT NULL,  
  vid VARCHAR NOT NULL,  
  wid VARCHAR NOT NULL,  
  short_feature array<smallint>(4),  
  float_feature array<float>(4),  
  ann index short_feature_index(short_feature), 
  ann index float_feature_index(float_feature),  
  PRIMARY KEY (xid, cid, vid)
) 
DISTRIBUTED BY HASH(xid) PARTITION BY VALUE(cid) LIFECYCLE 4;

For more information about vector indexes and vector search, see Vector search.

Define a foreign key index

Create a table named store_returns. Use the foreign key syntax FOREIGN KEY to associate sr_item_sk with the primary key column customer_id of the customer table.

CREATE TABLE store_returns (
  sr_sale_id BIGINT NOT NULL PRIMARY KEY,
  sr_store_sk BIGINT,
  sr_item_sk BIGINT NOT NULL,
  FOREIGN KEY (sr_item_sk) REFERENCES customer (customer_id)
);

Define a JSON Array index

Create a JSON Array index on the vj column with the name idx_vj.

CREATE TABLE json(
  id INT,
  vj JSON,
  INDEX idx_vj(vj->'$[*]')
)
DISTRIBUTED BY HASH(id);

For more information about creating and modifying JSON Array indexes, see Usage notes and JSON Array index.

FAQ

Compression and storage

Can I specify a compression algorithm or compression level when creating a table?

No. Storage compression in AnalyticDB for MySQL is managed automatically by the system. You cannot specify a compression algorithm or compression level in the CREATE TABLE statement. To save storage cost, use STORAGE_POLICY: COLD stores data in OSS to reduce cost, while HOT stores data in SSD for better query performance.

Column attributes and constraints

Do auto-increment columns start from 1? Are the values unique?

Auto-increment values are not sequentially incremented and do not start from 1. However, all auto-increment values are unique.

Distribution key, partition key, and lifecycle

What is the difference between a distribution key and a partition key?

Based on the HASH results of distribution key values, data is distributed across different shards. On each shard, data is divided into different partitions based on partition key values. The diagram is as follows.

Is it required to specify a distribution key when creating a table?

For regular tables, manually specifying a distribution key is not required. If not specified, AnalyticDB for MySQL uses the primary key as the distribution key. Without a primary key, it automatically generates a column __adb_auto_id__ as both the distribution key and primary key.
For broadcast tables, no distribution key is needed. Specify DISTRIBUTED BY BROADCAST instead, indicating each storage node stores a full copy of the data.

Does scaling change the number of shards?

Scaling does not change the number of shards in a cluster.

How do I query partition information for a table?

Execute the following SQL to query partition information:

SELECT partition_id, -- Partition name
 row_count, -- Total rows
 local_data_size, -- Local storage size
 index_size, -- Index size
 pk_size, -- Primary key index size
 remote_data_size -- Remote storage size
FROM information_schema.kepler_partitions
WHERE schema_name = '$DB'
 AND table_name ='$TABLE' 
 AND partition_id > 0;

Why can't I find partition information after creating a partitioned table?

There are two main reasons why partition information is unavailable after creating a partitioned table:

During table creation, only partitioning rules are defined; no partitions are actually created. Partitions are determined by partition key values. If no data has been written, no partitions exist.
Partitions are not built in real time. Partition information is visible only after BUILD completes.

Solution:

Write data first and wait for the BUILD task to complete. Partition information becomes available after BUILD finishes.

How do I query data from a specific partition?

You can query data from a specific partition using the filter condition WHERE <partition_key> = '<partition_value>'. The following syntax is not supported: SELECT * FROM table PARTITION(202304).

A complete example is as follows.

A partitioned table orders_demo has been created, partitioned by date (order_date). The CREATE TABLE statement is:

CREATE TABLE orders_demo (
  order_id BIGINT NOT NULL COMMENT 'Order ID',
  customer_id INT NOT NULL COMMENT 'Customer ID',
  order_status VARCHAR(1) NOT NULL COMMENT 'Order status',
  total_price DECIMAL(15, 2) NOT NULL COMMENT 'Order amount',
  order_date DATE NOT NULL COMMENT 'Order date',
  PRIMARY KEY(order_id,order_date)
)
DISTRIBUTED BY HASH(order_id) 
PARTITION BY VALUE(date_format(order_date, '%Y%m')) LIFECYCLE 30 ;

Insert 10 rows of sample data:

INSERT INTO orders_demo (order_id, customer_id, order_status, total_price, order_date)
VALUES
  (1001, 1, 'C', 150.75, '2023-10-01'),
  (1002, 2, 'P', 200.50, '2023-10-01'),
  (1003, 3, 'S', 99.99, '2023-10-01'),
  (1004, 4, 'C', 300.00, '2023-10-01'),
  (1005, 5, 'P', 450.25, '2023-10-02'),
  (1006, 6, 'S', 120.00, '2023-10-02'),
  (1007, 7, 'C', 80.50, '2023-10-03'),
  (1008, 8, 'P', 600.00, '2023-10-03'),
  (1009, 9, 'S', 250.75, '2023-10-03'),
  (1010, 10, 'C', 199.99, '2023-10-14');

Manually BUILD the partitioned table:

BUILD TABLE orders_demo;

When certain conditions are met, a table can complete BUILD automatically. This example uses manual BUILD for easier demonstration.

Query the BUILD status. When the status field returns FINISH, the BUILD is complete.

SELECT table_name, schema_name, status FROM INFORMATION_SCHEMA.KEPLER_META_BUILD_TASK WHERE table_name='ORDERS_DEMO';

In this example, the partition key order_date is of DATE type. To query partition data for 2023-10-01:

SELECT * FROM orders_demo WHERE order_date='2023-10-01';

If the partition key is DATETIME type, the filter condition should be WHERE order_date >= "2023-10-01 00:00:00" and order_date < "2023-10-02 00:00:00". Example:

SELECT * FROM orders_demo WHERE order_date >= "2023-10-01 00:00:00" and order_date < "2023-10-02 00:00:00";

Must the partition key be included as a filter condition when querying a partitioned table?

If a table has a partition key, it is not required in the filter condition. However, using the partition key as a filter significantly accelerates queries by scanning only relevant partitions instead of the entire table.

What data type requirements apply to partition keys?

The partition key data type can be numeric, datetime, or string representing numbers. Other data types may cause write errors.

If you encounter the error partition format function error during data writes, the partition key value does not meet data type requirements.

Can functions other than DATE_FORMAT and FROM_UNIXTIME be used for partition keys?

No. Only three methods are supported: PARTITION BY VALUE(column), PARTITION BY VALUE(DATE_FORMAT(column,'format')), or PARTITION BY VALUE(FROM_UNIXTIME(column,'format')). Using other functions causes an error.

Note

For partition key definition methods, see partition_options (partition key and lifecycle).

How do I view the lifecycle of a partitioned table?

Use SHOW CREATE TABLE <table_name> to view the CREATE TABLE statement, which displays the partition lifecycle.

I set LIFECYCLE to 30 (retain 30 days), but I can still query data older than 30 days. Why?

There are two possible reasons:

The partition has just expired and has not been deleted yet. Expired partitions are not deleted immediately — they are deleted after the BUILD task completes.
For tables created on kernel versions below 3.2.1.1, LIFECYCLE defines partitions retained per shard. This phenomenon may occur when the actual partition count on a shard is less than the LIFECYCLE value. Tables created on versions 3.2.1.1+ do not have this issue.
For example:
- Uneven data distribution: Suppose data is partitioned by date. Shard 1 has partitions 20231201-20231230; Shard 2 has 20231202-20231231. Both shards have 30 partitions, not exceeding LIFECYCLE (30), so no partitions are deleted. Data from 20231201 to 20231231 is returned.
- No data written for a long time: Suppose Shard 1 has partitions 20231201-20231204, and no new data is written after 20231204. Shard 1 has only 4 partitions, not exceeding LIFECYCLE (30), so no partitions are deleted. Data from 20231201 can still be queried after 20231231.

Is expired partition data cleaned up immediately?

No. Partitions are not built or cleaned in real time. After a partition expires, the table must complete a BUILD before the partition is cleaned up.

Indexes

How do I query the clustered index of a table?

Use SHOW CREATE TABLE to view the clustered index in the CREATE TABLE statement.

Is UNIQUE INDEX supported?

AnalyticDB for MySQL does not support UNIQUE INDEX. However, AnalyticDB for MySQL the primary key index is a unique index that ensures primary key values are unique within the table.

Are multi-column composite indexes supported, such as INDEX(column1,column2)?

Multi-column composite indexes are not supported. Each regular index can contain only one column, e.g., INDEX(column1).

Why does the storage overview show 0 for the primary key index and regular index sizes?

Newly written data first enters the real-time engine. Primary keys and indexes are materialized to partitions only after the BUILD task completes. If the storage overview shows 0 for the primary key index or regular index sizes, the BUILD task has usually not been run or not yet completed. Run BUILD TABLE table_name; and check again after the task completes.

Columnar storage

What does TABLE_PROPERTIES='{"format":"columnstore"}' mean in the CREATE TABLE statement?

TABLE_PROPERTIES='{"format":"columnstore"}' is a fixed value indicating that data in ENGINE uses columnar format. You do not need to specify this during table creation.

Can some partitions use row storage while others use columnar storage?

No, this is not supported.

Other

Which parameters can be changed via ALTER TABLE after table creation?

ALTER TABLE supports changing the following parameters:

table_name, column_name, column_type, COMMENT
Add and delete columns (except primary key columns)
Column default values
Change NOT NULL to NULL
Add and delete INDEX indexes
Date format of the partition function
Lifecycle
Storage policy

For details, see ALTER TABLE.

Other parameters cannot be changed after table creation.

Which properties cannot be modified after table creation?

The following properties cannot be modified after table creation. Recreate the table and migrate data to change them:

Primary key: Primary key columns cannot be added, removed, or changed.
Distribution key: Cannot add, remove, or change distribution key columns.
Partition key: Cannot add a partition key (non-partitioned tables cannot be converted), and partition key columns cannot be added, removed, or modified.
Storage engine: Cannot switch between XUANWU and XUANWU_V2.

For more information, see Quick start guide.

What is the maximum number of tables per cluster?

A single AnalyticDB for MySQL cluster has the following table limits:

Enterprise Edition cluster: 80000/(shard_count/reserved_nodes/3). shard_count/reserved_nodes/3, rounded up. increase reserved resource nodes to increase the internal table limit.
Basic Edition cluster: 80000/(shard_count/reserved_nodes). (shard_count/reserved_nodes), rounded up. increase reserved resource nodes to increase the internal table limit.
Enterprise Edition, Basic Edition, and Data Lakehouse Edition and Data Warehouse Edition Elastic mode The external table limit: 500,000.
Data Lakehouse Edition Internal table limit: [80000/(shard_count/storage_group_count)]*2. One storage reserved resource group = 24 ACU. For example, 48 ACU = 2 groups.scale up storage reserved resources to increase the internal table limit.
Data Warehouse Edition Elastic mode Internal table limit: [80000/(shard_count/EIU_count)]*2. shard_count/EIU_count, rounded up. EIU stands for Elastic I/O Unit. increase EIU count to increase the internal table limit.
Data Warehouse Edition Reserved mode cluster (1-20 node groups): 80000/(shard_count/node_group_count). shard_count/node_group_count, rounded up. increase node group count to increase the internal table limit.

Note

Query shard count (number of shards): SELECT COUNT(1) FROM information_schema.kepler_meta_shards;. The shard count cannot be changed.

AnalyticDB for MySQL default character set?

AnalyticDB for MySQL The default character set is utf-8, equivalent to MySQL's utf8mb4. Other character sets are not currently supported.

How do I determine if a table is internal or external?

You can use SHOW CREATE TABLE db_name.table_name; to view the DDL. If the ENGINE parameter is not found, or ENGINE is XUANWU or XUANWU_V2, the table is an internal table; otherwise, it is an external table.

What are the quantity limits for table creation?

For complete usage limits, see Usage limits. The following are common quantity limits for table creation:

Limit	Maximum
Maximum columns per table	4096
Maximum partitions per cluster	102400
Maximum rows per partition per shard	2.1 billion
Maximum table COMMENT length	1024 characters

Note

The number of hash partitions (DISTRIBUTED BY HASH shard count) cannot be modified after table creation. The default shard count is 128. Plan accordingly based on your data volume. The maximum number of tables per cluster is directly related to the shard count — see How many tables can a cluster create? above.

Common errors

partition number must larger than 0

Cause: The CREATE TABLE statement defines partitions but does not set the partition lifecycle.

Example statement that causes this error:

CREATE TABLE test (
  id INT COMMENT '',
  name VARCHAR(10) COMMENT '',
  PRIMARY KEY (id, name)
) 
DISTRIBUTED BY HASH(id) PARTITION BY VALUE(name);

Solution: Define the partition lifecycle in the CREATE TABLE statement. Correct example:

CREATE TABLE test (
  id INT COMMENT '',
  name VARCHAR(10) COMMENT '',
  PRIMARY KEY (id, name)
) 
DISTRIBUTED BY HASH(id) PARTITION BY VALUE(name) LIFECYCLE 30;

Note

This error occurs only on clusters with kernel versions below 3.2.1.0.

Only 204800 partition allowed, the number of existing partition=>196462

Cause: AnalyticDB for MySQL The default partition limit per cluster is 102,400. This error occurs when the limit is exceeded.

Query the cluster partition count:

SELECT count(partition_id)
FROM information_schema.kepler_partitions
WHERE partition_id > 0;

Solution: You can use ALTER TABLE to adjust partition granularity, e.g., change daily to monthly partitioning.

partition column 'XXX' is not found in primary index=> [YYY]

Cause: The primary key must include both the distribution key and partition key. This error occurs when the primary key does not include the partition key.

SQL error example 1:

CREATE TABLE test (
  id INT COMMENT '',
  name VARCHAR(10) COMMENT '',
  PRIMARY KEY (id)
) 
DISTRIBUTED BY HASH(id) PARTITION BY VALUE(name) LIFECYCLE 30;

This error also occurs if no primary key or distribution key is specified, because AnalyticDB for MySQL automatically generates a column __adb_auto_id__ as the primary key and distribution key. The primary key then contains only __adb_auto_id__, which does not include the partition key, causing the error.

SQL error example 2:

CREATE TABLE test (
  id INT COMMENT '',
  name VARCHAR(10) COMMENT ''
) 
PARTITION BY VALUE(name) LIFECYCLE 30;

Solution: Add the partition key to the primary key.

SemanticException:only 5000 table allowed

Cause: AnalyticDB for MySQL The total table count in a cluster (active tables + recycle bin tables) has a limit. This error occurs when exceeded. Limits vary by product series. See table limits.

Solution:

Delete unused tables.
1. Check cluster business tables and table recycle bin for unused tables.
2. Delete unused business tables or delete unused tables from the recycle bin.
  
  Important
  After deleting unused business tables, the tables enter the recycle bin. You also need to delete them from the recycle bin.
Optimize table structure.

You can merge multiple business tables into a single table to reduce the table count.

unsigned expr not supported

Cause: AnalyticDB for MySQL does not support the UNSIGNED attribute (unsigned numbers).

Solution: Do not use the UNSIGNED attribute in column definitions. Implement non-negative constraints in your application code.

Quick start guide

Scenario 1: Date-partitioned fact table (most common)

Scenario 2: Hot/cold tiered partitioned table (reduce storage cost)

Scenario 3: High-performance query table with clustered index

Scenario 4: Non-partitioned table (small/dimension table)

Scenario 5: Broadcast table (small lookup table)

Important notes

Syntax

Parameters

table_name, column_name, column_type, COMMENT

column_attributes (default values and auto-increment)

DEFAULT {constant | CURRENT_TIMESTAMP}

AUTO_INCREMENT

column_constraints (NOT NULL and primary key)

NOT NULL

PRIMARY KEY

table_constraints (indexes)

INDEX | KEY

PRIMARY KEY

CLUSTERED KEY

FULLTEXT INDEX | FULLTEXT KEY

FOREIGN KEY

ANN INDEX

JSON INDEX

JSON Index

JSON Array Index

table_attribute (distribution key)

DISTRIBUTED BY HASH (column_name,...)

DISTRIBUTED BY BROADCAST

partition_options (partition key and lifecycle)

PARTITION BY

LIFECYCLE n

index_all (full-column index)

storage_policy (storage policy)

hot_partition_count (hot partitions)

block_size (data block)

engine (storage engine)

AS query_expr (CTAS)

Examples

Create a non-partitioned table

No distribution or partition key defined; primary key used as distribution key

No primary key or distribution key defined; system auto-generates both

Primary and distribution key defined, no partition key

Create indexes on specific columns

Define a full-text index

Define a vector index

Define a foreign key index

Define a JSON Array index

FAQ

Compression and storage

Column attributes and constraints

Distribution key, partition key, and lifecycle

Indexes

Columnar storage

Other

Common errors

Related topics