ClickHouse - мощная колоночная СУБД для аналитических запросов. Power BI - ведущий инструмент визуализации и анализа данных. Если их совместить, можно прокачать отчётность и повысить скорость обработки данных. Но есть одна проблема:
В Power BI нет встроенного коннектора для ClickHouse как для того же PostgreSQL или MySQL. В Excel его кстати тоже нет. Что же с этим делать? Разберём 3 варианта: стандартный с ODBC, продвинутый с Direct Query и прямой - без ODBC и шлюзов.
Сразу скажу про шлюзы в Power BI - их можно использовать, в этом нет ничего плохого. Но они периодически срывают обновление и требуют постоянно включённый комп/сервер. Без них удобнее - проще передать отчёт коллеге, проще обновлять, проще не платить за сервер, проще выключить комп и уйти домой. В конце мы разберём как обойтись без шлюза, но начнём с классики.
ClickHouse и Power BI: какой коннектор выбрать
Для начала короткое сравнение трёх способов подключить ClickHouse к Power BI. Это даст понимание вариантов и возможных задач.
1. ODBC + ClickHouse Power BI: классика, работает везде, но требует установки драйвера на каждой машине и шлюза для облака. Подходит когда нужен надёжный enterprise-сценарий.
2. Коннектор с Direct Query: кастомный clickhouse power bi connector в формате mez. Не тянет данные локально - запросы летят в ClickHouse напрямую. Подходит когда базы большие и хочется работать с актуальными данными.
3. Прямое подключение без ODBC: идём в ClickHouse через HTTP-интерфейс из Power Query. Данные приходят в Power BI и Excel без шлюза. Подходит когда не хочется городить инфраструктуру.
Данные ClickHouse можно получить любым из способов, разница в удобстве и стоимости поддержки. Но давайте к деталям.
ClickHouse + ODBC + Power BI
Стандартное решение из интернета предлагает нам использовать ODBC-драйвер (ссылка на официальный гитхаб). ODBC-драйвер ставится на ваш компьютер, а значит вам точно потребуется шлюз для подключения к Clickhouse, что снижает удобство метода. Зато это надёжный дедовский классический метод подключения к БД.
[TGBLOCK]
Прокручиваю страницу гитхаба до ссылок и скачиваю ODBC, мне нужен для Windows:
Распаковываю и запускаю первый файл:
После нехитрой установки нахожу в поиске Windows администратора ODBC, открывается окно:
Жму "Добавить" и добавляю Unicode версию ODBC ClickHouse:
Заполняю параметры подключения к БД:
После чего создаётся источник данных, который я могу использовать в Power BI или Excel. Для примера попробуем вызов из Excel:
Указываю SQL запрос:
Еще раз ввожу логин-пароль:
И таблица успешно получена:
Я показал на примере Excel, в Power BI это работает точно также. Разве что Excel не требует никаких шлюзов для обновления:)
Продвинутый вариант с Direct Query
В 2023-м году коллеги решили выпустить настоящий коннектор к ClickHouse для Power BI. Вот его репозиторий на Github. Получилось круто, но выполнен он в знакомом вам формате mez. Есть похожий вариант, не знаю чем он отличается, но формат тот же.
Чем плох mez вы уже знаете - он требует шлюз, потому что mez-файл лежит на вашем компьютере. Работает он в паре с ODBC, так что является продвинутой версией первого варианта.
Что нового даёт такой коннектор? Теперь можно подключаться к ClickHouse в режиме Direct Query! То есть не импортировать все данные себе в Power BI, а использовать Power BI как чисто визуальную систему. Данные остаются в ClickHouse, а Power BI будет каждый раз обращаться к ним для построения визуализаций. Как вы поняли, коннектор сделан под Power BI и для Excel бесполезен.
Переходим в релизы и качаем файл mez в папку с коннекторами Power BI. Если вы уже ставили коннекторы, вы знаете где эта папка. Если нет - посмотрите инструкцию в любом из моих коннекторов. Происходит настоящая магия:
В очередной раз ввожу параметры подключения, пользователя и пароль:
Подключаюсь к таблицам в режиме Direct Query:
Подключение прошло успешно:
ClickHouse по ссылке без шлюза
Обычно ClickHouse располагается на сервере, ХОСТ:ПОРТ которого выглядят примерно так - 185.71.67.69:8123. Но можно купить домен и привязать его к серверу, тогда ClickHouse будет доступен под конкретным доменом.
Подключение к обычному серверу ClickHouse не будет обновляться без шлюза. Но с привязанным доменом это станет обращением к сайту, а данные с сайтов тянутся без шлюза. Но остаётся еще ODBC, размещенный на вашем компьютере. Чтобы избавиться от шлюза совсем, нужно исключить ODBC, то есть обратиться к ClickHouse напрямую. Это исключает работу Direct Query, но он нужен далеко не всем.
Не стоит забывать и про безопасность. Размещённый на домене ClickHouse может стать жертвой мошенников, подобравших к нему пароль. Так что в настройках сервера стоит сразу проставить перечень допустимых IP (не забыв сервера майкрософта).
Ниже привожу код для прямого подключения к ClickHouse без использования ODBC и коннекторов:
Table.PromoteHeaders(Csv.Document(
Web.Contents("XXXXX.ru:8123", [Timeout=#duration(0,0,30,0), Query = [
user = "XXX",
password = "123123123",
query = " SELECT * FROM xxx.xxxxxxxxx " & " FORMAT CSVWithNames "
]])))Если вы замените домен, юзера, пароль и SQL-запрос на свои значения, получится обращение к ClickHouse, которое обновляется без шлюза. Можно смело вставлять этот код в расширенный редактор вашего запроса.
Не рекомендую удалять отсюда порт - без него тоже всё работает, но только с небольшими данными, потом начинаются проблемы. Так что порт указываем через двоеточие после домена. "FORMAT CSVWithNames" тоже не удаляем, это часть SQL запроса, определяющая формат ответа.
В Excel последний способ работает даже без домена, так что если тянете данные в Excel, можно вообще не покупать домен и не ставить ODBC. Только не отправляйте клиентам файл с таким запросом. Помните, что в коде Power Query у вас указаны все координаты БД включая логин и пароль.
Какие данные ClickHouse можно тянуть в Power BI
Данные из ClickHouse в Power BI идут как из обычной SQL-базы - вы пишете SELECT и получаете таблицу, так что сами данные могут быть любыми. Несколько типичных сценариев, которые встречаются чаще всего:
Логи и события - миллиарды строк, агрегации по времени. ClickHouse заточен под это, Power BI визуализирует. Связка стандарт для observability-дашбордов.
Маркетинговая аналитика - кликстрим, события, конверсии. ClickHouse тянет сырые события, Power BI собирает воронки и когорты.
Финансовая аналитика - транзакции, платежи, агрегации по периодам. Данные ClickHouse сжаты в десятки раз, Power BI быстро строит отчёты.
IoT и телеметрия - метрики устройств, временные ряды. ClickHouse держит миллионы записей в секунду, Power BI рисует дашборды.
Главное правило: при подключении ClickHouse к Power BI старайтесь не тянуть всё подряд. Делайте предварительную агрегацию SQL-запросом и грузите уже подготовленные данные ClickHouse в модель. Иначе размер pbix-файла вырастет до десятка гигабайт🙀
Типичные ошибки при подключении ClickHouse к Power BI
Вот частые проблемы при работе связки clickhouse - power bi:
1. "Cannot connect to ClickHouse server" при первом подключении. Чаще всего дело в SSL: для ClickHouse Cloud нужен SSLMode=require, для self-hosted - часто none. Проверьте порт (8123 HTTP, 8443 HTTPS).
2. Пустой ответ от ClickHouse. Скорее всего в URL остался протокол http:// или https:// - его быть не должно, только хост. Это классическая ошибка коннектора.
3. Долгое обновление Power BI при подключении ClickHouse через ODBC. ODBC поддерживает только Import-режим, всё тянется в pbix. Решение - перейти на коннектор с Direct Query или предагрегацию через materialized views в ClickHouse.
4. Шлюз ругается на коннектор в Power BI Service. Custom-коннекторы требуют отдельной настройки on-premises gateway. Если шлюза нет - используйте вариант 3 (прямое подключение).
5. Логин и пароль в Power Query. При прямом подключении кредлы видны в M-коде. Не делитесь pbix-файлами с прямыми коннектами, либо используйте read-only пользователя ClickHouse.
Заключение
Что можно сказать по связке ClickHouse + Power BI? Если вы не привязали домен или обращаетесь через ODBC или хотите работать с Direct Query, то ВАМ НУЖЕН POWER BI GATEWAY. Единственный вариант его исключить - работать с кликхаусом на домене напрямую без всяких ODBC или Direct Query.
В Excel с кликхаусом можно работать как через ODBC, так и напрямую, домен при этом не нужен.
ВАЖНО: не отправляйте никому pbix или xlsx файлы где в запросах Power Query указаны логин и пароль. Если вы хотите работать без шлюза, обеспечьте безопасность файлов ваших отчётов и вашей БД. Хорошая практика - делать под каждого клиента свои доступы, чтобы при отправке файла он получил доступ только к своим отчётам. Ну и конечно выставляйте ограничения подключений к серверу по IP.
Соединив мощь ClickHouse и гибкость Power BI, вы сможете сильно увеличить скорость обработки и объёмы данных + повысить качество и управляемость визуализаций. А с режимом Direct Query у вас в принципе нет ограничений по объёмам - только ограничения мощности сервера ClickHouse, но это тема другой статьи😊
