Приведенное ниже решение описывает интеграцию и работу с данными Метрики в Yandex Cloud. При создании инструкции использованы материалы с сайтов Яндекс Метрики¹,² и Yandex Cloud³.

• 2023-12-05 v01 Первая редакция

1. Yandex Data Transfer для передачи данных Метрики в облако
2. Managed Service for ClickHouse в качестве буферной зоны и источника данных для Datalens
3. Yandex Datalens для визуализации данных
4. Yandex Object Storage для хранения данных в файловом формате
5. Yandex Query для ad-hoc анализа данных при помощи SQL-запросов

1. Настройка инфраструктуры
   1. Подготовка кластера ClickHouse
   2. Настройка endpoint'ов подключения к Метрике и ClickHouse
   3. Настройка и активация трансфера
2. Визуализация данных Метрики в Datalens
3. Выгрузка данных в Yandex Object Storage (S3)
4. Работа с данными Метрики при помощи Yandex Query

Для быстрой настройки инфраструктуры вы можете воспользоваться скриптами из каталога terraform. В terraform-скриптах описана вся инфраструктура за исключением endpoint'а Метрики и самого трансфера, котоые на момент публикации (декабрь 2023) не поддержаны в провайдере, и их потребуется создать через веб-интерфейс.

Если у вас нет кластера ClickHouse, вы можете создать его в вашем облаке при помощи terraform-скрипта, через утилиту YC и UI-консоль Yandex Cloud. Подробное описание работы с кластерами ClickHouse приведены на странице управляемого сервиса ClickHouse⁴.

Создайте endpoint'ы подключения для источника Метрика:

- и приемника Clickhouse:

Источник Метрика поставляет 2 таблицы - с визитами (visits) и хитами (hits). Стоит обратить внимание, что в случае отсутствия таблиц в приемнике Data Transfer создаст таблицу самостоятельно в соответствии с настройками шардирования приемника. Если вы хотите изменить топологию таблиц, то после создания трансфера вы можете сразу деактивировать его и модифицировать структуру таблиц, а в свойствах endpoint'а укажите политику очистки "Truncate" или "Не очищать". Пример определений (DDL) таблиц визитов и хитов находится в каталоге ddl.

Трансфер из Метрики работает в режиме репликации, и мы рекомендуем выбирать политику "Не очищать", т.к. если вы по какой-то причине захотите деактивировать трансфер, то при повторной активации данные предыдущего запуска сохранятся.

Создайте трансфер

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

В качестве примера рассмотрим описанные на сайте Метрики² типовые запросы к данным. Необходимо создать подключение к кластеру ClickHouse, после чего можно приступить к созданию QL-чартов.

/*
https://yandex.ru/support/metrica/pro/data-work.html#data-work__traffic

- не забыть указать корректное имя таблицы в своей БД
- id счетчика можно убрать
- на вкладке параметр создать параметр с именем "interval" и типом date-interval
*/ 

SELECT StartDate AS `ym:s:date`, 
        sum(Sign) AS `ym:s:visits` -- правильное коллапсирование нескольких версий визита в самую последнюю и актуальную, и подсчет количества визитов
from 
metrica_copy.visits_<id трансфера>
as `default.visits_all` 
WHERE `ym:s:date` >= {{interval_from}} -- исторические данные до момента создания коннектора в данной версии не поддерживаются
        and `ym:s:date` <= {{interval_to}} -- данные за "сегодня" (и медленные обновления за более поздние дни, например, оффлайн конверсии) могут доезжать с опозданием относительно интерфейса
GROUP BY `ym:s:date` 
WITH TOTALS  
HAVING `ym:s:visits` >= 0.0 
ORDER BY `ym:s:date` ASC 
limit 0,10

/*
https://yandex.ru/support/metrica/pro/data-work.html#data-work__utm

- не забыть указать корректное имя таблицы в своей БД
- id счетчика можно убрать
- на вкладке Параметры создать параметр с именем "interval" и типом date-interval
*/ 

