motor de tabela CollapsingMergeTree - ClickHouse Documentation

Descrição

O motor CollapsingMergeTree herda de MergeTree e adiciona lógica para colapsar linhas durante o processo de mesclagem. O motor de tabela CollapsingMergeTree exclui (colapsa) de forma assíncrona pares de linhas se todos os campos da chave de ordenação (ORDER BY) forem equivalentes, exceto o campo especial Sign, que pode ter valor 1 ou -1. As linhas sem um par com valor oposto de Sign são mantidas. Para mais detalhes, consulte a seção Collapsing deste documento.

Este motor pode reduzir significativamente o volume de armazenamento, aumentando, consequentemente, a eficiência das consultas SELECT.

Parâmetros

Todos os parâmetros deste motor de tabela, com exceção do parâmetro Sign, têm o mesmo significado que em MergeTree.

Sign — O nome dado a uma coluna que indica o tipo de linha, em que 1 é uma linha de “estado” e -1 é uma linha de “cancelamento”. Tipo: Int8.

Criando uma tabela

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
)
ENGINE = CollapsingMergeTree(Sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

Para uma descrição dos parâmetros de consulta, consulte a descrição da consulta.
Ao criar uma tabela CollapsingMergeTree, são exigidas as mesmas cláusulas da consulta usadas na criação de uma tabela MergeTree.

Collapsing

Dados

Considere a situação em que você precisa salvar dados que mudam continuamente de um determinado objeto. Pode parecer lógico ter uma linha por objeto e atualizá-la sempre que algo mudar, porém, operações de atualização são caras e lentas para o SGBD, porque exigem a regravação dos dados no armazenamento. Se precisarmos gravar dados rapidamente, realizar um grande número de atualizações não é uma abordagem aceitável, mas sempre podemos gravar sequencialmente as alterações de um objeto. Para isso, usamos a coluna especial Sign.

Se Sign = 1, isso significa que a linha é uma linha de “estado”: uma linha que contém campos que representam o estado válido atual.
Se Sign = -1, isso significa que a linha é uma linha de “cancelamento”: uma linha usada para cancelar o estado de um objeto com os mesmos atributos.

Por exemplo, queremos calcular quantas páginas os usuários visitaram em um site e por quanto tempo permaneceram nelas. Em um determinado momento, gravamos a seguinte linha com o estado da atividade do usuário:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Em um momento posterior, registramos a mudança na atividade do usuário e a gravamos nas duas linhas a seguir:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

A primeira linha cancela o estado anterior do objeto (neste caso, representando um usuário). Ela deve copiar todos os campos da chave de ordenação da linha “cancelada”, exceto Sign. A segunda linha acima contém o estado atual. Como precisamos apenas do estado mais recente da atividade do usuário, a linha original de “estado” e a linha de “cancelamento” que inserimos podem ser excluídas, como mostrado abaixo, colapsando o estado inválido (antigo) de um objeto:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │ -- old "state" row can be deleted
│ 4324182021466249494 │         5 │      146 │   -1 │ -- "cancel" row can be deleted
│ 4324182021466249494 │         6 │      185 │    1 │ -- new "state" row remains
└─────────────────────┴───────────┴──────────┴──────┘

CollapsingMergeTree realiza precisamente esse comportamento de colapso enquanto ocorre a mesclagem das partes de dados.

O motivo pelo qual duas linhas são necessárias para cada alteração é discutido em mais detalhes no parágrafo Algoritmo.

As particularidades dessa abordagem

O programa que escreve os dados deve se lembrar do estado de um objeto para poder cancelá-lo. A linha de “cancelamento” deve conter cópias dos campos da chave de ordenação do “estado” e o Sign oposto. Isso aumenta o tamanho inicial do armazenamento, mas permite gravar os dados rapidamente.
Arrays longos e que continuam crescendo nas colunas reduzem a eficiência do motor devido ao aumento da carga de gravação. Quanto mais simples forem os dados, maior será a eficiência.
Os resultados de SELECT dependem fortemente da consistência do histórico de alterações do objeto. Seja preciso ao preparar os dados para inserção. Dados inconsistentes podem gerar resultados imprevisíveis. Por exemplo, valores negativos para métricas não negativas, como profundidade de sessão.

Algoritmo

Quando o ClickHouse mescla partes, cada grupo de linhas consecutivas com a mesma chave de ordenação (ORDER BY) é reduzido a no máximo duas linhas: a linha de “estado” com Sign = 1 e a linha de “cancelamento” com Sign = -1. Em outras palavras, no ClickHouse as entradas são colapsadas. Para cada parte de dados resultante, o ClickHouse salva:


1.	A primeira linha de “cancelamento” e a última linha de “estado”, se o número de linhas de “estado” e de “cancelamento” for igual e a última linha for uma linha de “estado”.
2.	A última linha de “estado”, se houver mais linhas de “estado” do que de “cancelamento”.
3.	A primeira linha de “cancelamento”, se houver mais linhas de “cancelamento” do que de “estado”.
4.	Nenhuma linha, em todos os outros casos.

Além disso, quando há pelo menos duas linhas de “estado” a mais do que linhas de “cancelamento” ou pelo menos duas linhas de “cancelamento” a mais do que linhas de “estado”, a mesclagem continua. No entanto, o ClickHouse trata essa situação como um erro lógico e a registra no log do servidor. Esse erro pode ocorrer se os mesmos dados forem inseridos mais de uma vez. Assim, o colapsamento não deve alterar os resultados do cálculo de estatísticas. As alterações são gradualmente colapsadas para que, no fim, reste apenas o último estado de quase todos os objetos. A coluna Sign é necessária porque o algoritmo de mesclagem não garante que todas as linhas com a mesma chave de ordenação estarão na mesma parte de dados resultante, nem mesmo no mesmo servidor físico. O ClickHouse processa consultas SELECT com múltiplas threads e não pode prever a ordem das linhas no resultado. A agregação é necessária quando se precisa obter dados totalmente “colapsados” da tabela CollapsingMergeTree. Para concluir o colapsamento, escreva uma consulta com a cláusula GROUP BY e funções de agregação que levem o sinal em conta. Por exemplo, para calcular a quantidade, use sum(Sign) em vez de count(). Para calcular a soma de alguma coisa, use sum(Sign * x) junto com HAVING sum(Sign) > 0 em vez de sum(x) como no exemplo abaixo. As funções de agregação count, sum e avg podem ser calculadas dessa forma. A função de agregação uniq pode ser calculada se um objeto tiver pelo menos um estado não colapsado. As funções de agregação min e max não podem ser calculadas porque o CollapsingMergeTree não salva o histórico dos estados colapsados.

Se você precisar extrair dados sem agregação (por exemplo, para verificar se há linhas cujos valores mais recentes correspondem a determinadas condições), pode usar o modificador FINAL para a cláusula FROM. Ele mesclará os dados antes de retornar o resultado. Para CollapsingMergeTree, apenas a linha de estado mais recente de cada chave é retornada.

Exemplos

Exemplo de uso

Considere os dados de exemplo a seguir:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Vamos criar a tabela UAct usando o CollapsingMergeTree:

CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

Em seguida, vamos inserir alguns dados:

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1)

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1)

