ClickHouse

O ClickHouse é um sistema de gerenciamento de banco de dados (SGBD) analítico columnar (OLAP) de código aberto, desenvolvido pela Yandex. É otimizado especificamente para consultas analíticas em tempo real sobre grandes volumes de dados.

Info

O ClickHouse é conhecido por sua excepcional velocidade em consultas analíticas.

Utiliza armazenamento columnar e processamento paralelo massivo (MPP) para alta performance.

Características principais

• Columnar storage: Armazena dados por colunas, otimizando consultas analíticas
• Compressão avançada: Algoritmos de compressão específicos para cada tipo de dado
• SQL completo: Suporte robusto ao SQL com extensões para análise avançada
• Distribuído: Escalabilidade horizontal nativa com clusters
• Real-time: Inserções e consultas em tempo real
• Formatos diversos: Suporte nativo a Parquet, CSV, JSON e outros formatos

Preparando o ambiente

Vamos configurar um ambiente com ClickHouse utilizando Docker e trabalhar com os mesmos dados da aula de DuckDB.

Exercise

Crie um diretório para o projeto:

$ mkdir clickhouse
$ cd clickhouse

Mark as done

Exercise

Crie um arquivo clickhouse/docker-compose.yml para configurar o ClickHouse:

Atenção!

Caso seja necessário, edite as portas externas!

services:
  clickhouse:
    image: clickhouse/clickhouse-server:25.7.4.11-alpine
    container_name: clickhouse-server
    ports:
      - "8123:8123"
      - "9000:9000"
    environment:
      CLICKHOUSE_DB: bike_share_clickhouse
      CLICKHOUSE_USER: admin
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
      CLICKHOUSE_PASSWORD: password123
    volumes:
      - clickhouse-data:/var/lib/clickhouse
      - ./dados:/var/lib/clickhouse/user_files
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

volumes:
  clickhouse-data:

Info!

O ClickHouse só permite acesso a arquivos dentro do diretório user_files por questões de segurança.

Ao fazer ingestão, acessaremos ./dados via /var/lib/clickhouse/user_files/ dentro do container.

Atenção!

Observe que, com a definição do volume clickhouse-data, teremos persistência dos dados mesmo após a parada ou remoção do container.

Confira os volumes com (só irá aparecer após você iniciar o container):

$ docker volume ls

Mark as done

Exercise

O que a seção ulimits está configurando?

Submit

Answer

O ulimits está configurando o número máximo de arquivos abertos que o ClickHouse pode ter, aumentando o limite padrão para 262144.

Exercise

Crie uma pasta clickhouse/dados e copie os CSVs e Parquets criados na aula de DuckDB.

Mark as done

Exercise

Inicie o container do ClickHouse:

$ docker compose up -d

Mark as done

Exercise

Verifique se o ClickHouse está rodando e se o volume foi criado:

$ docker logs clickhouse-server
$ docker volume ls

Mark as done

Interface de acesso

O ClickHouse oferece múltiplas interfaces de acesso: HTTP, cliente nativo, e diversas linguagens de programação (Java, Python, Go, etc.).

Cliente HTTP

Exercise

Teste a conexão via HTTP:

Info!

Esta chamada retorna a versão do ClickHouse.

$ curl 'http://admin:password123@localhost:8123/' -d 'SELECT version()'

Não tenho curl

Caso não tenha curl, acesse a URL diretamente em seu navegador:

http://localhost:8123/?query=SELECT%20version()

Mark as done

Cliente CLI

Exercise

Acesse o cliente ClickHouse dentro do container:

$ docker exec -it clickhouse-server clickhouse-client --user admin --password password123

Você verá um prompt clickhouse-server :) onde pode executar comandos SQL.

Mark as done

Exercise

Execute a seguinte query para ver informações do sistema:

Atenção!

As queries (esta e as próximas) devem ser executadas no terminal que está conectado ao Clickhouse!

Aquele que você iniciou com o comando:

docker exec -it clickhouse-server clickhouse-client --user admin --password password123

Query:

SELECT 
    name,
    value
FROM system.settings 
WHERE name LIKE '%thread%'
LIMIT 5;

Mark as done

Obtendo os dados

Vamos utilizar os mesmos dados de bicicletas de São Francisco que usamos na aula do DuckDB.

Criando databases e tabelas

Database

Exercise

No cliente ClickHouse, crie um database:

CREATE DATABASE IF NOT EXISTS bike_share;
USE bike_share;

Mark as done

Importando dados CSV

Vamos começar criando tabelas e importando os dados CSV.

Exercise

