Crie sua primeira 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.

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;

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.

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.

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.

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.