Comparing ClickHouse Cloud and BigQuery

Resource organization

ClickHouse Cloudのリソースの組織方法は、BigQueryのリソース階層に似ています。以下の図は、ClickHouse Cloudリソース階層を示しています。

Organizations

BigQueryと同様に、組織はClickHouse Cloudリソース階層のルートノードです。ClickHouse Cloudアカウントに設定した最初のユーザーは、自動的にそのユーザーが所有する組織に割り当てられます。ユーザーは、他のユーザーを組織に招待できます。

BigQuery Projects vs ClickHouse Cloud Services

組織内では、ClickHouse Cloudにおけるデータはサービスに関連付けられているため、BigQueryのプロジェクトに相当するサービスを作成できます。ClickHouse Cloudでは、いくつかのサービスタイプが利用可能です。各ClickHouse Cloudサービスは特定の地域に展開され、以下を含みます。

コンピュートノードのグループ（現在、開発ティアサービス用に2ノード、プロダクションティアサービス用に3ノード）。これらのノードに対して、ClickHouse Cloudは垂直および水平スケーリングをサポートしており、手動および自動の両方が可能です。
サービスがすべてのデータを保存するオブジェクトストレージフォルダー。
エンドポイント（またはClickHouse Cloud UIコンソールを介して作成された複数のエンドポイント） - サービスに接続するために使用するサービスURL（例：https://dv2fzne24g.us-east-1.aws.clickhouse.cloud:8443）

BigQuery Datasets vs ClickHouse Cloud Databases

ClickHouseはテーブルをデータベースに論理的にグループ化します。BigQueryデータセットと同様に、ClickHouseデータベースはテーブルデータを整理し、アクセスを制御する論理コンテナです。

BigQuery Folders

ClickHouse Cloudには、現在BigQueryフォルダーに相当する概念はありません。

BigQuery Slot reservations and Quotas

BigQueryのスロット予約と同様に、ClickHouse Cloudでは垂直および水平自動スケーリングを構成できます。垂直自動スケーリングでは、サービスのコンピュートノードのメモリとCPUコアの最小および最大サイズを設定できます。このサービスはその範囲内で必要に応じてスケールします。これらの設定は、サービスの初期作成フロー中にも利用可能です。サービス内の各コンピュートノードは同じサイズです。水平スケーリングにより、サービス内のコンピュートノードの数を変更できます。

さらに、BigQueryのクォータに似て、ClickHouse Cloudは同時実行制御、メモリ使用制限、およびI/Oスケジューリングを提供し、ユーザーがクエリをワークロードクラスに分離できるようにします。特定のワークロードクラスの共有リソース（CPUコア、DRAM、ディスクおよびネットワークI/O）に制限を設定することで、これらのクエリが他の重要なビジネスクエリに影響を及ぼさないようにします。同時実行制御は、高数の同時クエリのシナリオでスレッドの過剰サブスクリプションを防ぎます。

ClickHouseは、サーバー、ユーザー、およびクエリレベルでのメモリ割り当てのバイトサイズを追跡し、柔軟なメモリ使用制限を可能にします。メモリオーバーコミットにより、クエリは保証されたメモリを超える余剰メモリを使用できますが、他のクエリに対してメモリ制限を保証します。さらに、集約、ソート、および結合句のメモリ使用を制限できるため、メモリ制限が超えた場合には外部アルゴリズムへのフォールバックが可能です。

最後に、I/Oスケジューリングにより、ユーザーはワークロードクラスに基づいて最大帯域幅、進行中のリクエスト、およびポリシーに基づいてローカルおよびリモートディスクアクセスを制限することができます。

Permissions

ClickHouse Cloudは、クラウドコンソールとデータベースの2か所でユーザーアクセスを制御します。コンソールアクセスは、clickhouse.cloudユーザーインターフェースを介して管理されます。データベースアクセスは、データベースのユーザーアカウントとロールを介して管理されます。さらに、コンソールユーザーは、データベース内でのロールを付与され、当社のSQLコンソールを介してデータベースと対話できるようになります。

Data types

ClickHouseは数値に関してより詳細な精度を提供します。たとえば、BigQueryは数値型INT64, NUMERIC, BIGNUMERIC および FLOAT64を提供しています。これに対してClickHouseは、小数、浮動小数点および整数用の複数の精度タイプを提供します。これらのデータ型を使用することで、ClickHouseのユーザーはストレージとメモリのオーバーヘッドを最適化し、結果としてクエリの高速化とリソース消費の削減を実現できます。以下に、各BigQuery型に対応するClickHouse型のマッピングを提供します。

