Partições de tabela

O que são as partições de tabela no ClickHouse

​

O que são partições de tabela no ClickHouse?

As partições agrupam as partes de dados de uma tabela na família de motores MergeTree em unidades lógicas organizadas — uma forma de organizar os dados que faz sentido do ponto de vista conceitual e se alinha a critérios específicos, como intervalos de tempo, categorias ou outros atributos-chave. Essas unidades lógicas facilitam o gerenciamento, a consulta e a otimização dos dados.

​

PARTITION BY

O particionamento pode ser habilitado quando uma tabela é inicialmente definida por meio da cláusula PARTITION BY. Essa cláusula pode conter uma expressão SQL em quaisquer colunas, cujos resultados definirão a qual partição uma linha pertence.

Para ilustrar isso, expandimos a tabela de exemplo O que são partes de tabela adicionando uma cláusula PARTITION BY toStartOfMonth(date), que organiza as partes de dados da tabela de acordo com os meses das vendas de imóveis:

CREATE TABLE uk.uk_price_paid_simple_partitioned
(
    date Date,
    town LowCardinality(String),
    street LowCardinality(String),
    price UInt32
)
ENGINE = MergeTree
ORDER BY (town, street)
PARTITION BY toStartOfMonth(date);

Você pode consultar esta tabela no nosso Playground SQL do ClickHouse.

​

Estrutura no disco

Sempre que um conjunto de linhas é inserido na tabela, em vez de criar (com pelo menos) uma única parte de dados contendo todas as linhas inseridas (como descrito aqui), o ClickHouse cria uma nova parte de dados para cada valor único de chave de partição entre as linhas inseridas:

O servidor ClickHouse primeiro divide, pelo valor da chave de partição toStartOfMonth(date), as linhas do exemplo de inserção com 4 linhas ilustrado no diagrama acima. Em seguida, para cada partição identificada, as linhas são processadas como de costume, executando várias etapas sequenciais (① Ordenação, ② Divisão em colunas, ③ Compressão, ④ Gravação em disco).

Observe que, com o particionamento habilitado, o ClickHouse cria automaticamente índices MinMax para cada parte de dados. Eles são simplesmente arquivos de cada coluna da tabela usada na expressão de chave de partição, contendo os valores mínimo e máximo dessa coluna dentro da parte de dados.

​

Mesclagens por partição

Com o particionamento habilitado, o ClickHouse só faz mesclagem de partes de dados dentro das partições, mas não entre partições. Isso é ilustrado na nossa tabela de exemplo acima:

Como ilustrado no diagrama acima, partes pertencentes a partições diferentes nunca são mescladas. Se for escolhida uma chave de partição com alta cardinalidade, as partes distribuídas por milhares de partições nunca serão candidatas à mesclagem, ultrapassando os limites pré-configurados e causando o temido erro Too many parts. Resolver esse problema é simples: escolha uma chave de partição adequada com cardinalidade abaixo de 1000..10000.

​

Monitorando partições

Você pode consultar a lista de todas as partições únicas existentes da nossa tabela de exemplo usando a coluna virtual _partition_value:

Como alternativa, o ClickHouse rastreia todas as partes e partições de todas as tabelas na tabela de sistema system.parts, e a consulta a seguir retorna, para a nossa tabela de exemplo, a lista de todas as partições, além do número atual de partes ativas e da soma de linhas nessas partes para cada partição:

​

Para que servem as partições de tabela?

​

Gerenciamento de dados

No ClickHouse, o particionamento é, прежде de tudo, um recurso de gerenciamento de dados. Ao organizar os dados logicamente com base em uma expressão de partição, cada partição pode ser gerenciada de forma independente. Por exemplo, o esquema de particionamento da tabela de exemplo acima permite cenários em que apenas os últimos 12 meses de dados são mantidos na tabela principal, com os dados mais antigos sendo removidos automaticamente por meio de uma regra de TTL (veja a última linha adicionada na instrução DDL):

CREATE TABLE uk.uk_price_paid_simple_partitioned
(
    date Date,
    town LowCardinality(String),
    street LowCardinality(String),
    price UInt32
)
ENGINE = MergeTree
PARTITION BY toStartOfMonth(date)
ORDER BY (town, street)
TTL date + INTERVAL 12 MONTH DELETE;

Como a tabela é particionada por toStartOfMonth(date), partições inteiras (conjuntos de partes de tabela) que atendem à condição de TTL serão descartadas, tornando a operação de limpeza mais eficiente, sem precisar reescrever as partes.

Da mesma forma, em vez de excluir dados antigos, eles podem ser movidos automaticamente e com eficiência para uma camada de armazenamento mais econômica:

