Связаться с намиПодключиться

Добавление данных в ClickHouse®

Статья создана
Обновлена 26 августа 2024 г.

Для обычной вставки данных в таблицы используйте запрос INSERT INTO:

INSERT INTO db_name.table_name VALUES (v11, v12, v13), (v21, v22, v23), ...

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

Подробнее о запросе INSERT INTO см. в документации ClickHouse®.

Вставка данных из файла

Для вставки данных в таблицу из локального файла используйте запрос INSERT INTO вида:

INSERT INTO db_name.table_name FROM INFILE '<полный_путь_к_файлу>'
[COMPRESSION '<формат_сжатия>'] FORMAT <формат_данных>;

Опция COMPRESSION позволяет передавать сжатые файлы. Используйте ее, чтобы загружать большие объемы информации. Опция поддерживается при работе через clickhouse-client или HTTP-интерфейс. Если формат сжатия не указан, то он определяется по расширению файла. Возможные значения формата сжатия: none, gzip, deflate, br, xz, zstd, lz4, bz2.

Список поддерживаемых форматов данных приведен в документации ClickHouse®. О настройке схем форматов данных Cap'n Proto и Protobuf см. в разделе Управление схемами формата данных.

Вставка данных с использованием буферизации

При вставке данных в ClickHouse® часть вычислительных ресурсов расходуется на выполнение служебных операций. При выполнении каждого INSERT-запроса ClickHouse® создает в хранилище отдельный кусок данных. Помимо строк таблицы такой кусок содержит ряд вспомогательных файлов с метаданными. Затем ClickHouse® объединяет куски данных в фоновом режиме. Чем больше потребуется операций объединения, тем больше будет задействовано ресурсов.

В результате тысяча запросов на вставку одной строки создаст большую нагрузку на кластер, чем один запрос на вставку тысячи строк. Поэтому рекомендуется вставлять данные в таблицу большими порциями — от 1000 до 100 000 строк.

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

Асинхронная вставка данных

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

  • Размер буфера достиг значения настройки Async insert max data size.
  • С момента первого после сброса данных INSERT-запроса прошло время, указанное в настройке Async insert busy timeout.

Чтобы включить асинхронную вставку данных, установите значение настройки Async insert на 1.

При использовании асинхронных вставок недоступна дедупликация строк.

Подробную информацию об асинхронной вставке данных см. в документации ClickHouse®.

Вставка данных через буферную таблицу

Буферная таблица создается в оперативной памяти, накапливает поступающие в нее данные и сбрасывает их в основную таблицу при выполнении заданных условий.

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

Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)

Где:

  • database — имя БД.
  • table — имя таблицы, в которую будут сброшены данные.
  • num_layers — количество буферов. Физически таблица хранится в памяти в виде нескольких независимых буферов.
  • min_time, max_time, min_rows, max_rows, min_bytes и max_bytes — параметры, определяющие условия сброса данных в основную таблицу. Время измеряется в секундах.

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

Информацию о дополнительных параметрах движка и ограничениях таблиц на движке Buffer см. в документации ClickHouse®.

Пример

  1. Создайте обычную таблицу users в базе данных db1:

    CREATE TABLE IF NOT EXISTS db1.users (id UInt64, name String)
ENGINE = MergeTree() ORDER BY id;

  2. Создайте буферную таблицу users_buffer, привязанную к основной таблице users:

    CREATE TABLE db1.users_buffer AS db1.users ENGINE = Buffer(db1, users, 1, 10, 100, 10000, 1000000, 10000000, 100000000);

  3. Передайте данные в буферную таблицу:

    INSERT INTO db1.users_buffer VALUES (1, 'Vlad'), (2, 'John'), (3, 'Zara');

  4. Проверьте данные в основной таблице, они появятся через 100 секунд (max_time):

    SELECT * FROM db1.users;

Результат:

┌─id─┬─name─┐
│  1 │ Vlad │
│  2 │ John │
│  3 │ Zara │
└────┴──────┘

Вставка данных с указанием схемы формата данных

Managed Service for ClickHouse® позволяет вставлять (INSERT) и выводить (SELECT) данные в различных форматах. Большинство таких форматов — самоописываемые, то есть они уже содержат в себе схему формата данных, описывающую допустимые типы данных, их порядок и представление в этом формате. Это позволяет, например, сразу делать вставку из файла.

Примечание

Схема формата данных (format schema) описывает формат ввода или вывода данных, в то время как схема данных (data scheme) описывает структуру и устройство баз данных и таблиц ClickHouse®, которые хранят эти данные. Эти понятия не являются взаимозаменяемыми.

