Crie sua primeira tabela MergeTree

Antes de escrever qualquer SQL, é útil entender o que torna o MergeTree diferente de uma tabela de banco de dados tradicional.

Quando você insere dados em uma tabela MergeTree, o ClickHouse não grava as linhas uma a uma. Em vez disso, ele grava uma parte de dados — um pequeno bloco de linhas, ordenado e comprimido — diretamente no disco. Depois, o ClickHouse mescla essas partes em segundo plano ao longo do tempo. É daí que vem o nome: merge + tree.

Cada parte de dados é ordenada pela expressão ORDER BY da tabela. Essa ordem se torna o índice de chave primária, o que permite ao ClickHouse ignorar grandes blocos de dados que não precisa ler durante uma consulta (isso é chamado de poda de dados). Quanto mais seletivas forem as colunas do seu ORDER BY para as consultas mais comuns, menos dados o ClickHouse lerá.

Três cláusulas controlam como o MergeTree organiza seus dados:

Agora você já deve conseguir explicar a relação entre partes de dados, a chave primária e o desempenho das consultas em uma tabela MergeTree.

Antes de criar sua tabela, inspecione o arquivo de origem usando a função de tabela s3. Isso permite consultar o S3 diretamente, sem antes gravar nenhum dado no ClickHouse.

Execute o seguinte no Console SQL:

DESCRIBE s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
);

Observe que quase toda coluna foi inferida como Nullable(String). O ClickHouse está lendo um CSV bruto, então ele não sabe quais são os tipos de dados reais — isso é algo que você corrigirá ao definir o esquema da sua tabela no próximo passo.

Visualize algumas linhas:

SELECT *
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
)
LIMIT 5;

O conjunto de dados contém vendas de imóveis residenciais na Inglaterra e no País de Gales registradas no HM Land Registry, incluindo o id da transação, o price da venda, a date, o type do imóvel, campos de endereço e identificadores geográficos. Você também notará duas colunas no final (column15, column16) que estão vazias — elas podem ser ignoradas.

Verifique isso confirmando que você consegue ver linhas com colunas como id, price, date, postcode, type, town e county.

Agora crie uma tabela permanente com um esquema apropriado. Os tipos de coluna abaixo foram escolhidos deliberadamente:

• LowCardinality(String) é usado para colunas com poucos valores distintos (códigos postais, nomes de cidades, nomes de condados). Ele usa codificação por dicionário internamente, reduz drasticamente o armazenamento e melhora o desempenho de agrupamento e filtragem nessas colunas.
• Enum8 codifica as colunas type e duration como pequenos inteiros em disco, mantendo rótulos de string legíveis nas consultas. O CSV de origem usa códigos de uma única letra, então vamos mapeá-los durante a inserção.
• PARTITION BY toYYYYMM(date) cria uma partição por mês do calendário, permitindo que o ClickHouse ignore meses inteiros quando a cláusula WHERE filtra por date.
• ORDER BY (postcode, addr1, addr2) ordena os dados para permitir buscas rápidas por endereço do imóvel — o padrão de acesso mais natural para esse conjunto de dados.

CREATE TABLE uk_price_paid
(
price      UInt32,
date       Date,
postcode   LowCardinality(String),
type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
is_new     UInt8,
duration   Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
addr1      String,
addr2      String,
street     LowCardinality(String),
locality   LowCardinality(String),
town       LowCardinality(String),
district   LowCardinality(String),
county     LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (postcode, addr1, addr2);

Verifique se a tabela foi criada executando:

SHOW CREATE TABLE uk_price_paid;

Clique duas vezes na célula de resultado para inspecionar a saída completa. Observe que, embora você tenha especificado ENGINE = MergeTree, o ClickHouse Cloud criou a tabela com SharedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}'). Isso é esperado — o Cloud converte automaticamente MergeTree em SharedMergeTree, adicionando suporte à replicação e ao armazenamento compartilhado. O comportamento e a interface de consulta permanecem os mesmos.

Insira o conjunto de dados completo selecionando diretamente da função de tabela s3(). O ClickHouse transmite o arquivo comprimido do S3 e o grava na sua tabela em partes ordenadas.

