Stack Overflow dataset
s3://datasets-documentation/stackoverflow/parquet/ から利用できます。
示されている主キーとリレーションシップは制約によって強制されるものではありません (Parquet はテーブルフォーマットではなくファイルフォーマットであるため) 。あくまで、データ同士がどのように関連しているか、およびそれぞれがどの一意キーを持つかを示しているだけです。
Stack Overflow データセットには、相互に関連する複数のテーブルが含まれています。データモデリング作業では、まず主要なテーブルの取り込みから始めることを推奨します。これは必ずしも最大のテーブルとは限らず、むしろ最も多くの分析クエリが実行されると想定されるテーブルです。こうすることで、ClickHouse の主要な概念や型に慣れることができます。これは特に、主に OLTP を扱ってきた方にとって重要です。追加のテーブルを加えて ClickHouse の機能を十分に活用し、最適なパフォーマンスを得るために、このテーブルは再モデリングが必要になる場合があります。 このガイドの目的上、上記のスキーマは意図的に最適化していません。
初期スキーマを確立する
posts テーブルはほとんどの分析クエリの対象となるため、このテーブルのスキーマをまず定義することに焦点を当てます。このデータは公開 S3 バケット s3://datasets-documentation/stackoverflow/parquet/posts/*.parquet で入手でき、年ごとに 1 つのファイルが格納されています。
S3 から Parquet フォーマットでデータを読み込む方法は、ClickHouse へのデータ取り込みでもっとも一般的で、推奨される方法です。ClickHouse は Parquet の処理向けに最適化されており、S3 から毎秒数千万行を読み取って挿入できる場合があります。ClickHouse には、データセットの型を自動的に識別するスキーマ推論機能があります。これは Parquet を含むすべてのデータフォーマットでサポートされています。この機能を利用すると、s3 テーブル関数と
DESCRIBEコマンドを使って、データの ClickHouse 型を特定できます。以下の例では、stackoverflow/parquet/posts フォルダー内のすべてのファイルを読み込むために、glob パターン *.parquet を使用しています。
s3 table function を使うと、S3 内のデータを ClickHouse からその場でクエリできます。この関数は、ClickHouse がサポートするすべてのファイルフォーマットに対応しています。これにより、最適化前の初期スキーマを取得できます。デフォルトでは、ClickHouse はこれらを対応する Nullable 型にマッピングします。これらの型を使って、
CREATE EMPTY AS SELECT コマンドを実行するだけで ClickHouse テーブルを作成できます。
ORDER BY () という句は、索引がなく、より正確にはデータに順序がないことを意味します。これについては後ほど詳しく説明します。今のところは、すべてのクエリで線形スキャンが必要になると理解しておいてください。
テーブルが作成されたことを確認するには:
INSERT INTO SELECT でデータを投入できます。以下の例では、8 コアの ClickHouse Cloud インスタンスで posts データを約 2 分で読み込みます。
上記のクエリでは 6000 万行を読み込みます。ClickHouse にとっては小規模ですが、インターネット接続が遅い環境では、データの一部だけを読み込みたい場合もあります。これは、読み込みたい年を glob パターンで指定するだけで実現できます。たとえば、https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2008.parquetやhttps://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/{2008, 2009}.parquetのように指定します。glob パターンを使ってファイルの一部を対象にする方法については、こちらを参照してください。
型の最適化
ClickHouseがこれほど高い圧縮率を実現できる理由については、こちらの記事をおすすめします。要するに、ClickHouseはカラム指向データベースであるため、値はカラム順に書き込まれます。これらの値がソートされていれば、同じ値が隣り合って並びます。圧縮アルゴリズムは、このような連続したデータパターンを活用します。さらに、ClickHouseにはコーデックや粒度を選べるデータ型があり、圧縮方法をさらに調整できます。ClickHouseの圧縮は、主に3つの要因、すなわち 順序キー、データ型、使用するコーデックの影響を受けます。これらはすべてスキーマで設定されます。 圧縮とクエリパフォーマンスを最初に大きく改善するには、単純な型最適化を行うのが効果的です。スキーマを最適化するために、以下のようないくつかの簡単なルールを適用できます。
- 厳密な型を使う - 最初のスキーマでは、明らかに数値である多くのカラムにStringを使用していました。適切な型を使うことで、フィルタリングや集計で期待どおりの意味で処理されます。同じことは日付型にも当てはまり、こちらはParquetファイル内で正しく提供されています。
- Nullable カラムを避ける - デフォルトでは、上記のカラムはNullを取りうるものとして扱われます。Nullable型を使うと、クエリで空の値とNull 値の違いを判別できます。そのために、UInt8型の別カラムが作成されます。この追加カラムは、ユーザーが nullable カラムを扱うたびに毎回処理しなければなりません。その結果、ストレージ使用量が増え、ほぼ常にクエリパフォーマンスに悪影響を及ぼします。型のデフォルトの空値とNullの間に意味上の違いがある場合にのみ、Nullableを使用してください。たとえば、
ViewCountカラムでは、空値に0を使えば、ほとんどのクエリでは十分であり、結果にも影響しないでしょう。空値を別扱いにすべき場合でも、多くはフィルタでクエリから除外できます。 - 数値型では最小限の精度を使う - ClickHouseには、さまざまな数値範囲や精度に対応する多数の数値型があります。常に、カラムを表現するのに必要なビット数を最小限に抑えることを目指してください。Int16のようにサイズの異なる整数型に加えて、ClickHouseは最小値が0である符号なし型も提供しています。これにより、カラムに必要なビット数を減らせる場合があります。たとえばUInt16の最大値は65535で、Int16の2倍です。可能であれば、より大きい符号付き型よりもこれらの型を優先してください。
- 日付型では最小限の精度を使う - ClickHouseは複数の日付型および日時型をサポートしています。純粋な日付の格納にはDateとDate32を使用でき、後者はより多くのビットを使う代わりに、より広い日付範囲をサポートします。DateTimeとDateTime64は日時を扱えます。DateTimeは秒粒度に限定され、32ビットを使用します。DateTime64はその名のとおり64ビットを使用しますが、ナノ秒粒度まで対応します。ここでも同様に、クエリ要件を満たす範囲でより粗い型を選び、必要なビット数を最小化してください。
- LowCardinalityを使う - 一意な値の数が少ないNumber、String、Date、DateTimeのカラムは、LowCardinality型でエンコードできる可能性があります。これはDictionaryエンコードによって値を符号化し、ディスク上のサイズを削減します。一意な値が1万未満のカラムでは、使用を検討してください。
- 特殊なケースではFixedStringを使う - 固定長のStringは、FixedString型でエンコードできます。たとえば言語コードや通貨コードです。これは、データ長がちょうどNバイトである場合に効率的です。それ以外のケースでは、効率が下がる可能性が高く、LowCardinalityのほうが適しています。
- データ検証のためのEnum - Enum型は、列挙型を効率よくエンコードするために使用できます。Enumは、格納する一意な値の数に応じて、8ビットまたは16ビットになります。挿入時の検証 (宣言されていない値は拒否される) が必要な場合や、Enum値の自然な順序を活用するクエリを実行したい場合には、これを検討してください。たとえば、ユーザーの応答を格納するフィードバックカラムに
Enum(':(' = 1, ':|' = 2, ':)' = 3)が含まれるケースを考えてみてください。
ヒント: すべてのカラムの範囲と個別値の数を調べるには、単純なクエリ SELECT * APPLY min, * APPLY max, * APPLY uniq FROM table FORMAT Vertical を使用できます。これは高コストになる可能性があるため、より小さなデータのサブセットに対して実行することをおすすめします。正確な結果を得るには、このクエリの対象となる数値が少なくとも数値型として定義されている必要があります。つまり、Stringであってはなりません。
これらの単純なルールをpostsテーブルに適用することで、各カラムに最適な型を特定できます。
上記により、次のスキーマが得られます。
INSERT INTO SELECT で投入できます。
順序キーの選び方
テーブル内のすべてのカラムは、そのカラム自体がキーに含まれているかどうかにかかわらず、指定した順序キーの値に基づいてソートされます。たとえば、順序キーを選ぶ際に役立つ、いくつかのシンプルなルールがあります。以下の条件は互いに競合する場合もあるため、この順序で検討してください。この過程で複数のキー候補を洗い出せますが、通常は 4〜5 個で十分です。CreationDateをキーとして使用すると、他のすべてのカラムの値の並び順もCreationDateカラムの値の順序に対応します。複数の順序キーを指定することもでき、その場合の並び順はSELECTクエリのORDER BYclause と同じ意味になります。
- 一般的によく使う filter に合ったカラムを選びます。あるカラムが
WHEREclauses で頻繁に使われるなら、使用頻度の低いカラムよりも優先してキーに含めてください。 filter 時に全行の大部分を除外できるカラムを優先すると、読み取る必要があるデータ量を減らせます。 - テーブル内の他のカラムと高い相関がある可能性の高いカラムを優先してください。これにより、それらの値も連続して格納されやすくなり、圧縮効率が向上します。
順序キーに含まれるカラムに対する
GROUP BYおよびORDER BY操作も、よりメモリ効率よく実行できます。
例
posts テーブルに当てはめると、ユーザーはたとえば日付や投稿タイプで絞り込む分析を行いたいとします。たとえば次のようなものです。
“直近 3 か月で最もコメント数が多かった質問はどれか”。
最適化された型を使用しているものの、順序キー は持たない先ほどの posts_v2 テーブルを使うと、この問いに対するクエリは次のようになります。
ここでのクエリは、6,000万行すべてを線形スキャンしているにもかかわらず非常に高速です。ClickHouse はとにかく速いのです :) TB や PB スケールでは、順序キーに十分な価値があることは、ひとまず信じてください！順序キーとして
PostTypeId と CreationDate のカラムを選びましょう。
このケースでは、ユーザーは常に PostTypeId で絞り込むと考えられます。これはカーディナリティが 8 で、順序キーの最初のエントリとして理にかなった選択です。また、日付粒度でのフィルタリングで十分な可能性が高く (それでも datetime フィルタの恩恵は受けられます) 、キーの 2 番目の部分には toDate(CreationDate) を使います。これにより、日付は 16 ビットで表現できるため、より小さな索引が生成され、フィルタリングも高速になります。最後のキーエントリは CommentCount で、コメント数の多い投稿を見つけやすくするためです (最後のソート) 。
次へ: データモデリング手法
Posts を維持することを目指します。他のテーブルを個別にクエリすることもできますが、多くの分析は posts を中心に行うことを想定しています。
この節では、他のテーブルについて最適化済みのバリアントを使用します。これらのスキーマは示しますが、簡潔にするため、そうした判断の詳細は省略します。これらは前述のルールに基づいており、どのような判断を行ったのかは読者自身で推論してください。以下のアプローチはいずれも、読み取りを最適化してクエリパフォーマンスを向上させるために、JOIN の必要性を最小限に抑えることを目的としています。ClickHouse は JOIN を完全にサポートしていますが、最適なパフォーマンスを得るには必要最小限の使用にとどめることを推奨します (JOIN クエリで 2〜3 テーブルなら問題ありません) 。
ClickHouse には外部キーの概念がありません。これは JOIN を禁止するものではありませんが、参照整合性はアプリケーションレベルでユーザーが管理する必要があります。ClickHouse のような OLAP システムでは、データ整合性は、データベース自体で大きなオーバーヘッドをかけて強制するのではなく、アプリケーションレベルやデータのインジェスト時に管理されることが一般的です。このアプローチにより、より高い柔軟性と高速なデータ挿入が可能になります。これは、非常に大規模なデータセットに対する読み取りクエリと insert クエリの速度、および scalability を重視する ClickHouse の方針にも合致しています。クエリ時の JOIN の使用を最小限に抑えるために、ユーザーはいくつかの手段/アプローチを利用できます。
- データの非正規化 - テーブルを結合し、1:1 ではない関係には複合型を使うことでデータを非正規化します。多くの場合、これには JOIN をクエリ時から挿入時へ移すことが含まれます。
- Dictionaries - 直接 JOIN や key value のルックアップを扱うための ClickHouse 固有の機能です。
- インクリメンタルmaterialized view - 集計値を段階的に計算する機能を含め、計算コストをクエリ時から挿入時へ移すための ClickHouse の機能です。
- リフレッシュ可能なマテリアライズドビュー - 他の database 製品で使われる materialized view と同様に、クエリ結果を定期的に計算し、その結果を cache できます。