BigQuery	ClickHouse
ARRAY	Array(t)
NUMERIC	Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S)
BIG NUMERIC	Decimal256(S)
BOOL	Bool
BYTES	FixedString
DATE	Date32 (with narrower range)
DATETIME	DateTime, DateTime64 (narrow range, higher precision)
FLOAT64	Float64
GEOGRAPHY	Geo Data Types
INT64	UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256
INTERVAL	NA - supported as expression or through functions
JSON	JSON
STRING	String (bytes)
STRUCT	Tuple, Nested
TIME	DateTime64
TIMESTAMP	DateTime64

ClickHouseタイプの複数の選択肢がある場合は、データの実際の範囲を考慮して、必要最小限のものを選択してください。また、さらなる圧縮のために適切なコーデックを利用することを検討してください。

Query acceleration techniques

Primary and Foreign keys and Primary index

BigQueryでは、テーブルに主キーと外部キー制約を設定できます。通常、主キーと外部キーは、データの整合性を確保するためにリレーショナルデータベースで使用されます。主キーの値は通常、各行で一意であり、NULLであってはなりません。行の各外部キーの値は、主キーのテーブルの主キー列に存在するか、NULLである必要があります。BigQueryでは、これらの制約は強制されませんが、クエリ最適化器はこれらの情報を使用してクエリを最適化できます。

ClickHouseでも、テーブルに主キーを設定できます。BigQueryと同様に、ClickHouseはテーブルの主キー列の値の一意性を強制しません。ただし、BigQueryとは異なり、テーブルのデータは主キー列によってディスクに順序付けられてているため、クエリ最適化器はこの順序を利用して、再ソートを防ぎ、結合時のメモリ使用量を最小限に抑え、リミット句でのショートサーキット処理を可能にします。ClickHouseは、主キー列の値に基づいて自動的に（スパース）主インデックスを作成します。このインデックスは、主キー列に対するフィルタを含むすべてのクエリをスピードアップするために使用されます。ClickHouseは現在、外部キー制約をサポートしていません。

Secondary indexes (Only available in ClickHouse)

ClickHouseでは、テーブルの主キー列の値から作成された主インデックスに加えて、主キーに含まれないカラムに対してセカンダリインデックスを作成できます。ClickHouseはいくつかの種類のセカンダリインデックスを提供しており、それぞれが異なるタイプのクエリに適しています。

ブルームフィルタインデックス:
- 等号条件（例：=、IN）を持つクエリを高速化するために使用します。
- データブロック内に値が存在するかどうかを判断するために確率的データ構造を使用します。
トークンブルームフィルタインデックス:
- ブルームフィルタインデックスに似ていますが、トークン化された文字列に使用され、全文検索クエリに適しています。
最小最大インデックス:
- 各データパートのカラムの最小値と最大値を保持します。
- 指定された範囲内にないデータパートの読み取りをスキップするのに役立ちます。

Search indexes

BigQueryの検索インデックスに似て、ClickHouseテーブルの文字列値を持つカラムに対して全文インデックスを作成できます。

Vector indexes

BigQueryは最近、Pre-GA機能としてベクターインデックスを導入しました。同様に、ClickHouseもベクター検索ユースケースを高速化するためのインデックスに対する実験的サポートを提供しています。

Partitioning

BigQueryと同様に、ClickHouseはテーブルをパーティショニングして、大きなテーブルのパフォーマンスと管理性を向上させるために、テーブルをより小さく管理しやすい部分（パーティション）に分割します。ClickHouseのパーティショニングについての詳細はこちらで説明します。

Clustering

クラスタリングにより、BigQueryは指定された数個の列の値に基づいてテーブルデータを自動的にソートし、最適サイズのブロックに配置します。クラスタリングはクエリ性能を向上させ、BigQueryがクエリの実行コストをより良く推定できるようにします。クラスタリングされた列を使用することで、クエリは不要なデータのスキャンを排除します。

ClickHouseでは、データは自動的にディスク上でクラスタリングされており、テーブルの主キー列に基づいて論理的に整理され、クエリが主インデックスデータ構造を利用することにより迅速に位置を特定またはプルーニングできるブロックが形成されています。

