21 Manipuler des données avec `duckdb`

21.1 Tâches concernées et recommandations

L’utilisateur souhaite manipuler des données structurées sous forme de data.frame par le biais de l’écosystème duckdb (sélectionner des variables, sélectionner des observations, créer des variables, joindre des tables).

Important

Tâches concernées et recommandations

Pour des tables de données de taille petite et moyenne (inférieure à 1 Go ou moins d’un million d’observations), il est recommandé d’utiliser les packages tibble, dplyr et tidyr qui sont présentés dans la fiche Manipuler des données avec le tidyverse;
Pour des tables de données de grande taille (plus de 1 Go en CSV, plus de 200 Mo en Parquet, ou plus d’un million d’observations), il est recommandé d’utiliser soit les packages arrow (voir la fiche Manipuler des données avec arrow) et duckdb qui fait l’objet de la présente fiche, soit le package data.table qui fait l’objet de la fiche Manipuler des données avec data.table.
Il est essentiel de travailler avec la dernière version d’arrow, de duckdb et de R car les packages arrow et duckdb sont en cours de développement. Par ailleurs, les recommandations d’utilitR peuvent évoluer en fonction du développement de ces packages.
Si les données sont très volumineuses (plus de 5 Go en CSV, plus de 1 Go en Parquet ou plus de 5 millions d’observations), il est recommandé de manipuler les données avec duckdb (et avec arrow) plutôt qu’avec le tidyverse. Il peut arriver que le volume de données soit tellement important qu’il ne soit pas possible de les traiter avec duckdb et arrow; il faut s’orienter vers des infrastructures big data permettant le calcul distribué et utiliser des logiciels adaptés (Spark par exemple).

Note

Apprendre à utiliser duckdb n’est pas difficile, car la syntaxe utilisée est quasiment identique à celle du tidyverse. Toutefois, une bonne compréhension du fonctionnement de R et de duckdb est nécessaire pour bien utiliser duckdb sur des données volumineuses. Voici quelques conseils pour bien démarrer:

Il est indispensable de lire la fiche Manipuler des données avec le tidyverse avant de lire la présente fiche.
Il est recommandé de lire les fiches Se connecter à une base de données et Manipuler des données avec arrow avant de lire la présente fiche.
Il est complètement normal de rencontrer des erreurs difficiles à comprendre lorsqu’on commence à utiliser duckdb, il ne faut donc pas se décourager.
Il ne faut pas hésiter à demander de l’aide à des collègues, ou à poser des questions sur les salons Tchap adaptés (le salon Langage R par exemple).

21.2 Présentation du package `duckdb` et du projet associé

21.2.1 Qu’est-ce que `duckdb`?

DuckDB est un projet open-source (license MIT) qui propose un moteur SQL optimisé pour réaliser des travaux d’analyse statistique sur des bases de données :

un moteur SQL rapide, capable d’utiliser des données au format parquet sans les charger complètement en mémoire,
un dialecte SQL enrichi avec des fonctions qui facilitent l’analyse de données,
une installation et une utilisation faciles,
un moteur portable, utilisable sous Windows, MacOS, Linux, et interfacé avec de nombreux langages de programmation (R, Python, Javascript, etc.).

Un point important à comprendre est que DuckDB n’est pas un outil spécifique à R: DuckDB est un système de gestion de base de données (SGBD), similaire par exemple à une base PostgreSQL. Cela a deux conséquencées:

la base de données DuckDB a une existence propre sur le disque ou dans la mémoire, et on peut donc lui envoyer directement des requêtes SQL, sans passer par R.
Il faut bien distinguer le projet DuckDB du package R duckdb. Ce package propose simplement une interface avec R parmi les autres interfaces existantes : Python, Java, Javascript, Julia, etc.

Toutefois, DuckDB est très facile à utiliser avec R, ce qui permet de bénéficier des optimisations inhérentes au langage SQL, à la fois en terme d’utilisation de la mémoire et de rapidité de calcul. C’est de plus un bon intermédiaire avant de passer à des infrastructures avancées telles que spark ou oracle.

21.2.2 À quoi sert le package `duckdb`?

Du point de vue d’un statisticien utilisant R, le package duckdb permet de faire trois choses:

Importer des données (exemples: fichiers CSV, fichiers Parquet);
Manipuler des données avec la syntaxe dplyr, ou avec le langage SQL;
Écrire des données au format Parquet.

21.2.3 Quels sont les avantages de `duckdb`?

Disponibilité immédiate: on peut pré-visualiser les données ou le résultat d’un calcul sans l’exécuter totalement, sans attendre le chargement des données;
Performances élevées: duckdb est très rapide pour la manipulation de données tabulaires (nettement plus performant que dplyr par exemple);
Ne pas nécessairement charger les données en mémoire: duckdb permet également de travailler directement sur des fichiers du disque dur;
Optimisations automatiques: duckdb sélectionne automatiquement les colonnes nécessaires, et ne lit que les lignes nécessaires. Cela permet d’accélérer les calculs et de réduire considérablement les besoins en mémoire, même lorsque les données sont volumineuses;
Facilité d’apprentissage grâce aux approches dplyr et SQL: duckdb peut être utilisé avec les verbes de dplyr (select, mutate, etc.) et/ou avec le langage SQL. Par conséquent, il n’est pas nécessaire d’apprendre une nouvelle syntaxe pour utiliser duckdb, on peut s’appuyer sur la ou les approches que l’on maîtrise déjà.

21.2.4 Quels sont les points d’attention à l’usage ?

Représentation des données en mémoire : duckdb est un moteur SQL. Les lignes n’ont pas d’ordre pré-défini, et le résultat d’un traitement peut être dans un ordre imprévisible.
Traitement de données volumineuses: duckdb peut traiter de gros volumes de données, qu’elles soient en mémoire vive ou sur le disque dur. Lorsque les données sont en mémoire vive, les packages duckdb et arrow peuvent être utilisés conjointement de façon très efficace: cela veut dire concrètement que duckdb peut manipuler directement des données stockées dans un objet Arrow Table, sans avoir à convertir les données dans un autre format. Avec des données stockées sur le disque dur, duckdb est capable de faire les traitements sur des données plus volumineuses que la mémoire vive (RAM). C’est un avantage majeur en comparaison aux autres approches possibles en R (data.table et dplyr par exemple). Toutefois, il faut dans ce cas ajouter le temps de lecture des données au temps nécessaire pour le calcul.
Évaluation différée: duckdb construit des requêtes SQL, qui sont exécutées uniquement lorsque le résultat est explicitement demandée, après optimisation des étapes intermédiaires, et peuvent être exécutées partiellement. La Section 21.5.2 présente en détail cette notion.
Traduction en SQL: duckdb traduit automatiquement les instructions dplyr en requêtes SQL (de la même façon qu’arrow traduit ces instructions en code C++). Il arrive toutefois que certaines fonctions de dplyr n’aient pas d’équivalent direct en duckdb et ne puissent être traduites automatiquement. Dans ce cas (qui est heureusement moins fréquent qu’avec arrow), il faut parfois utiliser une fonction SQL directement ou trouver une solution pour contourner le problème. La Section 21.5.3 donne quelques trucs et astuces dans ce cas.
Interopérabilité: duckdb est conçu pour être interopérable entre plusieurs langages de programmation tels que R, Python, Java, C++, etc. Cela signifie que les données peuvent être échangées entre ces langages sans avoir besoin de convertir les données, d’où des gains importants de temps et de performance.

21.2.5 Quand utiliser `duckdb` plutôt que `arrow` ?

Les packages duckdb et arrow ont des cas d’usage très similaires (voir la fiche Manipuler des données avec arrow), mais on peut préférer l’un à l’autre selon les cas. On peut également les utiliser ensemble pour profiter de chacun de leurs avantages. Le tableau ci-dessous compare quelques cas d’usage de ces deux packages :

Je souhaite…	arrow	duckdb
Optimiser mes traitements pour des données volumineuses	✔️	✔️
Travailler sur un fichier .parquet ou .csv sans le charger entièrement en mémoire	✔️	✔️
Utiliser la syntaxe `dplyr` pour traiter mes données	✔️	✔️
Utiliser du langage SQL pour traiter mes données	❌	✔️
Joindre des tables très volumineuses (plus de 4 Go)	❌	✔️
Utiliser des fonctions fenêtres (voir Section 21.6)	❌	✔️
Utiliser des fonctions statistiques qui n’existent pas dans arrow (voir Section 21.6)	❌	✔️
Écrire un fichier .parquet	✔️	✔️ *

* pour écrire un fichier .parquet avec le package duckdb, il faut utiliser une instruction SQL (voir Section 21.5.4.2)

21.3 Installation de `duckdb`

Il suffit d’installer le package duckdb, qui contient à la fois DuckDB et une interface pour que R puisse s’y connecter.

install.packages("duckdb", repos="https://cloud.r-project.org")

21.4 Utilisation de `duckdb`

Dans cette section, on présente l’utilisation basique de duckdb. C’est très facile: il n’est pas nécessaire de connaître le langage SQL car il est possible d’utiliser duckdb avec la syntaxe dplyr.

21.4.1 Charger le package `duckdb`

Pour utiliser duckdb, il faut commencer par charger le package. Cela charge automatiquement le package DBI, qui permet de se connecter aux bases de données. Il est utile de charger également le package dplyr afin de pouvoir requêter la base de données avec la syntaxe bien connue de dplyr.

library(duckdb)
library(dplyr)

Le moteur duckdb fonctionnant “en dehors” de R, il détecte le nombre de processeurs et effectue les opérations en parallèle si possible.

21.4.2 Connexion à une base de données

duckdb est une base de données distante et s’utilise comme telle: il faut ouvrir une connexion, puis “charger” les données dans la base de données pour les manipuler.

Comme beaucoup d’autres bases de données (distantes ou locales), on ouvre une connexion au moteur duckdb avec une base de données en mémoire vive de la façon suivante :

conn_ddb <- DBI::dbConnect(drv = duckdb::duckdb())

Concrètement, cette commande crée une nouvelle base de données duckdb dans la mémoire vive. Cette base de données ne contient aucune donnée lorsqu’elle est créée. L’objet conn_ddb apparaît dans l’onglet Data de l’environnement RStudio, mais la liste des tables n’y est pas directement accessible. Pour plus d’informations, se reporter à la documentation du package DBI.

À la fin du traitement ou du programme, on ferme la connexion avec le code ci-dessous. L’option shutdown est importante : elle permet de fermer complètement la session duckdb et de libérer la mémoire utilisée. Si on n’utilise pas cette option, il arrive souvent que des connexions à moitié ouvertes continuent à consommer des ressources, et il faut alors relancer la session R.

DBI::dbDisconnect(conn_ddb, shutdown = TRUE)

Par défaut, duckdb utilisera tous les cœurs disponibles. Si vous travaillez sur un serveur mutualisé, il est conseillé de limiter le nombre de cœurs utilisés par duckdb afin de ne pas consommer toutes les ressources. Vous pouvez trouver plus d’information dans la section Configurer duckdb.

conn_ddb <- DBI::dbConnect(duckdb::duckdb(
  config = list(threads = "6")
))

Pour la suite, on supposera que la connexion à une base de données duckdb est ouverte.

21.4.3 Chargement des données

Une fois qu’on s’est connecté à une base de données duckDB, il faut charger des données dans cette base de données. Il y a deux façons de le faire:

En établissant un lien entre la base de données duckDB et les objets de la session R;
En indiquant à duckdb l’emplacement des données sur le disque dur.

21.4.3.1 Chargement de données provenant de la session `R`

La fonction duckdb_register() permet de charger dans duckdb des données présentes dans la session R. Cette méthode a l’avantage de ne pas recopier les données: elle se contente d’établir un lien logique entre la base de données duckdb et un objet de la session R. Voici un exemple avec la Base permanente des équipements: grâce à la fonction duckdb::duckdb_register(), l’objet bpe_ens_2018 est référencé dans la base de données duckdb sous le nom bpe_ens_2018_duckdb.

# Charger la Base permanente des équipements 2018 dans la session R
bpe_ens_2018 <- doremifasolData::bpe_ens_2018 |> as_tibble()

# Etablir le lien logique entre la base de données duckdb et la table de données
conn_ddb %>% duckdb::duckdb_register(
  name = "bpe_ens_2018_duckdb", 
  df = bpe_ens_2018)

Le code ci-dessous permet de vérifier que le chargement des données a bien fonctionné. La fonction tbl permet d’accéder à un objet de la base de données par le nom (de la table), ou par du code SQL (utilisation un peu plus avancée). Par défaut, duckdb affiche les 10 premières lignes du résultat, sans effectuer tout le calcul. C’est très pratique et très rapide !

conn_ddb %>% tbl("bpe_ens_2018_duckdb")

# Source:   table<bpe_ens_2018_duckdb> [?? x 7]
# Database: DuckDB v0.9.2 [unknown@Linux 6.5.0-1024-azure:R 4.3.2/:memory:]
   REG   DEP   DEPCOM DCIRIS    AN TYPEQU NB_EQUIP
   <chr> <chr> <chr>  <chr>  <dbl> <chr>     <dbl>
 1 84    01    01001  01001   2018 A401          2
 2 84    01    01001  01001   2018 A404          4
 3 84    01    01001  01001   2018 A504          1
 4 84    01    01001  01001   2018 A507          1
 5 84    01    01001  01001   2018 B203          1
 6 84    01    01001  01001   2018 C104          1
 7 84    01    01001  01001   2018 D233          1
 8 84    01    01001  01001   2018 F102          1
 9 84    01    01001  01001   2018 F111          1
10 84    01    01001  01001   2018 F113          1
# ℹ more rows

21.4.3.2 Chargement de données stockées sur le disque dur

Pour l’exemple suivant, on sauvegarde les données bpe_ens_2018 au format Parquet.

bpe_ens_2018 |> arrow::write_dataset("bpe_ens_2018_dataset")

Il existe deux méthodes pour manipuler des données stockées en Parquet avec duckdb sans avoir à les charger en mémoire: soit utiliser la fonction dplyr::tbl() qui lit directement les fichiers Parquet avec duckdb, soit utiliser la fonction arrow::open_dataset() et créer un lien logique avec la fonction arrow::to_duckdb(). Si la deuxième méthode est plus simple, surtout quand vous connaissez déjà arrow, la première est systématiquement plus efficace et peut générer des gains de consommation mémoire et de temps de traitement conséquents. Il est donc conseillé de ne pas lire vos fichiers avec arrow::open_dataset si vos traitements sont lourds (il ne faut pas hésiter à faire des tests).

