1. Extração e Transformação do Schema¶

1.1. Visão Geral¶

Esta fase é estrategicamente crítica devido à antiguidade e leniência do ambiente de origem (MySQL 5.1). Uma exportação direta de DDL (Linguagem de Definição de Dados) é insuficiente e levaria a falhas significativas de compatibilidade no Cloud SQL. É necessário um processo de transformação automatizado e mais sofisticado, não apenas para garantir a compatibilidade, mas também para modernizar o schema conforme as melhores práticas atuais.

A metodologia central envolve um script Python personalizado (scripts\extract_schema.py) que se conecta programaticamente ao banco de dados de origem, busca as instruções CREATE de cada objeto, as analisa e reescreve o DDL de acordo com um conjunto de regras predefinidas. Essa abordagem automatizada garante consistência e elimina o risco de erro manual.

O desafio principal é a variedade de incompatibilidades e padrões legados presentes no schema original. Essas incompatibilidades incluem, mas não se limitam a:

• Definições de tabelas que incluem restrições e chaves, que precisam ser separadas.
• Cláusulas DEFINER em views, rotinas e triggers que vinculam objetos a usuários específicos.
• Uso do motor (engine) legado MyISAM, que deve ser atualizado para InnoDB.
• Colunas com valores DEFAULT inválidos, como '0000-00-00' para tipos de data.
• Ausência de chaves primárias em tabelas com colunas AUTO_INCREMENT.
• Colunas TEXT e BLOB com cláusulas DEFAULT, que não são suportadas.
• Colunas varchar e tinytext que precisam ser atualizadas para utf8mb4 para compatibilidade com Unicode completo.
• Colunas NOT NULL, mas que apresentavam data inválida 0000-00-00.

O resultado desta fase é um conjunto granular de arquivos .sql, meticulosamente organizados por tipo de objeto (ex: tabelas, restrições, views, rotinas, triggers) e enviados para um bucket designado no Google Cloud Storage (GCS). Essa estrutura granular é uma decisão arquitetônica chave, fornecendo a flexibilidade para executar cada etapa do processo de carregamento de forma independente, o que é crítico para um tratamento de erros robusto e ajuste de desempenho ( performance tuning) nas fases posteriores.

1.2. Transformações e Correções Automatizadas de DDL¶

As regras de transformação a seguir foram desenvolvidas por um processo iterativo de simulações (dry runs) de implantação do schema no ambiente de destino do Cloud SQL. Cada regra aborda diretamente uma incompatibilidade específica ou um padrão legado descoberto durante os testes, como os erros 1075 e 1067, garantindo que o DDL final seja robusto e moderno.

1.3. Organização e Armazenamento dos Artefatos do Schema¶

Cada artefato de schema gerado é nomeado com o nome da tabela ou do objeto correspondente, facilitando a rastreabilidade. Isso também permite operações granulares, onde somente tabelas ou objetos específicos podem ser recriados, ou ajustados conforme necessário. Isso é particularmente útil em um processo de pipeline automatizado, onde a idempotência e a capacidade de retomar de falhas são cruciais. Com os artefatos do schema extraídos, transformados e armazenados no GCS, a fase paralela de preparação dos dados reais para a migração pode prosseguir. A extração direta de metadados do MySQL 5.1 é insuficiente devido a incompatibilidades de sintaxe e padrões legados que não são aceitos no Google Cloud SQL. Esta fase utiliza um script Python personalizado para ler o schema de origem, aplicar transformações programáticas e gerar arquivos DDL modernizados.

1.4. Variáveis do Servidor de Origem (MySQL 5.1)¶

Para garantir a reprodutibilidade da migração, abaixo estão as principais variáveis de ambiente capturadas na fonte. Elas serviram de base para as regras de transformação de collation e engine.

Nota: Para a lista completa de todas as 290 variáveis do sistema, consulte o arquivo técnico em nosso repositório de governança.

A estratégia central é decompor o banco de dados em artefatos granulares, permitindo a execução independente de cada etapa.

2. O protótipo do script de extração e transformação de schema¶

Optou-se por modelar um script python no jupyter notebook para a extração e transformação do schema. O script se conecta ao banco de dados MySQL 5.1, extrai o DDL de cada objeto, aplica as transformações necessárias e salva os arquivos .sql resultantes em um bucket do Google Cloud Storage (GCS). O script é modular, permitindo fácil manutenção e ajustes conforme necessário. A idéia éra testar a forma mais rápida para extrair os dados e sua consequente carga no MySQL no ecossistema da Google Cloud. Então seria uma migração de one-shot, ou seja, um processo de migração onde os dados são extraídos, transformados e carregados em uma única etapa. O script é projetado para ser executado uma vez, mas pode ser adaptado para processos iterativos se necessário. O foco principal é garantir que o DDL gerado seja compatível com o Cloud SQL, evitando erros comuns de sintaxe e incompatibilidade. A biblioteca utilizada foi SQlAlchemy para a conexão e extração do schema, e o Google Cloud Storage Client Library para Python para o upload dos arquivos .sql gerados. O script é estruturado em funções para facilitar a leitura e manutenção, e inclui tratamento de erros para garantir que quaisquer problemas durante a extração ou transformação sejam adequadamente registrados e tratados.

