Загрузка собственных данных Яндекс Метрики в ClickHouse

16 июля, 2024

Практическое руководство по выгрузке статистики счетчика Яндекс Метрики с помощью Logs API и Python с последующей загрузкой данных в собственный кластер ClickHouse в Yandex Cloud.

Рекомендуется к прочтению:

Начало работы

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

Пример отчета (сводки) в Яндекс Метрике

Однако работа в интерфейсе Яндекс Метрики возможна только с тем набором инструментов и опций, которые заложили в него сами разработчики Яндекса. Именно поэтому интернет-маркетологи и веб-аналитики используют в своей работе API. Для получения неагрегированных данных, так называемых «сырых данных» Яндекс Метрики, используется Logs API. Вы делаете запрос на те данные, которые хотите загрузить из Logs API, и вам выдается список тех посещений пользователей, которые были на вашем сайте. Можно увидеть Client ID посещения, время пребывания на сайте, просмотры страниц, регион пользователя, источник перехода и многое другое.

Пример неагрегированных данных, выгруженных с помощью Logs API

Как раз Logs API позволяет строить различные воронки, пути пользователей по вашему сайту, включая данные по страницам, карту событий/целей, по источникам трафика, проводить когортный анализ, создавать собственные модели атрибуции, прогнозировать будущие показатели на основе исторических данных счетчика Метрики и многое другое.

Итак, чтобы начать работу, вам необходимо зарегистрироваться в Yandex Cloud, настроить сообщество в DataSphere и привязать к нему платежный аккаунт. Перейдите по ссылке и нажмите на кнопку Попробовать бесплатно:

Попробовать Yandex DataSphere бесплатно

Войдите с помощью Яндекс ID или рабочего аккаунта в федерации (SSO). Поскольку мы будем работать с данными Яндекс Метрики, то используйте для входа свою учетную запись, на которой размещен счетчик.

Вход через Яндекс ID

Если вы впервые используете Yandex Cloud, то после входа в Yandex DataSphere для вас будет создана новая организация с префиксом organization- и названием вашего аккаунта.

Новая организация

Создание платежного аккаунта

Вам также необходимо создать платежный аккаунт и привязать его к сообществу DataSphere. Для этого перейдите по ссылке и создайте новый аккаунт:

Создание платежного аккаунта

На шаге Общая информация придумайте имя аккаунта, выберите организацию, для которой будет использоваться платежный аккаунт, а также задайте страну, резидентом которой вы являетесь, и укажите плательщика.

Общая информация

В рамках одного пользовательского аккаунта все плательщики должны осуществлять оплату в одной и той же валюте. При выборе другой страны возникнет ошибка. Подробнее об этом читайте в официальной документации Яндекс Облака.

Выбрав Новый плательщик и Тип аккаунта (Физическое лицо или Юридическое лицо/ИП), нажмите Вперед. Если вы выбрали физическое лицо, то на следующем шаге заполните свои персональные данные - имя и фамилию.

Персональные данные

На шаге платежных данных привяжите свою банковскую карту. Укажите 16-значный номер, срок действия, код CVV (с обратной стороны карты):

Привязка карты

Заполнив данные, нажмите кнопку Привязать. Для проверки валидности карты на вашем счету будет заблокирована незначительная сумма (~ 11 рублей). Эти средства не списываются и будут снова доступны после завершения проверки и разблокировки. Срок разблокировки несписанных средств зависит от условий работы вашего банка.

Если карта прошла валидацию, вы увидите ее в списке привязанных:

Привязанная карта

На последнем шаге заполните контактные данные – электронную почту и телефон:

Контактная информация

Эта информация нужна не только для связи с вами, но и для выставления счетов и финансовых документов.

Если это ваш первый платежный аккаунт в Yandex Cloud, вам будет доступно подключение пробного периода и гранта. Для этого обязательно поставьте галочку Включить пробный период:

Активация пробного периода и получения гранта

Грант - скидка, которая предоставляется ИП, организации или физическому лицу на использование любых сервисов Yandex Cloud. Он будет начислен при создании первого платежного аккаунта, если ранее вы никогда не приобретали услуг Yandex Cloud и не активировали пробный период. Стартовый грант действует 60 дней. Размер и валюта гранта зависят от страны, в которой вы проживаете:

  • для резидентов Российской Федерации (РФ), а также налоговых резидентов Республики Беларусь (РБ) размер гранта составляет не менее 4000 ₽, с учетом НДС;
  • для резидентов Республики Казахстан (РК) размер гранта составляет не менее 24 000 ₸, с учетом НДС;
  • для нерезидентов Российской Федерации и Республики Казахстан размер гранта составляет не менее 50 $, без учета налогов и сборов.
Подробнее о гранте читайте в официальной документации Yandex Cloud.

Если вы выбрали тип Юридическое лицо или ИП, тогда вам потребуется указать данные плательщика (название организации, почтовый индекс, фактический и юридический адреса, ИНН, КПП, БИК, расчетный счет). При выборе способа оплаты Банковский перевод платежный аккаунт будет создан в статусе «Не подтвержден». На вашу почту, указанную в аккаунте Яндекса или Яндекс Коннекта, будет отправлено письмо с описанием дальнейших действий. Активация платежного аккаунта может занять до трех рабочих дней.

Также для юридических лиц предусмотрен пробный период. Пробный период - это разовое ознакомительное предложение для юридических лиц. Вы можете воспользоваться пробным периодом, если вы:

  • никогда не приобретали услуги Yandex Cloud;
  • ранее не активировали пробный период.

Пробный период позволяет бесплатно использовать ресурсы Yandex Cloud в ограниченном режиме. При этом потребленные ресурсы оплачиваются из средств стартового гранта. После завершения пробного периода ресурсы будут сохранены, но остановлены. Для возобновления работы с Yandex Cloud в полном объеме потребуется перейти на платную версию.

Примечание: включение пробного периода происходит только на этапе создания первого платежного аккаунта для юридического лица в сервисе Yandex Cloud Billing.

В завершение нажмите кнопку Создать. После этого вас перенаправит на страницу Биллинга, на которой будут отображены ваши платежные реквизиты. Убедитесь, что ваш аккаунт активен:

Активный платежный аккаунт

Привязка платежного аккаунта к DataSphere

Теперь вернитесь в Yandex DataSphere, перейдите в раздел Сообщества и выберите сообщество организации по умолчанию:

Сообщества - сообщество по умолчанию

Привяжите платежный аккаунт к сообществу DataSphere, в котором вы будете работать. Для это прокрутите страницу чуть вниз и в блоке 1. Привяжите платежный аккаунт нажмите на кнопку Привязать:

Привязка платежного аккаунта к DataSphere

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

Привязать

Через некоторое время вы получите уведомление об успешной привязке платежного аккаунта к сообществу DataSphere.

Подключение ClickHouse

ClickHouse позволяет работать с неагрегированными статистическими данными Яндекс Метрики, полученными с помощью Logs API. Для этого вы можете арендовать место в Yandex Cloud. Но сразу хотелось бы сказать, что это решение является платным и будет зависеть от выбранного вами кластера и итоговой конфигурации (настроек).

Для работы с ClickHouse в Яндекс Облаке вам понадобится проект. Если вы никогда не работали в Yandex Cloud, то перейдите по ссылке и создайте свое первое облако. Выберите вашу организацию и введите название облака:

Создание облака в Yandex Cloud

После его создания вас перенаправит в консоль управления:

Консоль управления облаком

Теперь в созданном каталоге для создания кластера ClickHouse выберите меню Managed Service for ClickHouse:

Managed Service for ClickHouse

В открывшемся окне нажмите Создать кластер ClickHouse:

Создание кластера ClickHouse

Укажите настройки кластера ClickHouse:

  • в блоке Базовые параметры укажите произвольное имя кластера или оставьте его по умолчанию;
  • в блоке Ресурсы выберите платформу Intel Cascade Lake, тип burstable и класс хоста b2.medium;

Примечание: не рекомендуется использовать конфигурации ВМ типа burstable в продакшн-среде. В руководстве Yandex Cloud они используются в качестве примера. Для продакшн-решений используйте конфигурации standard или memory-optimized.

  • в блоке Размер хранилища оставьте значение 10 ГБ;
  • в блоке Хосты нажмите значок карандаша (редактирования);

Редактирование хоста

Включите опцию Публичный доступ и нажмите кнопку Сохранить.

Публичный доступ

В блоке Настройки СУБД выключите управление пользователями через SQL, укажите имя пользователя, пароль и имя БД:

Настройки СУБД

В блоке Сервисные настройки включите опции:

  • доступ из DataLens
  • доступ из WebSQL
  • доступ из Метрики и AppMetrica
  • доступ из Serverless
  • доступ из Yandex Query

Сервисные настройки

После всех проведенных настроек вы можете оценить итоговую конфигурацию вашего кластера ClickHouse и ежемесячную стоимость аренды перед тем, как его создать. Как вы уже знаете, стоимость аренды хранилища зависит от выбранного ресурса, его размера и других настроек. Согласно тому, что написано в документации Yandex Cloud, примерная стоимость аренды для текущего решения и работы с Logs API Яндекс Метрики будет составлять 3000 руб./месяц:

Конфигурация кластера ClickHouse и его настройки

