Comparing ClickHouse Cloud and BigQuery

## Data types[​](#data-types "Direct link to Data types")

ClickHouse offers more granular precision with respect to numerics. For example, BigQuery offers the numeric types [`INT64`, `NUMERIC`, `BIGNUMERIC` and `FLOAT64`](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#numeric_types). Contrast these with ClickHouse, which offers multiple precision types for decimals, floats, and integers. With these data types, you can optimize storage and memory overhead, resulting in faster queries and lower resource consumption. Below we map the equivalent ClickHouse type for each BigQuery type:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| BigQuery ClickHouse|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [ARRAY](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#array_type) [Array(t)](/docs/sql-reference/data-types/array)| [NUMERIC](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#decimal_types) [Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S)](/docs/sql-reference/data-types/decimal)| [BIG NUMERIC](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#decimal_types) [Decimal256(S)](/docs/sql-reference/data-types/decimal)| [BOOL](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#boolean_type) [Bool](/docs/sql-reference/data-types/boolean)| [BYTES](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#bytes_type) [FixedString](/docs/sql-reference/data-types/fixedstring)| [DATE](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#date_type) [Date32](/docs/sql-reference/data-types/date32) (with narrower range)| [DATETIME](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#datetime_type) [DateTime](/docs/sql-reference/data-types/datetime), [DateTime64](/docs/sql-reference/data-types/datetime64) (narrow range, higher precision)| [FLOAT64](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#floating_point_types) [Float64](/docs/sql-reference/data-types/float)| [GEOGRAPHY](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#geography_type) [Geo Data Types](/docs/sql-reference/data-types/float)| [INT64](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#integer_types) [UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256](/docs/sql-reference/data-types/int-uint)| [INTERVAL](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#integer_types) NA - [supported as expression](/docs/sql-reference/data-types/special-data-types/interval#usage-remarks) or [through functions](/docs/sql-reference/functions/date-time-functions#addYears)| [JSON](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#json_type) [JSON](/docs/integrations/data-formats/json/inference)| [STRING](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#string_type) [String (bytes)](/docs/sql-reference/data-types/string)| [STRUCT](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#constructing_a_struct) [Tuple](/docs/sql-reference/data-types/tuple), [Nested](/docs/sql-reference/data-types/nested-data-structures/nested)| [TIME](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#time_type) [DateTime64](/docs/sql-reference/data-types/datetime64)| [TIMESTAMP](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp_type) [DateTime64](/docs/sql-reference/data-types/datetime64) | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

When presented with multiple options for ClickHouse types, consider the actual range of the data and pick the lowest required. Also, consider utilizing [appropriate codecs](https://clickhouse.com/blog/optimize-clickhouse-codecs-compression-schema) for further compression.

## Query acceleration techniques[​](#query-acceleration-techniques "Direct link to Query acceleration techniques")

### Primary and Foreign keys and Primary index[​](#primary-and-foreign-keys-and-primary-index "Direct link to Primary and Foreign keys and Primary index")

In BigQuery, a table can have [primary key and foreign key constraints](https://cloud.google.com/bigquery/docs/information-schema-table-constraints). Typically, primary and foreign keys are used in relational databases to ensure data integrity. A primary key value is normally unique for each row and isn't `NULL`. Each foreign key value in a row must be present in the primary key column of the primary key table or be `NULL`. In BigQuery, these constraints aren't enforced, but the query optimizer may use this information to optimize queries better.

In ClickHouse, a table can also have a primary key. Like BigQuery, ClickHouse doesn't enforce uniqueness for a table's primary key column values. Unlike BigQuery, a table's data is stored on disk [ordered](/docs/guides/best-practices/sparse-primary-indexes#optimal-compression-ratio-of-data-files) by the primary key columns. The query optimizer utilizes this sort order to prevent resorting, to minimize memory usage for joins, and to enable short-circuiting for limit clauses. Unlike BigQuery, ClickHouse automatically creates [a (sparse) primary index](/docs/guides/best-practices/sparse-primary-indexes#an-index-design-for-massive-data-scales) based on the primary key column values. This index is used to speed up all queries that contain filters on the primary key columns. ClickHouse currently doesn't support foreign key constraints.

## Secondary indexes (Only available in ClickHouse)[​](#secondary-indexes-only-available-in-clickhouse "Direct link to Secondary indexes (Only available in ClickHouse)")

In addition to the primary index created from the values of a table's primary key columns, ClickHouse allows you to create secondary indexes on columns other than those in the primary key. ClickHouse offers several types of secondary indexes, each suited to different types of queries:

- [**Bloom Filter Index**](/docs/engines/table-engines/mergetree-family/mergetree#bloom-filter):
  - Used to speed up queries with equality conditions (e.g., =, IN).
  - Uses probabilistic data structures to determine whether a value exists in a data block.
- [**Token Bloom Filter Index**](/docs/engines/table-engines/mergetree-family/mergetree#token-bloom-filter):
  - Similar to a Bloom Filter Index but used for tokenized strings and suitable for full-text search queries.
- [**Min-Max Index**](/docs/engines/table-engines/mergetree-family/mergetree#minmax):
  - Maintains the minimum and maximum values of a column for each data part.
  - Helps to skip reading data parts that don't fall within the specified range.

## 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")