ApsaraDB for ClickHouse:テーブルエンジン

テーブルエンジンはテーブルのタイプを参照します。 ApsaraDB for ClickHouseでは、データの保存方法と読み取り方法、インデックスがサポートされているかどうか、プライマリ /セカンダリレプリケーションがサポートされているかどうかがテーブルエンジンによって決定されます。 次の表に、ApsaraDB for ClickHouseでサポートされているテーブルエンジンを示します。

この例では、主キーを使用して特徴の違いを示します。 ApsaraDB for ClickHouseのSQL構文では、プライマリキーを使用して重複を削除し、データが一意になるようにします。 MergeTreeテーブルエンジンでは、主キーを使用してクエリを高速化します。 コンパクションが完了した後でも、同じ主キーを持つデータ行はまだ存在します。

次の例は、MergeTreeテーブルエンジンの使用方法を示しています。

1. test_tblという名前のテーブルを作成します。 主キーは (id, create_time) です。 格納されたデータは主キーに基づいてソートされ、create_timeの値に基づいてパーティション分割されます。

   CREATE TABLE test_tbl ON CLUSTER default (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = MergeTree()
      PARTITION BY create_time
        ORDER BY  (id, create_time)
        PRIMARY KEY (id, create_time)
        SETTINGS index_granularity=8192;

2. 主キーが重複しているデータを書き込みます。

   insert into test_tbl values(1, '2019-12-13', null);
   insert into test_tbl values(1, '2019-12-13', null);
   insert into test_tbl values(2, '2019-12-14', null);
   insert into test_tbl values(3, '2019-12-15', null);
   insert into test_tbl values(3, '2019-12-15', null);

3. データの照会

   select * from test_tbl;

   次のクエリ結果が返されます。

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

4. OPTIMIZE文を実行して, バックグラウンドで強制的にコンパクションを実行します。 圧縮が強制的に実行されるのは、MergeTreeファミリのテーブルエンジンがログ構造のマージツリー (LSMツリー) に似た構造を使用し、ストレージ層の処理ロジックが圧縮フェーズが開始されるまで実装されないためです。

   optimize table test_tbl final;

5. データを再度照会します。

   select * from test_tbl;

   次のクエリ結果が返されます。 主キーが重複しているデータはまだ存在します。

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

ReplacingMergeTreeテーブルエンジンでは、同じ主キーを持つ重複を削除できますが、テーブルエンジンには次の制限があります。 したがって、ReplacingMergeTreeテーブルエンジンを使用して、データの重複が最終的に削除されるようにしますが、クエリプロセス中にプライマリキーが一意になるようにすることはできません。

• 分散シナリオでは、同じ主キーを有するデータは、異なるノードに分散され得る。 異なるシャード間のデータの重複は削除できません。

• OPTIMIZEステートメントの実行が完了する前に、同じ主キーを持つ重複は削除できません。 たとえば、一部のデータでは重複が削除されますが、他のデータでは同じ主キーを持つ重複は削除されません。

• OPTIMIZE文はバックグラウンドで実行され、実行時間は予測できません。

• 大量のデータが存在するシナリオでは、OPTIMIZEステートメントを手動で実行するのに長い時間が必要です。 この場合、リアルタイムクエリの要件を満たすことができません。

次の例は、ReplacingMergeTreeテーブルエンジンの使用方法を示しています。

1. test_tbl_replacingという名前のテーブルを作成します。

   CREATE TABLE test_tbl_replacing (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = ReplacingMergeTree()
      PARTITION BY create_time
        ORDER BY  (id, create_time)
        PRIMARY KEY (id, create_time)
        SETTINGS index_granularity=8192;

2. 主キーが重複しているデータを書き込みます。

   insert into test_tbl_replacing values(1, '2019-12-13', null);
   insert into test_tbl_replacing values(1, '2019-12-13', null);
   insert into test_tbl_replacing values(2, '2019-12-14', null);
   insert into test_tbl_replacing values(3, '2019-12-15', null);
   insert into test_tbl_replacing values(3, '2019-12-15', null);

3. データの照会

   select * from test_tbl_replacing;

   次のクエリ結果が返されます。

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘ 

4. OPTIMIZE文を実行して, バックグラウンドで強制的にコンパクションを実行します。 MergeTreeファミリのテーブルエンジンはLSMツリーに似た構造を使用し、ストレージ層の処理ロジックはコンパクションフェーズが開始されるまで実装されないため、コンパクションが強制的に実行されます。

   optimize table test_tbl_replacing final;

5. データを再度照会します。

   select * from test_tbl_replacing;

   次のクエリ結果が返されます。 主キーが重複しているデータは削除されます。

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘     

CollapsingMergeTreeテーブルエンジンにより、ReplacingMergeTreeテーブルエンジンの機能の制限がなくなります。 CollapsingMergeTreeテーブルエンジンを使用する場合は、CREATE tableステートメントでSign列を指定する必要があります。 行は、Sign列の値に基づいて、state rowとcancel rowの2つのカテゴリに分けられます。 Sign列の値が1行は、状態行と呼ばれます。 状態行は状態を追加するために使用されます。 Sign列の値が -1である行は、キャンセル行と呼ばれます。 キャンセル行は状態を削除するために使用されます。

バックグラウンドでの圧縮中に、同じ主キーと反対のSign値を持つ行が折りたたまれるか削除されます。 コンパクションが実行されない場合、state行とcancel行が同時に存在します。 同じ主キーを持つ行を折りたたむか削除するには、ビジネス層で次の操作を実行します。

• 状態を削除する前に、元の状態行の値を記録するか、データベースを照会して元の状態行の値を取得します。

  その理由は、状態を削除するときにキャンセル行を記述する必要があるためです。 Cancel行には、Sign列を除いて、元の状態の行の主キーと同じ主キーを持つデータが含まれている必要があります。

• count() やsum(col) などの集計関数を実行すると、データの冗長性が存在する場合があります。 有効な結果を取得するには、ビジネス層でSQL文を変更します。 count() をsum(Sign) に、sum(col) をsum(col * Sign) に変更します。

  次の理由があります。

  • バックグラウンドでコンパクションを実行する時間は予測できません。 クエリを開始すると、状態行とキャンセル行が折りたたまれたり削除されたりすることはありません。

  • ApsaraDB for ClickHouseは、同じプライマリキーを持つ行が同じノードに収まるようにすることはできません。 ノード間でデータを縮小または削除することはできません。

次に、CollapsingMergeTreeテーブルエンジンの使用例を示します。

1. test_tbl_colflapingという名前のテーブルを作成します。

   CREATE TABLE test_tbl_collapsing
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8
   )
   ENGINE = CollapsingMergeTree(Sign)
   ORDER BY UserID;

2. Sign列の値が1である状態行を挿入します。

   INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, 1);

   説明

   キャンセル行を挿入してからステート行を挿入すると、キャンセル行とステート行が故障している可能性があります。 この場合、バックグラウンドで強制的にコンパクションを行っても、同一の主キーを持つデータを破棄または削除することはできません。