В завершение нажмите кнопку Создать кластер.

Примечание: конфигурация кластера и итоговая стоимость ClickHouse у вас может быть другой, все зависит от проекта и объемов ваших данных Яндекс Метрики.

Настройка групп безопасности

После создания кластера ClickHouse необходимо к нему подключиться. Сделать это можно несколькими способами:

  • через интернет, если вы настроили публичный доступ для нужного хоста. К таким хостам подключиться можно только используя SSL-соединение.
  • с виртуальных машин Yandex Cloud, расположенных в той же облачной сети. Если к хосту нет публичного доступа, для подключения с таких виртуальных машин необязательно использовать SSL-соединение.

Примечание: если в вашем проекте Yandex Cloud уже есть созданные ранее сеть и подсети, вы можете пропустить этот шаг и сразу перейти к настройке групп безопасности. Если при создании каталога и кластера ClickHouse вы не добавляли их по умолчанию, то вам нужно сделать это.

Для этого перейдите в консоль управления своего проекта Yandex Cloud по ссылке и выберите нужный каталог. В разделе Все сервисы нажмите на Virtual Private Cloud:

Virtual Private Cloud

Если до этого у вас не было облачной сети, создайте ее:

Создание облачной сети

Yandex Virtual Private Cloud позволяет создавать облачные сети и подсети, которые используются для передачи информации между облачными ресурсами и связи ресурсов с интернетом.

Облачная сеть - это аналог традиционной локальной сети в дата-центре, а подсеть — это диапазон IP-адресов в облачной сети. Трафик может передаваться между подсетями одной сети, но не может передаваться между подсетями разных сетей.

Задайте имя сети, например, network1. В разделе Дополнительно оставьте галочку Создать подсети и нажмите Создать сеть:

Создание сети и подсетей

Ваша сеть и несколько подсетей будут созданы. Теперь необходимо настроить группы безопасности, чтобы был разрешен трафик с определенных портов и IP-адресов. В левом навигационном меню сервиса Yandex Virtual Private Cloud нажмите на раздел Группа безопасности:

Группы безопасности

Создайте вашу группу безопасности:

Создание группы безопасности

На открывшейся странице задайте имя группы (например: firstgroup) и Сеть, выбрав ее из выпадающего списка:

Имя группы безопасности и сеть

Теперь вам необходимо добавить правила для входящего трафика. Переключитесь на вкладку Входящий трафик и нажмите кнопку Добавить правило:

Добавить правило (Входящий трафик)

В описание введите произвольный текст (например: Порт 8443) и следующие настройки:

  • Описание – Порт 8443
  • Диапазон портов – 8443
  • Протокол – TCP
  • Источник – CIDR
  • CIDR блоки – 0.0.0.0, а после / 0

В интерфейсе Yandex Cloud правило для входящего трафика (порт 8443) будет выглядеть так:

Добавление правила для входящего трафика

Сохраните правило. В общем списке вы увидите его на вкладке входящего трафика:

Созданное правило для входящего трафика

Необходимо создать еще одно правило, только для порта 9440. Нажмите кнопку Добавить правило и добавьте все те же самые настройки, что и для порта 8443, за исключением Диапазон портов. Там выставите значение 9440.

  • Описание – Порт 9440
  • Диапазон портов – 9440
  • Протокол – TCP
  • Источник – CIDR
  • CIDR блоки – 0.0.0.0, а после / 0

В интерфейсе Yandex Cloud правило для входящего трафика (порт 9440) будет выглядеть так:

Добавление правила для входящего трафика

Сохраните правило. В результате вы должны получить два новых правила для входящего трафика:

Два правила для входящего трафика

Сохранить вашу группу безопасности с помощью кнопки Сохранить в самом низу страницы:

Сохранение группы безопасности

После этого вас перенаправит на страницу со всеми созданными группами безопасности:

Созданная группа безопасности

Получение SSL-сертификата

Для взаимодействия с кластером ClickHouse вам необходимо получить SSL-сертификат безопасности. Для этого воспользуйтесь стандартным командным интерпретатором Windows PowerShell.

PowerShell – это кроссплатформенное решение для автоматизации задач, которое включает оболочку командной строки, скриптовый язык и платформу управления конфигурацией. PowerShell поддерживается в Windows, Linux и macOS. Он позволяет:

  • менять настройки операционной системы;
  • управлять службами и процессами;
  • настраивать роли и компоненты сервера;
  • устанавливать программное обеспечение;
  • управлять установленным ПО через специальные интерфейсы;
  • встраивать исполняемые компоненты в сторонние программы;
  • создавать сценарии для автоматизации задач администрирования;
  • работать с файловой системой, хранилищем сертификатов и т.д.

