Da extração à visualização: análise de dados abertos utilizando Airflow, Redshift e Metabase

O objetivo deste artigo é construir um projeto que passa pela aquisição, processamento, modelagem, armazenamento e visualização dos dados. Podendo servir como arquitetura base para outros projetos, estudo e até mesmo portfólio.

No processo de análise de dados existem alguns passos que devem ser seguidos para criar um produto que venha agregar valor ao negócio. Eles so resumidos em: aquisição, armazenamento, processamento, modelagem, visualização, atualização e monitoramento.

Neste artigo temos como objetivo construir um projeto de ponta a ponta, trabalhando em todas as fases que foram mencionadas anteriormente.

Vamos analisar a imagem abaixo que mostra a arquitetura do projeto que construiremos ao longo deste artigo.

Assim podemos destacar:

1. Dados Abertos: a nossa fonte de dados será relacionada a dados abertos do Governo Federal, especificamente dados da Prova Brasil 2011 que podem ser encontrados neste link.
2. Apache Airflow: ferramenta open source para orquestração de pipeline de dados, onde é possível construir todo o fluxo de um pipeline de dados. Será responsável pela aquisição, processamento, monitoramento e atualização dos dados.
3. Amazon S3: trata-se de um object storage em nuvem fornecido pela AWS, permite armazenar dados dos mais variados formatos. Caso queira conhecer um pouco mais confira este artigo. Será a ferramenta de armazenamento temporário dos dados neste projeto.
4. Amazon Redshifit: é um banco de dados totalmente gerenciado pela AWS, focado na construção de soluções analíticas como Data Warehouse (DW). Os dados serão armazenados no Redshifit com a modelagem desejada para visualização.
5. Metabase: é uma ferramenta de visualização de dados open source, onde é possível criar gráficos e dashboards de forma simples, bonita e rápida. Vamos construir as visualizações desejadas no Metabase a partir de consultas realizadas no Redshift.

Agora é o momento de codificar e utilizar todas as ferramentas que foram descritas acima.

Todo o projeto será escrito utilizando a linguagem Python e o código completo pode ser encontrado neste repositório do Github.

Infraestrutura necessária

Para a realização do projeto é necessário executar o Airflow e o Metabase em um servidor ou em sua máquina, configurar um bucket no S3 e criar um cluster no Redshift.

No caso do S3 e do Redshift as configurações são feitas diretamente no console da AWS, porém o Metabase e o Airflow precisam ser instalados.

Uma boa alternativa é utilizar o Docker e Docker Compose para provisionar o ambiente e é o que vamos fazer agora.

A seguir contém o arquivo necessário para subir o Airflow e o Postgres. Como o Airflow precisa de um banco de dados, a stack já executa os containers configurados para essa conexão. O Airflow está configurado para receber comunicação na porta 8080.

O arquivo abaixo inicia uma instância do Metabase, onde neste exemplo não iremos utilizar um banco de dados, pois o Metabase consegue utilizar uma base para armazenamento de forma embarcado. Esse ambiente é ideal para desenvolvimento e testes.

Aquisição dos dados

Conhecendo os dados

O primeiro passo é conhecer melhor a base de dados em que se vai trabalhar e entender alguns pontos como: quais as regras de negócios envolvidas, quantidade de dados disponíveis, origem dos dados, onde é possível acessar os dados e entre outros.

Como mencionado anteriormente a nossa fonte de dados será relacionada a Prova Brasil 2011, mas o que é a Prova Brasil?

