Excel ⇨ Python ⇨ СУБД ⇨ Power BI

Новый формат коннекторов (Python+Docker) 🎉

Многие знают меня по коннекторам к разным сервисам для Excel и Power BI. Но рынок не стоит на месте: многие компании отказываются от Power BI в пользу Superset и Datalens из-за рисков санкций.

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

Как выглядит решение?

В качестве среды идеально подходит Docker, где можно как развернуть базу данных, так и запустить скрипт загрузки данных. Если кратко, Docker - это ПО, которое позволяет быстро развернуть приложение на любой ОС - Windows, MacOS, Linux. Так что если раньше мне приходилось возвращать деньги пользователям Mac, то теперь коннекторы запустятся и у них🥂

Вам нужно установить Docker, заполнить ряд настроек, а затем запустить контейнер с базой данных и контейнер с загрузкой данных, после чего данные окажутся в БД, а вы сможете строить отчёты 🍻

В качестве БД используется Clickhouse, который легко интегрируется с большинством современных BI, особенно с Superset и Datalens🔥

Перед запуском Clickhouse вам нужно скорректировать файл настроек и задать доступы в файле пользователей. Для загрузки данных также есть 2 файла - настройки БД и настройки источников. Если запускаете всё на сервере - файлы нужно будет закинуть на сервер (например, с помощью Filezilla).

Что доступно из источников?

1) Wildberries: stocks, sales, orders, realized, nmreport, reklama.

2) Ozon: stocks, transactions, products, realization, returns_fbo, returns_fbs, postings_fbo, finance_cashflow, finance_details, reklama.

3) Yandex Direct: статистика по датам, статистика без даты, список объявлений.

4) GetCourse: пользователи, заказы, оплаты, группы.

Само решение лежит в открытых репозиториях докера и гитхаба, но все настройки и инструкции доступны на Boosty и в курсах с коннекторами.

Все ссылки:
Видео на YouTube
Файлы настроек на Boosty
Команды установки и запуска в Docker
Docker Desktop для запуска на вашем компьютере
@botfather и @userinfobot для настройки логирования
Код на Github (если кому-то интересно)
Получение данных из Clickhouse в Excel/Power BI

Пока готовится большая публикация про коннекторы в докере, хочу поделиться маленьким скриптом 😊

Если кто-то хотел следить за остатками Яндекс Директа по всем клиентам в единой гугл табличке, то этот скрипт для Google Sheets вам поможет 🔥

Как это работает?
1️⃣ вписываете в accountsData токены и логины,
2️⃣ вставляете функцию в app script,
3️⃣ запускаете 1 раз и даёте разрешение на выполнение,
4️⃣ добавляете триггер на обновление раз в 5-10 минут,
✅ наблюдаете всегда актуальные остатки 🥳

Ну а для пользователей Power Query есть статья с коннектором в PQ
Получение токенов Директа всё также по ссылке

function getYandexDirectBalance() {
  var accountsData = [
    {
      token: 'y0_AFpQ',
      login: 'ydrctr'  
    },
    {
      token: 'y0_AJQ',
      login: 'xxxxx'  
    }
  ];
  var apiUrl = 'https://api.direct.yandex.ru/live/v4/json/';
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clearContents();
  sheet.appendRow([ 'Currency', 'Discount', 'Amount', 'AccountID', 'AmountAvailableForTransfer', 'AgencyName', 'Login', 'DateTime']);
  accountsData.forEach(function(accountData) {
    var requestBody = {
      "method": "AccountManagement",
      "token": accountData.token,
      "param": {
        "Action": "Get",
        "SelectionCriteria": {
          "Logins": [accountData.login],  
          "AccountIDS": []
        }
      }
    };
    var options = {
      'method': 'post',
      'contentType': 'application/json',
      'payload': JSON.stringify(requestBody),
      'muteHttpExceptions': true
    };
    try {
      var response = UrlFetchApp.fetch(apiUrl, options);
      var result = JSON.parse(response.getContentText());
      if (response.getResponseCode() !== 200 || result.error) {
        Logger.log('Error: ' + (result.error ? result.error.error_detail : 'Unexpected response code ' + response.getResponseCode()));
        return;
      }
      var accounts = result.data.Accounts;
      var currentDateTime = new Date(); 
            accounts.forEach(function(account) {
        sheet.appendRow([
          account.Currency,
          account.Discount,
          account.Amount,
          account.AccountID,
          account.AmountAvailableForTransfer,
          account.AgencyName,
          account.Login,
          currentDateTime
        ]);
      });
    } catch (e) {
      Logger.log('Exception: ' + e);
    }
  });
}