Откройте меню Пуск на своем компьютере и начните вводить powershell. В появившихся результатах нажмите на приложение Windows PowerShell (для Windows):

Windows PowerShell

Откроется окно командной строки. Скопируйте нижеприведенный текст для Windows в PowerShell и нажмите Enter:

Этот код и другие для macOS и Linux можно скопировать из официальной документации Yandex Cloud.

В программе это будет выглядеть так:

Выполнение скопированного кода в Windows PowerShell

После нажатия Enter высветиться окно с подтверждением установки сертификата. Нажмите Да:

Установка сертификата

После этого окно можно закрыть. Сертификаты будут сохранены на вашем компьютере:

  • $HOME\.yandex\RootCA.crt
  • $HOME\.yandex\IntermediateCA.crt

Воспользуйтесь поиском по файлам компьютера, чтобы найти сертификаты:

Сохраненные SSL-сертификаты на компьютере

Запомните полный путь к корневому файлу RootCA.crt. В дальнейшем он вам пригодится при подключении к ClickHouse.

Подключение DataSphere

Yandex DataSphere — это сервис, который позволяет обучать ML-модели на конфигурациях с GPU и быстро запускать их в эксплуатацию. При этом не нужно самостоятельно создавать и обслуживать виртуальные машины: сервис «из коробки» выделяет необходимые конфигурации и легко настраивается.

Мы же можем использовать DataSphere как среду для разработки на Python от Yandex Cloud с интерфейсом Jupyter Notebook. В нем есть все нужные пакеты для анализа данных и машинного обучения (TensorFlow, Keras, PyTorch, NumPy и др.).

Откройте главную страницу DataSphere. В разделе Сообщества выберите сообщество, в котором вы хотите создать проект. В правом верхнем углу нажмите кнопку Создать проект:

Создание проекта

В открывшемся окне укажите имя и (опционально) описание проекта. Требования к имени:

  • Длина - от 3 до 63 символов;
  • Может содержать строчные буквы латинского алфавита, цифры и дефисы;
  • Первый символ - буква. Последний символ - не дефис.

Создание проекта

Нажмите кнопку Создать. После создания проекта нажмите кнопку Открыть проект в JupyterLab:

Открыть проект в JupyterLab

Перед вами откроется среда разработки JupyterLab, в которой вы будете работать дальше:

JupyterLab

DataSphere позволяет работать с проектом из любой локальной IDE, если она поддерживает работу с удаленными серверами Jupyter. По умолчанию эта возможность выключена, администратор сообщества может включить ее в настройках сообщества. Получить ссылку для подключения к проекту из IDE может только пользователь с ролью datasphere.community-projects.developer или выше.

Подробнее о том, как подключиться к JupyterLab проекта из локальной IDE (например, Visual Studio Code) читайте в официальной документации Yandex Cloud.

Создание приложения и получение токена доступа

Чтобы выгрузить данные из своего счетчика Яндекс Метрики, вам необходимо сначала создать приложение, а затем получить токен доступа. В рамках данного руководства я не буду подробно описывать эти шаги настройки. Вы можете прочитать две статьи ниже на контентной платформе lib.osipenvkov.ru:

В них подробно разобраны все этапы. В завершение не забудьте скопировать полученный токен доступа. После этого перейдите в Yandex DataSphere и в корневой директории создайте новый текстовый файл:

Создание текстового файла

Назовите файл .yatoken.txt, в содержимое файла вставьте полученный токен доступа:

Ваш токен доступ в файле .yatoken.txt

Сохраните изменения в файле. Если в директории вы не видите свой текстовый файл, то попробуйте включить скрытые файлы через меню View - Show Hidden Files:

View - Show Hidden Files

Выгрузка данных через Logs API Яндекс Метрики

В корневой директории создайте новый файл, нажав правой кнопкой мыши и выбрав New Notebook, или через меню сверху - File - New - Notebook:

New Notebook

Переименуйте файл по своему усмотрению. Например, можно назвать его 1. get_data_logs_api. Откройте этот файл.

Открытие файла первой программы

Вставьте в первую ячейку следующий код:

Этот код импортирует несколько библиотек, которые часто используются в Python для работы с данными, веб-запросами и обработкой строк. Вот краткое описание каждой из них:

  1. requests - библиотека для выполнения HTTP-запросов. Она позволяет отправлять HTTP/1.1 запросы с использованием методов GET и POST;
  2. pandas - библиотека для анализа данных и манипуляций с ними. Она предоставляет структуры данных и функции для работы с таблицами и временными рядами;
  3. StringIO из модуля io - класс для работы с текстовыми данными в памяти, как если бы это был файл;
  4. datetime - модуль для работы с датами и временем;
  5. json - модуль для работы с JSON (JavaScript Object Notation) данными;
  6. urlencode из модуля urllib.parse - функция для кодирования параметров URL;
  7. time - модуль для работы с временем, предоставляет функции для измерения времени и задержек.

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