La première approche repose uniquement sur duckdb. Vous devez utilisez la fonction dplyr::tbl:

conn_ddb %>% tbl("read_parquet('bpe_ens_2018_dataset/**/*.parquet')")

ℹ Using `ident_q()` for a table identifier is intended as fallback in case of
  bugs.
ℹ If you need it to work around a bug please open an issue
  <https://github.com/tidyverse/dbplyr/issues>.
It looks like you tried to incorrectly use a table in a schema as source.
ℹ If you want to specify a schema use `in_schema()` or `in_catalog()`.
ℹ If your table actually contains "." in the name use `check_from = FALSE` to
  silence this message.
This message is displayed once every 8 hours.

# Source:   SQL [?? x 7]
# Database: DuckDB v0.9.2 [unknown@Linux 6.5.0-1024-azure:R 4.3.2/:memory:]
   REG   DEP   DEPCOM DCIRIS    AN TYPEQU NB_EQUIP
   <chr> <chr> <chr>  <chr>  <dbl> <chr>     <dbl>
 1 84    01    01001  01001   2018 A401          2
 2 84    01    01001  01001   2018 A404          4
 3 84    01    01001  01001   2018 A504          1
 4 84    01    01001  01001   2018 A507          1
 5 84    01    01001  01001   2018 B203          1
 6 84    01    01001  01001   2018 C104          1
 7 84    01    01001  01001   2018 D233          1
 8 84    01    01001  01001   2018 F102          1
 9 84    01    01001  01001   2018 F111          1
10 84    01    01001  01001   2018 F113          1
# ℹ more rows

Quelques explications de cette commande:

La fonction read_parquet est une fonction interne à duckdb, elle ne doit surtout pas être confondue avec la fonction read_parquet() du package arrow. Remarque: duckdb propose aussi des fonctions pour lire d’autres formats comme csv, json…
**/*.parquet est un motif qui indique que vous souhaitez lire, dans tous les sous-dossiers quelque soit le niveau (**), l’ensemble des fichiers parquets (*.parquet) qui s’y trouvent. C’est notamment utile pour lire des fichiers Parquet partitionnés. Quand vous n’avez pas besoin de passer d’arguments à read_parquet, vous pouvez l’omettre :

conn_ddb %>% tbl('bpe_ens_2018_dataset/**/*.parquet')

It looks like you tried to incorrectly use a table in a schema as source.
ℹ If you want to specify a schema use `in_schema()` or `in_catalog()`.
ℹ If your table actually contains "." in the name use `check_from = FALSE` to
  silence this message.

# Source:   table<bpe_ens_2018_dataset/**/*.parquet> [?? x 7]
# Database: DuckDB v0.9.2 [unknown@Linux 6.5.0-1024-azure:R 4.3.2/:memory:]
   REG   DEP   DEPCOM DCIRIS    AN TYPEQU NB_EQUIP
   <chr> <chr> <chr>  <chr>  <dbl> <chr>     <dbl>
 1 84    01    01001  01001   2018 A401          2
 2 84    01    01001  01001   2018 A404          4
 3 84    01    01001  01001   2018 A504          1
 4 84    01    01001  01001   2018 A507          1
 5 84    01    01001  01001   2018 B203          1
 6 84    01    01001  01001   2018 C104          1
 7 84    01    01001  01001   2018 D233          1
 8 84    01    01001  01001   2018 F102          1
 9 84    01    01001  01001   2018 F111          1
10 84    01    01001  01001   2018 F113          1
# ℹ more rows

La seconde approche consiste à passer par arrow, puis à transmettre les données à duckdb. Cette méthode utilise un objet intermédiaire de type Arrow Dataset (voir la fiche Manipuler des données avec arrow):

# Créer une connexion au dataset Parquet
bpe_ens_2018_dataset <- arrow::open_dataset("bpe_ens_2018_dataset")

# Etablir le lien entre la base de données duckdb et le dataset Parquet
bpe_ens_2018_dataset %>% arrow::to_duckdb(conn_ddb)

# Source:   table<arrow_001> [?? x 7]
# Database: DuckDB v0.9.2 [unknown@Linux 6.5.0-1024-azure:R 4.3.2/:memory:]
   REG   DEP   DEPCOM DCIRIS    AN TYPEQU NB_EQUIP
   <chr> <chr> <chr>  <chr>  <dbl> <chr>     <dbl>
 1 84    01    01001  01001   2018 A401          2
 2 84    01    01001  01001   2018 A404          4
 3 84    01    01001  01001   2018 A504          1
 4 84    01    01001  01001   2018 A507          1
 5 84    01    01001  01001   2018 B203          1
 6 84    01    01001  01001   2018 C104          1
 7 84    01    01001  01001   2018 D233          1
 8 84    01    01001  01001   2018 F102          1
 9 84    01    01001  01001   2018 F111          1
10 84    01    01001  01001   2018 F113          1
# ℹ more rows

Ces deux approches ont un point commun important: elles établissent une connexion aux données contenues dans le dataset Parquet, mais elles ne chargent pas les données en mémoire (ni dans la mémoire de R, ni dans celle de DuckDB).

Pour plus de commodité, on sauvegarde l’instruction précédente dans la variable bpe_ens_2018_dataset.

bpe_ens_2018_dataset <- conn_ddb %>% 
  tbl('bpe_ens_2018_dataset/*.parquet')

It looks like you tried to incorrectly use a table in a schema as source.
ℹ If you want to specify a schema use `in_schema()` or `in_catalog()`.
ℹ If your table actually contains "." in the name use `check_from = FALSE` to
  silence this message.

21.4.4 Manipulation des données avec la syntaxe `dplyr`

Le package R duckdb a été écrit de façon à pouvoir manipuler les données avec la syntaxe de dplyr (select, filter, mutate, left_join, etc.). duckdb traduit le code R, y compris certaines fonctions de stringr et lubridate en requête SQL. Cela s’avère très commode en pratique, car lorsqu’on sait utiliser dplyr et le tidyverse, on peut commencer à utiliser duckdb sans avoir à apprendre une nouvelle syntaxe de manipulation de données.

Dans l’exemple suivant, on calcule le nombre d’équipements par région, à partir d’un tibble et à partir d’une table duckdb. La seule différence apparente entre les deux traitement est la présence de la fonction collect() à la fin des instructions; cette fonction indique que l’on souhaite obtenir le résultat du traitement sous la forme d’un tibble. La raison d’être de ce collect() est expliquée plus loin, dans le paragraphe sur l’évaluation différée. Les résultats sont identiques, à l’exception de l’ordre des lignes. En effet, un moteur SQL ne respecte pas l’ordre par défaut, il faut le demander explicitement avec arrange.

Manipulation d’un tibble

doremifasolData::bpe_ens_2018 |>
  group_by(REG) |>
  summarise(
    NB_EQUIP_TOT = sum(NB_EQUIP)
  )

# A tibble: 18 × 2
   REG   NB_EQUIP_TOT
   <chr>        <dbl>
 1 01           23939
 2 02           19068
 3 03            7852
 4 04           30767
 5 06            7353
 6 11          469181
 7 24           81379
 8 27           96309
 9 28          107186
