Operações do ClickHouse

Este documento explica os conceitos do ClickHouse que você encontrará neste laboratório de migração — o que são, por que existem e como diferem das construções do Snowflake usadas na Parte 1.

1. Mecanismos de tabela

O ClickHouse não é um banco de dados com um único mecanismo. Toda tabela criada precisa declarar seu mecanismo, que determina como os dados são armazenados no disco, como as duplicatas são tratadas e quais recursos ficam disponíveis. Escolher o mecanismo errado é o erro mais comum no projeto de esquemas do ClickHouse.

MergeTree

O mecanismo-base de quase todas as tabelas em produção.

CREATE TABLE analytics.dim_taxi_zones (
    zone_id      UInt16,
    borough      String,
    service_zone String
) ENGINE = MergeTree()
ORDER BY zone_id;

O que ele faz: o ClickHouse armazena os dados em partes — blocos ordenados e compactados no disco. Quando você insere dados, novas partes são gravadas. Em segundo plano, o ClickHouse mescla continuamente as partes menores em partes maiores, mantendo os dados ordenados pela chave ORDER BY. Daí vem o nome.

Quando usar: em qualquer tabela que não precise de desduplicação e cujas inserções sejam apenas acréscimos ou cargas em massa (tabelas de dimensão, tabelas de eventos brutos e tabelas de log).

Propriedade importante: não há imposição de chave primária. Duas linhas com valores ORDER BY idênticos são armazenadas. Se você precisar de desduplicação, use ReplacingMergeTree.

ReplacingMergeTree(version_col)

O mecanismo de desduplicação. Ele estende o MergeTree com uma regra: durante uma mesclagem em segundo plano, quando duas linhas compartilham a mesma chave ORDER BY, somente aquela com o maior valor de version_col é mantida.