Materialized views

BigQueryとClickHouseの両方が、パフォーマンスと効率を向上させるためにベーステーブルに対する変換クエリの結果に基づいて事前計算された結果を持つマテリアライズドビューをサポートしています。

Querying materialized views

BigQueryのマテリアライズドビューは直接クエリできるか、オプティマイザによってベーステーブルに対するクエリを処理するために使用されます。ベーステーブルへの変更がマテリアライズドビューを無効にする可能性がある場合、データはベーステーブルから直接読み取られます。ベーステーブルへの変更がマテリアライズドビューを無効にしない場合、残りのデータはマテリアライズドビューから読み取られ、変更された部分のみがベーステーブルから読み取られます。

ClickHouseでは、マテリアライズドビューは直接クエリできるのはのみです。ただし、BigQuery（マテリアライズドビューはベーステーブルの変更からの5分以内に自動的に更新されますが、30分ごと以上の頻度ではありません）と比較して、ClickHouseのマテリアライズドビューは常にベーステーブルと同期しています。

Updating materialized views

BigQueryは、定期的にマテリアライズドビューを完全に更新し、ベーステーブルに対してビューの変換クエリを実行します。更新間の期間中、BigQueryはマテリアライズドビューのデータと新しいベーステーブルデータを組み合わせて、マテリアライズドビューを使用しながら一貫したクエリ結果を提供します。

ClickHouseでは、マテリアライズドビューはインクリメンタルに更新されます。このインクリメンタル更新メカニズムは、高スケーラビリティと低コンピューティングコストを提供します：インクリメンタルに更新されたマテリアライズドビューは、ベーステーブルが数十億または数兆の行を含むシナリオのために特別に設計されています。マテリアライズドビューを更新するために常に成長し続けるベーステーブルを繰り返しクエリするのではなく、ClickHouseは単に新しく挿入されたベーステーブル行の値から部分的な結果を計算します。この部分的な結果は、バックグラウンドで以前に計算された部分的な結果とインクリメンタルにマージされます。これにより、全ベーステーブルからマテリアライズドビューを繰り返し更新するよりも、劇的に低いコンピューティングコストが実現されます。

Transactions

ClickHouseとは異なり、BigQueryは単一のクエリ内でのマルチステートメントトランザクションや、セッションを使用する場合の複数のクエリにわたるマルチステートメントトランザクションをサポートしています。マルチステートメントトランザクションを使用すると、1つ以上のテーブルに対して行を挿入または削除するなどの変更を行うことができ、変更を原子的にコミットまたはロールバックします。マルチステートメントトランザクションは、ClickHouseの2024年のロードマップにあります。

Aggregate functions

BigQueryと比較して、ClickHouseは大幅に多くの組み込み集計関数を提供します。

BigQueryには18の集計関数と、4つの近似集計関数があります。
ClickHouseは150以上の事前構築された集計関数を持ち、事前構築された集計関数の動作を拡張するための強力な集計コンビネータも備えています。例えば、150以上の事前構築された集計関数をテーブル行ではなく配列に適用することができ、-Arrayサフィックスを使用して呼び出すだけで済みます。また、-Mapサフィックスを使用することで、任意の集計関数をマップに適用できます。そして、-ForEachサフィックスを使用すれば、任意の集計関数をネストされた配列に適用できます。

Data sources and file formats

BigQueryと比較して、ClickHouseは大幅に多くのファイル形式およびデータソースをサポートしています。

ClickHouseは、実質的にあらゆるデータソースから90以上のファイル形式でのデータの読み込みをネイティブにサポートしています。
BigQueryは5つのファイル形式と19のデータソースをサポートしています。

SQL language features

ClickHouseは、分析タスクにより適した多くの拡張機能や改良を備えた標準SQLを提供します。たとえば、ClickHouse SQLはラムダ関数や高階関数をサポートしているため、変換を適用する際に配列をアンネストしたりエクスプロードしたりする必要がありません。これは、BigQueryなどの他のシステムに対する大きな利点です。

Arrays

BigQueryの8つの配列関数と比較して、ClickHouseには優雅でシンプルに幅広い問題をモデル化し解決するための80以上の組み込み配列関数があります。

