Подключиться
Yandex Managed Service for ClickHouse

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

Статья создана

Обычная вставка данных

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

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

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

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.

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

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

  • Виртуальная машина в Облаке c 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. Подключитесь к кластеру и cоздайте таблицу 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. Cкомпилируйте файл схемы 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/YandexInternalRootCA.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 FORMAT {type} SETTINGS format_schema='{name}:{cls}'
    '''.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/YandexInternalRootCA.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 FORMAT {type} SETTINGS format_schema='{name}:{cls}'
    '''.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 в кластере.

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

  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     │
└────┴─────────┘
В этой статье: