Сравнение BigQuery и ClickHouse Cloud

Чем BigQuery отличается от ClickHouse Cloud

​

Организация ресурсов

Организация ресурсов в ClickHouse Cloud похожа на иерархию ресурсов BigQuery. Ниже описаны основные различия на основе следующей диаграммы, на которой показана иерархия ресурсов ClickHouse Cloud:

​

Организации

Как и в BigQuery, организации — это корневые узлы в иерархии ресурсов ClickHouse Cloud. Первый пользователь, которого вы создаёте в своей учётной записи ClickHouse Cloud, автоматически назначается в организацию, принадлежащую ему. Этот пользователь может приглашать в организацию других пользователей.

​

Проекты BigQuery и сервисы ClickHouse Cloud

В организациях можно создавать сервисы, в определённой степени эквивалентные проектам BigQuery, поскольку данные, хранящиеся в ClickHouse Cloud, привязаны к сервису. В ClickHouse Cloud доступно несколько типов сервисов. Каждый сервис ClickHouse Cloud развёртывается в определённом регионе и включает:

1. Группу вычислительных узлов (сейчас это 2 узла для сервиса уровня Development и 3 — для сервиса уровня Production). Для этих узлов ClickHouse Cloud поддерживает вертикальное и горизонтальное масштабирование как вручную, так и автоматически.
2. Папку в Объектном хранилище, где сервис хранит все данные.
3. Конечную точку (или несколько конечных точек, созданных через консоль ClickHouse Cloud) — URL сервиса, который используется для подключения к нему (например, https://dv2fzne24g.us-east-1.aws.clickhouse.cloud:8443)

​

Датасеты BigQuery и базы данных ClickHouse Cloud

ClickHouse логически объединяет таблицы в базы данных. Как и датасеты BigQuery, базы данных ClickHouse — это логические контейнеры для организации табличных данных и управления доступом к ним.

​

Папки BigQuery

В ClickHouse Cloud сейчас нет понятия, эквивалентного папкам BigQuery.

​

Резервирование Slot и квоты BigQuery

Как и в BigQuery с резервированием Slot, в ClickHouse Cloud можно настроить вертикальное и горизонтальное автомасштабирование. Для вертикального автомасштабирования можно задать минимальный и максимальный размер оперативной памяти и число ядер CPU для вычислительных узлов сервиса. Затем сервис будет масштабироваться по мере необходимости в этих пределах. Эти настройки также доступны на этапе первоначального создания сервиса. Каждый вычислительный узел в сервисе имеет одинаковый размер. Изменить количество вычислительных узлов в сервисе можно с помощью горизонтального масштабирования.

Кроме того, как и квоты BigQuery, ClickHouse Cloud предлагает управление параллелизмом, ограничения на использование памяти и планирование I/O, что позволяет изолировать запросы по классам рабочей нагрузки. Устанавливая ограничения на общие ресурсы (ядра CPU, DRAM, дисковый и сетевой I/O) для определенных классов рабочей нагрузки, можно гарантировать, что эти запросы не будут влиять на другие критически важные бизнес-запросы. Управление параллелизмом предотвращает чрезмерную подписку потоков в сценариях с большим числом параллельных запросов.

ClickHouse отслеживает объемы выделения памяти в байтах на уровне сервера, пользователя и запроса, что позволяет гибко задавать ограничения на использование памяти. Оверкоммит памяти позволяет запросам использовать дополнительную свободную память сверх гарантированного объема, при этом сохраняя гарантии по лимитам памяти для других запросов. Кроме того, можно ограничить использование памяти для секций aggregation, sort и JOIN, что позволяет переходить на внешние алгоритмы при превышении лимита памяти.

Наконец, планирование I/O позволяет ограничивать доступ к локальному и удаленному disk для классов рабочей нагрузки на основе максимальной пропускной способности, числа запросов в полете и политики.

​

Разрешения

В ClickHouse Cloud доступ пользователей контролируется в двух местах: через консоль Cloud и через базу данных. Доступ к консоли управляется через интерфейс clickhouse.cloud. Доступ к базе данных управляется с помощью учетных записей пользователей базы данных и ролей. Кроме того, пользователям консоли можно назначать роли в базе данных, которые позволяют им взаимодействовать с базой данных через нашу SQL-консоль.

​

Типы данных

ClickHouse предлагает более гибкий выбор точности для числовых типов. Например, BigQuery поддерживает числовые типы INT64, NUMERIC, BIGNUMERIC and FLOAT64. В ClickHouse, в отличие от BigQuery, есть несколько вариантов точности для десятичных чисел, чисел с плавающей точкой и целых чисел. Эти типы данных позволяют оптимизировать хранение и расход памяти, что ускоряет выполнение запросов и снижает потребление ресурсов. Ниже приведено соответствие типов BigQuery и их эквивалентов в ClickHouse:

BigQuery	ClickHouse
ARRAY	Array(t)
NUMERIC	Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S)
BIG NUMERIC	Decimal256(S)
BOOL	Bool
BYTES	FixedString
DATE	Date32 (с более узким диапазоном)
DATETIME	DateTime, DateTime64 (узкий диапазон, более высокая точность)
FLOAT64	Float64
GEOGRAPHY	гео-типы данных
INT64	UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256
INTERVAL	Н/Д — поддерживается как expression или через функции
JSON	JSON
STRING	String (байты)
STRUCT	Tuple, Nested
TIME	DateTime64
TIMESTAMP	DateTime64

