This topic describes how to create partitioned tables and replicated tables by using the CREATE TABLE statement in AnalyticDB for MySQL and how to specify distribution keys, partition keys, indexes, partition lifecycle, and tiered storage policies of hot and cold data for tables.
The following figure shows the concepts that you need to familiarize yourself with before you create a table, such as shards, partitions, and clustered indexes.
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'By default, internal tables of AnalyticDB for MySQL use the zstd compression algorithm.
Parameters
table_name, column_name, column_type, and COMMENT
table_constraints (Indexes)
AnalyticDB for MySQL supports a variety of indexes, including INDEX, PRIMARY KEY, CLUSTERED KEY, FOREIGN KEY, FULLTEXT INDEX, and ANN INDEX. A table can have one or more types of indexes.
INDEX | KEY
Specifies a regular index. INDEX and KEY can be used interchangeably.
For XUANWU_V2 tables, AnalyticDB for MySQL does not create indexes on all columns of the table by default. If a primary key is specified for the table, AnalyticDB for MySQL creates a regular index only on the primary key by default.
For XUANWU tables, indexes are created on all columns of the table by default. However, if you create an index on specific columns when you create a XUANWU table, such as an index on the id column by using
INDEX (id), AnalyticDB for MySQL does not automatically create indexes on other columns of the table.
Note: You cannot use INDEX (column1,column2) to create a composite index on multiple columns.
PRIMARY KEY
Specifies the primary key index.
Overview
Each table can have only one primary key.
The primary key can consist of a single column, such as
PRIMARY KEY (id), or multiple columns, such asPRIMARY KEY (id,name).A composite primary key must include a distribution key and a partition key. We recommend that you place distribution and partition keys in the front section of a composite primary key.
Usage notes
You cannot perform the DELETE or UPDATE operation on tables that do not have primary keys.
If no primary key is specified, the following rules apply:
If no distribution key is specified, AnalyticDB for MySQL automatically adds the
__adb_auto_id__column to the table and uses the column as the primary key and the distribution key.If a distribution key is specified, AnalyticDB for MySQL does not automatically add a primary key.
After a table is created, you cannot add, remove, or change primary key columns.
To ensure high performance, we recommend that you select one or a small number of numeric columns as the primary key.
If a table has excessive primary key columns, the following issues may occur:
Higher consumption of CPU and I/O resources. This is because AnalyticDB for MySQL checks whether duplicate primary key values exist when data is written.
Higher disk usage of primary key indexes. To view the disk usage of primary key indexes, use the Storage Analysis feature.
Slower partition rebuilding speed of BUILD jobs.
CLUSTERED KEY
Specifies a clustered index. A clustered index is configured at the partition level. It determines the physical order in which data is stored. Data in a partition is sorted and stored sequentially based on the values of the clustered index. By default, data is sorted and stored in ascending order. Data records with the same or similar key values of the clustered index are stored in the same or adjacent data blocks. In range queries or equivalence filtering, using a clustered index can reduce disk I/O and speed up data reads. This is because the storage engine can read contiguous data blocks when query conditions are the same as clustered index columns.
Applicable scenarios
A clustered index works well with range queries and equivalence filtering. A column that is frequently used in the conditions of range queries or equivalence filtering is an ideal clustered index column.
When query conditions match or partially match a clustered index column, read efficiency significantly improves. For example, you can use the user ID as the clustered index in software-as-a-service (SaaS) applications. This ensures that the records of a specific user ID are stored in the same or contiguous data blocks and improves data query performance.
Usage notes
Each table can have only one clustered index.
A clustered index can be created on a single column, such as
CLUSTERED KEY index(id), or on multiple columns, such asCLUSTERED KEY index(id,name). When the clustered index involves two columns, data is first sorted based on the values of the first clustered index column. If the values of the first clustered index column are the same, data is sorted based on the values of the second clustered index column. Therefore,CLUSTERED KEY index(id,name)andCLUSTERED KEY index(name,id)are different clustered indexes.By default, clustered indexes are sorted in ascending order and are suitable for ascending queries. If your query is in descending order, set the order of the clustered index to descending when you create a table, such as
CLUSTERED KEY index(id) DESC. If you want to query data from an existing table, you can delete the existing clustered index and create a clustered index in descending order.To prevent reduced sorting performance, we recommend that you do not use a column that has lengthy values (such as strings larger than 10 KB) in a clustered index.
FULLTEXT INDEX | FULLTEXT KEY
Specifies a full-text index.
FOREIGN KEY
Specifies a foreign key index. Foreign key indexes are used to eliminate unnecessary joins.
ANN INDEX
Specifies a vector index.
Note: You cannot create vector indexes for XUANWU_V2 tables.
JSON INDEX
Specifies a JSON index or a JSON array index.
table_attribute (Distribution key)
Determines whether a table is a standard or replicated table.
DISTRIBUTED BY HASH: specifies the table as a standard table. A standard table can make full use of the query capability of a distributed system to improve query efficiency. Each standard table can store up to hundreds of billions of data records.
DISTRIBUTED BY BROADCAST: specifies the table as a replicated table. A replicated table stores a data replica in each shard of the AnalyticDB for MySQL cluster to which the table belongs. We recommend that you store up to 20,000 rows in each replicated table.
DISTRIBUTED BY HASH (column_name,...)
Specifies the distribution key of a table. A table that has a distribution key is a partitioned table (standard table). AnalyticDB for MySQL calculates the hash values of the distribution key and divides the table into shards based on the hash values. Sharding improves scalability and query performance.
Overview
Each table can have only one distribution key.
Each distribution key can contain one or more columns.
Distribution key columns must be included in the primary key columns. For example, if the distribution key is the customer_id column, the primary key must include the customer_id column.
Usage notes
If you do not specify a distribution key when you create a table, the following rules apply:
If the table has a primary key, AnalyticDB for MySQL uses the primary key as the distribution key.
If the table does not have a primary key, AnalyticDB for MySQL automatically adds the
__adb_auto_id__column to the table and uses the column as the primary key and the distribution key.
After a table is created, you cannot add, remove, or change distribution key columns. To change a distribution key, you must create a table that has the desired distribution key and migrate your data to the new table.
Recommendations
Select fewer columns to make the distribution key more suitable for a variety of complex queries.
Select columns whose values are evenly distributed as the distribution key, such as the transaction ID, device ID, user ID, or auto-increment columns. However, these columns are not ideal distribution keys when query conditions are limited to a small number of column values. For example, if Column A has evenly distributed values but the query condition is always A=3, setting Column A as the distribution key causes data hotspot.
Select columns that can be used to join tables as the distribution key. This way, data with the same distribution key values in two joined tables is distributed to the same shard. Join operations are performed on the same shard without the need of data transmission among shards. This minimizes data redistribution and improves query performance. For example, if your use scenario involves querying the order history of customers, you can specify the
customer_idcolumn as the distribution key.Do not select columns of the date, time, and timestamp types as the distribution key. The preceding columns are prone to data skew during data writes and may degrade write performance. Most queries are limited to a period of time such as a day or a month. In this case, the data that you want to query may exist on only one node, and queries cannot leverage the processing capabilities of all nodes in a distributed database system. We recommend that you select columns of the date and time types as the partition key.
You can use the storage diagnostics feature to check whether a column is a suitable distribution key and whether data skew occurs.
DISTRIBUTED BY BROADCAST
Specifies a replicated table. A replicated table stores a data replica in each shard of the AnalyticDB for MySQL cluster to which the table belongs. We recommend that you do not store a large amount of data in each replicated table.
Advantages: When you perform a JOIN query, you do not need to transmit the data of a replicated table across different nodes. This significantly reduces network transmission overheads and improves cluster stability in high-concurrency scenarios.
Disadvantages: When data is changed after you perform the INSERT, UPDATE, and DELETE operations in a replicated table, these changes are broadcast to all nodes of the cluster to ensure data consistency. However, this affects the overall write performance. Therefore, we recommend that you do not frequently create, delete, or modify replicated tables.
partition_options (Partition key and lifecycle)
If a single shard contains a large amount of data after you specify a distribution key for a table, you can divide the shard into partitions to speed up data filtering and improve query performance.
PARTITION BY
Specifies a partition key.
Syntax: PARTITION BY VALUE {(column_name)|(DATE_FORMAT(column_name, 'format'))|(FROM_UNIXTIME(column_name, 'format'))} LIFECYCLE n
Parameters
column_name: the name of the partition key. In the
PARTITION BY VALUE(column_name)syntax, partitioning is based on the values of thecolumn_namecolumn. The partition key can be one of the following types: numeric, datetime, or a string that specifies a number.DATE_FORMAT(column_name, 'format'))|FROM_UNIXTIME(column_name, 'format'): converts a datetime column to a specific date format by using theDATE_FORMAT()orFROM_UNIXTIME()function and partitions the data. The specified date format supports only year, month, and day in the following forms: %Y, %y, %Y%m, %y%m, %Y%m%d, and %y%m%d. After a table is created, you can change the format by executing the ALTER TABLE statement.If the
columnis of the BIGINT, TIMESTAMP, DATETIME, or VARCHAR type, you must use theDATE_FORMAT()function. Example for a BIGINT column: 1734278400000 (UNIX timestamp in milliseconds). Example for a TIMESTAMP, DATETIME, or VARCHAR column: 2024-11-26 00:01:02.If the
columnis of the INT type, you must use theFROM_UNIXTIME()function. Example: 1696266000 (UNIX timestamp in seconds).
Usage notes
For AnalyticDB for MySQL clusters earlier than V3.2.1.0, you must use the
LIFECYCLE nparameter to specify the lifecycle of partitions when you use thePARTITION BYclause to specify a partition key. Otherwise, an error occurs.For AnalyticDB for MySQL clusters of V3.2.1.0 or later, the
LIFECYCLE nparameter is optional when you use thePARTITION BYclause to specify a partition key. If you do not specify the LIFECYCLE n parameter, partition data is not deleted.After a table is created, you cannot add partition keys, or add, remove, or change partition key columns. To add or change a partition key, create a table that has the desired partition key and migrate data to the new table. For more information, see ALTER TABLE.
Recommendations
We recommend that you use a datetime column as the partition key.
A partition that is excessively large or small may affect read/write performance or even cluster stability. For information about the recommended partition size and the criteria for partition field reasonability, see the "Partitioned table diagnostics" section of the Storage diagnostics topic.
We recommend that you do not frequently update data in historical partitions. If you frequently update data in historical partitions, you may need to change the partition key.
LIFECYCLE n
The LIFECYCLE n parameter must be used together with the PARTITION BY clause. You can use this parameter to manage the lifecycle of partitions. AnalyticDB for MySQL sorts partitions in descending order based on partition key values. The first n partitions are retained and the other partitions are deleted.
For AnalyticDB for MySQL clusters earlier than V3.2.1.1, the
LIFECYCLE nparameter specifies that up to n partitions can be retained in each shard. The partition lifecycle is managed at the shard level. However, if data is unevenly distributed or the amount of data is excessively small, more than n partitions may be retained in specific shards.For AnalyticDB for MySQL clusters of V3.2.1.1 or later, the partition lifecycle is managed at the table level. The
LIFECYCLE nparameter specifies that up to n partitions can be retained in each table. However, for tables that are created before AnalyticDB for MySQL clusters are updated to V3.2.1.1 or later, the partition lifecycle is managed at the shard level. TheLIFECYCLE nparameter specifies that up to n partitions can be retained in each shard.
Example
PARTITION BY VALUE (DATE_FORMAT(date, '%Y%m%d')) LIFECYCLE 30 specifies that during partitioning, data in the date column is converted into the yyyyMMdd format and up to 30 partitions are retained. Assume that data from the 1st to the 30th day is written to corresponding partitions from Partition 20231201 to Partition 20231230. On the 31st day when data is written to Partition 20231231, the partition with the smallest partition key value (Partition 20231201) is automatically deleted because only up to 30 partitions can be retained.
INDEX_ALL (Indexes on all columns)
STORAGE_POLICY (Storage policy)
Enterprise Edition, Basic Edition, and Data Lakehouse Edition clusters and Data Warehouse Edition clusters in elastic mode for Cluster Edition allow you to specify a data storage policy. Storage policies vary in read/write performance and storage costs.
Valid values:
hot: hot storage. For XUANWU tables, all data is hot data by default. Data in all partitions of the entire table is stored on SSDs. Hot storage has the best read/write performance but incurs the highest storage cost.
cold: cold storage. For XUANWU_V2 tables, all data is cold data. Data in all partitions of the entire table is stored in Object Storage Service (OSS). Compared with hot storage, cold storage has lower read/write performance but is the least costly option.
mixed: a combination of hot storage and cold storage, also called tiered storage. This policy reduces storage costs and ensures query performance by storing frequently accessed data (hot data) on SSDs and infrequently accessed data (cold data) in OSS. If you set the STORAGE_POLICY parameter to mixed, you must use the
PARTITION BYclause to specify a partition key and thehot_partition_countparameter to specify the number of hot partitions. If you do not specify a partition key, tiered storage does not take effect and data is stored on SSDs.
Examples
Create a partitioned table that is automatically partitioned by date
Create a partitioned table named sales that is automatically partitioned by the date value of the sale_time volume.
CREATE TABLE sales (
sale_id BIGINT NOT NULL COMMENT 'Order ID',
customer_id VARCHAR NOT NULL COMMENT 'Customer ID',
phone_num BIGINT NOT NULL COMMENT 'Phone number',
revenue DECIMAL(15, 2) COMMENT 'Total 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')); Create a partitioned table that is configured with a partition lifecycle
Create a partitioned table named customer. Specify login_time, customer_id, and phone_num as the composite primary key, customer_id as the distribution key, and login_time as the partition key. Set the partition lifecycle to 30.
All partitions are sorted in descending order based on the values of the login_time partition key. Only the first 30 partitions are retained. When data is written to the 31st partition, the partition with the smallest partition key value is automatically deleted.
Assume that data from the 1st (with login_time value 20231201) to the 30th (with login_time value 20231230) day is written to corresponding partitions from Partition 20231201 to Partition 20231230. When data with login_time value 20231231 is written to the database on the 31st day, the partition with the smallest login_time value (Partition 20231201) is automatically deleted. This way, only data within the last 30 days is retained.
CREATE TABLE customer (
customer_id BIGINT NOT NULL COMMENT 'Customer ID',
customer_name VARCHAR NOT NULL COMMENT 'Customer name',
phone_num BIGINT NOT NULL COMMENT 'Phone number',
city_name VARCHAR NOT NULL COMMENT 'City',
sex INT NOT NULL COMMENT 'Gender',
id_number VARCHAR NOT NULL COMMENT 'ID card number',
home_address VARCHAR NOT NULL COMMENT 'Home address',
office_address VARCHAR NOT NULL COMMENT 'Office address',
age INT NOT NULL COMMENT 'Age',
login_time TIMESTAMP NOT NULL COMMENT 'Logon time',
PRIMARY KEY (login_time,customer_id,phone_num)
)
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT 'Customer information table'; Create a non-partitioned table
Create a non-partitioned table without distribution or partition keys
If you create a table that has a primary key but not a distribution key, AnalyticDB for MySQL automatically uses the primary key as the distribution key.
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 'Total amount',
order_date DATE NOT NULL COMMENT 'Order date',
PRIMARY KEY(order_id,order_date)
);Query the statement that is used to create the table and verify that 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 'Total 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)Create a non-partitioned table without primary or distribution keys
If you create a table that does not have a primary key or a distribution key, AnalyticDB for MySQL adds the __adb_auto_id__ column to the table and uses the column as the primary key and the 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 'Total amount',
order_date DATE NOT NULL COMMENT 'Order date'
);Query the statement that is used to create the table and verify that the auto-increment column named __adb_auto_id__ is automatically added to the table and used as the primary key and the 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 'Total 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)Create a non-partitioned table with primary and distribution keys but without a partition key
Create a table named supplier that uses the supplier_id auto-increment column as the distribution key and is sharded based on the hash values of 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);Specify storage policies
Specify a cold storage policy
CREATE TABLE item (
order_id BIGINT NOT NULL,
item_id INT NOT NULL,
quantity DECIMAL(15, 2) NOT NULL,
discount DECIMAL(15, 2) NOT NULL,
shipdate DATE NOT NULL,
PRIMARY KEY (order_id,item_id,shipdate)
)
DISTRIBUTED BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='COLD';Specify a hot storage policy
CREATE TABLE item (
order_id BIGINT NOT NULL,
item_id INT NOT NULL,
quantity DECIMAL(15, 2) NOT NULL,
discount DECIMAL(15, 2) NOT NULL,
shipdate DECIMAL NOT NULL,
PRIMARY KEY (order_id,item_id,shipdate)
)
DISTRIBUTED BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='HOT';Specify a tiered storage policy and set the number of hot partitions to 16
CREATE TABLE item (
order_id BIGINT NOT NULL,
item_id INT NOT NULL,
quantity DECIMAL(15, 2) NOT NULL,
discount DECIMAL(15, 2) NOT NULL,
shipdate DATE NOT NULL,
PRIMARY KEY (order_id,item_id,shipdate)
)
DISTRIBUTED BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=16;Create regular indexes on specific columns
Create regular indexes on the id and date columns.
CREATE TABLE index_tb (
id INT,
sales DECIMAL(15, 2),
date DATE,
INDEX (id),
INDEX (date),
PRIMARY KEY (id)
)
DISTRIBUTED BY HASH(id);Specify a full-text index
Create a full-text index named fidx_c on the content column.
CREATE TABLE fulltext_tb (
id INT,
content VARCHAR,
keyword VARCHAR,
FULLTEXT INDEX fidx_c(content),
PRIMARY KEY (id)
)
DISTRIBUTED BY HASH(id);For information about how to create and change a full-text index, see the "Create a full-text index for an existing table" section of the Create a full-text index topic.
For information about full-text search, see Full-text search.
Specify a vector index
Create a table that has a four-dimensional vector column of the ARRAY<SMALLINT> type named short_feature and a four-dimensional vector column of the ARRAY<FLOAT> type named float_feature.
Create vector indexes named short_feature_index and float_feature_index on the vector columns for the table.
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 indexing and vector search, see Vector search.
Specify a foreign key index
Create a table named store_returns. Use the FOREIGN KEY clause to associate the sr_item_sk column of the store_returns table 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)
);Specify a JSON array index
Create a table and create a JSON array index named idx_vj on the vj column.
CREATE TABLE json(
id INT,
vj JSON,
INDEX idx_vj(vj->'$[*]')
)
DISTRIBUTED BY HASH(id);For information about how to create and change JSON array indexes, see the "Create a JSON array index" section of the JSON indexes topic and the "JSON array indexes" section of the ALTER TABLE topic.
FAQ
Column attributes and constraints
Distribution key, partition key, and lifecycle
A distribution key is used in sharding. Based on the hash values of the distribution key, data in a table is distributed across different shards. A partition key is used in partitioning. Within a shard, data is further distributed across different partitions based on the values of the partition key. The following figure shows how sharding and partitioning work.
Indexes
Column-oriented storage
Others
Common errors and troubleshooting
References
For information about how to write data to a table, see INSERT INTO.
For information about how to insert query results into a table or overwrite specific data in a table with query results, see INSERT SELECT FROM or INSERT OVERWRITE SELECT.
For information about how to import data from data sources, such as ApsaraDB RDS, MaxCompute, and OSS, into AnalyticDB for MySQL, see Data import.