Стриминг данных из SQL Server в ClickHouse для быстрой аналитики: пошаговое руководство

Стриминг данных из SQL Server в ClickHouse для быстрой аналитики

В этой статье мы разберем, как организовать стриминг данных из SQL Server в ClickHouse. ClickHouse — отличный выбор, если вам нужна сверхбыстрая аналитика для внутренней панели мониторинга или клиентских панелей мониторинга. Мы пошагово покажем, как настроить обе базы данных, как соединить их между собой и, наконец, как использовать Streamkap для стриминга данных. Если SQL Server обеспечивает ваши повседневные операции, а для аналитики вам нужны скорость и возможности ClickHouse, вы попали по адресу.

​

Почему стоит передавать данные из SQL Server в ClickHouse?

Если вы читаете это, то, скорее всего, уже сталкивались с этой проблемой: SQL Server отлично справляется с транзакциями, но для тяжёлых аналитических запросов в реальном времени он просто не предназначен.

Здесь и проявляются сильные стороны ClickHouse. ClickHouse создан для аналитики: он обеспечивает сверхбыструю агрегацию и построение отчётов даже на очень больших датасетах. Поэтому, если настроить стриминговый CDC-конвейер для передачи транзакционных данных в ClickHouse, можно получать молниеносно быстрые отчёты — это особенно полезно для операционных задач, продуктовых команд и клиентских панелей мониторинга.

Типичные сценарии использования:

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

​

Что нужно для начала

Прежде чем переходить к деталям, подготовьте следующее:

​

Предварительные требования

• Работающий экземпляр SQL Server

• Для этого руководства мы используем AWS RDS for SQL Server, но подойдет любой современный экземпляр SQL Server.Настройка AWS SQL Server с нуля.

• Экземпляр ClickHouse

• Самоуправляемый или облачный.Настройка ClickHouse с нуля.

• Streamkap

• Этот инструмент станет основой вашего конвейера стриминга данных.

​

Параметры подключения

Убедитесь, что у вас есть:

• адрес сервера SQL Server, порт, имя пользователя и пароль. Для доступа Streamkap к вашей базе данных SQL Server рекомендуется создать отдельные учетную запись и роль.Ознакомьтесь с нашей документацией по настройке.
• адрес сервера ClickHouse, порт, имя пользователя и пароль. IP Access List в ClickHouse определяет, какие сервисы могут подключаться к вашей базе данных ClickHouse.Следуйте инструкциям здесь.
• таблицы, которые вы хотите передавать в поток — пока начните с одной

​

Настройка SQL Server в качестве источника

Приступим!

​

Шаг 1: Создание источника SQL Server в Streamkap

Начнём с настройки подключения к источнику. Так Streamkap понимает, откуда забирать изменения.

Вот как это сделать:

1. Откройте Streamkap и перейдите в раздел источников.
2. Создайте новый источник.

• Задайте ему понятное имя (например, sqlserver-demo-source).

3. Укажите сведения о подключении к SQL Server:

• Хост (например, your-db-instance.rds.amazonaws.com)
• Порт (по умолчанию для SQL Server — 3306)
• Имя пользователя и пароль
• Имя базы данных

​

Что происходит под капотом

После настройки Streamkap подключится к вашему SQL Server и определит таблицы. Для этого примера мы выберем таблицу, в которую уже поступают данные, например события или транзакции.

​

Создание пункта назначения ClickHouse

Теперь настроим пункт назначения, в которое мы будем отправлять все эти данные.

​

Шаг 2: Добавьте пункт назначения ClickHouse в Streamkap

Как и для источника, мы создадим пункт назначения, используя сведения о подключении к ClickHouse.

​

Шаги:

1. Перейдите в раздел пунктов назначения в Streamkap.
2. Добавьте новый пункт назначения — выберите ClickHouse в качестве типа назначения.
3. Укажите данные ClickHouse:

• Хост
• Порт (по умолчанию — 9000)
• Имя пользователя и пароль
• Имя базы данных

Пример скриншота: добавление нового пункта назначения ClickHouse в панели мониторинга Streamkap.

​

Режим upsert: что это такое?

Это важный шаг: мы хотим использовать в ClickHouse режим «upsert», который под капотом использует движок ReplacingMergeTree. Это позволяет эффективно объединять входящие записи и обрабатывать обновления после приёма с помощью того, что в ClickHouse называется «слиянием частей».

• Это гарантирует, что ваша целевая таблица не будет заполняться дубликатами при изменениях на стороне SQL Server.

​

Обработка изменений схемы

У ClickHouse и SQL Server не всегда одинаковые столбцы — особенно когда приложение уже работает в продакшене, а разработчики продолжают на лету добавлять новые.

• Хорошая новость: Streamkap поддерживает базовые изменения схемы. Это значит, что если вы добавите новый столбец в SQL Server, он появится и в ClickHouse.

Просто выберите “schema evolution” в настройках пункта назначения. При необходимости это всегда можно изменить позже.

​

Создание стриминг-конвейера

Когда источник и пункт назначения настроены, пора перейти к самому интересному — к стримингу данных!

​

Шаг 3: Настройте конвейер в Streamkap

​

Настройка конвейера

1. Перейдите на вкладку Pipelines в Streamkap.

2. Создайте новый конвейер.

3. Выберите источник SQL Server (sqlserver-demo-source).

4. Выберите пункт назначения ClickHouse (clickhouse-tutorial-destination).

5. Выберите таблицу, данные из которой хотите передавать — допустим, это events.

6. Настройте CDC (фиксацию изменений данных).

