Como deixar a consulta dos dados do auxilio emergencial mais eficientes?

Prepara um café ai que esse post vai ser grande, pelo menos para o meu padrão de postagem.

NOTA: Os scripts e programas usados nesse projeto serão enviados aqui

Overview

Tempo atrás, ali por abril eu estava bolando uma forma de fazer consultas sobre os beneficiários do auxilio emergencial. As informações ficam todas no portal da transparência porém é um pouco lento se virar por lá. Já foi pior do que está hoje, alias.

Dali surgiu o projeto react_auxilio_emergencial que foi a minha tentativa de fazer um sistema de consulta. Até ai tudo bem. O problema mesmo é a questão de dados. O portal da transparência aquela época tinha problemas de certificado que minguavam todo o esquema. Local até funcionava mas quando subia num github pages ai dava problema.

O problema ao tentar resolver o problema

Para quem não sabe, uma página em um endereço HTTPS não pode fazer requests em segundo plano para uma URL HTTP. Questão de segurança, e eu dou razão pra quem desenvolve esses browsers. O problema era que a API do portal da transparência usava um certificado que não estava na cadeia de certificação que os browsers aceitam.

Minha ideia então era fazer uma pseudoapi, só que tem também a questão moral. Quanto esse dataset vai ficar depois de processado? Minha ideia era hospedar em um repositório github e acessar os dados usando as raw urls pra puxar os dados.

Naquele dia ai fui baixar o dataset completo, de março, o CSV zipado estava passando de 2.5GB, extraído dava coisa de 8GB. Infelizmente esse dataset não está mais disponível no portal da transparência sabe se lá por que. Acho que muito malaco burguês foi tentar o auxilio e conseguiu por que aquele CSV tinha coisa de 50 milhões de linhas, $[\frac{1}{4}] do brasil enquanto que o de abril tinha coisa de 5 milhões e por algum motivo desconhecido o campo de cidade e UF estavam com valores vazios e o código do IBGE ausente.

Minha ideia para tratar com os dados é fazer um sistema de rotas usando pastas.

Um esquema de URL usando o mês, o ano e o código do IBGE. Usando a própria API do IBGE daria para pegar o código e o mês que o usuário deseja ver e assim ver diretamente o nome apenas de cada beneficiário.

O esquema de URL ficaria nesse estilo: /:periodo/:cod_ibge e cada pessoa tem uma linha.

Ex: /202004/1200013 diria todos os beneficiários do auxilio emergencial de Acrelância - AC de Abril de 2020. Por que Acrelância no exemplo? Por que é a primeira cidade que aparece no dataset, depois de 27 linhas sem cidade.

Então vamos fazer a nossa própria datasource

Minha primeira ideia era preprocessar o CSV e importar no SQLITE. Por que preprocessar? Por que tem mais de 1GB só de aspas duplas e no SQLite isso ia atrapalhar. Tem também algum problema nos acentos dos campos, ai eu queria normalizar para campos mais simples.

MANHA: Se tu abrir um arquivo grande no neovim, tu não precisa carregar ele inteiro se tu der um Ctrl+C. Ele não carrega o tema e os plugins mas já serve pra não deixar a sua máquina entrar em colapso

Pelo jeito eles deram uma mudada no dataset de Abril de 2020 hein

FUN FACT: Usei um sed para tirar as aspas para facilitar a vida do sqlite e o que eu não esperava era a diferença de tamanho que isso deu nos arquivos

• O primeiro arquivo é o processado pelo cat 202004_AuxilioEmergencial.csv | sed s:\"::g > 202004_auxilio.csv
• O segundo é o CSV original
• O terceiro é o CSV zipado

Mudança de planos

Para pré-processamento de dados, depois desse teste com o sed, inicialmente eu fiz um programa em C, esse programa geraria um CSV melhorado, sem aspas e ali eu poderia importar o CSV em um DB mas eu queria algo mais automático.

Fiz um programa em Go para normalizar os dados também e já inserir diretamente no SQLite. Por que Go? Queria conhecer mais profundamente o database/sql e achei essa situação uma boa oportunidade . O problema? Lento pakas. Não tem como aproveitar as magias do SQLite fazendo um insert de elemento depois do outro, eu preciso de batch na minha mesa, AGORA!

Ai veio a ideia de usar um dos programas mais poderosos e menos conhecidos no mainstream, o awk. Sério. Agora que eu to digitando isso é 13 de novembro, qualquer referência com sexta feira 13 é apenas mera coincidência, mas eu fiz um programinha awk que recebe a stream do CSV na stdin, normaliza os campos, splita string e gera o SQL pronto que só precisa mandar para o SQLite. O basicão do sistema se resume a isso, esse é o componente em awk:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

