CNES

Fluxo ETL: CNES

A documentação a seguir apresenta o processo ETL (extract, transform, load) aplicado aos dados do Cadastro Nacional de Estabelecimentos de Saúde (CNES) do DATASUS para indexação na Plataforma de Ciência de Dados aplicada à Saúde (PCDaS). O processo ETL aplicado consiste basicamente em:

Extract:

• Acessar o servidor FTP do DATASUS e baixar os arquivos com extensão .dbc contendo os microdados do CNES para cada estado, para cada ano e para cada mês disponível/desejado;
• Converter os arquivos baixados para a extensão .dbf;
• Ler e unir os arquivos em um único conjunto de dados para posterior tratamento.

Transform:

• Tratar o conjunto de dados adquirido de maneira a:
  • Eliminar valores inválidos;
  • Decodificar e enriquecer os dados de acordo com dicionário de dados do CNES;
  • Enriquecer os dados relacionados a município;
  • Enriquecer os dados relacionados a UF;
  • Enriquecer os dados relacionados a datas;
  • Enriquecer os dados relacionados a coordenadas.

Load:

• Indexar dados tratados do CNES em banco de dados NoSQL Elasticsearch localizado em infraestrutura da PCDaS no Laboratório Nacional de Computação Científica (LNCC);
• Coletar log de indexação.

A implementação de todo o fluxo ETL descrito foi feita no ambiente da ferramenta Dataiku Data Science Studio (DSS). Este processo ETL é reexecutado para cada ano e para cada mês selecionado disponível no servidor FTP do DATASUS. Uma visão geral do fluxo ETL é apresentada no diagrama a seguir:

                 Fase Extract                                        Fase Transform                                      Fase Load

Detalhes de código/dados da implementação do fluxo ETL são apresentados a seguir.

Fase Extract:

1: Acessando o servidor FTP do DATASUS e baixando os arquivos com extensão .dbc contendo os dados do CNES para cada estado, para cada ano e para cada mês disponível/desejado;

Código R:

Função para fazer download de arquivos .dbc do CNES a partir do FTP do Datasus:

download.dbc <- function(ftp_path,
                         dest_files_path = getwd(),
                         estados = NULL, anos = NULL, meses = NULL){
  require(RCurl)
  require(tidyverse)
  require(stringr)
  
  anos <- str_pad(as.numeric(anos)-2000, 2, pad = "0")
  meses <- str_pad(meses, 2, pad = "0")
  
  url <- ftp_path
  #userpwd <- "yourUser:yourPass"
  #recupera o nome dos arquivos em ftp_path
  filenames <- getURL(url, userpwd = NULL,
                      ftp.use.epsv = FALSE,dirlistonly = TRUE)
  filenames <- unlist(strsplit(filenames,"\n"))
  filenames <- data.frame(filename=filenames)
  filenames <- data.frame(lapply(filenames, as.character), stringsAsFactors=FALSE)
  
  #filtra os estados desejados se estes forem especificados
  if(!is.null(estados)){
    filenames <- filenames %>%
      filter(str_detect(filename, paste(estados,sep="",collapse = '|')))
  }
  #filtra os anos e meses desejados se estes forem especificados
  aamm <- as.vector(outer(anos, meses, paste, sep=""))
  #filtra os anos e meses desejados se estes forem especificados
  if(!is.null(aamm)){
    filenames <- filenames %>%
      filter(str_detect(filename, paste(aamm,sep="",collapse = '|')))
  }
  
  #faz download dos arquivos selecionados e salva os arquivos em dest_files_path
  for (filename in filenames$filename) {
    download.file(paste(url, filename, sep = ""), paste(dest_files_path, "/", filename, sep = ""))
  }
  
  #retorna um data.frame com os nomes e caminho dos arquivos
  return(data.frame(file_name=filenames$filename,file_path=dest_files_path))
}

Código para fazer download de arquivos .dbc do CNES a partir do FTP do Datasus:

#============[INTERAÇÂO DSS]============
library(dataiku)
library(dplyr)
#===========================================

#Caminho para função "download.dbc"
path.script = "https://github.com/bigdata-icict/ETL-Dataiku-DSS/raw/master/CNES/download.dbc.r"

#Caminho FTP Datasus
ftp_path = "ftp://ftp.datasus.gov.br/dissemin/publicos/CNES/200508_/Dados/ST/"

#============[EDITAR]============
#Local para salvamento dos arquivos .dbc
path.data = "path_dbc_files"
#================================

#============[OPCIONAL]============
#filtros de estado e/ou anos
estados = NULL #outro exemplo: c("RJ")
anos = 2018 #outro exemplo: 2000:2014
meses = 12 #outro exemplo: 1:12
#==================================

#Carrega função "download.dbc"
if(!exists("download.dbc", mode="function")) source(path.script)