Usamos duas consultas INSERT para criar duas partes de dados diferentes.

Se inserirmos os dados com uma única consulta, o ClickHouse criará apenas uma parte de dados e nunca fará nenhuma mesclagem.

Podemos selecionar os dados usando:

SELECT * FROM UAct

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Vamos dar uma olhada nos dados retornados acima e ver se houve colapso… Com duas consultas INSERT, criamos duas partes de dados. A consulta SELECT foi executada em dois threads, e obtivemos uma ordem aleatória das linhas. No entanto, o colapso não ocorreu porque ainda não havia acontecido nenhuma mesclagem das partes de dados e o ClickHouse faz mesclagem das partes de dados em segundo plano, em um momento desconhecido que não podemos prever. Portanto, precisamos de uma agregação, que realizamos com a função de agregação sum e a cláusula HAVING:

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘

Se não precisarmos de agregação e quisermos forçar a operação de colapso, também podemos usar o modificador FINAL na cláusula FROM.

SELECT * FROM UAct FINAL

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Essa forma de selecionar os dados é menos eficiente e não é recomendada para grandes volumes de dados lidos (milhões de linhas).

Exemplo de outra abordagem

A ideia por trás dessa abordagem é que as mesclagens levem em conta apenas os campos-chave. Na linha “cancelamento”, portanto, podemos especificar valores negativos que compensam a versão anterior da linha ao somar, sem usar a coluna Sign. Para este exemplo, usaremos os dados de exemplo abaixo:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │        -5 │     -146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Para essa abordagem, é necessário alterar os tipos de dados de PageViews e Duration para armazenar valores negativos. Por isso, alteramos os tipos dessas colunas de UInt8 para Int16 ao criar nossa tabela UAct usando a collapsingMergeTree:

CREATE TABLE UAct
(
    UserID UInt64,
    PageViews Int16,
    Duration Int16,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

Vamos testar a abordagem inserindo dados na nossa tabela. No entanto, para exemplos ou tabelas pequenas, isso é aceitável:

INSERT INTO UAct VALUES(4324182021466249494,  5,  146,  1);
INSERT INTO UAct VALUES(4324182021466249494, -5, -146, -1);
INSERT INTO UAct VALUES(4324182021466249494,  6,  185,  1);

SELECT * FROM UAct FINAL;

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

SELECT
    UserID,
    sum(PageViews) AS PageViews,
    sum(Duration) AS Duration
FROM UAct
GROUP BY UserID

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘

SELECT COUNT() FROM UAct

┌─count()─┐
│       3 │
└─────────┘

OPTIMIZE TABLE UAct FINAL;

SELECT * FROM UAct

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

​Descrição

​Parâmetros

​Criando uma tabela

​Collapsing

​Dados

​Algoritmo

​Exemplos

​Exemplo de uso

​Exemplo de outra abordagem

Descrição

Parâmetros

Criando uma tabela

Collapsing

Dados

Algoritmo

Exemplos

Exemplo de uso

Exemplo de outra abordagem