A Prova Brasil é uma avaliação censitária das escolas públicas das redes municipais, estaduais e federal, com o objetivo de avaliar a qualidade do ensino. Participam desta avaliação as escolas que possuem, no mínimo, 20 alunos matriculados nas séries/anos avaliados, sendo os resultados disponibilizados por escola e por ente federativo (fonte: https://academia.qedu.org.br/prova-brasil/o-que-e-a-prova-brasil/).

Automatizando a aquisição dos dados

Então já sabemos um pouco mais sobre os dados e onde conseguimos acessá-los, agora precisamos realizar o download para que estejam disponíveis para processamento.

Porém não vamos fazer isso manualmente, pois caso seja necessário reproduzir o projeto em outro ambiente não tenha que fazer as mesmas tarefas.

O script shell abaixo irá realizar a tarefa de baixar os dados da fonte no formato zip, descompactar, mover os dados necessários para a pasta dados dentro da pasta dags do Airflow e depois remover os arquivos que não são mais necessários.

Obs: importante executar o script dentro da pasta raiz do Airflow, no mesmo nível da pasta dags.

Depois da execução do script teremos os dados disponíveis dentro da pasta dags/dados e basicamente são dois arquivos que vamos utilizar para montar o nosso DW:

• TS_RESULTADO_ALUNO.csv
• TS_RESULTADO_MUNICIPIO.csv

Modelagem dos dados

Antes de começarmos a trabalhar de forma prática com os dados, é necessário definir como será a modelagem e quais visualizações queremos construir.

Os resultados que vamos extrair neste projeto serão relacionados aos alunos e responderá perguntas como:

• Qual é a quantidade de alunos que fizeram a prova por estado, zona escolar e localização administrativa?
• Qual é a média obtida na disciplina de matemática por estado?
• Quais são os estados que obtiveram maiores notas na prova?

Como o banco de dados será o Redshift para armazenar os dados já processados, a modelagem adotada será a Star Schema e pela simplicidade do projeto teremos três dimensões: turma, escola e localização, e uma tabela fato: resultados dos alunos.

O desenho do modelo relacional pode ser visualizado a seguir:

Processamento dos dados

Bibliotecas e funções iniciais

Agora iremos começar a parte prática do projeto, para isso é necessário definir quais bibliotecas serão utilizadas, como será escrito o código e quais as funções auxiliares para chegarmos aos resultados.

Nesta fase do projeto iremos utilizar três bibliotecas do Python, o Pandas, Numpy e o Boto3.

Como a ferramenta de ETL será o Airflow, o código será encapsulado na DAG e teremos diversas tasks, porém por questão de organização vamos dividir o projeto em três arquivos de código:

• dag_etl_prova_brasil.py: irá conter a declaração da DAG e das tasks, suas configurações e a lógica de execução.
• etl_prova_brasil.py: irá conter o código com as regras de negócio e todas as funções que serão utilizadas pelas tasks. Neste arquivo conterá a classe ETLProvaBrasil.
• util_prova_brasil.py: funções globais que serão utilizadas pelo código principal de processamento dos dados. Neste arquivo conterá a classe UtilProvaBrasil.

Então com o devido entendimento de cada arquivo do projeto, o código completo da classe UtilProvaBrasil ficará da seguinte forma:

A classe acima possui os métodos para transformações básicas dos dados, pois como a maioria dos campos contém códigos utilizados pelo Governo internamente, precisamos das descrições para melhor entendimento, então vamos recuperar as descrições através dos códigos, conforme a documentação dos dados.

No caso da classe ETLProvaBrasil a escrita será dividida em diversas partes para facilitar o entendimento.

Na primeira parte é o momento de declarar as bibliotecas utilizadas e também um dicionário do Python que contém o mapeamento de quais serão as tabelas do DW, exceto a tabela fato (iremos discutir isso mais a frente), com os devidos arquivos que contém os dados para serem carregados nelas.

Um destaque para as bibliotecas S3Hook e PostgresHook que são internas do Airflow.

Os hooks encapsulam as conexões que são feitas em nossas aplicações, assim não precisamos nos preocupar em criar uma conexão de forma manual.

No caso do Redshift a sua conexão será realizada através do PostgresHook que já vem por traz uma instância da biblioteca PSYCOPG criada e configurada. E para o S3 será o S3Hook que utiliza o boto3 como base.

Para utilizar os hooks é necessário configurar as conexões, processo que é realizado na interface gráfica do Airflow na parte de connections, conforme a imagem a seguir.

Agora vamos analisar como ficará o início da classe ETLProvaBrasil, onde é declarada a função init que inicializa os objetos que serão os atributos da nossa classe e a função get_dados_principais que basicamente abre o arquivo TS_RESULTADO_ALUNO.csv que será utilizado por mais de uma função dentro do fluxo de processamento.

Na função init já estamos instanciando os objetos das connections do S3 e do Redshit, o nome do bucket no S3 para armazenar os arquivos e o caminho onde estão os arquivo da Prova Brasil que realizamos o download anteriormente.

Obs: o caminho dos arquivos está apontando para o diretório dags do Ariflow dentro do container, ou seja, o diretório que mapeamos no arquivo do Docker Compose.

Dimensão turma

Neste momento vamos preparar os dados que compõem a primeira dimensão, a DIM_TURMA.

No processo de limpeza, padronização e modelagem dos dados iremos adotar a estratégia de realizar todo o processamento e no final gravar os dados em um arquivo CSV para facilitar as demais etapas. Em toda a parte de manipulação dos dados será utilizado o Pandas e o Numpy.

No caso dos dados da turma, o dataset que será utilizado é o arquivo TS_RESULTADO_ALUNO, que já realizamos download anteriormente.

Neste dataset é necessário extrair todas as séries e turnos que participaram da Prova Brasil, retirar os dados nulos (dropna), extrair as descrições pelos códigos utilizando a classe UtilProvaBrasil com os métodos get_descricao_serie e get_descricao_turno.

Para finalizar serão removidos os dados duplicados (drop_duplicates) para que fiquem apenas séries únicas e depois separar os campos que compõem a tabela, são eles: ID_SERIE, ID_TURNO, DESC_SERIE e DESC_TURNO (lembrando que o ID_SERIE e ID_TURNO são chaves de negócio e nos ajudará a montar os relacionamentos com a tabela fato posteriormente).

Então depois de todo o processo salvamos o arquivo na pasta dags/dados com o nome dim_turma.csv.

O código com os procedimentos descritos acima pode ser conferido logo abaixo:

Dimensão escola

Para os dados relacionados à escola iremos utilizar a mesma estratégia do processamento anterior.

Assim também é necessário carregar os dados do dataset TS_RESULTADO_ALUNO.csv, extrair através do código os campos de descrição da localização (Urbana ou Rural) com o método get_descricao_dependencia_adm e também da localização administrativa da escola (Federal, Estadual ou Municipal) com o método get_descricao_localizacao.

Depois do processamento iremos utilizar os campos ID_ESCOLA, DESC_DEPENCIA_ADM E DESC_LOCALIZACAO que irão compor a tabela DIM_ESCOLA e por enquanto será salvo o arquivo com o nome dim_escola.csv.

O código com o processamento dos dados da escola pode ser conferido logo abaixo:

Dimensão localização

No caso dos dados relacionados a localização das provas, o processamento será um pouco diferente e até mais simples, pois agora temos um dataset específico para isso, o TS_RESULTADO_MUNICIPIO.csv.

Depois de abrir o arquivo iremos separar os campos código de estado (ID_UF) e código do município (ID_MUNICIPIO) que são padronizados pelo IBGE. Também iremos utilizar os campos com a sigla do estado (SIGLA_UF) e o nome do município (NOME_MUNICIPIO).

Depois de selecionar as colunas iremos agrupar por todos os campos, para que assim não tenhamos linhas duplicadas com dados repetidos.

Para finalizar os dados serão salvos já processados em um arquivo com o nome dim_localizacao.csv.

O código para tratamento dos dados da localização das escolas pode ser conferido logo abaixo:

ODS resultado aluno

Agora precisamos começar a processar os dados da tabela fato, pois já concluímos a limpeza e modelagem das dimensões.

No caso da tabela fato o processo é um pouco diferente, pois iremos utilizar uma tabela temporária para carregar os dados, mas ainda sem fazer relacionamentos com as outras tabelas do DW.

Existem diversas estratégias para montar um DW e também para carregar uma tabela fato, aqui iremos adotar a estratégia utilizando uma tabela ODS (Operational Data Store).

Uma tabela ODS é basicamente um “tabelão” que contém alguns dados das dimensões e também as métricas, ela serve tanto para facilitar a extração de relatórios operacionais quanto para carregar a tabela fato.

No caso da tabela ODS_RESULTADO_ALUNO iremos carregar os campos necessários para realizar os relacionamentos com as dimensões (ID_TURNO, ID_SERIE, ID_ESCOLA, ID_UF e ID_MUNICIPIO) e também as métricas, que são as notas dos alunos na matéria de matemática e português.

A nossa ODS será o espelho da tabela fato de resultados dos alunos e para isso vamos fazer alguns processamentos importantes, como: verificar se a prova do aluno é válida, ou seja, se ela está com a situação válida no censo e se foi totalmente preenchida, depois filtrar os campos necessários, que são: ID_PROVA_BRASIL, ID_UF, ID_MUNICIPIO, ID_ESCOLA, ID_TURNO, ID_SERIE, PROFICIENCIA_LP_SAEB e PROFICIENCIA_MT_SAEB.

Por fim vamos separar os campos que contém as notas de matemática (PROFICIENCIA_MT_SAEB) e português (PROFICIENCIA_LP_SAEB), também remover as notas vazias (estratégia adotada pela simplicidade, poderia ser outra como a imputação de dados) e convertê-las para um número flutuante com arredondamento de duas casas decimais (float64).

No final de todo processamento, os dados serão salvos em um arquivo com o nome ods_resultado_aluno.csv.

O código com o processamento descrito anteriormente pode ser conferido logo abaixo:

Armazenamento dos dados

Para carregar os dados no Redshift precisamos seguir alguns passos, que são:

1. Criar as tabelas do DW contendo todos os campos, tipos dos dados e constraints. Como o script SQL para a criação das tabelas é um pouco grande, este pode ser encontrado no repositório do projeto ou exclusivamente neste link, onde basicamente o script cria todas as dimensões, a tabela ODS e a fato.
2. Salvar os arquivos gerados na parte de processamento no bucket do S3. E para isso devemos criar uma função que receba o nome de um arquivo, recupera-o e depois o salva o mesmo no S3.
3. Carregar as tabelas através dos arquivos que estão no S3. Para isso iremos utilizar uma função que receba o nome do arquivo e sua tabela de destino, conforme o mapeamento definido no dicionário do Python TABELAS. E depois devemos utilizar o comando chamado copy do Redshift para realizar a tarefa de carregar as tabelas pelos arquivos salvos no S3.

As funções para salvar os arquivos no S3 e carregar os dados nas tabelas no Redshift estão logo abaixo.

Fato resultado aluno

Para finalizar a classe ETLProvaBrasil e a parte de ETL do nosso projeto, agora basta carregar a tabela fato de resultados dos alunos, a FAT_RESULTADO_ALUNO.

Os dados necessários para carregar a tabela fato se encontram na tabela ODS_RESULTADO_ALUNO e como ela precisa fazer relacionamentos com as demais dimensões é necessário realizar um inner join e pegar as chaves primárias dessas tabelas (SK — Surrograte Key).

Para isso vamos escrever um código SQL, onde o select recupera os dados necessários e no modelo da tabela fato e o insert logo após irá inserir os dados na tabela FAT_RESULTADO_ALUNO, tudo isso executado diretamente no Redshift, conforme a função do código abaixo:

Construção da DAG no Airflow

Agora chegou o momento de finalizar a parte de codificação do projeto e do processo de ETL. Para isso é necessário construir a lógica de execução da DAG com todas as tasks que precisamos executar através das funções que criamos na classe ETLProvaBrasil.

Por questões de didática vamos dividir a explicação em duas partes:

Na primeira parte iremos declarar todas as tasks relacionadas ao processamento dos arquivos. Neste momento as funções que criamos para extrair os dados de localização, turma, escola e resultado dos alunos serão utilizadas.

Também já iremos criar a task para enviar os arquivos para o S3, onde temos um detalhe importante, essa task será gerada de forma dinâmica em tempo de execução para todos os itens do dicionário TABELAS. E também será executada em paralelo para todas as tabelas.

Na segunda parte iremos carregar todas as tabelas de dimensão e a ODS de forma dinâmica, bem parecido com o envio dos arquivo feito anteriormente. É necessário atenção para a tabela fato, já que neste processo não pode ser carregada em paralelo, pois necessita que as outras sejam carregadas primeiro e também porque o seu método de carga é diferente.

Então logo após finalizada a carga das dimensões e da ODS, a tabela função que carrega a tabela fato, será executada.

Depois de carregar todas as tabelas, devemos excluir os arquivo que foram gerados localmente no container, conforme o código das funções logo abaixo:

Obs: No caso deste artigo todos os arquivos Python precisam ser adicionados na pasta dags que foi mapeada no Docker Compose do Airflow.

Abaixo podemos conferir como ficou a DAG em execução no Airflow com todas as tasks definidas.

Visualização dos dados

Após realizar todo o processo de carga dos dados, resta-nos o último passo, que é visualizar dos dados.

Para isso é necessário conectar o Metabase ao Redshift, o que é bem fácil, pois ele traz uma conexão nativa para o serviço da AWS.

Depois de conectado basta criar as visualizações de dados. No meu caso criei as visualizações conforme as perguntas que foram definidas no início deste artigo, onde temos gráficos para:

• Quantidade de alunos por estado, Zona Escolar e Localização Administrativa que participaram da Prova Brasil;
• Média de notas da matéria de matemática por estado;
• E os 10 primeiros estados com maiores médias de notas.

A imagem a seguir mostra como ficou o meu dashboard.

O seu pode ser criado conforme a sua criatividade e perguntas que queira responder.

Conclusão

Neste projeto conseguimos criar um pipeline de dados completo, que contempla basicamente todos os processos de um projeto envolvendo grande quantidades de dados.

Além de criar o projeto de ponta a ponta, conseguimos aplicar tecnologias relevantes que são utilizadas em grandes projetos de importantes empresas.

O mais importante é que esse projeto possa servir como base de arquitetura para projetos reais que venham agregar valor ao negócio, como portfólio ou evolução de seus estudos na área de dados.

Ainda relembrando que o projeto completo pode ser encontrado neste repositório do Github.