Успешно закончил курс "Инженер данных". Набрал 470 баллов из 480 🎉

"Успешно" ставят всем, кто набрал больше половины, но это тоже нелегко)) Забавно, что 6 баллов срезали на блоке "визуализация", но ДЗ я делал в Superset, который только изучаю, да и расслабился под конец 🙄

Нагрузка и доходимость
По моим личным наблюдениям от общей группы до конца дошло примерно 20-25%. Большинство отвалилось после Airflow и Greenplum когда уже пошли Hadoop и Big Data.

После биг даты кстати стало полегче и в конце уже было совсем лайтово. Правда финальный проект курса пока сломался, поэтому окончание курса уже было простым😊 Ребята обещали починить, так что возможно мы еще к нему вернёмся.

Как оценить время на выполнение ДЗ? Смотрите на кол-во баллов за него. Если там 1 балл - скорее всего это займёт 5 минут, если 20 баллов - вероятно целый день 🥹

Немного итогов:
1. Как уже писал, вам понадобится время, как минимум первые 3 месяца хотя бы часа по 3-4 в день, так что освободите себя от работы. У курса есть базовые требования по знаниям, если чего-то из этого не знаете, заложите доп время и на них. Концовка курса (после большого блока биг даты) более лайтовая, но дойти до конца всё равно непросто.
2. Курс хардовый и преподы сильные. Это и хорошо и плохо - с одной стороны есть чему учиться, с другой - иногда сидишь ничего не понимаешь, общаешься с ChatGPT. Но это тоже уже писал:)
3. В конце интересные блоки по машинному обучению, оказывается ML не так сложно применять и даже не погружаясь в матстат можно делать очень многое.
4. Курс 100% придётся пересматривать, но думаю его за этим и берут - чтобы потом повторять и внедрять разные инструменты. Спикеры сами пересматривают уроки других спикеров, чтобы освежить знания.
5. Курс не даст вас понимание всех деталей без реальной практики, но даст хорошую базу, которую можно дополнить книгами, общением с ChatGPT и собственным опытом.
6. К концу уже устаёшь от количества потребляемой теории, так что наверное 5 месяцев - оптимальное время для меня, учиться год я бы не выдержал))

Стоит ли идти на такой курс?
Если у вас есть время, желание и база - думаю курс стоит потраченных денег🔥

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

Впрочем, проблемы с обновлением материала всё же есть, но пока это не критично, так как общие принципы обычно остаются неизменными.

Доработал программу для массовых запросов к ChatGPT

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

Какие изменения:
- работает без VPN,
- добавил свежие модели (список моделей подтягивается из GitHub и будет актуализироваться),
- устранил ошибки и немного переписал алгоритм

Тестовая версия для Windows теперь поддерживает все популярные модели, но обрабатывает до 30 строк.

Полная версия находится тут

⚡️⚡️⚡️ Биай будущего ⚡️⚡️⚡️

https://www.youtube.com/watch?v=sySNItx4zPs

ОТЗЫВЫ ПО КУРСАМ И КОННЕКТОРАМ

На канале уже более 1000 человек, а просмотры постов порой достигают и нескольких тысяч🔥 Спасибо всем, кто подписался и читает (или слушает песни)🥂

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

Хочу попросить вас оставить небольшой отзыв:
1. Какую задачу хотели решить
2. Чем вам помог курс/мой ответ/моя работа
3. Ваши впечатления, пожелания

Если не любите пункты, можно писать в любой форме:) Даже если напишете пару слов - это уже будет супер, главное, чтобы отзыв был настоящий✅

Сейчас много инструментов для работы с данными, так что решил структурировать всё и собрать в единый файл.

Разбил инструменты на несколько категорий, по каждой категории собрал наиболее актуальные. В текущих условиях делал упор на OpenSource и доступные в России системы. Инструменты DevOps тоже попали сюда, так как обработка данных часто идёт на кластерах, которые тоже надо поддерживать.

