Power BI и Excel как ETL-системы: загрузка данных в Clickhouse и PostgreSQL

Итак, вы ведёте проект с большим количеством данных. Возможно магазин на Wildberries с тысячами товаров или рекламная кампания в соцсетях с тысячами объявлений. Каждый день по вашим объявлениям или товарам копится статистика и данных становится всё больше. 

Вы подключаетесь к сервису по API, забираете данные и начинаете строить отчёты в Power BI. Но API просто не предназначено для таких объёмов и всё работает крайне медленно, а то и вообще не работает. Так что вы начинаете задумываться о собственном хранилище данных.

Проблемы хранилища данных

Тут всплывает сразу несколько вопросов. Первый - выбор СУБД (системы управления базой данных). Можно развернуть базу на классическом PostgreSQL или выбрать быстрый Clickhouse. Процесс разворачивания базы не так сложен и делается по инструкции. Но есть более сложные вопросы: 

1. Как загружать данные в БД? Где взять коннекторы?
2. Как мониторить процесс загрузки данных?
3. Как перетянуть данные если они загрузились неверно?
4. Как вообще всем этим управлять когда таблиц много?
5. Как сделать систему гибкой, чтобы решать нестандартные задачи?
6. Как не отдать свои данные сторонним сервисам, которые могут ими воспользоваться?

Кажется, пора нанимать Python-разработчика и писать скрипты для загрузки данных в базу. Также, нужно внедрить систему управления загрузкой, например Airflow. Можно уже закладывать 150-200 тысяч рублей на зарплату подобного специалиста, а это немало, даже включая налоги. 

Но что, если я скажу вам, что всё это можно сделать абсолютно бесплатно и даже сохранив свои данные в безопасности? Ведь у вас уже есть прекрасный ETL-инструмент для загрузки данных в базу и это Power BI!

А вы точно правильно используете Power BI?

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

Допустим, у вас бесплатный аккаунт Power BI и вы не хотите покупать Pro. У вас есть развёрнутая база данных, куда вы хотите ежедневно загружать свежие данные. Также есть API сервиса (wildberries, VK или другой сервис - не так важно), который без проблем отдаёт вам 7 последних дней. Что же мы можем с этим сделать?

1. Создаём в отчёте Power BI запрос, который будет забирать 7 последних дней по API.
2. Добавляем к этому запросу удаление данных в вашей базе за последние 7 дней.
3. Добавляем к этому запросу функцию загрузки свежих данных в вашу БД.
4. Выгружаем отчёт в Power BI Service и ставим на обновление каждое утро.
5. Заходим в БД и проверяем, что данные успешно пересбораны.
6. Иногда заходим в сам отчёт Power BI, чтобы он не отключался из-за неактивности пользователей.

Логичные вопросы - будет ли обновляться отчёт и не будут ли дублироваться данные в БД? Да, обновление работает, а данные дублируются только в процессе тестирования. Но тестовые загрузки легко можно удалить вручную. Да, Power BI слегка неуправляем в отправке запросов, так что на этапе тестов в Power BI Desktop вы обязательно поймаете дубли в данных, но зато потом всё будет грузиться правильно. 

Из плюсов - Power BI может делать и GET, и POST-запросы если нужно. Ну а касаемо SQL - поддерживаются не только SELECT-запросы. INSERT или DELETE тоже прекрасно работают. Так что из Power BI можно сделать неплохой ETL, тем более что вся инфраструктура уже готова.

Какие тут есть возможности: 

• можно делать по несколько запросов в отчёте, группируя их по любым признакам; 
• можно создавать несколько отчётов для загрузки таблиц разных типов;
• можно разбивать всё по рабочим областям; 
• задавать разное время обновления чтобы не нагружать базу;
• можно параметризовать даты пересбора данных;
• можно вставлять скрипты загрузки в потоки данных если купить power bi pro;
• и много чего еще...

Power BI Service - уже готовая платформа с довольно простым интерфейсом, так что разобраться в ней не так сложно, а возможности невероятны.

Анонсы всех видео, статей и полезностей - в нашем Telegram-канале🔥
Присоединяйтесь, обсуждайте и автоматизируйте!

Для централизации лучше создавать отдельный PBIX-отчёт на каждый источник (сервис). В этом отчёте будет единая функция подключения к источнику, которую удобно менять в случае изменений API. Если вы захотите создать много таблиц по одному источнику, можно добавлять их в этот отчёт, выставляя свою задержку на каждый запрос чтобы исключить одновременное обновление данных.

Пробуем загрузить данные в базу

Я тестировал и на PostgreSQL и на Clickhouse, поэтому буду показывать на примере этих СУБД. Функцию, которая загружает таблицу в БД, я предоставлю в этой статье. Функцию, которая удаляет данные за прошлые даты, а затем загружает свежие данные можно скачать на Boosty или написать самостоятельно (имея основной код фукнции это не так сложно).

Чтобы всё работало корректно, пойдём по простому пути: подготовим в базе данных таблицу из нужных столбцов, затем получим аналогичную таблицу из API обычным коннектором на Power Query. В конце применим функцию загрузки в базу к полученной таблице, чтобы прогрузить данные в БД. 

Подготовим таблицу для вставки данных в Clickhouse (движок MergeTree): 

Извлекаем данные из Яндекс.Метрики с помощью стандартного коннектора. Эту статистику мы и будем заливать в БД:

Заметьте, что порядок и типы столбцов в Power Query полностью аналогичны кликхаусу, иначе вставка не работает. Названия столбцов кликхауса и power query могут различаться - это ни на что не влияет.