"""
Módulo: step_00_extract_schema.py
Projeto: InfoEscola (Data Engineering Pipeline - Fase 1)
Localização Original: src/infoescola/migration/

RESUMO:
Script pioneiro responsável por extrair, transformar e modernizar metadados (DDLs)
do MySQL 5.1 (legado) para publicação no Google Cloud Storage (GCS). Ele garante
que o "esqueleto" do banco de destino no Cloud SQL (MySQL 8.1) seja compatível,
seguro e otimizado.

PRINCIPAIS FUNCIONALIDADES:
1. Extração Abrangente: Mapeia Tabelas, Views, Routines, Triggers, Events e
   Constraints diretamente do 'information_schema'.
2. Modernização de Engine e Charset: Converte tabelas de MyISAM para InnoDB e
   altera o encoding de latin1 para utf8mb4_unicode_ci de forma automatizada.
3. Sanitização de DDL:
   - Remove cláusulas DEFINER (essencial para portabilidade na nuvem).
   - Corrige datas nulas inconsistentes ('0000-00-00') para 'DEFAULT NULL'.
   - Converte tipos VARCHAR > 255 para TEXT para evitar estouro de limite de linha.
   - Converte TINYTEXT para TEXT para padronização.
4. Desacoplamento de Constraints: Identifica e separa Foreign Keys em scripts
   idempotentes (DROP/ADD com NOT VALIDATED) para permitir que a carga inicial
   de dados ocorra sem erros de dependência.
5. Orquestração Paralela: Utiliza o TaskRunner em modo multithread para processar
   múltiplos artefatos simultaneamente, otimizando o tempo de extração.

LOGÍSTICA DE ARQUIVOS (GCS):
Os DDLs transformados são organizados hierarquicamente no Bucket:
- /SCHEMA/01_create_tables/
- /SCHEMA/02_create_views/
- /SCHEMA/05_create_constraints/ (FKs isoladas para execução posterior)

NOTAS TÉCNICAS:
- Utiliza SQLAlchemy para introspecção do banco e Google Cloud Storage SDK para persistência.
- Lógica de idempotência injetada via SQL dinâmico (PREPARE/EXECUTE) nos scripts de FK.
- Desenhado para execução via CLI com suporte a filtros granulares (--tables, --all-views, etc.).
"""

Nos testes observou que a extração seria mais rápida utilizando a biblioteca connectorx. Essa biblioteca apresentou um desempenho excelente em comparação a polars e pandas:

O script step_01_load_schema.py orquestra a subida dos artefatos para o banco de destino seguindo uma ordem lógica de dependências estruturais, garantindo que os objetos "base" existam antes dos objetos que os referenciam.

A ordem de execução definida no dicionário GCS_ARTIFACT_PATHS do script é a seguinte:

1. TABLES (01_create_tables): Criação das tabelas "nuas" (sem Foreign Keys), servindo de fundação para todos os outros objetos.

2. VIEWS (02_create_views): Definidas após as tabelas, pois dependem da existência destas para serem validadas.

3. ROUTINES (03_create_routines): Procedures e Functions, que podem referenciar tabelas ou views.

4. EVENTS (04_create_events): Agendamentos internos do banco de dados.

5. CONSTRAINTS (05_create_constraints): Aplicação das Foreign Keys que foram isoladas anteriormente. Nesta etapa, o script desabilita temporariamente o FOREIGN_KEY_CHECKS para garantir que a carga ocorra sem erros de validação cruzada.

6. TRIGGERS (06_create_triggers): Gatilhos de automação, carregados por último para evitar disparos acidentais durante a criação da estrutura ou carga inicial de dados.

Detalhes Importantes da Ordem: Multi-Pass: Para os tipos VIEW e ROUTINE, o script executa uma lógica de múltiplas passagens (até 10 tentativas) para resolver dependências circulares onde um objeto referencia outro que ainda não foi criado no mesmo lote.

Single-Pass: Para os demais tipos (TABLE, CONSTRAINT, TRIGGER, EVENT), a execução é feita em passo único, assumindo que a ordem de precedência principal já resolve as dependências.

"""
Módulo: step_01_load_schema.py
Projeto: InfoEscola (Data Engineering Pipeline - Fase 1)
Localização Original: src/infoescola/migration/

RESUMO:
Orquestrador de carga de DDLs responsável por reconstruir a estrutura do banco de
dados no destino. O script consome os artefatos SQL do GCS (gerados pelo step_00)
e os executa seguindo uma lógica de dependências para garantir a criação correta
de tabelas, views, procedures e outros objetos.

PRINCIPAIS FUNCIONALIDADES:
1. Execução Multi-Pass (Inteligência de Dependência): Implementa uma lógica de
   múltiplas passagens (até 10 tentativas) especificamente para VIEWS e ROUTINES.
   Isso resolve automaticamente erros de dependência circular onde um objeto
   referencia outro que ainda não foi criado.
2. Mecanismo de Circuit Breaker: Monitora se o número de falhas retentáveis
   diminui a cada passe. Caso o progresso estagne, o processo é interrompido
   para evitar loops infinitos, sinalizando erros lógicos ou circulares.
3. Tratamento de Delimitadores Complexos: Reconhece e processa corretamente
   blocos de código que utilizam '$$' (comum em Triggers e Routines) versus
   o ';' padrão.
4. Gestão de Foreign Keys: Durante a carga de CONSTRAINTS, desabilita
   temporariamente o 'FOREIGN_KEY_CHECKS' para permitir a criação de chaves
   estrangeiras em lote sem conflitos de validação imediata.
5. Filtro de Erros Retentáveis: Mapeia códigos de erro específicos do MySQL
   (ex: 1146, 1305, 1356) para distinguir entre falhas de dependência (temporárias)
   e erros de sintaxe ou permissão (permanentes).

LOGÍSTICA DE CARGA (GCS_ARTIFACT_PATHS):
O script segue a ordem lógica definida na extração:
- TABLES -> VIEWS -> ROUTINES -> EVENTS -> CONSTRAINTS -> TRIGGERS.

NOTAS TÉCNICAS:
- Utiliza TaskRunner em modo multithread para paralelizar a carga de artefatos
  independentes, otimizando o tempo total de provisionamento.
- Implementa logs detalhados (load_schema_error.log) para rastrear exatamente
  quais instruções SQL falharam e por quê.
- Compatível com a estratégia "Schema First", permitindo a criação de tabelas
  nuas antes da ingestão massiva de dados.
"""