Если для типа ClickHouse доступно несколько вариантов, учитывайте фактический диапазон данных и выбирайте минимально необходимый. Также стоит использовать подходящие кодеки для дополнительного сжатия.

​

Методы ускорения запросов

​

Первичные и внешние ключи и первичный индекс

В BigQuery таблица может иметь ограничения первичного и внешних ключей. Обычно первичные и внешние ключи используются в реляционных базах данных для обеспечения целостности данных. Значение первичного ключа обычно уникально для каждой строки и не равно NULL. Каждое значение внешнего ключа в строке должно присутствовать в столбце первичного ключа таблицы, на которую ссылается внешний ключ, либо быть NULL. В BigQuery эти ограничения не проверяются, однако оптимизатор запросов может использовать эту информацию для более эффективной оптимизации запросов.

В ClickHouse таблица также может иметь первичный ключ. Как и BigQuery, ClickHouse не обеспечивает уникальность значений в столбце первичного ключа таблицы. В отличие от BigQuery, данные таблицы хранятся на диске упорядоченными по столбцам первичного ключа. Оптимизатор запросов использует этот порядок сортировки, чтобы избежать повторной сортировки, минимизировать использование памяти для JOIN и обеспечивать досрочное завершение для секций LIMIT. В отличие от BigQuery, ClickHouse автоматически создает первичный (разреженный) индекс на основе значений столбцов первичного ключа. Этот индекс используется для ускорения всех запросов, содержащих фильтры по столбцам первичного ключа. В настоящее время ClickHouse не поддерживает ограничения внешних ключей.

​

Вторичные индексы (доступны только в ClickHouse)

Помимо первичного индекса, создаваемого на основе значений столбцов первичного ключа таблицы, ClickHouse позволяет создавать вторичные индексы на столбцах, не входящих в первичный ключ. ClickHouse поддерживает несколько типов вторичных индексов, каждый из которых подходит для разных видов запросов:

• Индекс Bloom Filter:
  • Используется для ускорения запросов с условиями равенства (например, =, IN).
  • Использует вероятностные структуры данных, чтобы определять, содержится ли значение в блоке данных.
• Индекс Token Bloom Filter:
  • Аналогичен индексу Bloom Filter, но используется для токенизированных строк и подходит для запросов полнотекстового поиска.