Форматы данных Cap'n Proto и Protobuf (включая ProtobufSingle) не содержат в себе схему формата — данные представляются в бинарном виде без какой-либо информации о структуре. Перед началом работы с данными в этих форматах (например, перед вставкой в таблицу) необходимо подключить схему формата данных к кластеру Managed Service for ClickHouse®. Это позволит правильно интерпретировать количество, порядок и тип значений при работе с бинарными данными.

Вы можете подключить к кластеру Managed Service for ClickHouse® одну или несколько таких схем формата и использовать их для ввода и вывода требуемых данных в соответствующих форматах.

Важно

Чтобы использовать подключенные схемы формата, вставку данных в Managed Service for ClickHouse® следует производить с помощью HTTP-интерфейса, т. к. в этом случае сериализация и десериализация данных производится на стороне сервера с использованием подключенных схем.

Подробнее о форматах данных см. в документации ClickHouse®.

Пример работы со схемой формата при вставке данных

Пример проверялся в следующем окружении:

  • Виртуальная машина в Облаке с Ubuntu 20.04 LTS.
  • Bash: 5.0.16.
  • clickhouse-client: 20.10.2.20.
  • capnproto: 0.7.0.
  • protobuf-compiler: 3.6.1.
  • Python: 3.8.5; pip3: 20.0.2.

Допустим, что создан однохостовый кластер Managed Service for ClickHouse® chclusterс базой данных db1 и нужно вставить данные о пользователях в таблицу db1.users. Пусть каждая запись о пользователе содержит следующую информацию:

  • идентификатор пользователя id;
  • имя пользователя name.

Чтобы вставить данные о пользователях в форматах Cap'n Proto и Protobuf в таблицу db1.users:

  1. Установите зависимости.
  2. Подготовьте схемы формата данных.
  3. Подготовьте скрипты.
  4. Вставьте данные.

Перед началом работы

  1. Изучите формат данных, который будет использоваться при вставке, чтобы подготовить корректные схемы формата.

    В этом сценарии использования для иллюстрации принимается, что:

    • идентификатор пользователя id представлен в виде целого беззнакового 64-битного числа (Uint64 в Cap'n Proto и ClickHouse®, uint64 в Protobuf);
    • имя пользователя name представлено в виде строки (Text в Cap'n Proto, string в Protobuf, String в ClickHouse®).

    Подробнее о поддерживаемых типах данных см. в документации Cap'n Proto, Protobuf и ClickHouse®.

  2. Подключитесь к кластеру и создайте таблицу db1.users нужного вида, если ее еще не существует:

    CREATE TABLE IF NOT EXISTS db1.users (id UInt64, name String)
ENGINE = MergeTree() ORDER BY id;

Установка зависимостей

sudo apt update && sudo apt install -y python3 python3-pip capnproto protobuf-compiler && \
pip3 install protobuf varint pycapnp

Подготовка схем формата данных

  1. Создайте файл с описанием схемы:

    user.capnp

    @0xbec0f3f99cec4fbf;

struct User {
  id @0 :UInt64;
  name @1 :Text;
}

    Подробнее о формате файла см. в документации Cap'n Proto.

    user.proto

    syntax = "proto3";

message User {
  uint64 id = 1;
  string name = 2;
};

    Подробнее о формате файла см. в документации Protobuf.

  2. Загрузите файл в Object Storage и получите ссылку на него.

  3. Подключите схему формата данных к кластеру chcluster:

    • Для схемы формата данных Cap'n Proto (файл user.capnp) задайте имя schema-capnproto.
    • Для схемы формата данных Protobuf (файл user.protobuf) задайте имя schema-protobuf.

Подготовка скриптов

Эти Python-скрипты подготавливают тестовые данные о пользователях в нужных форматах и вставляют их в таблицу кластера.

Примечание

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

Чтобы подготовить скрипты:

  1. Скомпилируйте файл схемы Protobuf user.proto для Python:

    protoc user.proto --python_out .

    Будет сгенерирован файл user_pb2.py.

  2. Создайте файлы с кодом Python:

    capnproto-example.py

    import requests
import io
import capnp
from user_capnp import User

DB_HOST="<FQDN_хоста_ClickHouse®>"
DB_NAME="db1"
DB_USER="<имя_пользователя_БД>"
DB_PASS="<пароль_пользователя_БД>"
CA_CERT="/usr/local/share/ca-certificates/Yandex/RootCA.crt"

SCHEMA_NAME = 'schema-capnproto'
SCHEMA_TYPE = "CapnProto"
SCHEMA_CLASS = "User"

def execute_query(query, data=None):
    url = 'https://{host}:8443/'.format(host=DB_HOST)
    params = {
        'database': DB_NAME,
        'query': query.strip()
    }
    auth = {
        'X-ClickHouse-User': DB_USER,
        'X-ClickHouse-Key': DB_PASS
    }

    rs = requests.post(url,
                       params=params,
                       headers=auth,
                       data=data,
                       verify=CA_CERT)

    rs.raise_for_status()

def add_user(fileobj, user_id, user_name):
    user = User.new_message()
    user.id = user_id
    user.name = user_name

    fileobj.write(user.to_bytes())

message = io.BytesIO()

add_user(message, 11, 'John')
add_user(message, 12, 'Bob')
add_user(message, 13, 'Jane')

execute_query(
    '''
    INSERT INTO {database}.users SETTINGS format_schema='{name}:{cls}' FORMAT {type}
    '''.format(database=DB_NAME,
               type=SCHEMA_TYPE,
               name=SCHEMA_NAME,
               cls=SCHEMA_CLASS), data=message.getvalue())

    Этот скрипт:

    1. Получает класс User из подключенного файла user.capnp (from user_capnp import User).
    2. Выполняет запросы к кластеру по HTTPS, использует SSL.
    3. Записывает тестовый набор данных в объект класса User (def add_user ...) и добавляет этот объект к битовому потоку ввода-вывода message.
    4. Вставляет данные из битового потока message в таблицу db1.users, опираясь на данные класса User схемы формата данных schema-capnproto в кластере.

    protobuf-example.py

    import requests
import io
import varint
from user_pb2 import User

DB_HOST="<FQDN_хоста_ClickHouse®>"
DB_NAME="db1"
DB_USER="<имя_пользователя_БД>"
DB_PASS="<пароль_пользователя_БД>"
CA_CERT="/usr/local/share/ca-certificates/Yandex/RootCA.crt"

SCHEMA_NAME = 'schema-protobuf'
SCHEMA_TYPE = "Protobuf"
SCHEMA_CLASS = "User"

def execute_query(query, data=None):
    url = 'https://{host}:8443/'.format(host=DB_HOST)
    params = {
        'database': DB_NAME,
        'query': query.strip()
    }
    auth = {
        'X-ClickHouse-User': DB_USER,
        'X-ClickHouse-Key': DB_PASS
    }

    rs = requests.post(url,
                       params=params,
                       headers=auth,
                       data=data,
                       verify=CA_CERT)

    rs.raise_for_status()

def add_user(fileobj, user_id, user_name):
    user = User()
    user.id = user_id
    user.name = user_name

    fileobj.write(varint.encode(user.ByteSize()))
    fileobj.write(user.SerializeToString())

message = io.BytesIO()
add_user(message, 21, 'Stephen')
add_user(message, 22, 'Olivia')
add_user(message, 23, 'Tim')

execute_query(
    '''INSERT INTO {database}.users SETTINGS format_schema='{name}:{cls}' FORMAT {type}
    '''.format(database=DB_NAME,
               type=SCHEMA_TYPE,
               name=SCHEMA_NAME,
               cls=SCHEMA_CLASS), data=message.getvalue())

    Этот скрипт:

    1. Получает класс User из подключенного файла user_pb2.py, который был получен после компиляции proto-файла (from user_pb2 import User).
    2. Записывает тестовый набор данных в объект класса User (def add_user ...) и добавляет этот объект к битовому потоку ввода-вывода message.
    3. Вставляет данные из битового потока message в таблицу db1.users, опираясь на данные класса User схемы формата данных schema-protobuf в кластере.

    О том, как получить FQDN хоста, см. инструкцию.

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

  1. Запустите подготовленные на предыдущем этапе скрипты:

    python3 capnproto-example.py

    python3 protobuf-example.py

  2. Подключитесь к кластеру и проверьте, что данные были успешно вставлены, выполнив запрос SELECT:

    SELECT * FROM db1.users;

┌─id─┬─name─┐
│ 11 │ John │
│ 12 │ Bob  │
│ 13 │ Jane │
└────┴──────┘

    SELECT * FROM db1.users;

┌─id─┬─name────┐
│ 21 │ Stephen │
│ 22 │ Olivia  │
│ 23 │ Tim     │
└────┴─────────┘

ClickHouse® является зарегистрированным товарным знаком ClickHouse, Inc.

Предыдущая
Все руководства
Следующая
Миграция данных в Managed Service for ClickHouse®