ClickHouse - мощная колоночная СУБД для аналитических запросов. Power BI - ведущий инструмент визуализации и анализа данных. Если их совместить, можно прокачать отчётность и повысить скорость обработки данных. Но есть одна проблема:
В Power BI нет встроенного коннектора для ClickHouse как для того же PostgreSQL или MySQL. В Excel его кстати тоже нет. Что же с этим делать? Разберём 3 варианта: стандартный с ODBC, продвинутый с Direct Query и прямой - без ODBC и шлюзов.
Сразу скажу про шлюзы в Power BI - их можно использовать, в этом нет ничего плохого. Но они периодически срывают обновление и требуют постоянно включённый комп/сервер. Без них удобнее - проще передать отчёт коллеге, проще обновлять, проще не платить за сервер, проще выключить комп и уйти домой. В конце мы разберём как обойтись без шлюза, но начнём с классики.
ClickHouse + ODBC + Power BI
Стандартное решение из интернета предлагает нам использовать ODBC-драйвер (ссылка на официальный гитхаб). ODBC-драйвер ставится на ваш компьютер, а значит вам точно потребуется шлюз для подключения к Clickhouse, что снижает удобство метода. Зато это надёжный дедовский классический метод подключения к БД.
Анонсы всех видео, статей и полезностей - в нашем Telegram-канале🔥
Присоединяйтесь, обсуждайте и автоматизируйте!
Прокручиваю страницу гитхаба до ссылок и скачиваю 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? Если вы не привязали домен или обращаетесь через ODBC или хотите работать с Direct Query, то ВАМ НУЖЕН POWER BI GATEWAY. Единственный вариант его исключить - работать с кликхаусом на домене напрямую без всяких ODBC или Direct Query.
В Excel с кликхаусом можно работать как через ODBC, так и напрямую, домен при этом не нужен.
ВАЖНО: не отправляйте никому pbix или xlsx файлы где в запросах Power Query указаны логин и пароль. Если вы хотите работать без шлюза, обеспечьте безопасность файлов ваших отчётов и вашей БД. Хорошая практика - делать под каждого клиента свои доступы, чтобы при отправке файла он получил доступ только к своим отчётам. Ну и конечно выставляйте ограничения подключений к серверу по IP.
Соединив мощь ClickHouse и гибкость Power BI, вы сможете сильно увеличить скорость обработки и объёмы данных + повысить качество и управляемость визуализаций. А с режимом Direct Query у вас в принципе нет ограничений по объёмам - только ограничения мощности сервера ClickHouse, но это тема другой статьи😊
Комментарии