Ниже приведена наша функция, можно добавить её отдельным запросом в Power Query, назовём его "insert_clickhouse". Не забудьте заменить ВАШ ХОСТ ИЛИ ДОМЕН CLICKHOUSE:ПОРТ на значения реального хоста/домена и порта. К сожалению, мы не можем вынести это в параметры функции, так что придётся зайти и вписать их вручную в расширенный редактор:

(pq_table,  ch_user as text, ch_pass as text, ch_table as text)=>
let
dateFunc = (dat)=>
       if dat = "yesterday" then Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()) , -1),"yyyy-MM-dd") 
       else if dat = "today" then Date.ToText(Date.From(DateTime.LocalNow()),"yyyy-MM-dd")
       else if (try Number.From(Text.BeforeDelimiter(dat,"daysAgo")) otherwise 0) >0 then Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-1*Number.From(Text.BeforeDelimiter(dat,"daysAgo"))),"yyyy-MM-dd") 
       else dat,

added = Table.AddColumn( pq_table,  "all", each  
"(" & Text.Combine( List.Transform( Record.ToList(_), each 
if Value.Is(_, type text) then "'"&_&"'" 
else if Value.Is(_, type date) then "'"&Date.ToText(_,"yyyy-MM-dd")&"'"
else Text.From(_) 
),",") & ")" 
),
textview = Text.Combine(added[all],","),
   
insert =  Csv.Document(Web.Contents("ВАШ ХОСТ ИЛИ ДОМЕН CLICKHOUSE:ПОРТ" , [
      Query=[user = ch_user, password = ch_pass, query = "INSERT INTO " & ch_table  &" VALUES " & textview ], 
       Content = Binary.FromText("") ,ManualStatusHandling={429,400,500}]), 1,"",ExtraValues.Ignore,65001)
in insert

Затем добавляем новый шаг к запросу Метрики, вызывая нашу функцию:

insert_clickhouse(#"Последний шаг таблицы Метрики", "ПОЛЬЗОВАТЕЛЬ БАЗЫ ДАННЫХ", "ПАРОЛЬ ПОЛЬЗОВАТЕЛЯ", "СХЕМА.ТАБЛИЦА")

В clickhouse появляются данные:

Теперь попробуем с PostgrSQL. Ниже такая же таблица Метрики, но с другим порядком столбцов:

Выставляем аналогичный порядок столбцов в Power Query:

Добавляем функцию "insert_postgresql" для PostgreSQL (не забывая менять "ВАШ ХОСТ" и "НАЗВАНИЕ БД" на свои):

(pq_table,  postgresql_table as text)=>
let
dateFunc = (dat)=>
       if dat = "yesterday" then Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()) , -1),"yyyy-MM-dd") 
       else if dat = "today" then Date.ToText(Date.From(DateTime.LocalNow()),"yyyy-MM-dd")
       else if (try Number.From(Text.BeforeDelimiter(dat,"daysAgo")) otherwise 0) >0 then Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-1*Number.From(Text.BeforeDelimiter(dat,"daysAgo"))),"yyyy-MM-dd") 
       else dat,
added = Table.AddColumn( pq_table, "all", each  
"(" & Text.Combine( List.Transform( Record.ToList(_), each 
if Value.Is(_, type text) then "'"&_&"'" 
else if Value.Is(_, type date) then "'"&Date.ToText(_,"yyyy-MM-dd")&"'"
else Text.From(_) 
),",") & ")" 
),
textview = Text.Combine(added[all],","),
insert =  Value.NativeQuery( PostgreSQL.Database("ВАШ ХОСТ", "НАЗВАНИЕ БД"), "INSERT INTO " & postgresql_table &" VALUES " & textview )
in insert

Новый шаг с вызовом функции в запросе к Метрике:

 insert_postgresql(#"Последний шаг запроса Метрики", "СХЕМА.ТАБЛИЦА")

И данные также успешно вставляются в PostgreSQL:

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

Напоминаю, что функция с удалением и вставкой данных находится на Boosty. Будем рады если поддержите нас там, но и не расстроимся если вы напишете её сами. Пусть это будет вашим домашним заданием для прокачки навыков Power Query;)

Заключение про Excel

Каждый раз, когда я пишу статью, мне жаль, что кто-то не рассказал мне всё это раньше. И в данной статье это ощущение особенно сильно. Хотя, возможно тогда я бы не начал изучать Python, который имеет гораздо более широкие возможности и управляемость, чем Power Query. Но теперь я точно знаю, что Power Query - это потрясающий Low Code. 

Остаётся добавить, что для загрузки данных в Clickhouse вам не нужен даже Power BI. Ведь это можно делать на обыкновенном Excel. 

Открываем Power Query в Excel, вставляем ту же Метрику с выгрузкой в БД, загружаем результат запроса на лист, нажимаем правой кнопкой на запрос и выбираем "Свойства". В следующем окне выбираем "обновлять каждые N минут" и пересобираем нашу базу хоть каждую минуту:) Главное - не закрывать файл Excel. 

Правда загрузка из Excel в PostgreSQL работает только при наличии коннектора, а вот с Clickhouse можно работать всегда. Если же вам потребуется использовать Power BI или Excel для загрузки в другие СУБД, будем рады помочь адаптировать функции под них.

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

Ну а мне только остаётся напомнить, что у нас есть разные коннекторы на Power Query, бОльшая часть которых работает как в Power BI, так и в Excel: 

1. Коннекторы для рекламных отчётов: https://directprorf.ru/excel
2. Коннекторы для маркетплейсов: https://directprorf.ru/marketplaces
3. Коннектор для получения данных сообществ VK
4. Коннектор к YandexGPT
5. Коннектор к ChatGPT
6. Бесплатные mez-коннекторы на Github для Power BI

Для mez-коннекторов потребуется шлюз Power BI Gateway (personal mode), в то время как коннекторы-функции работают без шлюза.