CREATE TABLE uk.uk_price_paid_simple_partitioned
(
    date Date,
    town LowCardinality(String),
    street LowCardinality(String),
    price UInt32
)
ENGINE = MergeTree
PARTITION BY toStartOfMonth(date)
ORDER BY (town, street)
TTL date + INTERVAL 12 MONTH TO VOLUME 'slow_but_cheap';

​

Otimização de consultas

As partições podem ajudar no desempenho das consultas, mas isso depende muito dos padrões de acesso. Se as consultas atingirem apenas algumas partições (idealmente uma), o desempenho pode melhorar. Em geral, isso só é útil se a chave de particionamento não estiver na chave primária e você estiver filtrando por ela, como mostra a consulta de exemplo abaixo.

A consulta é executada na nossa tabela de exemplo acima e calcula o maior preço entre todos os imóveis vendidos em Londres em dezembro de 2020, filtrando tanto por uma coluna (date) usada na chave de particionamento da tabela quanto por uma coluna (town) usada na chave primária da tabela (e date não faz parte da chave primária).

O ClickHouse processa essa consulta aplicando uma sequência de técnicas de poda para evitar avaliar dados irrelevantes:

① Poda de partições: Índices MinMax são usados para ignorar partições inteiras (conjuntos de partes) que, logicamente, não podem corresponder ao filtro da consulta em colunas usadas na chave de particionamento da tabela.

② Poda de grânulos: Para as partes de dados restantes após a etapa ①, seu índice primário é usado para ignorar todos os grânulos (blocos de linhas) que, logicamente, não podem corresponder ao filtro da consulta em colunas usadas na chave primária da tabela.

Podemos observar essas etapas de poda de dados inspecionando o plano físico de execução da nossa consulta de exemplo acima por meio de uma cláusula EXPLAIN :

EXPLAIN indexes = 1
SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE date >= '2020-12-01'
  AND date <= '2020-12-31'
  AND town = 'LONDON';

    ┌─explain──────────────────────────────────────────────────────────────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))                                                                    │
 2. │   Aggregating                                                                                                │
 3. │     Expression (Before GROUP BY)                                                                             │
 4. │       Expression                                                                                             │
 5. │         ReadFromMergeTree (uk.uk_price_paid_simple_partitioned)                                              │
 6. │         Indexes:                                                                                             │
 7. │           MinMax                                                                                             │
 8. │             Keys:                                                                                            │
 9. │               date                                                                                           │
10. │             Condition: and((date in (-Inf, 18627]), (date in [18597, +Inf)))                                 │
11. │             Parts: 1/436                                                                                     │
12. │             Granules: 11/3257                                                                                │
13. │           Partition                                                                                          │
14. │             Keys:                                                                                            │
15. │               toStartOfMonth(date)                                                                           │
16. │             Condition: and((toStartOfMonth(date) in (-Inf, 18597]), (toStartOfMonth(date) in [18597, +Inf))) │
17. │             Parts: 1/1                                                                                       │
18. │             Granules: 11/11                                                                                  │
19. │           PrimaryKey                                                                                         │
20. │             Keys:                                                                                            │
21. │               town                                                                                           │
22. │             Condition: (town in ['LONDON', 'LONDON'])                                                        │
23. │             Parts: 1/1                                                                                       │
24. │             Granules: 1/11                                                                                   │
    └──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

A saída acima mostra:

① Poda de partições: As linhas 7 a 18 da saída de EXPLAIN acima mostram que o ClickHouse primeiro usa o índice MinMax do campo date para identificar 11 dos 3257 grânulos existentes (blocos de linhas) armazenados em 1 das 436 partes de dados ativas existentes que contêm linhas correspondentes ao filtro date da consulta.

② Poda de grânulos: As linhas 19 a 24 da saída de EXPLAIN acima indicam que o ClickHouse então usa o índice primário (criado sobre o campo town) da parte de dados identificada na etapa ① para reduzir ainda mais o número de grânulos (que potencialmente também contêm linhas correspondentes ao filtro town da consulta) de 11 para 1. Isso também se reflete na saída do cliente ClickHouse que exibimos mais acima para a execução da consulta:

... Elapsed: 0.006 sec. Processed 8.19 thousand rows, 57.34 KB (1.36 million rows/s., 9.49 MB/s.)
Peak memory usage: 2.73 MiB.

Isso significa que o ClickHouse varreu e processou 1 grânulo (bloco de 8192 linhas) em 6 milissegundos para calcular o resultado da consulta.

​

O particionamento é principalmente um recurso de gerenciamento de dados