10 32          175859
11 44          181130
12 52          119689
13 53          111291
14 75          237008
15 76          256813
16 84          303775
17 93          254405
18 94           21778

Manipulation d’une table duckdb

bpe_ens_2018_dataset |>
  group_by(REG) |>
  summarise(
    NB_EQUIP_TOT = sum(NB_EQUIP)
  ) |>
  collect()

Warning: Missing values are always removed in SQL aggregation functions.
Use `na.rm = TRUE` to silence this warning
This warning is displayed once every 8 hours.

# A tibble: 18 × 2
   REG   NB_EQUIP_TOT
   <chr>        <dbl>
 1 84          303775
 2 32          175859
 3 76          256813
 4 06            7353
 5 24           81379
 6 75          237008
 7 52          119689
 8 01           23939
 9 94           21778
10 93          254405
11 11          469181
12 04           30767
13 02           19068
14 03            7852
15 28          107186
16 53          111291
17 44          181130
18 27           96309

On peut examiner la requête SQL construite par duckdb avec la fonction show_query().

bpe_ens_2018_dataset |>
  group_by(REG) |>
  summarise(
    NB_EQUIP_TOT = sum(NB_EQUIP)
  ) |>
  show_query()

<SQL>
SELECT REG, SUM(NB_EQUIP) AS NB_EQUIP_TOT
FROM "bpe_ens_2018_dataset/*.parquet"
GROUP BY REG

Cette requête est envoyée au serveur SQL et exécutée de façon différente en fonction de la dernière instruction du traitement:

si le traitement se termine par collect(): le calcul est exécuté en entier et le résultat est retourné sous la forme d’un tibble,
si le traitement se termine par print(n=nb_lignes): le calcul est exécuté partiellement, et seules les nb_lignes demandées sont affichées. Cela permet de minimiser les ressources et la mémoire utilisées.

Ce point est important: en utilisant print(), on peut prévisualiser le résultat d’une requête duckdb de façon très rapide, sans exécuter tout le traitement. Il ne faut pas hésiter à s’en servir pour explorer les données et pour construire le traitement étape par étape, en ajustant en fonction des résultats.

21.4.5 Écriture au format Parquet

Pour écrire une table (ou le résultat de n’importe quelle requête) sur le disque au format Parquet, il est recommandé d’utiliser la librairie arrow.

bpe_ens_2018_dataset %>% 
  arrow::to_arrow() %>% arrow::write_dataset("temp_dataset")
list.files("temp_dataset") # liste des fichiers du répertoire temp_dataset/

[1] "part-0.parquet"

Pour un usage basique en syntaxe dplyr, passer par arrow (au lieu de SQL) est plus facile à manipuler, notamment quand on souhaite ajouter des options telle que le partitionnement.

21.4.6 Erreurs courantes

Cette section présente quelques erreurs classiques.

21.4.6.1 On a éliminé des colonnes nécessaires

bpe_ens_2018_dataset |> select(DEP) |>
  mutate(NB_EQUIP_TOTAL_DEP  = sum(NB_EQUIP))

Error in `mutate()`:
ℹ In argument: `NB_EQUIP_TOTAL_DEP = sum(NB_EQUIP)`
Caused by error:
! Object `NB_EQUIP` not found.

21.4.6.2 Convertir les types

Dans cet exemple, on veut multiplier un nombre par une indicatrice.

bpe_ens_2018_dataset %>%
  summarise(nb_boulangeries  = sum(NB_EQUIP * (TYPEQU == "B203")), .by = DEP)

Error in `collect()`:
! Failed to collect lazy table.
Caused by error:
! rapi_prepare: Failed to prepare query SELECT DEP, SUM(NB_EQUIP * (TYPEQU = 'B203')) AS nb_boulangeries
FROM "bpe_ens_2018_dataset/*.parquet"
GROUP BY DEP
LIMIT 11
Error: Binder Error: No function matches the given name and argument types '*(DOUBLE, BOOLEAN)'. You might need to add explicit type casts.
    Candidate functions:
    *(TINYINT, TINYINT) -> TINYINT
    *(SMALLINT, SMALLINT) -> SMALLINT
    *(INTEGER, INTEGER) -> INTEGER
    *(BIGINT, BIGINT) -> BIGINT
    *(HUGEINT, HUGEINT) -> HUGEINT
    *(FLOAT, FLOAT) -> FLOAT
    *(DOUBLE, DOUBLE) -> DOUBLE
    *(DECIMAL, DECIMAL) -> DECIMAL
    *(UTINYINT, UTINYINT) -> UTINYINT
    *(USMALLINT, USMALLINT) -> USMALLINT
    *(UINTEGER, UINTEGER) -> UINTEGER
    *(UBIGINT, UBIGINT) -> UBIGINT
    *(INTERVAL, BIGINT) -> INTERVAL
    *(BIGINT, INTERVAL) -> INTERVAL

LINE 1: SELECT DEP, SUM(NB_EQUIP * (TYPEQU = 'B203')) AS nb_boulangeries
                                 ^

Avec duckdb, il faut transformer explicitement un booléen en nombre (entier ou flottant).

bpe_ens_2018_dataset %>%
  summarise(nb_boulangeries  = sum(NB_EQUIP * as.integer(TYPEQU == "B203")), .by = DEP)

# Source:   SQL [?? x 2]
# Database: DuckDB v0.9.2 [unknown@Linux 6.5.0-1024-azure:R 4.3.2/:memory:]
   DEP   nb_boulangeries
   <chr>           <dbl>
 1 13               1626
 2 14                599
 3 30                685
 4 40                321
 5 02                339
 6 03                299
 7 17                628
 8 18                237
 9 43                237
10 57                871
# ℹ more rows

21.5 Notions avancées / bien utiliser `duckdb`

21.5.1 Configurer `duckdb`

duckdb propose de nombreux paramètres mais nous n’allons voir que les principaux. Vous pouvez vous reporter à la documentation officielle pour en apprendre davantage sur la configuration de duckdb.

21.5.1.1 Configuration lors de l’initialisation

Pour configurer duckdb lors de l’initialisation de la base de données (c’est-à-dire au moment où on utilise DBI::dbConnect(drv = duckdb::duckdb())), on utilise les arguments du driver duckdb.

# Configurer le driver duckdb
drv <- duckdb::duckdb(
  dbdir = "fichier.db", 
  config = list(
    threads = "4",
    memory_limit = "40GB",
    temp_directory = "tmp_path/",
    preserve_insertion_order = "true")
)

# Initaliser la base de données duckdb avec la configuration
conn_ddb <- DBI::dbConnect(drv = drv)

Voici une description des principaux paramètres de configuration:

dbdir : utiliser une base de données persistante. Par défaut, duckdb crée une base de données dans la mémoire vive, qui est automatiquement détruite lorsque vous fermez la session R ou la connexion duckdb. Si vous mettez un chemin dans le paramètre dbdir, duckdb créera une base de données sur disque que vous pourrez réouvrir à votre prochaine session.

Si vous utilisez principalement dplyr, les bases de données en mémoire sont certainement suffisantes. En revanche, ce paramètre peut éventuellement vous être utile si vous utilisez du SQL, si vous créez des vues ou si vous utilisez dplyr::compute.

