Практическая работа
Гибридное хранение и обработка данных в Clickhouse
Из описания сценария вы узнаете, как организовать кластер Managed Service for ClickHouse с гибридным хранилищем, которое позволит управлять жизненным циклом данных.
Шаг 1.
Создадим кластер ClickHouse. В консоли управления Yandex.Cloud выберите Managed Service for ClickHouse. В правой верхней части страницы нажмите Создать кластер. Откроется страница с параметрами кластера ClickHouse:
В разделе Базовые параметры задайте имя кластера как inventory.
В разделе База данных задайте имя БД и имя пользователя как inventory. Также задайте пароль для пользователя.
В разделе Хосты добавьте новый хост. Укажите зону доступности ru-central1-a,
установите настройку «Публичный доступ», для возможности подключиться к калстеру через публичный интернет и нажмите Сохранить.

В разделе Дополнительные настройки включите опции:
  • Гибридное хранилище.
  • Доступ из DataLens.
  • Доступ из консоли управления.
  • Доступ из Метрики и AppMetrica.
  • Доступ из Serverless.
Опции позволяют интегрировать ClickHouse с внешними системами.
  • Нажмите Создать кластер.
  • На создание кластера может уйти некоторое время. Когда кластер будет создан, его статус измениться на Alive:
После создания кластера откройте DBeaver. В левом верхнем углу нажмите значок, чтобы создать соединение к источнику данных. В открывшемся окне выберите тип All и нажмите на ClickHouse:
Нажмите Далее. Откроется окно с параметрами соединения:
Укажите параметры:
  • Хост кластера ClickHouse.
  • Порт.
  • БД/Схема — название БД. В нашем случае inventory.
  • Пользователь.В нашем случае inventory.
  • Пароль пользователя.
Нажмите Готово, чтобы сохранить соединение. Во вкладке Базы данных появится БД с именем inventory:

Если Вы подключаетесь к кластеру ClickHouse через публичный интернет – необходимо использовать SSL-соединение. Подготовьте SSL-сертификат , переключитесь на вкладку На вкладке SSH/SSL:
  1. Включите настройку Use SSL.
  2. Укажите путь к файлу SSL-сертификата для подключения.
Нажмите Готово, чтобы сохранить соединение.
Подробнее о настройке подключения через dBeaver см. документацию.

Во вкладке Базы данных появится БД с именем inventory:
Шаг 2.
Теперь можно создать связь с таблицей во внешней БД MySQL. Конфигурирование интеграционных таблиц осуществляется с помощью запросов CREATE TABLE или ALTER TABLE. Настроенная интеграция выглядит как обычная таблица, но запросы к ней передаются во внешнюю систему.
Нажмите на БД inventory. На панели сверху нажмите SQL → Новый редактор SQL (или значок
). Откроется редактор SQL:
В редакторе введите запрос на создание таблицы mysql_sales_order_grid:
CREATE TABLE mysql_sales_order_grid  (
  `entity_id` UInt32,
  `status` Nullable(String),
  `store_id` Nullable(UInt16),
  `store_name` Nullable(String), 
  `customer_id` Nullable(UInt32),
  `grand_total` Nullable(Float32),
  `order_currency_code` String,
  `created_at` DateTime,
  `updated_at` DateTime,
  `billing_address` Nullable(String),
  `shipping_address` Nullable(String),
  `shipping_information` Nullable(String),
  `customer_email` Nullable(String),
  `customer_group` Nullable(String)
 
)  ENGINE = MySQL('<my-yandex-cloud-cluster>.mdb.yandexcloud.net:3306', '<mysql-database-name>', 'sales_order_grid', '<mysql-user>', '<mysql-user-pwd>');
В запросе замените:
  • <my-yandex-cloud-cluster> на хост кластера MySQL.
  • <mysql-database-name> на имя БД.
  • <mysql-user> на имя пользователя.
  • <mysql-user-pwd> на пароль пользователя.
Чтобы отправить запрос, выделите его в окне редактирования и нажмите значок:
Чтобы посмотреть содержимое созданной страницы, можно отправить запрос:
select * from mysql_sales_order_grid;
Отправьте запрос на создание таблицы sales_order:
CREATE TABLE sales_order ON CLUSTER  `{cluster}`(
  `entity_id` UInt32,
  `status` Nullable(String),
  `store_id` Nullable(UInt16),
  `store_name` Nullable(String), 
  `customer_id` Nullable(UInt32),
  `grand_total` Nullable(Float32),
  `order_currency_code` String,
  `created_at` DateTime,
  `updated_at` DateTime,
  `billing_address` Nullable(String),
  `shipping_address` Nullable(String),
  `shipping_information` Nullable(String),
  `customer_email` Nullable(String),
  `customer_group` Nullable(String)
) ENGINE = ReplacingMergeTree
  PARTITION BY toYYYYMM(`created_at`)
  ORDER BY (`entity_id`)
  TTL `created_at` + INTERVAL 1 MONTH TO DISK 'object_storage';
ReplacingMergeTree позволяет исключить дубликаты записей.
Если Вы создали кластер ClickHose из нескольких реплик, для того, чтобы таблица sales_order реплицировалась – нужно использовать конструкцию вида:
ReplacingMergeTree('/clickhouse/tables/{shard}/sales_order', '{replica}')

TTL (Time to Live) указывает, что через месяц после создания записи, она будет перенесена в Yandex Object Storage в системный раздел S3.
ReplacingMergeTree позволяет исключить дубликаты записей.
TTL (Time to Live) указывает, что через месяц после создания записи, она будет перенесена в Yandex Object Storage в системный раздел S3.
Отправьте запрос на добавление строк в таблицу sales_order:
INSERT INTO inventory.sales_order (entity_id, status, store_id, store_name, customer_id, grand_total, order_currency_code, created_at, updated_at, 
billing_address, shipping_address, shipping_information, customer_email, customer_group) 
SELECT  entity_id, status, store_id, store_name, customer_id, grand_total, order_currency_code, created_at, updated_at, 
billing_address, shipping_address, shipping_information, customer_email, customer_group
FROM mysql_sales_order_grid
WHERE entity_id > (SELECT MAX(entity_id) from sales_order);
Чтобы посмотреть содержимое страницы sales_order, можно отправить запрос:
select * from sales_order so;
Чтобы оценить объем данных, который находится на сетевых дисках и в Object Storage, можно отправить запрос:
SELECT * FROM system.disks;
В ответе вы должны получить кластер ClickHouse с гибридным хранилищем:
Так гибридное хранилище позволяет хранить часто используемые данные в кластере Managed Service for ClickHouse, а архивные данные — в Yandex Object Storage.
Конец документа