最初のMergeTreeテーブルを作成する

リアルタイム分析データウェアハウジングオブザーバビリティAI/MLCloudOSS

前提条件

To successfully follow this guide, you’ll need the following:

A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

作成するもの

このクイックスタートでは、1995年以降の英国の住宅不動産売買記録を保存する MergeTree テーブルを作成します。適切なカラム型でスキーマを設計し、適切な ORDER BY と PARTITION BY を選択して、S3 からデータを直接読み込み、その後 system.parts にクエリを実行して、ClickHouse がディスク上でデータを物理的にどのように配置しているかを確認します。最後には、MergeTree エンジンがほぼすべての ClickHouse テーブルの基盤である理由と、そのソートやパーティション化の選択がクエリ性能にどのように直接影響するかを理解できるようになります。

MergeTree の仕組みを理解する

SQL を書く前に、MergeTree が従来のデータベーステーブルと何が違うのかを知っておくと役立ちます。MergeTree テーブルにデータを挿入しても、ClickHouse は行を 1 行ずつ書き込むわけではありません。代わりに、data part (ソート済みかつ圧縮された、行の小さなまとまり) を直接ディスクに書き込みます。その後、ClickHouse はそれらのパーツを時間をかけてバックグラウンドでマージしていきます。名前の由来はまさにここにあります: merge + tree。各 data part は、テーブルの ORDER BY 式に従ってソートされます。この並び順が 主キーインデックス となり、ClickHouse はクエリ実行時に読み取る必要のない大きなデータブロックをスキップできるようになります (これをデータのプルーニングと呼びます) 。よく使うクエリに対して ORDER BY のカラムの選択性が高いほど、ClickHouse が読み取るデータ量は少なくなります。MergeTree がデータをどのように構成するかは、3 つの句で制御されます。

Clause	役割
`ORDER BY`	各パート内のデータを物理的にソートします。主キーを決定します。必須です。
`PARTITION BY`	データを個別のパーティションに分割します。通常は日付範囲ごとです。異なるパーティションのパート同士がマージされることはないため、高速なパーティションプルーニングが可能になります。
`PRIMARY KEY`	明示的により短いプレフィックスを設定しない限り、デフォルトでは `ORDER BY` になります。スパースインデックスはこれをもとに構築されます。

これで、MergeTree テーブルにおける data part、主キー、クエリ性能の関係を説明できるはずです。

ソースデータをプレビューする

テーブルを作成する前に、s3テーブル関数を使ってソースファイルの内容を確認します。これにより、まず ClickHouse にデータを書き込まなくても、S3 を直接クエリできます。SQLコンソールで次を実行します。

DESCRIBE s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
);

ほぼすべてのカラムが Nullable(String) と推論されている点に注意してください。ClickHouse は生の CSV を読み込んでいるため、実際のデータ型までは判別できません。これは、次のステップでテーブルのスキーマを設計する際に修正します。いくつかの行をプレビューします:

SELECT *
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
)
LIMIT 5;

このデータセットには、HM Land Registry に登録されたイングランドおよびウェールズの住宅不動産の売買データが含まれており、取引 id、売買 price、date、物件 type、住所関連のフィールド、地理識別子が含まれます。また、末尾の 2 つのカラム (column15、column16) は空になっていますが、これらは無視してかまいません。これを確認するには、id、price、date、postcode、type、town、county などのカラムを含む行が表示されることを確認してください。

MergeTree テーブルを設計して作成する

次に、適切なスキーマを持つ永続テーブルを作成します。以下のカラム型は意図して選ばれています。

LowCardinality(String) は、一意の値が限られているカラム (郵便番号、町名、カウンティ名) に使用します。内部的に辞書エンコーディングを使うことで、これらのカラムでのグループ化やフィルタリング時のストレージ使用量を大幅に削減し、パフォーマンスも向上させます。
Enum8 は、クエリでは人間が読める文字列ラベルを保ったまま、type カラムと duration カラムをディスク上で小さな整数としてエンコードします。元の CSV では 1 文字のコードが使われているため、INSERT 時にそれらをマッピングします。
PARTITION BY toYYYYMM(date) は、暦月ごとに 1 つのパーティションを作成します。これにより、WHERE 句で date を条件に指定した場合、ClickHouse は月単位でデータを丸ごとスキップできます。
ORDER BY (postcode, addr1, addr2) は、物件住所による高速なルックアップ (このデータセットで最も自然なアクセスパターン) を支えるためにデータをソートします。

CREATE TABLE uk_price_paid
(
price      UInt32,
date       Date,
postcode   LowCardinality(String),
type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
is_new     UInt8,
duration   Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
addr1      String,
addr2      String,
street     LowCardinality(String),
locality   LowCardinality(String),
town       LowCardinality(String),
district   LowCardinality(String),
county     LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (postcode, addr1, addr2);

次を実行して、テーブルが作成されていることを確認します。

SHOW CREATE TABLE uk_price_paid;

結果セルをダブルクリックして、出力全体を確認します。ENGINE = MergeTree を指定したにもかかわらず、ClickHouse Cloud では SharedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}') でテーブルが作成されていることに注目してください。これは想定どおりの動作です。Cloud は MergeTree を自動的に SharedMergeTree に変換し、レプリケーションと共有ストレージのサポートを追加します。動作とクエリインターフェイスは変わりません。

S3 からデータを読み込む

s3() テーブル関数から直接選択して、データセット全体を挿入します。ClickHouse は圧縮ファイルを S3 からストリーミングし、ソート済みのパーツとしてテーブルに書き込みます。

INSERT INTO uk_price_paid
SELECT
    toUInt32(price),
    date,
    postcode,
    transform(type, ['T', 'S', 'D', 'F', 'O'],
        ['terraced', 'semi-detached', 'detached', 'flat', 'other'], 'other') AS type,
    if(is_new = 'Y', 1, 0) AS is_new,
    transform(duration, ['F', 'L', 'U'],
        ['freehold', 'leasehold', 'unknown'], 'unknown') AS duration,
    addr1,
    addr2,
    street,
    locality,
    town,
    district,
    county
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
);

ソースのCSVでは、すべての値が1文字のコード付き文字列として格納されているため (例: T は terraced、F は freehold、Y/N は new-build) 、transform を使って読みやすいラベルに変換し、toUInt32/if で数値カラムをキャストします。id、column15、column16 の各カラムは不要なため除外します。これには、サービスのサイズに応じて1〜2分ほどかかります。完了したら、行数を確認します:

SELECT formatReadableQuantity(count())
FROM uk_price_paid;

約3,000万行が読み込まれていることを確認できるはずです。

system.parts を使ってパーツを確認する

ここで、MergeTree の内部の仕組みが見えてきます。system.parts テーブルには、サービス内のすべての MergeTree テーブルについて、ディスク上のすべてのデータパーツが記録されています。

SELECT
partition,
name,
rows,
bytes_on_disk,
marks
FROM system.parts
WHERE table = 'uk_price_paid'
AND active = true
ORDER BY partition
LIMIT 20;

各行は、1 つのアクティブなデータパーツを表します。注目すべき点は次のとおりです。

partition - PARTITION BY 式から導出された YYYYMM の値です。月ごとにデータが分けられています。
name - パーツ名には、パーティション、ブロック番号の範囲、マージレベルがエンコードされています (例: 199501_1_4_2 は、パーティション 199501、ブロック 1～4、2 回マージ済みであることを意味します) 。
marks - インデックスグラニュールの数です。各グラニュールはデフォルトで 8,192 行をカバーし、主キーインデックスはグラニュールごとに 1 つのエントリを保持します。このスパースインデックスがメモリ上に保持されることで、高速なデータスキップが可能になります。
bytes_on_disk - ClickHouse はデフォルトで、各パーツをカラムごとに LZ4 で圧縮します。これを生のサイズと比較すると、圧縮率を把握できます。