Crie a tabela station:

CREATE TABLE station
(
    id UInt32,
    name String,
    lat Float64,
    long Float64,
    dockcount UInt32,
    landmark String,
    installation String
)
ENGINE = MergeTree()
ORDER BY id;

Mark as done

Exercise

Importe os dados das estações:

Atenção!

Este comando deve ser rodado em outro terminal da sua máquina e não no que está conectado como cliente.

Ele supõe que seu diretório de trabalho atual é a pasta clickhouse.

Path!

Antes de executar o comando, garanta que está no path correto, que é a raiz da aula!

Nesta pasta, deve haver uma sub-pasta dados.

$ docker exec -i clickhouse-server clickhouse-client --user admin --password password123 --query="INSERT INTO bike_share.station FORMAT CSVWithNames" < dados/station.csv

Mark as done

Exercise

Verifique os dados importados:

Atenção!

Copie, cole e execute os comandos abaixo, um por vez, no terminal que está conectado ao Clickhouse!

SHOW TABLES;
SELECT COUNT(*) FROM station;
SELECT * FROM station LIMIT 5;

Mark as done

Exercise

Acesse http://localhost:8123/dashboard para monitorar o servidor!

Atenção!

Informe o usuário e senha!

Mark as done

Exercise

Crie a tabela trip com tipos de dados otimizados:

CREATE TABLE trip
(
    id UInt64,
    duration UInt32,
    start_date DateTime,
    start_station_name String,
    start_station_id UInt32,
    end_date DateTime,
    end_station_name String,
    end_station_id UInt32,
    bike_id UInt32,
    subscription_type String,
    zip_code String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(start_date)
ORDER BY (start_date, start_station_id);

Mark as done

Particionamento

O ClickHouse permite particionamento eficiente dos dados. Aqui particionamos por mês da data de início.

Exercise

Importe os dados das viagens. Para isso, no cliente ClickHouse, execute a query:

INSERT INTO trip
SELECT 
    toUInt64(id) as id,
    toUInt32(duration) as duration,
    parseDateTimeBestEffort(start_date) as start_date,
    start_station_name,
    toUInt32(start_station_id) as start_station_id,
    parseDateTimeBestEffort(end_date) as end_date,
    end_station_name,
    toUInt32(end_station_id) as end_station_id,
    toUInt32(bike_id) as bike_id,
    subscription_type,
    zip_code
FROM file('trip.csv', 'CSVWithNames');

Magic do ClickHouse

A função parseDateTimeBestEffort() consegue interpretar automaticamente formatos de data diversos, incluindo o formato americano MM/DD/YYYY HH:MM.

Caminho do arquivo

Note que agora usamos apenas 'trip.csv' (sem caminho) porque o arquivo está mapeado diretamente no diretório user_files.

Mark as done

Exercise

Verifique se os dados foram importados corretamente:

SELECT COUNT(*) FROM trip;
SELECT * FROM trip LIMIT 5;

Mark as done

Consultas básicas

Análise exploratória

Exercise

Execute consultas básicas para entender os dados:

-- Contagem total de viagens
SELECT COUNT(*) as total_trips FROM trip;

-- Duração média das viagens
SELECT AVG(duration) as avg_duration_seconds FROM trip;

-- Top 5 estações mais utilizadas como ponto de partida
SELECT 
    start_station_name,
    COUNT(*) as trip_count
FROM trip 
GROUP BY start_station_name 
ORDER BY trip_count DESC 
LIMIT 5;

Mark as done

Funções de data e tempo

O ClickHouse possui funções avançadas para manipulação de datas e tempo.

Exercise

Analise padrões temporais:

-- Viagens por dia da semana
SELECT 
    toDayOfWeek(start_date) as day_of_week,
    COUNT(*) as trip_count
FROM trip 
GROUP BY day_of_week 
ORDER BY day_of_week;

-- Viagens por hora do dia
SELECT 
    toHour(start_date) as hour_of_day,
    COUNT(*) as trip_count
FROM trip 
GROUP BY hour_of_day 
ORDER BY hour_of_day;

Mark as done

Consultas analíticas avançadas

Window Functions

O ClickHouse suporta window functions para análises sofisticadas.

Exercise

Utilize window functions para análises avançadas:

-- Ranking das estações mais populares por mês
SELECT 
    toYYYYMM(start_date) as month,
    start_station_name,
    COUNT(*) as trip_count,
    ROW_NUMBER() OVER (PARTITION BY toYYYYMM(start_date) ORDER BY COUNT(*) DESC) as rank
FROM trip 
GROUP BY month, start_station_name 
ORDER BY month, rank
LIMIT 20;

Mark as done

Exercise

Calcule médias móveis:

-- Média móvel de viagens por dia (janela de 7 dias)
SELECT 
    date,
    daily_trips,
    AVG(daily_trips) OVER (ORDER BY date ROWS 6 PRECEDING) as moving_avg_7days
FROM (
    SELECT 
        toDate(start_date) as date,
        COUNT(*) as daily_trips
    FROM trip 
    GROUP BY date
    ORDER BY date
)
ORDER BY date;

Mark as done

Agregações complexas

Exercise

Execute análises com múltiplas agregações:

-- Estatísticas por tipo de assinatura
SELECT 
    subscription_type,
    COUNT(*) as trip_count,
    AVG(duration) as avg_duration,
    MIN(duration) as min_duration,
    MAX(duration) as max_duration,
    quantile(0.5)(duration) as median_duration,
    quantile(0.95)(duration) as p95_duration
FROM trip 
GROUP BY subscription_type;

Mark as done

Trabalhando com formatos avançados

Importando dados Parquet

O ClickHouse tem suporte nativo ao formato Parquet.

Exercise

Crie uma tabela lendo diretamente do Parquet (com conversão de tipos):

CREATE TABLE trip_parquet
(
    id String,
    duration Int32,
    start_date DateTime,
    start_station_name String,
    start_station_id Int32,
    end_date DateTime,
    end_station_name String,
    end_station_id Int32,
    bike_id String,
    subscription_type String,
    zip_code String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(start_date)
ORDER BY (start_date, start_station_id);

-- Then insert the transformed data
INSERT INTO trip_parquet
SELECT
    id,
    duration,
    parseDateTimeBestEffort(start_date) AS start_date,
    start_station_name,
    start_station_id,
    parseDateTimeBestEffort(end_date) AS end_date,
    end_station_name,
    end_station_id,
    bike_id,
    subscription_type,
    zip_code
FROM file('parquets/trip.parquet', 'Parquet');

Conversão de tipos

Como o ClickHouse pode interpretar colunas de data como String ao ler Parquet, usamos parseDateTimeBestEffort() para converter adequadamente os tipos de data tanto na partição quanto na ordenação.

Mark as done

Exercise

Teste a leitura da tabela recém criada com:

SELECT *
FROM trip_parquet
LIMIT 10;

Mark as done

Formatos de saída

Exercise

O ClickHouse suporta múltiplos formatos de saída:

-- JSON
SELECT * FROM station LIMIT 3 FORMAT JSON;

-- CSV
SELECT * FROM station LIMIT 3 FORMAT CSV;

-- Pretty (formatado para humanos!)
SELECT * FROM station LIMIT 3 FORMAT Pretty;

Mark as done

Análises geoespaciais

O ClickHouse possui funções geoespaciais avançadas.

Exercise

Faça análises baseadas em localização:

-- Junção para obter coordenadas das estações
CREATE TABLE trip_with_coords
ENGINE = MergeTree
ORDER BY (start_station_id, end_station_id)
AS SELECT 
    t.*,
    s1.lat as start_lat,
    s1.long as start_long,
    s2.lat as end_lat, 
    s2.long as end_long
FROM trip t
LEFT JOIN station s1 ON t.start_station_id = s1.id
LEFT JOIN station s2 ON t.end_station_id = s2.id;

Mark as done

Exercise

Calcule distâncias entre estações:

-- Distância aproximada entre estações (em metros)
SELECT 
    start_station_name,
    end_station_name,
    COUNT(*) as trip_count,
    geoDistance(start_long, start_lat, end_long, end_lat) as distance_meters
FROM trip_with_coords 
WHERE start_lat IS NOT NULL AND end_lat IS NOT NULL
GROUP BY start_station_name, end_station_name, start_lat, start_long, end_lat, end_long
HAVING trip_count > 10
ORDER BY distance_meters DESC
LIMIT 10;

Mark as done

Monitoramento e métricas

System tables

O ClickHouse oferece tabelas de sistema para monitoramento.

Exercise

Explore as tabelas de sistema:

-- Ver queries em execução
SELECT * FROM system.processes;

-- Estatísticas das tabelas
SELECT 
    name,
    rows,
    bytes_on_disk,
    formatReadableSize(bytes_on_disk) as size
FROM system.parts 
WHERE database = 'bike_share';

-- Performance das queries
SELECT 
    query,
    read_rows,
    read_bytes,
    result_rows,
    memory_usage,
    query_duration_ms
FROM system.query_log 
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 5;

Mark as done

Integração com Python

Exercise

Crie ou ative um ambiente virtual adequado com Python 3.12!

Mark as done

Exercise

Instale o cliente Python para ClickHouse:

Arquivo requirements.txt

clickhouse-connect==0.8.18
pandas==2.3.1
python-dotenv==1.0.1

$ uv pip install -r requirements.txt

Mark as done

Exercise

Crie um script src/clickhouse_python.py para interagir com ClickHouse:

Arquivo .env

# ClickHouse Configuration
CLICKHOUSE_HOST=localhost
CLICKHOUSE_PORT=8123
CLICKHOUSE_USERNAME=admin
CLICKHOUSE_PASSWORD=password123
CLICKHOUSE_DATABASE=bike_share

import clickhouse_connect
import pandas as pd
import os
from dotenv import load_dotenv

# Carregar variáveis de ambiente do arquivo .env
load_dotenv()

# Conectar ao ClickHouse usando variáveis de ambiente
client = clickhouse_connect.get_client(
    host=os.getenv('CLICKHOUSE_HOST', 'localhost'),
    port=int(os.getenv('CLICKHOUSE_PORT', 8123)),
    username=os.getenv('CLICKHOUSE_USERNAME', 'admin'), 
    password=os.getenv('CLICKHOUSE_PASSWORD'),
    database=os.getenv('CLICKHOUSE_DATABASE', 'bike_share')
)

# Executar consulta e obter DataFrame
result = client.query_df("""
    SELECT 
        start_station_name,
        COUNT(*) as trip_count,
        AVG(duration) as avg_duration
    FROM trip 
    GROUP BY start_station_name 
    ORDER BY trip_count DESC 
    LIMIT 10
""")

print("Top 10 estações mais utilizadas:")
print(result)

# Inserir dados via DataFrame
sample_data = pd.DataFrame({
    'id': [999, 1000],
    'name': ['Test Station 1', 'Test Station 2'], 
    'lat': [37.7849, 37.7849],
    'long': [-122.4194, -122.4094],
    'dockcount': [15, 20],
    'landmark': ['San Francisco', 'San Francisco'],
    'installation': ['2023-01-01', '2023-01-02']
})

client.insert_df('station', sample_data)
print("Dados inseridos com sucesso!")

Mark as done

Exercise

Rode o script com:

$ python src/clickhouse_python.py

Mark as done

Comparação de Performance

Exercise

Execute um benchmark simples comparando diferentes tipos de consulta:

-- Consulta simples de agregação
SELECT 
    subscription_type, 
    COUNT(*) 
FROM trip 
GROUP BY subscription_type;

-- Consulta com filtro temporal
SELECT COUNT(*) 
FROM trip 
WHERE start_date >= '2013-09-01' AND start_date < '2013-10-01';

-- Consulta complexa com múltiplas agregações
SELECT 
    toYYYYMM(start_date) as month,
    start_station_name,
    COUNT(*) as trips,
    AVG(duration) as avg_duration,
    quantile(0.95)(duration) as p95_duration
FROM trip 
GROUP BY month, start_station_name
HAVING trips > 50
ORDER BY month, trips DESC;

Mark as done

Exercícios finais

Exercise

Compare o ClickHouse com o DuckDB em relação aos casos de uso. Quando você usaria cada um?

Submit

Answer

O ClickHouse é otimizado para consultas analíticas em grandes volumes de dados, enquanto o DuckDB é mais adequado para análises em ambientes locais e em menor escala.

Usaria o ClickHouse para análises em tempo real e grandes conjuntos de dados, enquanto o DuckDB seria ideal para protótipos rápidos e análises ad-hoc.

Exercise

Crie uma análise que:

1. Identifique os fluxos de viagens mais populares (origem → destino)
2. Calcule estatísticas temporais (padrões por hora, dia da semana)
3. Analise a utilização das bicicletas individuais
4. Exporte os resultados em diferentes formatos

Mark as done

Exercise

Quais são as principais vantagens do armazenamento columnar do ClickHouse para consultas analíticas?

Submit

Documentação

Consulte a documentação oficial do ClickHouse para mais recursos avançados!

Substituindo PostgreSQL por ClickHouse no Data Warehouse

Agora que você domina os conceitos básicos do ClickHouse, vamos aplicá-lo em um cenário prático: substituir o PostgreSQL do Data Warehouse da aula anterior (DW Parte I) pelo ClickHouse.

Preparando o ambiente

Exercise

Navegue até o diretório da aula anterior onde você implementou o pipeline ETL.

Crie uma cópia da pasta 02-etl.

$ cp -r /caminho/para/dataeng-03-repo-base/02-etl /caminho/para/dataeng-03-repo-base/02-etl-clickhouse

Atenção!

Certifique-se de que todos os serviços estejam parados antes de iniciar a migração.

Mark as done

Modificando a arquitetura

Exercise

Edite o arquivo 02-etl-clickhouse/docker-compose.yml para substituir o PostgreSQL pelo ClickHouse como Data Warehouse.

Mark as done

Criando o schema ClickHouse

Exercise

Crie um novo arquivo 02-etl/sql/0001-ddl-clickhouse.sql com o schema otimizado para ClickHouse.

Mark as done

Exercise

Quais otimizações foram aplicadas por você no schema do ClickHouse em comparação com o PostgreSQL?

Submit

Answer

Otimizações que poderiam ser aplicadas (pesquise como fazer):

1. LowCardinality: Para campos com poucos valores únicos (nome, categoria, uf, status)
2. Particionamento: Tabelas particionadas por mês usando toYYYYMM()
3. Tipos específicos: UInt32/UInt64 para IDs, Decimal para valores monetários
4. Ordenação otimizada: ORDER BY considera padrões de consulta (data, categoria, etc.)
5. Remoção de constraints: Sem chaves estrangeiras ou checks para melhor performance de inserção

Adaptando o script de inicialização

Exercise

Crie um novo arquivo 02-etl/src/init_clickhouse.py para inicializar o ClickHouse.

Mark as done

Modificando o pipeline ETL

Exercise

Crie um novo arquivo 02-etl/src/etl_vendas_clickhouse.py e faça o processo de ETL.

Mark as done

Exercise

Atualize o arquivo 02-etl/requirements.txt para incluir o cliente ClickHouse.

Mark as done

Testando a nova arquitetura

Exercise

Pare os serviços anteriores (se estiverem rodando):

Atenção!

O -v no comando abaixo irá garantir que os volumes criados sejam apagados.

Utilize isto quando quiser apagar dados persistidos em volumes do docker.

$ docker compose down -v

Mark as done

Exercise

Construa e inicie os novos serviços:

$ docker compose build
$ docker compose up

Mark as done

Exercise

Verifique os logs para confirmar que:

1. O simulador de vendas está gerando dados no PostgreSQL
2. O ETL está extraindo dados do PostgreSQL e carregando no ClickHouse
3. Não há erros de conexão ou inserção

Mark as done

Validação e análises

Exercise

Conecte-se ao ClickHouse via cliente CLI para validar os dados:

$ docker exec -it clickhouse-dw clickhouse-client --user dw_user --password dw_pass --database vendas_dw

Mark as done

Exercise

Execute consultas de validação no ClickHouse:

-- Verificar contagem de registros por tabela
SELECT 'cidade' as tabela, count() as total FROM cidade
UNION ALL
SELECT 'cliente', count() FROM cliente
UNION ALL  
SELECT 'produto', count() FROM produto
UNION ALL
SELECT 'venda', count() FROM venda
UNION ALL
SELECT 'item_venda', count() FROM item_venda;

-- Análise temporal das vendas
SELECT 
    toDate(data) as data,
    count(*) as total_vendas,
    sum(valor_total) as receita_total
FROM venda
GROUP BY data 
ORDER BY data DESC
LIMIT 10;

Mark as done

Exercícios de comparação

Exercise

Compare o desempenho do ClickHouse com o PostgreSQL para consultas analíticas. Quais diferenças você observa?

Submit

Exercise

Quais são as principais vantagens de usar ClickHouse como Data Warehouse em comparação com PostgreSQL?

Submit

Exercise

Em que cenários você ainda preferiria usar PostgreSQL em vez de ClickHouse?

Submit

Exercise

Modifique o dashboard (Jupyter Notebook) para conectar-se ao ClickHouse em vez do PostgreSQL e compare os tempos de resposta das consultas.

Mark as done

Exercise

Implemente um mecanismo de monitoramento que compare métricas entre os dois sistemas: 1. Tempo de resposta das consultas 2. Uso de CPU e memória 3. Espaço em disco utilizado 4. Throughput de inserção de dados

Mark as done

Limpeza

Exercise

Para parar e remover os containers e volumes:

Warning

O -v no comando abaixo irá garantir que os volumes criados sejam apagados.

Utilize isto quando quiser apagar dados persistidos em volumes do docker.

Limpeza!

Lembre-se que você criou/alterou dois conjuntos de serviços nesta aula!

$ docker compose down -v

Mark as done