Votre voisin de bureau et la Slack Grrr n’ont qu’un mot à la bouche : DBI. Mais alors, qu’est-ce que c’est ? À quoi ça sert ? Et surtout, comment on s’en sert ? Lecteur débutant, cet article est fait pour toi. Et pour les adaptes DBI, ne partez pas tout de suite, il y a surement quelque chose à prendre 😉 !
Sommaire
DBI qu’est ce que ça signifie ? A quoi ça sert ?
di-bi-aïl, vous avez dit di-bi-aïl ?
DBI est l’acronyme pour DataBase Interface, c’est-à-dire l’interface R pour interroger des base de données en bon français. Là vous allez me dire : “euh, ouiiii et alors ?”.
Au cas où, commençons par le commencement, une base de données est un truc où l’information est rangée, sécurisée et utilisable par plusieurs personnes en même temps grâce à un Système de Gestion de Base de Données (SGBD). Ce système permet de lire, créer, modifier et supprimer des informations (opérations dites CRUD pour Create-Read-Update-Delete) en garantissant une certaine ACIDité des transactions. L’acidité, ça n’est pas seulement le citron vert dans le mojito, c’est surtout l’acronyme de Atomicité (tout ou rien) – Cohérence (validité) – Isolation (absence de dépendances) – Durabilité (permanence) des transactions.
Dans la famille SGDB, je voudrais…
Pour simplifier, il existe deux “familles” de bases de données : les bases de données dites relationnelles et…les autres.
Les bases relationnelles sont composées de tableaux à deux dimensions (qui ne sont pas sans rappeler les dataframes) appelées relations. Elles sont reliées par des clés (des identifiants en somme). Grâce à des opérations d’algèbre relationnel telles que des intersections, des sélections et des jointures ces bases sont intérrogées grâce à SQL (Structured Query Language ou en français langage de requête structurée). Voilà pour la première famille : les bases de données relationnelles, improprement appelées “bases de données SQL”.
La deuxième famille ? Ce sont tous les autres types de bases de données 🙂 Les bases de données “NoSQL”, acronyme récursif de “Not Only SQL”. On y trouve les bases de données orientées clé-valeur (Redis…), les bases de données orientées documents (MongoDB…) ou encore les bases de données orientées colonne (ElasticSearch…).
Allo BDD ? Ici R
Maintenant que c’est un peu plus clair, imaginons que vous vouliez travailler sur une grande base de données relationnelle présente sur un serveur (des essais cliniques, des données clients, des données génétiques…). Le souci c’est que vous ne pouvez pas télécharger toutes les données sur votre ordinateur, cela prendrait trop de temps (Il est même possible que la base de données soit trop grosse pour votre ordinateur !). Et pour les analyser, ça serait pareil. Si à chaque fois que vous voulez faire une somme vous devez attendre une journée pour avoir la réponse, votre productivité risque d’en pâtir sérieusement… Cela dit, ça vous donne une bonne excuse pour jouer à {Rcade} (https://github.com/RLesur/Rcade).
Une solution possible, mais non optimale serait de scinder la base de données en plusieurs petites sous-bases, mais cela pose des problèmes de gestion et ignore complètement le fait que les bases de données peuvent-être interrogées avec SQL.
La solution idéale serait de formuler des requêtes dans R, par exemple en tidyverse (à tout hasard) puis que R les transforme en requêtes SQL et que la base de données renvoie un tableau de données avec uniquement ce qui nous intéresse !
Voici ce que fait le package {DBI} (avec le package {dplyr}) 😀 . Il propose une interface unifiée pour la connexion/déconnection, les transactions et l’extraction de données pour tous les types de bases de données relationnelles (PostGRE, MySQL, SQLite, MariaDB…).
Le protocole de communication s’appuie sur l’ODBC (Open Database Connectivity) qui permet d’interagir avec ces bases de données. C’est le package {odbc} qui joue les entremetteurs avec {DBI}.
Voyons maintenant comment mettre en oeuvre ce package, sponsorisé par le RConsortium depuis 2016.
Les requêtes
Se connecter et se déconnecter à une base de données
La fonction dbConnect() permet de se connecter à la base de données en fournissant le serveur, le nom d’utilisateur et le mot de passe si nécessaire.
Pour se déconnecter c’est la fonction dbDisconnect().
library(DBI)
bd <- dbConnect(RSQLite::SQLite(), ":memory:")Ici j’ai créé une base de données de type SQLite en mémoire éphémère, dans la RAM. Vous remarquerez que le premier argument de cette fonction est SQLite, qu’on est venu piocher dans le package {RSQLite}— gardez le en tête, on y reviendra plus bas !
Demander les tables disponibles, ajouter une table, lire une table ou la liste des champs
La fonction dbListTables() permet de connaître la liste des tables disponibles dans la base de données.
Pour ajouter une nouvelle table il faut utiliser la fonction dbWriteTable().
Pour lire une table on utilise la fonction dbReadTable() et pour connaître les champs dbListFields().
dbListTables(bd) ## character(0)# elle est bien évidement vide
dbWriteTable(bd, "mtcars", mtcars)
# j'y positionne le tableau de données mtcars disponible dans le package de {base}
dbListTables(bd)## [1] "mtcars"# maintenant elle n'est plus vide !
dbListFields(bd, "mtcars")## [1] "mpg" "cyl" "disp" "hp" "drat" "wt" "qsec" "vs" "am" "gear"
## [11] "carb"# je regarde la liste des attributs
head(dbReadTable(bd, "mtcars"))## mpg cyl disp hp drat wt qsec vs am gear carb
## 1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
## 2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
## 3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
## 4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
## 5 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
## 6 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1# je regarde le haut du tableauEnvoyer des requêtes en SQL à la base de données
Pour envoyer des requêtes en SQL à la base de données deux solutions existent :
- Utiliser la fonction
dbSendQuery()et écrire directement en SQL si vous connaissez ce langage
voiturelegere <- dbSendQuery(bd, "SELECT * FROM mtcars WHERE wt < 2")
# je sélectionne les voitures avec un poids inférieur à une tonne (2 000 lbs)
dbFetch(voiturelegere)## mpg cyl disp hp drat wt qsec vs am gear carb
## 1 30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2
## 2 33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1
## 3 27.3 4 79.0 66 4.08 1.935 18.90 1 1 4 1
## 4 30.4 4 95.1 113 3.77 1.513 16.90 1 1 5 2
- Sélectionner la table à utiliser avec la fonction
tbl()du package{dplyr}puis écrire la requête en tidyverse. Pour en savoir plus sur le{tidyverse}rendez-vous ici, ici et là 😀 .
library(dplyr)
mtcars_bd <- tbl(bd, "mtcars")
filter(mtcars_bd, wt < 2)## # Source: lazy query [?? x 11]
## # Database: sqlite 3.22.0 [:memory:]
## mpg cyl disp hp drat wt qsec vs am gear carb
## <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 30.4 4 75.7 52 4.93 1.62 18.5 1 1 4 2
## 2 33.9 4 71.1 65 4.22 1.84 19.9 1 1 4 1
## 3 27.3 4 79 66 4.08 1.94 18.9 1 1 4 1
## 4 30.4 4 95.1 113 3.77 1.51 16.9 1 1 5 2L’ingéniosité de {DBI} réside dans son couplage avec les fonctions plus haut niveau de {dplyr} : mtcars_bd n’est rapatriée dans l’environnement global de R qu’à partir du moment où des calculs/manipulations sont opérés dessus, comme ici un filter() selon le critère wt < 2.
En coulisses, c’est une requête SQL qui est passée à mtcars_bd :
## <SQL>
## SELECT *
## FROM `mtcars`
## WHERE (`wt` < 2.0)Et pour finir on se déconnecte de la base de données.
dbDisconnect(bd)Voilà 🙂 !
Aller plus loin avec DBI
Voilà, vous savez à peu près tout sur les bases de {DBI}. Mais voilà, une question vous brûle peut-être les lèvres : pourquoi passe-t-on en premier argument de notre fonction de connexion la fonction d’un autre package ? La réponse à cette question est aussi la grande force de {DBI}, et probablement la raison pour laquelle ce projet suscite autant l’adhésion — le principe même de {DBI} est de fournir une interface globale, que vous pouvez intégrer dans un package interfaçant une base de données. En clair, ces autres packages sont des « drivers », que vous appelez à l’intérieur de {DBI}.
Alors, si demain vous venait la folie l’envie de créer une nouvelle interface à une base de données SQL, vous auriez la possibilité de vous reposer sur les épaules de {DBI}, et les utilisateurs pourront utiliser tout ce que nous venons de vous montrer ci-dessus, avec votre nouveau package. Mais bon, avouons-le, on commence à toucher à de l’utilisation avancée 😉
En espérant que cet article vous ai été utile. N’hésitez pas à laisser vos commentaires ci-dessous, ils seront lus avec attention.
En savoir plus :
- Le repo du package : https://github.com/r-dbi/DBI
- Le site officiel : http://dbi.r-dbi.org/
Laisser un commentaire