• Индекс Min-Max:
  • Хранит минимальное и максимальное значения столбца для каждой части данных.
  • Помогает пропускать чтение частей данных, которые не попадают в указанный диапазон.

​

Поисковые индексы

Как и поисковые индексы в BigQuery, полнотекстовые индексы можно создавать для таблиц ClickHouse по столбцам со строковыми значениями.

​

Векторные индексы

BigQuery недавно представил векторные индексы как возможность на этапе Pre-GA. Аналогично, в ClickHouse есть экспериментальная поддержка индексов для ускорения сценариев векторного поиска.

​

Партиционирование

Как и BigQuery, ClickHouse использует партиционирование таблиц, чтобы повысить производительность и упростить работу с большими таблицами, разбивая их на более мелкие и удобные части — партиции. Подробнее о партиционировании в ClickHouse читайте здесь.

​

Кластеризация

При кластеризации BigQuery автоматически сортирует данные таблицы по значениям нескольких указанных столбцов и размещает их в блоках оптимального размера. Кластеризация повышает производительность запросов, позволяя BigQuery точнее оценивать стоимость их выполнения. Для кластеризованных столбцов запросы также позволяют избежать сканирования лишних данных.

В ClickHouse данные автоматически кластеризуются на диске на основе столбцов первичного ключа таблицы и логически организуются в блоки, которые запросы, использующие структуру первичного индекса, могут быстро находить или отсекать.

​

materialized views

И BigQuery, и ClickHouse поддерживают materialized views — предварительно вычисленные результаты, формируемые на основе запроса преобразования к базовой таблице, что повышает производительность и эффективность.

​

Запросы к materialized view

Materialized view в BigQuery можно запрашивать напрямую или использовать через оптимизатор для обработки запросов к базовым таблицам. Если изменения в базовых таблицах могут сделать materialized view недействительным, данные считываются напрямую из базовых таблиц. Если изменения в базовых таблицах не приводят к недействительности materialized view, остальная часть данных считывается из materialized view, а из базовых таблиц считываются только изменения.

В ClickHouse materialized view можно запрашивать только напрямую. Однако, в отличие от BigQuery (где materialized view автоматически обновляются в течение 5 минут после изменения базовых таблиц, но не чаще чем раз в 30 минут), materialized view всегда синхронизированы с базовой таблицей.

Обновление materialized view

BigQuery периодически полностью обновляет materialized view, выполняя запрос преобразования представления к базовой таблице. Между обновлениями BigQuery объединяет данные materialized view с новыми данными из базовой таблицы, чтобы обеспечивать согласованные результаты запросов и при этом продолжать использовать materialized view.

В ClickHouse materialized view обновляются инкрементально. Этот механизм обеспечивает высокую масштабируемость и низкие вычислительные затраты: materialized view с инкрементальным обновлением специально разработаны для сценариев, в которых базовые таблицы содержат миллиарды или триллионы строк. Вместо того чтобы повторно выполнять запросы к постоянно растущей базовой таблице для обновления materialized view, ClickHouse просто вычисляет частичный результат только по значениям вновь вставленных строк базовой таблицы. Затем этот частичный результат постепенно сливается в фоновом режиме с ранее вычисленным частичным результатом. Это существенно снижает вычислительные затраты по сравнению с повторным обновлением materialized view на основе всей базовой таблицы.

​

Транзакции

В отличие от ClickHouse, BigQuery поддерживает многооператорные транзакции в рамках одного запроса или нескольких запросов при использовании сеансов. Многооператорная транзакция позволяет выполнять мутации данных, например вставку или удаление строк в одной или нескольких таблицах, а затем либо зафиксировать изменения, либо атомарно откатить их. Многооператорные транзакции входят в дорожную карту ClickHouse на 2024 год.

​

Агрегатные функции

По сравнению с BigQuery, в ClickHouse доступно значительно больше встроенных агрегатных функций:

• BigQuery включает 18 агрегатных функций и 4 приближённые агрегатные функции.
• В ClickHouse доступно более 150 готовых агрегатных функций, а также мощные комбинаторы агрегации, позволяющие расширять поведение готовых агрегатных функций. Например, любую из этих 150+ готовых агрегатных функций можно применять к массивам вместо строк таблицы, просто добавив суффикс -Array. С суффиксом -Map любую агрегатную функцию можно применять к данным типа Map. А с суффиксом -ForEach любую агрегатную функцию можно применять к вложенным массивам.

​

Источники данных и форматы файлов

По сравнению с BigQuery, ClickHouse поддерживает значительно больше форматов файлов и источников данных:

• ClickHouse имеет встроенную поддержку загрузки данных более чем в 90 форматах файлов практически из любого источника данных
• BigQuery поддерживает 5 форматов файлов и 19 источников данных

​

Возможности языка SQL

ClickHouse предоставляет стандартный SQL со множеством расширений и улучшений, которые делают его удобнее для аналитических задач. Например, ClickHouse SQL поддерживает лямбда-функции и функции высшего порядка, поэтому при применении преобразований не требуется разворачивать массивы. Это большое преимущество по сравнению с другими системами, такими как BigQuery.

​

Массивы

По сравнению с BigQuery, где есть 8 функций для работы с массивами, в ClickHouse доступно более 80 встроенных функций для работы с массивами, которые позволяют элегантно и просто моделировать и решать широкий спектр задач.

В ClickHouse типичный паттерн состоит в том, чтобы использовать агрегатную функцию groupArray для (временного) преобразования значений из определённых строк таблицы в массив. Затем этот массив можно удобно обработать с помощью функций для работы с массивами, а результат — преобразовать обратно в отдельные строки таблицы с помощью функции arrayJoin.

Поскольку ClickHouse SQL поддерживает лямбда-функции высшего порядка, многие сложные операции с массивами можно выполнять простым вызовом одной из встроенных функций высшего порядка для работы с массивами, вместо того чтобы временно преобразовывать массивы обратно в таблицы, как это часто требуется в BigQuery, например для фильтрации или объединения в пары массивов. В ClickHouse эти операции сводятся к простому вызову функций высшего порядка arrayFilter и arrayZip соответственно.

Ниже приведено соответствие операций с массивами в BigQuery и ClickHouse:

BigQuery	ClickHouse
ARRAY_CONCAT	arrayConcat
ARRAY_LENGTH	length
ARRAY_REVERSE	arrayReverse
ARRAY_TO_STRING	arrayStringConcat
GENERATE_ARRAY	range

Создать массив с одним элементом на каждую строку в подзапросе

BigQuery

Функция ARRAY

SELECT ARRAY
  (SELECT 1 UNION  ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

/*-----------*
 | new_array |
 +-----------+
 | [1, 2, 3] |
 *-----------*/

ClickHouse

groupArray агрегатная функция

SELECT groupArray(*) AS new_array
FROM
(
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
)

   ┌─new_array─┐
1. │ [1,2,3]   │
   └───────────┘

Преобразовать массив в набор строк

BigQuery

Оператор UNNEST

SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
  AS element
WITH OFFSET AS offset
ORDER BY offset;

/*----------+--------*
 | element  | offset |
 +----------+--------+
 | foo      | 0      |
 | bar      | 1      |
 | baz      | 2      |
 | qux      | 3      |
 | corge    | 4      |
 | garply   | 5      |
 | waldo    | 6      |
 | fred     | 7      |
 *----------+--------*/

ClickHouse

оператор ARRAY JOIN

WITH ['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'] AS values
SELECT element, num-1 AS offset
FROM (SELECT values AS element) AS subquery
ARRAY JOIN element, arrayEnumerate(element) AS num;

/*----------+--------*
 | element  | offset |
 +----------+--------+
 | foo      | 0      |
 | bar      | 1      |
 | baz      | 2      |
 | qux      | 3      |
 | corge    | 4      |
 | garply   | 5      |
 | waldo    | 6      |
 | fred     | 7      |
 *----------+--------*/

Возврат массива дат

BigQuery

функция GENERATE_DATE_ARRAY

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

/*--------------------------------------------------*
 | example                                          |
 +--------------------------------------------------+
 | [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
 *--------------------------------------------------*/

функции range + arrayMap

ClickHouse

SELECT arrayMap(x -> (toDate('2016-10-05') + x), range(toUInt32((toDate('2016-10-08') - toDate('2016-10-05')) + 1))) AS example

┌─example───────────────────────────────────────────────┐
1. │ ['2016-10-05','2016-10-06','2016-10-07','2016-10-08'] │
   └───────────────────────────────────────────────────────┘

Вернуть массив временных меток

BigQuery

функция GENERATE_TIMESTAMP_ARRAY

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
                                INTERVAL 1 DAY) AS timestamp_array;

