Пошаговая запись материализованного представления clickhouse

задняя часть

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

CREATE MATERIALIZED VIEW dwst.tt (
`sort_key` UInt8,
 `id` UInt64,
 `type` UInt8,
 `is_profit_avg` UInt8,
 `bd1` UInt64,
 `bd2` UInt64,
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{ck_cluster}/dwst/tt', '{replica}') PARTITION BY sn_sort_key ORDER BY (id, type, bd1, bd2) SETTINGS index_granularity = 8192 AS 
SELECT halfMD5(id) % 64 AS sn_sort_key, id,  type, 
multiIf(((sum(v1) - sum(v2)) < 0, 2, 1) AS is_profit_avg, bd1, bd2 FROM dwst.base_detail  WHERE date <=(today() - 10) GROUP BY sort_key,id,type,bd1,
	bd2

В целях безопасности некоторые реквизиты убраны, общий смысл агрегировать id, type, bd1, bd2 на основе базовой таблицы base_detail, и выгодно ли это на t-10, т.к. объем данных базовой таблицы относительно большой, я хочу использовать материализованные представления для предварительного вычисления данных и сокращения времени на запросы SQL; В процессе практики мы обнаружили две проблемы, а также получили более глубокое понимание материализованного представления clickhouse.

Проблема 1: общее количество данных в каждом представлении несовместимо

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

    Просмотрев кликхаузофициальный выпуск, чтобы увидеть, есть ли похожие проблемы, и я нашел две похожие проблемыDuplicated data in MATERIALIZED VIEW Duplicated primary key in materialized view, официальное объяснение следующее

    Data blocks are deduplicated. For multiple writes of the same data block (data blocks of the same size containing the same rows in the same order), the block is only written once. 
The reason for this is in case of network failures when the client application doesn’t know if the data was written to the DB, so the INSERT query can simply be repeated.
 It doesn’t matter which replica INSERTs were sent to with identical data. 
 INSERTs are idempotent. Deduplication parameters are controlled by merge_tree server settings.

大概的意思 clickhouse insert 语句是幂等的，在对于同一个data block进行写操作的时候，由于网络原因，客户端应用不确认数据已经被写入了，所以就出现了重复插入的问题

好吧，建议采用以下的解决方案：

*   使用子查询对于重复的数据进行二次加工，进行去重（官方推荐）

*   使用ReplicatedReplacingMergeTree 执行引擎进行数据的去重，这是我在实践中想采用的，每次使用插入完数据之后，通过optimize table 的方式，对数据进行去重；

Проблема 2: Количество строк для каждой прибыли несовместимо

对于数据进行去重之后，我发现数据的总数是准确的，但是每次的is_profit_avg的总数却不是一致的，这使我有点恼火了；后面通过查找官方的文档

A materialized view is implemented as follows: when inserting data to the table specified in `SELECT`, 
part of the inserted data is converted by this `SELECT` query, and the result is inserted in the view.

Important
Materialized views in ClickHouse are implemented more like insert triggers. If there’s some aggregation in the view query, it’s applied only to the batch of freshly inserted data.
 Any changes to existing data of source table (like update, delete, drop partition, etc.) doesn’t change the materialized view.</pre>

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

A `SELECT` query can contain `DISTINCT`, `GROUP BY`, `ORDER BY`, `LIMIT`… Note that the corresponding conversions are performed independently on each block of inserted data.
   For example, if `GROUP BY` is set, data is aggregated during insertion, but only within a single packet of inserted data. 
The data won’t be further aggregated. The exception is when using an `ENGINE` that independently performs data aggregation, such as `SummingMergeTree`.

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

Суммировать

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

Ссылка на ссылку

1:Duplicated data in MATERIALIZED VIEW

2:Duplicated primary key in materialized view

3: Data Replication

4: CREATE VIEW

Оригинальная ссылка:Woohoo.Краткое описание.com/Fear/Beethoven 7 раундов 258-х…

Категории