SELECT
    `TrafficSource.UTMSource`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignUTMSource`,
    sum(Sign) AS `ym:s:visits`,
    least(uniqExact(CounterUserIDHash), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * Sign) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * Sign) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * Sign) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`,
    sumArray(arrayMap(x -> (if(isFinite(x), x, 0) * Sign), arrayMap(x_0 -> toInt64(notEmpty(x_0)), `EPurchase.ID`))) AS `ym:s:ecommercePurchases`
FROM metrica_copy.visits_<id трансфера>  -- сюда вставить свою базу и свою таблицу визитов
WHERE (StartDate >= {{interval_from}})
        AND (StartDate <= {{interval_to}} ) 
        AND (`ym:s:lastSignUTMSource` != '')
GROUP BY `ym:s:lastSignUTMSource`
HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.) OR (`ym:s:ecommercePurchases` > 0.)
ORDER BY
    `ym:s:visits` DESC,
    `ym:s:lastSignUTMSource` ASC
LIMIT 0, 50

Добавьте чарты на дашборд и заведите параметр с именем "interval" (Вкладка "Ручной ввод", тип "Календарь" и флажок "Диапазон"):

Для возможности выгрузки данных в Object Storage необходимость настроить доступ из кластера Clickhouse⁵.

После настройки доступа вы сможете выгружать данные в объектное хранилище при помощи встроенного в Clickhouse табличного движка S3:

/* создание S3-таблицы. подставтье свои значения для id кластера, id трансфера и имя S3 bucket'а */

create table metrica.hits_s3 on cluster <id кластера> as hits_<id трансфера>
ENGINE = S3('https://storage.yandexcloud.net/<имя s3 bucket-а>/metrica/hits/hits.csv.gz',
 'CSVWithNames', 'gzip')
SETTINGS input_format_with_names_use_header = 1;


/* вставка данных в s3-таблицу */
insert into hits_s3 settings s3_create_new_file_on_insert=1 select * from hits_<id трансфера> where EventDate=cast('2023-11-01' as date);
insert into hits_s3 settings s3_create_new_file_on_insert=1 select * from hits_<id трансфера> where EventDate=cast('2023-11-02' as date);
insert into hits_s3 settings s3_create_new_file_on_insert=1 select * from hits_<id трансфера> where EventDate=cast('2023-11-03' as date);

/* проверим пути выгруженных файлов */
select _path, _file, EventDate from hits_s3 where EventDate=cast('2023-11-01' as date) limit 1
union all
select _path, _file, EventDate from hits_s3 where EventDate=cast('2023-11-02' as date) limit 1
union all
select _path, _file, EventDate from hits_s3 where EventDate=cast('2023-11-03' as date) limit 1

/* сравним исходную и S3-таблицу */
select 's3' as storage, count(1) as cnt from hits_s3
union all 
select 'ch' as storage, count(1) as cnt from hits_<id трансфера> where EventDate between cast('2023-11-01' as date) and cast('2023-11-03' as date);

storage|cnt    |
-------+-------+
ch     |3902732|
s3     |3902732|

Помимо явной выгрузки в Object Storage с возможностью последующей работы с данными при помощи других инструментов, в Yandex Cloud поддержан режим гибридного хранилища для Managed ClickHouse. Настроив автоматический перенос данных в гибридное хранилище при помощи конструкции TTL⁶ в определении таблицы, вы сможете расширить объем доступного дискового пространства для кластера ClickHouse объектным хранилищем, причем по более низкой цене, чем стандартные диски.

Yandex Query (YQ) - это serverless-движок для работы с данными, находящимися во внешнем объектном хранилище или во внешней БД (на декабрь 2023 поддерживаются PostgreSQL и ClickHouse⁷ и PostgreSQL⁸), в том числе с возможностью выполнения федеративных запросов над данными из разных источников.

При помощи YQ можно выполнять как аналитическую обработку данных, так и потоковую обработку данных (из Yandex Data Streams (YDS)).

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

• Хранение данных в Object Storage
• Оплата только за потребление ресурсов во время выполнения запросов
• Web UI со встроенным учебником
• Поддержка Yandex Query как источника для Datalens⁷

