3 min de leitura ·

Depois de migrar 8TB, o Clickhouse "sequestrou" meus dados kkkk

E aí (novamente), pessoal.

Seguindo a saga de migração do PostgreSQL para o ClickHouse...

Hoje venho falar de um problema específico que estou enfrentando depois de migrar todos os dados para o ClickHouse.

Estou usando a versão self hosted do Clickhouse, não a versão cloud.

Mas antes... se este é o primeiro post que você vê dessa saga, aqui vai a lista de posts anteriores:

Continuando...

Vou tentar ser mais sucinto neste post, pois escrevi muito nos anteriores.

De ontem para hoje estou passando raiva com o ClickHouse. Motivo? Extrair os dados dele para outro DB ou até para outra tabela dentro dele mesmo é um verdadeiro parto!
E isso está começando a me deixar preocupado com situações futuras que demandem esse processo em produção.

Consegui fazer a migração de 44 bilhões de dados do PostgreSQL para uma tabela já com os ajustes finais no ClickHouse.

Até aí, tudo bem. Já pretendia colocar em produção, até que surgiu a necessidade de mudar o esquema da tabela.

Explicação: O ClickHouse possui a capacidade de particionar os dados e, na ideia de que seria um particionamento similar ao do PostgreSQL, quis logo implementar para facilitar as consultas.

Particionei com base em uma coluna que fazia sentido para acelerar as consultas.

Até que descobri que o particionamento não faz diferença nisso; pelo contrário, acaba prejudicando os inserts, já que os dados precisam ser "pulverizados" nas partições (que são pastas no disco).

A solução? Criar uma nova tabela sem particionamento (já que, no meu caso, é inútil) e mover os dados de uma para outra!

De início, pareceu bastante simples. A própria documentação do ClickHouse diz que basta executar um INSERT INTO tabela_nova SELECT * FROM tabela_antiga e o DB se encarrega de fazer isso em lotes, sem dor de cabeça.

Masss... nem tudo são flores.

Por algum motivo, essa migração estava sendo cancelada entre 4 e 5 bilhões de dados movidos.

Executei 3 vezes, e nas 3 parou no mesmo ponto.

Tentei executar uma migração via script personalizado (como fiz do PostgreSQL para o ClickHouse).

Problema: O ClickHouse não tem ID sequencial nativo, e o ORDER BY da tabela não garante uma sequência ideal de consulta para percorrer todo o DB em lotes.
E, para piorar, consultas com OFFSET são desaconselhadas no ClickHouse, pois não performam nada bem.

Nesse momento, estou escrevendo este post e pensando em como solucionar esse dilema que o ClickHouse criou. Ele praticamente "sequestrou" os dados e não quer me deixar mover kkkk.

Me pergunto como seria se, futuramente, eu precisasse abandonar o ClickHouse e migrar para outro DB.

Por enquanto, estou tentando migrar usando as partições que existem na tabela (128 no total, cada uma com algumas centenas de milhões de dados). Migrar cada uma individualmente parece funcionar, mas o problema é a tabela nova, que não possui particionamento — o que tornaria essa abordagem inviável futuramente.

Existe a possibilidade de fazer um dump dos dados em um arquivo CSV (ou outro padrão de texto), mas não imagino que seja viável considerando a quantidade massiva de 44Bi linhas.

Me resta queimar neurônios um pouco mais...

Um pouco desapontado com o ClickHouse, mas espero encontrar uma solução para esse problema.

crystian

4 meses atrás

Estou gostando muito dessa série de postagens que mostrem mais a fundo uma ferramenta e suas limitações/vantagens em comparação com outras e seus usos, aprendi muito mais dessa forma.

Pilati

4 meses atrás

Um pouco desapontado com o ClickHouse, mas espero encontrar uma solução para esse problema.

Queria falar que estou gostando muito de ver alguém quebrando a cara com uma ferramenta que não conhece. Não falo no sentido pejorativo, mas no sentido de ver uma pessoa descobrindo as limitações de uma tecnologia e tendo que lidar com elas.

Eu tenho pouquíssima experiência com esse DB e com essa série de posts estou aprendendo junto!

Pelo que estudei do clickhouse ele é um banco que se dá muito bem com resultados agregados. Mas é péssimo para resultados brutos.

A conclusão que tive é que ele nunca deve ser tratado como um banco de dados. Não é confiável guardar todas as informações nele, essas informações devem vir de outra fonte.

Minha recomendação (que não vale muito) é manter uma cópia desses dados no postgres, assim você pode importar novamente caso precise.

ethi

Autor

4 meses atrás

opa, obrigado pelo comentário. gosto de aprender na prática sobre ferramentas e sistemas, imagino que seja assim que um profissional capacitado se forme, aprendendo com erros e enfrentando problemas.

sobre o clickhouse, realmente é oque voce disse, pra buscar dados brutos ele nao se da tão bem, mas esses dados que estou armazenando nele sao justamente pra métricas e agregações, tanto que as consultas pra usar ele ja estão prontas e performam incrivelmente bem, só falta alguns ajustes mesmo e aprender um pouco mais sobre ele pra não dar BO em produção e eu ficar sem saber pra onde correr kkkk

falei sobre ele sequestrar meus dados por conta dessa dificuldade de mover eles mesmo, mas esses dados tem só 90 dias de validade, então se futuramente eu precisar abandonar o clickhouse, ainda tem a alternativa de começar a armazenar os novos dados em outro DB e ir fazendo essa alternância entre os 2 até que o clickhouse fique com dados obsoletos.
não é a melhor alternativa mas é uma saída em último caso kkkk

mas é a vida né, nada é perfeito e software sempre tem problemas hahaha

macnator

