Erros de validação recorrentes

fjuniorr commented 2 years ago

Desde o início da execução do fluxo de ETL temos tidos erros de validação no make validate. Dos 206 recursos, o menor número de erros foi de 20 na execução de 28/12/2021. Desde então esse número tem se mantido de torno de 55 em jan/2022.

Por causa de problemas como o relatado em transparencia-mg/new-age7#79, a investigação e resolução dos problemas é custosa, mas precisa ser feita.

Macro ações:

[x] Correção mantis #164334
[x] Ajuste horário das cargas
[x] Conferência erros de integridade referencial
[x] Criação de relatório online

fjuniorr commented 2 years ago

Estamos com 4 recursos com problemas de validação de integridade referencial que precisam ser resolvidos pelo NUCC

fjuniorr commented 2 years ago

Com a última rodada de correção falta apenas 1 recurso com problemas:

Enviei para avaliação da PRODEMGE.

Os snippets abaixo foram úteis para validação interativa

dtamg-py etl-make extract --resource $resource_name 2> logs/extract/$resource_name.txt
mv data/raw/$resource_name.csv data/staging/$resource_name.csv
gzip -n < data/staging/$resource_name.csv > data/$resource_name.csv.gz
dtamg-py etl-make validate -r $resource_name > logs/validate/$resource_name.json

fjuniorr commented 2 years ago

Temos três problemas identificados mas que não todos serão resolvidos em março.

Linhas duplicadas na tabela ft_despesa_2022

A tabela ft_despesa_2022 estava com linhas duplicadas porque não estava sendo truncada a cada carga.

ft_despesa_2021 | 2.598.979,00
ft_despesa_2022 | 9.112.374,00

Solucionado em transparencia-mg/new-age7#27

Integridade referencial ft_convenio_investimento

