Comparing ClickHouse Cloud and BigQuery

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

*ClickHouse*

[arrayZip](/docs/sql-reference/functions/array-functions#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`](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator) 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](/docs/sql-reference/functions/array-functions#arraySum), [arrayAvg](/docs/sql-reference/functions/array-functions#arrayAvg), ... function, or any of the over 90 existing aggregate function names as argument for the [arrayReduce](/docs/sql-reference/functions/array-functions#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](#resource-organization)
  - [Organizations](#organizations)- [BigQuery Projects vs ClickHouse Cloud Services](#bigquery-projects-vs-clickhouse-cloud-services)- [BigQuery Datasets vs ClickHouse Cloud Databases](#bigquery-datasets-vs-clickhouse-cloud-databases)- [BigQuery Folders](#bigquery-folders)- [BigQuery Slot reservations and Quotas](#bigquery-slot-reservations-and-quotas)- [Permissions](#permissions)- [Data types](#data-types)- [Query acceleration techniques](#query-acceleration-techniques)
      - [Primary and Foreign keys and Primary index](#primary-and-foreign-keys-and-primary-index)- [Secondary indexes (Only available in ClickHouse)](#secondary-indexes-only-available-in-clickhouse)- [Search indexes](#search-indexes)- [Vector indexes](#vector-indexes)- [Partitioning](#partitioning)- [Clustering](#clustering)- [Materialized views](#materialized-views)- [Querying materialized views](#querying-materialized-views)- [Transactions](#transactions)- [Aggregate functions](#aggregate-functions)- [Data sources and file formats](#data-sources-and-file-formats)- [SQL language features](#sql-language-features)- [Arrays](#arrays)

Was this page helpful?