2.1. O script de extração e transformação de schema¶

O script utiliza a biblioteca ConnectorX para ler dados do MySQL diretamente para o formato Apache Arrow, que é então processado em "pedaços" (chunks) e enviado ao GCS em formato Parquet. O uso do arrow_stream permite que o script processe tabelas gigantes sem estourar a memória RAM, pois os dados nunca são carregados integralmente no ambiente (Colab).

2.2. Preparação e Configuração de Estado¶

Ao iniciar a função main(), o script estabelece as conexões com o MySQL e o bucket do GCS.

Ele utiliza dois arquivos de controle no GCS:

• tabelas_para_ingestao.txt: se não existir, o script varre o banco de dados e gera uma listade todas as tabelas.

• tabelas_ingeridas.txt: funciona como um "checkpoint". O script lê este arquivo para saber quais tabelas já foram processadas, permitindo que a migração seja retomada de onde parou em caso de queda.

2.3. Loop Principal¶

Para cada tabela na lista de pendências, o script executa:

• Instanciação do ArrowStreamToGCSWriter: Cria um objeto responsável por gerenciar o buffer de memória (configurado para até 4GB no código) e a escrita no GCS.

• Geração de Query Robusta: A função generate_production_select monta um SELECT que trata automaticamente problemas comuns, como datas nulas do MySQL (0000-00-00), convertendo-as para NULL para evitar erros de tipagem no Arrow/Parquet.

2.4. Pipeline de Streaming¶

Aqui ocorre a execução do ConnectorX com o parâmetro return_type="arrow_stream". O fluxo de dados funciona assim:

• Leitura: O ConnectorX extrai um batch (lote) de registros (ex: 100.000 linhas) do MySQL.

• Acúmulo: O método writer.add_batch(batch) recebe esse lote e o coloca em um buffer interno.

• Flush Automático: Quando a soma dos lotes no buffer atinge o limite definido (ex: 4096 MB),o script automaticamente:

  • Transforma o buffer em uma tabela Arrow.

  • Escreve um arquivo .parquet no GCS (ex: part-00001.parquet).

  • Limpa a memória para o próximo lote.

2.5. Finalização da Tabela¶

Após processar todos os registros da tabela, o comando writer.close() garante que qualquer dado remanescente no buffer seja enviado ao GCS. A tabela é então registrada no arquivo de "concluídas", e o coletor de lixo do Python (gc.collect()) é chamado para liberar memória antes de iniciar a próxima tabela (não é necessário chamar o GC, mas sabe como é...).

A idéia é: - Zero Cópia: Ao usar ConnectorX e Arrow, o script evita a conversão pesada para objetos Python (como listas ou dicionários), que consomem muita memória.

• Escalabilidade: Como ele divide a tabela em vários arquivos Parquet (part-00001, part-00002), você consegue migrar tabelas de centenas de gigabytes mesmo usando uma máquina com pouca RAM.

• Resiliência: O sistema de arquivos de estado (.txt) garante que você não precise recomeçar do zero se a conexão cair após 10 horas de execução.

"""
Módulo: step_02_data_extraction_hex.py
Projeto: InfoEscola (Data Engineering Pipeline - Fase 2)
Localização Original: src/infoescola/migration/

RESUMO:
Orquestrador de extração massiva de dados focado em integridade e performance.
Realiza a leitura da origem (MySQL 5.1) e o carregamento para a Camada Bronze
do Data Lake (GCS), utilizando a técnica de conversão HEX() para blindar o
transporte de dados sensíveis, binários ou com encodings legados (Latin1).

PRINCIPAIS FUNCIONALIDADES:
1. Extração via SQL HEX: Converte colunas para representação hexadecimal no
   ato da leitura, garantindo que caracteres especiais ou quebras de linha
   não corrompam os arquivos TSV gerados.
2. Streaming com Chunking: Gerencia a extração em lotes (chunks) configuráveis,
   evitando o consumo excessivo de memória e permitindo o particionamento
   automático de tabelas volumosas no GCS.
3. Gerenciamento de Estado Resiliente:
   - Integra-se ao 'PipelineStateManager' (SQLite local) para registrar o
     progresso de cada tabela.
   - Suporta 'Checkpoints': permite retomar extrações interrompidas pulando
     tabelas já concluídas.
4. Modo Reconciliação: Funcionalidade específica para reprocessar apenas tabelas
   marcadas com status 'FAILED' em execuções anteriores, otimizando o retrabalho.
5. Tuning de Performance: Permite ajuste fino de 'chunk_size' (tamanho do arquivo
   no GCS) e 'fetch_size' (quantidade de linhas por leitura no banco).

LOGÍSTICA DE DADOS (GCS):
Os dados são salvos no formato TSV compactado (.tsv.gz) seguindo o padrão:
- /TSV/{table_name}/part-00001.tsv.gz
- /TSV/{table_name}/part-00002.tsv.gz

NOTAS TÉCNICAS:
- Utiliza 'TaskRunner' em modo multithread para paralelizar a extração de
  múltiplas tabelas simultaneamente (I/O-bound).
- Depende do módulo 'transformers.build_select_hex' para a construção dinâmica
  das queries SQL de extração.
- Implementa um sistema de 'Lock' para garantir a integridade das atualizações
  no banco de estado SQLite durante a execução paralela.
"""

3. Por que não usar mysqldump ou ferramentas de migração tradicionais?¶

Ferramentas tradicionais como mysqldump ou utilitários de migração específicos do MySQL são inadequados para este cenário devido a várias limitações:

1. Incompatibilidade de DDL: O mysqldump gera DDL específico para a versão do MySQL em uso. No caso do MySQL 5.1, seria necessário rodar o mysqldump da versão 5.1, ou seja, precisaria baixar o mysql 5.1 para ter acesso ao utilitário.
2. Falta de Transformação Automatizada: Ferramentas tradicionais não possuem a capacidade de aplicar transformações programáticas ao DDL, como a separação de restrições, remoção de cláusulas DEFINER, ou modernização do motor de armazenamento. Isso exigiriaria uma intervenção manual significativa, aumentando o risco de erros e inconsistências.
3. Desempenho e Escalabilidade: O mysqldump e ferramentas similares não são otimizados para lidar com bancos de dados grandes e complexos, especialmente quando se trata de migrações para ambientes em nuvem. Eles podem consumir muita memória e tempo, e não possuem mecanismos eficientes de streaming ou processamento em lote, o que é crucial para migrações de grande escala.
4. Falta de Flexibilidade: Ferramentas tradicionais geralmente não permitem a granularidade necessária para organizar os artefatos do schema em arquivos separados por tipo de objeto, o que é uma parte fundamental da estratégia de migração adotada para garantir flexibilidade e controle durante o processo de implantação no Cloud SQL.
5. Texto e Gzip: O mysqldump gera arquivos de texto que podem ser compactados com gzip, mas isso não é ideal para o processo de migração planejado, que se beneficia do formato Parquet para eficiência de armazenamento e desempenho de leitura no ambiente de destino.

Durante a migração do banco de dados legado (MySQL 5.1) para o MySQL 8.0 (no Cloud SQL), diversos problemas e bloqueios surgiram tanto na recriação da estrutura (Schema) quanto na carga de dados (Ingestão). Os desafios enfrentados dividem-se em restrições rigorosas do novo motor, incompatibilidades de sintaxe, problemas de concorrência e comportamento de tipos de dados.

Abaixo estão os principais problemas enfrentados:

3.1. Incompatibilidades de Schema e Rigor do MySQL 8.0¶

• Motor MyISAM Desabilitado (Erro 3161): O Cloud SQL desabilita o motor legado MyISAM por padrão para garantir transações ACID. Tabelas que usavam esse motor tiveram que ser convertidas para InnoDB.
• Restrições de AUTO_INCREMENT (Erro 1075): O MySQL 8.0 exige obrigatoriamente que qualquer coluna com AUTO_INCREMENT seja definida como uma chave (PRIMARY KEY ou UNIQUE KEY). O DDL antigo não respeitava essa restrição e as tabelas eram rejeitadas.
• Proibição de DEFAULT em campos de Texto (Erro 1101): Colunas com tipos TEXT, BLOB, JSON ou GEOMETRY não podem conter a cláusula DEFAULT.
• Fim da "Data Zero" (Erro 1067): Devido ao sql_mode mais estrito, o MySQL 8.0 rejeita valores inválidos como '0000-00-00' ou '0000-00-00 00:00:00' definidos como DEFAULT em colunas DATE e TIMESTAMP. Foi necessário substituí-los por DEFAULT NULL ou CURRENT_TIMESTAMP na camada de ingestão.
• Expansão de Tamanho por Encoding (Warning 1265 e Erro 1074): A conversão do formato latin1 para utf8mb4 fez com que caracteres acentuados passassem de 1 para 2 bytes. Isso causou truncamento de dados em colunas TINYTEXT que ultrapassavam o limite de 255 bytes. Além disso, a definição de variáveis em funções excedia o limite de 65.535 bytes do MySQL (ex: VARCHAR(50000)), exigindo a conversão para TEXT.

3.2. Sintaxe Obsoleta e Conflitos de Palavras Reservadas¶

• Novas Palavras Reservadas (Erro 1064): Consultas em rotinas legadas usavam Rank como alias, o que causou erro, pois Rank tornou-se uma palavra reservada para Window Functions no MySQL 8.0.
• Sintaxes Obsoletas em Rotinas: Houve quebras por comandos antigos, como uso do operador matemático + após um comando temporal (ex: INTERVAL + Minutes MINUTE), e também o uso incorreto da cláusula INTO (variáveis de atribuição) aninhada em subconsultas.
• Índices Inexistentes (Erro 1176): Criações de Views falhavam ao apontarem para dicas de índice (USE INDEX) que haviam sido renomeados ou que não existiam mais no novo schema.
• Sintaxe de DROP de Constraint: Para garantir que a aplicação do Schema fosse "idempotente" e não gerasse duplicidade (Erro 1826), foi necessário utilizar o comando DROP CONSTRAINT IF EXISTS, pois o comando DROP FOREIGN KEY antigo não suporta IF EXISTS ou falha no MySQL 8.0.

3.3. Problemas de Ordem, Execução e Concorrência¶

• Deadlocks ao Inserir Constraints (Erro 1213): Executar a carga de FOREIGN KEYS paralelamente em várias threads gerava um cabo de guerra (Deadlock) entre as tabelas pai e filha travando os metadados. Foi exigido que o processo operasse com uma única thread (--workers 1).
• Lentidão Extrema na Validação: Criar constraints em tabelas muito grandes sem dados pré-saneados travava a ingestão, forçando o banco a varrer todas as linhas para verificar integridade referencial. A solução foi aplicar a cláusula NOT VALIDATED temporariamente ou alterar o FOREIGN_KEY_CHECKS.
• Ordem Incorreta de Dependências (Erro 1146 e 1305): Views e Funções falhavam fatalmente ao referenciar outros objetos que não haviam sido criados ou migrados primeiro. O script exigiu uma lógica de retentativas múltiplas (multi-pass). Também houve problemas de dependência de KEY (índices), pois uma FK exige a existência de um índice prévio.
• Quebra de Delimitadores de DDL: Usar divisores de string simples (split(';')) em Python cortava as PROCEDURES e TRIGGERS no meio, pois essas contêm vários comandos finalizados em ponto e vírgula, obrigando a criação de um parser especial para detectar e pular o escopo do comando DELIMITER.
• Escopo de Conexões em Pool: Comandos como SET FOREIGN_KEY_CHECKS=0 têm efeito apenas na sessão ativa. Quando os scripts usavam pool de conexões distribuídas, os comandos DDL caíam em conexões diferentes, reativando a checagem e travando a exclusão de tabelas (DROP) entre bancos com dependências mútuas.

