Аналитика в Yandex DataLens за пределами CSV: строим аналитические витрины в ClickHouse

Бывает, что блоки данных, которые необходимо загружать и анализировать в реальном времени, становятся слишком большими для использования в строковых СУБД вроде PostgreSQL. В этом случае на помощь приходят колоночные СУБД. Они позволяют оптимизировать загрузку, хранение и OLAP‑сценарии обработки данных объёмом от сотен мегабайт до терабайт.

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

Доступная в Yandex Cloud как управляемая СУБД, ClickHouse является хорошим примером колоночной системы управления базами данных. Её создавали, чтобы было удобнее строить отчёты по неагрегированным логам действий пользователей сервисов Яндекса в интерактивном режиме. Но со временем ClickHouse получила больше функций и теперь поддерживает консольный клиент, HTTP API, ряд wrapper’ов на Python, PHP, Node.js, Perl, Ruby, R и многие другие. Также для ClickHouse есть JDBC‑ и Golang‑драйверы.

ClickHouse позволяет реализовать быстрый доступ к корпоративным хранилищам данных, поддерживает декларативный язык запросов на основе SQL, во многих случаях совпадающий с SQL‑стандартом. СУБД можно интегрировать с такими Big Data системами, как Apache Kafka^® и HDFS, а также с MySQL^® и прочими внешними источниками данных через ODBC или JDBC.

Рассмотрим развёртывание и загрузку данных из управляемой базы данных ClickHouse в Yandex DataLens. Попробуем подключиться к БД, загрузим данные несколькими способами с помощью IDE‑клиента и визуализируем их.

Шаг 1. Создание кластераШаг 1. Создание кластера

В конфигурации выбираем тип хоста Standard. Этой конфигурации многим будет достаточно, а при необходимости всегда можно добавить ресурсы.

Так как планируется загружать данные снаружи, в настройках нужно включить публичный доступ. Если же все данные будут курсировать между сервисами Yandex Cloud, это делать необязательно.

Шаг 2. Подключение к базе данныхШаг 2. Подключение к базе данных

База данных создана, переходим к вопросу взаимодействия.

ClickHouse поддерживает множество интерфейсов, в том числе основные: HTTP (REST API), TCP, gRPC, а также MySQL^® и PostgreSQL. Для удобства можно воспользоваться клиентом MySQL^® или PostgreSQL или клиентом, поддерживающим работу с ClickHouse, вроде DBeaver. Необходимые данные для подключения можно найти с помощью подсказки «Подключиться» в правом верхнем углу. Также могут понадобиться данные пользователя, использованные при создании кластера. Варианты подключения подробно описаны здесь.

Создадим подключение в DBeaver для комфортной работы со структурами данных. Обратите внимание, что для этого подключения следует использовать порт для HTTPS‑подключений (по умолчанию 8443), а в настройках SSL указать ssl = true и sslrootcert = <путь к сертификату>. Инструкцию по загрузке сертификата можно взять из той же подсказки.

Шаг 3. Создание структур и загрузка данных с помощью IDЕклиентаШаг 3. Создание структур и загрузка данных с помощью IDE‑клиента

Если всё сделано правильно, можно подключаться и создавать таблицу для загрузки. Для примера возьмём данные в количестве 1 000 000 строк, это примерно 380 Мб.

Создадим под них таблицу SQL‑командой или визуальными средствами клиента.

CREATE TABLE default.Sales
(
    Date Date,
    Diller String,
    StoreType String,
    State String,
    City String,
    Address String,
    StoreNo String,
    GroupName String,
    Brand String,
    SKU String,
    SKUCode String,
    Items Decimal(15, 3),
    Amount Decimal(15, 2),
    Price Decimal(15, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(Date)
ORDER BY (Date, Diller, Brand, SKU);

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

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

Загрузка полного блока данных в миллион строк с клиентской машины через обычный домашний интернет заняла около 80 секунд.

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

Можно переходить к DataLens, но прежде стоит рассмотреть вариант импорта данных с использованием клиента командной строки и http‑интерфейса, которые лучше подходят для реализации автоматической загрузки по расписанию.

Шаг 4. Автоматизируемые и быстрые методы загрузки данныхШаг 4. Автоматизируемые и быстрые методы загрузки данных

Если в СУБД PostgreSQL (её возможности мы описывали ранее) и MySQL^® пакетная вставка (bulk insert) является, скорее, надстройкой, то в ClickHouse она реализуется командой INSERT в базовом функционале. Более того, поддерживается вставка данных большого перечня форматов, помимо CSV. Установка сервера и клиента ClickHouse описана тут.

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

Команда для загрузки данных из примера будет выглядеть так:

clickhouse-client --host rc1b-qo2ye7o0msuhb795.mdb.yandexcloud.net --secure \
                  --user admin --password passw0rd --port 9440 \
                  --format_csv_delimiter="|" \
                  --query="INSERT INTO Sales FORMAT CSVWithNames" < SalesExp.csv

* Список ключей, используемых при импорте данных, можно получить по команде: clickhouse-client --help или запросом: select * from system.settings.

В этом случае 1 000 000 строк, которые в формате csv занимают 380 Мб, попадают в базу данных за 6–7 секунд! Намного быстрее, чем через стороннего клиента (время загрузки — 80 секунд).

В итоге в сжатом виде данные занимают всего 37 Мб.

Реализация аналогичной загрузки с использованием HTTP‑интерфейса на Python тоже лаконичная и несложная:

import requests
import clickhouse
import pandas as pd
...
SSL_VERIFY = '/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt'
CH_HOST = 'rc1b-qo2ye7o0msuhb795.mdb.yandexcloud.net'
CH_USER = 'admin'
CH_PASS = 'passw0rd'
...

df = pd.read_csv('SalesExp.csv', sep = '\t') # для разнообразия входные данные с разделением табуляцией, как в выдаче Метрики
...

q = {
'format_csv_delimiter': "|",
'query': 'INSERT INTO visits_test FORMAT CSVWithNames'}

requests.post(CH_HOST, data=df.to_csv(index = False, sep = '|').encode('utf-8'), params=q, auth=(CH_USER, CH_PASS), verify=SSL_VERIFY)

Ещё с помощью HTTP‑интерфейса можно через браузер выполнять команды и запросы c URL. URL‑адрес будет выглядеть примерно таким образом: https://rc1a-....yandexcloud.net:8443/play.

Теперь переходим к визуализации и проверке скоростей в ней.

Шаг 5. Подключение DataLens и визуализацияШаг 5. Подключение DataLens и визуализация

Реализуем подключение и датасет в DataLens.

При использовании Managed Service настройка выполняется предельно просто: нужно задать облако и каталог и в появившихся списках кластеров, хостов и логинов выбрать нужные. Останется ввести пароль и установить уровень доступа SQL.

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

Всё готово для создания чартов и проверки быстродействия обработки блока данных в созданной связке.

Таким образом, за 5 простых шагов мы развернули экземпляр СУБД ClickHouse, опробовали 3 варианта загрузки фрагмента данных в миллион записей (с помощью DBeaver, консольного клиента и через Python) и получили неплохие показатели на скромной конфигурации как при загрузке, так и при отображении.