CREATE TABLE

新しいテーブルを作成します。デフォルトでは、テーブルは現在のサーバー上にのみ作成されます。分散 DDL クエリは ON CLUSTER 句によって実現されており、これについては別途説明しています。

構文形式

ユースケースに応じて、このクエリにはさまざまな構文形式があります。

明示的なスキーマを指定してテーブルを作成する

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [COMMENT 'comment for column'] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [COMMENT 'comment for column'] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine
  [COMMENT 'comment for table']

db が設定されている場合は db データベースに、設定されていない場合は現在のデータベースに、括弧内で指定した構造と engine エンジンを持つ table_name という名前のテーブルを作成します。テーブルの構造は、カラム定義、セカンダリ索引、プロジェクション、および制約の一覧です。主キーが engine でサポートされている場合は、テーブルエンジンのパラメータとして示されます。最も単純な場合、カラム定義は name type です。例: RegionID UInt32。型に続く修飾子である COMMENT、compression_codec、STATISTICS、TTL、COLLATE、PRIMARY KEY、およびカラムごとの SETTINGS は、任意の順序で記述でき、それぞれ最大 1 回だけ指定できます。たとえば、RegionID UInt32 CODEC(ZSTD) COMMENT 'comment for column' と RegionID UInt32 COMMENT 'comment for column' CODEC(ZSTD) は同じです。SHOW CREATE TABLE はカラム宣言を正規化することに注意してください。残る修飾子は常に COMMENT、CODEC、STATISTICS、TTL、COLLATE、SETTINGS の正規順序で出力され、カラムごとの PRIMARY KEY はカラム宣言からテーブルレベルの PRIMARY KEY 句へ移動されます。デフォルト値用の式も定義できます (下記を参照) 。必要に応じて、1 つ以上のキー式を使って主キーを指定できます。カラムおよびテーブルにコメントを追加できます。

既存テーブルのスキーマを使用してテーブルを作成する

CREATE TABLE [IF NOT EXISTS] [db2.]table_clone AS [db.]table [ENGINE = engine]

ClickHouse では、既存テーブルのスキーマとデータをコピーできます。既存テーブルのスキーマを複製するには: これにより、別のテーブルと同じ構造を持つテーブルが作成されます。

既存テーブルのスキーマとデータを使用してテーブルを作成する

既存テーブルのスキーマとデータを複製するには:

CREATE TABLE [IF NOT EXISTS] [db2.]table_clone CLONE AS [db.]table [ENGINE = engine]

これにより、既存のテーブルと同じスキーマとデータを持つテーブルが作成されます。新しいテーブルの作成後、db.table のすべてのパーティションがそのテーブルにアタッチされます。つまり、db.table のデータは作成時に db2.table_clone に複製されます。このクエリは、次のものと同等です。

CREATE TABLE [IF NOT EXISTS] [db2.]table_clone AS [db.]table [ENGINE = engine];
ALTER TABLE [db2.]table_clone ATTACH PARTITION ALL FROM [db.]table;

どちらの機能でも、テーブルには別のエンジンを指定できます。エンジンを指定しない場合は、元のテーブル (db.table) と同じエンジンが使用されます。

table function を使用してテーブルを作成する

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS table_function()

指定したtable functionと同じ結果を返すテーブルを作成します。作成されたテーブルも、指定した対応する table function と同じように動作します。

SELECT クエリでテーブルを作成する

CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...

SELECT クエリの結果と同様の構造を持つテーブルを engine エンジンで作成し、SELECT のデータを格納します。カラム定義を明示的に指定することもできます。テーブルがすでに存在し、IF NOT EXISTS が指定されている場合、このクエリは何も実行しません。クエリでは、ENGINE 句の後にほかの句を続けることもできます。テーブルの作成方法の詳細については、テーブルエンジンの説明を参照してください。例

Query

CREATE TABLE t1 (x String) ENGINE = Memory AS SELECT 1;
SELECT x, toTypeName(x) FROM t1;

Response

┌─x─┬─toTypeName(x)─┐
│ 1 │ String        │
└───┴───────────────┘

カラムのデフォルト値を指定する