INSERT INTO uk_price_paid
SELECT
    toUInt32(price),
    date,
    postcode,
    transform(type, ['T', 'S', 'D', 'F', 'O'],
        ['terraced', 'semi-detached', 'detached', 'flat', 'other'], 'other') AS type,
    if(is_new = 'Y', 1, 0) AS is_new,
    transform(duration, ['F', 'L', 'U'],
        ['freehold', 'leasehold', 'unknown'], 'unknown') AS duration,
    addr1,
    addr2,
    street,
    locality,
    town,
    district,
    county
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
);

Como o CSV de origem armazena tudo como strings com códigos de uma única letra (por exemplo, T para casa geminada, F para propriedade plena, Y/N para imóvel novo), usamos transform para mapeá-los para rótulos legíveis e toUInt32/if para converter as colunas numéricas. As colunas id, column15 e column16 são excluídas, pois não precisamos delas.

Isso levará um ou dois minutos, dependendo do tamanho do seu serviço. Quando concluir, confirme a contagem de linhas:

SELECT formatReadableQuantity(count())
FROM uk_price_paid;

Você deverá ver cerca de 30 milhões de linhas carregadas.

É aqui que os componentes internos do MergeTree ficam visíveis. A tabela system.parts rastreia cada parte de dados no disco de cada tabela MergeTree no seu serviço.

SELECT
partition,
name,
rows,
bytes_on_disk,
marks
FROM system.parts
WHERE table = 'uk_price_paid'
AND active = true
ORDER BY partition
LIMIT 20;

Cada linha representa uma parte de dados ativa. Observe:

• partition - o valor YYYYMM derivado da sua expressão PARTITION BY. Os dados de cada mês ficam isolados.
• name - o nome da parte codifica a partição, o intervalo de números de bloco e o nível de merge (por exemplo, 199501_1_4_2 significaria a partição 199501, os blocos de 1 a 4 e duas mesclagens).
• marks - o número de grânulos de índice. Cada grânulo cobre 8.192 linhas por padrão, e o índice de chave primária armazena uma entrada por grânulo. É esse índice esparso que permanece na memória e permite pular dados rapidamente.
• bytes_on_disk - o ClickHouse comprime cada parte coluna por coluna usando LZ4 por padrão. Compare isso com o tamanho bruto para entender a taxa de compressão.

Para ver o número total de partes e o tamanho total comprimido da sua tabela, execute:

SELECT
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;

Se você executar esta consulta novamente daqui a algum tempo, poderá notar que a contagem de partes diminuiu. Esse é o merge do MergeTree em ação — o ClickHouse mescla continuamente partes menores em partes maiores em segundo plano, reduzindo o número de partes. O filtro active = true garante que você veja apenas as partes atuais, já mescladas, em vez de partes mais antigas que ainda estejam aguardando limpeza.

Agora execute algumas consultas analíticas reais. Primeiro, encontre as vendas de maior valor já registradas:

SELECT
addr1,
addr2,
town,
county,
price,
date
FROM uk_price_paid
ORDER BY price DESC
LIMIT 5;

Verifique as estatísticas da consulta no console SQL — observe que todas as 30.033.199 linhas foram lidas. Como price não faz parte da chave ORDER BY, ClickHouse não pode usar o índice primário para pular dados e precisa fazer uma varredura completa da tabela.

Em seguida, encontre o preço médio de venda por condado:

SELECT
county,
round(avg(price)) AS avg_price,
count()           AS sales
FROM uk_price_paid
GROUP BY county
ORDER BY avg_price DESC;

Novamente, todas as 30.033.199 linhas são lidas — county não está em ORDER BY nem em PARTITION BY, então o ClickHouse varre a tabela inteira.

Agora execute uma consulta que combina agregação com o seu ORDER BY. Como os dados estão ordenados por (postcode, addr1, addr2), filtrar por um prefixo de código postal permite que o ClickHouse pule a maior parte da tabela. Aqui, encontramos o preço médio de venda por ano para imóveis na área de código postal SW1A:

SELECT
toYear(date) AS year,
round(avg(price)) AS avg_price,
count() AS sales,
min(price) AS cheapest,
max(price) AS most_expensive
FROM uk_price_paid
WHERE postcode LIKE 'SW1A%'
GROUP BY year
ORDER BY year DESC;

Verifique as estatísticas da consulta no SQL Console após cada consulta. A agregação filtrada por postcode deve ler apenas uma fração das linhas da tabela, demonstrando o índice da chave primária em ação. Compare isso com as consultas anteriores, que fazem uma varredura mais ampla - a diferença mostra por que escolher o ORDER BY correto é importante.