Migrating from BigQuery to ClickHouse Cloud

11 rows in set. Elapsed: 0.004 sec.
```

### When to use projections[​](#when-to-use-projections "Direct link to When to use projections")

Projections are an appealing feature for new users as they're automatically
maintained as data is inserted. Furthermore, queries can just be sent to a single
table where the projections are exploited where possible to speed up the response
time.

This is in contrast to materialized views, where the user has to select the
appropriate optimized target table or rewrite their query, depending on the filters.
This places greater emphasis on user applications and increases client-side
complexity.

Despite these advantages, projections come with some inherent limitations which
you should be aware of and thus should be deployed sparingly. For further
details see ["materialized views versus projections"](/docs/managing-data/materialized-views-versus-projections)

We recommend using projections when:

- A complete reordering of the data is required. While the expression in the projection can, in theory, use a `GROUP BY,` materialized views are more effective for maintaining aggregates. The query optimizer is also more likely to exploit projections that use a simple reordering, i.e., `SELECT * ORDER BY x`. You can select a subset of columns in this expression to reduce storage footprint.
- Users are comfortable with the associated increase in storage footprint and overhead of writing data twice. Test the impact on insertion speed and [evaluate the storage overhead](/docs/data-compression/compression-in-clickhouse).

## Rewriting BigQuery queries in ClickHouse[​](#rewriting-bigquery-queries-in-clickhouse "Direct link to Rewriting BigQuery queries in ClickHouse")

The following provides example queries comparing BigQuery to ClickHouse. This list aims to demonstrate how to exploit ClickHouse features to significantly simplify queries. The examples here use the full Stack Overflow dataset (up to April 2024).

**Users (with more than 10 questions) which receive the most views:**

*BigQuery*

*ClickHouse*

```
SELECT
    OwnerDisplayName,
    sum(ViewCount) AS total_views
FROM stackoverflow.posts
WHERE (PostTypeId = 'Question') AND (OwnerDisplayName != '')
GROUP BY OwnerDisplayName
HAVING count() > 10
ORDER BY total_views DESC
LIMIT 5

   ┌─OwnerDisplayName─┬─total_views─┐
1. │ Joan Venge       │    25520387 │
2. │ Ray Vega         │    21576470 │
3. │ anon             │    19814224 │
4. │ Tim              │    19028260 │
5. │ John             │    17638812 │
   └──────────────────┴─────────────┘

5 rows in set. Elapsed: 0.076 sec. Processed 24.35 million rows, 140.21 MB (320.82 million rows/s., 1.85 GB/s.)
Peak memory usage: 323.37 MiB.
```

**Which tags receive the most views:**

*BigQuery*

  

*ClickHouse*

```
-- ClickHouse
SELECT
    arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tags,
    sum(ViewCount) AS views
FROM stackoverflow.posts
GROUP BY tags
ORDER BY views DESC
LIMIT 5

   ┌─tags───────┬──────views─┐
1. │ javascript │ 8190916894 │
2. │ python     │ 8175132834 │
3. │ java       │ 7258379211 │
4. │ c#         │ 5476932513 │
5. │ android    │ 4258320338 │
   └────────────┴────────────┘

5 rows in set. Elapsed: 0.318 sec. Processed 59.82 million rows, 1.45 GB (188.01 million rows/s., 4.54 GB/s.)
Peak memory usage: 567.41 MiB.
```

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

Where possible, you should exploit ClickHouse aggregate functions. Below, we show the use of the [`argMax` function](/docs/sql-reference/aggregate-functions/reference/argmax) to compute the most viewed question of each year.

*BigQuery*

*ClickHouse*

```
-- ClickHouse
SELECT
    toYear(CreationDate) AS Year,
    argMax(Title, ViewCount) AS MostViewedQuestionTitle,
    max(ViewCount) AS MaxViewCount
FROM stackoverflow.posts
WHERE PostTypeId = 'Question'
GROUP BY Year
ORDER BY Year ASC
FORMAT Vertical

