このトピックでは、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 圧縮アルゴリズムを使用します。
パラメーター
パラメーター
例
日付による自動パーティション分割
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 の各列が composite primary key を構成します。distribution key は customer_id、partition key は login_time、lifecycle は 30 に設定されます。
パーティションは partition key login_time（例：20231202、20231201）の降順でソートされます。システムは、キー値が最大の上位 30 個のパーティションのみを保持します。31 番目のパーティションにデータが書き込まれると、システムは自動的にキー値が最小のパーティションを削除します。
たとえば、1 日目に login_time が 20231201 のデータが書き込まれ、2 日目に 20231202 のデータが書き込まれる、といった具合に処理が進むと仮定します。31 日目に login_time が 20231231 のデータが書き込まれると、システムは自動的に最も古いデータを持つパーティション（'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 '顧客情報テーブル'; 非パーティションテーブル
デフォルト動作：プライマリキーを分散キーとして使用
テーブルに primary key を定義するものの distribution key を指定しない場合、AnalyticDB for MySQL はデフォルトで primary key を distribution key として使用します。
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)
);SHOW CREATE TABLE 文を実行して結果を確認します。出力には、primary key 列である order_id および order_date が distribution key としても使用されていることが示されます。
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)デフォルト動作：自動追加されるプライマリキーおよび分散キー
primary key や distribution key を指定せずにテーブルを作成すると、AnalyticDB for MySQL は自動的に __adb_auto_id__ という名前の column を追加し、これを primary key および distribution key の両方として使用します。
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 '注文日'
);SHOW CREATE TABLE 文を実行して結果を確認します。出力には、AnalyticDB for MySQL が auto-increment column である __adb_auto_id__ を追加し、これを primary key および 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 '注文 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 という名前のテーブルを作成します。supplier_id 列は auto-increment column です。distribution key は supplier_id であり、データは supplier_id の値をハッシュ化することでシャーディングされます。
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);詳細については、「フルテキストインデックスの作成」をご参照ください。
詳細については、「全文検索」をご参照ください。
ベクトルインデックスの定義
short_feature（型：array<smallint>）および float_feature（型：array<float>）を、ディメンションが 4 のベクトルカラムとして定義します。
short_feature カラムにベクトルインデックス short_feature_index を、float_feature カラムにベクトルインデックス 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 句を使用して、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 配列インデックス」をご参照ください。
よくある質問
列の属性と制約
分散キー、パーティションキー、およびライフサイクル
インデックス
列指向ストレージ
その他
一般的なエラー
関連トピック
-
テーブルにデータを書き込む方法については、「INSERT INTO」をご参照ください。
-
クエリ結果を使用してテーブルにデータを挿入または上書きする方法については、「INSERT SELECT FROM」または「INSERT OVERWRITE SELECT」をご参照ください。
-
ApsaraDB RDS、MaxCompute、OSS などのデータソースからデータをインポートする方法については、「データインポート」をご参照ください。