Документ структурирован и разбит на категории:
1. Хранение данных (SQL и NoSQL СУБД)
2. Инструменты обработки данных
3. Оркестраторы данных
4. Системы визуализации
5. Интеграторы (с набором коннекторов)
6. Очереди задач и брокеры сообщений
7. Хранение больших данных
8. Обработка больших данных
9. Инструменты контейнеризации
10. Инструменты DevOps
11. Инструменты ML и блокноты

Ссылка на файлик: современные инструменты работы с данными

Пишите если забыл что-то важное - дополню🤝

————————————
Музыкальный альбом
"Реквием по Power BI"

Нет, Power BI всё еще нормально работает:)

Пишите какая песня вам понравилась больше всего 😊

Все тексты песен на Github

Всех с пятницей! 🥂

Наши коннекторы для маркетплейсов очень популярны, но грузить рекламные данные напрямую в Excel или Power BI довольно сложно из-за ограничений API.

В связи с этим сделал решение для загрузки рекламных данных OZON и WB в ClickHouse. Из ClickHouse можно получать данные в Excel или Power BI, в DataLens или Google Sheets.

Всего за 1 час вы сможете:
- арендовать небольшой сервер;
- развернуть clickhouse;
- запустить сбор рекламных данных OZON и WB.

Все детали в видео:
https://youtu.be/fhoW_awgd8c

Скрипты загрузки в БД есть на Boosty по подписке ПРО:
https://boosty.to/morinad/posts/593c38d7-e20b-43f5-9202-ff9902c36d5f?share=post_link

Плюс добавил скрипты в 5-й урок коннекторов для маркетплейсов.

Касаемо точности данных - мы проверяли загрузку для нескольких клиентов и ошибок не было. Но данные рекламы могут не совпадать с той же "историей затрат", что вполне нормально для того же WB. Поэтому цифры в БД имеет смысл сверять только с ответами API.

Уведомления об ошибках Power BI Report Server в Telegram

Совместно с Машей сделали скрипт, который регулярно отправляет в Telegram ошибки обновлений отчётов из Power BI Report Server. Запускается в самом Report Server.

Как заставить скрипт работать:
- Указать в скрипте IP сервера и Название БД под RS
- Создать телеграм-бота и стартовать его
- Получить ваш ID пользователя от @userinfobot
- Указать в скрипте TOKEN бота (вместо TOKEN) и ID пользователя
- Добавить скрипт в отчёт на Report Server
- В настройках источника ввести логин-пароль самого RS (под ними он стучится в БД)
- Поставить время обновления скрипта после остальных отчётов

Ежедневно вам будут приходить все ошибки обновлений за сегодня. Если хотите отправлять уведомления в группу - уточните ID группы и добавьте туда бота с правами админа.

Код скрипта:

