Миграция с BigQuery в ClickHouse Cloud

Почему стоит использовать ClickHouse Cloud вместо BigQuery?

TLDR: Потому что ClickHouse быстрее, дешевле и мощнее, чем BigQuery для современных аналитических задач:

Загрузка данных из BigQuery в ClickHouse Cloud

Набор данных

В качестве примера набора данных для демонстрации типичной миграции с BigQuery в ClickHouse Cloud мы используем набор данных Stack Overflow, задокументированный здесь. Это содержит каждое post, vote, user, comment и badge, которые произошли на Stack Overflow с 2008 года по апрель 2024 года. Схема BigQuery для этих данных показана ниже:

Для пользователей, которые хотят заполнить этот набор данных в экземпляре BigQuery для тестирования шагов миграции, мы предоставили данные для этих таблиц в формате Parquet в GCS bucket, и команды DDL для создания и загрузки таблиц в BigQuery доступны здесь.

Миграция данных

Миграция данных между BigQuery и ClickHouse Cloud делится на два основных типа рабочих нагрузок:

• Начальная массовая загрузка с периодическими обновлениями - Необходима миграция первоначального набора данных вместе с периодическими обновлениями через установленные интервалы, например, ежедневно. Обновления здесь обрабатываются повторной отправкой строк, которые изменились, - определяемых либо колонкой, которая может использоваться для сравнений (например, дата). Удаления обрабатываются с полной периодической перезагрузкой набора данных.
• Реальная репликация или CDC - Необходима миграция первоначального набора данных. Изменения в этом наборе данных должны отражаться в ClickHouse в реальном времени с задержкой в несколько секунд. Это фактически процесс Change Data Capture (CDC), при котором таблицы в BigQuery должны синхронизироваться с ClickHouse, т.е. вставки, обновления и удаления в таблице BigQuery должны применяться к эквивалентной таблице в ClickHouse.

Массовая загрузка через Google Cloud Storage (GCS)

BigQuery поддерживает экспорт данных в объектное хранилище Google (GCS). Для нашего примера набора данных:

1. Экспортируйте 7 таблиц в GCS. Команды для этого доступны здесь.

2. Импортируйте данные в ClickHouse Cloud. Для этого мы можем использовать функцию таблицы gcs. Команды DDL и запросы импорта доступны здесь. Обратите внимание, что поскольку экземпляр ClickHouse Cloud состоит из нескольких вычислительных узлов, вместо функции таблицы gcs, мы используем функцию таблицы s3Cluster. Эта функция также работает с корзинами gcs и использует все узлы сервиса ClickHouse Cloud для загрузки данных параллельно.

Этот подход имеет ряд преимуществ:

• Функциональность экспорта BigQuery поддерживает фильтр для экспорта подмножества данных.
• BigQuery поддерживает экспорт в форматы Parquet, Avro, JSON и CSV и несколько типов сжатия - все они поддерживаются ClickHouse.
• GCS поддерживает управление жизненным циклом объектов, позволяя удалять данные, которые были экспортированы и импортированы в ClickHouse, после заданного периода.
• Google позволяет бесплатно экспортировать до 50 ТБ в день в GCS. Пользователи платят только за хранение GCS.
• Экспорты автоматически создают несколько файлов, ограничивая каждый максимальным размером 1 ГБ данных таблицы. Это полезно для ClickHouse, поскольку позволяет параллелизовать импорты.

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

Реальная репликация или CDC через запланированные запросы

Change Data Capture (CDC) - это процесс, с помощью которого таблицы поддерживаются в синхронизации между двумя базами данных. Это значительно более сложно, если обновления и удаления необходимо обрабатывать в почти реальном времени. Один из подходов - просто запланировать периодический экспорт, используя функциональность запланированных запросов BigQuery. Если вы можете принять некоторое задержку в данных, вставляемых в ClickHouse, этот подход просто реализовать и поддерживать. Пример приведён в этом блоге.

Проектирование схем

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

Соблюдая этот принцип, мы сосредоточимся на основной таблице posts. Схема BigQuery для этого показана ниже:

Оптимизация типов

Применение процесса описанного здесь приводит к следующей схеме:

Мы можем заполнить эту таблицу с помощью простого INSERT INTO SELECT, считывая экспортированные данные из gcs, используя gcs функцию таблицы. Обратите внимание, что в ClickHouse Cloud вы также можете использовать совместимую с gcs s3Cluster функцию таблицы для параллелизации загрузки по нескольким узлам:

Мы не сохраняем никаких null в нашей новой схеме. Вышеуказанный insert неявно преобразует их в значения по умолчанию для своих соответствующих типов - 0 для целых чисел и пустое значение для строк. ClickHouse также автоматически преобразует любые числовые значения в целевую точность.

Каковы отличия первичных ключей ClickHouse?

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

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

• Эффективность использования памяти и диска имеют первостепенное значение для масштаба, на котором ClickHouse часто используется. Данные записываются в таблицы ClickHouse партиями, известными как parts, с установленными правилами для слияния этих частей в фоне. В ClickHouse у каждой части есть свой первичный индекс. Когда части сливаются, индексы первичных ключей объединённых частей также сливаются. Обратите внимание, что эти индексы не создаются для каждой строки. Вместо этого первичный индекс для части имеет одну запись индекса на группу строк - эта техника называется разреженной индексацией.
• Разреженная индексация возможна из-за того, что ClickHouse хранит строки для части на диске в порядке, установленном определённым ключом. Вместо того чтобы непосредственно находить отдельные строки (например, индекс, основанный на B-дереве), разреженный первичный индекс позволяет быстро (через бинарный поиск по записям индекса) идентифицировать группы строк, которые могут соответствовать запросу. Найденные группы потенциально подходящих строк затем, параллельно, потока в движок ClickHouse для нахождения совпадений. Этот дизайн индекса позволяет первичному индексу быть маленьким (он полностью вмещается в основную память), при этом значительно ускоряет время выполнения запросов, особенно для диапазонных запросов, которые типичны для случаев использования аналитики данных. Для получения дополнительных сведений мы рекомендуем это углубленное руководство.

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

Все колонки в таблице будут отсортированы на основе значения указанного ключа порядка, независимо от того, включены ли они в сам ключ. Например, если CreationDate используется в качестве ключа, порядок значений во всех остальных колонках будет соответствовать порядку значений в колонке CreationDate. Можно указать несколько ключей порядка - они будут сортированы с той же семантикой, что и клаузула ORDER BY в запросе SELECT.

Выбор ключа порядка

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

Техники моделирования данных

Мы рекомендуем пользователям, мигрирующим из BigQuery, ознакомиться с руководством по моделированию данных в ClickHouse. Это руководство использует тот же набор данных Stack Overflow и исследует несколько подходов с использованием возможностей ClickHouse.

Партиции

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

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

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

Данные части логически ассоциируются с каждой партицией на диске и могут быть запрошены в изоляции. В следующем примере мы партиционируем таблицу постов по годам с использованием выражения toYear(CreationDate). По мере вставки строк в ClickHouse это выражение будет оцениваться относительно каждой строки, и строки будут направлены в соответствующую партицию в виде новых частей данных, принадлежащих этой партиции.

Применения

Партиционирование в ClickHouse имеет аналогичные применения, как и в BigQuery, но с некоторыми тонкими отличиями. В частности:

• Управление данными - В ClickHouse пользователи должны прежде всего считать партиционирование функцией управления данными, а не техникой оптимизации запросов. Разделяя данные логически на основе ключа, каждую партицию можно обрабатывать независимо, например, удалять. Это позволяет пользователям перемещать партиции, а следовательно, подмножества, между уровнями хранения эффективно со временем или истекать данные/эффективно удалять из кластера. Например, ниже мы удаляем посты с 2008 года:

• Оптимизация запросов - Хотя партиции могут помочь с производительностью запросов, это сильно зависит от схем доступа. Если запросы нацелены только на несколько партиций (желательно одну), производительность может потенциально улучшиться. Это обычно полезно, если ключ партиционирования не находится в первичном ключе, и вы фильтруете по нему. Тем не менее, запросы, которым необходимо охватить много партиций, могут работать хуже, чем если бы не использовалось партиционирование (поскольку в результате партиционирования может быть больше частей). Преимущества нацеливания на одну партицию будут также менее выражены или вовсе отсутствовать, если ключ партиционирования уже является ранним элементом в первичном ключе. Партиционирование также можно использовать для оптимизации запросов GROUP BY, если значения в каждой партиции уникальны. Однако, в общем, пользователи должны убедиться, что первичный ключ оптимизирован и рассмотреть партиционирование как технику оптимизации запросов только в исключительных случаях, когда схемы обращения обращаются к определённому предсказуемому подмножеству дня, например, партиционирование по дням, в то время как большинство запросов в последний день.

Рекомендации

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

Важно: Убедитесь, что ваше выражение ключа партиционирования не приводит к высокой кардинальности, т.е. создание более 100 партиций следует избегать. Например, не партиционируйте ваши данные по колонкам высокой кардинальности, таким как идентификаторы клиентов или имена. Вместо этого сделайте идентификатор клиента или имя первой колонкой в выражении ORDER BY.

Внутри ClickHouse создаются части для вставленных данных. По мере вставки больше данных количество частей увеличивается. Чтобы предотвратить чрезмерно высокое количество частей, что ухудшает производительность запросов (поскольку читается больше файлов), части объединяются в фоне асинхронном процессе. Если количество частей превышает преднастроенный лимит, ClickHouse выдаст исключение при вставке, как ошибка "слишком много частей". Это не должно происходить в нормальной эксплуатации и происходит только в случае неправильной настройки ClickHouse или неправильного использования, например, множество мелких вставок. Так как части создаются для каждой партиции в изоляции, увеличение числа партиций приводит к увеличению количества частей, т.е. это кратное количество партиций. Ключи партиционирования с высокой кардинальностью могут, таким образом, вызвать эту ошибку, и их следует избегать.

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

Концепция проекций ClickHouse позволяет пользователям указывать несколько клаузул ORDER BY для таблицы.

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

Например, рассмотрим следующий запрос:

Этот запрос требует сканирования всех 90 миллионов строк (признаемся, быстро), так как UserId не является ключом порядка. Ранее мы решили эту задачу с помощью материализованного представления, действующего как поиск для PostId. Ту же проблему можно решить с помощью проекции. Команда ниже добавляет проекцию для ORDER BY user_id.

Обратите внимание, что сначала мы должны создать проекцию, а затем материализовать её. Эта последняя команда приводит к тому, что данные хранятся дважды на диске в двух различных порядках. Проекцию также можно определить при создании данных, как показано ниже, и она будет автоматически поддерживаться по мере вставки данных.

Если проекция создаётся через команду ALTER, создание происходит асинхронно, когда команда MATERIALIZE PROJECTION выдается. Пользователи могут подтвердить ход этой операции с помощью следующего запроса, ожидая, что is_done=1.

Если мы повторим вышеуказанный запрос, мы увидим, что производительность значительно улучшилась за счёт увеличения объёма хранения.

С помощью команды EXPLAIN мы также подтверждаем, что для этого запроса использовалась проекция:

Когда использовать проекции

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

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

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

• Проекции не позволяют использовать разные TTL для исходной таблицы и (скрытой) целевой таблицы. Материализованные представления позволяют использовать разные TTL.
• Проекции в настоящее время не поддерживают optimize_read_in_order для (скрытой) целевой таблицы.
• Лёгкие обновления и удаления не поддерживаются для таблиц с проекциями.
• Материализованные представления могут быть соединены: целевая таблица одного материализованного представления может быть исходной таблицей другого материализованного представления и так далее. Это невозможно с проекциями.
• Проекции не поддерживают соединения; материализованные представления поддерживают.
• Проекции не поддерживают фильтры (клаузу WHERE); материализованные представления поддерживают.

Мы рекомендуем использовать проекции, когда:

• Необходимо полное переупорядочение данных. Хотя выражение в проекции может, теоретически, использовать GROUP BY, более эффективно использовать материализованные представления для поддержания агрегатов. Оптимизатор запросов также с большей вероятностью использует проекции, которые используют простое переупорядочение, т.е. SELECT * ORDER BY x. Пользователи могут выбрать подмножество колонок в этом выражении, чтобы уменьшить объем хранения.
• Пользователи комфортны с ассоциированным увеличением объема хранения и накладными расходами на запись данных дважды. Проверьте влияние на скорость вставки и оцените накладные расходы на хранение.

Переписывание запросов BigQuery в ClickHouse

Ниже приведены примерные запросы, сравнивающие BigQuery с ClickHouse. Этот список призван продемонстрировать, как можно использовать функции ClickHouse для значительного упрощения запросов. Примеры здесь используют полный набор данных Stack Overflow (до апреля 2024 года).

Пользователи (с более чем 10 вопросами), которые получают наибольшее количество просмотров:

BigQuery

ClickHouse

Какие тэги получают наибольшее количество просмотров:

BigQuery

ClickHouse

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

Где это возможно, пользователи должны использовать агрегатные функции ClickHouse. Ниже мы показываем использование argMax функции для вычисления самого просматриваемого вопроса каждого года.

BigQuery

ClickHouse

Условные операторы и массивы

Функции условий и массивов значительно упрощают запросы. Следующий запрос вычисляет теги (с более чем 10000 вхождений) с наибольшим процентным увеличением с 2022 по 2023 год. Обратите внимание, как следующий запрос ClickHouse лаконичен благодаря условиям, функциям массивов и возможности повторного использования псевдонимов в предложениях HAVING и SELECT.

BigQuery

ClickHouse

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