ClickHouseの典型的なデザインパターンは、特定のテーブル行の値を配列に（テンポラリに）変換するためにgroupArray集計関数を使用することです。これによって、配列関数を介して便利に処理が可能となり、結果はarrayJoin集計関数を介して元のテーブル行に戻すことができます。

ClickHouse SQLが高阶ラムダ関数をサポートしているため、多くの高度な配列操作は、一時的に配列をテーブルに戻す必要がなく、単に高階組み込み配列関数の1つを呼び出すことで実現できます。これは、BigQueryで要求されることが多いです。たとえば、フィルタリングやジッパリング配列の場合、ClickHouseではこれらの操作は単に高階関数arrayFilterやarrayZipを呼び出すだけで実行できます。

以下に、BigQueryからClickHouseへの配列操作のマッピングを提供します。

BigQuery	ClickHouse
ARRAY_CONCAT	arrayConcat
ARRAY_LENGTH	length
ARRAY_REVERSE	arrayReverse
ARRAY_TO_STRING	arrayStringConcat
GENERATE_ARRAY	range

Create an array with one element for each row in a subquery

BigQuery

ARRAY function

SELECT ARRAY
  (SELECT 1 UNION  ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

/*-----------*
 | new_array |
 +-----------+
 | [1, 2, 3] |
 *-----------*/

ClickHouse

groupArray aggregate function

SELECT groupArray(*) AS new_array
FROM
(
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
)
   ┌─new_array─┐
1. │ [1,2,3]   │
   └───────────┘

Convert an array into a set of rows

BigQuery

UNNEST operator

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS element
WITH OFFSET AS offset
ORDER BY offset;

/*----------+--------*
 | element  | offset |
 +----------+--------+
 | foo      | 0      |
 | bar      | 1      |
 | baz      | 2      |
 | qux      | 3      |
 | corge    | 4      |
 | garply   | 5      |
 | waldo    | 6      |
 | fred     | 7      |
 *----------+--------*/

ClickHouse

ARRAY JOIN clause

WITH ['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'] AS values
SELECT element, num-1 AS offset
FROM (SELECT values AS element) AS subquery
ARRAY JOIN element, arrayEnumerate(element) AS num;

/*----------+--------*
 | element  | offset |
 +----------+--------+
 | foo      | 0      |
 | bar      | 1      |
 | baz      | 2      |
 | qux      | 3      |
 | corge    | 4      |
 | garply   | 5      |
 | waldo    | 6      |
 | fred     | 7      |
 *----------+--------*/

Return an array of dates

BigQuery

GENERATE_DATE_ARRAY function

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

/*--------------------------------------------------*
 | example                                          |
 +--------------------------------------------------+
 | [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
 *--------------------------------------------------*/

range + arrayMap functions

ClickHouse

SELECT arrayMap(x -> (toDate('2016-10-05') + x), range(toUInt32((toDate('2016-10-08') - toDate('2016-10-05')) + 1))) AS example

   ┌─example───────────────────────────────────────────────┐
1. │ ['2016-10-05','2016-10-06','2016-10-07','2016-10-08'] │
   └───────────────────────────────────────────────────────┘

Return an array of timestamps

BigQuery

GENERATE_TIMESTAMP_ARRAY function

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
                                INTERVAL 1 DAY) AS timestamp_array;

/*--------------------------------------------------------------------------*
 | timestamp_array                                                          |
 +--------------------------------------------------------------------------+
 | [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
 *--------------------------------------------------------------------------*/

ClickHouse

range + arrayMap functions

SELECT arrayMap(x -> (toDateTime('2016-10-05 00:00:00') + toIntervalDay(x)), range(dateDiff('day', toDateTime('2016-10-05 00:00:00'), toDateTime('2016-10-07 00:00:00')) + 1)) AS timestamp_array

Query id: b324c11f-655b-479f-9337-f4d34fd02190

   ┌─timestamp_array─────────────────────────────────────────────────────┐
1. │ ['2016-10-05 00:00:00','2016-10-06 00:00:00','2016-10-07 00:00:00'] │
   └─────────────────────────────────────────────────────────────────────┘

Filtering arrays

BigQuery

Requires temporarily converting arrays back to tables via UNNEST operator

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x
        WHERE x < 5) AS doubled_less_than_five
FROM Sequences;

/*------------------------*
 | doubled_less_than_five |
 +------------------------+
 | [0, 2, 2, 4, 6]        |
 | [4, 8]                 |
 | []                     |
 *------------------------*/