Добавление ячейки в программу

Вставьте следующий код:

В переменную TOKEN мы помещаем путь к расположению нашего текстового файла .yatoken.txt с токеном.

Код с переменной TOKEN

Добавьте еще одну ячейку в программе для следующего кода:

, где в переменной COUNTER_ID вам необходимо указать идентификатор вашего счетчика Метрики:

Идентификатор счетчика Яндекс Метрики в COUNTER_ID

Его значение вы можете найти в списке счетчиков:

ID счетчика

Укажите дату начала анализируемого периода в качестве значения переменной START_DATE и дату окончания в переменной END_DATE.

Важно: диапазон дат НЕ будет включать дату окончания. Например, если вы хотите получить данные по 5 декабря 2022 года, вставьте в переменную END_DATE значение 2022-12-06. И не забудьте, что статистика за текущий день (сегодня) в Logs API недоступна, так как она может быть неполной. Запрашивайте данные за предыдущий день и более ранний период.

Наверняка вы знаете, что статистику счетчика Яндекс Метрики можно выгружать по Logs API на уровне визитов (visits) и просмотров (hits). В зависимости от того, как вы будете работать дальше с данными, вам нужно выгрузить тот или иной уровень организации данных. Ниже я покажу оба приема.

Давайте сначала начнем с уровня визитов. Проверьте, чтобы в переменной SOURCE было написано visits. Затем в поле API_FIELDS вам необходимо через запятую перечислить все поля визитов Logs API, которые вам будут нужны. Подробнее о том, какие поля в Logs API присутствуют, читайте в официальной документации Яндекса:

Пусть это будет следующий набор полей (на уровне визитов/visits):

  • ym:s:date - дата визита;
  • ym:s:dateTime - дата и время визита (в часовом поясе счетчика);
  • ym:s:startURL - cтраница входа;
  • ym:s:deviceCategory - тип устройства (1 - десктоп, 2 - мобильные телефоны, 3 - планшеты, 4 - TV);
  • ym:s:operatingSystemRoot - группа операционных систем;
  • ym:s:clientID - уникальный идентификатор пользователя в браузере (first-party cookies);
  • ym:s:browser - браузер;
  • ym:s:lastTrafficSource - источник трафика (модель атрибуции - Последний переход);
  • ym:s:purchaseRevenue - общий доход или суммарная ценность транзакции (при настроенной электронной коммерции);
  • ym:s:purchaseID - идентификатор покупки (при настроенной электронной коммерции).

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

Вот так это будет выглядеть в JupyterLab:

Итоговый сформированный запрос в Logs API

Теперь поочередно в каждую ячейку вставьте следующий код:

Этот код создает словарь header_dict, который используется для хранения заголовков HTTP-запроса. Затем он может быть использован для аутентификации и указания типа содержимого.

Словарь header_dict

Следующая ячейка с кодом:

Этот код формирует запрос к Logs API Яндекс Метрики, чтобы получить данные за указанный период и с указанными параметрами.

Запрос к Logs API

Далее (в отдельной ячейке):

И в конце (последняя ячейка):

Сначала мы проверяем, можно ли в принципе создать такой запрос в Logs API. Для этого запустите выполнения вашей программы с помощью кнопки Run:

Запуск программы

Вас попросят выбрать виртуальную машину (ВМ) и ее конфигурацию для выполнения программы. Поскольку нам нужно всего лишь выгрузить данные из Яндекс Метрики с помощью Logs API, то достаточно использовать самую простой и недорогой вариант. Нажмите Выбрать.

Конфигурация ВМ проекта

Yandex Cloud арендует для вас виртуальную машину с ее вычислительными мощностями. Затем вы сможете выполнить программу. Для этого поочередно нажмите кнопку Run или сочетание клавиш Shift + Enter, дойдя до последней ячейки программы. В конце вы должны увидеть статус ответа 200 и возможность создания запроса логов со значением true:

Возможность создания запроса логов за указанный период

Это означает, что все хорошо, и теперь мы можем приступить к созданию основного запроса и выгрузке данных.

Продолжаем писать программу. Создайте новую ячейку в этом же файле и вставьте в нее следующий код:

Этот код формирует запрос к Logs API Яндекс Метрики, чтобы создать запрос на получение данных за указанный период и с указанными параметрами.

Запрос к Logs API

Затем четыре ячейки и четыре строчки кода. Первая:

Вторая:

Третья:

Четвертая:

Результат в программе:

Код программы