Esse erro está presente também na base de produção. A PRODEMGE pediu que fosse aberto um Mantis (#164334) para investigação.

Integridade referencial execução

Novamente estamos com erros de integridade referencial:

20220331T083444

Analisando os logs de validação, temos novamente problemas de integridade referencial para as tabelas

ft_diarias_scdp
ft_diarias_scdp_liqpag
ft_compras
ft_despesa_2022

Decorrentes de registros ausentes nas tabelas dimensões:

dm_documento_viagem
dm_contrato
dm_processo
dm_contratado
dm_empenho_desp_2022

Alguns exemplos de linhas com problemas:

"note": "for \"id_documento_viagem\": values \"213493\" not found in the lookup table \"dm_documento_viagem\" as \"id_documento_viagem\"",
"note": "for \"id_contrato\": values \"74549\" not found in the lookup table \"dm_contrato\" as \"id_contrato\"",
"note": "for \"id_processo\": values \"366144\" not found in the lookup table \"dm_processo\" as \"id_processo\"",
"note": "for \"id_contratado\": values \"70718\" not found in the lookup table \"dm_contratado\" as \"id_contratado\"",
"note": "for \"id_empenho\": values \"13341464\" not found in the lookup table \"dm_empenho_desp_2022\" as \"id_empenho\"",

Ontem o NUCC/CGE entrou em contato e disse que devido a um problema de conexão com o servidor FTP, para as tabelas que são extraídas para arquivo csv antes da carga o carregamento está com falha. Isso pode explicar.

fjuniorr commented 2 years ago

Para fins de documentação, os seguintes erros de validação também foram solucionados em março:

transparencia-mg/new-age7#33
transparencia-mg/new-age7#32
transparencia-mg/new-age7#29

Falta ainda a questão dos problemas de integridade referencial. O NUCC estava tendo perda de conexões com o FTP por causa de problemas de espaço. Ontem (06/04) isso aparentemente foi solucionado e podemos fazer um novo teste.

fjuniorr commented 2 years ago

Em 11/04 chegamos a ter apenas um recurso com erro de validação, ft_convenio_investimento, que depende da PRODEMGE para solução (vide Mantis #164334)

Os erros foram aumentando e na execução de 24/04 temos 21 recursos com erros de validação:

gabrielbdornas commented 2 years ago

@fjuniorr, em 05/05/2022 temos 5 recursos com erro de validação segundo o relatório recebido por email:

ft_convenio_investimento, que depende da PRODEMGE para solução (vide Mantis #164334): Entrei em contato com Luiz para ter notícias sobre o mantis aberto, mas ainda sem resposta; e
ft_restos_pagar_2002, ft_restos_pagar_2006, ft_restos_pagar_2010, ft_restos_pagar_2014 e ft_restos_pagar_2018:
- O comando dtamg-py etl-make validate -r $resource_name > logs/validate/$resource_name.json no commit atual do repositório para todos os recurso o resultado para a propriedade "valid" é true, diferente do apresentado no relatório recebido por e-mail;
- Visualizando o último commit de todos os arquivos de validação é possível verificar que os dos recursos em questão datam de 14 dias atrás, como exemplificado abaixo:

gabrielbdornas commented 2 years ago

Em 06/05/2021 4 recursos apresentaram erros de validação:

ft_convenio_investimento, que depende da PRODEMGE para solução (vide Mantis #164334): Entrei em contato com Luiz para ter notícias sobre o mantis aberto e fui informado que a previsão é ele iniciar o trabalho de correção após dia 16/05/2021;
As demais estamos entendendo ser um problema na orquestração do processo, pois a carga realizada pelo processo do Fernando não está rodando corretamente. Exemplo:
- Erro tabela ft_compras diz respeito a falta de id_contratos (74888), que após processo de carga do Fernando ser finalizado apareceu.

Acordei com Márcia os seguintes horários:

20 horas - processo Márcia de extração total banco Portal da Transparência e carga MySql das 56 maiores tabelas (Processo finalizará por volta de 2 da manhã);
3 da manhã - carga Fernando demais tabelas; e
5 da manhã - processo ETL make DTA.

gabrielbdornas commented 2 years ago

Em 09/05/2021 5 recursos apresentaram erro de validação:

Rodei a validação novamente para os recursos, com exceção de ft_convenio_investimento, conforme script abaixo:

dtamg-py etl-make validate -r ft_diarias_scdp > logs/validate/ft_diarias_scdp.json
dtamg-py etl-make validate -r ft_despesa_2022 > logs/validate/ft_despesa_2022.json
dtamg-py etl-make validate -r ft_restos_pagar_2010 > logs/validate/ft_restos_pagar_2010.json
dtamg-py etl-make validate -r ft_compras > logs/validate/ft_compras.json,

Estou utilizando a branch gh-pages para gerar o novo relatório de validação dos recursos, conforme pode ser observado aqui.

Destas apenas a ft_despesa_2022 permaneceu com erro de validação de integridade referencial. Como exemplo pesquisamos no banco de produção do Pdt se existem registros na tabela dm_empenho_desp com id_empenho iguais a 13379174, 13379805, 13377784 e constatamos que sim, eles existem.

Como a suspeita inicial é o tempo de orquestrção de todos os processos, o novo acordo de horário com Márcia e Fernando ficou a iniciar em (10/05/2021):

10 horas - processo Márcia de extração total banco Portal da Transparência e carga MySql das 56 maiores tabelas (Processo finalizará por volta de 2 da manhã); 15 horas - carga Fernando demais tabelas; e 17 horas - processo ETL make DTA.

gabrielbdornas commented 2 years ago

Em 10/05/2022 os testes de anonimização da tabela dm_favorecido não foram realizados para as regras de hanseníase:

https://github.com/transparencia-mg/age7/blob/b7f59a11cf9da7a581760b5788b329b68ba7a108/logs/all.txt#L268-L288

Descobrimos que a tabela dm_empenho_desp estava com as linhas duplicadas, conforme figura abaixo:

WhatsApp Image 2022-05-10 at 3 18 12 PM

Informei Márcia do ocorrido e ela já iniciou a nova carga.

Perguntei Francisco se não seria melhor retirar o commit inicial do processo de entro das { }

https://github.com/transparencia-mg/age7/blob/b7f59a11cf9da7a581760b5788b329b68ba7a108/all.sh#L14-L17

E ele explicou que se passarmos ele para fora das chaves corremos risco de incluir no repositório público dados sem anonimização, caso os testes de anonimização falharem.

gabrielbdornas commented 2 years ago

@fjuniorr, em 12/05/2022 5 recursos apresentaram erros de validação, conforme figura abaixo:

Página html detalhando estes erros pode ser acessada aqui

ft_convenio_investimento, que depende da PRODEMGE para solução (vide Mantis #164334): Entrei em contato com Luiz para ter notícias sobre o mantis aberto e fui informado que a previsão é ele iniciar o trabalho de correção após dia 16/05/2021;
ft_diarias_scdp e ft_diarias_scdp_liqpag apresentaram erro de integridade referencial com a tabela dm_documento_viagem; e
ft_compras apresentou erro de integridade referencial com a tabela dm_contrato.

Investigando as tabelas evento_etl_extracao e evento_etl_carga verifiquei que a extração e carga realizado pelo processo da Márcia finalizou ontem (11/05/2022) às 15:33:56 e a carga realizada por Fernando finalizou somente em 12/05/2022 às 01:19:55. Esta pode ser a causa do problema pois o processo de ETL da DTA finalixou ontem (11/05/2022) às 21:20:06.

Consultas ao banco mostraram (12/05/2021 às 13:30) que de fato os ids apontados como faltantes no relatório para as tabelas dm_documento_viagem e dm_contratos não existiam:

select * from dm_documento_viagem
where id_documento_viagem in (1256843, 1256844, 1256845);

select * from dm_contrato
where id_contrato in (1799775, 1800110, 1800111);

Em conversa com Fernando e Márcia acordamos novos horários para extrações e carga. Isto foi proposto pois:

Não teremos carga rodando em horário comercial (o que atrapalha se precisarmos fazer alguma consulta); e
Não teremos carga do portal rodando simultâneo com extração da Márcia.

Sendo assim, os novos horários planejados foram:

Extração e carga Márcia 18;
Carga Fernando 23; e
Processo ETL DTA 01 da manhã.

gabrielbdornas commented 2 years ago

@fjuniorr, em 13/05/2022 2 recursos apresentaram erros de validação, conforme figura abaixo:

ft_convenio_investimento, que depende da PRODEMGE para solução (vide Mantis #164334): Entrei em contato com Luiz para ter notícias sobre o mantis aberto e fui informado que a previsão é ele iniciar o trabalho de correção após dia 16/05/2021;
ft_restos_pagar_2010, na verdade não apresenta erro. Como não houve nenhuma modificação na mesma seu relatório de validação não foi atualizado. Foi o que eu fiz via comando abaixo. Commitei esta modificação e subi para o repositório do github.

$ dtamg-py etl-make validate -r ft_restos_pagar_2010 > logs/validate/ft_restos_pagar_2010.json

Neste sentido, somente a ft_convenio_investimento apresenta de fato um erro, mas que deveremos aguardar a finalização do chamado aberto para a PRODEMGE, confome relatado acima.

Relatório de erros consolidado pode ser consultado neste arquivo.

@fjuniorr, considerando que o único erro do conjunto no momento depende da correção da PRODEMGE, você concorda em fecharmos o issue? E se for o caso abrirmos um específico para ele?

gabrielbdornas commented 2 years ago

Considerações sobre a atualização do dia 02/05/2022:

Erros tabela ft_convenio_investimento continua aguardando resolução mantis #164334;
Nenhum log de validação do novo processo implementado no PR transparencia-mg/age7#278:
- Como função python utilizada os.system executa seus comandos em uma subshell, é necessário armazenar o log em arquivos específicos, commitados no repositório ao final do processo. Incluí esta melhoria neste commit.
Tabela dm_tipo_divida veio com linhas zeradas novamente:
- Criei um o PR transparencia-mg/age7#279 para implementar novo processo que verifica recursos com linhas zeradas e realizar sua extração e validação novamente.
- Para testar o mesmo, tendo em vista que em minha máquina pessoal não tenho liberação para acessar o banco de dados, busquei as tabelas com linhas zeradas no commit c4b595f. Deixei os arquivos desejados abertos e voltei para o commit de teste. Salvei os arquivos (.csv.gz) na pasta data/raw e depois descompactei-os utilizando o comando gunzip <resource_name>.csv.gz.

gabrielbdornas commented 2 years ago

@fjuniorr, em 07/06/2021 tivemos um erro de validação na tabela fl_compras_empenho

O erro foi:

"note": "constraint \"pattern\" is \"\\d{4} \\d{2}.\\d{3}.\\d{3}.\\d{4}.\\d{1} \\d{1}.\\d{1}.\\d{2}.\\d{2}.\\d{2} \\d{1}.\\d{2}.\\d{1}\"",
          "message": "The cell \"\"1\" in row at position \"916893\" and field \"dotacao_orcamentaria\" at position \"7\" does not conform to a constraint: constraint \"pattern\" is \"\\d{4} \\d{2}.\\d{3}.\\d{3}.\\d{4}.\\d{1} \\d{1}.\\d{1}.\\d{2}.\\d{2}.\\d{2} \\d{1}.\\d{2}.\\d{1}\"",
          "description": "A field value does not conform to a constraint."

Investigando a tabela ví que, de fato, o último registro da mesma está com a dotação orçamentária errada

Pesquisa Avançada no Portal da Transparência mostra Dotações Orçamentárias: 1260 12.368.151.2074.1 3.3.90.30.08 0.23.1

Consultas sql realizadas confirmam igualdade do número do empenho (971) no campo nr_empenho e consulta avançada citada acima:

select * from fl_compras_empenho
where id_processo = 366667

select * from dm_processo
where id_processo = 366667

select * from dm_empenho_desp
where id_empenho = 13349981

Segunda Márcia, houve erro de conexão do ftp na gravação do csv da tabela fl_compras_empenho, o que explica o ocorrido.

gabrielbdornas commented 2 years ago

Mantis #164334 corrigido.

transparencia-mg / age7