Crie sua primeira visão materializada

Saiba como usar visões materializadas no ClickHouse para pré-computar e armazenar resultados de consultas com uma ordenação diferente, permitindo buscas rápidas em colunas não cobertas pela chave primária.

Todos os guias de início rápido

Analytics em tempo realArmazenamento de dadosObservabilidadeAI/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 o guia de início rápido Crie sua primeira tabela MergeTree, pois este guia se baseia diretamente na tabela uk_price_paid criada lá.

O que você vai criar

No guia de início rápido do MergeTree, você viu que consultar uk_price_paid por town ou county exige uma varredura completa da tabela, porque ela é ordenada por (postcode, addr1, addr2). Neste guia de início rápido, você vai resolver esse problema criando uma visão materializada que armazena os mesmos dados ordenados por (town, date), permitindo buscas rápidas por cidade sem alterar a tabela original. Ao final, você entenderá como visões materializadas funcionam como gatilhos de inserção, como fazer backfill dos dados existentes e o trade-off de espaço em disco ao armazenar os dados duas vezes.

1. Entenda por que você precisa de uma visão materializada

   Sua tabela uk_price_paid está ordenada por (postcode, addr1, addr2). Isso significa que o ClickHouse pode ignorar grandes blocos de dados quando você filtra por postcode, addr1 ou addr2, mas consultas que filtram por town precisam examinar cada uma das linhas — todas as 30 milhões.

   Você poderia criar uma segunda tabela com um ORDER BY diferente, mas aí precisaria se lembrar de inserir os dados nas duas tabelas sempre que novos dados chegassem. Uma visão materializada automatiza isso: ela monitora as inserções em uma tabela de origem, transforma as linhas e as grava automaticamente em uma tabela de destino.

   Pense em uma visão materializada como um gatilho de inserção — toda vez que linhas são inseridas na tabela de origem, a consulta SELECT da MV é executada sobre o novo bloco de linhas, e o resultado é inserido na tabela de destino.

2. Crie a tabela de destino

   Uma visão materializada precisa de algum lugar para armazenar seu resultado. É apenas uma tabela MergeTree comum — você tem controle total sobre seu esquema, ORDER BY e PARTITION BY.

   Crie uma tabela ordenada por (town, date) com apenas as colunas necessárias para consultas por cidade:

   CREATE TABLE uk_price_paid_by_town
   (
       town       LowCardinality(String),
       date       Date,
       price      UInt32,
       type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0)
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(date)
   ORDER BY (town, date);
   

   Não há nada de especial nesta tabela - ela é uma tabela MergeTree padrão. A visão materializada que você vai criar na próxima etapa simplesmente encaminhará os dados para ela.

   Verifique se a tabela foi criada:

   SHOW CREATE TABLE uk_price_paid_by_town;
   
3. Crie a visão materializada

   Agora, crie a visão materializada que conecta a tabela de origem (uk_price_paid) à tabela de destino (uk_price_paid_by_town):

   CREATE MATERIALIZED VIEW uk_price_paid_by_town_mv
   TO uk_price_paid_by_town
   AS SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   A cláusula TO uk_price_paid_by_town informa ao ClickHouse que ele deve gravar a saída do SELECT na sua tabela de destino. A partir de agora, sempre que linhas forem inseridas em uk_price_paid, essa MV será acionada e inserirá as linhas transformadas em uk_price_paid_by_town.

   Há uma ressalva importante: visões materializadas só são acionadas por inserções. Se você excluir ou atualizar linhas na tabela de origem, a tabela de destino não saberá disso — as MVs não permanecem sincronizadas com exclusões ou atualizações. Se você precisar desse tipo de sincronização, considere usar projeções.

4. Fazer backfill dos dados existentes

   A visão materializada processa apenas inserções futuras. As 30 milhões de linhas já em uk_price_paid foram inseridas antes de a MV existir, então a tabela de destino está vazia no momento.

   Faça o backfill manualmente:

   INSERT INTO uk_price_paid_by_town
   SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   Isso insere diretamente na tabela de destino - a MV não está envolvida neste passo. Quando terminar, verifique se as contagens de linhas correspondem:

   SELECT
       'uk_price_paid' AS table,
       count() AS rows
   FROM uk_price_paid
   UNION ALL
   SELECT
       'uk_price_paid_by_town' AS table,
       count() AS rows
   FROM uk_price_paid_by_town;
   

   Ambas as tabelas devem ter o mesmo número de linhas.

5. Consulte a tabela de destino da visão materializada

   Agora, execute uma consulta com filtro em town na tabela de destino e compare o resultado com uma consulta feita diretamente na tabela de origem.

   Primeiro, consulte a tabela de origem:

   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 — todas as 30 milhões de linhas são lidas porque town não está no ORDER BY da tabela de origem.

   Agora execute a mesma consulta na tabela de destino da visão materializada:

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

   Verifique novamente as estatísticas da consulta - bem menos linhas são lidas porque a tabela de destino está ordenada por (town, date) e o ClickHouse pode ignorar todos os dados que não correspondem a LONDON.

   Execute SHOW TABLES para ver o que foi criado:

   SHOW TABLES;
   

   Você verá tanto uk_price_paid_by_town (a tabela de destino) quanto uk_price_paid_by_town_mv (a view). Como você usou CREATE MATERIALIZED VIEW ... TO, tem controle sobre o nome da tabela de destino. Se omitir a cláusula TO, o ClickHouse criará uma tabela de destino com nome implícito (.inner.xxx), com a qual é mais difícil trabalhar diretamente. Por isso, recomenda-se criar visões materializadas usando a cláusula TO.

6. Observe que os dados são armazenados duas vezes

   Visões materializadas oferecem leituras mais rápidas, em troca de espaço adicional em disco. Consulte system.parts para ver quanto espaço cada tabela usa:

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

   Os dados são armazenados fisicamente duas vezes: uma em uk_price_paid, ordenados por (postcode, addr1, addr2), e outra em uk_price_paid_by_town, ordenados por (town, date). Esse é o trade-off fundamental: você usa mais espaço em disco em troca de leituras mais rápidas para diferentes padrões de acesso.

   A tabela de destino pode ocupar menos espaço em disco porque contém menos colunas, e a ordenação (town, date) pode ser compactada de forma diferente da original.

Próximos passos

Neste guia de início rápido, você criou uma visão materializada para armazenar dados de vendas de imóveis do Reino Unido com uma ordem de ordenação diferente, permitindo consultas rápidas por cidade sem modificar a tabela original. Você aprendeu que as MVs funcionam como gatilhos de inserção, que os dados existentes precisam ser carregados manualmente de forma retroativa e que a contrapartida é o uso de espaço adicional em disco.

Confira a seguir os próximos guias de início rápido:

• Crie sua primeira projeção

Ou aprofunde-se na documentação de referência:

• Referência de visão materializada
• Visões materializadas incrementais
• Projeções

Check out the ClickHouse academy for on-demand and live training

Todos os guias de início rápido