ClickHouse

arrayFilter function

WITH Sequences AS
    (
        SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
        UNION ALL
        SELECT [2, 4, 8, 16, 32] AS some_numbers
        UNION ALL
        SELECT [5, 10] AS some_numbers
    )
SELECT arrayMap(x -> (x * 2), arrayFilter(x -> (x < 5), some_numbers)) AS doubled_less_than_five
FROM Sequences;
   ┌─doubled_less_than_five─┐
1. │ [0,2,2,4,6]            │
   └────────────────────────┘
   ┌─doubled_less_than_five─┐
2. │ []                     │
   └────────────────────────┘
   ┌─doubled_less_than_five─┐
3. │ [4,8]                  │
   └────────────────────────┘

Zipping arrays

BigQuery

Requires temporarily converting arrays back to tables via UNNEST operator

WITH
  Combinations AS (
    SELECT
      ['a', 'b'] AS letters,
      [1, 2, 3] AS numbers
  )
SELECT
  ARRAY(
    SELECT AS STRUCT
      letters[SAFE_OFFSET(index)] AS letter,
      numbers[SAFE_OFFSET(index)] AS number
    FROM Combinations
    CROSS JOIN
      UNNEST(
        GENERATE_ARRAY(
          0,
          LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
    ORDER BY index
  );

/*------------------------------*
 | pairs                        |
 +------------------------------+
 | [{ letter: "a", number: 1 }, |
 |  { letter: "b", number: 2 }] |
 *------------------------------*/

ClickHouse

arrayZip function

WITH Combinations AS
    (
        SELECT
            ['a', 'b'] AS letters,
            [1, 2, 3] AS numbers
    )
SELECT arrayZip(letters, arrayResize(numbers, length(letters))) AS pairs
FROM Combinations;
   ┌─pairs─────────────┐
1. │ [('a',1),('b',2)] │
   └───────────────────┘

Aggregating arrays

BigQuery

Requires converting arrays back to tables via UNNEST operator

WITH Sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
  (SELECT SUM(x)
   FROM UNNEST(s.some_numbers) AS x) AS sums
FROM Sequences AS s;

/*--------------------+------*
 | some_numbers       | sums |
 +--------------------+------+
 | [0, 1, 1, 2, 3, 5] | 12   |
 | [2, 4, 8, 16, 32]  | 62   |
 | [5, 10]            | 15   |
 *--------------------+------*/

ClickHouse

arraySum, arrayAvg, ... function, or any of the over 90 existing aggregate function names as argument for the arrayReduce function

WITH Sequences AS
    (
        SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
        UNION ALL
        SELECT [2, 4, 8, 16, 32] AS some_numbers
        UNION ALL
        SELECT [5, 10] AS some_numbers
    )
SELECT
    some_numbers,
    arraySum(some_numbers) AS sums
FROM Sequences;
   ┌─some_numbers──┬─sums─┐
1. │ [0,1,1,2,3,5] │   12 │
   └───────────────┴──────┘
   ┌─some_numbers──┬─sums─┐
2. │ [2,4,8,16,32] │   62 │
   └───────────────┴──────┘
   ┌─some_numbers─┬─sums─┐
3. │ [5,10]       │   15 │
   └──────────────┴──────┘

Resource organization​

Organizations​

BigQuery Projects vs ClickHouse Cloud Services​

BigQuery Datasets vs ClickHouse Cloud Databases​

BigQuery Folders​

BigQuery Slot reservations and Quotas​

Permissions​

Data types​

Query acceleration techniques​

Primary and Foreign keys and Primary index​

Secondary indexes (Only available in ClickHouse)​

Search indexes​

Vector indexes​

Partitioning​

Clustering​

Materialized views​

Querying materialized views​

Transactions​

Aggregate functions​

Data sources and file formats​

SQL language features​

Arrays​

Resource organization

Organizations

BigQuery Projects vs ClickHouse Cloud Services

BigQuery Datasets vs ClickHouse Cloud Databases

BigQuery Folders

BigQuery Slot reservations and Quotas

Permissions

Data types

Query acceleration techniques

Primary and Foreign keys and Primary index

Secondary indexes (Only available in ClickHouse)

Search indexes

Vector indexes

Partitioning

Clustering

Materialized views

Querying materialized views

Transactions

Aggregate functions

Data sources and file formats

SQL language features

Arrays