Esteja ciente de que consultar todas as partições normalmente é mais lento do que executar a mesma consulta em uma tabela não particionada.

Com o particionamento, os dados geralmente ficam distribuídos em mais partes de dados, o que muitas vezes leva o ClickHouse a examinar e processar um volume maior de dados.

Podemos demonstrar isso executando a mesma consulta tanto na tabela de exemplo What are table parts (sem particionamento habilitado) quanto na nossa tabela de exemplo atual acima (com particionamento habilitado). Ambas as tabelas contêm os mesmos dados e o mesmo número de linhas:

No entanto, a tabela com particionamento habilitado tem mais partes de dados ativas porque, como mencionado acima, o ClickHouse só faz mesclagem de partes de dados dentro das partições, e não entre partições:

Como mostrado acima, a tabela particionada uk_price_paid_simple_partitioned tem mais de 600 partições e, portanto, 600.306 partes de dados ativas. Já na nossa tabela não particionada uk_price_paid_simple, todas as partes de dados iniciais puderam ser mescladas em uma única parte ativa por mesclagens em segundo plano.

Quando verificamos o plano físico de execução da consulta com uma cláusula EXPLAIN para a nossa consulta de exemplo acima, sem o filtro de partição, executada sobre a tabela particionada, podemos ver nas linhas 19 e 20 da saída abaixo que o ClickHouse identificou 671 dos 3257 grânulos existentes (blocos de linhas), distribuídos em 431 das 436 partes de dados ativas existentes, que potencialmente contêm linhas que correspondem ao filtro da consulta e, portanto, serão examinados e processados pelo mecanismo de execução de consultas:

EXPLAIN indexes = 1
SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE town = 'LONDON';

    ┌─explain─────────────────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))                       │
 2. │   Aggregating                                                   │
 3. │     Expression (Before GROUP BY)                                │
 4. │       Expression                                                │
 5. │         ReadFromMergeTree (uk.uk_price_paid_simple_partitioned) │
 6. │         Indexes:                                                │
 7. │           MinMax                                                │
 8. │             Condition: true                                     │
 9. │             Parts: 436/436                                      │
10. │             Granules: 3257/3257                                 │
11. │           Partition                                             │
12. │             Condition: true                                     │
13. │             Parts: 436/436                                      │
14. │             Granules: 3257/3257                                 │
15. │           PrimaryKey                                            │
16. │             Keys:                                               │
17. │               town                                              │
18. │             Condition: (town in ['LONDON', 'LONDON'])           │
19. │             Parts: 431/436                                      │
20. │             Granules: 671/3257                                  │
    └─────────────────────────────────────────────────────────────────┘

O plano físico de execução da mesma consulta de exemplo, executada na tabela sem partições, mostra nas linhas 11 e 12 da saída abaixo que o ClickHouse identificou 241 dos 3083 blocos de linhas existentes na única parte de dados ativa da tabela que potencialmente contêm linhas que correspondem ao filtro da consulta:

EXPLAIN indexes = 1
SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple
WHERE town = 'LONDON';

    ┌─explain───────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))             │
 2. │   Aggregating                                         │
 3. │     Expression (Before GROUP BY)                      │
 4. │       Expression                                      │
 5. │         ReadFromMergeTree (uk.uk_price_paid_simple)   │
 6. │         Indexes:                                      │
 7. │           PrimaryKey                                  │
 8. │             Keys:                                     │
 9. │               town                                    │
10. │             Condition: (town in ['LONDON', 'LONDON']) │
11. │             Parts: 1/1                                │
12. │             Granules: 241/3083                        │
    └───────────────────────────────────────────────────────┘

Ao executar a consulta na versão particionada da tabela, o ClickHouse varre e processa 671 blocos de linhas (~ 5,5 milhões de linhas) em 90 milissegundos:

SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE town = 'LONDON';

┌─highest_price─┐
│     594300000 │ -- 594,30 milhões
└───────────────┘

1 row in set. Elapsed: 0.090 sec. Processed 5.48 million rows, 27.95 MB (60.66 million rows/s., 309.51 MB/s.)
Peak memory usage: 163.44 MiB.

Já ao executar a consulta na tabela não particionada, o ClickHouse varre e processa 241 blocos (~ 2 milhões de linhas) em 12 milissegundos:

SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple
WHERE town = 'LONDON';

┌─highest_price─┐
│     594300000 │ -- 594,30 milhões
└───────────────┘

1 row in set. Elapsed: 0.012 sec. Processed 1.97 million rows, 9.87 MB (162.23 million rows/s., 811.17 MB/s.)
Peak memory usage: 62.02 MiB.