
NoSQL ソリューションによって広まった一般的な手法の 1 つに、
JOIN をサポートしない環境でデータを非正規化し、すべての統計情報や関連する行を、カラムやネストされたオブジェクトとして親の行に格納する方法があります。たとえば、ブログのスキーマ例では、各投稿に属するすべての Comments を、オブジェクトの Array としてそれぞれの投稿に格納できます。
一般に、次のような場合に非正規化を推奨します。
- 更新頻度が低いテーブル、または分析クエリで利用可能になるまでの遅延を許容できるテーブルを非正規化します。つまり、データをバッチで完全に再読み込みできる場合です。
- 多対多の関係を非正規化するのは避けてください。1 つの元データの行が変更されただけでも、多数の行を更新する必要が生じる可能性があります。
- カーディナリティの高い関係を非正規化するのは避けてください。あるテーブルの各行が、別のテーブルに数千件の関連エントリを持つ場合、それらは
Array(プリミティブ型またはタプル) として表現する必要があります。一般に、1000 個を超えるタプルを含む配列は推奨されません。 - すべてのカラムをネストされたオブジェクトとして非正規化するのではなく、materialized view を使って統計情報のみを非正規化することも検討してください (以下を参照) 。
- テーブルの行が変更されたときに、適切な JOIN 文をトリガーすること。理想的には、これによって JOIN 対象のすべてが更新されるのではなく、影響を受けたものだけが更新されるべきです。適切な行だけを効率的に絞り込めるように JOIN を調整し、さらに高スループット下でこれを実現するには、外部ツールまたは追加のエンジニアリングが必要です。
- ClickHouse における行の更新は慎重に管理する必要があり、複雑さがさらに増します。
そのため、一般的には、非正規化されたデータを定期的にまとめて再読み込みするバッチ更新プロセスが採用されます。 非正規化が適している実践的な例と、代わりに別のアプローチのほうが望ましい例をいくつか見ていきます。
Posts テーブルが、AnswerCount や CommentCount などの統計情報を含む形ですでに非正規化されているとします。元データはこの形式で提供されています。実際には、こうした情報は頻繁に更新される可能性が高いため、むしろ正規化したい場合もあります。これらのカラムの多くは他のテーブルからも取得できます。たとえば、投稿に対するコメントは PostId カラムと Comments テーブルを通じて参照できます。ここでは例として、投稿はバッチ処理で再読み込みされるものとします。
また、ここでは他のテーブルの内容を Posts に非正規化する場合だけを考えます。これは、分析における主テーブルを Posts とみなしているためです。逆方向に非正規化することも、一部のクエリでは適切です。その場合にも、上記と同じ考慮事項が当てはまります。
以下の各例では、両方のテーブルを JOIN で使用する必要があるクエリが存在すると仮定してください。
投稿への投票は、別個のテーブルとして表されます。これに最適化されたスキーマと、データを読み込むための insert コマンドを以下に示します。
Score カラムはそのような統計の 1 つで、つまり賛成票の合計から反対票の合計を引いた値を表しています。理想的には、クエリ時に単純なルックアップでこうした統計を取得できるのが望ましいでしょう (辞書を参照) 。
次に、Users と Badges を見ていきましょう。

バッジに関する統計情報をUsersに非正規化したい場合もあります。たとえば、バッジの数などです。このデータセットに対して書き込み時に辞書を使用する際に、そのような例を取り上げます。
PostLinks は、ユーザーが関連または重複していると見なす Posts 同士を結び付けます。次のクエリは、スキーマとロードコマンドを示しています。
INSERT INTO SELECT を使用します。
- Named Tuples - 関連する構造をカラムの集合として表現できます。
- Array(Tuple) or Nested - 名前付きタプルの配列で、Nested とも呼ばれます。各エントリが 1 つのオブジェクトを表します。一対多の関係に適しています。
PostLinks を Posts に非正規化する方法を示します。
前述の PostLinks スキーマに示したように、各 post には他の post への複数のリンクを含めることができます。Nested 型では、これらのリンク先や重複先の post を次のように表現できます。
設定 flatten_nested=0 が使われている点に注意してください。ネストされたデータのフラット化は無効にすることを推奨します。
この非正規化は、OUTER JOIN クエリを使った INSERT INTO SELECT で実行できます:
ここで処理時間に注目してください。約2分で6,600万行を非正規化できています。後ほど示すように、この処理はスケジュールできます。JOIN の前に
groupArray 関数を使って、PostLinks を各 PostId ごとの配列にまとめている点にも注目してください。次に、この配列を 2 つのサブリスト LinkedPosts と DuplicatePosts に絞り込み、外部 JOIN による空の結果も除外しています。
いくつかの行を選択して、新しい非正規化後の構造を確認できます。
INSERT INTO SELECT でデータをロードした後、この変換を実行できます。これは定期的なバッチ変換に適しています。
定期的なバッチロードプロセスを許容できる場合、ClickHouse でこれをオーケストレーションする方法はいくつかあります。
- リフレッシュ可能なマテリアライズドビュー - リフレッシュ可能なマテリアライズドビューを使用すると、結果をターゲットテーブルに書き出すクエリを定期的にスケジュールできます。クエリの実行時には、このビューによってターゲットテーブルがアトミックに更新されます。これにより、この処理をスケジュールするための ClickHouse ネイティブな手段が得られます。
- 外部ツール - dbt や Airflow などのツールを使って、変換を定期的にスケジュールすることもできます。dbt 向け ClickHouse 連携 を使うと、ターゲットテーブルの新しいバージョンを作成し、その後、クエリを受け付けているバージョンとアトミックに入れ替える (EXCHANGE コマンドを使用) ことで、これをアトミックに実行できます。