Запустите программу с первой ячейки после ее первого запуска. В самом конце вы должны получить идентификатор запроса логов (request_id):

Получение идентификатора запроса логов

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

Этот код периодически проверяет статус запроса логов, отправляя запросы к API Яндекс Метрики, пока статус не изменится с created.

Проверка статуса запроса

Следующая строка программа такая:

И в завершение:

Выполните эти три ячейки программы. Вы должны получить схожий результат:

Статус запроса - processed

Статус запроса processed означает, что он обработан и все хорошо. В параметре size вы можете увидеть итоговый размер выгружаемых данных из Яндекс Метрики, а в параметра part_number - количество частей, за которое они будут выгружены. Если 0, то всю статистику можно будет выгрузить за один раз, а не частями.

Создайте еще одну ячейку, в которую вставьте этот код на Python:

Этот код загружает части логов, объединяет их в один датафрейм и сохраняет результат в переменной visits_df, поскольку сейчас мы работаем с данными на уровне визитов (visits).

Выгрузка данных Яндекс Метрики

Следующая строка программы:

Этот код используется для получения размеров датафрейма. Он возвращает кортеж, содержащий количество строк и столбцов в DataFrame.

И финальная ячейка:

Эта строка сохраняет DataFrame visits_df в файл CSV с именем metrika_data_visits.csv в корневой директории.

Выполните оставшуюся часть программы. Если все сделали правильно, то слева в директории вы увидите итоговый файл со своими данными Яндекс Метрики на уровне визитов:

Файл с выгруженными данными Logs API Метрики на уровне визитов

Если вы хотите выгрузить статистику еще и на уровне просмотров (hits), то можете:

  1. скопировать файл со своей программой и выполнить те же шаги отдельно;
  2. просто продолжить текущую программу.

В новой ячейке текущей программы задайте:

Пусть это будет следующий набор полей (на уровне просмотров/hits):

  • ym:pv:date - дата события;
  • ym:pv:dateTime - дата и время события (в часовом поясе счетчика);
  • ym:pv:URL - адрес страницы;
  • ym:pv:deviceCategory - тип устройства (1 - десктоп, 2 - мобильные телефоны, 3 - планшеты, 4 - TV);
  • ym:pv:operatingSystemRoot - группа операционных систем;
  • ym:pv:clientID - уникальный идентификатор пользователя в браузере (first-party cookies);
  • ym:pv:browser - браузер;
  • ym:pv:lastTrafficSource - источник трафика (модель атрибуции - Последний переход);

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

Список полей для данных Яндекс Метрики на уровне просмотров (хитов)

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

Затем:

Далее:

После:

Получение идентификатора запроса:

Так это выглядит в программе:

Ячейки программы

И затем вторая часть:

Тот же самый код, который периодически проверяет статус запроса логов, отправляя запросы к API Яндекс Метрики, пока статус не изменится с created.

Следующая ячейка:

Затем:

На уровне хитов статистика запроса может быть разбита на несколько частей, как в моем примере:

Номер части подготовленных логов обработанного запроса

После этого выгружаем данные на уровне хитов:

Аналогичный код для получения размеров датафрейма, только уже с другим названием (hits_df), отличающемся от visits_df данных, выгруженных на уровне визитов:

И финальная строка с кодом сохранения данных в корневой директории файла metrika_data_hits в формате csv:

Результат выполнения программы должен быть таким:

Файл с выгруженными данными Logs API Метрики на уровне хитов

Поздравляю! Вы только что выгрузили сырые данные вашего счетчика Яндекс Метрики с нужным набором полей с помощью Logs API, кода Python и JupyterLab в Yandex DataSphere.

При желании вы можете скачать полученные файлы csv к себе на компьютер и посмотреть как выглядит ваша статистика. Для этого нажмите правой кнопкой мыши на каждый файл и выберите Download:

Скачивание данных на компьютер

Файл будет скачан вам на компьютер. Открыть его можно с помощью обычного блокнота, Notepad++ или того же Microsoft Excel:

Сырые данные Яндекс Метрики в Microsoft Excel

Разделите текст по столбцам, используя знак табуляции, чтобы было удобнее просматривать статистику:

Сырые данные Яндекс Метрики в Microsoft Excel (по столбцам)

Вот так выглядят данные вашего счетчика Яндекс Метрики "под капотом".

Теперь нам предстоит следующий шаг - загрузить данные в ClickHouse!

Загрузка данных в ClickHouse из DataSphere

В корневой директории создайте новый notebook. Дайте ему название - например, 2. upload_clickhouse:

Файл для новой программы

В этом файле мы будем писать программу по загрузке данных в ClickHouse. Начинаем с импорта библиотек:

В программе это будет выглядеть так:

Библиотеки Python

