このトピックでは、AnalyticDB for MySQL で CREATE TABLE 文を使用してパーティションテーブルとレプリケートテーブルを作成する方法、およびテーブルの分散キー、パーティションキー、インデックス、パーティションのライフサイクル、ホットデータとコールドデータの階層型ストレージポリシーを指定する方法について説明します。
構文
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'デフォルトでは、AnalyticDB for MySQL の内部テーブルは zstd 圧縮アルゴリズムを使用します。
パラメーター
table_name、column_name、column_type、および COMMENT
partition_options (パーティションキーとライフサイクル)
INDEX_ALL (すべての列のインデックス)
例
日付で自動的にパーティション分割されるパーティションテーブルの作成
sale_time ボリュームの日付値で自動的にパーティション分割される sales という名前のパーティションテーブルを作成します。
CREATE TABLE sales (
sale_id BIGINT NOT NULL COMMENT '注文 ID',
customer_id VARCHAR NOT NULL COMMENT '顧客 ID',
phone_num BIGINT NOT NULL COMMENT '電話番号',
revenue DECIMAL(15, 2) COMMENT '合計金額',
sale_time TIMESTAMP NOT NULL COMMENT '注文時間',
PRIMARY KEY (sale_time,sale_id)
)
DISTRIBUTED BY HASH(sale_id)
PARTITION BY VALUE(DATE_FORMAT(sale_time, '%Y%m%d')); パーティションライフサイクルが設定されたパーティションテーブルの作成
customer という名前のパーティションテーブルを作成します。login_time、customer_id、および phone_num を複合プライマリキーとして、customer_id を分散キーとして、login_time をパーティションキーとして指定します。パーティションライフサイクルを 30 に設定します。
すべてのパーティションは、login_time パーティションキーの値に基づいて降順にソートされます。最初の 30 個のパーティションのみが保持されます。31 番目のパーティションにデータが書き込まれると、パーティションキー値が最も小さいパーティションが自動的に削除されます。
1 日目 (login_time 値 20231201) から 30 日目 (login_time 値 20231230) までのデータが、パーティション 20231201 からパーティション 20231230 までの対応するパーティションに書き込まれると仮定します。31 日目に login_time 値 20231231 のデータがデータベースに書き込まれると、login_time 値が最も小さいパーティション (パーティション 20231201) が自動的に削除されます。これにより、過去 30 日間のデータのみが保持されます。
CREATE TABLE customer (
customer_id BIGINT NOT NULL COMMENT '顧客 ID',
customer_name VARCHAR NOT NULL COMMENT '顧客名',
phone_num BIGINT NOT NULL COMMENT '電話番号',
city_name VARCHAR NOT NULL COMMENT '都市',
sex INT NOT NULL COMMENT '性別',
id_number VARCHAR NOT NULL COMMENT 'ID カード番号',
home_address VARCHAR NOT NULL COMMENT '自宅住所',
office_address VARCHAR NOT NULL COMMENT '勤務先住所',
age INT NOT NULL COMMENT '年齢',
login_time TIMESTAMP NOT NULL COMMENT 'ログイン時間',
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 '顧客情報テーブル'; 非パーティションテーブルの作成
分散キーまたはパーティションキーのない非パーティションテーブルの作成
プライマリキーはあるが分散キーがないテーブルを作成すると、AnalyticDB for MySQL は自動的にプライマリキーを分散キーとして使用します。
CREATE TABLE orders (
order_id BIGINT NOT NULL COMMENT '注文 ID',
customer_id INT NOT NULL COMMENT '顧客 ID',
order_status VARCHAR(1) NOT NULL COMMENT '注文ステータス',
total_price DECIMAL (15, 2) NOT NULL COMMENT '合計金額',
order_date DATE NOT NULL COMMENT '注文日',
PRIMARY KEY(order_id,order_date)
);テーブルを作成するために使用された文をクエリし、プライマリキー列 order_id と order_date が分散キーとして使用されていることを確認します。
SHOW CREATE TABLE orders;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` ( |
| | 'order_id' bigint NOT NULL COMMENT '注文 ID', |
| | 'customer_id' int NOT NULL COMMENT '顧客 ID', |
| | 'order_status' varchar(1) NOT NULL COMMENT '注文ステータス', |
| | 'total_price' decimal(15, 2) NOT NULL COMMENT '合計金額', |
| | 'order_date' date NOT NULL COMMENT '注文日', |
| | 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)プライマリキーまたは分散キーのない非パーティションテーブルの作成
プライマリキーまたは分散キーのないテーブルを作成すると、AnalyticDB for MySQL はテーブルに __adb_auto_id__ 列を追加し、その列をプライマリキーおよび分散キーとして使用します。
CREATE TABLE orders_new (
order_id BIGINT NOT NULL COMMENT '注文 ID',
customer_id INT NOT NULL COMMENT '顧客 ID',
order_status VARCHAR(1) NOT NULL COMMENT '注文ステータス',
total_price DECIMAL (15, 2) NOT NULL COMMENT '合計金額',
order_date DATE NOT NULL COMMENT '注文日'
);テーブルを作成するために使用された文をクエリし、__adb_auto_id__ という名前の自動インクリメント列が自動的にテーブルに追加され、プライマリキーおよび分散キーとして使用されていることを確認します。
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 '注文 ID', |
| | 'customer_id' int NOT NULL COMMENT '顧客 ID', |
| | 'order_status' varchar(1) NOT NULL COMMENT '注文ステータス', |
| | 'total_price' decimal(15, 2) NOT NULL COMMENT '合計金額', |
| | 'order_date' date NOT NULL COMMENT '注文日', |
| | 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)プライマリキーと分散キーはあるがパーティションキーのない非パーティションテーブルの作成
supplier_id 自動インクリメント列を分散キーとして使用し、supplier_id 値のハッシュ値に基づいてシャーディングされる supplier という名前のテーブルを作成します。
CREATE TABLE supplier (
supplier_id BIGINT AUTO_INCREMENT PRIMARY KEY,
supplier_name VARCHAR,
address INT,
phone VARCHAR
)
DISTRIBUTED BY HASH(supplier_id);ストレージポリシーの指定
コールドストレージポリシーの指定
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';ホットストレージポリシーの指定
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='HOT';階層型ストレージポリシーを指定し、ホットパーティションの数を 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;特定の列に通常のインデックスを作成する
id 列と date 列に通常のインデックスを作成します。
CREATE TABLE index_tb (
id INT,
sales DECIMAL(15, 2),
date DATE,
INDEX (id),
INDEX (date),
PRIMARY KEY (id)
)
DISTRIBUTED BY HASH(id);クラスター化インデックスの指定
quantity 列に clustered_index という名前のクラスター化インデックスを作成します。
CREATE TABLE clustered (
product_id INT,
product_name VARCHAR,
quantity INT,
price DECIMAL(10, 2),
CLUSTERED KEY INDEX clustered_index(quantity)
)
DISTRIBUTED BY HASH(product_id);フルテキストインデックスの指定
content 列に fidx_c という名前のフルテキストインデックスを作成します。
CREATE TABLE fulltext_tb (
id INT,
content VARCHAR,
keyword VARCHAR,
FULLTEXT INDEX fidx_c(content),
PRIMARY KEY (id)
)
DISTRIBUTED BY HASH(id);フルテキストインデックスの作成と変更方法については、「フルテキストインデックスの作成」をご参照ください。
全文検索については、「全文検索」をご参照ください。
ベクトルインデックスの指定
ARRAY<SMALLINT> 型の 4 次元ベクトル列 short_feature と、ARRAY<FLOAT> 型の 4 次元ベクトル列 float_feature を持つテーブルを作成します。
テーブルのベクトル列に short_feature_index と float_feature_index という名前のベクトルインデックスを作成します。
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;ベクトルインデックスとベクトル検索の詳細については、「ベクトル検索」をご参照ください。
外部キーインデックスの指定
store_returns という名前のテーブルを作成します。FOREIGN KEY 句を使用して、store_returns テーブルの sr_item_sk 列を customer テーブルのプライマリキー列 customer_id に関連付けます。
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)
);JSON 配列インデックスの指定
テーブルを作成し、vj 列に idx_vj という名前の JSON 配列インデックスを作成します。
CREATE TABLE json(
id INT,
vj JSON,
INDEX idx_vj(vj->'$[*]')
)
DISTRIBUTED BY HASH(id);JSON 配列インデックスの作成と変更方法については、「JSON インデックス」トピックの「JSON 配列インデックスの作成」セクションと、「ALTER TABLE」トピックの「JSON 配列インデックス」セクションをご参照ください。
よくある質問
列の属性と制約
分散キー、パーティションキー、およびライフサイクル
インデックス
列指向ストレージ
その他
一般的なエラーとトラブルシューティング
関連ドキュメント
テーブルにデータを書き込む方法については、「INSERT INTO」をご参照ください。
クエリ結果をテーブルに挿入したり、テーブルの特定のデータをクエリ結果で上書きしたりする方法については、「INSERT SELECT FROM」または「INSERT OVERWRITE SELECT」をご参照ください。
ApsaraDB RDS、MaxCompute、OSS などのデータソースから AnalyticDB for MySQL にデータをインポートする方法については、「データインポート」をご参照ください。