3. Sign列の値が -1であるキャンセル行を挿入します。 キャンセル行の値は、Sign列の値を除いて、挿入された状態行の値と同じです。 同時に、キャンセル行と同じ主キーを持つ新しい状態行を挿入します。

   INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);

4. データの照会

   SELECT * FROM test_tbl_collapsing;

   次のクエリ結果が返されます。

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐ 
   │ 4324182021466249494 │    5      │    146   │   1   │ 
   │ 4324182021466249494 │    5      │    146   │  -1   │ 
   │ 4324182021466249494 │    6      │    185   │   1   │ 
   └─────────────────────┴───────────┴──────────┴───────┘

5. 指定した列で集計関数を実行する場合は、SQL文を変更して有効な結果を取得します。 この例では、集計関数sum(col) が使用され、SQL文は次の文に変更されます。

   SELECT
       UserID,
       sum(PageViews * Sign) AS PageViews,
       sum(Duration * Sign) AS Duration
   FROM test_tbl_collapsing
   GROUP BY UserID
   HAVING sum(Sign) > 0;

   データが集計されると、次のクエリ結果が返されます。

   ┌────────UserID───────┬─PageViews─┬─Duration──┐ 
   │ 4324182021466249494 │    6      │    185    │ 
   └─────────────────────┴───────────┴───────────┘

6. OPTIMIZE文を実行して, バックグラウンドで強制的にコンパクションを実行します。 MergeTreeファミリのテーブルエンジンはLSMツリーに似た構造を使用し、ストレージ層の処理ロジックはコンパクションフェーズが開始されるまで実装されないため、コンパクションが強制的に実行されます。

   optimize table test_tbl_collapsing final;