Ниже мы рассмотрим простой сценарий обогащения данных Метрики из ClickHouse с сохранением результата запроса в файл в Object Storage.

При обращении к данным через Yandex Query необходимо описать схему колонок и типов. Сделать это можно как напрямую в SQL-запросе при помощи конструкции WITH .. SCHEMA (..) ⁹, так посредством механизма привязки (binding).

Для демонстрации работы Yandex Query с объектным хранилищем создадим соединение¹⁰, которое понадобится нам в дальнейшем, и привязку¹¹ к каталогу с файлами ранее выгруженной из ClickHouse таблицы хитов:

(в таблице большое количество колонок, для демонстрации зададим не все)

SELECT
    `CounterID`,
    `EventDate`,
    `CounterUserIDHash`,
    `UTCEventTime`,
    `WatchID`,
    `AdvEngineID`,
    `AdvEngineStrID`,
    `BrowserCountry`,
    `BrowserEngineID`,
    `BrowserEngineStrID`,
    `BrowserEngineVersion1`,
    `URL`
FROM `metrica-hits-s3`
LIMIT 10;

Выполним аналогичный запрос без привязки - напрямую из соединения с заданием схемы прямо в запросе:

SELECT
    `CounterID`,
    `EventDate`,
    `CounterUserIDHash`,
    `UTCEventTime`,
    `WatchID`,
    `AdvEngineID`,
    `AdvEngineStrID`,
    `BrowserCountry`,
    `BrowserEngineID`,
    `BrowserEngineStrID`,
    `BrowserEngineVersion1`,
    `URL`
FROM `<id подключения к Object Storage>`.`/metrica/hits/hits*.csv.gz`
    WITH
    (
        format = csv_with_names,
        compression = gzip,
        Schema =
        (
            CounterID UInt32 Not null, 
            EventDate date not null,
            CounterUserIDHash uint64 not null,
            UTCEventTime datetime not null,
            WatchID uint64,
            AdvEngineID uint16,
            AdvEngineStrID string,
            BrowserCountry string, 
            BrowserEngineID uint16,
            BrowserEngineStrID string,
            BrowserEngineVersion1 uint16,
            URL string
        )
    ) 
LIMIT 10;

Теперь создадим соединение к Managed ClickHouse:

/* Выполним проверочный запрос к таблице хитов */
    select * from metrica.`hits_<id трансфера>`
    limit 10;

Загрузим следующий версионный справочник в виде CSV-файла в объектное хранилище и создадим привязку:

"BrowserCountry","BrowserCountryDesc","FromDT","ToDT"
"ru","Russian Fed.","2023-01-01","2023-11-01"
"ru","Russian Federation","2023-01-02","2099-12-31"

Сохраним в CSV-файл результат федеративной выборки, в которой соединяются таблица фактов из ClickHouse с загруженным в объектное хранилище справочником:

/* Вставка в файл выборки федеративным запросом */
insert into `<имя привязки>`.`/metrica/yq/`
    WITH
    (
        format='csv_with_names'
    )
SELECT
    f.`BrowserCountry`,
    f.`EventDate`,
    d.`BrowserCountryDesc`,
    count(1) as cnt
FROM `metrica-hits-s3` f
join   `dim_browser_country` d
on f.`BrowserCountry` == d.`BrowserCountry`
where f.`BrowserCountry`='ru'
and f.`EventDate` >= d.`FromDT` and f.`EventDate` <= d.`ToDT`
group by
    f.`BrowserCountry`,
    f.`EventDate`,
    d.`BrowserCountryDesc`
order by f.`EventDate`

Помимо записи через соединение, возможна также запись данных через привязку¹².

Datalens поддерживает Yandex Query как источник¹³. Т.о. мы можем создавать визуализации на основе данных из Object Storage, а также данных из федеративных запросов к нескольким источникам, используя движок YQ для их выполнения.

Ваши предложения по модификации сценария вы можете направить через pull request.

Для вопросов, пожеланий и консультаций по сервисам платформы данных Yandex Cloud: группа https://t.me/YandexDataPlatform в Telegram