Подключение к базе данных ClickHouse с помощью DBeaver

После того, как вы создали свой кластер ClickHouse в Yandex Cloud и загрузили в него данные, вы можете использовать специальную программу для работы с базой данных посредством SQL-команд.

ClickHouse поддерживает множество интерфейсов, в том числе основные - HTTP (REST API), TCP, gRPC, а также MySQL и PostgreSQL. В этом руководстве я предлагаю использовать клиент, поддерживающий подключение к базе данных ClickHouse - DBeaver.

Установка DBeaver

DBeaver - это бесплатный инструмент для работы с базами данных, который поддерживает большое количество СУБД, включая MySQL, PostgreSQL, Oracle, SQL Server, ClickHouse и многие другие. Его используют программисты и аналитики в сферах мобильной и веб-разработки, администрирования баз данных и бизнес-аналитики.

Вы можете использовать DBeaver:

• для проверки загружаемых таблиц, например, после настройки интеграции Яндекс.Метрики с Yandex Cloud, или когда выполняете выгрузку статистику по API из Яндекс.Директа в ClickHouse с последующей визуализацией данных в Yandex DataLens;
• для просмотра текущих таблиц, уже загруженных в ClickHouse;
• для создания новых баз данных, изменения или удаления текущих таблиц;
• для выполнения SQL-запросов.

DBeaver доступен в нескольких версиях, как в платной (Enterprise Edition), так и в бесплатной (DBeaver Community). Мы будем использовать DBeaver Community. Чтобы установить программу, перейдите по ссылке и скачайте DBeaver Comminity.

DBeaver можно установить на Windows, macOS и Linux. В зависимости от того, какую операционную систему (ОС) вы используете, загрузите нужный инсталлятор:

Поскольку в своей работе я использую Windows, то скачивание и установку DBeaver буду производить именно для этой ОС. Нажав на Windows (installer), начнется скачивание последней версии программы. После загрузки на компьютер запустите установщик. Выберите язык:

Затем Далее:

Примите условия лицензионного соглашения:

На этапе выбора пользователей лучше изменить на всех пользователей:

Выберите все компоненты устанавливаемой программы:

Выберите папку для установки программы (можно оставить по умолчанию). Примерное требуемое место на диске для установки программы - 150 Мбайт:

На завершающем шаге нажмите Установить:

Программа начнет устанавливается. Дождавшись ее окончания, вы можете создать ярлык на рабочем столе. Для этого поставьте галочку Create Desktop Shortcut:

Нажмите Готово. На этом установка DBeaver Community завершена.

Вышеприведенные шаги установки программы подойдут для установки DBeaver на macOS. На Linux клиент устанавливается через командную строку. Инструкция по установке размещена на официальном сайте в разделе для Linux.

Подключение к базе данных в кластере ClickHouse

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

Это можно сделать двумя способами:

1. с помощью виртуальной машины, созданной в пространстве Яндекс.Облака;
2. из интернета при получении SSL-сертификата (сертификата безопасности).

Поскольку мы будем использовать DBeaver Community на своем компьютере локально, то я буду рассматривать второй способ - подключение из интернета с получением SSL-сертификата.

Создание сети

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

Для этого перейдите в консоль управления своего проекта Yandex Cloud по ссылке и выберите нужный каталог. В разделе Все сервисы нажмите на Virtual Private Cloud:

Если до этого у вас не было облачной сети, создайте ее:

Yandex Virtual Private Cloud позволяет создавать облачные сети и подсети, которые используются для передачи информации между облачными ресурсами и связи ресурсов с интернетом.

Облачная сеть - это аналог традиционной локальной сети в дата-центре, а подсеть — это диапазон IP-адресов в облачной сети. Трафик может передаваться между подсетями одной сети, но не может передаваться между подсетями разных сетей.

Задайте имя сети, например, network1. В разделе Дополнительно оставьте галочку Создать подсети и нажмите Создать сеть:

Ваша сеть и несколько подсетей будут созданы.

Группа безопасности

Теперь необходимо настроить группы безопасности, чтобы был разрешен трафик с определенных портов и IP-адресов. В левом навигационном меню сервиса Yandex Virtual Private Cloud нажмите на раздел Группа безопасности:

