Premiers pas dans Sqlite database avec R
Au fur et à mesure que le nombre de fichiers Excel et CSV augmente, la gestion de ceux-ci devient un véritable challenge.
La solution, c’est de disposer d’une database local et gratuite, Sqlite. L’intérêt c’est que c’est très léger et rapide à mettre en place, on peut s’en servir aussi bien pour stocker des données dans comme une vraie base de données par exemple les balances, GL, ventes, extraction… Avec l’avantage de créer plusieurs Tables et y accéder facilement.
Un autre avantage qu’il faut le souligner c’est la possibilité de connecter votre base de données Sqlite avec Power BI ce que va réduire le temps d’analyse et ganger en productivité grâce à l’automatisation du Workflow.
Avec Rstudio et le puissant package dplyr, on n’a pas besoin de maitriser le SQL. Le package dplyr simplifie la transformation des données mais aussi il est capable d’interagir directement avec les bases de données en traduisant les verbes dplyr en requêtes SQL. suivre ce lien pour en savoir plus
Si vous ne disposez pas du package RSQlite dans votre environnement R, passer cette commande : install.packages(“RSQLite”)
# Load des packages
library(dplyr)
library(tibble)
library(RSQLite)Load des données de nycflights
library(nycflights13)Créer des tables
first step, c’est la création d’une base de données in memory .
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbListTables(con)## character(0)On va créer deux table dans notre base de données Sqlite et afficher la liste des tables crées
dbWriteTable(con, "Flights", flights)
dbWriteTable(con, "Airports", airports)
dbListTables(con)## [1] "Airports" "Flights"Pour avoir plus d’information sur la connexion
summary(con)## Length Class Mode
## 1 SQLiteConnection S4Explorer les champs (colonnes) dans la table Flights
dbListFields(con, "Flights")## [1] "year" "month" "day" "dep_time"
## [5] "sched_dep_time" "dep_delay" "arr_time" "sched_arr_time"
## [9] "arr_delay" "carrier" "flight" "tailnum"
## [13] "origin" "dest" "air_time" "distance"
## [17] "hour" "minute" "time_hour"On peut référencer la table Flights à l’aide de la fonction tbl()
flights_db <- tbl(con, "Flights")
airports_db <- tbl(con, "Airports")Un apreçu de la table tibble
flights_db## # Source: table<Flights> [?? x 19]
## # Database: sqlite 3.34.1 [:memory:]
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ... with more rows, and 11 more variables: arr_delay <dbl>, carrier <chr>,
## # flight <int>, tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>,
## # distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dbl>Querying les données
Selectionner les premiers lignes
Exécuter… un SQL statement
dbGetQuery(con, "select * from Flights limit 5")## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## arr_delay carrier flight tailnum origin dest air_time distance hour minute
## 1 11 UA 1545 N14228 EWR IAH 227 1400 5 15
## 2 20 UA 1714 N24211 LGA IAH 227 1416 5 29
## 3 33 AA 1141 N619AA JFK MIA 160 1089 5 40
## 4 -18 B6 725 N804JB JFK BQN 183 1576 5 45
## 5 -25 DL 461 N668DN LGA ATL 116 762 6 0
## time_hour
## 1 1357034400
## 2 1357034400
## 3 1357034400
## 4 1357034400
## 5 1357038000Meme résultat en utilisant dbFetch()
query <- dbSendQuery(con, "select * from Flights")
dbFetch(query, n = 5)## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## arr_delay carrier flight tailnum origin dest air_time distance hour minute
## 1 11 UA 1545 N14228 EWR IAH 227 1400 5 15
## 2 20 UA 1714 N24211 LGA IAH 227 1416 5 29
## 3 33 AA 1141 N619AA JFK MIA 160 1089 5 40
## 4 -18 B6 725 N804JB JFK BQN 183 1576 5 45
## 5 -25 DL 461 N668DN LGA ATL 116 762 6 0
## time_hour
## 1 1357034400
## 2 1357034400
## 3 1357034400
## 4 1357034400
## 5 1357038000avec R
flights_db %>% head()## # Source: lazy query [?? x 19]
## # Database: sqlite 3.34.1 [:memory:]
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## # ... with 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dbl>Filtrer les données
avec SQL
dbSendQuery(con, "SELECT * FROM Flights WHERE year = 2013")## <SQLiteResult>
## SQL SELECT * FROM Flights WHERE year = 2013
## ROWS Fetched: 0 [incomplete]
## Changed: 0Avec dplyr
flights_db %>% filter(year == 2013)## # Source: lazy query [?? x 19]
## # Database: sqlite 3.34.1 [:memory:]
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ... with more rows, and 11 more variables: arr_delay <dbl>, carrier <chr>,
## # flight <int>, tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>,
## # distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dbl>Il faut mentionner que c’est une requêtes à distantes, le code R est traduit en SQL et exécuté dans la base de données, pas dans R. Quand vous travaillez avec des bases de données, Dplyr essaie d’être aussi Lazy que possible : Il ne charge jamais les données dans R sauf si vous le demandez explicitement.
tailnum_delay_db <- flights_db %>%
group_by(tailnum) %>%
summarise(
delay = mean(arr_delay),
n = n()
) %>%
arrange(desc(delay)) %>%
filter(n > 100)Cette séquence d’opérations ne touche jamais la base de données. Ce n’est que lorsque vous demandez les données (par exemple, en exécutant tailnum_delay) que dplyr génère le SQL et envoie ensuite à la base de données en une seule étape tous le code.
tailnum_delay_db## # Source: lazy query [?? x 3]
## # Database: sqlite 3.34.1 [:memory:]
## # Ordered by: desc(delay)
## tailnum delay n
## <chr> <dbl> <int>
## 1 <NA> NA 2512
## 2 N0EGMQ 9.98 371
## 3 N10156 12.7 153
## 4 N10575 20.7 289
## 5 N11106 14.9 129
## 6 N11107 15.0 148
## 7 N11109 14.9 148
## 8 N11113 15.8 138
## 9 N11119 30.3 148
## 10 N11121 10.3 154
## # ... with more rowsdplyr traduit votre code R en SQL. Vous pouvez voir le SQL qu’il génère avec show_query() :
tailnum_delay_db %>% show_query()## <SQL>
## SELECT *
## FROM (SELECT `tailnum`, AVG(`arr_delay`) AS `delay`, COUNT(*) AS `n`
## FROM `Flights`
## GROUP BY `tailnum`)
## WHERE (`n` > 100.0)utiliser collect() pour extraire toutes les données dans au format tibble :
tailnum_delay <- tailnum_delay_db %>% collect()Jointure
flights_join_db <- flights_db %>%
select(year:day, dep_delay, arr_delay, dest) %>%
left_join(airports_db, by = c("dest" = "faa"))
flights_join_db## # Source: lazy query [?? x 13]
## # Database: sqlite 3.34.1 [:memory:]
## year month day dep_delay arr_delay dest name lat lon alt tz
## <int> <int> <int> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 2013 1 1 2 11 IAH George B~ 30.0 -95.3 97 -6
## 2 2013 1 1 4 20 IAH George B~ 30.0 -95.3 97 -6
## 3 2013 1 1 2 33 MIA Miami In~ 25.8 -80.3 8 -5
## 4 2013 1 1 -1 -18 BQN <NA> NA NA NA NA
## 5 2013 1 1 -6 -25 ATL Hartsfie~ 33.6 -84.4 1026 -5
## 6 2013 1 1 -4 12 ORD Chicago ~ 42.0 -87.9 668 -6
## 7 2013 1 1 -5 19 FLL Fort Lau~ 26.1 -80.2 9 -5
## 8 2013 1 1 -3 -14 IAD Washingt~ 38.9 -77.5 313 -5
## 9 2013 1 1 -3 -8 MCO Orlando ~ 28.4 -81.3 96 -5
## 10 2013 1 1 -2 8 ORD Chicago ~ 42.0 -87.9 668 -6
## # ... with more rows, and 2 more variables: dst <chr>, tzone <chr>Si on veut générer le code SQL
flights_join_db %>% show_query()## <SQL>
## SELECT `year`, `month`, `day`, `dep_delay`, `arr_delay`, `dest`, `name`, `lat`, `lon`, `alt`, `tz`, `dst`, `tzone`
## FROM (SELECT `year`, `month`, `day`, `dep_delay`, `arr_delay`, `dest`
## FROM `Flights`) AS `LHS`
## LEFT JOIN `Airports` AS `RHS`
## ON (`LHS`.`dest` = `RHS`.`faa`)C’est juste un aperçu de la manipulation des database avec Rstudio, si vous voulez en savoir plus visiter le site officiel de Rstudio:https://db.rstudio.com/getting-started/connect-to-database et sur le lazy evaluation : https://smithjd.github.io/sql-pet/chapter-lazy-evaluation-queries.html