7. データを再度照会します。

   SELECT * FROM test_tbl_collapsing;

   次のクエリ結果が返されます。

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐  
   │ 4324182021466249494 │    6      │    185   │   1   │ 
   └─────────────────────┴───────────┴──────────┴───────┘

次の例は、VersionedCollapsingMergeTreeテーブルエンジンの使用方法を示しています。

1. test_tbl_Versionedという名前のテーブルを作成します。

   CREATE TABLE test_tbl_Versioned
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8,
       Version UInt8
   )
   ENGINE = VersionedCollapsingMergeTree(Sign, Version)
   ORDER BY UserID;

2. Sign列の値が -1であるキャンセル行を挿入します。

   INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, -1, 1);

3. Sign列の値が1で、Version列の値が1である状態行を挿入します。 他の列の値は、挿入されるキャンセル行の値と同じです。 同時に、キャンセル行と同じ主キーを持つ新しい状態行を挿入します。

   INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);

4. データの照会

   SELECT * FROM test_tbl_Versioned;

   次のクエリ結果が返されます。

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐ 
   │ 4324182021466249494 │    5      │    146   │  -1    │    1   │
   │ 4324182021466249494 │    5      │    146   │   1    │    1   │
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

5. 指定した列で集計関数を実行する場合は、SQL文を変更して有効な結果を取得します。 この例では、集計関数sum(col) が使用され、SQL文は次の文に変更されます。

   SELECT
       UserID,
       sum(PageViews * Sign) AS PageViews,
       sum(Duration * Sign) AS Duration
   FROM test_tbl_Versioned
   GROUP BY UserID
   HAVING sum(Sign) > 0;

   データが集計されると、次のクエリ結果が返されます。

   ┌────────UserID───────┬─PageViews─┬─Duration─┐ 
   │ 4324182021466249494 │    6      │    185   │
   └─────────────────────┴───────────┴──────────┘

6. OPTIMIZE文を実行して, バックグラウンドで強制的にコンパクションを実行します。 MergeTreeファミリのテーブルエンジンはLSMツリーに似た構造を使用し、ストレージ層の処理ロジックはコンパクションフェーズが開始されるまで実装されないため、コンパクションが強制的に実行されます。

   optimize table test_tbl_Versioned final;

7. データを再度照会します。

   SELECT * FROM test_tbl_Versioned;

   次のクエリ結果が返されます。

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐ 
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

SummingMergeTreeテーブルエンジンを使用する前に、次の点に注意してください。

• ApsaraDB for ClickHouseでは、プライマリキー列は、バックグラウンドでコンパクションが実行された場合にのみ事前集計されます。 コンパクションを実行する時間は予測できません。 したがって、いくつかのデータは事前に集約され得るが、いくつかのデータは事前に集約され得ない。 GROUP BY句は、集計を実行するためにSQL文で引き続き必要です。

• 事前集計中、ApsaraDB for ClickHouseは主キー列を除くすべての列を事前集計します。 NUMERICデータ型の列など、これらの列を集計できる場合、値は合計されます。 STRINGデータ型の列など、列を集計できない場合は、ランダムな値が使用されます。

• SummingMergeTreeテーブルエンジンをMergeTreeテーブルエンジンと組み合わせて使用することを推奨します。 MergeTreeテーブルエンジンは完全なデータを保存し、SummingMergeTreeテーブルエンジンは事前に集計された結果を保存します。

次の例は、SummingMergeTreeテーブルエンジンの使用方法を示しています。

1. test_tbl_summingという名前のテーブルを作成します。

   CREATE TABLE test_tbl_summing
   (
       key UInt32,
       value UInt32
   )
   ENGINE = SummingMergeTree()
   ORDER BY key;

2. データを書き込みます。

   INSERT INTO test_tbl_summing Values(1,1),(1,2),(2,1);

3. データの照会

   select * from test_tbl_summing;

   次のクエリ結果が返されます。

   ┌─key─┬value─┐
   │  1  │  1   │
   │  1  │  2   │
   │  2  │  1   │
   └─────┴──────┘    

4. OPTIMIZE文を実行して, バックグラウンドで強制的にコンパクションを実行します。 MergeTreeファミリのテーブルエンジンはLSMツリーに似た構造を使用し、ストレージ層の処理ロジックはコンパクションフェーズが開始されるまで実装されないため、コンパクションが強制的に実行されます。

   optimize table test_tbl_summing final;

