In the ever-evolving landscape of big data analytics, organizations often find themselves at a crossroads when considering database migration. Whether driven by cost optimization, data sovereignty concerns, or the need for enhanced performance, the transition from one data warehouse to another can be a complex undertaking. This article explores key differences between two popular data analytics platforms: ClickHouse and BigQuery, focusing on their SQL implementations and how these variations can impact your migration strategy.

The Challenge of Database Migration

As a team lead for data analysts responsible for developing and maintaining data marts, I recently faced the challenge of migrating our analytics infrastructure from BigQuery to ClickHouse. This transition involved hundreds of views written in BigQuery’s SQL dialect, all of which needed to be adapted to work seamlessly with ClickHouse. Through this process, we encountered several key differences that required careful consideration and creative problem-solving.

Key Differences in SQL Implementation

1. JOIN with Fuzzy Conditions

One of the most significant differences we encountered was in handling JOINs with non-exact conditions.

BigQuery Approach:

BigQuery allows for straightforward implementation of JOINs with multiple non-exact conditions. For example:

SELECT t.user_id AS user_id,
    date_paid, amount, group_id
FROM
(
    SELECT user_id, date_paid, amount
    FROM dataset.transactions
) AS t
LEFT JOIN dataset.user_group AS ug 
    ON ug.user_id = t.user_id
    AND t.date_paid >= ug.link_begin
    AND t.date_paid <= ug.link_end

ClickHouse Solution:

ClickHouse doesn’t support JOINs with multiple non-exact conditions directly. However, we can achieve the same result using array functions:

SELECT t.user_id AS user_id,
    date_paid, amount,
    arrayFilter(x-> x.2<=date_paid AND x.3>=date_paid, ug.params)[1].1 AS group_id
FROM
(
    SELECT user_id, date_paid, amount
    FROM dataset.transactions
) AS t
LEFT JOIN 
(
  SELECT user_id,
    groupArray(tuple(group_id, link_begin, link_end)) AS params
  FROM dataset.user_group 
  GROUP BY 1
) AS ug ON ug.user_id = t.user_id

This approach first groups the data using groupArray(), then filters the results using arrayFilter().

2. Column Calculation Order

Another surprising difference lies in how calculated columns are processed within subqueries.

BigQuery Behavior:

In BigQuery, columns calculated in a subquery are based on the original values from the source. For example:

SELECT a+1 AS a, a+2 AS b
FROM ( SELECT 1 AS a )

This query in BigQuery would return:

a | b
2 | 3

ClickHouse Behavior:

ClickHouse, however, uses the modified value of ‘a’ when calculating ‘b’:

SELECT a+1 AS a, a+2 AS b
FROM ( SELECT 1 AS a )

This query in ClickHouse would return:

a | b
2 | 4

This difference requires careful review of existing queries to ensure calculations remain accurate after migration.

3. Date Storage and Display

Handling of dates and time zones also differs between the two platforms.

BigQuery Approach:

BigQuery stores dates in UTC by default. When specifying a time zone, it adjusts the stored value accordingly:

SELECT TIMESTAMP("2024-05-21 00:00:00", "Europe/Paris") AS paris_time

This would store and display the time as 22:00:00 UTC.

ClickHouse Approach:

ClickHouse stores dates in the time zone specified in the server settings. Specifying a time zone when inserting data doesn’t change the stored value but adds a time zone indicator:

SELECT toDateTime('2024-05-21 00:00:00', 'Europe/Paris') AS paris_time

This would store and display the time as 00:00:00, with a Paris time zone indicator.

To avoid confusion, it’s recommended to work with UTC times consistently and perform time zone conversions at the application level when necessary.

4. NULL Handling in JOINs

The final key difference we encountered relates to how NULL values are handled in JOINs.

BigQuery Behavior:

In BigQuery, fields from unmatched rows in a LEFT JOIN are always filled with NULL, regardless of the data type.

ClickHouse Behavior:

ClickHouse’s behavior depends on the join_use_nulls setting. When set to 1, it behaves like BigQuery. However, when set to 0 (default), it uses type-specific default values (0 for numbers, empty string for strings, etc.) instead of NULL.

To ensure consistent behavior, it’s recommended to set join_use_nulls = 1 for all users, or include it in your queries:

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id
SETTINGS join_use_nulls = 1

Conclusion

Migrating between data warehouse platforms like BigQuery and ClickHouse presents unique challenges, particularly in SQL syntax and behavior. Key areas to focus on include:

1. Adapting complex JOINs using array functions in ClickHouse
2. Carefully reviewing calculated column order in subqueries
3. Standardizing date and time zone handling
4. Ensuring consistent NULL behavior in JOINs

By understanding these differences and adapting your queries accordingly, you can ensure a smoother transition and maintain the integrity of your data analytics processes. Remember, thorough testing and validation are crucial when migrating between different database systems.

As the data analytics landscape continues to evolve, staying adaptable and knowledgeable about various platforms will be key to success in managing and deriving insights from big data.