カラム定義では、DEFAULT expr、MATERIALIZED expr、または ALIAS expr の形式でデフォルト値の式を指定できます。例: URLDomain String DEFAULT domain(URL)。式 expr は省略可能です。省略した場合は、カラム型を明示的に指定する必要があります。その場合のデフォルト値は、数値カラムでは 0、文字列カラムでは '' (空文字列) 、配列カラムでは [] (空の配列) 、日付カラムでは 1970-01-01、Nullable カラムでは NULL になります。デフォルト値を持つカラムでは、カラム型を省略でき、その場合は expr の型から推論されます。たとえば、EventDate DEFAULT toDate(EventTime) というカラムの型は date になります。データ型とデフォルト値の式の両方が指定されている場合は、式を指定された型に変換する暗黙的な型キャスト関数が挿入されます。例: Hits UInt32 DEFAULT 0 は内部的には Hits UInt32 DEFAULT toUInt32(0) として表現されます。デフォルト値の式 expr では、任意のテーブルカラムや定数を参照できます。ClickHouse は、テーブル構造の変更によって式の計算にループが生じないことを確認します。INSERT では、式を解決可能であること、つまり式の計算に必要なすべてのカラムが渡されていることを確認します。

DEFAULT

DEFAULT expr 通常のデフォルト値です。この種のカラムでは、値が INSERT クエリで指定されていない場合、expr から計算されます。例:

