Вставка данных в ClickHouse

Как выполнять вставку данных в ClickHouse

​

Вставка в ClickHouse в сравнении с OLTP-базами данных

Как OLAP-база данных (онлайн-аналитическая обработка), ClickHouse оптимизирована для высокой производительности и масштабируемости, что позволяет потенциально вставлять миллионы строк в секунду. Это достигается за счет сочетания высокопараллельной архитектуры и эффективного столбцово-ориентированного сжатия, но ценой компромисса в отношении немедленной согласованности. Точнее, ClickHouse оптимизирована для операций только на добавление и предоставляет лишь гарантии eventual consistency.

В отличие от нее, OLTP-базы данных, такие как Postgres, специально оптимизированы для транзакционных вставок с полным соблюдением ACID, обеспечивая строгую согласованность и надежность. PostgreSQL использует MVCC (Multi-Version Concurrency Control) для обработки параллельных транзакций, что предполагает хранение нескольких версий данных. Такие транзакции могут затрагивать лишь небольшое число строк за раз, при этом из-за гарантий надежности возникают значительные накладные расходы, которые ограничивают производительность вставки.

Чтобы добиться высокой производительности вставки при сохранении строгих гарантий согласованности, при вставке данных в ClickHouse следует придерживаться простых правил, описанных ниже. Соблюдение этих правил поможет избежать проблем, с которыми пользователи часто сталкиваются при первом знакомстве с ClickHouse, пытаясь применять стратегию вставки, подходящую для OLTP-баз данных.

​

Рекомендации по вставке данных

​

Выполняйте вставку большими батчами

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

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

​

Обеспечьте согласованность батчей для идемпотентных повторных попыток

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

Это означает, что вставки остаются устойчивыми в следующих случаях:

• 1. Если на узле, принимающем данные, возникают проблемы, запрос на вставку завершится по тайм-ауту (или вернет более конкретную ошибку), и подтверждение получено не будет.
• 2. Если узел записал данные, но из-за сетевых сбоев не может вернуть подтверждение отправителю запроса, отправитель либо получит тайм-аут, либо ошибку сети.

С точки зрения клиента случаи (i) и (ii) бывает трудно различить. Однако в обоих случаях вставку без подтверждения можно сразу повторить. Если повторный запрос на вставку содержит те же данные и в том же порядке, ClickHouse автоматически проигнорирует повторную вставку, если исходная вставка (без подтверждения) завершилась успешно.

​

Вставка в таблицу MergeTree или distributed таблицу

Мы рекомендуем выполнять вставку напрямую в таблицу MergeTree (или таблицу Replicated), балансируя запросы между узлами, если данные разбиты на сегменты, и устанавливая internal_replication=true. Это позволит ClickHouse реплицировать данные на любые доступные реплики сегментов и обеспечит итоговую согласованность данных.

Если такая балансировка нагрузки на стороне клиента неудобна, то можно выполнять вставку через distributed таблицу, которая затем распределит записи по узлам. В этом случае также рекомендуется установить internal_replication=true. Однако стоит отметить, что этот подход немного менее производителен, поскольку записи сначала выполняются локально на узле с distributed таблицей, а затем отправляются в сегменты.

​

Используйте асинхронные вставки для небольших батчей

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

При асинхронных вставках данные сначала попадают в буфер, а затем позже записываются в хранилище базы данных в 3 этапа, как показано на схеме ниже:

Когда асинхронные вставки включены, ClickHouse:

(1) асинхронно принимает запрос на вставку. (2) сначала записывает данные запроса в буфер в памяти. (3) сортирует данные и записывает их в виде части в хранилище базы данных только при следующем сбросе буфера на диск.

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

Обратите внимание: до сброса в хранилище базы данных эти данные недоступны для запросов, а параметры сброса буфера на диск можно настраивать.

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

​

Используйте официальные клиенты ClickHouse

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