3.4. Anomalias na Carga e Comportamento de Dados¶

• Valores Zero no AUTO_INCREMENT: O sistema de origem possuía o valor literal 0 na chave primária de algumas tabelas. Ao tentar inserir esse 0 via ingestão normal, o comportamento padrão do MySQL 8.0 ignorava o número e gerava a próxima sequência válida de AUTO_INCREMENT (inserindo o ID 1). A correção só ocorreu aplicando o modo especial SET SESSION sql_mode = CONCAT(@@sql_mode, ',NO_AUTO_VALUE_ON_ZERO') no carregamento.
• Erros com Booleanos e Binários (BIT e TINYINT): Ao exportar dados utilizando o LOAD DATA INFILE, o MySQL recebia representações em formato string de texto (ex: "false" ou a versão em bytes "b'\x00'"). Por se tratar de uma string não vazia, o sistema avaliava pelo tamanho do campo e convertia automaticamente todo dado importado para verdadeiro (true / 1) na coluna destino BIT(1). A solução com mais alto desempenho exigiu que, na fase de geração dos TSVs, esses dados booleanos fossem convertidos para os literais em byte nulo (\0 ou \1).

3.5. Configurações de Nuvem e Privilégios¶

• Privilégios de SUPER (Erro 1419): Devido ao MySQL Cloud SQL funcionar como um banco de dados gerenciado seguro, o usuário não possuía privilégios SUPER para inserir as Functions e Procedures no contexto do binary logging. Isso forçou a alteração de configuração da própria instância do Cloud para aceitar a flag log_bin_trust_function_creators = On.

3.6. LOAD DATA¶

O uso do comando LOAD DATA (especificamente LOAD DATA LOCAL INFILE) enfrentou uma série de obstáculos severos porque ele é projetado para ser um carregador de texto extremamente rápido, mas estruturalmente "cego" (sem inferência inteligente de tipos). A transição dos dados de formatos tipados (MySQL -> Parquet -> DataFrame) para um formato de texto sem tipagem (TSV) causou uma "perda de fidelidade", resultando nos seguintes problemas:

1. Incompatibilidade bizarra com o tipo BIT(1) (Booleanos)

   • Na origem, as colunas lógicas eram do tipo bit(1). Ao exportar esses dados para TSV, eles eram gravados como strings literais, como "false" ou "b'\x00'". O LOAD DATA tenta ler o início da string para converter em número; como não começavam com dígitos, a conversão falhava. O cenário ficou ainda mais complexo quando os valores foram substituídos manualmente pela string "0". O MySQL continuou inserindo como true (1) no destino . Isso ocorre porque, ao inserir uma string em uma coluna BIT, o MySQL avalia o comprimento em bytes da string, e não o seu valor numérico; como a string "0" tem 1 byte de comprimento (que é maior que 0), o banco a interpretava como verdadeiro. A solução exigiu passar os bytes nulos literais (\0 e \1) diretamente no arquivo.

2. Conversão acidental de NULL para '0000-00-00'

   • Durante a exportação do DataFrame para o arquivo de texto, valores nulos (None ou NaN) eram frequentemente convertidos em strings vazias (''). Quando o LOAD DATA lia essa string vazia para preencher uma coluna do tipo DATE ou DATETIME, ele a convertia incorretamente para a data inválida '0000-00-00', causando corrupção silenciosa de dados ou falhas devido ao SQL_MODE mais estrito do destino.
3. Incapacidade de exportar dados binários para CSV/TSV
   • Como algumas colunas do MySQL legado foram ingeridas no Parquet como binários (large_binary), ferramentas como o Polars retornavam o erro datatype binary cannot be written to CSV ao tentar gerar o arquivo TSV para o LOAD DATA. Um formato de texto puro simplesmente não sabe como representar sequências arbitrárias de bytes sem uma conversão explícita (como Base64).