Row 1:
──────
Year:                    2008
MostViewedQuestionTitle: How to find the index for a given item in a list?
MaxViewCount:            6316987

Row 2:
──────
Year:                    2009
MostViewedQuestionTitle: How do I undo the most recent local commits in Git?
MaxViewCount:            13962748

...

Row 16:
───────
Year:                    2023
MostViewedQuestionTitle: How do I solve "error: externally-managed-environment" every time I use pip 3?
MaxViewCount:            506822

Row 17:
───────
Year:                    2024
MostViewedQuestionTitle: Warning "Third-party cookie will be blocked. Learn more in the Issues tab"
MaxViewCount:            66975

17 rows in set. Elapsed: 0.225 sec. Processed 24.35 million rows, 1.86 GB (107.99 million rows/s., 8.26 GB/s.)
Peak memory usage: 377.26 MiB.
```

## Conditionals and arrays[​](#conditionals-and-arrays "Direct link to Conditionals and arrays")

Conditional and array functions make queries significantly simpler. The following query computes the tags (with more than 10000 occurrences) with the largest percentage increase from 2022 to 2023. Note how the following ClickHouse query is succinct thanks to conditionals, array functions, and the ability to reuse aliases in the `HAVING` and `SELECT` clauses.

*BigQuery*

*ClickHouse*

```
SELECT
    arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tag,
    countIf(toYear(CreationDate) = 2023) AS count_2023,
    countIf(toYear(CreationDate) = 2022) AS count_2022,
    ((count_2023 - count_2022) / count_2022) * 100 AS percent_change
FROM stackoverflow.posts
WHERE toYear(CreationDate) IN (2022, 2023)
GROUP BY tag
HAVING (count_2022 > 10000) AND (count_2023 > 10000)
ORDER BY percent_change DESC
LIMIT 5

┌─tag─────────┬─count_2023─┬─count_2022─┬──────percent_change─┐
│ next.js     │      13788 │      10520 │   31.06463878326996 │
│ spring-boot │      16573 │      17721 │  -6.478189718413183 │
│ .net        │      11458 │      12968 │ -11.644046884639112 │
│ azure       │      11996 │      14049 │ -14.613139725247349 │
│ docker      │      13885 │      16877 │  -17.72826924216389 │
└─────────────┴────────────┴────────────┴─────────────────────┘

5 rows in set. Elapsed: 0.096 sec. Processed 5.08 million rows, 155.73 MB (53.10 million rows/s., 1.63 GB/s.)
Peak memory usage: 410.37 MiB.
```

This concludes our basic guide if you're migrating from BigQuery to ClickHouse. We recommend reading the guide for [modeling data in ClickHouse](/docs/data-modeling/schema-design) to learn more about advanced ClickHouse features.

- [Why use ClickHouse Cloud over BigQuery?](#why-use-clickhouse-cloud-over-bigquery)- [Loading data from BigQuery to ClickHouse Cloud](#loading-data-from-bigquery-to-clickhouse-cloud)
    - [Dataset](#dataset)- [Migrating data](#migrating-data)- [Real-time replication or CDC via scheduled queries](#real-time-replication-or-cdc-via-scheduled-queries)- [Designing schemas](#designing-schemas)
      - [Optimizing types](#optimizing-types)- [How are ClickHouse Primary keys different?](#how-are-clickhouse-primary-keys-different)
        - [Choosing an ordering key](#choosing-an-ordering-key)- [Data modeling techniques](#data-modeling-techniques)
          - [Partitions](#partitions)- [Materialized views vs projections](#materialized-views-vs-projections)
            - [When to use projections](#when-to-use-projections)- [Rewriting BigQuery queries in ClickHouse](#rewriting-bigquery-queries-in-clickhouse)- [Aggregate functions](#aggregate-functions)- [Conditionals and arrays](#conditionals-and-arrays)

Was this page helpful?