Power BI

Microsoft Power BI может выполнять запросы к данным из ClickHouse Cloud или загружать данные в память из самоуправляемого развертывания.

Существует несколько вариантов Power BI, которые можно использовать для визуализации данных:

• Power BI Desktop: настольное приложение для Windows для создания панелей мониторинга и визуализаций
• Power BI Service: доступный в Azure SaaS-сервис для размещения панелей мониторинга, созданных в Power BI Desktop

В Power BI панели мониторинга создаются в версии Desktop, а затем публикуются в Power BI Service.

В этом руководстве вы узнаете, как:

• установить ODBC-драйвер ClickHouse
• установить коннектор ClickHouse Power BI в Power BI Desktop
• выполнять запросы к данным из ClickHouse для визуализации в Power BI Desktop
• настроить локальный шлюз данных для Power BI Service

В этом руководстве предполагается, что на вашем компьютере с Windows уже установлен Microsoft Power BI Desktop. Скачать и установить Power BI Desktop можно здесь

Мы рекомендуем обновить Power BI до последней версии. Коннектор ClickHouse по умолчанию доступен начиная с версии 2.137.751.0.

Для подключения к вашему экземпляру ClickHouse потребуются следующие сведения:

• Имя хоста — ClickHouse
• Имя пользователя — имя пользователя из учетных данных
• Пароль — пароль пользователя
• База данных — имя базы данных в экземпляре, к которой вы хотите подключиться

Чтобы начать работать с запросами к данным в Power BI Desktop, выполните следующие шаги:

1. Установите ODBC-драйвер ClickHouse
2. Найдите коннектор ClickHouse
3. Подключитесь к ClickHouse
4. Выполняйте запросы и визуализируйте данные

Скачайте последний релиз ClickHouse ODBC.

Запустите скачанный установщик .msi и следуйте указаниям мастера установки.

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

Найдите ODBC в меню «Пуск» и выберите "ODBC Data Sources (64-bit)".

Убедитесь, что ClickHouse Driver присутствует в списке.

На стартовом экране Power BI Desktop нажмите "Get Data".

Введите "ClickHouse" в строке поиска

Выберите коннектор и введите учетные данные экземпляра ClickHouse:

• Host (обязательно) — Домен/адрес вашего экземпляра. Убедитесь, что он указан без префиксов и суффиксов.
• Port (обязательно) — Порт вашего экземпляра.
• Database — Имя вашей базы данных.
• Options — Любой параметр ODBC из списка на странице ClickHouse ODBC в GitHub
• Data Connectivity mode — DirectQuery

• Укажите имя пользователя и пароль

Наконец, в представлении Navigator должны отображаться базы данных и таблицы. Выберите нужную таблицу и нажмите "Load", чтобы импортировать данные из ClickHouse.

После завершения импорта данные ClickHouse будут доступны в Power BI как обычно.

Чтобы использовать Microsoft Power BI Service, необходимо создать локальный шлюз данных.

Подробнее о настройке Custom Connectors см. в документации Microsoft: использование пользовательских коннекторов данных с локальным шлюзом данных.

Мы рекомендуем использовать коннектор ClickHouse с DirectQuery.

Установите драйвер ODBC на экземпляр локального шлюза данных и проверьте его, как описано выше.

После завершения установки драйвера можно создать источник данных ODBC. Найдите ODBC в меню «Пуск» и выберите "ODBC Data Sources (64-bit)".

Здесь нужно добавить новый User DSN. Нажмите кнопку "Add" слева.

Выберите версию ODBC-драйвера с поддержкой Unicode.

Заполните сведения о подключении.

Если у вас еще не установлен Power BI, скачайте и установите Power BI Desktop.

На стартовом экране Power BI Desktop нажмите "Get Data".

Выберите "Other" -> "ODBC".

Выберите из списка ранее созданный источник данных.

В итоге вы должны увидеть базы данных и таблицы в представлении Navigator. Выберите нужную таблицу и нажмите "Load", чтобы импортировать данные из ClickHouse.

После завершения импорта данные ClickHouse будут доступны в Power BI, как обычно.

PowerBI рассчитан на традиционные построчные базы данных с умеренными объёмами данных. При работе с ClickHouse в большом масштабе (миллиарды строк) для достижения оптимальной производительности требуются определённые архитектурные подходы.

PowerBI автоматически генерирует SQL-запросы с вложенными подзапросами, сложными JOIN и преобразованиями на лету. Такие конструкции хорошо работают в традиционных SQL-базах данных, но могут быть неэффективны при запросах к крупным колоночным базам данных, таким как ClickHouse.

Рекомендуемый подход для больших наборов данных: вместо прямых запросов к исходным таблицам создавайте в ClickHouse отдельные materialized view для каждой визуализации на панели мониторинга. Это даёт:

• Стабильно высокую производительность независимо от объёма данных
• Меньшую нагрузку на ваш кластер ClickHouse
• Более предсказуемые затраты

Создайте materialized view для нескольких уровней агрегации:

• Почасовые агрегации для детализированных панелей мониторинга за недавний период
• Ежедневные агрегации для исторических тенденций
• Ежемесячные rollup-агрегации для долгосрочной отчетности
• Храните сырые данные с подходящим TTL для разового анализа по запросу

• Определяйте ключи ORDER BY в соответствии с шаблонами запросов
• Используйте партиционирование для временных рядов
• Преобразуйте небольшие таблицы-измерения в словари для эффективного поиска по ключу
• Используйте проекции для дополнительной оптимизации запросов

Беззнаковые целочисленные типы, такие как UInt64 и выше, не загружаются в набор данных автоматически, поскольку Int64 — это максимальный целочисленный тип, поддерживаемый Power BI.

В этом примере в таблице pageviews есть столбец UInt64, который по умолчанию распознаётся как "Binary". "Transform Data" открывает Power Query Editor, где можно переназначить тип столбца, задав, например, Text.

После этого нажмите "Close & Apply" в левом верхнем углу и продолжите загрузку данных.