4. Bloqueios de Segurança do Servidor (local_infile)
   • O comando LOAD DATA LOCAL INFILE é considerado um risco de segurança e vem desabilitado por padrão em instâncias modernas como o Cloud SQL. O servidor rejeitava a carga (gerando erros como Can't find file 'DUMMY') até que a flag de permissão local_infile=On fosse explicitamente ativada tanto nas configurações do servidor quanto na string de conexão do script cliente.

3.7. Carga para o destino (MySQL 8.0 na Google Cloud SQL)¶

O fluxo é dividido em duas grandes passadas para resolver o problema clássico de "quem vem primeiro: o ovo ou a galinha" em bancos de dados relacionais:

3.8. Estrutura e Dados¶

Nesta fase, o objetivo é criar as tabelas e enchê-las com os dados o mais rápido possível.

• Busca do DDL Original: Para cada tabela, ela executa SHOW CREATE TABLE na origem para obter o comando exato de criação.

• Limpeza de Constraints (separate_ddl_and_constraints): O script remove temporariamente as definições de FOREIGN KEY do comando CREATE TABLE. Isso é vital porque você não pode inserir dados em uma tabela "Filha" se a tabela "Pai" ainda não foi criada ou preenchida.

• Criação da Tabela "Limpa": A tabela é criada no destino apenas com colunas e chaves primárias.

• Carga de Dados Otimizada (load_parquet_to_destination):

  • Lê os arquivos Parquet do GCS.

  • Utiliza o comando LOAD DATA LOCAL INFILE do MySQL, que é ordens de grandeza mais

  • rápido que o INSERT comum, pois faz um "bulk load" direto para o disco.

• Checkpoints: Cada tabela finalizada é gravada em passada1_tabelas_concluidas.txt, permitindo retomar o trabalho se o Colab desconectar.

3.9. Aplicação de Constraints¶

Após todas as tabelas estarem criadas e com seus dados carregados, o script inicia a segunda rodada.

• Reconstrução das Chaves Estrangeiras: Ele percorre a lista de comandos ALTER TABLE que foram guardados na Passada 1.

• Vinculação: Agora que todas as tabelas existem, o MySQL consegue validar as relações entre elas sem erros de "tabela inexistente".

• Finalização: Assim como na fase anterior, cada constraint aplicada é registrada em um arquivo de estado (passada2_constraints_concluidas.txt).

É uma boa abordagem porque: * Performance: O LOAD DATA ignora verificações pesadas de índices durante a carga inicial. * Ordem de Dependência: Ao deixar as Foreign Keys para o final, você não precisa se preocupar em qual ordem as tabelas são carregadas. * Resiliência: O uso dos arquivos de estado (PASS1_STATE_FILE e PASS2_STATE_FILE) transforma um processo que poderia durar horas em algo "imune" a interrupções.

"""
Módulo: step_03_load_data_hex.py
Projeto: InfoEscola (Data Engineering Pipeline - Fase 3)
Localização Original: src/infoescola/migration/

RESUMO:
Orquestrador de carga massiva responsável por reconstruir os dados no banco de
destino. O script realiza o download dos arquivos TSV.GZ do GCS e utiliza o comando
'LOAD DATA LOCAL INFILE' do MySQL para garantir a máxima velocidade de inserção,
revertendo a codificação HEX aplicada na Fase 2 para os tipos de dados originais.

PRINCIPAIS FUNCIONALIDADES:
1. Reversão HEX Inteligente (build_load_sql): Reconstrói os dados "on-the-fly"
   durante a carga, aplicando UNHEX() e CAST() para restaurar tipos numéricos,
   datas e binários, além de converter textos para utf8mb4.
2. Pipeline de Streaming com Named Pipes (FIFOs): Utiliza pipes nomeados do
   sistema operacional para extrair, descomprimir e carregar os dados no MySQL
   sem a necessidade de salvar arquivos temporários em disco, economizando IOPS.
3. Tratamento de Integridade e IDs:
   - Gerencia valores nulos explicitamente (\N).
   - Utiliza 'NO_AUTO_VALUE_ON_ZERO' para preservar IDs originais em colunas
     AUTO_INCREMENT, garantindo a fidelidade dos dados legados.
4. Orquestração com State Management:
   - Integra-se ao 'PipelineStateManager' para monitorar o status da carga.
   - Implementa truncamento de tabelas antes da carga com desabilitação
     temporária de Foreign Key Checks para evitar bloqueios.
5. Paralelismo Multithread: Carrega múltiplas tabelas simultaneamente,
   otimizando o throughput de rede entre o GCS e o Cloud SQL.

LOGÍSTICA DE EXECUÇÃO:
- Origem: gs://bucket/migration/DB/TSV/{table_name}/part-*.tsv.gz
- Destino: Cloud SQL (MySQL 8.1)
- Fluxo: Download GCS -> Decompress (Gzip) -> Named Pipe -> LOAD DATA LOCAL.

NOTAS TÉCNICAS:
- Requer que o cliente MySQL e o servidor permitam 'LOCAL INFILE'.
- Utiliza 'tqdm' para fornecer feedback visual do progresso por tabela.
- Sistema de tratamento de erros robusto com rollback de status no StateManager
  em caso de falha de blob ou conexão.
"""

4. Verificando se tudo que ta lá, ta aqui. Ou seria o contrário?¶

Após a extração e transformação do schema, é crucial verificar se todos os objetos do banco de dados original foram corretamente processados e estão presentes nos arquivos .sql gerados.

Isso pode ser feito comparando a lista de objetos extraídos com a lista de objetos no banco de dados original, utilizando consultas SQL para contar o número de tabelas, views, rotinas e triggers em ambos os ambientes. Além disso, é importante revisar os arquivos .sql para garantir que as transformações foram aplicadas corretamente e que não há erros de sintaxe ou incompatibilidades que possam causar falhas durante a implantação no Cloud SQL.

A função check_ingestion() atua como uma camada de reconciliação de dados, garantindo que o que foi extraído do MySQL realmente chegou ao Google Cloud Storage (GCS) sem perdas. Ela é fundamental para validar a integridade do pipeline, especialmente em migrações de larga escala.

Fluxo lógico de como essa checagem funcionou:

1. Preparação da Reconciliação
   • Conexão Dual: O script abre frentes de comunicação simultâneas com o banco de origem (MySQL) via SQLAlchemy e com o destino (GCS) via biblioteca pyarrow.parquet.Lista de Alvos:
   • Ele lê o arquivo tabelas_para_ingestao.txt para saber exatamente quais tabelas devem ser validadas.
2. O Processo de Contagem
   • Contagem na Origem (get_mysql_count): Executa um comando SELECT COUNT(*) diretamente no MySQL. Isso fornece o "número de ouro" (a verdade absoluta do banco de origem).
   • Contagem no Destino (get_gcs_parquet_count): Em vez de baixar os arquivos pesados, o script utiliza uma técnica otimizada: ele lê apenas os metadados dos arquivos Parquet no GCS.
   • Ele acessa a propriedade fragment.metadata.num_rows de cada parte do dataset.
   • Vantagem: Isso é extremamente rápido e consome quase zero de memória RAM, mesmo para tabelas com milhões de linhas.

3. Lógica de Comparação e Status Após obter os dois números, o script compara os resultados e atribui um status:

   • OK: As contagens são idênticas.
   • DIVERGENTE: Os números não batem, sinalizando que algo se perdeu no transporte ou no buffer.
   • ERRO/NA: Indica que a tabela não foi encontrada em um dos lados (comum se a ingestão falhou antes de começar).

"""
Módulo: step_04_run_reconciliation.py
Projeto: InfoEscola (Data Engineering Pipeline - Fase 4)
Localização Original: src/infoescola/migration/

RESUMO:
Ferramenta de auditoria e reconciliação de dados encarregada de garantir que a 
migração da Fase 2 e 3 ocorreu sem perdas ou corrupção. O script compara a 
origem (MySQL 5.1) e o destino (MySQL 8.1) utilizando somas de verificação 
(checksums) baseadas em lógica binária (BIT_XOR) sobre dados convertidos em HEX.

PRINCIPAIS FUNCIONALIDADES:
1. Reconciliação Multi-Estratégia:
   - Estratégia A (Chunking): Para tabelas com PK numérica, divide os dados em 
     lotes (chunks) e compara os checksums. Permite o "Early Exit" (saída 
     antecipada) assim que a primeira divergência é encontrada.
   - Estratégia B (Full Scan): Fallback para tabelas sem PK numérica, 
     comparando o checksum integral da tabela.
2. Localização de Divergência (Bisseção): Em caso de erro no checksum de um chunk, 
   o script inicia um processo de bisseção (busca binária) para encontrar a 
   exata Chave Primária (PK) onde o dado difere.
3. Validação de Conteúdo Real: Ao usar BIT_XOR(CAST(CONV(MD5(CONCAT_WS(...))))), 
   o script valida o valor real das colunas sanitizadas, ignorando diferenças 
   de formatação de baixo nível, mas detectando qualquer alteração no dado.
4. Integração com State Manager:
   - Registra resultados detalhados no SQLite (counts, checksums, esquemas).
   - Suporta modo '--reconciliacao' para focar apenas em tabelas que falharam 
     em etapas anteriores.
5. Paralelismo de Auditoria: Utiliza 'ThreadPoolExecutor' para validar múltiplas 
   tabelas simultaneamente, otimizando o tempo de conferência.

LOGÍSTICA DE VALIDAÇÃO:
- Passo 1: Comparação de Row Count (Contagem de linhas).
- Passo 2: Comparação de Schema (Estrutura das colunas).
- Passo 3: Comparação de Checksum (Conteúdo binário dos dados).

NOTAS TÉCNICAS:
- Fornece sugestões de comando para o 'sherlock.py' ao encontrar uma PK divergente, 
  permitindo uma inspeção visual rápida da diferença.
- Utiliza 'tqdm' para acompanhamento do progresso em tempo real no console.
- Projetado para ser executado após a Fase 3 (Carga) e antes da ativação 
  final das Foreign Keys.
"""

5. Sherlock - Sim, ele mesmo¶

Sherlock atua como um perito forense de dados, capaz de rastrear a linhagem completa de um único registro desde a origem até o destino final, identificando exatamente onde e por que ocorreu uma divergência.

Somente com ajuda da ferramenta foi possível identificar problemas na migração dos dados.

Como o Sherlock funcionava:

• Complemento à Reconciliação: Enquanto o script de reconciliação principal (00_run_reconciliation.py) indicava "O QUÊ" estava errado ao apontar uma falha de checksum agregado na tabela, o Sherlock era executado cirurgicamente para descobrir "ONDE" (em qual coluna) e "POR QUÊ" (qual caractere) a discrepância ocorria para uma Chave Primária (PK) específica.
• Metodologia de Hash e HEX: Inicialmente, ele buscava os dados da origem e do destino para o Python, serializava-os como JSON e calculava um hash SHA256 para encontrar a diferença.
• Análise Forense Byte a Byte: Para ficar mais inteligente, ele foi aprimorado com a função analyze_hex_diff, transformando-se em um "detetive de HEX". O script passou a extrair os dados convertidos em strings hexadecimais, utilizando as exatas mesmas regras de transformação do pipeline, e comparava as strings campo a campo, gerando uma tabela visual que apontava o índice exato e o byte/caractere divergente (ex: mostrando Byte(O): 0A ('\n') vs Byte(D): 20 (' ')).

Problemas que o Sherlock conseguiu identificar:

A precisão do Sherlock expôs problemas profundos de infraestrutura, dados e código ao longo da migração:

1. Limites na Limpeza de Dados (Casos Extremos): O script provou que a lógica de limpeza que reduzia espaços duplos tinha um limite matemático. Ele identificou registros com anomalias massivas, como um campo de texto com 222 a 274 espaços contíguos, mostrando que a origem ficava com 2 espaços resultantes enquanto o destino ficava com 1, forçando a refatoração da lógica de REPLACE aninhados para suportar a limpeza.
2. Data Drift (Deriva de Dados): O Sherlock identificou campos com tamanhos diferentes entre origem e destino, provando que, mesmo fora de produção, eventos ou triggers no banco de dados legado atualizaram os registros na origem (Tempo T1) logo após a extração inicial (Tempo T0), deixando o destino genuinamente desatualizado.
3. Schema Drift e Tratamento Inconsistente: Ao analisar o código HEX, o Sherlock revelou que certas colunas tinham tipos físicos diferentes, como VARBINARY na origem e VARCHAR no destino. Isso fazia com que o pipeline pulasse a limpeza de texto na origem (assumindo ser dado binário bruto) e limpasse os dados no destino, causando divergência.
4. Diferenças de Encoding (Charset): O script evidenciou a normalização intencional de caracteres de acentuação, apontando como caracteres em Latin1 na origem (ex: a letra 'á' como E1) eram convertidos para UTF-8 no destino (como C3A1), o que era um comportamento esperado de normalização.
5. Anomalias Ocultas de Caracteres (Newlines): Ele apontou que quebras de linha (\n, correspondente ao HEX 0A) presentes na origem estavam sendo transformadas em espaços (HEX 20) ou truncadas no destino.
6. Dessincronização do Pipeline (Cache do Python): Quando o Sherlock relatou dados brutos na origem que já deveriam ter sido limpos, ele ajudou a diagnosticar que o console do Linux estava usando uma versão em cache (bytecode .pyc) de um script de transformação antigo, enquanto a extração real havia rodado com o código atualizado.

Dessa forma, o Sherlock foi indispensável não apenas para detectar erros, mas para entender as naturezas complexas da transformação de dados legados, atuando como o verificador final da fidelidade da migração.

"""
Módulo: sherlock.py
Projeto: InfoEscola (Data Engineering Pipeline - Auditoria Forense)
Localização Original: src/infoescola/migration/

RESUMO:
Ferramenta de investigação granular projetada para validar a integridade de registros
específicos ao longo de toda a cadeia de custódia do dado. O Sherlock compara o
estado do registro em cinco pontos críticos: Origem (MySQL), Parquet (GCS),
Dataframe (Polars), Dataframe Transformado e Destino (Cloud SQL).

PRINCIPAIS FUNCIONALIDADES:
1. Rastreamento de Linhagem (Lineage): Recupera e compara o mesmo registro em
   todas as camadas do pipeline (Bronze, Silver e Gold/Destino).
2. Análise Forense HEX (analyze_hex_diff): O "coração" do script. Compara strings
   hexadecimais coluna por coluna e, em caso de divergência, aponta o índice exato
   do byte, tentando decodificá-lo para UTF-8 para facilitar o debugging.
3. Comparação de Tipos Python: Gera um relatório de tipos (int, str, NoneType, etc.)
   em cada etapa para identificar conversões implícitas indesejadas pelos drivers.
4. Checksums Canônicos: Calcula hashes (MD5) baseados em strings canônicas para
   fornecer um status de integridade rápido ("Igual à origem" vs "Diferente").
5. Relatórios Ricos e Exportáveis: Utiliza a biblioteca 'rich' para gerar tabelas
   visuais no terminal e exporta automaticamente um relatório forense em HTML
   para documentação de incidentes.

FLUXO DE INVESTIGAÇÃO:
- Entrada: Nome da tabela e filtros (ex: --filtros id=123).
- Processo:
    1. Busca HEX na Origem
    2. Busca no Parquet via PyArrow e Polars
    3. Busca HEX no Destino
    4. Comparação Cruzada e Diagnóstico de Bytes.

NOTAS TÉCNICAS:
- Extremamente útil para diagnosticar erros de encoding (Latin1 vs UTF8) e
  truncamento de dados invisíveis a olho nu.
- Utiliza 'transformers.canonical_string' para garantir que a comparação de
  hashes seja justa entre diferentes motores de dados.
- Integra-se ao 'transformers.build_select_hex' para garantir paridade com os
  scripts de extração e carga.
"""

6. Construindo um pipeline robusto e idempotente¶

Após identificar uma série de problemas com o protótipo inicial, foram criados vários scripts em python para rodar diretamente no console, utilizando linha de comando e argumentos para rodar o pipeline.

Isso cria um pipeline robusto que pode ser retomado de falhas, monitorado, agendado e de fácil integração se comparado a outras ferramentas de migração.

O objetivo central é garantir que o "esqueleto" do banco de dados (tabelas, views, triggers, etc.) seja extraído, limpo de incompatibilidades e preparado para ser reconstruído em um novo ambiente.

Aqui está a divisão das principais responsabilidades do código:

1. Extração Seletiva e Paralela

   • O script utiliza o information_schema e comandos SHOW CREATE para buscar a definição original de diversos artefatos:

   • Tabelas e Views.

   • Routines (Stored Procedures e Functions).

   • Triggers e Events.

   • Execução Multi-thread: utiliza um TaskRunner para processar múltiplos artefatos simultaneamente, o que acelera drasticamente a extração em bancos com centenas de tabelas.

2. Saneamento e Transformação de DDL (Data Definition Language)

   • Ele aplica uma série de "filtros" para corrigir problemas comuns de compatibilidade ou otimizar o banco de destino:

   • Segurança de Charset: Converte automaticamente tabelas de latin1 para utf8mb4. Ele inclui uma lógica de segurança que dobra o tamanho de colunas VARCHAR (até 255) para comportar caracteres especiais do UTF-8 sem truncar dados.

   • Correção de Tipos:

   • Converte TINYTEXT para TEXT.

   • Converte VARCHAR muito longos (>255) diretamente para TEXT.

   • Troca o motor de armazenamento MyISAM para InnoDB.

   • Limpeza de Defaults: Remove cláusulas DEFAULT inválidas para tipos TEXT, BLOB e JSON.

   • Tratamento de Datas Zero: Transforma o problemático 0000-00-00 do MySQL em NULL.

   • Segurança (Definer): Remove a cláusula DEFINER, garantindo que os objetos possam ser criados no destino sem depender de usuários específicos que existiam apenas na origem.

3. Gestão de Constraints (Chaves Estrangeiras)

   • O código possui uma lógica especial para chaves estrangeiras (del_constraints):

   • Ele separa as FOREIGN KEYs do corpo principal do CREATE TABLE.

   • Gera scripts SQL idempotentes (que podem ser executados várias vezes sem erro).

   • Utiliza a cláusula NOT VALIDATED na recriação, permitindo que a constraint seja adicionada rapidamente sem validar cada linha existente no momento da criação.

4. Organização no GCS

   • Os arquivos resultantes são salvos no Google Cloud Storage organizados por pastas numeradas, facilitando a ordem de execução posterior:

   • 01_create_tables

   • 02_create_views

   • 03_create_routines... e assim por diante.