Comparing ClickHouse Cloud and BigQuery

## Search indexes[​](#search-indexes "Direct link to Search indexes")

Similar to [search indexes](https://cloud.google.com/bigquery/docs/search-index) in BigQuery, [full-text indexes](/docs/engines/table-engines/mergetree-family/textindexes) can be created for ClickHouse tables on columns with string values.

## Vector indexes[​](#vector-indexes "Direct link to Vector indexes")

BigQuery recently introduced [vector indexes](https://cloud.google.com/bigquery/docs/vector-index) as a Pre-GA feature. Likewise, ClickHouse has experimental support for [indexes to speed up](/docs/engines/table-engines/mergetree-family/annindexes) vector search use cases.

## Partitioning[​](#partitioning "Direct link to Partitioning")

Like BigQuery, ClickHouse uses table partitioning to enhance the performance and manageability of large tables by dividing tables into smaller, more manageable pieces called partitions. We describe ClickHouse partitioning in detail [here](/docs/engines/table-engines/mergetree-family/custom-partitioning-key).

## Clustering[​](#clustering "Direct link to Clustering")

With clustering, BigQuery automatically sorts table data based on the values of a few specified columns and colocates them in optimally sized blocks. Clustering improves query performance, allowing BigQuery to better estimate the cost of running the query. With clustered columns, queries also eliminate scans of unnecessary data.

In ClickHouse, data is automatically [clustered on disk](/docs/guides/best-practices/sparse-primary-indexes#optimal-compression-ratio-of-data-files) based on a table's primary key columns and logically organized in blocks that can be quickly located or pruned by queries utilizing the primary index data structure.

## Materialized views[​](#materialized-views "Direct link to Materialized views")

Both BigQuery and ClickHouse support materialized views – precomputed results based on a transformation query's result against a base table for increased performance and efficiency.

## Querying materialized views[​](#querying-materialized-views "Direct link to Querying materialized views")

BigQuery materialized views can be queried directly or used by the optimizer to process queries to the base tables. If changes to base tables might invalidate the materialized view, data is read directly from the base tables. If the changes to the base tables don't invalidate the materialized view, then the rest of the data is read from the materialized view, and only the changes are read from the base tables.

In ClickHouse, materialized views can be queried directly only. However, compared to BigQuery (in which materialized views are automatically refreshed within 5 minutes of a change to the base tables, but no more frequently than [every 30 minutes](https://cloud.google.com/bigquery/docs/materialized-views-manage#refresh)), materialized views are always in sync with the base table.

**Updating materialized views**

BigQuery periodically fully refreshes materialized views by running the view's transformation query against the base table. Between refreshes, BigQuery combines the materialized view's data with new base table data to provide consistent query results while still using the materialized view.

In ClickHouse, materialized views are incrementally updated. This incremental update mechanism provides high scalability and low computing costs: incrementally updated materialized views are engineered especially for scenarios where base tables contain billions or trillions of rows. Instead of querying the ever-growing base table repeatedly to refresh the materialized view, ClickHouse simply calculates a partial result from (only) the values of the newly inserted base table rows. This partial result is incrementally merged with the previously calculated partial result in the background. This results in dramatically lower computing costs compared to refreshing the materialized view repeatedly from the whole base table.

## Transactions[​](#transactions "Direct link to Transactions")

In contrast to ClickHouse, BigQuery supports multi-statement transactions inside a single query, or across multiple queries when using sessions. A multi-statement transaction lets you perform mutating operations, such as inserting or deleting rows on one or more tables, and either commit or rollback the changes atomically. Multi-statement transactions are on [ClickHouse's roadmap for 2024](https://github.com/ClickHouse/ClickHouse/issues/58392).

## Aggregate functions[​](#aggregate-functions "Direct link to Aggregate functions")

Compared to BigQuery, ClickHouse comes with significantly more built-in aggregate functions:

- BigQuery comes with [18 aggregate functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions), and [4 approximate aggregate functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/approximate_aggregate_functions).
- ClickHouse has over [150 pre-built aggregation functions](/docs/sql-reference/aggregate-functions/reference), plus powerful [aggregation combinators](/docs/sql-reference/aggregate-functions/combinators) for [extending](https://www.youtube.com/watch?v=7ApwD0cfAFI) the behavior of pre-built aggregation functions. As an example, you can apply the over 150 pre-built aggregate functions to arrays instead of table rows simply by calling them with a [-Array suffix](/docs/sql-reference/aggregate-functions/combinators#-array). With a [-Map suffix](/docs/sql-reference/aggregate-functions/combinators#-map) you can apply any aggregate function to maps. And with a [-ForEach suffix](/docs/sql-reference/aggregate-functions/combinators#-foreach), you can apply any aggregate function to nested arrays.

## Data sources and file formats[​](#data-sources-and-file-formats "Direct link to Data sources and file formats")

Compared to BigQuery, ClickHouse supports significantly more file formats and data sources:

- ClickHouse has native support for loading data in 90+ file formats from virtually any data source
- BigQuery supports 5 file formats and 19 data sources

## SQL language features[​](#sql-language-features "Direct link to SQL language features")

ClickHouse provides standard SQL with many extensions and improvements that make it more friendly for analytical tasks. E.g. ClickHouse SQL [supports lambda functions](/docs/sql-reference/functions/overview#arrow-operator-and-lambda) and higher order functions, so you don't have to unnest/explode arrays when applying transformations. This is a big advantage over other systems like BigQuery.

## Arrays[​](#arrays "Direct link to Arrays")

Compared to BigQuery's 8 array functions, ClickHouse has over 80 [built-in array functions](/docs/sql-reference/functions/array-functions) for modeling and solving a wide range of problems elegantly and simply.

A typical design pattern in ClickHouse is to use the [`groupArray`](/docs/sql-reference/aggregate-functions/reference/grouparray) aggregate function to (temporarily) transform specific row values of a table into an array. This then can be conveniently processed via array functions, and the result can be converted back into individual table rows via [`arrayJoin`](/docs/sql-reference/functions/array-join) aggregate function.

Because ClickHouse SQL supports [higher order lambda functions](/docs/sql-reference/functions/overview#arrow-operator-and-lambda), many advanced array operations can be achieved by simply calling one of the higher order built-in array functions, instead of temporarily converting arrays back to tables, as it is often [required](https://cloud.google.com/bigquery/docs/arrays) in BigQuery, e.g. for [filtering](https://cloud.google.com/bigquery/docs/arrays#filtering_arrays) or [zipping](https://cloud.google.com/bigquery/docs/arrays#zipping_arrays) arrays. In ClickHouse these operations are just a simple function call of the higher order functions [`arrayFilter`](/docs/sql-reference/functions/array-functions#arrayFilter), and [`arrayZip`](/docs/sql-reference/functions/array-functions#arrayZip), respectively.

In the following, we provide a mapping of array operations from BigQuery to ClickHouse: