Перейти к основному содержимому
Перейти к основному содержимому

Миграция из 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-хранилище, а команды 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 хранятся на диске в порядке, заданном столбцами первичного ключа. Этот порядок сортировки используется оптимизатором запросов для предотвращения повторной сортировки, минимизации использования памяти для соединений и обеспечения короткого замыкания для ограничений.

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

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

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

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

Выбор ключа упорядочивания​

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

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

Мы рекомендуем пользователям, migrating from 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

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