Desafio 09

SQLite - Processamento de Dados em Lote

SQLite SQLite é uma aplicação de banco de dados de pouquíssimo impacto e com um potencial de aplicação bastante amplo. Habitualmente, em ambientes de produção que exijam alta performance ou operações mais complexas, SQLite não é a ferramenta padrão de escolha. Para aplicações de menor porte, o SQLite é uma ferramenta de grande importância, pois adequa-se aos padrões de SQL e é multiplataforma.

Objetivo Ao fim deste laboratório, você deverá ser capaz de:

Usar a estratégia de leitura por chunks implementada no pacote readr para leitura dos dados de vôos; Utilizar a função SideEffectChunkCallback$new() para depositar os dados do arquivo diretamente no banco de dados (sem devolver resultados para o usuário); Recomendações Não utilize o servidor jupyter.ime.unicamp.br para executar esta atividade; Instale os pacotes readr e RSQLite, se necessário. A sugestão é empregar install.packages(c(“readr”, “RSQLite”), dep=TRUE, type=‘win.binary’). Atente para mensagens de erro adicionais (como outros pacotes faltantes) e instale-os conforme requerido.

Atividades

Não esquecer de colocar um comando no código que “printe” o dia e hora que foi compilado o arquivo
Crie um arquivo de banco de dados em SQLite chamado voos.sqlite3. (Dica: o comando dbConnect() se conecta num banco de dados se o arquivo apontado existir ou cria um novo, caso o arquivo não exista.)
Leia os arquivos airlines.csv e airports.csv. Deposite o conteúdo de cada um destes arquivos nas tabelas, respectivamente, airlines e airports. Utilize o comando dbWriteTable() para isso.
Crie uma função chamada lerDados contendo 2 argumentos, input e pos. A função deve apresentar ao usuário uma mensagem de progresso da leitura do arquivo flights.csv (utilize o comando message()), aos moldes do apresentado abaixo. A função deve salvar apenas os vôos que partiram ou chegaram aos seguintes aeroportos BWI, MIA, SEA, SFO e JFK, numa tabela chamada flights. Observe que a função não deve retornar nada para o usuário, deve apenas gravar a tabela obtida do chunk no banco de dados. (Dica: utilize o comando dbWriteTable() e estude como o argumento append deve ser utilizado para permitir que os chunks intermediários sejam adicionados ao fim da tabela.)

Simulando um valor para ‘pos’

pos = 1000 message(“Leitura atingiu a linha”, pos)

Leia o arquivo flights.csv, restringindo-se às colunas YEAR, MONTH, DAY, AIRLINE, FLIGHT_NUMBER, ORIGIN_AIRPORT, DESTINATION_AIRPORT e ARRIVAL_DELAY, e aplique a função lerDados() criada acima. Observe, novamente, que a função lerDados() não retorna nada para o usuário. Por isso, a função de callback a ser utilizada é SideEffectChunkCallback$new(). Leia 100 mil registros por vez.
Acesse o banco de dados e, por meio de uma chamada em SQL, apresente o tempo médio de atraso de chegada por aeroporto de destino, a sigla do aeroporto, o nome completo do aeroporto e o nome completo da companhia aérea. Ordene o resultado (na mesma chamada de SQL) por ordem decrescente deste atraso médio (i.e., o primeiro registro deve ser o aeroporto que tem o maior tempo de atraso na chegada). Atente para o fato de que o mesmo nome de coluna pode acontecer em diferentes tabelas.

# Atividade 0 - Data e hora de compilação
print(paste("Arquivo compilado em:", Sys.time()))

[1] "Arquivo compilado em: 2025-09-30 12:00:35"

# Carregar os pacotes necessários
library(readr)

Warning: package 'readr' was built under R version 4.2.3

library(RSQLite)
library(dplyr)

Warning: package 'dplyr' was built under R version 4.2.3


Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

# Atividade 1 - Criar/conectar ao banco de dados SQLite
con <- dbConnect(SQLite(), "voos.sqlite3")

