Сегодня разберем выгрузку очищенных данных на Core-уровень КХД. Сюда сохраняются данные, которые потом используются для построения витрин и моделей на уровне Data Mart.
КХД состоит из нескольких условных уровней:
Уровень Stage (опционально) — выгрузка данных из источника как есть — без трансформаций, чтобы дальнейшие процессы не дергали источники.
ETL-процессы — трансформации данных со Stage уровня и из источников, чтобы получить таблицы, из которых удобно собирать витрины/модели
Core уровень — хранение данных, полученных в ходе ETL-процессов.
Datamart уровень — тут формируются витрины и модели из таблиц Core уровня, к которым подключаются BI и другие потребители.
Аналитическое хранилище
Как вы знаете, Loginom предоставляет большое количество возможностей по трансформации, очистке и обогащению данных («Генеральная уборка данных»). Однако, чтобы эти данные стали частью КХД, недостаточно просто взять и выгрузить их в базу. Нам нужно, чтобы таблицы на Core-уровне были совместимы друг с другом, буквально как кубики конструктора или части мозаики.
Такая «квадратная» совместимость важна для того, чтобы при росте количества таблиц сохранялась единая логика связей, и мы могли легко изменять модели данных. Более того, только такой подход является реально масштабируемым и позволяет не закапываться в бесконечных исправлениях «уникально созданных» моделей.
Сбор модели данных
Для наполнения КХД мы приготовили набор таблиц следующей структуры (в таком виде они будут лежать на Core-уровне, до организации в витрину или «Звезду»).
Структура таблиц
В него входят:
Разделение на таблицы показателей и справочников условно. Таблица показателей обычно включает поля для расчетов (меры, measures; например, «Выручка» в таблице продаж) и даты, к которым привязаны эти данные. В ней также есть ключевые поля, которые связывают данные со справочными таблицами.
Справочники содержат поля, которые на стороне аналитических систем становятся измерениями (еще их называют разрезы, аналитики, dimensions). По значениям этих полей на визуализациях разбиваются результаты агрегаций. Например, в диаграмме, на которой показывается разбивка выручки по клиентам, поле «Клиент» является измерением.
При этом таблицы показателей могут содержать свои собственные поля измерений (когда эти поля не целесообразно выносить в отдельный справочник, т.к. они используются только для этой таблицы). А в условном справочнике клиентов может появиться поле «Дата создания», и вот уже мы можем посчитать показатель «Количество новых клиентов», посчитав идентификаторы клиентов в разрезе дат их создания.
В нашем подходе на уровне КХД таблицы справочников и показателей ничем технически не отличаются. Это просто общепринятое обозначение для прояснения основной роли таблицы.
Откройте сценарий второго дня в папке M3\Data Monetization Pack\Examples. Скачайте пакет в папку.
LGP-файл
Откройте пакет.
Для справки: когда вы создаете сценарий выгрузки данных в КХД, в вашем пакете должны находиться ссылки на 3 набора компонентов:
Пакет
В сценарии находится подмодель для очистки КХД. Можете запустить ее, если запутаетесь и захотите сбросить вашу базу к исходному состоянию, и проделать все операции с чистого листа.
Очистка КХД
Обычно в ETL-сценариях для КХД подготовка каждой таблицы происходит внутри ее подмодели. Зайдем в подмодель Sales plan, чтобы увидеть детали процесса.
Подмодель Sales plan
Импорт LGD-файла заменяет нам ETL-процесс, который происходит на этапах 1-2 схемы потока данных. В реальном сценарии тут скорее всего была бы подмодель с ETL-процессом.
Замена подмодели с ETL-процессом
Разберем, как должна выглядеть правильно подготовленная таблица для core-уровня КХД.
Откроем предпросмотр данных из LGD-файла плана продаж.
Предпросмотр данных
Технически, можно сохранить в БД любые данные. Но если мы хотим, чтобы в дальнейшем из них было легко собрать витрины/модели, в которые в будущем можно легко добавить другие таблицы, нужно соблюсти следующий набор правил.
Нейминг полей — ключевые поля
Ключевые поля — это такие поля, по которым строится связь между таблицами, позволяя делать вычисления по полям одной таблицы в разрезе полей другой таблицы.
Нейминг полей — поля данных:
Меры определяются добавлением «_M» в конец метки поля. Каноническая дата определяется через добавление «_CD» в конец метки поля. Каноническая дата — термин из моделирования данных. Обозначает единую ось данных в модели/витрине, вокруг которой собираются показатели из разных таблиц, и их можно анализировать на одной временной оси (например, план и факт продаж). Одна таблица может содержать несколько канонических дат. Подробнее разберем эту тему позже.
Все поля, не являющиеся ключами, мерами или каноническими датами, становятся измерениями. Во время сборки витрины/модели, измерения могут быть связаны по ключевым полям с другими таблицами. Давая таким образом анализировать показатели одних таблиц в разрезе полей из других таблиц.
Форматы дат:
Особенность потока превращения данных в аналитику в том, что преобразования данных могут происходить на каждом из этапов пути.
_0.svg)
Поток данных
Можно посчитать все данные на стороне источника (особенно если это что-то вроде 1С), выгрузить это в BI и визуализировать простейшими формулами.
А можно взять самые кошмарные данные, загрузить их сразу в BI, написав там сложный ETL-процесс, трехэтажные формулы, и получить аналогичный результат.
А еще можно создавать новые аналитические признаки на этапе формирования модели/витрины. В общем, можно посчитать много что и много где. Как же в итоге лучше распределить на потоке преобразования вычисления, что и где лучше считать?
Вот некоторые рекомендации:
1. Если какой-то расчетный показатель может быть в готовом виде получен из источника — лучше взять его из источника (например, расчетные показатели из 1С вроде валовой прибыли);
2. Чем больше показателей будет посчитано на уровне Core, тем проще вам будет это визуализировать дальше, т.к. не придется полагаться на возможности выражений BI-системы. Ваши данные должны быть подготовлены на таком уровне, чтобы 90% показателей считалось максимум формулами вроде sum_if и ее аналогами.
Почему? Потому что чем больше специфических функций используется на стороне визуализации данных, тем менее универсальным является КХД, и тем больше вы привязаны к конкретному инструменту аналитики. Чем больше показателей и логики рассчитывается на core-уровне КХД, тем проще ее переиспользовать в любых инструментах. И к этому нам и нужно стремиться, но без фанатизма.
3. Расчеты, для которых требуется высокий уровень интерактивности, и которые нельзя заранее рассчитать на core-уровне, имеет смысл делать на стороне BI. Например, показатель «Средний чек», который считается как сумма продаж, деленная на количество продаж.
В аналитике этот показатель может требоваться выводить в самых разных разрезах с самыми разными фильтрами. Посчитать это на уровне таблицы данных заранее в таком количестве вариантов невозможно. Поэтому такие вещи смело считаем на стороне BI.
Результат ETL-процесса должен быть сохранены в Clickhouse, и за это отвечает компонент «📁 CH: Выгрузка в КХД».
Замена подмодели с ETL-процессом
Этот компонент — часть библиотеки Clickhouse Kit.
Clickhouse Kit
Т.к. библиотеки из Data Monetization Pack содержат множество компонентов, которые, в том числе, должны комбинироваться друг с другом для решения рабочих задач, для упрощения работы используются готовые шаблоны сценариев (отмечены значком папки). Такие компоненты добавляются в сценарий как производный узел, т.е. подмодель в которую можно зайти, увидеть готовую схему связей компонентов, и донастроить ее под ваш сценарий.
Зайдем в подмодель «📁 CH: Выгрузка в КХД».
Подмодель
Активируем компонент «Аудит таблицы». Он используется для проверки, все ли учтено для сохранения таблицы в core-уровень.
Компонент «Аудит таблицы
Первый порт пропускает входные данные без изменений.
Второй порт содержит метаданные, т.е. описание полей таблицы, которые будут использоваться для формирования интерактивной документации и автоматизаций КХД.
Часть метаданных формируется автоматически, а другая часть (в т.ч. произвольное описание полей) задается вами в настройках аудита (про них поговорим дальше).
Метаданные
Третий порт содержит результаты аудита. Если там есть строки с красным крестиком, значит не все было правильно подготовлено к сохранению в core-уровень. В данном случае, проблема в заполнении обязательных параметров.
Результаты аудита
Откроем второй порт переменных на входе в аудит.
Второй порт переменных
Заполните указанные параметры в соответствие с примером:
Полученные данные
Сохраните настройки, запустите аудит повторно и откройте третий выходной порт. Вы должны увидеть отсутствие красных крестиков. Лампочка обозначает ситуацию, которая не является критической ошибкой, но влияет на то, как данные этой таблицы могут быть использованы в дальнейшем. Просто убедитесь, что пункты с лампочкой существуют в соответствие с вашей задумкой.
Проверьте также второй выходной порт — вы должны увидеть новые атрибуты в метаданных, которые появляются только при отсутствии проблем в аудите.
Второй выходной порт
После успешного аудита может быть активирован узел сохранения метаданных. Если в аудите есть критические ошибки, сохранение не сработает, и дальнейший процесс выгрузки данных не будет произведен. Метаданные сохраняются в папке библиотеки DMP в виде LGD-файлов.
Узел сохранения метаданных
Чтобы данные могли быть выгружены в базу, в ней должна быть создана принимающая таблица. Компонент «CH: Создать / адаптировать MT таблицу» автоматически создает таблицу на основе полей данных.
Принимающая таблица
Данный компонент является универсальным и может использоваться для создания таблиц в Clickhouse для произвольных задач. Разберем настройки этого компонента в порту переменных.
Прежде всего, здесь нужно указать базу данных и таблицу, которая будет принимать данные. Если их не существует — они будут созданы автоматически. Т.к. компонент используется в рамках преднастроенной схемы, в нем уже указана принимающая БД (временная база для core-уровня) и название таблицы (на основе значения, указанного в аудите).
Данные
Далее, идет набор специфичных для Clickhouse настроек. Краткая справка по основам хранения данных в Clickhouse:
У каждой таблицы есть движок. Движок определяет способ хранения данных и логику отработки запросов. Основной движок, использующий преимущества СУБД Clickhouse — MergeTree() и его разновидности. Он установлен в настройках по умолчанию.
Фишка Clickhouse — в быстрых выборках больших объемов данных. Однако, чтобы этот функционал работал более оптимально, для таблицы можно задать ряд дополнительных настроек:
Поле Партиции. Партиция — это поле, по которому идет разбивка на большие блоки данных с целью их быстрой переброски между разными таблицами. Имеет смысл использовать при наличии в партициях от 100 000 строк. Раньше имели большую роль в оптимизации чтения, но теперь эта роль сместилась в параметр Order By, указываемый при создании таблицы.
Поля сортировки Order By — основной способ ускорения чтения данных из MergeTree таблиц. Здесь обычно указывают набор полей, по которым происходит группировка данных при запросах из таблицы. При этом поля указываются в порядке возрастания частотности значений (количество уникальных значений или кардинальность).
Обычно это важно для таблиц, к которым идут запросы вида Group by (здесь перечисляются наиболее часто используемые поля в Group by), что довольно редко происходит для таблиц core-уровня, т.к. они используются для сборок витрин в виде материализованных таблиц на базе select-запросов. А вот к витринам уже идут group by запросы из BI. Таким образом, без особой необходимости для таблиц core-уровня здесь ничего можно не писать.
Однако, этот параметр является обязательным для создания таблицы. Поэтому оставим его пустым, таблица будет создана с сортировкой order by Tuple(), что эквивалентно сортировке по всем полям в порядке их создания. Для более оптимальной работы нужно, чтобы поля были расставлены в порядке возрастания частотности значений.
Чтобы не заниматься расстановкой порядка полей вручную (определяется порядком на входном порту), можно активировать опцию «Порядок полей по частотности при создании таблицы».
Тогда перед созданием таблицы будет проведен анализ частотности значений (может занять некоторое время), и порядок определится автоматически.
Поля первичного ключа — это не тот первичный ключ, о котором вы подумали (и он не имеет отношения к первичному ключу, указанному в аудите). В Clickhouse есть разновидность MergeTree движков, которые умеют агрегировать данные в фоновом режиме. Делают они это в разрезе полей, указанных в Order By. Например, в таблицу пишется информация из чековой ленты, разбитая по товарам внутри чека (т.е. по несколько строк на 1 чек). Но за счет указания Order By вида Город, Магазин, Дата, ИД чека и использования движка SummingMergeTree, данные суммируются на уровне чека.
Однако, такой Order By не является оптимальным с точки зрения использования group by запросов, т.к. в нем содержится слишком детализированное поле ИД чека. И вот для этих случаев используется параметр PrimaryKey — в него можно дописать часть полей, которые уже используются в Order By. Например, только Город, Магазин, Дата. Это сделает работу group by запросов более оптимальной.
В целом для сохранения данных в core-уровень КХД вы можете держать все эти настройки пустыми. Они нужны прежде всего для специальных таблиц-витрин, к которым обращаются аналитические инструменты.
Параметр Nullable_mode (0 — все nullable, 1 — все не nullable, 2 — автоопределение) отвечает за то, могут ли принимать поля таблицы значения NULL или нет. При создании таблицы, для каждого поля может быть определена эта опция. Nullable-поля работают менее оптимально по сравнению с не-nullable с точки зрения скорости выполнения запросов. Но при этом нет смысла не использовать их, если такова логика данных (например, отсутствующее значение в ключевых полях все-таки имеет смысл хранить как null, чтобы не получить искусственный пустой ключ, на который будут завязаны все несвязанные данные).
Режим «2 — автоопределение» задает атрибут Nullable/не Nullable при создании таблицы в зависимости от наличия null-значений в текущем наборе данных.
Удалить и пересоздать. Если принимающая таблица уже существует в БД, то будет проведена проверка соответствия сохраняемой таблицы из Loginom и принимающей таблицы. При обнаружении расхождений будет выполнен запрос ALTER TABLE для того, чтобы принимающая таблица соответствовала по структуре сохраняемой. Возможные изменения: добавление полей, удаление полей, смена типов данных в поле.
Иногда возможны ситуации, когда изменение таблицы через запрос ALTER TABLE невозможно (например, вы переименовали все поля в сохраняемой таблицы, и теперь для актуализации нужно удалить все поля в принимающей таблице и создать новые с новыми именами, или происходит конвертация несовместимых типов данных). Тогда этот компонент будет возвращать ошибку и останавливать процесс сохранения данных. Чтобы исправить ситуацию, таблицу нужно пересоздать с нуля. Для этого можно активировать данную опцию и выполнить компонент. Не забудьте потом отключить ее, чтобы таблица не пересоздавалась при каждой активации компонента.
После активации компонента, на выходе окажутся:
1. Ваши данные, которые проходят насквозь без изменений;
2. Выполнение запроса DESCRIBE TABLE к целевой таблице показывает, что она реально существует в БД.
Выполнение запроса
3. Отчет о результатах активации компонента. Там в числе прочего можно увидеть действия, выполненные над таблицей и запрос (создание/изменение/ничего). А также шаблоны запросов Select с подстановкой алиасов и без — для быстрого использования в других местах.
Отчет
Далее отрабатывает стандартный узел экспорта в БД. В нем можно настроить способ обновления данных (частичная / полная перезапись).
Экспорт в БД
За счет того, что в сопоставлении полей включена автосинхронизация, вам не нужно настраивать его вручную. Кроме того, при изменении структуры таблицы изменение сопоставления полей будет происходить автоматически.
Автосинхронизация
Финальный этап — публикация таблицы. Этот компонент мгновенно переносит таблицу из текущего положения в новое («Старая БД».«Старая таблица» > «Новая БД. Новая таблица»).
Публикация таблицы
В случае сценария выгрузки в КХД, это означает перенос из временного core-уровня в рабочий. Этот процесс необходим, чтобы во время длительных процессов записи данных не допустить чтения неполных данных из рабочей базы. Также у компонента есть опция — создать клон публикуемой таблицы в старом расположении. Таким образом, можно одновременно иметь 2 версии таблицы, тестовую и рабочую. При этом процесс публикации данных можно отключить в настройках компонента, чтобы данные обновлялись только в тестовой таблице.
Для эксперимента можете подать данные на выгрузку через параметры полей (данные подаются не из Sales plan, а через параметры полей. Набор полей меняется на входе в компонент и дальше автоматически меняется в БД, позволяя принять новую таблицу), и активировать выгрузку в КХД.
Параметры полей
«CH: Создать / адаптировать MT таблицу» на сообщение о том, что выполнен запрос на изменение принимающей таблицы.
Публикация таблицы
Выполнение запроса
Переключите подачу данных на выгрузку обратно на LGD-файл и повторно выполните выгрузку.
Вернитесь на исходный сценарий и выполните всю цепочку обновления данных (там везде внутри все по аналогии с разобранным примером Sales plan).
Обновление данных
Добавьте в сценарий импорт из базы данных на основе готового подключения Clickhouse ADWH.
Импорт из базы данных
В настройках узла импорта подтвердите наличие девяти таблиц во временной и рабочей core-базах.
Настройка узла
В производных компонентах, в разделе Data_Preparation_Kit, добавьте в сценарий компонент «3.1. Список таблиц (мета-справочник)».
Добавление компонента
Оцените собранный справочник. В нем должно быть 9 таблиц. Напишите в чат, как бы вы могли использовать эти данные в своей работе?
Справочник
Скачайте и установите Loginom Community Edition, если он еще не установлен.