Создайте вашу группу безопасности:

На открывшейся странице задайте имя группы (например: firstgroup) и Сеть, выбрав ее из выпадающего списка:

Теперь вам необходимо добавить правила для входящего трафика. Переключитесь на вкладку Входящий трафик и нажмите кнопку Добавить правило:

В описание введите произвольный текст (например: Порт 8443) и следующие настройки:

• Описание – Порт 8443
• Диапазон портов – 8443
• Протокол – TCP
• Источник – CIDR
• CIDR блоки – 0.0.0.0, а после / 0

В интерфейсе Yandex Cloud правило для входящего трафика (порт 8443) будет выглядеть так:

Сохраните правило. В общем списке вы увидите его на вкладке входящего трафика:

Необходимо создать еще одно правило, только для порта 9440. Нажмите кнопку Добавить правило и добавьте все те же самые настройки, что и для порта 8443, за исключением Диапазон портов. Там выставите значение 9440.

• Описание – Порт 9440
• Диапазон портов – 9440
• Протокол – TCP
• Источник – CIDR
• CIDR блоки – 0.0.0.0, а после / 0

В интерфейсе Yandex Cloud правило для входящего трафика (порт 9440) будет выглядеть так:

Сохраните правило. В результате вы должны получить два новых правила для входящего трафика:

Сохранить вашу группу безопасности с помощью кнопки Сохранить в самом низу страницы:

После этого вас перенаправит на страницу со всеми созданными группами безопасности:

Получение SSL-сертификата

Также для взаимодействия с кластером ClickHouse вам необходимо получить SSL-сертификат безопасности. Для этого воспользуйтесь стандартным командным интерпретатором Windows PowerShell.

PowerShell – это кроссплатформенное решение для автоматизации задач, которое включает оболочку командной строки, скриптовый язык и платформу управления конфигурацией. PowerShell поддерживается в Windows, Linux и macOS. Он позволяет:

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

Откройте меню Пуск на своем компьютере и начните вводить powershell. В появившихся результатах нажмите на приложение Windows PowerShell (для Windows):

Откроется окно командной строки. Скопируйте нижеприведенный текст для Windows в PowerShell и нажмите Enter:

Для Linux (Bash) и macOS (Zsh) этот код:

Этот код еще можно скопировать из официальной документации Yandex Cloud.

В программе это будет выглядеть так:

После нажатия Enter высветиться окно с подтверждением установки сертификата. Нажмите Да:

После этого окно можно закрыть.

Вы можете проверить, действительно ли сертификат YandexCA.crt был установлен на ваш компьютер. Поскольку при установке вы указали путь вида $HOME\.clickhouse\YandexCA.crt, то он должен находиться именно в этой директории.

Под $HOME, как правило, подразумевается домашний каталог ''C:\Users\Имя пользователя''

Для простоты воспользуйтесь поиском на компьютере, введя в строке YandexCA.crt. Вы должны увидеть результат поиска. Нажмите на сертификат правой кнопкой мыши и выберите Открыть папку с файлом:

На вашем компьютере откроется отдельная папка с установленным сертификатом YandexCA.crt. Запомните полный путь к этому файлу. В дальнейшем он вам пригодится при подключении к базе данных ClickHouse в DBeaver.

Публичный доступ к кластеру ClickHouse

Подключаться к DBeaver можно только к хостам кластера в публичном доступе с использованием SSL-сертификата. Его вы получили на предыдущем шаге.

Теперь необходимо убедиться, что ваш кластер ClickHouse имеет публичный доступ. Для этого перейдите в консоль управления Yandex Cloud, выберите нужный каталог и свой кластер Managed Service for ClickHouse:

В списке кластеров ClickHouse нажмите на сам кластер, к которому вы будете подключаться в DBeaver:

В новом окне перейдите на вкладку Хосты:

В строке вашего хоста нажмите на иконку с тремя точками и Редактировать:

Для доступа к вашему кластеру из интернета ОБЯЗАТЕЛЬНО должна стоять галочка Публичный доступ:

Если ее нет, то поставьте и нажмите Сохранить. Данная настройка незначительно увеличит стоимость аренды кластера (на ~170 руб./месяц), но она необходима.