threads : limiter le nombre de threads utilisés par duckdb. Pour simplifier, un thread est un processeur ou un morceau de processeur (l’unité électronique qui réalise les calculs). Par défaut, duckdb utilise tous les processeurs disponibles, ce qui n’est pas forcément souhaitable pour plusieurs raisons :
sur un serveur partagé, vos collègues seront gênés ;
il est conseillé de disposer de 5 à 10Go de mémoire par thread (5 pour des aggrégations, 10 pour des jointures) donc beaucoup de threads implique beaucoup de mémoire ;
avoir trop de threads peut être contre-productif.

Il n’existe pas de règle générale pour définir le nombre de threads, mais utiliser 4 à 8 threads (en respectant le ratio threads/mémoire ci-dessus) constitue un point de départ raisonnable. Au delà, les performances augmentent généralement peu pour une consommation mémoire plus importante.

memory_limit : limiter la mémoire vive utilisée par duckdb. Par défaut, duckdb limite la mémoire à 80% de la mémoire disponible sur le serveur. Si vous avez une quantité limitée de mémoire, essayez plutôt de limiter le nombre de threads en respectant la règle de 5 à 10 Go par thread.
temp_directory : définir le dossier sur disque dans lequel duckdb peut écrire des fichiers temporaires. Un avantage de duckdb est qu’il sait “déborder” sur disque pour une grande partie de ces opérations. Cela signifie que duckdb va écrire dans des fichiers temporaires sur le disque les données qu’il ne peut conserver en mémoire car il a atteint la limite de mémoire fixée. Le paramètre temp_directory permet de choisir dans quel dossier ces fichiers temporaires seront écrits. Toutefois, il est généralement beaucoup plus efficace de diminuer le nombre de threads que de déborder sur disque mais dans le cas où vous avez besoin de “juste un peu plus” de mémoire cela peut se révéler utile. A noter que ce paramètre est automatiquement fixé si vous avez décidé d’utiliser une base persistante.
preserve_insertion_order : préserver l’ordre de lecture/écriture ou non. duckdb peut consommer beaucoup de mémoire pour conserver l’ordre de lecture et d’écriture. Ce dernier paramètre permet d’autoriser duckdb à ne pas préserver l’ordre des données à la lecture et à l’écriture des fichiers dans le cas où il n’y a pas de clause ORDER BY / arrange.

21.5.1.2 Fixer les paramètres après l’initialisation

Vous pouvez également changer les paramètres d’une base après son initialisation en utilisant la commande dbExecute. Par exemple, pour fixer le nombre de threads à 4 :

dbExecute(conn_ddb, "SET threads = '4';")

21.5.2 L’évaluation différée avec `duckdb` (lazy evaluation)

Tip

Il est vivement conseillé de lire la fiche Manipuler des données avec arrow avant de lire cette section, en particulier la partie sur l’évaluation différée.

Quand on manipule des objets duckdb, on construit des requêtes SQL. Le package duckdb se contente de traduire le code dplyr en SQL sans l’exécuter (de la même façon que le package arrow traduit du code dplyr en instructions C++). On rappelle qu’il faut utiliser show_query() pour visualiser la requête. La fonction print() permet de pré-visualiser le résultat.

# Étape 1: compter les équipements
req_dep <- 
  bpe_ens_2018_dataset |>
  group_by(DEP) |> 
  summarise(
    NB_EQUIP_TOT = sum(NB_EQUIP)
  ) 
req_dep |> 
  show_query()

<SQL>
SELECT DEP, SUM(NB_EQUIP) AS NB_EQUIP_TOT
FROM "bpe_ens_2018_dataset/*.parquet"
GROUP BY DEP

# Étape 2: filtrer sur le département
req_dep_filter <- req_dep |> 
  filter(DEP == "59") 
req_dep_filter |> 
  show_query()

<SQL>
SELECT DEP, SUM(NB_EQUIP) AS NB_EQUIP_TOT
FROM "bpe_ens_2018_dataset/*.parquet"
GROUP BY DEP
HAVING (DEP = '59')

La fonction collect() envoie à duckdb l’instruction d’exécuter le calcul, et transmet les résultats à R. Un point essentiel est qu’avant l’instruction collect(), c’est le moteur SQL de duckdb qui fait les calculs, tandis qu’après l’instruction collect(), c’est le moteur de R qui fait les calculs car on manipule un objet R (tibble) standard. Par conséquent, il faut faire le plus de calculs possibles avant collect() pour bénéficier de la rapidité du moteur SQL !

req_dep_filter |> collect()

# A tibble: 1 × 2
  DEP   NB_EQUIP_TOT
  <chr>        <dbl>
1 59           76125

On pourrait penser que, lorsqu’on exécute l’ensemble de ce traitement, duckdb se contente d’exécuter les instructions les unes après les autres: compter les équipements par département, puis conserver uniquement le département 59. Mais en réalité duckdb fait beaucoup mieux que cela: duckdb analyse la requête avant de l’exécuter, et optimise le traitement pour minimiser le travail. Dans le cas présent, duckdb repère que la requête ne porte en fait que sur le département 59, et commence donc par filtrer les données sur le département avant de compter les équipements, de façon à ne conserver que le minimum de données nécessaires et à ne réaliser que le minimum de calculs. Ce type d’optimisation s’avère très utile quand les données à traiter sont très volumineuses.

Situation à éviter

La première étape de traitement est déclenchée par collect(), la table intermédiaire res_etape1 est donc un tibble. C’est le moteur d’exécution de dplyr qui est utilisé pour manipuler res_etape1 lors de la seconde étape, ce qui dégrade fortement les performances sur données volumineuses.

# Etape 1
res_etape1 <- 
  bpe_ens_2018_dataset |>
  group_by(DEP) |>
  summarise(
    NB_EQUIP_TOT = sum(NB_EQUIP)
  ) |>
  collect()

# Etape 2
res_final <- res_etape1 |> 
  filter(DEP == "59") |> 
  collect()

# Sauvegarder les résultats
arrow::write_parquet(res_final, "resultats.parquet")

Usage recommandé

La première étape construit une requête SQL, sans effectuer de calcul. La deuxième étape complète la requête sans effectuer de calcul. Ici, pas de fonction print(), donc pas de calcul partiel. Le calcul n’est exécuté qu’au moment de la sauvegarde des résultats par DuckDB, ce qui assure de bonnes performances notamment sur données volumineuses. Les données ne sont chargées dans la mémoire de R à aucun moment.

# Etape 1
res_etape1 <- bpe_ens_2018_dataset |>
  group_by(DEP) |>
  summarise(
    NB_EQUIP_TOT = sum(NB_EQUIP)
  ) 

# Etape 2
res_final <- res_etape1 |> 
  filter(DEP == "59") 

# Sauvegarder les résultats
res_final |> arrow::to_arrow() |> 
  arrow::write_parquet("resultats.parquet")

Tip

Si vous ne savez plus si une table de données est une requête SQL ou un tibble, il suffit d’exécuter print(votre_table) ou class(votre_table).

21.5.3 Fonctions non traduites et/ou comment passer des paramètres ?

Il peut arriver que le package duckdb ne parvienne pas à traduire votre code dplyr en SQL, par exemple lorsque vous voulez utiliser une fonction R dont duckdb ne connaît pas la traduction SQL, ou lorsque vous voulez passer un paramètre à une fonction. Pour surmonter ce problème (heureusement peu fréquent), il faut mettre les mains dans le mécanisme de traduction vers SQL. Il y a deux points importants:

Lorsque duckdb ne connaît pas la traduction SQL d’une fonction R est que la fonction inconnue est reprise directement dans le code SQL sans aucune modification. Voici un exemple, dans lequel on peut voir que la fonction fonction_inexistante() apparaît telle quelle dans le code SQL.

req <- bpe_ens_2018_dataset |> 
  mutate(test = fonction_inexistante(DEP)) |> 
  show_query()

<SQL>
SELECT "bpe_ens_2018_dataset/*.parquet".*, fonction_inexistante(DEP) AS test
FROM "bpe_ens_2018_dataset/*.parquet"

DuckDB contient un grand nombre de fonctions optimisées (documentation ici), et il est possible de les utiliser directement dans du code R.

Ces deux points ensemble permettent de surmonter dans la plupart des cas le problème des fonctions R inconnues de duckdb: il suffit d’appeler la fonction de DuckDB qui fait la même chose. Voici un exemple qui explique cela en détail dans le cas de la fonction R as.Date(). On commence par créer une petite table duckdb contenant des dates sous forme de chaînes de caractères avec le format “DD/MM/YYYY”.

# Créer des dates sous forme de chaînes de caractères
dates <- tibble(
  date_naissance = c("02/07/1980", "29/02/2004"),
  date_deces = c("05/06/2001", "12/07/2023")
)

# Créer une connexion entre ces données et la base de données duckdb
conn_ddb %>% duckdb::duckdb_register(name = "dates_duckdb", df = dates, overwrite = TRUE)

Le package duckdb dispose d’une traduction SQL de la fonction as.Date(), mais cette traduction a deux limites: elle n’accepte que les données en format “YYYY-MM-DD”, et ne supporte pas l’argument format qui permet de préciser que les données sont en format “DD/MM/YYYY”. Par conséquent, on rencontre une erreur si on essaie d’utiliser la fonction as.Date() avec l’argument format (car duckdb ne sait pas gérer cet argument), et on rencontre une erreur si on essaie d’utiliser la fonction as.Date() sans cet argument (car les données n’ont pas le bon format).

conn_ddb %>% tbl("dates_duckdb") %>% 
  mutate(date_naissance = as.Date(date_naissance, format = "%d/%m/%Y")) # erreur

Error in as.Date(date_naissance, format = "%d/%m/%Y"): unused argument (format = "%d/%m/%Y")

conn_ddb %>% tbl("dates_duckdb") %>% 
  mutate(date_naissance = as.Date(date_naissance)) # erreur

Error in `collect()`:
! Failed to collect lazy table.
Caused by error:
! rapi_execute: Failed to run query
Error: Conversion Error: date field value out of range: "02/07/1980", expected format is (YYYY-MM-DD)

On pourrait penser que ce problème est sérieux. En fait, la solution est très simple: il suffit d’utiliser la fonction strptime du moteur SQL DuckDB en indiquant le paramètre adéquat. Comme vous pouvez voir dans l’exemple suivant, on appelle cette fonction directement dans le code R. Par ailleurs, cette façon d’utiliser les fonctions de DuckDB dans du code R permet de passer facilement un paramètre à une fonction (le format “%d/%m/%Y” dans le cas présent).

conn_ddb %>% tbl("dates_duckdb") %>% 
  mutate(date_naissance = strptime(date_naissance, "%d/%m/%Y"))

# Source:   SQL [2 x 2]
# Database: DuckDB v0.9.2 [unknown@Linux 6.5.0-1024-azure:R 4.3.2/:memory:]
  date_naissance      date_deces
  <dttm>              <chr>     
1 1980-07-02 00:00:00 05/06/2001
2 2004-02-29 00:00:00 12/07/2023

Note

La logique présentée ici fonctionne également dans un cas plus avancé: l’utilisation d’une fonction sur plusieurs variables avec mutate_at. L’exemple ci-dessous reprend l’exemple ci-dessus avec deux variables.

liste_variables <- c("date_naissance","date_deces")
conn_ddb %>% tbl("dates_duckdb") %>% 
  mutate_at(liste_variables, ~ strptime(.,"%d/%m/%Y"))

# Source:   SQL [2 x 2]
# Database: DuckDB v0.9.2 [unknown@Linux 6.5.0-1024-azure:R 4.3.2/:memory:]
  date_naissance      date_deces         
  <dttm>              <dttm>             
1 1980-07-02 00:00:00 2001-06-05 00:00:00
2 2004-02-29 00:00:00 2023-07-12 00:00:00

21.5.4 Manipulation des données avec SQL

DuckDB étant un moteur SQL à part entière, on peut interagir avec DuckDB directement avec des requêtes SQL. Par exemple, en reprenant une table enregistrée plus haut avec la fonction duckdb::duckdb_register :

DBI::dbGetQuery(conn_ddb, "SELECT * FROM bpe_ens_2018_duckdb") |> head()

  REG DEP DEPCOM DCIRIS   AN TYPEQU NB_EQUIP
1  84  01  01001  01001 2018   A401        2
2  84  01  01001  01001 2018   A404        4
3  84  01  01001  01001 2018   A504        1
4  84  01  01001  01001 2018   A507        1
5  84  01  01001  01001 2018   B203        1
6  84  01  01001  01001 2018   C104        1

Vous pouvez créer des vues ou des tables explicitement. La fonction dbExecute() retourne le nombre de lignes modifiées, tandis que la fonction dbGetQuery retourne le résultat sous la forme d’un tibble. Si vous n’avez pas l’intention de conserver durablement une table intermédiaire, il est préférable de créer une vue (qui ne consomme pas de mémoire) plutôt qu’une table (qui consomme de la mémoire). On peut d’ailleurs noter que les fonctions read_parquet() en SQL et duckdb_register du package utilisent CREATE VIEW implicitement.