パーツの総数とテーブル全体の圧縮サイズを確認するには、次を実行します。

SELECT
count()          AS parts,
sum(rows)        AS total_rows,
formatReadableSize(sum(bytes_on_disk)) AS compressed_size
FROM system.parts
WHERE table = 'uk_price_paid'
AND active = true;

このクエリをしばらく後でもう一度実行すると、パーツ数が減っていることに気づくかもしれません。これは MergeTree の マージ が機能しているためです。ClickHouse はバックグラウンドで小さなパーツを継続的に大きなパーツへマージし、パーツ数を減らします。active = true のフィルタにより、クリーンアップ待ちの古いパーツではなく、現在のマージ済みパーツだけが表示されます。

データにクエリを実行し、主キーの挙動を確認する

ここで、実際に分析クエリをいくつか実行してみましょう。まず、記録されている売上の中で最も高額なものを見つけます。

SELECT
addr1,
addr2,
town,
county,
price,
date
FROM uk_price_paid
ORDER BY price DESC
LIMIT 5;

SQL コンソールでクエリ統計を確認します。30,033,199 行すべてが読み込まれていることがわかります。price は ORDER BY キーの一部ではないため、ClickHouse はプライマリインデックスを使ってデータを読み飛ばせず、テーブル全体をスキャンする必要があります。次に、郡ごとの平均販売価格を求めます。

SELECT
county,
round(avg(price)) AS avg_price,
count()           AS sales
FROM uk_price_paid
GROUP BY county
ORDER BY avg_price DESC;

今回も 30,033,199 行すべてが読み込まれます。county は ORDER BY にも PARTITION BY にも含まれていないため、ClickHouse はテーブル全体をスキャンします。次に、集計と ORDER BY を組み合わせたクエリを実行します。データは (postcode, addr1, addr2) でソートされているため、郵便番号のプレフィックスで絞り込むと、ClickHouse はテーブルの大部分をスキップできます。ここでは、SW1A の郵便番号エリアにある物件について、年ごとの平均売却価格を求めます。

SELECT
toYear(date) AS year,
round(avg(price)) AS avg_price,
count() AS sales,
min(price) AS cheapest,
max(price) AS most_expensive
FROM uk_price_paid
WHERE postcode LIKE 'SW1A%'
GROUP BY year
ORDER BY year DESC;

各クエリの実行後に、SQL Consoleでクエリ統計を確認してください。postcode で絞り込んだ集計では、テーブルの行のごく一部だけが読み取られるはずで、これは主キー索引が機能していることを示しています。これを、より広い範囲をスキャンする前述のクエリと比較してください。この違いから、適切な ORDER BY を選ぶことがなぜ重要なのかがわかります。

次のステップ

このクイックスタートでは、ゼロから MergeTree テーブルを作成し、S3 から英国の不動産売買レコード 3,000 万件を取り込み、ClickHouse がデータをソート済みのパーツとパーティションにどのように整理するかを確認し、主キー索引の威力を示すクエリを実行しました。 MergeTree エンジンは基盤です。ここから、その上に構築された各種専用エンジンを調べたり、materialized view がこのパターンをさらにどのように発展させるかを学んだりできます。次に、以下のクイックスタートをご覧ください。

materialized view の概要

または、リファレンスドキュメントでさらに詳しく確認してください。

ClickHouse Academy — Master ClickHouse with expert-designed training for every skill level — Check out the ClickHouse academy for on-demand and live training

すべてのクイックスタート

​前提条件

​作成するもの

​MergeTree の仕組みを理解する

​ソースデータをプレビューする

​MergeTree テーブルを設計して作成する

​S3 からデータを読み込む

​system.parts を使ってパーツを確認する

​データにクエリを実行し、主キーの挙動を確認する

​次のステップ