• В этом примере мы будем передавать только новые данные (на первом этапе дозагрузку можно пропустить и сосредоточиться на событиях CDC).

Снимок экрана с настройками конвейера: выбор источника, пункта назначения и таблицы.

​

Нужна ли дозагрузка?

У вас может возникнуть вопрос: нужно ли дозагружать старые данные?

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

Пока просто выберите “не выполнять дозагрузку”, если у вас нет в этом конкретной необходимости.

​

Стриминг в действии: чего ожидать

Теперь ваш конвейер настроен и работает!

​

Шаг 4: Следите за потоком данных

Вот что происходит:

• Когда новые данные попадают в исходную таблицу в SQL Server, конвейер Streamkap отслеживает изменения и отправляет их в ClickHouse.
• ClickHouse (благодаря ReplacingMergeTree и слиянию частей) принимает эти строки и объединяет обновления.
• Схема успевает за изменениями — добавьте столбцы в SQL Server, и они появятся и в ClickHouse.

Панель мониторинга или журналы в реальном времени, показывающие, как растёт число строк в ClickHouse и SQL Server.

Можно буквально увидеть, как число строк в ClickHouse растёт по мере поступления данных в SQL Server.

-- Пример: подсчёт строк в ClickHouse 
SELECT COUNT(*) FROM analytics.events; |

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

​

Что происходит «под капотом»: что на самом деле делает Streamkap?

Чтобы вы лучше понимали, как это работает:

• Streamkap отслеживает бинарный лог SQL Server (тот самый лог, который используется для репликации).
• Как только в вашей таблице вставляется, обновляется или удаляется строка, Streamkap сразу фиксирует это событие.
• Он преобразует событие в формат, понятный ClickHouse, и передаёт его дальше, мгновенно применяя изменения в вашей аналитической БД.

Это не просто ETL — это полноценный CDC (фиксация изменений данных) в реальном времени.

​

Расширенные возможности

​

Режимы Upsert и Insert

Чем отличается простая вставка каждой строки (режим Insert) от режима, в котором также учитываются обновления и удаления (режим Upsert)?

• Режим Insert: Каждая новая строка добавляется — даже если это обновление, в результате появляются дубликаты.
• Режим Upsert: Обновления существующих строк перезаписывают текущие данные — такой режим гораздо лучше поддерживает актуальность и чистоту аналитики.

​

Обработка изменений схемы

Приложения меняются — и схемы тоже. С этим конвейером:

• Добавили новый столбец в рабочую таблицу? Streamkap обнаружит его и добавит на стороне ClickHouse.
• Удалили столбец? В зависимости от настроек может потребоваться миграция, но в большинстве случаев новые столбцы добавляются без проблем.

​

Практический мониторинг: как следить за конвейером

​

Проверка состояния конвейера

Streamkap предоставляет панель мониторинга, где вы можете:

• Просматривать отставание конвейера (насколько свежи ваши данные?)
• Отслеживать количество строк и пропускную способность
• Получать оповещения, если что-то идет не так

Пример панели мониторинга: график задержки, количество строк и индикаторы состояния.

​

Основные метрики, за которыми стоит следить

• Отставание: насколько ClickHouse отстаёт от SQL Server?
• Пропускная способность: число строк в секунду
• Частота ошибок: должна быть близка к нулю

​

Переходим к делу: запросы к ClickHouse

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

-- Показать топ-10 активных пользователей за последний час
SELECT user\_id, COUNT(*) AS actionsFROM analytics.eventsWHERE event\_time >= now() - INTERVAL 1 HOURGROUP BY user\_idORDER BY actions DESCLIMIT 10;

Используйте ClickHouse вместе с такими инструментами для создания панелей мониторинга, как Grafana, Superset и Redash, чтобы создавать полнофункциональные отчёты.

​

Следующие шаги и материалы для углублённого изучения

Это руководство лишь в общих чертах показывает, что можно сделать. Освоив основы, дальше можно изучить следующее:

• Настройка фильтрации потоков (синхронизация только некоторых таблиц/столбцов)
• Стриминг данных из нескольких источников в одну аналитическую БД
• Использование этого вместе с S3/озёрами данных для холодного хранения
• Автоматизация миграций схемы при изменении таблиц
• Защита вашего конвейера с помощью SSL и правил firewall

Следите за блогом Streamkap, где публикуются более подробные руководства.

​

FAQ и устранение неполадок

В: Это работает с облачными базами данных? О: Да! В этом примере мы использовали AWS RDS. Просто убедитесь, что открыты нужные порты.

В: А как насчёт производительности? О: ClickHouse очень быстрый. Узким местом обычно становится сеть или скорость binlog исходной базы данных, но в большинстве случаев задержка составляет меньше секунды.

В: Можно ли обрабатывать и удаления? О: Безусловно. В режиме upsert удаления тоже помечаются и обрабатываются в ClickHouse.

​

Подводя итоги

Итак, мы рассмотрели, как организовать потоковую передачу данных из SQL Server в ClickHouse с помощью Streamkap. Это быстро, гибко и отлично подходит для команд, которым нужна аналитика с минимальной задержкой, не перегружая рабочие базы данных.

Готовы попробовать? Перейдите на страницу регистрации и дайте нам знать, если хотите, чтобы мы осветили такие темы, как:

• Upsert и вставка: все тонкости обоих подходов
• Сквозная задержка: как быстро можно получить итоговое аналитическое представление?
• Настройка производительности и пропускная способность
• Практические панели мониторинга на основе этого стека

Спасибо, что дочитали! Удачного стриминга.