Добавление данных в 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:

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

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

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

В этом сценарии использования для иллюстрации принимается, что:
- идентификатор пользователя id представлен в виде целого беззнакового 64-битного числа (Uint64 в Cap'n Proto и ClickHouse, uint64 в Protobuf);
- имя пользователя name представлено в виде строки (Text в Cap'n Proto, string в Protobuf, String в ClickHouse).
Подробнее о поддерживаемых типах данных см. в документации Cap'n Proto, Protobuf и ClickHouse.
Подключитесь к кластеру и 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

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

Создайте файл с описанием схемы:
Cap'n Proto

Protobuf
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.
Загрузите файл в Object Storage и получите ссылку на него.
Подключите схему формата данных к кластеру chcluster:
- Для схемы формата данных Cap'n Proto (файл user.capnp) задайте имя schema-capnproto.
- Для схемы формата данных Protobuf (файл user.protobuf) задайте имя schema-protobuf.

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

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

Примечание

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

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

Cкомпилируйте файл схемы Protobuf user.proto для Python:
```
protoc user.proto --python_out .
```
Будет сгенерирован файл user_pb2.py.

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

Cap'n Proto

Protobuf

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())

Этот скрипт:

Получает класс User из подключенного файла user.capnp (from user_capnp import User).
Выполняет запросы к кластеру по HTTPS, использует SSL.
Записывает тестовый набор данных в объект класса User (def add_user ...) и добавляет этот объект к битовому потоку ввода-вывода message.
Вставляет данные из битового потока 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())