Полный список доступных клиентов и драйверов ClickHouse см. в разделе Clients and Drivers.

​

Предпочитайте формат Native

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

Если приложению нужна оптимальная производительность вставки, используйте формат Native. Он поддерживается большинством клиентов (например, Go и Python) и позволяет свести нагрузку на сервер к минимуму, поскольку этот формат уже является столбцово-ориентированным. В результате ответственность за преобразование данных в столбцово-ориентированный формат переносится на сторону клиента. Это важно для эффективного масштабирования вставок.

В качестве альтернативы можно использовать формат RowBinary (как в Java-клиенте), если предпочтителен построчный формат — его обычно проще формировать, чем формат Native. С точки зрения сжатия, сетевых накладных расходов и обработки на сервере он эффективнее, чем другие построчные форматы, такие как JSON. Формат JSONEachRow можно рассмотреть, если у вас невысокая пропускная способность записи и нужно быстро выполнить интеграцию. Однако учитывайте, что из-за парсинга этот формат создает дополнительную нагрузку на CPU в ClickHouse.

​

Использование HTTP-интерфейса

В отличие от многих традиционных баз данных, ClickHouse поддерживает HTTP-интерфейс. Его можно использовать как для вставки данных, так и для выполнения запросов, применяя любой из перечисленных выше форматов. Часто это предпочтительнее собственного протокола ClickHouse, поскольку трафик в этом случае легко направлять через балансировщики нагрузки. При использовании собственного протокола возможны небольшие отличия в производительности вставки, так как у него немного меньше накладных расходов. Существующие клиенты используют либо один из этих протоколов, либо оба сразу (например, клиент Go). Собственный протокол также позволяет легко отслеживать прогресс выполнения запроса.

См. HTTP-интерфейс, чтобы узнать подробности.

​

Базовый пример

Вы можете использовать в ClickHouse привычную команду INSERT INTO TABLE. Давайте вставим данные в таблицу, которую мы создали в руководстве быстрого старта “Создание таблиц в ClickHouse”.

