Introduction aux bases de données relationnelles et au SQL
Avril 2023
Charles Martin
- Qu’est-ce qu’une base de données ?
- Structure et normalisation des données
- Survol des logiciels disponibles
- Préparation de notre environnement de travail DuckDB
- Introduction au language SQL
Qu’est-ce qu’une base de données ?
Au plus simple, une base de données est une collection de tableaux de données, que l’on nomme dans ce jargon des tables. Chacun de ces tableaux de données comprend plusieurs colonnes (ou variables) que l’on nomme champ dans le vocabulaire spécifique aux bases de données.
Contrairement aux tableaux de données qui existent dans la mémoire de l’ordinateur, les tables sont stockées sur le disque dur. Elles peuvent donc être de beaucoup plus grande taille.
Aussi, contrairement aux tableaux des données, les tables des bases de données sont presque toujours indexées. C’est-à-dire qu’un travail est fait en amont, à mesure que les données sont ajoutées/modifiées/supprimées, pour accélérer les filtres et les tris au moment venu. Remarquez que cette caractéristique n’est pas unique aux bases de données. Par exemple, la librairie data.table peut elle aussi indexer ses données.
Structure et normalisation des données
Contrairement aux tableaux de données qui sont un produit figé, généré à la fin de la collection de données, les bases de données sont prévues pour être dynamiques. Elles comprennent toute une infrastructure pour permettre d’insérer de nouvelles données, de les mettre à jour, de les supprimer, etc., de façon sécuritaire.
Pour que ces modifications se déroulent bien, la façon de structurer nos données revêt une importance particulière, encore plus critique que dans un tableau de données. Tout un champ d’étude avec sa terminologie propre y est d’ailleurs consacré : la normalisation de base de données.
Nous ne pouvons évidemment que survoler ces techniques, mais il est important d’en comprendre quand même le grand principe, qui est de ne jamais dédoubler d’information.
Par exemple, ce tableau de données est parfait pour R :
| parcelle | site | richesse_parcelle | ph_site |
|---|---|---|---|
| A | X | 3 | 7.5 |
| B | X | 2 | 7.5 |
| C | X | 5 | 7.5 |
| D | Y | 1 | 6.9 |
| E | Y | 0 | 6.9 |
| F | Y | 3 | 6.9 |
Mais il ne serait pas approprié dans une base de données relationnelle. Il devrait être remplacé par les tables suivantes :
sites
| id | nom | ph |
|---|---|---|
| 1 | X | 7.5 |
| 2 | Y | 6.9 |
parcelles
| id | site_id | nom | richesse |
|---|---|---|---|
| 1 | 1 | A | 3 |
| 2 | 1 | B | 2 |
| 3 | 1 | C | 5 |
| 4 | 2 | D | 1 |
| 5 | 2 | E | 0 |
| 6 | 2 | F | 3 |
Remarquez dans chaque table la présence d’une colonne id, qui devient l’identifiant unique de chacune des observations. On appelle cet identifiant la clé primaire de la table.
Le lien entre les deux tables est quant à lui spécifié par la colonne site_id, que l’on nomme dans ce jargon la clé étrangère.
La relation ici est donc une relation 1:N, une relation un à plusieurs. Un même site peut avoir plusieurs parcelles, mais chaque parcelle n’appartient qu’à un seul site.
Il existe plusieurs autres types de relations, par exemple les relations N:N, plusieurs à plusieurs. Ce type de relation serait tout à fait approprié pour noter la liste des espèces présentes dans chaque parcelle :
especes
| id | nom |
|---|---|
| 1 | perchaude |
| 2 | meunier |
presences
| parcelle_id | espece_id |
|---|---|
| 1 | 1 |
| 1 | 3 |
Remarquez que pour définir une relation N:N, il faut se créer une table intermédiaire, qui ne contient que des identifiants associant les deux tables. C’est le travail ici de la table presences, qui relie la table especes et la table parcelles dans une relation N:N.
Survol des logiciels disponibles
Il existe des dizaines, voir des centaines de logiciels de base de données relationnelles différents. Ils peuvent en général être divisés en trois catégories d’usage :
-
Les immenses bases de données dans le cloud, comme Google BigQuery ou Amazon Redshift. Ces solutions sont extrêmement performantes parcequ’elles peuvent puiser dans une immense piscine de ressources au moment d’effectuer des requêtes. Leur utilisation est aussi très coûteuse.
-
Les serveurs de bases de données, comme MySQL, PostgreSQL, SQL Server, etc. Ces solutions permettent l’interrogation simultanée de leurs données par plusieurs utilisateurs à la fois. Ils sont une excellente solution client-serveur lorsque plusieurs utilisateurs doivent jouer simultanément avec les même données.
-
Les bases de données embarquées, comme SQLite et DuckDB. Ces solutions roulent directement sur votre ordinateur, augmentant de beaucoup sa capacité à cruncher des données, tout en vous évitant la configuration d’une infrastructure de serveurs.
Pour cet atelier, nous nous contenterons d’utiliser une base de données embarquée, soit DuckDB. L’avantage de cette base de données pour des situations d’apprentissage est qu’elle s’efface et se recrée de nouveau à chaque démarrage de R. Elle est aussi extrêmement performante, pouvant gérer des requêtes immenses, beaucoup plus rapidement que son concurrent SQLite.
Cependant, presque tout de ce que nous verrons pourrait être transféré à un autre logiciel sans problème.
Préparation de notre environnement de travail DuckDB
library(tidyverse)
── Attaching packages ─────────────────────────────────────── tidyverse 1.3.2 ──
✔ ggplot2 3.4.0 ✔ purrr 1.0.1
✔ tibble 3.1.8 ✔ dplyr 1.0.10
✔ tidyr 1.3.0 ✔ stringr 1.5.0
✔ readr 2.1.2 ✔ forcats 0.5.2
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag() masks stats::lag()
library(DBI)
library(duckdb)
Toutes nos opérations sur des bases de données passeront par la librairie DBI (DataBase Interface). C’est elle qui s’occupera de transférer nos demandes à la base de données. Par contre, comme chaque base de données possède ses petites particularités, nous devrons au moment de la connexion spécifier vers quelle base de données nous allons. Dans notre cas, ce sera vers DuckDB, donc :
connexion <- DBI::dbConnect(duckdb::duckdb())
Si notre base de données avait été un serveur PostgreSQL, on aurait pu spécifier notre connexion comme ceci :
connexion <- DBI::dbConnect(
RPostgres::Postgres(),
hostname = "adresse.com",
port = 1234
)
Enfin, dans un vrai projet d’analyse, il aurait probablement été approprié de spécifier un dossier dans lequel DuckDB pourra stocker la base de données plutôt que de devoir la recréer à chaque démarrage :
connexion <- DBI::dbConnect(duckdb::duckdb(), dbdir = "donnees_projet")
Nous allons maintenant nous créer quelques tables et les remplir pour pouvoir commencer à travailler. Vous pouvez d’ailleurs télécharger le jeu de données associé à cet atelier ici.
Remarquez que les données sont déjà bien organisées pour l’importation : chaque observation possède un identifiant unique associé et la structure de la base de données a déjà été normalisée.
Voici tout d’abord comment créer une table DuckDB à partir d’un fichier CSV :
duckdb_read_csv(connexion, "especes", "FakeData/especes.csv")
Comme nous avons plusieurs tables à créer, nous pouvons automatiser ce processus à l’aide de la fonction map :
liste_tables <- c("especes","sites","parcelles","presences")
map(liste_tables, function(x){
duckdb_read_csv(connexion, x, str_c("FakeData/",x,".csv"))
})
[[1]]
[1] 166
[[2]]
[1] 26
[[3]]
[1] 1300000
[[4]]
[1] 1e+07
On peut ensuite vérifier que toutes nos tables ont correctement été créées :
dbListTables(connexion)
[1] "especes" "parcelles" "presences" "sites"
La base de données avec laquelle nous allons travailler contient 26 sites, dans lesquels un total de 1 300 000 parcelles ont été inventoriées. Dans chacune des parcelles, la liste des toutes les espèces (83 au total) ont été notées, pour un total de 10 000 000 de présences. Ces données sont inventées de toute pièce, mais nous permettront de tester la rapidité et la puissance de l’approche par base de données. Ne craignez rien pour la vitesse d’exécution. Sur mon portable personnel, l’ensemble de l’atelier s’exécute en moins de 30 secondes…
Voici, pour information, la liste de tous les champs dans chacune des tables que nous avons créées :
dbListFields(connexion,"especes")
[1] "id" "nom"
dbListFields(connexion,"sites")
[1] "id" "nom" "ph"
dbListFields(connexion,"parcelles")
[1] "id" "site_id" "nom" "couvert_pct"
dbListFields(connexion,"presences")
[1] "parcelle_id" "espece_id"
Remarquez que chacune des parcelles est associée à un site par la colonne site_id, qui en fait en relation 1:N (un site contient plusieurs parcelles, mais chaque parcelle n’appartient qu’à un site).
La table de présences quant à elle ne contient que des identifiants, soit les combinaisons espèces-parcelles qui ont été observées, décrivant une relation N:N.
Introduction au language SQL
Le SQL (Structured Query Language) est un langage informatique (relativement) standardisé permettant d’interroger des bases de données. Je dis “relativement” parce que les grandes lignes du langage sont les mêmes d’une base de données à l’autre, mais chacune ajoute aussi des particularités qui leur sont propres.
Le SQL définit trois grandes familles de commandes soit celles pour :
- Extraire les données (faire des requêtes)
- Définir les données (insertion, modification, suppression, etc.)
- Contrôler les opérations (donner des accès, valider des transactions, etc.)
Dans la grande majorité des cas, il est possible de se connecter directement à la base de données à l’aide d’un terminal pour envoyer nos requêtes. Par contre, exception faite des opérations de maintenance, cette façon de faire est peu employée. Les requêtes sont habituellement envoyées par programmation dans le langage de notre choix, ici R.
Requêtes
Pour envoyer des requêtes permettant d’extraire des données, nous utiliserons la fonction dbGetQuery de la librairie DBI. Cette fonction attendra deux choses, soit une connexion vers une base de données, et la commande SQL à exécuter.
Sélectioner quoi, de quelle table?
La base des requêtes SQL est la commande SELECT, qui permet de choisir quels champs on veut extraire de notre table, suivie de FROM, qui spécifie sur quelle table effectuer l’extraction. Par exemple, pour obtenir les noms des sites, on pourrait lancer :
dbGetQuery(connexion, "SELECT nom FROM sites")
nom
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
16 P
17 Q
18 R
19 S
20 T
21 U
22 V
23 W
24 X
25 Y
26 Z
La fonction dbGetQuery retourne toujours un objet data.frame, que l’on peut attraper et utiliser normalement ensuite dans R :
x <- dbGetQuery(connexion, "SELECT nom FROM sites")
x %>%
slice(1:10) %>%
arrange(desc(nom))
nom
1 J
2 I
3 H
4 G
5 F
6 E
7 D
8 C
9 B
10 A
Notez que l’on a écrit ici les commandes SELECT et FROM en majuscules. Pour la majorité des logiciels de bases de données, le respect des majuscules/minuscules est facultatif. Les commandes SeLeCt, SELECT et select auraient exactement le même résultat. Par contre, par convention, il est d’usage d’inscrire les mots clés (SELECT, FROM, GROUP BY, etc.) en majuscules, et le nom des champs en minuscules.
Plutôt que de spécifier chacun des champs un à un, on peut aussi utiliser un *, qui agit comme un joker indiquant que l’on veut tous les champs de cette table :
dbGetQuery(connexion, "SELECT * FROM sites")
id nom ph
1 1 A 7.169245
2 2 B 6.954659
3 3 C 6.798699
4 4 D 6.746626
5 5 E 7.241875
6 6 F 6.970178
7 7 G 6.828074
8 8 H 6.692542
9 9 I 7.213349
10 10 J 7.310457
11 11 K 6.945442
12 12 L 7.693323
13 13 M 7.358573
14 14 N 7.196335
15 15 O 6.865845
16 16 P 7.118721
17 17 Q 6.759258
18 18 R 7.125312
19 19 S 7.078200
20 20 T 6.708648
21 21 U 6.577113
22 22 V 7.569592
23 23 W 6.879513
24 24 X 6.655040
25 25 Y 6.991663
26 26 Z 6.876635
Appliquer des filtres
Évidemment, on ne voudra pas toujours tout récupérer dans une table, on voudra parfois récupérer uniquement certaines informations. Par exemple, uniquement les sites avec un pH de 7 ou plus :
dbGetQuery(connexion, "SELECT * FROM sites WHERE ph >= 7")
id nom ph
1 1 A 7.169245
2 5 E 7.241875
3 9 I 7.213349
4 10 J 7.310457
5 12 L 7.693323
6 13 M 7.358573
7 14 N 7.196335
8 16 P 7.118721
9 18 R 7.125312
10 19 S 7.078200
11 22 V 7.569592
On peut aussi combiner plusieurs conditions, avec des ET et des OU :
dbGetQuery(connexion, "SELECT * FROM sites WHERE ph >= 7 AND ph < 7.4")
id nom ph
1 1 A 7.169245
2 5 E 7.241875
3 9 I 7.213349
4 10 J 7.310457
5 13 M 7.358573
6 14 N 7.196335
7 16 P 7.118721
8 18 R 7.125312
9 19 S 7.078200
Contrôler l’ordre
On peut changer l’ordre dans lequel apparaîtront les résultats en ajoutant une clause ORDER BY à notre requête, comme ceci :
dbGetQuery(connexion, "SELECT * FROM sites WHERE ph >= 7 ORDER BY ph")
id nom ph
1 19 S 7.078200
2 16 P 7.118721
3 18 R 7.125312
4 1 A 7.169245
5 14 N 7.196335
6 9 I 7.213349
7 5 E 7.241875
8 10 J 7.310457
9 13 M 7.358573
10 22 V 7.569592
11 12 L 7.693323
On peut choisir l’ordre décroissant plutôt que croissant en ajoutant le modificateur DESC :
dbGetQuery(connexion, "SELECT * FROM sites WHERE ph >= 7 ORDER BY ph DESC")
id nom ph
1 12 L 7.693323
2 22 V 7.569592
3 13 M 7.358573
4 10 J 7.310457
5 5 E 7.241875
6 9 I 7.213349
7 14 N 7.196335
8 1 A 7.169245
9 18 R 7.125312
10 16 P 7.118721
11 19 S 7.078200
Limiter la quantité d’information reçue
Si vous construisez des tableaux de bords, des rapports, etc., il pourrait vous arriver que vous n’ayez pas besoin de l’ensemble des lignes retournées par une requête, mais uniquement des premières. On peut utiliser la clause LIMIT pour couper nos résultats, par exemple en gardant uniquement les 5 premières lignes :
dbGetQuery(connexion, "SELECT * FROM sites ORDER BY ph LIMIT 5")
id nom ph
1 21 U 6.577113
2 24 X 6.655040
3 8 H 6.692542
4 20 T 6.708648
5 4 D 6.746626
La clause LIMIT deviendra particulièrement importante dans les requêtes à venir, qui pourraient retourner des millions de lignes…
Connecter plusieurs tables ensemble
Puisque nos données sont bien organisées en différentes tables avec une structure normalisée, on peut facilement exploiter les relations entre les différentes tables.
Comme premier scénario, imaginons que l’on voudrait obtenir toutes les parcelles situées dans des sites au pH de 7 ou plus.
Première étape, préparer une requête pour trouver ces sites :
dbGetQuery(connexion, "SELECT * FROM sites WHERE ph >= 7")
id nom ph
1 1 A 7.169245
2 5 E 7.241875
3 9 I 7.213349
4 10 J 7.310457
5 12 L 7.693323
6 13 M 7.358573
7 14 N 7.196335
8 16 P 7.118721
9 18 R 7.125312
10 19 S 7.078200
11 22 V 7.569592
Pour se faciliter la vie, nous allons introduire ici le concept d’alias, qui nous permet de renommer une table dans notre requête pour y faire référence plus facilement. Notez que les alias n’existent qu’au moment de cette requête en particulier. Lors de la prochaine, nous devrons le refaire cet alias si on veut l’utiliser à nouveau.
Nous en profiterons aussi pour clarifier que le pH et le * qui nous intéressent sont ceux de la table de sites :
dbGetQuery(connexion, "SELECT s.* FROM sites AS s WHERE s.ph >= 7")
id nom ph
1 1 A 7.169245
2 5 E 7.241875
3 9 I 7.213349
4 10 J 7.310457
5 12 L 7.693323
6 13 M 7.358573
7 14 N 7.196335
8 16 P 7.118721
9 18 R 7.125312
10 19 S 7.078200
11 22 V 7.569592
Maintenant, on peut aller connecter cette requête avec la table de parcelles :
dbGetQuery(connexion, "
SELECT s.*, p.*
FROM sites AS s
LEFT JOIN parcelles AS p
ON p.site_id = s.id
WHERE s.ph >= 7
LIMIT 10
")
id nom ph id site_id nom couvert_pct
1 5 E 7.241875 122881 5 megvgtlavc 60.040527
2 9 I 7.213349 122885 9 vnamlyqykq 10.955212
3 10 J 7.310457 122886 10 voynebxemt 87.516474
4 12 L 7.693323 122888 12 dcwdppalwt 91.731660
5 13 M 7.358573 122889 13 wdphqdvvnt 23.368555
6 14 N 7.196335 122890 14 vcvtjgthjp 3.024725
7 16 P 7.118721 122892 16 pjskhveidz 71.662687
8 18 R 7.125312 122894 18 lexdywvieo 91.496724
9 19 S 7.078200 122895 19 jlvqsnbyvy 61.266997
10 22 V 7.569592 122898 22 kfamkmimao 74.941283
Remarquez que l’on a besoin de deux clauses pour connecter les deux tables ensemble.
La première est le LEFT JOIN. Il existe différents types de “jointures” en SQL, la “gauche” étant la plus commune. Dans un LEFT JOIN, toutes les données de la table de gauche (ici les sites) seront présentes, et seules les données de la table de droite ayant des correspondances seront ajoutées.
Comme le SQL ne lit pas dans les pensées, il faut aussi ajouter une clause ON, qui spécifie sur quels champs connecter les deux tables. Ici, on spécifie que la colonne site_id de la table parcelles doit correspondre à la colonne id de la table de sites.
J’ai utilisé ici la clause LIMIT 10 pour éviter d’avoir 100 pages de résultat, mais dans une utilisation réelle, ce n’aurait pas été nécessaire.
Une chose particulièrement contre-intuitive ici est que l’on peut utiliser l’information provenant de la parcelle dans la clause SELECT, même si à ce point, nous n’avons pas mentionné que l’on voulait s’y lier. Le code SQL n’est pas interprété de façon séquentielle. La requête sera déconstruite et interprétée de façon optimale, pour l’ensemble de la requête.
Remarquez aussi que l’on mentionne que l’on veut toute l’info provenant des deux tables (s., p.), mais ce n’est pas obligatoire. On aurait aussi pu être sélectifs :
dbGetQuery(connexion, "
SELECT s.nom AS nom_site, s.ph, p.nom AS nom_parcelle, p.couvert_pct
FROM sites AS s
LEFT JOIN parcelles AS p
ON p.site_id = s.id
WHERE s.ph >= 7
LIMIT 10
")
nom_site ph nom_parcelle couvert_pct
1 A 7.169245 yjsvyluwyw 13.35865
2 E 7.241875 dvbqleheht 42.34773
3 I 7.213349 dspjaogwaf 53.68863
4 J 7.310457 wenvtalrhm 33.90705
5 L 7.693323 yiufghvqze 54.29557
6 M 7.358573 segjczoozw 46.62623
7 N 7.196335 bkbedboyii 97.87763
8 P 7.118721 esqsvztcos 13.13683
9 R 7.125312 vzozqzgsnw 19.40764
10 S 7.078200 xnvlnblopy 42.59576
Remarquez ici que l’on peut utiliser des alias dans la clause SELECT de notre requête
Effectuer des opérations par groupe
Le SQL contient aussi toute une série de mots clés permettant de faire des opérations par groupe.
Sachant que COUNT nous fournit le nombre de lignes correspondant dans une requête, p.ex.
dbGetQuery(connexion,"SELECT COUNT(id) FROM sites")
count(id)
1 26
On peut, par exemple, se demander combien de fois chacune des espèces a été observée :
dbGetQuery(connexion,"
SELECT COUNT(p.espece_id)
FROM presences AS p
GROUP BY p.espece_id
LIMIT 10
")
count(p.espece_id)
1 75840
2 22797
3 169707
4 160021
5 198948
6 177248
7 118475
8 87996
9 175962
10 27476
Évidemment, cette façon de présenter l’information n’est pas très utile, mais peut facilement le devenir en connectant la table d’espèces :
dbGetQuery(connexion,"
SELECT COUNT(p.espece_id) AS nb_observations, ANY_VALUE(e.nom) AS nom
FROM presences AS p
LEFT JOIN especes AS e ON e.id = p.espece_id
GROUP BY e.id
ORDER BY nb_observations DESC
LIMIT 10
")
nb_observations nom
1 439378 African giant pouched rat
2 439314 Thirteen-lined ground squirrel
3 436888 Little brown bat
4 434872 Musk shrew
5 413442 Giraffe
6 412510 African striped mouse
7 409380 Round-tailed muskrat
8 408834 African elephant
9 402402 Arctic ground squirrel
10 397896 Cow
Remarquez que l’on a dû ajouter la clause ANY_VALUE autour de e.nom. Lorsque l’on applique une opération par groupe, toutes les valeurs de la clause SELECT doivent subir une opération de type agrégation (i.e. COUNT, MIN, MAX,AVG, FIRST, LAST, ANY_VALUE, etc.) ou faire partie de la cause GROUP BY.
Vues et sous-requêtes
Enfin, comme dernier exemple d’agrégation, nous allons calculer la richesse en espèce moyenne par parcelle pour chaque site. Ce sera notre requête la plus complexe de l’atelier!
Tout d’abord, construisons la requête qui calcule le nombre d’espèce par parcelle :
dbGetQuery(connexion,"
SELECT
ANY_VALUE(pa.nom) AS nom,
ANY_VALUE(pa.id) AS id,
ANY_VALUE(pa.site_id) AS site_id,
COUNT(DISTINCT(pr.espece_id)) AS richesse
FROM parcelles AS pa
LEFT JOIN presences as pr ON pr.parcelle_id = pa.id
GROUP BY pa.id
LIMIT 10
")
nom id site_id richesse
1 nldjabkezk 1264584 22 8
2 wanjivedhu 1272225 19 14
3 ggswzbuius 72442 6 21
4 xbjkkbamow 114094 6 6
5 gxdsdbdicm 1287497 3 10
6 dokcidjdqi 814664 6 4
7 zskfrdbata 145840 6 16
8 fccafsyimm 1274512 18 11
9 owgxsxcczv 467450 22 10
10 ymmbyupkzm 186856 20 14
Ensuite, il est extrêmement pratique de savoir qu’il existe dans les bases de données des objets que l’on nomme des vues. Ces dernières sont une façon de sauvegarder une requête et de lui associer un nom, pour la réutiliser plus tard.
On peut créer une vue à partir de la requête ci-haut grâce à la commande CREATE VIEW :
dbExecute(connexion,"
CREATE VIEW v_richesses AS
SELECT
ANY_VALUE(pa.nom) AS nom,
ANY_VALUE(pa.id) AS id,
ANY_VALUE(pa.site_id) AS site_id,
COUNT(DISTINCT(pr.espece_id)) AS richesse
FROM parcelles AS pa
LEFT JOIN presences as pr ON pr.parcelle_id = pa.id
GROUP BY pa.id")
[1] 0
Cette vue peut ensuite être utilisée exactement comme si il s’agissait d’une table :
dbGetQuery(connexion, "SELECT * FROM v_richesses LIMIT 5")
nom id site_id richesse
1 nolxmnqmdx 1058277 25 11
2 cwbbnvcniq 1285844 14 4
3 fzlzionnwq 846829 9 12
4 xiewxpzcrz 635508 16 11
5 jorditqaqa 1042151 19 12
Pour calculer la richesse moyenne par site, il ne nous reste maintenant qu’à connecter les deux tables ensemble à l’aide d’une jointure :
dbGetQuery(connexion, "
SELECT sites.id, ANY_VALUE(sites.nom) AS nom, ANY_VALUE(sites.ph) AS ph, MEAN(richesse) AS richesse_moyenne
FROM sites
LEFT JOIN v_richesses
ON v_richesses.site_id = sites.id
GROUP BY sites.id
LIMIT 5
")
id nom ph richesse_moyenne
1 1 A 7.169245 7.12628
2 2 B 6.954659 7.13368
3 3 C 6.798699 7.09738
4 4 D 6.746626 7.13422
5 5 E 7.241875 7.11826
Remarquez qu’une stratégie alternative aurait été d’utiliser une sous-requête pour arriver au même résultat, mais de façon beaucoup moins lisible :
dbGetQuery(connexion,"
SELECT ANY_VALUE(s.nom) AS nom, ANY_VALUE(s.ph) AS ph, AVG(p.richesse) AS richesse_moyenne
FROM sites AS s
LEFT JOIN (
SELECT
ANY_VALUE(pa.nom) AS nom,
ANY_VALUE(pa.id) AS id,
ANY_VALUE(pa.site_id) AS site_id,
COUNT(DISTINCT(pr.espece_id)) AS richesse
FROM parcelles AS pa
LEFT JOIN presences as pr ON pr.parcelle_id = pa.id
GROUP BY pa.id
) AS p ON p.site_id = s.id
GROUP BY s.id
ORDER BY richesse_moyenne DESC
")
nom ph richesse_moyenne
1 L 7.693323 7.15808
2 X 6.655040 7.15736
3 K 6.945442 7.14678
4 Q 6.759258 7.13844
5 R 7.125312 7.13674
6 D 6.746626 7.13422
7 B 6.954659 7.13368
8 M 7.358573 7.13298
9 V 7.569592 7.13278
10 I 7.213349 7.12652
11 A 7.169245 7.12628
12 F 6.970178 7.12502
13 Z 6.876635 7.12202
14 E 7.241875 7.11826
15 U 6.577113 7.11738
16 O 6.865845 7.11498
17 N 7.196335 7.10808
18 G 6.828074 7.10546
19 W 6.879513 7.09800
20 C 6.798699 7.09738
21 S 7.078200 7.09358
22 H 6.692542 7.08934
23 T 6.708648 7.08824
24 Y 6.991663 7.07886
25 J 7.310457 7.07566
26 P 7.118721 7.06196
Insertion, modification et suppression de données
Dans le cadre de votre travail de biologiste, vous utiliserez probablement une base de données relativement statique, créée comme nous l’avons montré plus haut, directement à partir de fichiers CSV.
Néanmoins, il est intéressant de connaître la capacités complètes d’un système de base de données relationnelle. En particulier, comment ajouter, modifier et supprimer des données.
Insertion de données
Comme nous en avons discuté précédemment, il est très important dans une base de données relationnelle que chaque ligne dans une table puisse être identifiée de façon unique.
Certaines bases de données comme MySQL permettent, au moment de créer une table, de mentionner qu’un identifiant unique devra être généré chaque fois qu’une ligne est ajoutée.
Avec DuckDB, il faudra travailler un petit peu plus pour arriver au même résultat. Avant d’effectuer la première insertion, nous devrons créer un objet de type séquence, qui nous permettra d’obtenir la prochaine valeur d’id à utiliser lors de notre insertion.
Pour cette section, nous enverrons nos commandes, non pas à l’aide de la fonction dbGetQuery, mais à l’aide de la fonction dbExecute, puisque nos commandes SQL ne sont pas des requêtes conçues pour retourner des données.
Donc, voyons d’abord comment créer une séquence pour la table de sites, en spécifiant que la première valeur de la séquence devra être l’id maximum de site + 1.
Remarquez que contrairement aux id qui s’auto-incrémentent, cette stratégie est plutôt risquée si plusieurs personnes travaillent simultanément sur la même base de données. Après avoir récupéré l’id maximal, une nouvelle insertion pourrait avoir lieu et changer ce maximum. De plus, il serait possible pour quelqu’un d’insérer une ligne sans utiliser la séquence, etc.
Donc, premièrement, quel est le plus grand id de notre table de sites?
dbGetQuery(connexion,"SELECT MAX(id) FROM sites")
max(id)
1 26
Deuxièmement, on crée la séquence, en réutilisant ce chiffre
dbExecute(connexion, "CREATE SEQUENCE sequence_sites START 27")
[1] 0
On peut donc ensuite utiliser cette séquence pour les insertions subséquentes :
dbExecute(connexion, "INSERT INTO sites VALUES (nextval('sequence_sites'), 'site ajouté', 7.0)")
[1] 1
dbExecute(connexion, "INSERT INTO sites VALUES (nextval('sequence_sites'), 'deuxième ajout', 6.5)")
[1] 1
On peut maintenant aller voir dans la table que nos sites ont effectivement été ajoutés, avec des identifiants uniques :
dbGetQuery(connexion,"SELECT * FROM sites ORDER BY id DESC LIMIT 10")
id nom ph
1 28 deuxième ajout 6.500000
2 27 site ajouté 7.000000
3 26 Z 6.876635
4 25 Y 6.991663
5 24 X 6.655040
6 23 W 6.879513
7 22 V 7.569592
8 21 U 6.577113
9 20 T 6.708648
10 19 S 7.078200
Sachant cela, il pourrait être tentant d’insérer des données en utilisant des objets existants dans R, par exemple comme ceci :
nom <- "Site de Charles"
pH <- 7
dbExecute(connexion, paste0(
"INSERT INTO sites VALUES (nextval('sequence_sites'), '",
nom,
"', ",
pH,
")"
))
[1] 1
Remarquez d’abord que ce genre d’opération nécessite beaucoup de concentration, pour ne pas mélanger les guillemets doubles qui définissent nos chaînes de caractères dans R, et les guillemets simples, qui définissent les chaînes de caractère en SQL.
Remarquez aussi que, si notre nom de site contient, entre autres, une apostrophe, nous aurons une erreur SQL :
nom <- "L'autre site"
pH <- 7
dbExecute(connexion, paste0(
"INSERT INTO sites VALUES (nextval('sequence_sites'), '",
nom,
"', ",
pH,
")"
))
Error: Parser Error: syntax error at or near "autre"
LINE 1: ... VALUES (nextval('sequence_sites'), 'L'autre site', 7)
^
Ce genre de problème a donné lieu a beaucoup de cas de piratage de sites
web au début des années 1990. Il suffit par exemple d’imaginer un
formulaire dans lequel un visiteur mal intentionné entrerait
A'; DELETE * FROM users; ou
A';INSERT INTO admin VALUES ''pirate'',''motdepassedupirate''; comme nom d’usager
ou mot de passe !!!
Pour ces deux raisons, il est maintenant recommandé de toujours utiliser les fonctions de protection fournies avec la librairie qui gère votre base de données. Dans notre cas, on parle de la combinaison DBI + DuckDB. L’idée derrière ces fonctions de protection sera d’encoder tous les éléments potentiellement dangereux d’une requête pour éviter ces problèmes, qu’ils soient accidentels ou malveillants.
Pour cela, il faudra travailler en deux étapes, soit préparer le squelette de notre requête, et ensuite, dans un deuxième temps, demander à DBI d’effectuer les remplacements.
Dans le squelette de requête, tous les éléments qui proviendront de l’extérieur
sont précédés d’un ? :
valeur_tannante <- "L'apostrophe de malheur"
squelette <- "INSERT INTO sites VALUES (nextval('sequence_sites'),?nom,?ph)"
Ensuite, la fonction interpolate nous permet de construire une requête, où on fournit les valeurs à utiliser pour chacun des remplacements
requete <- sqlInterpolate(
connexion,
squelette,
nom="L'autre site",
ph=6.5
)
print(requete)
<SQL> INSERT INTO sites VALUES (nextval('sequence_sites'),'L''autre site',6.5)
dbExecute(connexion,requete)
[1] 1
Cette façon de faire devrait être appliquée chaque fois qu’une partie de la requête provient d’un objet de R, même lorsqu’il s’agit d’un simple SELECT :
squelette <- "SELECT * FROM sites WHERE nom = ?nom"
requete <- sqlInterpolate(
connexion,
squelette,
nom="L'autre site"
)
print(requete)
<SQL> SELECT * FROM sites WHERE nom = 'L''autre site'
dbGetQuery(connexion, requete)
id nom ph
1 30 L'autre site 6.5
Suppression de données
La suppression de données en SQL s’effectue avec la fonction… DELETE!
Vérifions d’abord que nous avons des sites avec des pH sous 6.6 :
dbGetQuery(connexion, "SELECT * FROM sites ORDER BY ph LIMIT 5")
id nom ph
1 28 deuxième ajout 6.500000
2 30 L'autre site 6.500000
3 21 U 6.577113
4 24 X 6.655040
5 8 H 6.692542
Pour supprimer tous les sites dont le pH est < 6.6, on pourrait lancer la requête suivante :
dbExecute(connexion,"DELETE FROM sites WHERE ph < 6.6")
[1] 3
On peut ensuite vérifier que nos données ont bien été supprimées :
dbGetQuery(connexion, "SELECT * FROM sites ORDER BY ph LIMIT 5")
id nom ph
1 24 X 6.655040
2 8 H 6.692542
3 20 T 6.708648
4 4 D 6.746626
5 17 Q 6.759258
Dans les bases de données plus avancées comme MySQL, PostgreSQL, etc., une telle opération aurait aussi pu entraîner la suppression de toutes les parcelles associées au site, de toutes les présences associées aux parcelles, etc. Par contre, comme DuckDB est une base de données locale, et légère conçue pour une seul utilisateur à la fois, cette cascade n’a pas eu lieu :
dbGetQuery(connexion, "SELECT * FROM parcelles WHERE site_id = 21 LIMIT 5")
id site_id nom couvert_pct
1 21 21 otuzagswlo 55.11549
2 47 21 orqxestuqr 58.59047
3 73 21 vytwvjjvcr 47.13821
4 99 21 odicuudsgl 42.04843
5 125 21 izpwayhfaq 78.15716
Mise à jour des données
Enfin, on peut mettre à jour des données avec la commande UPDATE.
Par exemple, si on s’est trompés et que toutes les observations de la parcelle 3 étaient en fait la parcelle 4, on pourrait faire :
dbExecute(connexion,"UPDATE presences SET parcelle_id = 4 WHERE parcelle_id = 3")
[1] 7
Et ensuite vérifier que tout s’est bien déroulé :
dbGetQuery(connexion, "
SELECT parcelle_id, COUNT(*)
FROM presences
WHERE parcelle_id = 3 OR parcelle_id = 4
GROUP BY parcelle_id
")
parcelle_id count_star()
1 4 10
Conclusion et références
Comme pour la majorité des ateliers du Numérilab, nous n’avons ici qu’effleuré la surface de ce qu’il est possible de faire avec le SQL. J’espère que vous avez apprécié le fait que notre base de données traitait des requêtes sur des millions de lignes sans même sourciller. Évidemment, les bases de données relationnelles et le SQL ont leur place dans différents systèmes informatiques, allant de quelques Mo de données pour un projet de maîtrise, à plusieurs Po de données pour gérer le contenu d’un site web transactionnel de classe mondiale.
Si vous voulez en apprendre plus sur le SQL, la ressources que je vous recommanderais probablement (et celle qui m’a aidé à me dérouiller après quelques années sans faire de SQL) est le tutoriel de W3Schools : https://www.w3schools.com/sql/.
Je vous encourage aussi fortement à aller explorer SQLite, une autre base de données légère et locale comme DuckDB, qui est utilisée comme stockage dans beaucoup d’applications.