BEGIN {
    FS=";" # separador de items
    BATCH=200 # quantos elementos numa tacada de insert
    ending="" # variável pra me ajudar a saber se vamos fechar o elemento antigo com vírgula ou ponto e vírgula
    print "PRAGMA journal_mode=WAL;"
    print "create table if not exists auxilio ("
        print "mes int not null,"
        print "ibge int not null,"
        print "nome text not null,"
        print "parcela int not null,"
        print "obs text,"
        print "valor int not null"
    print ");"
    print "begin transaction;" # faz o negócio inteiro em uma transaction
    printf "insert into auxilio (mes, ibge, nome, parcela, obs, valor) values "
}
NR>1{ # pula a linha do cabeçalho
    printf ending # printa a ending passada na iteração anterior
    gsub("\"", "") # tira as aspas da linha
    if ((NR % BATCH) == 0) { # a cada BATCH valores termina um insert pra começar outro
        printf "insert into auxilio (mes, ibge, nome, parcela, obs, valor) values "
    }
    printf("(")
    printf $1 # mês
    printf ","
    if ($3 == "") { # ibge, se não for especificado é 0
        printf 0
    } else {
        printf $3
    }
    printf ","
    printf "'" $7 "'" # nome, entre aspas pro sqlite não reclamar
    printf ","
    printf $12 + 0 # parcela
    printf ","
    if (substr($13, 1, 1) != "N") { # obs, se for Não há só pula, menos duplicidade
        printf "'" $13 "'"
    } else {
        printf "''"
    }
    printf ","
    printf $14 + 0 #valor, tira o ,00 do final
    printf ")"
    if ((NR % BATCH) == (BATCH - 1)) { # se for o ultimo elemento diz pra fechar com ;
        ending=";\n"
    } else { # se não for diz pra fechar com , mesmo entre cada insert
        ending=","
    }
}
END {
    printf ";\n" # necessário para não faltar ; no final
    print "commit;" # commita a transaction por ultimo
}

O programa em sí, e o awk, na verdade, não são complexos de entender. A linguagem é bem conveniente, alias:

• tem como definir variáveis
• print printa a string ou número com quebra de linha
• printf printa a string com quebra de linha
• No BEGIN você diz o que ele vai fazer antes de relar no arquivo
• No END você diz o que ele vai fazer depois de relar no arquivo
• Para pegar o primeiro número de uma string tu soma com zero
• Para transformar número como string só printar
• Tem como aplicar substituições inplace na linha
• $numero diz o elemento numero da linha. Isso é muito conveniente para parsear CSV
• Comentários são feitos usando #
• awk "$(cat arquivo.awk)" texto de entrada ou cat texto de entrada | awk "$(cat arquivo.awk)" e ele já começa a processar.
• NR é qual linha ele está atualmente.

O grosso do sisteminha é esse programa em awk, é a parte mais complexa, o resto é shell script de algumas linhas só para juntar as partes.

• Tem um que pega o texto do csv do arquivo zipado direto
• Tem um que chama esse awk processando o que vier da stdin dele
• Tem um que recebe sql na stdin e manda para o sqlite processar e retornar um resultado, ou não
• E tem um outro script que junta esses três. Você passa a pasta onde todos os zips estão baixados e onde vai salvar o DB e vai ter alguma vida social por que esse processo demora, poderia demorar BEEEM mais.

Se eu conseguir paralelizar mais ainda esses scripts a ponto de ter dois ou mais zips sendo processados ao mesmo tempo eu posso ter um ganho real de performance, porêm minha máquina provavelmente não aguentaria a pressão.

Eu poderia até usar uma gambiarra no github actions, o problema é mandar todos esses zips para lá e o SharePoint do portal da transparência não entrega os links diretos assim de graça. Quando eu baixei ele não me deixava baixar mais de dois arquivos ao mesmo tempo, então vamos fritar ovo com o bafo da ventoinha em casa, né.

Eu acho que achei um bug no sqlite

Chegou um ponto que o SQLite mesmo estava usando 100% de CPU, o que é fora do padrão que estava tendo, e o uso de memória ia crescendo aos poucos, o que costuma ser sintoma de memory leak. Eu simplesmente deixei o script rodando sem sessões concorrentes do SQLite, aconteceu de repente depois de cerca de uma hora e meia processando. Dos 5 arquivos 4 já estavam processados e o AWK ainda tinha um uso mínimo de CPU então provavelmente o AWK ainda não tinha terminado o seu trabalho.

Graças ao sistema de transação eu não perdi nada dos dados já commitados então eu precisaria só calcular o dataset de agosto e boa.

O problema meio que continuou acontecendo quando eu processei os dados em um banco a parte então provavelmente é um problema nesse journal mode que eu mudei pro memory pensando em performance.