5. バックグラウンドで強制的にコンパクションを実行した後、GROUP BY句を含むステートメントを実行してデータを集計します。 次に、データを再度照会します。

   SELECT key, sum(value) FROM test_tbl_summing GROUP BY key;

   次のクエリ結果が返されます。 主キーが重複するデータが集約されました。

   ┌─key─┬value─┐
   │  1  │  3   │
   │  2  │  1   │
   └─────┴──────┘

AggregatingMergeTreeテーブルエンジンの構文は複雑です。 AggregatingMergeTreeテーブルエンジンは、マテリアライズドビューまたはApsaraDB for ClickHouseの特別なデータ型AggregateFunctionと組み合わせて使用する必要があります。

次に、AggregatingMergeTreeテーブルエンジンの使用例を示します。

• マテリアライズドビューと組み合わせたAggregatingMergeTreeテーブルエンジンの使用

  1. visitsという名前の詳細テーブルを作成します。

     CREATE TABLE visits
     (
         UserID UInt64,
         CounterID UInt8,
         StartDate Date,
         Sign Int8
     )
     ENGINE = CollapsingMergeTree(Sign)
     ORDER BY UserID;

     による注文

  2. visits_agg_viewという名前のマテリアライズドビューを訪問テーブルに作成し、sumStateおよびuniqState関数を実行して訪問テーブルを事前に集計します。

     CREATE MATERIALIZED VIEW visits_agg_view
     ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
     AS SELECT
         CounterID,
         StartDate,
         sumState(Sign)    AS Visits,
         uniqState(UserID) AS Users
     FROM visits
     GROUP BY CounterID, StartDate;

  3. 訪問数テーブルにデータを書き込みます。

     INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
     INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);

  4. sumMergeおよびuniqMerge集計関数を実行して、マテリアライズドビューを集計します。 次に、集計データを照会します。

     SELECT
         StartDate,
         sumMerge(Visits) AS Visits,
         uniqMerge(Users) AS Users
     FROM visits_agg_view
     GROUP BY StartDate
     ORDER BY StartDate

     説明

     sumおよびuniq関数はもはや使用できません。 上記の関数を使用すると、SQLステートメントを実行するときに次のエラーメッセージが返されます。Illegal type AggregateFunction(sum, Int8) of argument for aggregate function sum...

     次のクエリ結果が返されます。

     ┌──StartDate──┬─Visits─┬─Users──┐
     │  2019-11-11 │   1    │   1    │
     │  2019-11-12 │   1    │   1    │
     └─────────────┴────────┴────────┘

• AggregatingMergeTreeテーブルエンジンを特別なデータ型AggregateFunctionと組み合わせて使用する

  1. detail_tableという名前の詳細テーブルを作成します。

     CREATE TABLE detail_table
     (   CounterID UInt8,
         StartDate Date,
         UserID UInt64
     ) ENGINE = MergeTree() 
     PARTITION BY toYYYYMM(StartDate) 
     ORDER BY (CounterID, StartDate);

  2. detail_tableにデータを書き込みます。

     INSERT INTO detail_table VALUES(0, '2019-11-11', 1);
     INSERT INTO detail_table VALUES(1, '2019-11-12', 1);

  3. UserID列のデータ型がAggregateFunctionであるagg_tableという名前の集計テーブルを作成します。

     CREATE TABLE agg_table
     (   CounterID UInt8,
         StartDate Date,
         UserID AggregateFunction(uniq, UInt64)
     ) ENGINE = AggregatingMergeTree() 
     PARTITION BY toYYYYMM(StartDate) 
     ORDER BY (CounterID, StartDate);

  4. uniqState集計関数を実行して、詳細テーブルのデータを集計テーブルに挿入します。

     INSERT INTO agg_table
     select CounterID, StartDate, uniqState(UserID)
     from detail_table
     group by CounterID, StartDate;

     説明

     INSERT INTO agg_table VALUES(1, '2019-11-12 ', 1); 文を実行して、集計テーブルにデータを挿入することはできません。 UInt64をAggregateFunction(uniq, UInt64) に変換できません。

  5. uniqMerge集計関数を実行して、集計テーブルのデータを集計します。 次に、集計データを照会します。

     SELECT uniqMerge(UserID) AS state 
     FROM agg_table 
     GROUP BY CounterID, StartDate;

     次のクエリ結果が返されます。

     ┌─state─┐
     │   1   │
     │   1   │
     └───────┘