let 
query = let 
messages = 
let
    Источник = Sql.Databases("IP сервера\Название БД под RS"), //в настройках подключения вводите логин пароль самого репорт сервера который стучится в БД
    ReportServer = Источник{[Name="ReportServer"]}[Data],
    dbo_ExecutionLog3 = ReportServer{[Schema="dbo",Item="ExecutionLog3"]}[Data],
    #"Строки с примененным фильтром" = Table.SelectRows(dbo_ExecutionLog3, each ([ItemAction] = "DataRefresh") and ([Status] = "rsInternalError")),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Строки с примененным фильтром",{"ItemPath", "TimeEnd", "Status", "ItemAction", "TimeStart"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Другие удаленные столбцы", "Сегодня", each DateTime.From(Date.From(DateTime.LocalNow()))),
    #"Измененный тип" = Table.TransformColumnTypes(#"Добавлен пользовательский объект",{{"Сегодня", type datetime}, {"TimeStart", type datetime}, {"TimeEnd", type datetime}}),
    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Измененный тип", "Сравнение", each [TimeEnd]>[Сегодня]),
    #"Строки с примененным фильтром1" = Table.SelectRows(#"Добавлен пользовательский объект1", each ([Сравнение] = true)),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Строки с примененным фильтром1",{"Status", "ItemAction", "Сегодня", "Сравнение"}),
    #"Сортированные строки" = Table.Sort(#"Удаленные столбцы",{{"TimeEnd", Order.Ascending}}),
    #"Добавлен пользовательский объект2" = Table.AddColumn(#"Сортированные строки", "Текст", each 
    "❌ " & [ItemPath]&"#(lf)с "&Text.From([TimeStart])&"#(lf)по "&Text.From([TimeEnd])&"#(lf)"
),
    tbl = Table.ReplaceValue(#"Добавлен пользовательский объект2","_"," ",Replacer.ReplaceText,{"Текст"}),
    Пользовательский1 = List.Accumulate( tbl[Текст], {""}, (st,cr)=> let last = List.Last(st), removedLast = List.RemoveLastN(st, 1),
join = removedLast&{last&"#(lf)"&cr}, result = if Text.Length(last)+Text.Length(cr)<3500 then join else st & {cr} in result)
in  
    Пользовательский1

, post = List.Accumulate(messages, {}, (st,cr)=> st & {(try Json.Document(Web.Contents("https://api.telegram.org/botTOKEN/sendMessage", [Query = [chat_id="ID ПОЛЬЗОВАТЕЛЯ", text=cr, parse_mode = "Markdown"], Content = Text.ToBinary("")]))[ok] otherwise false )})
in post,
 tab = #table(1, {{query}}),
    expand = Table.ExpandListColumn(tab, "Column1") 
in expand

Про Karpov Courses

Активно прохожу курс "Инженер данных" от Karpov Courses. Курс длится 5 месяцев и по идее должен дать хорошую базу для дальнейшей работы дата-инженером.

Прошла почти половина курса, впереди еще 2.5 месяца.

Что я понял на текущий момент:

1. Выделяйте время
Не нужно вписываться в подобный курс если ты загружен на работе и у тебя нет хотя бы 4 часов в день на изучение. Я не работаю, мне в этом плане проще, но многие проходят параллельно. Если тратить по часу в день - быстро отстанете. Можно конечно днём работать, а вечером учиться, но времени ни на что другое у вас не останется. Каждые два дня выходит 1 урок - это от 1 до 5 лекций, которые надо посмотреть, осмыслить и сделать задание. Кажется не много, но даже пара месяцев беспрерывных уроков и заданий - это непросто. А некоторые задания растягиваются на несколько дней, так что времени нужно закладывать прилично.

2. Готовьте базу
Входные требования на курс довольно высоки - Python + ООП, SQL + базовое знание СУБД, Git, Команды Linux. Конечно вам не откажут - всё же курс стоит ~90к и гнать вас никто не будет, но предупредят. И если у вас нет опыта в этих областях, вам предстоит параллельно изучать еще и их. Мне это пришлось делать минимально, но иногда приходится останавливаться и разбираться в чём-то. Это при итак плотном графике обучения.

3. Готовьтесь пересматривать уроки
Все преподы, которые встречались нам до текущего дня - это сильные хардовые ребята, которые глубоко понимают тему. В этом есть плюс - они реально знают о чём рассказывают и дают глубокий материал. Но есть и минус - иногда лекцию приходится ставить на паузу и просить ChatGPT пояснить что лектор сейчас сказал и почему он сказал именно так.

4. Инфы много, но далеко не вся
Инфы на курсе очень много, но далеко не все темы раскрываются на 100%. Основные вещи объясняются, но за кадром всё равно остаётся много всего, потому что невозможно рассказать всё (да и запомнить тоже). В уроках есть ссылки на доп. материалы, но пока не до них - основы бы выучить. И это я еще не говорю о практике. Практика на курсе интересная, но на реальной работе её всё равно гораздо больше. Так что тут как всегда - с деталями вы будете разбираться уже на реальных задачах по книгам и чатам, но сильную базу вам дадут.

5. Общайтесь с ChatGPT
Лучше всего проходить курс общаясь с ChatGPT - он очень хорошо дополняет те моменты, которые не сказали лекторы, помогает быстрее выполнять задания и вообще облегчает обучение. Но не думайте, что можно просто сказать ему "сделай вот это задание":) Любой код, любое решение надо осмыслить, наложить на свой кейс, учесть материал лекции и тогда что-то получится. Но если бы я проходил без ChatGPT - я бы наверное замучал саппорт курса вопросами. Конечно, не стоит забывать и про Google - там, где ChatGPT не спасает, спасает Google. Ну или эксперты из поддержки, которые на курсе всем помогают:)

Что по итогу

Когда курс закончится, напишу общее впечатление, но пока не жалею. Я хотел изучить основы профессии за 5 месяцев и для меня данный курс был идеальным решением. На Яндекс Практикум с его 12-месячным курсом как-то не хотелось.

Получение данных из Yandex Disk API 🔥