Далее создайте ячейку и добавьте туда следующий код:

, где вам необходимо переименовать имена файлов metrika_data_hits.csv  и metrika_data_visits.csv на свои собственные. Если вы делали все по моей инструкции, то названия файлов будут таким. Также обратите внимание на то, что в этом руководстве я демонстрирую возможность загрузки данных как на уровне визитов (visits), так и на уровне хитов (hits). Если вы загружаете данные только для визитов или только для хитов, то скорректируйте код и удалите лишнюю строку из программы:

Чтение данных из файлов

Этот код нужен для чтения данных из файлов, которые вы выгрузили на предыдущем шаге.

В корневой директории создайте новый текстовый файл с названием .chpass.txt. Запишите в него пароль заведенного пользователя, который вы задали при создании кластера ClickHouse (см. шаг выше, Настройки СУБД).

Файл .chpass.txt с паролем в корневой директории

Сохраните и закройте файл.

Теперь загрузите SSL-сертификат в корневую директорию. Для этого нажмите на значок загрузки, найдите сертификат RootCA.crt на своем компьютере (см. выше) и добавьте его в проект.

Загрузка SSL-сертификата в проект

Вернитесь в программу 2. upload_clickhouse и добавьте следующий код:

, где в CH_HOST_NAME вставьте имя хоста своего кластера ClickHouse. Найти его можно, перейдя в консоль управления и открыв сам кластер. Выберите из списка слева Хосты. На вкладке Обзор скопируйте имя хоста:

Копирование имени хоста (FQDN хоста)

В переменные CH_USER и CH_DB_NAME вставьте имя пользователя и имя БД (см. выше, Настройки СУБД). Все остальное оставьте без изменений:

Данные для подключения

Скачайте файл some_funcs.py по ссылке и загрузите его в свой проект. Расширение файла должно быть .py:

Файл some_funcs.py

В файле some_funcs есть класс simple_ch_client для работы с ClickHouse. Сначала надо создать экземпляр класса, инициализировав его начальными параметрами - хост, пользователь, пароль и путь к сертификату simple_ch_client(CH_HOST, CH_USER, CH_PASS, cacert).

В классе есть 4 метода:

  1. .get_version() - получает текущую версию ClickHouse. Хороший способ проверить, что указанные при инициализации параметры работают;
  2. .get_clickhouse_data(query) - выполняет запрос query и возвращает результат в текстовом формате;
  3. .get_clickhouse_df(query) - выполняет запрос query и возвращает результат в виде DataFrame;
  4. .upload(table, content) - загружает таблицу content, которая подается в текстовом формате в таблицу ClickHouse'а с именем table.

Используя заготовленные выше переменные, проверим доступ до сервера. Этот метод реализован в методе .get_version() класса для работы с ClickHouse. При успешном подключении не произойдет никакой ошибки при выполнении этого метода, и он сам вернет версию сервера ClickHouse (например 21.3.2.5).

Добавьте следующие строки кода:

Далее в отдельной ячейке:

И еще одна ячейка:

Выполните программу с самого начала. В конце вы должны получить версию вашего сервера ClickHouse. В моем примере - это 24.3.5.46:

Результат выполнения - версия сервера ClickHouse

Она должна совпадать с версией вашего кластера, которую можно посмотреть на вкладке Обзор в настройках ClickHouse:

Версия ClickHouse в настройках

После этого начнем загружать данные. Для этого проверим как они отображаются с помощью команды:

Запустите все ячейки в этой программы, начиная с самой первой. В JupyterLab вы должны увидеть данные по хитам в виде датафрейма:

Данные по хитам

Создайте новую ячейку и вставьте туда следующий код:

Этот код выполняет переименование столбцов, используя метод rename из библиотеки pandas:

  • 'ym:pv:browser':'Browser' - переименовывает столбец ym:pv:browser в Browser;
  • 'ym:pv:clientID':'ClientID' - переименовывает столбец ym:pv:clientID в ClientID;
  • 'ym:pv:date':'EventDate' - переименовывает столбец ym:pv:date в EventDate;
  • 'ym:pv:dateTime':'EventTime' - переименовывает столбец ym:pv:dateTime в EventTime;
  • 'ym:pv:deviceCategory':'DeviceCategory' - переименовывает столбец ym:pv:deviceCategory в DeviceCategory;
  • 'ym:pv:lastTrafficSource':'TraficSource' - переименовывает столбец ym:pv:lastTrafficSource в TraficSource;
  • 'ym:pv:operatingSystemRoot':'OSRoot' - переименовывает столбец ym:pv:operatingSystemRoot в OSRoot;
  • 'ym:pv:URL':'URL' - переименовывает столбец ym:pv:URL в URL.