INSERT INTO helloworld.my_first_table (user_id, message, timestamp, metric) VALUES
    (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
    (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
    (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
    (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 )

Чтобы проверить, что всё сработало, выполним следующий запрос SELECT:

SELECT * FROM helloworld.my_first_table

В результате:

user_id message                                             timestamp           metric
101         Hello, ClickHouse!                                  2024-11-13 20:01:22     -1
101         Granules are the smallest chunks of data read           2024-11-13 20:01:27 3.14159
102         Insert a lot of rows per batch                          2024-11-12 00:00:00 1.41421
102         Sort your data based on your commonly-used queries  2024-11-13 00:00:00     2.718

​

Загрузка данных из Postgres

Для загрузки данных из Postgres можно использовать:

• ClickPipes — ETL-инструмент, специально разработанный для репликации баз данных PostgreSQL. Он доступен в обоих вариантах:
  • ClickHouse Cloud — через наш управляемый сервис ингестии в ClickPipes.
  • Самоуправляемый — через open-source-проект PeerDB.
• PostgreSQL table engine для прямого чтения данных, как показано в предыдущих примерах. Обычно это подходящий вариант, если достаточно батч-репликации на основе известной водяной метки, например временной метки, или если речь идёт о разовой миграции. Этот подход масштабируется до десятков миллионов строк. Если вам нужно мигрировать более крупные наборы данных, стоит разбить процесс на несколько запросов, каждый из которых обрабатывает фрагмент данных. Для каждого фрагмента можно использовать staging-таблицы до переноса его партиций в итоговую таблицу. Это позволяет повторно выполнять запросы, завершившиеся ошибкой. Дополнительные сведения об этой стратегии массовой загрузки см. здесь.
• Данные можно экспортировать из PostgreSQL в формате CSV. Затем их можно вставить в ClickHouse либо из локальных файлов, либо через Объектное хранилище с использованием табличных функций.

Нужна помощь со вставкой больших наборов данных?

Если вам нужна помощь со вставкой больших наборов данных или вы столкнулись с ошибками при импорте данных в ClickHouse Cloud, свяжитесь с нами по адресу [e-mail], и мы поможем.

​

Вставка данных из командной строки

Предварительные требования

• У вас установлен ClickHouse
• clickhouse-server запущен
• У вас есть доступ к терминалу, где доступны wget, zcat и curl

В этом примере показано, как вставить CSV-файл в ClickHouse из командной строки с помощью clickhouse-client в батч-режиме. Дополнительные сведения и примеры вставки данных через командную строку с помощью clickhouse-client в батч-режиме см. в разделе “Батч-режим”.

В качестве примера мы будем использовать набор данных Hacker News, который содержит 28 миллионов строк с данными Hacker News.

1. ​

   Скачайте CSV

   Выполните следующую команду, чтобы скачать CSV-версию набора данных из нашего публичного S3 бакета:

   wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz
   

   Этот сжатый файл размером 4,6 ГБ, содержащий 28 млн строк, должен скачаться за 5–10 минут.

2. ​

   Создайте таблицу

   При запущенном clickhouse-server вы можете создать пустую таблицу со следующей схемой прямо из командной строки с помощью clickhouse-client в батч-режиме:

   clickhouse-client <<'_EOF'
   CREATE TABLE hackernews(
       `id` UInt32,
       `deleted` UInt8,
       `type` Enum('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5),
       `by` LowCardinality(String),
       `time` DateTime,
       `text` String,
       `dead` UInt8,
       `parent` UInt32,
       `poll` UInt32,
       `kids` Array(UInt32),
       `url` String,
       `score` Int32,
       `title` String,
       `parts` Array(UInt32),
       `descendants` Int32
   )
   ENGINE = MergeTree
   ORDER BY id
   _EOF
   

   Если ошибок нет, таблица успешно создана. В команде выше вокруг разделителя heredoc (_EOF) используются одинарные кавычки, чтобы предотвратить подстановку значений. Без одинарных кавычек пришлось бы экранировать обратные кавычки вокруг имен столбцов.

3. ​

   Вставьте данные из командной строки

   Теперь выполните приведенную ниже команду, чтобы вставить в таблицу данные из файла, который вы скачали ранее:

   zcat < hacknernews.csv.gz | ./clickhouse client --query "INSERT INTO hackernews FORMAT CSV"
   

   Поскольку данные сжаты, сначала нужно распаковать файл с помощью gzip, zcat или аналогичного инструмента, а затем передать распакованные данные по каналу в clickhouse-client с соответствующим оператором INSERT и FORMAT.

   При вставке данных с помощью clickhouse-client в интерактивном режиме можно позволить ClickHouse выполнить распаковку за вас во время вставки с помощью предложения COMPRESSION. ClickHouse может автоматически определить тип сжатия по расширению файла, но его также можно указать явно.

   Тогда запрос на вставку будет выглядеть так:

   clickhouse-client --query "INSERT INTO hackernews FROM INFILE 'hacknernews.csv.gz' COMPRESSION 'gzip' FORMAT CSV;"
   

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

   clickhouse-client --query "SELECT formatReadableQuantity(count(*)) FROM hackernews"
   28.74 million
   
4. ​

   Вставка данных через командную строку с использованием curl

   В предыдущих шагах вы сначала скачали CSV-файл на локальную машину с помощью wget. Также можно вставить данные напрямую с удаленного URL одной командой.

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

   clickhouse-client --query "TRUNCATE hackernews"
   

   Теперь выполните:

   curl https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz | zcat | clickhouse-client --query "INSERT INTO hackernews FORMAT CSV"
   

   Теперь вы можете выполнить ту же команду, что и ранее, чтобы убедиться, что данные были вставлены повторно:

   clickhouse-client --query "SELECT formatReadableQuantity(count(*)) FROM hackernews"
   28.74 million