Crie sua primeira projeção - ClickHouse Documentation

Analytics em Tempo RealArmazenamento de DadosObservabilidadeIA/MLCloudOSS

Pré-requisitos

To successfully follow this guide, you’ll need the following:

A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

Você também deve ter concluído os seguintes guias de início rápido, pois este guia se baseia diretamente na tabela uk_price_paid e nos conceitos apresentados neles:

O que você vai criar

No guia de início rápido do MergeTree, você viu que fazer consultas em uk_price_paid por town ou county exige uma varredura completa da tabela, porque ela está ordenada por (postcode, addr1, addr2). Neste guia de início rápido, você resolverá esse problema criando uma projeção — uma representação adicional ordenada dos seus dados armazenada dentro da mesma tabela. Ao contrário das visões materializadas, as projeções não exigem uma tabela de destino separada, permanecem sincronizadas com as mutações (exclusões e atualizações) e são usadas de forma transparente pelo otimizador de consultas — ou seja, você continua consultando a mesma tabela. Ao final, você entenderá como adicionar e materializar uma projeção, como o ClickHouse a seleciona automaticamente e quando escolher projeções em vez de visões materializadas.

Entenda por que você precisa de uma projeção

Sua tabela uk_price_paid está ordenada por (postcode, addr1, addr2). Isso significa que o ClickHouse pode pular grandes blocos de dados quando você filtra por postcode, addr1 ou addr2, mas consultas que filtram por town precisam varrer todas as linhas — 30 milhões ao todo.Uma projeção armazena uma cópia adicional ordenada de (algumas ou todas as) colunas dentro da mesma tabela. Quando você consulta a tabela, o otimizador de consultas verifica automaticamente se ler da projeção exigiria acessar menos grânulos do que os dados base e, se for o caso, a utiliza de forma transparente.Principais diferenças em relação a visões materializadas:

Sem tabela separada - a projeção fica dentro da própria uk_price_paid
Otimização transparente de consultas - você consulta uk_price_paid normalmente; o ClickHouse escolhe a projeção automaticamente
Permanece sincronizada com mutações - exclusões e atualizações aplicadas à tabela são refletidas na projeção

Adicione uma projeção à sua tabela

Defina uma projeção em uk_price_paid que armazene town, date, price e type, ordenada por (town, date):

ALTER TABLE uk_price_paid
    ADD PROJECTION uk_price_paid_by_town
    (
        SELECT town, date, price, type
        ORDER BY (town, date)
    );

Isso registra a projeção nos metadados da tabela, mas não a materializa para os dados existentes - apenas as inserções futuras vão preenchê-la.Verifique se a projeção aparece na definição da tabela:

SHOW CREATE TABLE uk_price_paid;

Você deverá ver o bloco PROJECTION uk_price_paid_by_town na saída.

Materialize a projeção para os dados existentes

Assim como as visões materializadas, uma projeção recém-adicionada só se aplica a inserções futuras. Para preenchê-la com os 30 milhões de linhas que já estão na tabela, materialize-a explicitamente:

ALTER TABLE uk_price_paid
    MATERIALIZE PROJECTION uk_price_paid_by_town;

Ela é executada como uma mutação em segundo plano. Você pode acompanhar seu progresso:

SELECT
    mutation_id,
    command,
    is_done
FROM system.mutations
WHERE table = 'uk_price_paid'
ORDER BY create_time DESC
LIMIT 5;

Quando is_done = 1, a projeção está totalmente materializada. Você também pode confirmar isso consultando system.projection_parts:

SELECT
    name,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.projection_parts
WHERE table = 'uk_price_paid'
  AND active = true
GROUP BY name;

Consulte a tabela e observe o uso automático da projeção

Agora execute uma consulta com filtro por town — na mesma tabela que você sempre consultou:

SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC;

Verifique as estatísticas da consulta — muito menos linhas são lidas em comparação com antes de a projeção existir, porque o ClickHouse escolheu automaticamente ler da projeção uk_price_paid_by_town em vez de varrer os dados da tabela base.Você pode confirmar que a projeção foi usada com EXPLAIN:

EXPLAIN
SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC;

Procure por ReadFromMergeTree na saída, fazendo referência ao nome da projeção. Se quiser comparar o desempenho explicitamente, você pode desativar a otimização de projeção para uma única consulta:

SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC
SETTINGS optimize_use_projections = 0;

Isso força uma varredura completa da tabela para que você possa ver a diferença no número de linhas lidas.

Compare projeções com visões materializadas

Projeções e visões materializadas resolvem o mesmo problema — leituras mais rápidas em padrões de acesso alternativos —, mas envolvem trade-offs diferentes. Em resumo, as projeções são melhores quando você só precisa de uma ordenação diferente sobre os mesmos dados; visões materializadas são mais flexíveis quando você precisa transformar, agregar ou direcionar dados para um schema diferente. Para uma comparação detalhada, consulte Visões materializadas versus projeções.

Observe a sobrecarga no armazenamento

As projeções armazenam uma segunda cópia das colunas selecionadas na mesma tabela, aumentando o uso de espaço em disco. Consulte system.parts para ver o tamanho total de uk_price_paid (que agora inclui os dados da projeção):

SELECT
    table,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS compressed_size
FROM system.parts
WHERE table = 'uk_price_paid'
  AND active = true
GROUP BY table;

Você também pode verificar o armazenamento da projeção:

SELECT
    name,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS projection_size
FROM system.projection_parts
WHERE table = 'uk_price_paid'
  AND active = true
GROUP BY name;

Este é o mesmo trade-off fundamental das visões materializadas: mais espaço em disco em troca de leituras mais rápidas. A projeção pode ser menor do que uma cópia completa porque inclui apenas as quatro colunas selecionadas, e a compressão varia conforme a ordem de ordenação.

Próximos passos

Neste guia de início rápido, você adicionou uma projeção a uk_price_paid que armazena os dados ordenados por (town, date), permitindo consultas rápidas por cidade sem criar uma tabela separada. Você aprendeu que as projeções são escolhidas automaticamente pelo otimizador de consultas, permanecem sincronizadas com as mutações e trocam espaço em disco por desempenho de leitura. Confira a seguir os próximos guias de início rápido: Ou aprofunde-se com a documentação de referência:

ClickHouse Academy — Master ClickHouse with expert-designed training for every skill level — Check out the ClickHouse academy for on-demand and live training

Todos os guias de início rápido

​Pré-requisitos

​O que você vai criar

​Entenda por que você precisa de uma projeção

​Adicione uma projeção à sua tabela

​Materialize a projeção para os dados existentes

​Consulte a tabela e observe o uso automático da projeção

​Compare projeções com visões materializadas

​Observe a sobrecarga no armazenamento

​Próximos passos