CREATE OR REPLACE TABLE test
(
    id UInt64,
    updated_at DateTime DEFAULT now(),
    updated_at_date Date DEFAULT toDate(updated_at)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test (id) VALUES (1);

SELECT * FROM test;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│  1 │ 2023-02-24 17:06:46 │      2023-02-24 │
└────┴─────────────────────┴─────────────────┘

MATERIALIZED

MATERIALIZED expr マテリアライズ式です。このようなカラムの値は、行の挿入時に、指定されたマテリアライズ式に基づいて自動的に計算されます。INSERT 時に値を明示的に指定することはできません。また、この型のデフォルト値カラムは、SELECT * の結果に含まれません。これは、SELECT * の結果を常に INSERT を使ってテーブルにそのまま挿入し直せるという不変条件を保つためです。この動作は、setting asterisk_include_materialized_columns を使用すると無効にできます。例:

CREATE OR REPLACE TABLE test
(
    id UInt64,
    updated_at DateTime MATERIALIZED now(),
    updated_at_date Date MATERIALIZED toDate(updated_at)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test VALUES (1);

SELECT * FROM test;
┌─id─┐
│  1 │
└────┘

SELECT id, updated_at, updated_at_date FROM test;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│  1 │ 2023-02-24 17:08:08 │      2023-02-24 │
└────┴─────────────────────┴─────────────────┘

SELECT * FROM test SETTINGS asterisk_include_materialized_columns=1;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│  1 │ 2023-02-24 17:08:08 │      2023-02-24 │
└────┴─────────────────────┴─────────────────┘

EPHEMERAL

EPHEMERAL [expr] 一時的なカラムです。この型のカラムはテーブルに保存されず、SELECT で取得することもできません。ephemeral カラムの唯一の用途は、これらを使って他のカラムのデフォルト値式を構築することです。カラムを明示的に指定しない INSERT では、この型のカラムはスキップされます。これは、SELECT * の結果を常に INSERT を使ってテーブルにそのまま挿入し直せるという不変条件を保つためです。例:

CREATE OR REPLACE TABLE test
(
    id UInt64,
    unhexed String EPHEMERAL,
    hexed FixedString(4) DEFAULT unhex(unhexed)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test (id, unhexed) VALUES (1, '5a90b714');

SELECT
    id,
    hexed,
    hex(hexed)
FROM test
FORMAT Vertical;

Row 1:
──────
id:         1
hexed:      Z��
hex(hexed): 5A90B714

ALIAS

ALIAS expr 計算カラム (同義語) です。この型のカラムはテーブルに保存されず、これらに値を INSERT することはできません。 SELECT クエリでこの型のカラムを明示的に参照すると、値はクエリ時に expr から計算されます。デフォルトでは、SELECT * には ALIAS カラムは含まれません。この動作は、設定 asterisk_include_alias_columns によって無効にできます。 ALTER クエリを使って新しいカラムを追加しても、それらのカラムについて古いデータは書き込まれません。代わりに、新しいカラムの値を持たない古いデータを読み取る際には、デフォルトで式がその場で計算されます。ただし、その式の実行にクエリ内で指定されていない別のカラムが必要な場合は、それらのカラムも追加で読み取られますが、必要なデータブロックに対してのみ行われます。テーブルに新しいカラムを追加したあとでそのデフォルト式を変更すると、古いデータに対して使われる値も変わります (ディスクに値が保存されていないデータについて) 。バックグラウンドマージの実行時には、マージ対象のいずれかのパーツに存在しないカラムのデータは、マージ後のパーツに書き込まれる点に注意してください。ネストされたデータ構造内の要素には、デフォルト値を設定できません。

CREATE OR REPLACE TABLE test
(
    id UInt64,
    size_bytes Int64,
    size String ALIAS formatReadableSize(size_bytes)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test VALUES (1, 4678899);

SELECT id, size_bytes, size FROM test;
┌─id─┬─size_bytes─┬─size─────┐
│  1 │    4678899 │ 4.46 MiB │
└────┴────────────┴──────────┘

SELECT * FROM test SETTINGS asterisk_include_alias_columns=1;
┌─id─┬─size_bytes─┬─size─────┐
│  1 │    4678899 │ 4.46 MiB │
└────┴────────────┴──────────┘

NULL または NOT NULL 修飾子

カラム定義では、データ型の後ろに NULL および NOT NULL 修飾子を付けることで、その型を Nullable にできるかどうかを指定できます。型が Nullable ではない場合、NULL を指定すると Nullable として扱われ、NOT NULL を指定した場合はそのまま Nullable にはなりません。たとえば、INT NULL は Nullable(INT) と同じです。型が Nullable であるにもかかわらず NULL または NOT NULL 修飾子を指定すると、例外がスローされます。関連項目: data_type_default_nullable 設定。

主キー

テーブルの作成時に主キーを定義できます。主キーは、次の2つの方法で指定できます。

カラムリスト内

CREATE TABLE [db.]table_name
(
    name1 type1, name2 type2, ...,
    PRIMARY KEY(expr1[, expr2,...])
)
ENGINE = engine;

カラムリストの外

CREATE TABLE [db.]table_name
(
    name1 type1, name2 type2, ...
)
ENGINE = engine
PRIMARY KEY(expr1[, expr2,...]);

1つのクエリで両方の方法を併用することはできません。

テーブル制約を指定する

カラムの説明に加えて、制約も定義できます:

CONSTRAINT

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    ...
    CONSTRAINT constraint_name_1 CHECK boolean_expr_1,
    ...
) ENGINE = engine

boolean_expr_1 には任意のブール式を指定できます。テーブルに制約が定義されている場合、INSERT クエリの各行に対してそれぞれの制約がチェックされます。いずれかの制約が満たされていない場合、サーバーは制約名とチェック式を含む例外を返します。大量の制約を追加すると、大規模な INSERT クエリのパフォーマンスに悪影響を及ぼす可能性があります。すべてのテーブルにある既存の制約は、system.constraints テーブルで確認できます。

ASSUME

ASSUME 句は、真であると仮定される table に対する CONSTRAINT を定義するために使用されます。この制約は、その後オプティマイザによって SQL queries のパフォーマンス向上に利用されます。 users_a table の作成時に ASSUME CONSTRAINT が使われている次の例を見てみましょう。

CREATE TABLE users_a (
    uid Int16, 
    name String, 
    age Int16, 
    name_len UInt8 MATERIALIZED length(name), 
    CONSTRAINT c1 ASSUME length(name) = name_len
) 
ENGINE=MergeTree 
ORDER BY (name_len, name);

ここでは、ASSUME CONSTRAINT を使って、length(name) 関数が常に name_len カラムの値と等しいことを前提としていることを示しています。つまり、クエリ内で length(name) が呼び出されるたびに、ClickHouse はそれを name_len に置き換えることができます。これにより length() 関数を呼び出さずに済むため、通常はそのほうが高速です。そのため、クエリ SELECT name FROM users_a WHERE length(name) < 5; を実行する際、ClickHouse は ASSUME CONSTRAINT に基づいて、これを SELECT name FROM users_a WHERE name_len < 5; に最適化できます。これにより、各行ごとに name の長さを計算する必要がなくなるため、クエリの実行速度が向上する可能性があります。 ASSUME CONSTRAINT は制約を強制するものではありません。これは、その制約が成り立つことをオプティマイザに知らせるだけです。制約が実際には成り立っていない場合、クエリの結果が不正確になる可能性があります。したがって、ASSUME CONSTRAINT は、その制約が正しいと確信できる場合にのみ使用してください。

TTL で保存期間を定義する

値の保存期間を定義します。指定できるのは MergeTree ファミリーのテーブルのみです。詳細は、カラムとテーブルの TTL を参照してください。

カラム圧縮コーデックを選択する

デフォルトでは、セルフマネージド版のClickHouseには lz4 圧縮が、ClickHouse Cloudには zstd 圧縮が適用されます。CREATE TABLE クエリでは、カラムごとに圧縮方式を指定することもできます。

CREATE TABLE codec_example
(
    dt Date CODEC(ZSTD),
    ts DateTime CODEC(LZ4HC),
    float_value Float32 CODEC(NONE),
    double_value Float64 CODEC(LZ4HC(9)),
    value Float32 CODEC(Delta, ZSTD)
)
ENGINE = <Engine>
...

利用可能な汎用、特殊用途、および暗号化コーデックについては、カラム圧縮コーデックを参照してください。

一時テーブルの作成

ClickHouse は一時テーブルをサポートしています。一時テーブルはセッションの終了時に削除されます。詳細については、CREATE TEMPORARY TABLE を参照してください。

REPLACE TABLE でテーブルをアトミックに更新する

REPLACE ステートメントを使用すると、テーブルをアトミックに更新できます。詳細については、REPLACE TABLEを参照してください。

テーブルコメントを追加する

テーブルの作成時にコメントを追加できます。構文

CREATE TABLE [db.]table_name
(
    name1 type1, name2 type2, ...
)
ENGINE = engine
COMMENT 'Comment'

COMMENT 句は、PARTITION BY、ORDER BY、ストレージ固有の SETTINGS など、ストレージ固有の句の後に指定する必要があります。COMMENT 句の後に解析されるのは、ストレージ関連の設定ではなく、max_threads などのクエリ固有の SETTINGS のみです。つまり、句の正しい順序は次のとおりです。

ENGINE
ストレージ句
COMMENT
クエリ設定 (ある場合)

例

Query

CREATE TABLE t1 (x String) ENGINE = Memory COMMENT 'The temporary table';
SELECT name, comment FROM system.tables WHERE name = 't1';

Response

┌─name─┬─comment─────────────┐
│ t1   │ The temporary table │
└──────┴─────────────────────┘

ブログ: スキーマとコーデックによるClickHouseの最適化
ブログ: ClickHouseで時系列データを扱う

SQLリファレンス

データ型

エンジン

関数

フォーマット

インターフェイス

設定

システムテーブル

データレイク

構文形式

明示的なスキーマを指定してテーブルを作成する

既存テーブルのスキーマを使用してテーブルを作成する

既存テーブルのスキーマとデータを使用してテーブルを作成する

table function を使用してテーブルを作成する

SELECT クエリでテーブルを作成する

カラムのデフォルト値を指定する

DEFAULT

MATERIALIZED

EPHEMERAL

ALIAS

NULL または NOT NULL 修飾子

主キー

テーブル制約を指定する

CONSTRAINT

ASSUME

TTL で保存期間を定義する

カラム圧縮コーデックを選択する

一時テーブルの作成

REPLACE TABLE でテーブルをアトミックに更新する

テーブルコメントを追加する

​構文形式

​明示的なスキーマを指定してテーブルを作成する

​既存テーブルのスキーマを使用してテーブルを作成する

​既存テーブルのスキーマとデータを使用してテーブルを作成する

​table function を使用してテーブルを作成する

​SELECT クエリでテーブルを作成する

​カラムのデフォルト値を指定する

​DEFAULT

​MATERIALIZED

​EPHEMERAL

​ALIAS

​NULL または NOT NULL 修飾子

​主キー

​テーブル制約を指定する

​CONSTRAINT

​ASSUME

​TTL で保存期間を定義する

​カラム圧縮コーデックを選択する

​一時テーブルの作成

​REPLACE TABLE でテーブルをアトミックに更新する

​テーブルコメントを追加する

​関連コンテンツ

構文形式

明示的なスキーマを指定してテーブルを作成する

既存テーブルのスキーマを使用してテーブルを作成する

既存テーブルのスキーマとデータを使用してテーブルを作成する

table function を使用してテーブルを作成する

SELECT クエリでテーブルを作成する

カラムのデフォルト値を指定する

DEFAULT

MATERIALIZED

EPHEMERAL

ALIAS

NULL または NOT NULL 修飾子

主キー

テーブル制約を指定する

CONSTRAINT

ASSUME

TTL で保存期間を定義する

カラム圧縮コーデックを選択する

一時テーブルの作成

REPLACE TABLE でテーブルをアトミックに更新する

テーブルコメントを追加する

関連コンテンツ