library(RSQLite)
library(readr)Warning: package 'readr' was built under R version 4.3.3Desafio 09
#1
# Caminho e nome do arquivo do banco de dados
caminho_banco <- "voos.sqlite3"
# Conecta-se ao banco de dados (cria o arquivo se não existir)
conn <- dbConnect(SQLite(), dbname = caminho_banco)
# Confirme que foi criado
conn<SQLiteConnection>
Path: \\smb\ra237599\WindowsDesktop\Unicamp\Banco de dados - ME315\Desafio 05 e 06 e 07 e 08\voos.sqlite3
Extensions: TRUE#2
# Ler os arquivos CSV
airlines <- read_csv("airlines.csv")Rows: 14 Columns: 2
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (2): IATA_CODE, AIRLINE
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.airports <- read_csv("airports.csv")Rows: 322 Columns: 7
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (5): IATA_CODE, AIRPORT, CITY, STATE, COUNTRY
dbl (2): LATITUDE, LONGITUDE
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.dbWriteTable(conn, "airlines", airlines, overwrite = TRUE) # Cria ou substitui a tabela airlines
dbWriteTable(conn, "airports", airports, overwrite = TRUE) # Cria ou substitui a tabela airportsdbListTables(conn)[1] "airlines" "airports" "avg_delay"
[4] "flights" "flights2" "flights_avg_delay"
[7] "flights_corrigido"#3
# Função lerDados
lerDados <- function(input, pos) {
# Mensagem de progresso
message("Leitura atingiu a linha ", pos)
# Filtrar os aeroportos desejados
dados_filtrados <- subset(
input,
origin %in% c("BWI", "MIA", "SEA", "SFO", "JFK") |
dest %in% c("BWI", "MIA", "SEA", "SFO", "JFK")
)
# Gravar no banco de dados
# append = TRUE para adicionar os chunks na mesma tabela
dbWriteTable(conn,
name = "flights",
value = dados_filtrados,
append = TRUE)
# A função não retorna nada
invisible(NULL)
}
# Simulando um valor para 'pos'
pos = 1000
message("Leitura atingiu a linha ", pos)Leitura atingiu a linha 1000# Comentários:
# - Define a função lerDados que recebe um chunk de dados (input) e a posição atual do chunk (pos)
# - message() exibe no console o progresso da leitura do arquivo
# - subset() filtra os voos que partem ou chegam aos aeroportos BWI, MIA, SEA, SFO e JFK
# - dbWriteTable() grava os dados filtrados no banco SQLite, adicionando ao final da tabela flights
# - invisible(NULL) faz a função não retornar nada
# - As últimas duas linhas simulam o valor de pos e mostram a mensagem de progresso no console#4
if ("flights" %in% dbListTables(conn)) { # Verifica se a tabela 'flights' já existe no banco
dbRemoveTable(conn, "flights") # Se existir, remove para começar do zero
}
# Função que filtra e grava no banco
lerDados <- function(input, pos) {
message("Leitura atingiu a linha ", pos) # Mostra no console a linha atual lida
# Filtra apenas os aeroportos desejados
dados_filtrados <- subset(
input,
ORIGIN_AIRPORT %in% c("BWI", "MIA", "SEA", "SFO", "JFK") | # Mantém voos de origem nos aeroportos listados
DESTINATION_AIRPORT %in% c("BWI", "MIA", "SEA", "SFO", "JFK") # Mantém voos de destino nos aeroportos listados
)
# Renomeia colunas para bater com os nomes desejados no banco
names(dados_filtrados)[names(dados_filtrados) == "ORIGIN_AIRPORT"] <- "origin" # ORIGIN_AIRPORT → origin
names(dados_filtrados)[names(dados_filtrados) == "DESTINATION_AIRPORT"] <- "dest" # DESTINATION_AIRPORT → dest
# Grava no banco, criando a tabela na primeira vez
dbWriteTable(conn, "flights", dados_filtrados, append = TRUE) # append = TRUE adiciona os chunks no final da tabela existente
invisible(NULL) # A função não retorna nada
}
# Callback para ler em chunks
callback <- SideEffectChunkCallback$new(lerDados) # Cria callback que chama lerDados a cada chunk
# Ler CSV em chunks de 100.000 linhas
read_csv_chunked(
file = "flights.csv", # Arquivo CSV a ser lido
callback = callback, # Callback definido acima
chunk_size = 100000, # Número de linhas por chunk
col_types = cols_only( # Lê apenas as colunas necessárias
YEAR = col_integer(),
MONTH = col_integer(),
DAY = col_integer(),
AIRLINE = col_character(),
FLIGHT_NUMBER = col_character(),
ORIGIN_AIRPORT = col_character(),
DESTINATION_AIRPORT = col_character(),
ARRIVAL_DELAY = col_double()
)
)Leitura atingiu a linha 1Leitura atingiu a linha 100001Leitura atingiu a linha 200001Leitura atingiu a linha 300001Leitura atingiu a linha 400001Leitura atingiu a linha 500001Leitura atingiu a linha 600001Leitura atingiu a linha 700001Leitura atingiu a linha 800001Leitura atingiu a linha 900001Leitura atingiu a linha 1000001Leitura atingiu a linha 1100001Leitura atingiu a linha 1200001Leitura atingiu a linha 1300001Leitura atingiu a linha 1400001Leitura atingiu a linha 1500001Leitura atingiu a linha 1600001Leitura atingiu a linha 1700001Leitura atingiu a linha 1800001Leitura atingiu a linha 1900001Leitura atingiu a linha 2000001Leitura atingiu a linha 2100001Leitura atingiu a linha 2200001Leitura atingiu a linha 2300001Leitura atingiu a linha 2400001Leitura atingiu a linha 2500001Leitura atingiu a linha 2600001Leitura atingiu a linha 2700001Leitura atingiu a linha 2800001Leitura atingiu a linha 2900001Leitura atingiu a linha 3000001Leitura atingiu a linha 3100001Leitura atingiu a linha 3200001Leitura atingiu a linha 3300001Leitura atingiu a linha 3400001Leitura atingiu a linha 3500001Leitura atingiu a linha 3600001Leitura atingiu a linha 3700001Leitura atingiu a linha 3800001Leitura atingiu a linha 3900001Leitura atingiu a linha 4000001Leitura atingiu a linha 4100001Leitura atingiu a linha 4200001Leitura atingiu a linha 4300001Leitura atingiu a linha 4400001Leitura atingiu a linha 4500001Leitura atingiu a linha 4600001Leitura atingiu a linha 4700001Leitura atingiu a linha 4800001Leitura atingiu a linha 4900001Leitura atingiu a linha 5000001Leitura atingiu a linha 5100001Leitura atingiu a linha 5200001Leitura atingiu a linha 5300001Leitura atingiu a linha 5400001Leitura atingiu a linha 5500001Leitura atingiu a linha 5600001Leitura atingiu a linha 5700001Leitura atingiu a linha 5800001NULL#5
query <- "
SELECT
f.dest AS airport_code,
a.AIRPORT AS airport_name,
al.AIRLINE AS airline_name,
AVG(CAST(f.ARRIVAL_DELAY AS REAL)) AS avg_arrival_delay
FROM flights f
JOIN airports a
ON f.dest = a.IATA_CODE
JOIN airlines al
ON f.AIRLINE = al.IATA_CODE
GROUP BY f.dest, a.AIRPORT, al.AIRLINE
ORDER BY avg_arrival_delay DESC
"
# A query seleciona:
# - Código do aeroporto de destino (f.dest)
# - Nome completo do aeroporto (a.AIRPORT)
# - Nome da companhia aérea (al.AIRLINE)
# - Média do atraso na chegada (ARRIVAL_DELAY), convertida para numérico
# Faz join com airports e airlines para obter os nomes correspondentes
# Agrupa pelo aeroporto de destino e pela companhia para calcular a média por grupo
# Ordena os resultados do maior para o menor atraso médio
# Executar a consulta
resultado <- dbGetQuery(conn, query)
# Executa a query no banco SQLite e armazena os resultados na variável 'resultado'
# Mostrar resultado
head(resultado, 20) airport_code airport_name
1 DTW Detroit Metropolitan Airport
2 JAX Jacksonville International Airport
3 IAH George Bush Intercontinental Airport
4 MSN Dane County Regional Airport
5 IAH George Bush Intercontinental Airport
6 ATL Hartsfield-Jackson Atlanta International Airport
7 LGA LaGuardia Airport (Marine Air Terminal)
8 ORD Chicago O'Hare International Airport
9 MTJ Montrose Regional Airport
10 MSP Minneapolis-Saint Paul International Airport
11 BNA Nashville International Airport
12 MSO Missoula International Airport
13 ATL Hartsfield-Jackson Atlanta International Airport
14 ORD Chicago O'Hare International Airport
15 JAC Jackson Hole Airport
16 DEN Denver International Airport
17 DCA Ronald Reagan Washington National Airport
18 OTH Southwest Oregon Regional Airport (North Bend Municipal)
19 IAH George Bush Intercontinental Airport
20 IAH George Bush Intercontinental Airport
airline_name avg_arrival_delay
1 Skywest Airlines Inc. 143.00000
2 Delta Air Lines Inc. 41.00000
3 US Airways Inc. 36.20000
4 United Air Lines Inc. 34.50000
5 Skywest Airlines Inc. 27.71429
6 Spirit Air Lines 25.79268
7 Frontier Airlines Inc. 24.02069
8 Spirit Air Lines 22.45725
9 Skywest Airlines Inc. 22.44828
10 Spirit Air Lines 22.17614
11 American Airlines Inc. 22.00000
12 Skywest Airlines Inc. 20.62069
13 Frontier Airlines Inc. 19.11236
14 Frontier Airlines Inc. 19.09427
15 Skywest Airlines Inc. 19.02762
16 Frontier Airlines Inc. 18.49952
17 Delta Air Lines Inc. 18.08219
18 Skywest Airlines Inc. 17.83264
19 Frontier Airlines Inc. 17.27628
20 Spirit Air Lines 17.09623# Exibe as 20 primeiras linhas da tabela resultante no console, mostrando aeroportos, companhias e média de atraso# Captura o horário atual
horario_geracao <- Sys.time()
# Mostrar no console
horario_geracao[1] "2025-09-30 11:06:26 -03"# Ou inserir em um HTML (exemplo com cat)
cat("<p>Relatório gerado em:", horario_geracao, "</p>")<p>Relatório gerado em: 1759241187 </p>dbDisconnect(conn)