Merge — Легкое объединение разных таблиц в CH

Пришел тут вопрос, на самом деле достаточно распространенный в физичных компаниях

Привет! а как мне в BI посмотреть данные по закупке товаров (это витрина закупок), движению между складами и потом по чекам туда же подтянуть продажи. На выходе хочу понимать, сколько где товаров сейчас осталось, куда их продали и все это в одном дэшике. Ну и чтобы по категориям можно было фильтровать.

Процессы

Задача бизнесово понятная, давайте разберем ее на кусочки процессов — и привяжем и к ним таблички

• Закупка это свой процесс, там всякие ФЗ могут быть, детализация по товару + поставщику, состояние закупки отдельный пункт, что-то может быть в пути, то есть по сути есть еще будущие даты;
• Остатки на складах — это другой процесс, считаем, что у нас есть остатки по дням и перемещения между складами и там же агрегированной суммой за день есть продажа
• Сами продажи, тут может быть много всякой атрибуции на продажу, агрегация идет по чекам, мы знаем, кто купил, куда дальше повезут, с какого склада взяли, тут опять же есть статус заказа — только вновь созданный еще не пройдет в остатках, а нам бы уже понимать, что будет с остатками послезавтра

Таблицы

Исходя из этих 3х процессов у нас будет 3 таблицы

purchase_orders — таблица с заявками на закупку

CREATE TABLE purchase_orders (
    date_creation Date,
    date_execution Date,
    order_id UInt64,
    event_dt Date DEFAULT date_execution,
    category String,
    contractor_name String,
    price UInt64,
    amount UInt64,
    warehouse_name String
) ENGINE = MergeTree()
ORDER BY event_dt;

Тут будут и даты создания заявки и дата исполнения, среди важных полей — категория товара, количество и склад

warehouse_movements — таблица с движениями остатков по складам

CREATE TABLE warehouse_movements (
    movement_date DATE,
    event_dt Date DEFAULT movement_date,
    warehouse_name String,
    category String,
    beginning_balance UInt64,
    ending_balance UInt64,
    movement_type String,
    movement_quantity UInt64
) order by event_dt;

ну и классическая табличка — sales — продажи наших товаров пользователям

CREATE TABLE sales (
    order_id UInt64,
    order_date Date,
    shipment_date Date,
    order_status String,
    user_name String,
    event_dt DateTime DEFAULT shipment_date,
    warehouse_name String,
    category String,
    amount UInt64,
    order_price UInt64
) ORDER BY event_dt;

Фишка в том, что часто это 3 разных Data Flow в процессах, разная зона ответственности, а вот в дэшах хочется смотреть всё сразу.

Создаем Merge-вьюху

И тут приходит на помощь мега крутая View — Merge таблица в ClickHouse, которая с версии 25.2 научилась хорошо обрабатывать несовпадающие поля между табличками. Сначала создадим эту табличку — Merge-движком

CREATE TABLE all_goods_movements
ENGINE = Merge(default, 'warehouse_movements|sales|purchase_orders');

Проверим, что у нас все в этой вьюхе хорошо:

SELECT _table, COUNT(1) FROM all_goods_movements GROUP BY 1;

{
«_table»: [«sales», «warehouse_movements», «purchase_orders»],
«count(1)»: [«20», «20», «20»]
}

Проверим, что работают фильтры

SELECT _table, COUNT(1) FROM all_goods_movements WHERE category = 'Категория 3' GROUP BY 1; --5,4,5

Теперь проверим, как работают поля, которые есть не во всех таблицах

SELECT _table, user_name FROM all_goods_movements WHERE category = 'Категория 3';

А что происходит с совпадающими полями — с ними всё хорошо, они в одной колонке сопоставились!

А ОПТИМАЛЬНО ЛИ?

И теперь главное поставим фильтр на user_name и посмотрим EXPLAIN

SELECT event_dt, user_name FROM all_goods_movements WHERE user_name = 'Жора' ;

Expression ((Project names + Projection))
  ReadFromMerge
    Expression (( + ( + )))
      Filter ((( + ( + )))[split])
        ReadFromMergeTree (default.purchase_orders)
    Expression (( + ( + )))
      Expression
        ReadFromMergeTree (default.sales)
        Indexes:
          PrimaryKey
            Condition: true
            Parts: 1/1
            Granules: 1/1
    Expression (( + ( + )))
      Filter ((( + ( + )))[split])
        ReadFromMergeTree (default.warehouse_movements)

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

ИТОГО

Можно объединять таблицы с разной гранулярностью, атрибутами, чтобы создавать единые бизнесовые метрики, не теряя при этом гибкости и не создавая бутылочных горлышков со стороны Дата-команды, пытаясь сделать одну витрину со всеми полями, даже если они будут null во всех других случаях.

Итоговый fiddle поиграться ТУТ