SIH

Fluxo ETL: SIHSUS

A documentação a seguir apresenta o processo ETL (extract, transform, load) aplicado aos dados do Sistema de Informações Hospitalares do SUS (SIHSUS) 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 SIHSUS 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:
  • Decodificar e enriquecer os dados de acordo com dicionário de dados do SIHSUS;
  • Enriquecer os dados relacionados a município;
  • Enriquecer os dados relacionados a UF;
  • Enriquecer os dados relacionados a Classificação Estatística Internacional de Doenças e Problemas Relacionados com a Saúde (CID10);
  • Enriquecer os dados relacionados a datas e idade;
  • Enriquecer os dados relacionados a coordenadas.

Load:

• Indexar dados tratados do SIHSUS em banco de dados NoSQL Elasticsearch localizado em infraestrutura do 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 SIH 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 SIHSUS 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 SIHSUS 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/SIH/download.dbc.r"

#Caminho FTP Datasus
ftp_path = "ftp://ftp.datasus.gov.br/dissemin/publicos/SIHSUS/200801_/Dados/"

#============[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 SIHSUS:

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 SIHSUS 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 SIHSUS 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: Decodificando e enriquecendo os dados:

Enriquecimentos com base em tabelas auxiliares:

Os enriquecimentos dos dados do SIHSUS descritos a seguir foram baseados em dados disponíveis em planilhas auxiliares:

Tabelas Auxiliares - SIH

Enriquecendo os dados relacionados a região:

O enriquecimento dos dados do SIHSUS relacionados a região foram baseados nos dados disponíveis no seguinte arquivo (planilha "REGUF"):

Enriquecendo os dados relacionadas ao tipo da AIH:

O enriquecimento dos dados do SIH relacionados ao tipo da AIH foram feitos deste modo.

Enriquecendo os dados relacionadas a região metropolitana:

O enriquecimento dos dados do SIH relacionados a região metropolitana foram baseados nos dados disponíveis no seguinte arquivo (planilha "RMETRBR"):

Enriquecendo os dados relacionados a CIR:

O enriquecimento dos dados do SIH relacionados a CIR foram baseados nos dados disponíveis no seguinte arquivo (planilha "CIRBR"):

Enriquecendo os dados relacionados a aglomerados:

O enriquecimento dos dados do SIH relacionados a aglomerados foram baseados nos dados disponíveis no seguinte arquivo (planilha "AGLBR"):

Enriquecendo os dados relacionados a mesorregião:

O enriquecimento dos dados do SIH relacionados a mesorregião foram baseados nos dados disponíveis no seguinte arquivo (planilha "MESOBR"):

Enriquecendo os dados relacionados a microrregião:

O enriquecimento dos dados do SIH relacionados a microrregião foram baseados nos dados disponíveis no seguinte arquivo (planilha "MICROBR"):

Enriquecendo os dados relacionados a região de saúde:

O enriquecimento dos dados do SIH relacionados a região de saúde foram baseados nos dados disponíveis no seguinte arquivo (planilha "RSAUDBR"):

Enriquecendo os dados relacionados a centro de saúde:

O enriquecimento dos dados do SIH relacionados a centro de saúde foram baseados nos dados disponíveis no seguinte arquivo (planilha "CSAUDBR"):

Enriquecendo os dados relacionados a esfera jurídica:

O enriquecimento dos dados do SIH relacionados a esfera jurídica foram baseados nos dados disponíveis no seguinte arquivo (planilha "ESFERAJUR"):

Enriquecendo os dados relacionados a etnia:

O enriquecimento dos dados do SIH relacionados a etnia foram baseados nos dados disponíveis no seguinte arquivo (planilha "ETNIA"):

Enriquecendo os dados relacionados a nacionalidade:

O enriquecimento dos dados do SIH relacionados a nacionalidade foram baseados nos dados disponíveis no seguinte arquivo (planilha "NACION3D"):

Enriquecendo os dados relacionados a CBO:

O enriquecimento dos dados do SIH relacionados a CBO foram baseados nos dados disponíveis no seguinte arquivo (planilha "CBO"):

Enriquecendo os dados relacionados a CNAE:

O enriquecimento dos dados do SIH relacionados a CNAE foram baseados nos dados disponíveis no seguinte arquivo (planilha "CNAE"):

Enriquecendo os dados relacionados a leitos:

O enriquecimento dos dados do SIH relacionados a leitos foram baseados nos dados disponíveis no seguinte arquivo (planilha "LEITOS"):

Enriquecendo os dados relacionados a procedimento realizado:

O enriquecimento dos dados do SIH relacionados a procedimento realizado foram baseados nos dados disponíveis no seguinte arquivo (planilha "TB_SIGTAB"):

Enriquecendo os dados relacionados a procedimento solicitado:

O enriquecimento dos dados do SIH relacionados a procedimento solicitado foram baseados nos dados disponíveis no seguinte arquivo (planilha "TB_SIGTAB"):

2: Enriquecendo os dados relacionados a município (residência e internação):

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

Dados - Município

3: Enriquecendo os dados relacionados a UF (residência e internação):

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

Dados - UF

4: Enriquecendo os dados relacionados a CID 10:

O enriquecimento dos dados do SIHSUS relacionados a CID10 foram baseados nos dados disponíveis nos seguintes arquivos:

CID10 - Capítulos, CID10 - Grupos, CID10 - Categorias, CID10 - Subcategorias

Enriquecendo os dados relacionados ao diagnóstico principal:

O enriquecimento dos dados do SIHSUS relacionados a diagnóstico principal foi realizado conforme abaixo.

Enriquecendo os dados relacionados ao primeiro diagnóstico secundário:

O enriquecimento dos dados do SIHSUS relacionados ao primeiro diagnóstico secundário foi realizado conforme abaixo.

5: Enriquecendo os dados relacionados a datas e idade:

6: Enriquecendo os dados relacionados a coordenadas (residência e internação):

Retorno da fase de tratamento (exemplo):

Fase Load:

1: Indexando dados tratados do SIHSUS 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 - SIHSUS:

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 SIHSUS 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