Загрузка собственных данных Яндекс Метрики в ClickHouse
Практическое руководство по выгрузке статистики счетчика Яндекс Метрики с помощью Logs API и Python с последующей загрузкой данных в собственный кластер ClickHouse в Yandex Cloud.
Рекомендуется к прочтению:
- Введение в API Яндекс Метрики
- Встречайте - Метрика Про!
- Интеграция Яндекс.Метрики c Yandex Cloud (Метрика Про)
- Математический расчет поведенческих факторов в Яндекс Метрике
- Воронки и когортный анализ на данных Яндекс Метрики
- Подключение к базе данных ClickHouse с помощью DBeaver
- Как работать с неагрегированными данными Яндекс.Метрики? (Метрика Про)
Начало работы
Владелец сайта после установки счетчика Яндекс Метрики, как правило, работает в интерфейсе сервиса. Он использует отчеты, просматривает графики, анализирует данные в таблицах, меняет параметры и показатели, накладывает фильтры, сравнивает сегменты и аудитории за разные диапазоны дат, и многое другое.
Однако работа в интерфейсе Яндекс Метрики возможна только с тем набором инструментов и опций, которые заложили в него сами разработчики Яндекса. Именно поэтому интернет-маркетологи и веб-аналитики используют в своей работе API. Для получения неагрегированных данных, так называемых «сырых данных» Яндекс Метрики, используется Logs API. Вы делаете запрос на те данные, которые хотите загрузить из Logs API, и вам выдается список тех посещений пользователей, которые были на вашем сайте. Можно увидеть Client ID посещения, время пребывания на сайте, просмотры страниц, регион пользователя, источник перехода и многое другое.
Как раз Logs API позволяет строить различные воронки, пути пользователей по вашему сайту, включая данные по страницам, карту событий/целей, по источникам трафика, проводить когортный анализ, создавать собственные модели атрибуции, прогнозировать будущие показатели на основе исторических данных счетчика Метрики и многое другое.
Итак, чтобы начать работу, вам необходимо зарегистрироваться в Yandex Cloud, настроить сообщество в DataSphere и привязать к нему платежный аккаунт. Перейдите по ссылке и нажмите на кнопку Попробовать бесплатно:
Войдите с помощью Яндекс ID или рабочего аккаунта в федерации (SSO). Поскольку мы будем работать с данными Яндекс Метрики, то используйте для входа свою учетную запись, на которой размещен счетчик.
Если вы впервые используете 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 Billing.
В завершение нажмите кнопку Создать. После этого вас перенаправит на страницу Биллинга, на которой будут отображены ваши платежные реквизиты. Убедитесь, что ваш аккаунт активен:
Привязка платежного аккаунта к DataSphere
Теперь вернитесь в Yandex DataSphere, перейдите в раздел Сообщества и выберите сообщество организации по умолчанию:
Привяжите платежный аккаунт к сообществу DataSphere, в котором вы будете работать. Для это прокрутите страницу чуть вниз и в блоке 1. Привяжите платежный аккаунт нажмите на кнопку Привязать:
В открывшемся окне выберите свой платежный аккаунт и нажмите Привязать:
Через некоторое время вы получите уведомление об успешной привязке платежного аккаунта к сообществу DataSphere.
Подключение ClickHouse
ClickHouse позволяет работать с неагрегированными статистическими данными Яндекс Метрики, полученными с помощью Logs API. Для этого вы можете арендовать место в Yandex Cloud. Но сразу хотелось бы сказать, что это решение является платным и будет зависеть от выбранного вами кластера и итоговой конфигурации (настроек).
Для работы с ClickHouse в Яндекс Облаке вам понадобится проект. Если вы никогда не работали в Yandex Cloud, то перейдите по ссылке и создайте свое первое облако. Выберите вашу организацию и введите название облака:
После его создания вас перенаправит в консоль управления:
Теперь в созданном каталоге для создания кластера ClickHouse выберите меню Managed Service for 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 необходимо к нему подключиться. Сделать это можно несколькими способами:
- через интернет, если вы настроили публичный доступ для нужного хоста. К таким хостам подключиться можно только используя SSL-соединение.
- с виртуальных машин Yandex Cloud, расположенных в той же облачной сети. Если к хосту нет публичного доступа, для подключения с таких виртуальных машин необязательно использовать SSL-соединение.
Примечание: если в вашем проекте Yandex Cloud уже есть созданные ранее сеть и подсети, вы можете пропустить этот шаг и сразу перейти к настройке групп безопасности. Если при создании каталога и кластера ClickHouse вы не добавляли их по умолчанию, то вам нужно сделать это.
Для этого перейдите в консоль управления своего проекта Yandex 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 и нажмите Enter:
1 2 3 4 5 6 7 8 9 10 11 |
mkdir -Force $HOME\.yandex; ` curl.exe https://storage.yandexcloud.net/cloud-certs/RootCA.pem ` --output $HOME\.yandex\RootCA.crt; ` curl.exe https://storage.yandexcloud.net/cloud-certs/IntermediateCA.pem ` --output $HOME\.yandex\IntermediateCA.crt; ` Import-Certificate ` -FilePath $HOME\.yandex\RootCA.crt ` -CertStoreLocation cert:\CurrentUser\Root; ` Import-Certificate ` -FilePath $HOME\.yandex\IntermediateCA.crt ` -CertStoreLocation cert:\CurrentUser\Root |
Этот код и другие для macOS и Linux можно скопировать из официальной документации Yandex Cloud.
В программе это будет выглядеть так:
После нажатия Enter высветиться окно с подтверждением установки сертификата. Нажмите Да:
После этого окно можно закрыть. Сертификаты будут сохранены на вашем компьютере:
- $HOME\.yandex\RootCA.crt
- $HOME\.yandex\IntermediateCA.crt
Воспользуйтесь поиском по файлам компьютера, чтобы найти сертификаты:
Запомните полный путь к корневому файлу RootCA.crt. В дальнейшем он вам пригодится при подключении к ClickHouse.
Подключение DataSphere
Yandex DataSphere — это сервис, который позволяет обучать ML-модели на конфигурациях с GPU и быстро запускать их в эксплуатацию. При этом не нужно самостоятельно создавать и обслуживать виртуальные машины: сервис «из коробки» выделяет необходимые конфигурации и легко настраивается.
Мы же можем использовать DataSphere как среду для разработки на Python от Yandex Cloud с интерфейсом Jupyter Notebook. В нем есть все нужные пакеты для анализа данных и машинного обучения (TensorFlow, Keras, PyTorch, NumPy и др.).
Откройте главную страницу DataSphere. В разделе Сообщества выберите сообщество, в котором вы хотите создать проект. В правом верхнем углу нажмите кнопку Создать проект:
В открывшемся окне укажите имя и (опционально) описание проекта. Требования к имени:
- Длина - от 3 до 63 символов;
- Может содержать строчные буквы латинского алфавита, цифры и дефисы;
- Первый символ - буква. Последний символ - не дефис.
Нажмите кнопку Создать. После создания проекта нажмите кнопку Открыть проект в JupyterLab:
Перед вами откроется среда разработки JupyterLab, в которой вы будете работать дальше:
DataSphere позволяет работать с проектом из любой локальной IDE, если она поддерживает работу с удаленными серверами Jupyter. По умолчанию эта возможность выключена, администратор сообщества может включить ее в настройках сообщества. Получить ссылку для подключения к проекту из IDE может только пользователь с ролью datasphere.community-projects.developer или выше.
Подробнее о том, как подключиться к JupyterLab проекта из локальной IDE (например, Visual Studio Code) читайте в официальной документации Yandex Cloud.
Создание приложения и получение токена доступа
Чтобы выгрузить данные из своего счетчика Яндекс Метрики, вам необходимо сначала создать приложение, а затем получить токен доступа. В рамках данного руководства я не буду подробно описывать эти шаги настройки. Вы можете прочитать две статьи ниже на контентной платформе lib.osipenvkov.ru:
В них подробно разобраны все этапы. В завершение не забудьте скопировать полученный токен доступа. После этого перейдите в Yandex DataSphere и в корневой директории создайте новый текстовый файл:
Назовите файл .yatoken.txt, в содержимое файла вставьте полученный токен доступа:
Сохраните изменения в файле. Если в директории вы не видите свой текстовый файл, то попробуйте включить скрытые файлы через меню View - Show Hidden Files:
Выгрузка данных через Logs API Яндекс Метрики
В корневой директории создайте новый файл, нажав правой кнопкой мыши и выбрав New Notebook, или через меню сверху - File - New - Notebook:
Переименуйте файл по своему усмотрению. Например, можно назвать его 1. get_data_logs_api. Откройте этот файл.
Вставьте в первую ячейку следующий код:
1 2 3 4 5 6 7 |
import requests import pandas as pd from io import StringIO import datetime import json from urllib.parse import urlencode import time |
Этот код импортирует несколько библиотек, которые часто используются в Python для работы с данными, веб-запросами и обработкой строк. Вот краткое описание каждой из них:
- requests - библиотека для выполнения HTTP-запросов. Она позволяет отправлять HTTP/1.1 запросы с использованием методов GET и POST;
- pandas - библиотека для анализа данных и манипуляций с ними. Она предоставляет структуры данных и функции для работы с таблицами и временными рядами;
- StringIO из модуля io - класс для работы с текстовыми данными в памяти, как если бы это был файл;
- datetime - модуль для работы с датами и временем;
- json - модуль для работы с JSON (JavaScript Object Notation) данными;
- urlencode из модуля urllib.parse - функция для кодирования параметров URL;
- time - модуль для работы с временем, предоставляет функции для измерения времени и задержек.
Затем создайте еще одну ячейку и продолжить добавлять нижеприведенный код.
Вставьте следующий код:
1 |
TOKEN = open('.yatoken.txt').read().strip() |
В переменную TOKEN мы помещаем путь к расположению нашего текстового файла .yatoken.txt с токеном.
Добавьте еще одну ячейку в программе для следующего кода:
1 2 3 4 5 6 7 |
API_HOST = 'https://api-metrika.yandex.ru' COUNTER_ID = 73226638 START_DATE = '2020-07-01' END_DATE = '2020-09-30' SOURCE = 'visits' API_FIELDS = ('ym:s:date', 'ym:s:dateTime', 'ym:s:startURL', 'ym:s:deviceCategory', 'ym:s:operatingSystemRoot', 'ym:s:clientID', 'ym:s:browser', 'ym:s:lastTrafficSource', 'ym:s:purchaseRevenue', 'ym:s:purchaseID') |
, где в переменной COUNTER_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:
Теперь поочередно в каждую ячейку вставьте следующий код:
1 2 3 |
header_dict = {'Authorization': f'OAuth {TOKEN}', 'Content-Type': 'application/x-yametrika+json' } |
Этот код создает словарь header_dict, который используется для хранения заголовков HTTP-запроса. Затем он может быть использован для аутентификации и указания типа содержимого.
Следующая ячейка с кодом:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
url_params = urlencode( [ ('date1', START_DATE), ('date2', END_DATE), ('source', SOURCE), ('fields', ','.join(API_FIELDS)) ] ) url = '{host}/management/v1/counter/{counter_id}/logrequests/evaluate?'\ .format(host=API_HOST, counter_id=COUNTER_ID) + url_params r = requests.get(url, headers = header_dict) |
Этот код формирует запрос к Logs API Яндекс Метрики, чтобы получить данные за указанный период и с указанными параметрами.
Далее (в отдельной ячейке):
1 |
r.status_code |
И в конце (последняя ячейка):
1 |
json.loads(r.text)['log_request_evaluation'] |
Сначала мы проверяем, можно ли в принципе создать такой запрос в Logs API. Для этого запустите выполнения вашей программы с помощью кнопки Run:
Вас попросят выбрать виртуальную машину (ВМ) и ее конфигурацию для выполнения программы. Поскольку нам нужно всего лишь выгрузить данные из Яндекс Метрики с помощью Logs API, то достаточно использовать самую простой и недорогой вариант. Нажмите Выбрать.
Yandex Cloud арендует для вас виртуальную машину с ее вычислительными мощностями. Затем вы сможете выполнить программу. Для этого поочередно нажмите кнопку Run или сочетание клавиш Shift + Enter, дойдя до последней ячейки программы. В конце вы должны увидеть статус ответа 200 и возможность создания запроса логов со значением true:
Это означает, что все хорошо, и теперь мы можем приступить к созданию основного запроса и выгрузке данных.
Продолжаем писать программу. Создайте новую ячейку в этом же файле и вставьте в нее следующий код:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
url_params = urlencode( [ ('date1', START_DATE), ('date2', END_DATE), ('source', SOURCE), ('fields', ','.join(sorted(API_FIELDS, key=lambda s: s.lower()))) ] ) url = '{host}/management/v1/counter/{counter_id}/logrequests?'\ .format(host=API_HOST, counter_id=COUNTER_ID) \ + url_params r = requests.post(url, headers=header_dict) |
Этот код формирует запрос к Logs API Яндекс Метрики, чтобы создать запрос на получение данных за указанный период и с указанными параметрами.
Затем четыре ячейки и четыре строчки кода. Первая:
1 |
r.status_code |
Вторая:
1 |
json.loads(r.text)['log_request'] |
Третья:
1 |
request_id = json.loads(r.text)['log_request']['request_id'] |
Четвертая:
1 |
request_id |
Результат в программе:
Запустите программу с первой ячейки после ее первого запуска. В самом конце вы должны получить идентификатор запроса логов (request_id):
Если это произошло, тогда вы можете двигаться дальше. Теперь необходимо дождаться окончания обработки запроса. Вставьте нижеприведенный код в следующую ячейку:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
status = 'created' while status == 'created': time.sleep(60) print('trying') url = '{host}/management/v1/counter/{counter_id}/logrequest/{request_id}' \ .format(request_id=request_id, counter_id=COUNTER_ID, host=API_HOST) r = requests.get(url, headers=header_dict) if r.status_code == 200: status = json.loads(r.text)['log_request']['status'] print(json.dumps(json.loads(r.text)['log_request'], indent = 4)) else: raise(BaseException(r.text)) |
Этот код периодически проверяет статус запроса логов, отправляя запросы к API Яндекс Метрики, пока статус не изменится с created.
Следующая строка программа такая:
1 |
json.loads(r.text)['log_request'] |
И в завершение:
1 2 |
parts = json.loads(r.text)['log_request']['parts'] parts |
Выполните эти три ячейки программы. Вы должны получить схожий результат:
Статус запроса processed означает, что он обработан и все хорошо. В параметре size вы можете увидеть итоговый размер выгружаемых данных из Яндекс Метрики, а в параметра part_number - количество частей, за которое они будут выгружены. Если 0, то всю статистику можно будет выгрузить за один раз, а не частями.
Создайте еще одну ячейку, в которую вставьте этот код на Python:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
tmp_dfs = [] for part_num in map(lambda x: x['part_number'], parts): url = '{host}/management/v1/counter/{counter_id}/logrequest/{request_id}/part/{part}/download' \ .format( host=API_HOST, counter_id=COUNTER_ID, request_id=request_id, part=part_num ) r = requests.get(url, headers=header_dict) if r.status_code == 200: tmp_df = pd.read_csv(StringIO(r.text), sep = '\t') tmp_dfs.append(tmp_df) else: raise(BaseError(r.text)) visits_df = pd.concat(tmp_dfs) |
Этот код загружает части логов, объединяет их в один датафрейм и сохраняет результат в переменной visits_df, поскольку сейчас мы работаем с данными на уровне визитов (visits).
Следующая строка программы:
1 |
visits_df.shape |
Этот код используется для получения размеров датафрейма. Он возвращает кортеж, содержащий количество строк и столбцов в DataFrame.
И финальная ячейка:
1 |
visits_df.to_csv('metrika_data_visits.csv', sep = '\t', index = False) |
Эта строка сохраняет DataFrame visits_df в файл CSV с именем metrika_data_visits.csv в корневой директории.
Выполните оставшуюся часть программы. Если все сделали правильно, то слева в директории вы увидите итоговый файл со своими данными Яндекс Метрики на уровне визитов:
Если вы хотите выгрузить статистику еще и на уровне просмотров (hits), то можете:
- скопировать файл со своей программой и выполнить те же шаги отдельно;
- просто продолжить текущую программу.
В новой ячейке текущей программы задайте:
1 2 3 |
SOURCE = 'hits' API_FIELDS = ('ym:pv:date', 'ym:pv:dateTime', 'ym:pv:URL', 'ym:pv:deviceCategory', 'ym:pv:operatingSystemRoot', 'ym:pv:clientID', 'ym:pv:browser', 'ym:pv:lastTrafficSource') |
Пусть это будет следующий набор полей (на уровне просмотров/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 - источник трафика (модель атрибуции - Последний переход);
Вы можете задать свои собственные поля, опираясь на список метрик из официальной документации.
Следующие ячейки идут те же самые, что и для запроса на уровне визитов. Просто вставьте по очереди:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
url_params = urlencode( [ ('date1', START_DATE), ('date2', END_DATE), ('source', SOURCE), ('fields', ','.join(sorted(API_FIELDS, key=lambda s: s.lower()))) ] ) url = '{host}/management/v1/counter/{counter_id}/logrequests?'\ .format(host=API_HOST, counter_id=COUNTER_ID) \ + url_params r = requests.post(url, headers=header_dict) |
Затем:
1 |
r.status_code |
Далее:
1 |
json.loads(r.text)['log_request'] |
После:
1 |
request_id = json.loads(r.text)['log_request']['request_id'] |
Получение идентификатора запроса:
1 |
request_id |
Так это выглядит в программе:
И затем вторая часть:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
status = 'created' while status == 'created': time.sleep(60) print('trying') url = '{host}/management/v1/counter/{counter_id}/logrequest/{request_id}' \ .format(request_id=request_id, counter_id=COUNTER_ID, host=API_HOST) r = requests.get(url, headers=header_dict) if r.status_code == 200: status = json.loads(r.text)['log_request']['status'] print(json.dumps(json.loads(r.text)['log_request'], indent = 4)) else: raise(BaseException(r.text)) |
Тот же самый код, который периодически проверяет статус запроса логов, отправляя запросы к API Яндекс Метрики, пока статус не изменится с created.
Следующая ячейка:
1 |
json.loads(r.text)['log_request'] |
Затем:
1 2 |
parts = json.loads(r.text)['log_request']['parts'] parts |
На уровне хитов статистика запроса может быть разбита на несколько частей, как в моем примере:
После этого выгружаем данные на уровне хитов:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
tmp_dfs = [] for part_num in map(lambda x: x['part_number'], parts): url = '{host}/management/v1/counter/{counter_id}/logrequest/{request_id}/part/{part}/download' \ .format( host=API_HOST, counter_id=COUNTER_ID, request_id=request_id, part=part_num ) r = requests.get(url, headers=header_dict) if r.status_code == 200: tmp_df = pd.read_csv(StringIO(r.text), sep = '\t') tmp_dfs.append(tmp_df) else: raise(BaseError(r.text)) hits_df = pd.concat(tmp_dfs) |
Аналогичный код для получения размеров датафрейма, только уже с другим названием (hits_df), отличающемся от visits_df данных, выгруженных на уровне визитов:
1 |
hits_df.shape |
И финальная строка с кодом сохранения данных в корневой директории файла metrika_data_hits в формате csv:
1 |
hits_df.to_csv('metrika_data_hits.csv', sep = '\t', index = False) |
Результат выполнения программы должен быть таким:
Поздравляю! Вы только что выгрузили сырые данные вашего счетчика Яндекс Метрики с нужным набором полей с помощью Logs API, кода Python и JupyterLab в Yandex DataSphere.
При желании вы можете скачать полученные файлы csv к себе на компьютер и посмотреть как выглядит ваша статистика. Для этого нажмите правой кнопкой мыши на каждый файл и выберите Download:
Файл будет скачан вам на компьютер. Открыть его можно с помощью обычного блокнота, Notepad++ или того же Microsoft Excel:
Разделите текст по столбцам, используя знак табуляции, чтобы было удобнее просматривать статистику:
Вот так выглядят данные вашего счетчика Яндекс Метрики "под капотом".
Теперь нам предстоит следующий шаг - загрузить данные в ClickHouse!
Загрузка данных в ClickHouse из DataSphere
В корневой директории создайте новый notebook. Дайте ему название - например, 2. upload_clickhouse:
В этом файле мы будем писать программу по загрузке данных в ClickHouse. Начинаем с импорта библиотек:
1 2 3 |
import os import pandas as pd import requests |
В программе это будет выглядеть так:
Далее создайте ячейку и добавьте туда следующий код:
1 2 |
hits_df = pd.read_csv('metrika_data_hits.csv', sep = '\t') visits_df = pd.read_csv('metrika_data_visits.csv', sep = '\t') |
, где вам необходимо переименовать имена файлов metrika_data_hits.csv и metrika_data_visits.csv на свои собственные. Если вы делали все по моей инструкции, то названия файлов будут таким. Также обратите внимание на то, что в этом руководстве я демонстрирую возможность загрузки данных как на уровне визитов (visits), так и на уровне хитов (hits). Если вы загружаете данные только для визитов или только для хитов, то скорректируйте код и удалите лишнюю строку из программы:
Этот код нужен для чтения данных из файлов, которые вы выгрузили на предыдущем шаге.
В корневой директории создайте новый текстовый файл с названием .chpass.txt. Запишите в него пароль заведенного пользователя, который вы задали при создании кластера ClickHouse (см. шаг выше, Настройки СУБД).
Сохраните и закройте файл.
Теперь загрузите SSL-сертификат в корневую директорию. Для этого нажмите на значок загрузки, найдите сертификат RootCA.crt на своем компьютере (см. выше) и добавьте его в проект.
Вернитесь в программу 2. upload_clickhouse и добавьте следующий код:
1 2 3 4 5 6 7 8 |
CH_HOST_NAME = 'хост' CH_USER = 'имя_пользователя' CH_DB_NAME = 'имя_бд' #------------------------------------------- CH_PASS = open('.chpass.txt').read().strip() CH_HOST = f'https://{CH_HOST_NAME}:8443' CH_CASERT = 'RootCA.crt' |
, где в CH_HOST_NAME вставьте имя хоста своего кластера ClickHouse. Найти его можно, перейдя в консоль управления и открыв сам кластер. Выберите из списка слева Хосты. На вкладке Обзор скопируйте имя хоста:
В переменные CH_USER и CH_DB_NAME вставьте имя пользователя и имя БД (см. выше, Настройки СУБД). Все остальное оставьте без изменений:
Скачайте файл some_funcs.py по ссылке и загрузите его в свой проект. Расширение файла должно быть .py:
В файле some_funcs есть класс simple_ch_client для работы с ClickHouse. Сначала надо создать экземпляр класса, инициализировав его начальными параметрами - хост, пользователь, пароль и путь к сертификату simple_ch_client(CH_HOST, CH_USER, CH_PASS, cacert).
В классе есть 4 метода:
- .get_version() - получает текущую версию ClickHouse. Хороший способ проверить, что указанные при инициализации параметры работают;
- .get_clickhouse_data(query) - выполняет запрос query и возвращает результат в текстовом формате;
- .get_clickhouse_df(query) - выполняет запрос query и возвращает результат в виде DataFrame;
- .upload(table, content) - загружает таблицу content, которая подается в текстовом формате в таблицу ClickHouse'а с именем table.
Используя заготовленные выше переменные, проверим доступ до сервера. Этот метод реализован в методе .get_version() класса для работы с ClickHouse. При успешном подключении не произойдет никакой ошибки при выполнении этого метода, и он сам вернет версию сервера ClickHouse (например 21.3.2.5).
Добавьте следующие строки кода:
1 2 |
import some_funcs from some_funcs import simple_ch_client |
Далее в отдельной ячейке:
1 |
my_client = simple_ch_client(CH_HOST, CH_USER, CH_PASS, CH_CASERT) |
И еще одна ячейка:
1 |
my_client.get_version() |
Выполните программу с самого начала. В конце вы должны получить версию вашего сервера ClickHouse. В моем примере - это 24.3.5.46:
Она должна совпадать с версией вашего кластера, которую можно посмотреть на вкладке Обзор в настройках ClickHouse:
После этого начнем загружать данные. Для этого проверим как они отображаются с помощью команды:
1 |
hits_df.head() |
Запустите все ячейки в этой программы, начиная с самой первой. В JupyterLab вы должны увидеть данные по хитам в виде датафрейма:
Создайте новую ячейку и вставьте туда следующий код:
1 2 3 4 5 6 7 8 |
hits_df.rename(columns={'ym:pv:browser':'Browser', 'ym:pv:clientID':'ClientID', 'ym:pv:date':'EventDate', 'ym:pv:dateTime':'EventTime', 'ym:pv:deviceCategory':'DeviceCategory', 'ym:pv:lastTrafficSource':'TraficSource', 'ym:pv:operatingSystemRoot':'OSRoot', 'ym:pv:URL':'URL'}, inplace = True) |
Этот код выполняет переименование столбцов, используя метод 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 отличается от примера в этом руководстве, то не забудьте скорректировать данный код под себя.
В новой ячейке программы добавьте этот код:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
q = f'drop table if exists {CH_DB_NAME}.hits ' my_client.get_clickhouse_data(q) q = f''' create table {CH_DB_NAME}.hits ( Browser String, ClientID UInt64, EventDate Date, EventTime DateTime, DeviceCategory String, TraficSource String, OSRoot String, URL String ) ENGINE = MergeTree() ORDER BY (intHash32(ClientID), EventDate) SAMPLE BY intHash32(ClientID) SETTINGS index_granularity=8192 ''' my_client.get_clickhouse_data(q) |
Этот код сначала удаляет таблицу hits, если она существует, а затем создает новую таблицу hits с заданной структурой в базе данных CH_DB_NAME. Не забудьте, что ваша итоговая структура полей и их типов может отличаться от данного примера. Ключевое, что необходимо будет исправить - это поля и их типы, ориентируясь на информацию из официальной документации Яндекса.
В завершение добавьте этот фрагмент:
1 2 3 |
my_client.upload( f'{CH_DB_NAME}.hits', hits_df.to_csv(index = False, sep = '\t')) |
Этот код преобразует данные из датафрейма в формат csv и загружает их в таблицу hits вашей базы данных ClickHouse.
Запустите программу. Если никаких ошибок в процессе не возникло, то в конце вы должны увидеть результат ' '
Это означает, что ваша таблица с данными Яндекс Метрики по хитам была загружена в ClickHouse. Вы можете убедиться в этом самостоятельно, перейдя в консоль управления вашим облаком и открыв кластер ClickHouse, кликнув на него. Выберите из списка слева WebSQL. Создайте новое подключение:
В открывшемся окне задайте имя подключения (например - connectdb), выберите свой кластер ClickHouse и укажите для него имя пользователя, пароль и имя базы данных (см. выше, Настройки СУБД):
В завершение нажмите кнопку Создать. После этого вас перенаправит в редактор запросов, в котором вы сможете посмотреть на структуру своей базы данных ClickHouse и увидеть таблицу по хитам, загруженную с помощью написанной нами программы на Python:
Чтобы загрузить таблица с визитами, вернитесь в JupyterLab и в программе 2. upload_clickhouse.ipynb добавьте новые ячейки с кодом:
1 |
visits_df.head() |
Теперь вы должны увидеть данные по визитам в виде датафрейма:
Следующая ячейка программы:
1 2 3 4 5 6 7 8 9 10 |
visits_df.rename(columns={'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'}, inplace = True) |
Этот код переименовывает столбцы в датафрейме следующим образом:
- 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 (идентификатор покупки), то в программу необходимо добавить такой код:
1 2 |
visits_df['Purchases'] = visits_df['Purchase.Revenue'].map(lambda x: x.count(',') + 1 if x != '[]' else 0) visits_df['Revenue'] = visits_df['Purchase.Revenue'].map(lambda x: sum(map(float, x[1:-1].split(','))) if x != '[]' else 0) |
Если коротко, то этот код создает два новых столбца в дат - Purchases, который содержит количество покупок, и Revenue, который содержит общую сумму дохода от покупок. Он также использует метод map для применения функции к каждому элементу соответствующего столбца. Функция удаляет квадратные скобки в начале и конце строки (x[1:-1]), разделяет строку по запятым и преобразует каждую часть в целое число, а затем суммирует эти числа. Если строка равна '[]', то значение в новом столбце будет 0. Таким образом, эти столбцы будут содержать общее количество покупок и общую сумму дохода от покупок.
В программе:
Поскольку в нашем датафрейме есть новые поля, то старые нужно удалить из таблицы с данными. За это отвечает следующий код:
1 |
visits_df.drop(columns=['Purchase.ID','Purchase.Revenue'], inplace=True) |
Теперь создаем таблицу для визитов в ClickHouse:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
q = f'drop table if exists {CH_DB_NAME}.visits ' my_client.get_clickhouse_data(q) q = f''' create table {CH_DB_NAME}.visits ( Browser String, ClientID UInt64, StartDate Date, StartTime DateTime, DeviceCategory UInt8, TraficSource String, OSRoot String, Purchases Int32, Revenue Double, StartURL String ) ENGINE = MergeTree() ORDER BY (intHash32(ClientID), StartDate) SAMPLE BY intHash32(ClientID) SETTINGS index_granularity=8192 ''' my_client.get_clickhouse_data(q) |
Этот фрагмент кода аналогичен предыдущему для хитов. Он сначала удаляет таблицу visits, если она существует, а затем создает новую таблицу visits с заданной структурой в базе данных CH_DB_NAME. Не забудьте, что ваша итоговая структура полей и их типов может отличаться от данного примера. Ключевое, что необходимо будет исправить - это поля и их типы, ориентируясь на информацию из официальной документации Яндекса.
В завершение добавьте этот фрагмент:
1 2 3 |
my_client.upload( f'{CH_DB_NAME}.visits', visits_df.to_csv(sep='\t', index =False)) |
Этот код преобразует данные из датафрейма в формат csv и загружает их в таблицу visits вашей базы данных ClickHouse.
Запустите программу. Если никаких ошибок в процессе не возникло, то в конце вы должны увидеть тот же самый результат ' '
Проверить корректность загрузки данных по визитам в ClickHouse можно аналогичным образом - перейдите в консоль управления вашим облаком и откройте свой кластер ClickHouse. Выберите из списка слева WebSQL.
Как видите, теперь в вашей базе данных ClickHouse две таблицы - одна загружена на уровне хитов, а другая на уровне визитов. Это и есть сырые данные вашего счетчика Яндекс Метрики, выгруженные с помощью Logs API и загруженные в кластер ClickHouse с помощью кода на Python в Yandex DataSphere. Теперь их можно визуализировать в Yandex DataLens, создавать QL-чарты, или, подключившись к ClickHouse с помощью специальной программы (например, DBeaver), работать с базой данных посредством SQL-команд.