# Créer une table dans la base de données DuckDB
DBI::dbExecute(conn_ddb, "
               CREATE TABLE bpe_ens_2018_table AS 
               SELECT REG, SUM(NB_EQUIP) AS NB_EQUIP_TOT 
               FROM bpe_ens_2018_duckdb 
               GROUP BY REG") # Utilise de la mémoire

[1] 18

# Créer une vue dans la base de données DuckDB
DBI::dbExecute(conn_ddb, "
               CREATE VIEW bpe_ens_2018_view AS 
               SELECT REG, SUM(NB_EQUIP) AS NB_EQUIP_TOT 
               FROM bpe_ens_2018_duckdb 
               GROUP BY REG")   # n'utilise pas de mémoire

[1] 0

Vous pouvez ensuite requêter les objets créés dans la base SQL via dplyr:

conn_ddb %>% tbl("bpe_ens_2018_view")

# Source:   table<bpe_ens_2018_view> [?? x 2]
# Database: DuckDB v0.9.2 [unknown@Linux 6.5.0-1024-azure:R 4.3.2/:memory:]
   REG   NB_EQUIP_TOT
   <chr>        <dbl>
 1 06            7353
 2 76          256813
 3 02           19068
 4 03            7852
 5 24           81379
 6 84          303775
 7 32          175859
 8 01           23939
 9 75          237008
10 94           21778
# ℹ more rows

Vous pouvez bien sûr lire des fichiers Parquet, CSV ou autres en utilisant les fonctions de duckdb :

DBI::dbGetQuery(conn_ddb, "SELECT * FROM read_parquet('bpe_ens_2018_dataset/**/*.parquet') LIMIT 5")

  REG DEP DEPCOM DCIRIS   AN TYPEQU NB_EQUIP
1  84  01  01001  01001 2018   A401        2
2  84  01  01001  01001 2018   A404        4
3  84  01  01001  01001 2018   A504        1
4  84  01  01001  01001 2018   A507        1
5  84  01  01001  01001 2018   B203        1

Tip

Le SQL de duckdb est très proche de celui de PostgreSQL avec quelques évolutions très pertinentes.

21.5.4.1 Séparer vos traitements SQL en blocs

Si vos requêtes deviennent trop complexes et/ou longues, vous pouvez facilement les découper en créant des vues intermédiaires que vous réutiliserez plus tard :

# Créer une vue qui correspond à la première étape du traitement
dbExecute(conn_ddb, "CREATE OR REPLACE VIEW data1_nettoye AS SELECT ... FROM read_parquet('data1.parquet')")

# Créer une vue qui correspond à la deuxième étape du traitement
dbExecute(conn_ddb, "CREATE OR REPLACE VIEW data2_nettoye AS SELECT ... FROM read_parquet('data2.parquet')")

# Faire la dernière étape du traitement et récupérer les résultats dans un tibble
resultats <- dbGetQuery(conn_ddb, "SELECT * FROM data1_nettoye LEFT JOIN data2_nettoye ON data1.id = data2.id")

Et vous pouvez bien sûr créer des tables intermédiaires (temporaires ou non) à la place des vues (en utilisant CREATE TABLE pluôt que CREATE VIEW) pour éviter de les recalculer à chaque fois.

21.5.4.2 Écrire des fichiers

Vous pouvez exporter des données vers des fichiers en utilisant COPY ... TO ... :

dbExecute(conn_ddb, "COPY (SELECT * FROM read_parquet('bpe_ens_2018_dataset/**/*.parquet')) 
                      TO 'mon_dataset_parquet' (FORMAT PARQUET, PARTITION_BY (REG), OVERWRITE_OR_IGNORE 1)")

[1] 0

Si vous préférez utiliser les fonctions de arrow, vous pouvez créez une vue et utiliser dbplr::tbl avec arrow::write_dataset :

dbExecute(conn_ddb, "CREATE OR REPLACE VIEW output AS SELECT ...")

tbl(conn_ddb, "output") |>
  arrow::to_arrow() |>
  write_dataset("mon_dataset")

21.5.5 Optimisations

Les opérations difficiles en SQL, longues, nécessitant beaucoup de mémoire, sont les fonctions dites “fenêtre”: jointures, GROUP BY avec beaucoup de petits groupes, dédoublonnage, etc. On propose ici quelques techniques pour faire passer ces calculs difficiles.

21.5.5.1 Utilisation de la mémoire vive

Comme expliqué plus haut, les objets manipulés dans cette fiche sont des requêtes SQL, et ne nécessitent pas de mémoire vive. Les données déclarées par read_parquet sont stockées sur le disque dur, lues à la demande, et “oubliées” à la fin du calcul. On retourne le résultat du calcul.

Pour les opérations compliquées, il peut être nécessaire de charger les données en mémoire pour effectuer le calcul, au risque de saturer la mémoire. Lorsque ce problème se pose, duckdb renvoie un message du type:

Error: rapi_execute: Failed to run query
Error: Out of Memory Error: could not allocate block of size 262KB (99.7MB/100.0MB used)
Database is launched in in-memory mode and no temporary directory is specified.
Unused blocks cannot be offloaded to disk.

Launch the database with a persistent storage back-end
Or set PRAGMA temp_directory='/path/to/tmp.tmp'

Pour contourner le manque de mémoire vive, on propose les quatre techniques suivantes :

diminuer le nombre de threads utilisés par duckdb, donc moins de besoins de mémoire (mais aussi moins de parallélisme):

conn_ddb <- dbConnect(duckdb(),
config=list("threads"="1")))

dbExecute(conn_ddb, "SET threads = '1';")

exécuter et sauvegarder les résultats au fur et à mesure. La commande arrow::write_dataset et la commande SQL COPY request TO filename.parquet savent le faire automatiquement, sans faire déborder la mémoire, pour certains calculs.
découper le calcul et sauvegarder une base intermédiaire (cf ci-dessous).
adosser un fichier sur le disque dur à la base de données en mémoire au moment de la création de la connexion. Cela ralentit considérablement les calculs, et ne permet pas toujours d’obtenir un résultat.

conn_ddb <- dbConnect(duckdb(), dbdir = "my-db.duckdb")

L’interaction entre les différentes options de duckdb est complexe et rendent difficile l’élaboration de recommandations claires. Nous mettrons à jour cette fiche quand des benchmarks plus poussés seront disponibles.

21.5.5.2 Sauvegarder des résultats intermédiaires

Dans plusieurs cas, vous pouvez vouloir passer par des résultats intermédiaires :

Votre traitement est long et vous ne souhaitez pas le recalculer entièrement à chaque fois ;
Certaines requêtes sont trop compliquées pour le moteur SQL et/ou pour la traduction automatique, vous devez le découper.

Vous avez plusieurs méthodes possibles :

arrow::write_dataset() sait faire les calculs par morceaux automatiquement, et libère la mémoire au fur et à mesure.

conn_ddb %>% calcul1() %>%
  arrow::to_arrow() %>%
  arrow::write_dataset("base_intermediaire")

arrow::open_dataset("base_intermediaire") %>%
  arrow::to_duckdb(conn_ddb) %>%
  calcul2()

Vous pouvez utiliser dbplyr::compute() pour créer une table duckdb stockée sur le disque (si vous avez préalablement créé une base sur disque) que vous pourrez directement utiliser par la suite dans une autre session :

conn_ddb %>%
  calcul1() %>%
  compute(name = "matable", temporary = FALSE)

tbl(conn_dbb, "matable") %>%
  calcul2()

La première méthode avec arrow est généralement la plus rapide et la seconde avec dbplyr::compute sur une table nommée est la plus efficace (de loin) en terme d’occupation mémoire.

A noter que vous pouvez également utiliser dbplyr::compute pour créer une table temporaire duckdb stockée en mémoire qui disparaitra à la fin de votre session :

table_temporaire <- conn_ddb %>%
  calcul1() %>%
  compute()

table_temporaire %>%
  calcul2()

21.5.5.3 Partitionner les données

Pour exécuter une fonction fenêtre, il faut pouvoir localiser les données en mémoire avec un index.
Les fichiers parquet ont un index min-max : les fichiers sont structurés en blocs, et on indique le minimum et maximum des valeurs du bloc dans les métadonnées. Ceci permet de sauter la lecture d’un bloc si l’on s’intéresse à des valeurs en dehors de la plage min-max, parce que l’on filtre les données par exemple.
En SQL, on peut créer un index, mais il faut que les données soient en mémoire, ce qui peut s’avérer être incompatible avec de très grosses volumétries.
Par contre, on peut partitionner les données, et le moteur SQL sait utiliser le partitionnement comme un index.

bpe_ens_2018_dataset %>% 
  arrow::to_arrow() %>% 
  arrow::write_dataset("bpe_ens_2018_dataset_parts", partitioning = "REG" )
list.files("bpe_ens_2018_dataset_parts") # on obtient un sous-répertoire par région

 [1] "REG=01" "REG=02" "REG=03" "REG=04" "REG=06" "REG=11" "REG=24" "REG=27"
 [9] "REG=28" "REG=32" "REG=44" "REG=52" "REG=53" "REG=75" "REG=76" "REG=84"
[17] "REG=93" "REG=94"

21.5.5.4 Exécuter les traitements par groupe explicitement

S’il faut absolument charger des données en mémoire, on peut découper le calcul pour ne charger qu’une partie des données. Par exemple, faire une jointure région par région au lieu de faire la jointure sur toute la base d’un coup. On peut utiliser le partitionnement pour sauvegarder les résultats partiels, et les ré-assembler ensuite.

groups <- bpe_ens_2018_dataset %>% 
  distinct(REG) %>%
  pull() # liste des modalités de la variable REG

# Définir une fonction qui traite un morceau des données
# Puis exporte le résultats dans un Parquet partitionné
f <- function(x) {
  bpe_ens_2018_dataset %>% 
    filter(REG == x) %>% 
    calcul_long() %>% 
    arrow::to_arrow() %>% 
    arrow::write_dataset("resultat", partitioning = "REG")
}

# Appliquer la fonction à chaque groupe
purrr::walk(f, groups)

21.6 Comparaison avec `arrow`

arrow et duckdb partagent de nombreux concepts. Voici quelques différences :

duckdb comprend parfaitement SQL. Si vous savez utiliser PROC SQL avec SAS, vous ne serez pas dépaysés.
Le projet duckdb est très récent. Il y a régulièrement des évolutions qui sont souvent des extensions ou des optimisations, et parfois la résolution de bugs. arrow est un projet plus ancien et plus mature.
Certaines fonctions standards de R ne sont pas traduites, mais la situation est meilleure du côté de duckdb que d’arrow. Hormis write_dataset(), la plupart des traitements peuvent être effectués en utilisant uniquement duckdb, sans passer par arrow.
Les conversions de type: duckdb est plus permissif que arrow et fera plus facilement des conversions automatiques sans danger.
Les jointures de tables volumineuses: arrow ne parvient pas à joindre des tables de données très volumineuses; il est préférable d’utiliser duckdb pour ce type d’opération.
Les réorganisations de données : les fonctions pivot_wider et pivot_longer existent nativement dans duckdb mais pas dans arrow.
Les fonctions fenêtre (window functions): arrow ne permet pas d’ajouter directement à une table des informations issues d’une agrégation par groupe de la même table. Par exemple, arrow ne peut pas ajouter directement à la base permanente des équipements une colonne égale au nombre total d’équipements du département. Le code fonctionne en duckdb.

# arrow ne peut pas exécuter ceci
bpe_ens_2018_dataset |>
  group_by(DEP) |>
  mutate(NB_EQUIP_TOTAL_DEP  = sum(NB_EQUIP)) |>
  select(DEP, NB_EQUIP, NB_EQUIP_TOTAL_DEP)

# Source:   SQL [?? x 3]
# Database: DuckDB v0.9.2 [unknown@Linux 6.5.0-1024-azure:R 4.3.2/:memory:]
# Groups:   DEP
   DEP   NB_EQUIP NB_EQUIP_TOTAL_DEP
   <chr>    <dbl>              <dbl>
 1 09           2               7316
 2 09           3               7316
 3 09           5               7316
 4 09          45               7316
 5 09           1               7316
 6 09           1               7316
 7 09           1               7316
 8 09           1               7316
 9 09           3               7316
10 09           1               7316
# ℹ more rows

les empilements de tables: il est facile d’empiler plusieurs tibbles avec dplyr grâce à la fonction bind_rows(): bind_rows(table1, table2, table3, table4). En revanche, il n’existe pas à ce jour de fonction équivalente dans arrow ou dans duckdb: il faut empiler les tables deux à deux avec les fonctions union_all() et union(). La différence entre arrow et duckdb est que duckdb est plus souple et acceptera d’empiler des tables qui ne sont pas exactement compatibles (exemple: pas le même nombre de colonnes), tandis qu’arrow exige que les deux tables soient parfaitement compatibles (il faut le même nombre de colonnes avec le même nom et le même type, ce qui n’est pas toujours le cas en pratique). Dans l’exemple suivant, on empile deux tables qui n’ont pas exactement le même nombre de colonnes:

# Comment empiler de multiples tables
table_empilees  <- bpe_ens_2018_dataset %>% 
  union_all(bpe_ens_2018_dataset |> select(-DEPCOM))

21.7 Pour en savoir plus

la documentation officielle du moteur DuckDB (en anglais) ;
la documentation du package R DuckDB ;
la documentation du package DBI décrit les mécanismes de traduction dplyr vers SQL utilisés dans toutes les bases de données interfacées avec R.

21.1 Tâches concernées et recommandations

21.2 Présentation du package duckdb et du projet associé

21.2.1 Qu’est-ce que duckdb?

21.2.2 À quoi sert le package duckdb?

21.2.3 Quels sont les avantages de duckdb?

21.2.4 Quels sont les points d’attention à l’usage ?

21.2.5 Quand utiliser duckdb plutôt que arrow ?

21.3 Installation de duckdb

21.4 Utilisation de duckdb

21.4.1 Charger le package duckdb

21.4.2 Connexion à une base de données

21.4.3 Chargement des données

21.4.3.1 Chargement de données provenant de la session R

21.4.3.2 Chargement de données stockées sur le disque dur

21.4.4 Manipulation des données avec la syntaxe dplyr

21.4.5 Écriture au format Parquet

21.4.6 Erreurs courantes

21.4.6.1 On a éliminé des colonnes nécessaires

21.4.6.2 Convertir les types

21.5 Notions avancées / bien utiliser duckdb

21.5.1 Configurer duckdb

21.5.1.1 Configuration lors de l’initialisation

21.5.1.2 Fixer les paramètres après l’initialisation

21.5.2 L’évaluation différée avec duckdb (lazy evaluation)

21.5.3 Fonctions non traduites et/ou comment passer des paramètres ?

21.5.4 Manipulation des données avec SQL

21.5.4.1 Séparer vos traitements SQL en blocs

21.5.4.2 Écrire des fichiers

21.5.5 Optimisations

21.5.5.1 Utilisation de la mémoire vive

21.5.5.2 Sauvegarder des résultats intermédiaires

21.5.5.3 Partitionner les données

21.5.5.4 Exécuter les traitements par groupe explicitement

21.6 Comparaison avec arrow

21.7 Pour en savoir plus

21.2 Présentation du package `duckdb` et du projet associé

21.2.1 Qu’est-ce que `duckdb`?

21.2.2 À quoi sert le package `duckdb`?

21.2.3 Quels sont les avantages de `duckdb`?

21.2.5 Quand utiliser `duckdb` plutôt que `arrow` ?

21.3 Installation de `duckdb`

21.4 Utilisation de `duckdb`

21.4.1 Charger le package `duckdb`

21.4.3.1 Chargement de données provenant de la session `R`

21.4.4 Manipulation des données avec la syntaxe `dplyr`

21.5 Notions avancées / bien utiliser `duckdb`

21.5.1 Configurer `duckdb`

21.5.2 L’évaluation différée avec `duckdb` (lazy evaluation)

21.6 Comparaison avec `arrow`