# Atividade 2 - Ler e salvar airlines e airports (se ainda não existirem)
if (!"airlines" %in% dbListTables(con)) {
  airlines <- read_csv("airlines.csv")
  dbWriteTable(con, "airlines", airlines, overwrite = TRUE)
  print("Tabela 'airlines' criada com sucesso")
}

if (!"airports" %in% dbListTables(con)) {
  airports <- read_csv("airports.csv")
  dbWriteTable(con, "airports", airports, overwrite = TRUE)
  print("Tabela 'airports' criada com sucesso")
}

# Atividade 3 - Função lerDados
lerDados <- function(input, pos) {
  message("Leitura atingiu a linha ", pos)
  
  # Filtrar apenas os aeroportos de interesse
  aeroportos_interesse <- c("BWI", "MIA", "SEA", "SFO", "JFK")
  dados_filtrados <- input %>%
    filter(ORIGIN_AIRPORT %in% aeroportos_interesse | 
           DESTINATION_AIRPORT %in% aeroportos_interesse)
  
  # Salvar no banco de dados
  if (nrow(dados_filtrados) > 0) {
    dbWriteTable(con, "flights", dados_filtrados, 
                 append = ifelse(pos == 1, FALSE, TRUE))
  }
}

# Atividade 4 - Ler flights.csv com chunks (apenas se a tabela não existir)
if (!"flights" %in% dbListTables(con)) {
  print("Iniciando leitura do arquivo flights.csv em chunks...")
  
  read_csv_chunked(
    "flights.csv",
    callback = SideEffectChunkCallback$new(lerDados),
    chunk_size = 100000,
    col_types = cols(
      YEAR = col_integer(),
      MONTH = col_integer(),
      DAY = col_integer(),
      AIRLINE = col_character(),
      FLIGHT_NUMBER = col_integer(),
      ORIGIN_AIRPORT = col_character(),
      DESTINATION_AIRPORT = col_character(),
      ARRIVAL_DELAY = col_double(),
      .default = col_skip()  # Ignorar todas as outras colunas
    )
  )
  print("Leitura do flights.csv concluída!")
} else {
  print("Tabela 'flights' já existe no banco")
}

[1] "Tabela 'flights' já existe no banco"

# Atividade 5 - Consulta SQL para atrasos médios (COM COLUNAS CORRETAS)
consulta <- "
SELECT 
    AVG(f.ARRIVAL_DELAY) as atraso_medio,
    f.DESTINATION_AIRPORT as aeroporto,
    ap.AIRPORT as nome_aeroporto,
    al.AIRLINE as companhia_aerea
FROM 
    flights f
JOIN 
    airports ap ON f.DESTINATION_AIRPORT = ap.IATA_CODE
JOIN 
    airlines al ON f.AIRLINE = al.IATA_CODE
WHERE 
    f.ARRIVAL_DELAY IS NOT NULL
GROUP BY 
    f.DESTINATION_AIRPORT, ap.AIRPORT, al.AIRLINE
ORDER BY 
    atraso_medio DESC
LIMIT 10
"

resultado <- dbGetQuery(con, consulta)
print(resultado)

   atraso_medio aeroporto                                   nome_aeroporto
1     143.00000       DTW                     Detroit Metropolitan Airport
2      41.00000       JAX               Jacksonville International Airport
3      36.20000       IAH             George Bush Intercontinental Airport
4      34.50000       MSN                     Dane County Regional Airport
5      27.71429       IAH             George Bush Intercontinental Airport
6      25.79268       ATL Hartsfield-Jackson Atlanta International Airport
7      24.02069       LGA          LaGuardia Airport (Marine Air Terminal)
8      22.45725       ORD             Chicago O'Hare International Airport
9      22.44828       MTJ                        Montrose Regional Airport
10     22.17614       MSP     Minneapolis-Saint Paul International Airport
          companhia_aerea
1   Skywest Airlines Inc.
2    Delta Air Lines Inc.
3         US Airways Inc.
4   United Air Lines Inc.
5   Skywest Airlines Inc.
6        Spirit Air Lines
7  Frontier Airlines Inc.
8        Spirit Air Lines
9   Skywest Airlines Inc.
10       Spirit Air Lines

# Fechar conexão com o banco
dbDisconnect(con)