/*--------------------------------------------------------------------------*
 | timestamp_array                                                          |
 +--------------------------------------------------------------------------+
 | [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
 *--------------------------------------------------------------------------*/

ClickHouse

функции range + arrayMap

SELECT arrayMap(x -> (toDateTime('2016-10-05 00:00:00') + toIntervalDay(x)), range(dateDiff('day', toDateTime('2016-10-05 00:00:00'), toDateTime('2016-10-07 00:00:00')) + 1)) AS timestamp_array

Query id: b324c11f-655b-479f-9337-f4d34fd02190

   ┌─timestamp_array─────────────────────────────────────────────────────┐
1. │ ['2016-10-05 00:00:00','2016-10-06 00:00:00','2016-10-07 00:00:00'] │
   └─────────────────────────────────────────────────────────────────────┘

Фильтрация массивов

BigQuery

Требует временного преобразования массивов обратно в таблицы с помощью оператора UNNEST

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
  ARRAY(SELECT x * 2
        FROM UNNEST(some_numbers) AS x
        WHERE x < 5) AS doubled_less_than_five
FROM Sequences;

/*------------------------*
 | doubled_less_than_five |
 +------------------------+
 | [0, 2, 2, 4, 6]        |
 | [4, 8]                 |
 | []                     |
 *------------------------*/

ClickHouse

функция arrayFilter

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 arrayMap(x -> (x * 2), arrayFilter(x -> (x < 5), some_numbers)) AS doubled_less_than_five
FROM Sequences;

   ┌─doubled_less_than_five─┐
1. │ [0,2,2,4,6]            │
   └────────────────────────┘
   ┌─doubled_less_than_five─┐
2. │ []                     │
   └────────────────────────┘
   ┌─doubled_less_than_five─┐
3. │ [4,8]                  │
   └────────────────────────┘

Объединение массивов поэлементно

BigQuery

Требуется временно преобразовать массивы обратно в таблицы с помощью оператора UNNEST

WITH
  Combinations AS (
    SELECT
      ['a', 'b'] AS letters,
      [1, 2, 3] AS numbers
  )
SELECT
  ARRAY(
    SELECT AS STRUCT
      letters[SAFE_OFFSET(index)] AS letter,
      numbers[SAFE_OFFSET(index)] AS number
    FROM Combinations
    CROSS JOIN
      UNNEST(
        GENERATE_ARRAY(
          0,
          LEAST(ARRAY_LENGTH(letters), ARRAY_LENGTH(numbers)) - 1)) AS index
    ORDER BY index
  );

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

ClickHouse

функция arrayZip

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)] │
   └───────────────────┘

Агрегация массивов

BigQuery

Требуется преобразовать массивы обратно в таблицы с помощью оператора UNNEST

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, arrayAvg, … функция или любое из более чем 90 существующих названий агрегатных функций в качестве аргумента для функции arrayReduce

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 │
   └──────────────┴──────┘