CREATE TABLE analytics.fact_trips (
    trip_id     String,
    pickup_at   DateTime,
    total_amount Float64,
    updated_at  DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (pickup_at, trip_id);

Importante — consistência eventual: a desduplicação ocorre somente durante as mesclagens em segundo plano. Em qualquer momento, sua tabela pode conter linhas duplicadas. Isso se chama consistência eventual. Para obter resultados totalmente desduplicados no momento da consulta, adicione FINAL ao SELECT:

-- Without FINAL: may return duplicates if merges haven't run
SELECT * FROM analytics.fact_trips WHERE trip_id = 'abc';

-- With FINAL: forces deduplication at query time (slower, always correct)
SELECT * FROM analytics.fact_trips FINAL WHERE trip_id = 'abc';

Como o dbt o usa: o adaptador dbt-clickhouse usa a estratégia incremental delete_insert como mecanismo principal — ele exclui explicitamente as linhas com chaves correspondentes e insere linhas novas, o que é sempre correto. ReplacingMergeTree funciona como uma rede de proteção que elimina as duplicatas que escaparem (por exemplo, devido a uma inserção parcial com falha).

Equivalente no Snowflake: não há equivalente direto. No Snowflake, você usou MERGE INTO ... WHEN MATCHED THEN UPDATE. O ClickHouse não tem uma instrução MERGE — ReplacingMergeTree junto com FINAL alcança o mesmo resultado lógico.

View materializada atualizável

O ClickHouse oferece dois tipos de views materializadas.

MV baseada em gatilho (tradicional): é executada a cada INSERT e processa somente o lote recém-inserido.

-- Trigger-based: only sees the rows inserted in the current batch
CREATE MATERIALIZED VIEW analytics.mv_realtime_counts
TO analytics.counts_table AS
SELECT pickup_date, count() AS trips
FROM default.trips_raw
GROUP BY pickup_date;

MV atualizável (agendada): executa novamente a consulta completa de acordo com um cronograma, como uma tarefa cron.

-- Refreshable: runs the full SELECT every 3 minutes
CREATE MATERIALIZED VIEW analytics.mv_hourly_revenue
REFRESH EVERY 180 SECOND AS
SELECT
    toStartOfHour(pickup_at) AS hour_bucket,
    pickup_borough,
    sum(total_amount)        AS revenue
FROM analytics.fact_trips FINAL
GROUP BY hour_bucket, pickup_borough;

Quando usar cada uma:

• Baseada em gatilho: agregações em tempo real sobre fluxos de inserção, quando basta processar os novos dados
• Atualizável: agregações que consultam fact_trips FINAL (precisam enxergar toda a tabela para desduplicar) ou dashboards que toleram alguns minutos de defasagem em troca de uma lógica mais simples

Como alterar o intervalo de atualização:

ALTER TABLE analytics.mv_hourly_revenue MODIFY REFRESH EVERY 60 SECOND;

2. Chaves de ordenação (ORDER BY)

No Snowflake, você usou CLUSTER BY como uma indicação para o otimizador. No ClickHouse, ORDER BY é o índice primário — ela determina a ordem física dos dados no disco e orienta todas as varreduras por intervalo.

Como funciona

O ClickHouse armazena um índice primário esparso: uma entrada de índice a cada cerca de 8.192 linhas (um grânulo de dados). Quando você filtra pelas colunas de ORDER BY, o ClickHouse ignora grânulos inteiros sem lê-los. É por isso que o ClickHouse consegue varrer bilhões de linhas por segundo — a maior parte dos dados nunca sai do disco.

A ordem de cardinalidade importa

Sempre coloque as colunas de baixa cardinalidade primeiro e as colunas de alta cardinalidade por último. Assim, o índice tem o máximo poder de eliminação no caso mais comum.

-- Good: low cardinality (borough, ~6 values) first, then high cardinality (trip_id)
ORDER BY (pickup_borough, toStartOfMonth(pickup_at), trip_id)

-- Bad: high cardinality first — the index can't skip anything useful
ORDER BY (trip_id, pickup_borough, pickup_at)

CLUSTER BY do Snowflake versus ORDER BY do ClickHouse

Exemplo: correspondência com uma chave de cluster existente no Snowflake

-- Snowflake
CLUSTER BY (DATE_TRUNC('month', PICKUP_AT), PICKUP_LOCATION_ID)

-- ClickHouse equivalent
ORDER BY (toStartOfMonth(pickup_at), pickup_location_id, trip_id)
-- Note: trip_id added as tiebreaker to ensure unique sort order

Índices de eliminação (breve apresentação)

Para colunas fora da chave ORDER BY, o ClickHouse oferece índices de eliminação (filtro Bloom, minmax e conjunto), que armazenam metadados no nível de colunas por grânulo. Eles são úteis para filtrar colunas de baixa cardinalidade que aparecem depois de uma coluna de alta cardinalidade na chave de ordenação.

-- Add a bloom filter skip index on payment_type
ALTER TABLE analytics.fact_trips
ADD INDEX idx_payment_type payment_type TYPE bloom_filter GRANULARITY 4;

3. Tratamento de JSON

O tipo de coluna VARIANT do Snowflake usa a notação de caminho com dois-pontos para percorrer JSON aninhado. O ClickHouse usa funções JSONExtract* explícitas.

Tabela de tradução lado a lado

Variantes das funções

-- Float (returns 0.0 if key missing or wrong type)
JSONExtractFloat(trip_metadata, 'driver', 'rating')

-- String (returns '' if missing)
JSONExtractString(trip_metadata, 'app', 'version')

-- Integer (returns 0 if missing)
JSONExtractInt(trip_metadata, 'driver', 'id')

-- Bool (returns 0/1)
JSONExtractBool(trip_metadata, 'app', 'is_shared')

-- Raw value as string (preserves JSON sub-object)
JSONExtractRaw(trip_metadata, 'route')

Dica de desempenho

Se você consulta repetidamente a mesma coluna JSON, considere extrair os campos para colunas tipadas no modelo de preparação (em stg_trips.sql), em vez de chamar JSONExtractFloat em todas as consultas posteriores. É isso que os modelos dbt do laboratório fazem.

4. Funções de data e hora

O Snowflake e o ClickHouse oferecem recursos semelhantes para data e hora, mas com sintaxes diferentes. Estas são as traduções mais comuns:

Tabela de tradução lado a lado

Outros recursos convenientes exclusivos do ClickHouse

yesterday()              -- today() - 1 day
toStartOfWeek(col)       -- Monday of the containing week
toStartOfQuarter(col)    -- first day of the quarter
toYear(col)              -- extract year as integer
toMonth(col)             -- extract month as integer (1-12)
toDayOfWeek(col)         -- 1=Monday, 7=Sunday

Sintaxe de intervalos

-- ClickHouse
now() - INTERVAL 7 DAY
now() - INTERVAL 1 HOUR
now() - INTERVAL 30 MINUTE
pickup_at + INTERVAL 90 SECOND

-- Snowflake equivalent
DATEADD('day', -7, CURRENT_TIMESTAMP())
DATEADD('hour', -1, CURRENT_TIMESTAMP())

5. Funções aproximadas

O ClickHouse foi criado para cargas de trabalho analíticas nas quais respostas exatas sobre bilhões de linhas são mais lentas que respostas aproximadas suficientemente precisas para dashboards. O ClickHouse oferece várias funções de agregação aproximada integradas.

Contagem distinta

-- Exact (like Snowflake COUNT(DISTINCT ...))
SELECT uniqExact(trip_id) FROM analytics.fact_trips FINAL;

-- Approximate — good for "how many unique passengers today?"
SELECT uniq(passenger_id) FROM analytics.fact_trips FINAL;

Percentis

-- P95 trip duration — approximate but uses O(1) memory
SELECT quantileTDigest(0.95)(duration_minutes)
FROM analytics.fact_trips FINAL;

-- Multiple percentiles in one pass
SELECT quantileTDigestMerge(0.5)(state), quantileTDigestMerge(0.95)(state)
FROM analytics.fact_trips FINAL;

Regra prática: use uniq e quantileTDigest em dashboards interativos. Use uniqExact e quantile somente quando precisar de valores exatos para faturamento, SLAs ou conformidade.

6. Dicionários

Dicionários são tabelas de busca na memória que o ClickHouse mantém quentes e pré-associadas no momento da consulta. Eles são o equivalente no ClickHouse a uma pequena tabela de dimensão que você quer associar sem o custo de uma JOIN completa.

O que são

Um dicionário é baseado em uma origem (uma tabela do ClickHouse, um arquivo ou um banco de dados externo) e carregado na memória quando o serviço é iniciado ou quando você chama SYSTEM RELOAD DICTIONARIES. As buscas são feitas por uma chave e retornam um ou mais atributos.

Sintaxe de CREATE DICTIONARY

-- From 04_create_dictionary.sql
CREATE DICTIONARY analytics.taxi_zones_dict (
    zone_id      UInt16,
    borough      String,
    service_zone String
)
PRIMARY KEY zone_id
SOURCE(CLICKHOUSE(
    TABLE 'dim_taxi_zones'
    DB    'analytics'
))
LIFETIME(MIN 300 MAX 600)   -- refresh every 5-10 minutes
LAYOUT(FLAT());             -- hash map, best for < 1M rows

Opções de LAYOUT:

• FLAT() — array indexado por chave inteira; o mais rápido; exige chaves inteiras sequenciais
• HASHED() — mapa hash; funciona com qualquer chave inteira
• COMPLEX_KEY_HASHED() — mapa hash com chaves compostas ou strings

Uso de dictGet

-- Instead of: JOIN analytics.dim_taxi_zones USING (zone_id)
SELECT
    trip_id,
    dictGet('analytics.taxi_zones_dict', 'borough', toUInt64(pickup_location_id)) AS pickup_borough,
    dictGet('analytics.taxi_zones_dict', 'borough', toUInt64(dropoff_location_id)) AS dropoff_borough
FROM analytics.fact_trips FINAL;

Quando usar dicionários ou JOINs

7. Cláusula SAMPLE

O ClickHouse oferece amostragem no nível de linhas diretamente na sintaxe da consulta. A amostragem lê uma fração determinística dos dados — útil para análises exploratórias quando você não precisa de resultados exatos.

Sintaxe

-- Read approximately 10% of rows
SELECT count(), avg(total_amount)
FROM analytics.fact_trips SAMPLE 0.1;

-- Read a specific number of rows (approximately)
SELECT trip_id, pickup_at, total_amount
FROM analytics.fact_trips SAMPLE 1000000;

Escalonamento dos resultados

Ao usar amostragem, multiplique as agregações por 1 / sample_rate para estimar os valores da tabela completa:

-- Estimate total revenue from 10% sample
SELECT sum(total_amount) * 10 AS estimated_total_revenue
FROM analytics.fact_trips SAMPLE 0.1;

Quando usar SAMPLE

• Em análises exploratórias (“a lógica da minha consulta está correta?”) antes de executá-la na tabela inteira
• Em blocos de dashboard nos quais valores aproximados são aceitáveis
• No treinamento de modelos de ML sobre um subconjunto representativo

Observação: SAMPLE exige que a chave ORDER BY da tabela comece com a coluna de amostragem ou que você adicione uma cláusula SAMPLE BY à instrução CREATE TABLE. A tabela trips_raw do laboratório é criada com SAMPLE BY cityHash64(trip_id) para esse fim.

8. Observações sobre o adaptador dbt-clickhouse

O adaptador dbt-clickhouse (dbt-clickhouse>=1.8) oferece suporte à maioria dos recursos padrão do dbt, mas tem alguns comportamentos específicos do ClickHouse que você precisa entender.

Estratégia incremental delete_insert

O ClickHouse não possui MERGE INTO. A estratégia delete_insert do adaptador dbt-clickhouse a emula:

1. Exclui da tabela de destino as linhas cujas colunas de chave correspondem ao lote recebido
2. Insere o conjunto completo das linhas recebidas

-- What dbt generates for incremental models
DELETE FROM analytics.fact_trips WHERE trip_id IN (SELECT trip_id FROM __dbt_tmp);
INSERT INTO analytics.fact_trips SELECT * FROM __dbt_tmp;

Configure-a em seu modelo:

{{
    config(
        materialized='incremental',
        incremental_strategy='delete_insert',
        unique_key='trip_id',
        engine='ReplacingMergeTree(updated_at)',
        order_by='(pickup_at, trip_id)'
    )
}}

Configuração de engine e order_by

Toda tabela MergeTree precisa de um mecanismo e de uma ORDER BY. Especifique ambos na configuração do modelo dbt:

{{
    config(
        engine='MergeTree()',
        order_by='(zone_id)'
    )
}}

order_by em vez de cluster_by

Nos modelos dbt para o Snowflake, você talvez tenha usado cluster_by. No dbt-clickhouse, use order_by. Não há um equivalente ao cluster_by do Snowflake no ClickHouse — ORDER BY sempre é a ordenação física.

profiles.yml para o ClickHouse Cloud

O ClickHouse Cloud exige TLS. Defina secure: true:

# ~/.dbt/profiles.yml
nyc_taxi_ch:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: "{{ env_var('CLICKHOUSE_HOST') }}"
      port: 8443
      user: default
      password: "{{ env_var('CLICKHOUSE_PASSWORD') }}"
      schema: analytics       # default database for models without a custom schema
      secure: true
      threads: 4

Nomes de esquema e bancos de dados separados

O ClickHouse usa o termo banco de dados onde o Snowflake usa esquema. O adaptador dbt-clickhouse mapeia os esquemas do dbt para bancos de dados do ClickHouse. A macro generate_schema_name neste projeto substitui o comportamento padrão do dbt para que os modelos com +schema: analytics sejam criados no banco de dados analytics, não em staging_analytics.

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
  {%- if custom_schema_name is none -%}
    {{ target.schema }}
  {%- else -%}
    {{ custom_schema_name }}
  {%- endif -%}
{%- endmacro %}

Esse é o mesmo padrão usado no projeto dbt do Snowflake (Parte 1) — a macro é intencionalmente idêntica, para manter o comportamento dos nomes de esquema consistente entre os dois adaptadores.