# Computa retorno da receita: nome dos arquivos .dbc baixados
datasus_files_list <- download.dbc(ftp_path = ftp_path,
                                   dest_files_path = path.data,
                                   estados = estados,
                                   anos = anos,
                                   meses=meses)

#=============[INTERAÇÂO DSS]=============== -> Alternativa: salvar data.frame em arquivo .csv 
# Retornando resultados para dataset do DSS
dkuWriteDataset(datasus_files_list,"datasus",schema = TRUE)
#===========================================

Retorno (exemplo):

2: Convertendo os arquivos baixados para a extensão .dbf e então lendo e unindo os arquivos em um único conjunto de dados no DSS para posterior tratamento.

Código R:

Função para converter, ler e unir os arquivos de dados do CNES:

load.dbc <- function(files){
  require(read.dbc)
  
  df_from_DBC = data.frame()
  
  for(f in files){
    bind_rows( df_from_DBC, read.dbc(f) ) -> df_from_DBC
  }
  
  return(df_from_DBC)
}

Código para converter, ler e unir arquivos de dados do CNES e escrever conjunto de dados resultantes no DSS:

#============[INTERAÇÂO DSS]============
library(dataiku)
library(dplyr)
#===========================================

#Instala o pacote "read.dbc"
#install.packages("read.dbc") 

#Caminho para função "load.dbc"
path.script = "https://github.com/bigdata-icict/ETL-Dataiku-DSS/raw/master/load.dbc.r"

#Carrega função "load.dbc"
if(!exists("load.dbc", mode="function")) source(path.script)

#============[INTERAÇÂO DSS]============  -> Alternativa: leitura de data.frame a partir de arquivo .csv
#Acessa lista de arquivos baixados para posterior conversão e leitura 
datasus_files_list <- dkuReadDataset("datasus")
#===========================================

#Concatena caminhos e nomes dos arquivos
datasus_files_list <- paste(datasus_files_list$file_path,datasus_files_list$file_name,sep="")

# Computa retorno da receita: dados do CNES advindos de todos os arquivos .dbc baixados
df_from_DBC <- load.dbc(datasus_files_list)

#============[INTERAÇÂO DSS]============ -> Alternativa: salvar data.frame em arquivo .csv
# Retornando resultados para dataset do DSS
dkuWriteDataset(df_from_DBC, "DATA",schema = TRUE)
#===========================================

Retorno (exemplo):

Fase Transform:

A transformação/preparação dos dados foi feita utilizando-se funcionalidades (processors) disponíveis no ambiente da ferramenta Dataiku Data Science Studio (DSS). A receita ("recipe") utilizada está disponível para download/visualização através dos arquivos json:

prepare_DATA.json e prepare_DATA.shaker

Alguns detalhes do tratamento dos dados são apresentados a seguir, onde colunas com nomes em maiúsculo representam dados originais advindos do DATASUS e colunas com nomes em minúsculo representam dados resultantes de tratamento:

1: Eliminando valores inválidos:

2: Decodificando e enriquecendo os dados:

Enriquecendo os dados relacionados ao tipo de unidade de saúde:

O enriquecimento dos dados do CNES relacionados ao tipo de unidade de saúde foram baseados nos dados disponíveis no seguinte arquivo (planilha "TIPOS DE ESTABELECIMENTO"):

Tabelas de Domínio - CNES

3: Enriquecendo os dados relacionados a município:

O enriquecimento dos dados do CNES relacionados a município foram baseados nos dados disponíveis no seguinte arquivo:

Dados - Município

4: Enriquecendo os dados relacionados a UF:

O enriquecimento dos dados do CNES relacionados a UF foram baseados nos dados disponíveis no seguinte arquivo:

Dados - UF

5: Enriquecendo os dados relacionados a data:

6: Enriquecendo os dados relacionados a coordenadas:

Retorno da fase de tratamento (exemplo):

Fase Load:

1: Indexando dados tratados do CNES em banco de dados NoSQL Elasticsearch:

Uma vez finalizada a trasformação/preparação dos dados, estes são indexados em um servidor de banco de dados NoSQL Elasticsearch localizado em infraestrutura da PCDaS no LNCC.

Código Python:

O código utilizado para indexação está disponível em formato Jupyter Notebook: Notebook Python

Retorno - log de indexação (exemplo):

Dados e dicionário de variáveis - CNES:

O download e a prévia do conjunto de dados resultante de todo o tratamento realizado e indexado no Elasticsearch estão disponíveis na PCDaS, assim como o dicionário de variáveis.

Dashboards Kibana:

Os dados CNES indexados no Elasticsearch possibilitam o desenvovimento de dashboards Kibana para a visualização simples, rápida e dinâmica de grandes quantidades de dados. Os dashboards desenvolvidos estão disponíveis para visualização na PCDaS na seção de Análise Visual.