Если ваш гугл-диск попал (или может попасть) под санкции, а шлюзы вы не любите, вам очень пригодится функция для Яндекс Диск API:)

Как обновлять данные из локального файла в Power BI Service без шлюза? 🥹

1) Грузим файл на Яндекс.Диск.
2) Обращаемся к API Яндекс.Диска с помощью функции и получаем файл в Power BI Desktop.
3) Выгружаем отчёт с файлом в альтернативную рабочую область Power BI Service ("Моя рабочая область" не подойдёт).

В "Моей рабочей области" Power BI требует шлюз, так что там пользы от функции мало.

Где это еще работает?

Функция работает в Excel, а также в потоках данных Power BI. Можно запустить поток данных с обращением к разным файлам и использовать везде его - это довольно удобно.

Сложно поверить, но говорят, что даже в Report Server функция работает 😵‍💫

А вот и сама функция:

let 
function=

(fileLink as text, token as text )=>
let
headers=[#"Content-Type" = "application/json", #"Authorization" = "OAuth "&token, #"Accept" = "application/json"],
querydata = [#"path" = Text.Replace(fileLink, "\","/")],
web = Web.Contents("https://cloud-api.yandex.net/", [RelativePath="v1/disk/resources/download", Headers = headers, ManualStatusHandling = {404, 400}, Query =querydata]),
result = Json.Document(web),
link = result[href], 
relative = Text.Replace(result[href], "https://downloader.disk.yandex.ru/",""),
href = Web.Contents("https://downloader.disk.yandex.ru/", [RelativePath = relative, Headers = headers, ManualStatusHandling = {404, 400}])
in href,

result = Value.ReplaceType(function, FuncType),

FuncType = type function (

fileLink as (type text meta [
        Documentation.FieldCaption = "Путь к файлу после C:\YandexDisk\:",
        Documentation.SampleValues = {"Мои файлы\файлик.xxx"}
    ]),   

token as (type text meta [
        Documentation.FieldCaption = "Ваш токен (ссылка на получение ниже):",
        Documentation.SampleValues = {"y0_AgAAAMfGA...rBX0d-ZpA5O"}
    ])
         )
    as table meta [
        Documentation.Name = "Получаем файл с Яндекс.Диска",
        Documentation.LongDescription =    "",
    
    Documentation.Examples = {
                [
            Description = "Ссылка на получение токена:",
            Code = "https://oauth.yandex.ru/authorize?response_type=token&client_id=a363cf6712db45978a012820777fc06a",
            Result = "По ссылке вы получите токен"
                    ]
        }

    
    ]

in result

Добавил в игру по DAX подсказки 🥂

У многих были типовые неправильные ответы, так что теперь при подобных ответах вы получите подсказку как нужно скорректировать формулу☺️

Проверка ответа идёт на 3 уровнях:
- проверка кол-ва открытых и закрытых скобочек в формуле;
- проверка неправильных написаний (divede вместо divide итд);
- проверка на конкретную ошибку.

Пробуйте: https://t.me/courses2probot

Краткий курс по DAX готов🔥

Мне пришла идея сделать небольшой бесплатный курс в формате игры😊

Так что сделал телеграм-бота, в котором вы можете проходить краткие уроки, выполнять задания, получать баллы и идти дальше если правильно ответили ✅

В конце КАЖДОГО урока есть задание. Чтобы открыть следующий урок, задание надо выполнить. Ответы вы отправляете боту в формате "Мера = ФОРМУЛА".

Курс рассчитан на начинающих, так что смело заходите и попробуйте выиграть! Я только что прошел его с телефона и как пользователю мне очень понравилось😉

Удачи в игре: @courses2probot

Коннектор к API GetCourse (бесплатно для Excel / Power BI)

Стройте отчёты по онлайн-школе на базе Power BI, Excel или Google Sheets!

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

Если грузите разные отчёты геткурса в рамках одного потока или отчёта, можно выставить задержки так:
1) пользователей получаем без задержки,
2) группу - с задержкой 60 секунд,
3) заказы - с задержкой 120 секунд,
4) платежи - с задержкой 180 секунд.
Так вы избежите одновременной загрузки и ошибок с ней связанных.

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

Полная инструкция тут: статья в блоге
Функция для power bi/excel: ссылка на гитхаб
Файл для гугл таблиц на Boosty: ссылка на пост Boosty