Теперь вы можете переходить к подключению к вашей базе данных ClickHouse с помощью DBeaver.

Подключение к ClickHouse в DBeaver

DBeaver подключается к ClickHouse с помощью JDBC через HTTP(S). Для этого вам нужно:

• конечная точка
• номер порта
• имя пользователя
• пароль

Запустите DBeaver Community. Откройте меню Базы данных - Новое соединение:

В открывшемся окне выберите тип нового соединения ClickHouse:

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

• ClickHouse - для версий 21.3+
• ClickHouse (Legacy) - для версий < 21.3

Проверить свою версию кластера ClickHouse можно в сервисе Managed Service for ClickHouse в созданном проекте Yandex Cloud. Напротив вашего кластера в столбце Версия будет указана текущая версия базы данных:

Если она выше 21.3, то выбирайте тип соединения ClickHouse, ниже - ClickHouse (Legacy). Нажмите Далее:

На следующем шаге вам необходимо добавить настройки базового соединения. Укажите параметры подключения на вкладке Главное:

• Хост
• Порт - 8443
• БД/Схема — имя вашей базы данных для подключения
• в блоке Аутентификация укажите имя и пароль пользователя БД

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

На вкладке Shell прокрутите вниз до примера строки подключения. Там в строке clickhouse-client --host будет отображаться ваш хост, который вам нужно скопировать и вставить в DBeaver:

Таким образом, настройки на вкладке Главное будут выглядеть так:

Перейдите на вкладку Свойства драйвера и укажите значение:

• ssl - true
• sslrootcert - <путь к сохраненному файлу SSL-сертификата на вашем компьютере>

Для проверки подключения нажмите кнопку Тест соединения...

Если соединение будет установлено, то вы увидите соответствующее уведомление:

Если DBeaver обнаружит, что у вас не установлены драйвера ClickHouse, он предложит вам их загрузить. Сделайте это, а затем повторно проверьте соединение.

Если все хорошо и соединение установлено, нажмите кнопку Готово, чтобы сохранить настройки соединения с БД:

После этого в списке подключений отобразится созданное соединение к вашей базе ClickHouse:

Нажимая поочередно на иконку раскрытия, вы можете провалиться внутрь вашего кластера в конкретную базу данных и посмотреть какие таблицы с каким набором полей и какого объема находятся внутри:

Поскольку я подключился к кластеру ClickHouse, который предназначался для интеграции Яндекс.Метрики c Yandex Cloud, в базе данных у меня хранится две таблицы:

• с префиксом hits_ для хитов;
• с префиксом visits_ для визитов.

Кликнув два раза на уровне Таблицы, справа в окне откроются сведения о текущих таблицах, их название, схема, размер таблицы, количество строк и так далее:

А нажав на конкретную таблицу, можно увидеть подробные данные о ней:

Увидеть как свойства таблицы (схему данных с названиями полей), так и ее данные

Ваш первый SQL-запрос

Чтобы выполнить свой первый SQL-запрос в DBeaver, правой кнопкой мыши нажмите на базу данных или само подключение, и выберите Редактор SQL - Новый редактор SQL:

В открывшемся окне вы можете написать SQL-запрос. В качестве примера произведем расчет посещаемости по визитам за выбранный диапазон дат для данных Яндекс.Метрики. Для этого скопируйте запрос и вставьте его в редактор:

, где вместо:

• yandex_data_transfer_test.visits_dttql4la13mb206q472r вставьте свою базу данных и таблицу визитов;
• toDate('2023-01-31') - измените дату начала на свою;
• toDate('2023-02-06') - измените дату окончания на свою;
• CounterID = 24226447 - измените номер счетчика Яндекс.Метрики на свой;

Для моего счетчика Яндекс.Метрики (osipenkov.ru) и выбранного диапазона дат (с 5 по 12 августа 2023 года) SQL-запрос будет выглядеть так:

Чтобы выполнить команду, нажмите на оранжевую иконку или сочетание клавиш Ctrl+Enter:

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

При желании, вы можете сравнить статистику сырых данных Яндекс.Метрики в ClickHouse с данными в интерфейсе вашего счетчика.

Подробнее о том, как работать с неагрегированными данными Яндекс.Метрики, загруженными в ClickHouse, рассмотрено в этой статье.