Параметр inplace = True указывает на то, что изменения будут применены непосредственно к DataFrame, а не будет создана его копия с новыми именами столбцов.

В программе:

Переименование столбцов

Если ваш перечень полей Logs API отличается от примера в этом руководстве, то не забудьте скорректировать данный код под себя.

В новой ячейке программы добавьте этот код:

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

Создание таблицы хитов в ClickHouse

В завершение добавьте этот фрагмент:

Этот код преобразует данные из датафрейма в формат csv и загружает их в таблицу hits вашей базы данных ClickHouse.

Запустите программу. Если никаких ошибок в процессе не возникло, то в конце вы должны увидеть результат ' '

Результат выполнения всей программы по загрузке данных в ClickHouse

Это означает, что ваша таблица с данными Яндекс Метрики по хитам была загружена в ClickHouse. Вы можете убедиться в этом самостоятельно, перейдя в консоль управления вашим облаком и открыв кластер ClickHouse, кликнув на него. Выберите из списка слева WebSQL. Создайте новое подключение:

WebSQL - создание подключения

В открывшемся окне задайте имя подключения (например - connectdb), выберите свой кластер ClickHouse и укажите для него имя пользователя, пароль и имя базы данных (см. выше, Настройки СУБД):

Создание подключения.

В завершение нажмите кнопку Создать. После этого вас перенаправит в редактор запросов, в котором вы сможете посмотреть на структуру своей базы данных ClickHouse и увидеть таблицу по хитам, загруженную с помощью написанной нами программы на Python:

Загруженная таблица по хитам в ClickHouse

Чтобы загрузить таблица с визитами, вернитесь в JupyterLab и в программе 2. upload_clickhouse.ipynb добавьте новые ячейки с кодом:

Теперь вы должны увидеть данные по визитам в виде датафрейма:

Данные по визитам

Следующая ячейка программы:

Этот код переименовывает столбцы в датафрейме следующим образом:

  • ym:s:browser -> Browser
  • ym:s:clientID -> ClientID
  • ym:s:date -> StartDate
  • ym:s:dateTime -> StartTime
  • ym:s:deviceCategory -> DeviceCategory
  • ym:s:lastTrafficSource -> TraficSource
  • ym:s:operatingSystemRoot -> OSRoot
  • ym:s:purchaseRevenue -> Purchase.Revenue
  • ym:s:purchaseID -> Purchase.ID
  • ym:s:startURL -> StartURL

Переименование столбцов

Если ваш перечень полей Logs API отличается от примера в этом руководстве, то не забудьте скорректировать данный код под себя.

Если с таблицей визитов вы выгружали данные по покупкам, тогда вы должны учесть тип данных, относящийся ко всем полям типа purchase. Все они в Logs API являются массивами (array). А поскольку в этом руководстве я использовал поля ym:s:purchaseRevenue (общий доход или суммарная ценность транзакции) и ym:s:purchaseID (идентификатор покупки), то в программу необходимо добавить такой код:

Если коротко, то этот код создает два новых столбца в дат - Purchases, который содержит количество покупок, и Revenue, который содержит общую сумму дохода от покупок. Он также использует метод map для применения функции к каждому элементу соответствующего столбца. Функция удаляет квадратные скобки в начале и конце строки (x[1:-1]), разделяет строку по запятым и преобразует каждую часть в целое число, а затем суммирует эти числа. Если строка равна '[]', то значение в новом столбце будет 0. Таким образом, эти столбцы будут содержать общее количество покупок и общую сумму дохода от покупок.

В программе:

Преобразование полей покупок

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

Теперь создаем таблицу для визитов в ClickHouse:

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

Создание таблицы визитов в ClickHouse

В завершение добавьте этот фрагмент:

Этот код преобразует данные из датафрейма в формат csv и загружает их в таблицу visits вашей базы данных ClickHouse.

Запустите программу. Если никаких ошибок в процессе не возникло, то в конце вы должны увидеть тот же самый результат ' '

Результат выполнения всей программы по загрузке данных в ClickHouse

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

Загруженная таблица по визитам в ClickHouse

Как видите, теперь в вашей базе данных ClickHouse две таблицы - одна загружена на уровне хитов, а другая на уровне визитов. Это и есть сырые данные вашего счетчика Яндекс Метрики, выгруженные с помощью Logs API и загруженные в кластер ClickHouse с помощью кода на Python в Yandex DataSphere. Теперь их можно визуализировать в Yandex DataLens, создавать QL-чарты, или, подключившись к ClickHouse с помощью специальной программы (например, DBeaver), работать с базой данных посредством SQL-команд.

Получайте бесплатные уроки и фишки

По контекстной, таргетированной рекламе и аналитике