Mudei também a estratégia de batching, ao invés de fazer inserts de coisa de 200 elementos e uma transação por zip eu aumentei o batch para 64*1024 e criei uma transação por batch na tentativa de ter commits mais rápidos e frequentes. Isso acabou com o efeito de ele criar o journal num arquivo auxiliar e depois o db ir crescendo mais ou menos no tamanho daquele journal.

Usando journaling na memória também acabou com os arquivos auxiliáres, a ideia aqui é escovar bit mesmo, evitar arquivos passarem pelo disco em momentos intermediários. Ele puxa o zip e cospe o DB, só.

O awk com essas alterações ficou assim:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

BEGIN {
    FS=";"
    BATCH=64*1024
    ending=""
    print "PRAGMA journal_mode=MEMORY;"
    print "PRAGMA syncronous=OFF;"
    print "create table if not exists auxilio ("
        print "mes int not null,"
        print "ibge int not null,"
        print "nome text not null,"
        print "parcela int not null,"
        print "obs text,"
        print "valor int not null"
    print ");"
    print "begin transaction;"
    printf "insert into auxilio (mes, ibge, nome, parcela, obs, valor) values "
}
NR>1{
    printf ending
    gsub("\"", "")
    if ((NR % BATCH) == 0) {
        print "begin transaction;"
        printf "insert into auxilio (mes, ibge, nome, parcela, obs, valor) values "
    }
    printf("(")
    printf $1 # mês
    printf ","
    if ($3 == "") { # ibge
        printf 0
    } else {
        printf $3
    }
    printf ","
    printf "\"" $7 "\"" # nome
    printf ","
    printf $12 + 0 # parcela
    printf ","
    if (substr($13, 1, 1) != "N") { # obs
        printf "'" $13 "'"
    } else {
        printf "''"
    }
    printf ","
    printf $14 + 0 #valor
    printf ")"
    if ((NR % BATCH) == (BATCH - 1)) {
        ending=";commit;\n"
    } else {
        ending=","
    }
}
END {
    printf ";\n"
    print "commit;"
}

Eu provavelmente vou fazer um post comparando velocidades de build (dos dados, por que eu não to compilando nada xD) das abordagens. A adição do dataset de agosto com essas alterações levou aproximadamente 12 minutos

Consultas a base de 12GB gerada dos datasets de abril a agosto podem demorar um pouquinho, executando um SQL que passa por todos os elementos em uma máquina com SSD Kingston leva em torno de um minuto.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

$ time sqlite3 database.db 'select distinct mes from auxilio'
202004
202005
202006
202007
202008

real    1m8,294s
user    0m27,408s
sys     0m9,770s

Questão de replicabilidade

Os dados que eu usei aqui foram baixados do SharePoint do portal da transparência em formato ZIP. Dentro de cada ZIP tem um CSV separado por ponto e vírgula.

Para fins de registro mesmo vou deixar os hashes dos arquivos que eu baixei.

1
2
3
4
5

3c25277c4e219a14f4c48be2e8d86c2c0a6fdf72  202004_AuxilioEmergencial.zip
077995bcf0a8686eb2eac3adddc261d0d70e0caa  202005_AuxilioEmergencial.zip
d4ed12b23ae776a4d243409b4c2915566cdfb68e  202006_AuxilioEmergencial.zip
63855c14c4ee5a1b606890c7228e77fdeaa8f26b  202007_AuxilioEmergencial.zip
ee0296150213d86dc658cff678e519e2061c7433  202008_AuxilioEmergencial.zip

Gerando uma pseudoapi

Sinceramente eu não sei como fornecer esse acesso sem ser abusivo para o serviço de hospedagem. Estou aberto a sugestões. São 11GB de dados. A maior duplicidade que pode ter são de nomes das pessoas, quem sabe uma estrutura de pastas /:primeiro nome/:ibge ou /:ibge/:periodo, não sei se o nome das pastas vai contar na cota dos serviços e eu não quero tomar processo por testar essa prova de conceito.

Quem sabe dê para shipar o banco junto com um container docker no heroku como os dados não vão mudar entre deploys, apesar do tempo de boot dos containers e dos problemas de estabilidade do heroku. Também tem a questão de acesso aos dados com eficiência, coisa que eu preciso entender para fazer uma aplicação eficiente.

Uma operação que percorre o banco inteiro leva um minuto e pouco na minha máquina com SSD, como seria a performance em HD? E usando um SGBD completão tipo MySQL? Ou um elastic search (o problema ai é hospedar mesmo 😛 ) ?

O front nessa infraestrutura seria a parte fácil, principalmente se o sistema fornecer blocos relativamente pequenos de dados para o front processar. Gerador de site estático seria covardia para os provedores por que geraria muita duplicidade, a menos que eles usem algum sistema de compressão para os dados deployados.