4 meses atrás

Está trabalhando diretamente nas bases ou usando uma linguagem de programação "que não seja SQL"?
Scripts Python resolvem — como orquestrador de lotes por hash com retry + dedupe + contagem. É a forma mais prática e segura de migrar seus 44 bi sem OFFSET e sem depender de um ID sequencial se eu entendi bem seu problema.

Confirme o motivo das paradas entre 4–5 bi
Veja o system.query_log e ajuste timeouts/parts, algo como isso:

SELECT query_id, type, exception, read_rows, written_rows, ProfileEvents['InsertedRows']
FROM system.query_log
WHERE event_time >= now() - INTERVAL 1 DAY
  AND query LIKE 'INSERT INTO nova_tabela SELECT%';

ethi

Autor

4 meses atrás

opa, obrigado pela dica, uso script em js pra fazer migrações entre DBs, mas o clickhouse em específico não se encaixa no padrão que usei ate então.

sobre a parada, descobri que era um problema paralelo.
no meu arranjo atual tenho 1 tabela principal onde chegam os dados brutos e outras 2 tabelas de agregações que são populadas por 2 MVs que processam os inserts assim que chegam.

o problema estava em uma dessas MVs que estava processando os inserts de forma mais lenta do que eles estavam sendo inseridos na tabela principal, e como o clickhouse faz muito trabalho de forma assíncrona, na hora de sincronizar os dados, o DB tava gerando dados temporários muito maiores do que o disco disponível (por conta de um armazenamento de state que faz comparações constantemente pra remover duplicações)

estou pensando em fazer um post sobre isso e como solucionei esse problema.

mas foi só remover essa MV problemática que eu consegui mover os dados entre tabelas, inclusive num tempo recorde kkkk coisa de 2h movi 44bi linhas.

agora o novo problema é re-popular a tabela de agregações com a MV que tive que remover.

macnator

4 meses atrás

Olha o código abaixo em node.js dá uma ajustada pro teu cenário já que usa js

import { createClient } from '@clickhouse/client';

const ch = createClient({
  host: process.env.CHK_HOST,  // https://host:8443
  username: process.env.CHK_USER,
  password: process.env.CHK_PASS,
  database: process.env.CHK_DB,
});

const BUCKETS = 256;
const CUTOFF = '2025-09-08 00:00:00'; // opcional (estratégia de cutoff)

async function backfill() {
  for (let i = 0; i < BUCKETS; i++) {
    const q = `
      INSERT INTO agg_tabela
      SELECT
        k1, k2,
        sumState(m1) AS m1_state,
        uniqCombinedState(user_id) AS u_state,
        countState() AS cnt_state
      FROM fatos
      WHERE sipHash64(k1, k2) % ${BUCKETS} = ${i}
        AND event_time <= parseDateTimeBestEffort('${CUTOFF}')  -- remova se não usar cutoff
      GROUP BY k1, k2
      SETTINGS max_execution_time=0
    `;
    await ch.exec({ query: q });
    console.log(`bucket ${i} OK`);
  }
}

backfill().then(()=>process.exit(0)).catch(e=>{console.error(e);process.exit(1);});

O que isso ai não faz:

Não cria a MV nova depois do backfill — você precisa criar com:

CREATE MATERIALIZED VIEW mv_agg TO agg_tabela AS SELECT ... GROUP BY ...;

Não valida as contagens automaticamente. Se quiser isso, adicione lógica de validação com:

const res = await ch.query({
  query: `SELECT count() FROM agg_tabela WHERE sipHash64(k1,k2) % 256 = ${i}`,
  format: 'JSONEachRow',
});

Espero que ajude, acho muito interessante fazer artigo de como resolveu, acho que isso é o maior déficit na web, as pessoas pedem ajuda, resolvem e não voltam para dar um feedback de como resolveram e quem lê em busca de ajuda se lasca.

Oletros

4 meses atrás

Meus 2 cents,

Chutando um pouco - pelo que vi o clickhouse tem uma funcao interna chamada "row_number()"

https://clickhouse.com/docs/sql-reference/window-functions/row_number

Talvez juntando isso com a ideia do @macnator possa ser util - algo como so comecar a exportar se o row_number() for maior que x.

Uma outra alternativa eh usar 'NOT EXISTS':

SELECT customer_id, customer_name
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

Mas pelo volume nao sei se seria viavel

Outra ideia seria um LEFT JOIN (semelhante ao NOT EXISTS acima):

SELECT c.CustomerID, c.CustomerName
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

OU mais direto:

SELECT
    so.*
FROM
    SalesOrders so
LEFT JOIN
    ExportLog el ON so.OrderID = el.OrderID
WHERE
    el.OrderID IS NULL;

Novamente, nao sei se eh viavel (pelos volumes).

Boa sorte - Saude e sucesso !

ethi

Autor

4 meses atrás

opa, muito obrigado pelas dicas. irei analisar com calma se funcionam.

mas o processo de mover os dados de uma tabela pra outra acabou funcionando depois que usei o clickhouse-client direto no terminal.
com ele eu consegui descobrir o erro, era um problema com uma MV que faz agregações em uma outra tabela conforme os inserts chegam, removi temporariamente essa MV e a migração foi tranquila.

agora o próximo passo é popular manualmente a tabela de agregação já que a MV que fazia isso foi removida.

afonsolelis

4 meses atrás

Agora é a hora de começar a testar o Spark com python, o problema não é o banco em si, eu vi mais abaixo como você resolveu o problema. Mas considere utilizar um spark sql ou com python em um notebook jupyter provisionado com docker compose.

ethi

Autor

4 meses atrás

opa, não conhecia esse spark, vlw pela dica, vou ja estudar sobre.