Magie de DuckDB 🦆

DuckDB en quelques mots

DuckDB est un logiciel permettant d’exécuter du code SQL sur différents types de données de manière très légère, avec zéro administration, et une optimisation pour les opérations “analytiques”.
Qu’entend-on par “analytique” ? En général, c’est un terme à opposer à “transactionnel”, le travail historique demandé aux bases de données, à savoir la mise à jour de données avec un système de confirmation / restauration des opérations. La partie “analytique”, elle, correspond plutôt à l’aspect de requêtage et de traitement statistique des données.
Si DuckDB s’avère finalement très bon sur l’aspect transactionnel, c’est d’abord pour des besoins analytiques qu’on l’utilise, et ce sera le cas ici.

Sources de données possibles (dans R) :

• un data.frame 📰 _{(je n’ai pas trouvé d’emoji plus convaincant pour “data.frame”} 😅₎
• un fichier délimité 🧾 type CSV / séparateur tabulation / etc.
• un ou plusieurs fichiers Parquet (en collaboration avec Arrow)🗃️

Voyons cela en détail, et en chronométrant les temps de traitement (sur un PC vieux de quelques années, avec le package {tictoc}) pour donner un ordre de grandeur.

DuckDB 101 : comment démarrer ?

Chaque utilisateur aura sa propre base de données DuckDB, pilotée depuis R. Evidemment la première chose à faire sera d’activer le package {duckdb}. Puis de démarrer une session DuckDB, qui sera associée à un fichier physique. Le rôle de celui-ci est de stocker si besoin des données volumineuses en cours de traitement.

library(duckdb)
tictoc::tic()
bd <- dbConnect(duckdb())
tictoc::toc()

0.05 sec elapsed

La syntaxe ci-dessus est le minimum vital pour démarrer une session DuckDB fonctionnelle. Le dialogue avec cette session passera désormais par l’objet nommé bd (le nom est bien sûr libre, il s’appelle con dans quasiment tous les exemples que j’ai pu lire ; vous pouvez aussi l’appeler coincoin ou howard).

On peut ajouter des options, particulièrement utiles dans un environnement contraint (serveur R partagé avec d’autres utilisateurs) pour éviter un accaparement excessif des ressources.

tictoc::tic()
bd <- dbConnect(duckdb(
  dbdir = "fichier_stockage_permanent.duckdb",
  config = list(temp_directory = "fichier_stockage_temporaire.duckdb",
                threads = "4", 
                memory_limit = "4GB")
))
tictoc::toc()

0.05 sec elapsed

• 📁Le fichier de l’option dbdir servira à stocker des tables créées au cours de la session DuckDB (principalement via la syntaxe SQL CREATE TABLE) ou d’autres objets SQL (vue, schéma, etc.). Ce fichier est conservé à la fin de la session et peut être réutilisé dans une autre session pour repartir de ces tables.
  Indiquez dans cette option un fichier non partagé (on ne peut pas avoir plusieurs sessions DuckDB simultanées qui pointent vers le même fichier), dans un répertoire où il y a de la place libre. En particulier, ne pointez pas vers un fichier temporaire comme ce que crée tempfile() car la taille de ces fichiers est souvent limitée.
• 📁Le fichier de l’option temp_directory servira le temps de la session DuckDB à stocker des données sur disque si la mémoire allouée à la session n’est pas suffisante (décharge ou spill). Ce fichier est détruit à la fin de la session DuckDB et tout ce qui y est stocké est perdu. Le stockage n’y est jamais volontaire de la part de l’utilisateur, contrairement à dbdir.
  Indiquez dans cette option un fichier non partagé (on ne peut pas avoir plusieurs sessions DuckDB qui pointent vers le même fichier), dans un répertoire où il y a de la place libre. En particulier, ne pointez pas vers un fichier temporaire comme ce que crée tempfile() car la taille de ces fichiers est souvent limitée.
• 🤍L’option threads indique le nombre de coeurs de calculs utilisables pour paralléliser les requêtes. Par courtoisie pour vos collègues, évitez d’utiliser plus du tiers des coeurs du serveur.
• 🧠L’option memory_limit indique la RAM associée à la session DuckDB. Quand cette quantité sera saturée, DuckDB écrira sur le disque dans le fichier indiqué précédemment. Là encore il s’agit de ne pas privatiser un serveur au détriment des collègues. Si la quantité de RAM indiquée ici est basse, l’exécution sera juste plus lente.

Une liste exhaustive des options se trouve ici.

Mettre des données à disposition de DuckDB

Evidemment, dans une base de données, il faut des données. Il ne s’agit pas forcément de les importer dans DuckDB, une des forces de cet outil étant de pouvoir travailler sur des liens vers des sources de données.

library(nycflights13)
tictoc::tic()
data(flights)

duckdb_register(bd, "tb_vols", flights)
tictoc::toc()

0.82 sec elapsed

tictoc::tic()
duck_vols <- dplyr::tbl(bd, "tb_vols")
# affichage des premières lignes
duck_vols

# Source:   table<tb_vols> [?? x 19]
# Database: DuckDB 1.4.4 [PC@Windows 10 x64:R 4.5.3/C:\Users\PC\Documents\pro\linkedin\fichier_stockage_permanent.duckdb]
    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
   <int> <int> <int>    <int>          <int>     <dbl>    <int>          <int>
 1  2013     1     1      517            515         2      830            819
 2  2013     1     1      533            529         4      850            830
 3  2013     1     1      542            540         2      923            850
 4  2013     1     1      544            545        -1     1004           1022
 5  2013     1     1      554            600        -6      812            837
 6  2013     1     1      554            558        -4      740            728
 7  2013     1     1      555            600        -5      913            854
 8  2013     1     1      557            600        -3      709            723
 9  2013     1     1      557            600        -3      838            846
10  2013     1     1      558            600        -2      753            745
# ℹ more rows
# ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
#   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
#   hour <dbl>, minute <dbl>, time_hour <dttm>

tictoc::toc()

0.19 sec elapsed

tictoc::tic()
dbExecute(bd, "DROP TABLE IF 
EXISTS tb_films") # à tout hasard

[1] 0

dbExecute(bd,
          "CREATE TABLE tb_films AS
          SELECT *
          FROM 'C:/olivier/imdb/title.basics.tsv'")

[1] 12621902

tictoc::toc()

12.7 sec elapsed

library(dplyr, quietly = TRUE)
tictoc::tic()
tbl(bd, "tb_films") %>% glimpse()

Rows: ??
Columns: 9
Database: DuckDB 1.4.4 [PC@Windows 10 x64:R 4.5.3/C:\Users\PC\Documents\pro\linkedin\fichier_stockage_permanent.duckdb]
$ tconst         <chr> "tt0000001", "tt0000002", "tt0000003", "tt0000004", "tt…
$ titleType      <chr> "short", "short", "short", "short", "short", "short", "…
$ primaryTitle   <chr> "Carmencita", "Le clown et ses chiens", "Poor Pierrot",…
$ originalTitle  <chr> "Carmencita", "Le clown et ses chiens", "Pauvre Pierrot…
$ isAdult        <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
$ startYear      <chr> "1894", "1892", "1892", "1892", "1893", "1894", "1894",…
$ endYear        <chr> "\\N", "\\N", "\\N", "\\N", "\\N", "\\N", "\\N", "\\N",…
$ runtimeMinutes <chr> "1", "5", "5", "12", "1", "1", "1", "1", "45", "1", "1"…
$ genres         <chr> "Documentary,Short", "Animation,Short", "Animation,Come…

tictoc::toc()

0.03 sec elapsed

tictoc::tic()
dbExecute(bd, "DROP TABLE IF 
EXISTS tb_films") # supprime le 1er essai

[1] 0

dbExecute(bd,
          "CREATE TABLE tb_films AS
          SELECT *
          FROM read_csv('C:/olivier/imdb/title.basics.tsv',
                        delim='\t',
                        nullstr='\\N',
                        header=true,
                        columns={
                          'tconst'         : 'VARCHAR',
                          'titleType'      : 'VARCHAR',
                          'primaryTitle'   : 'VARCHAR',
                          'originalTitle'  : 'VARCHAR',
                          'isAdult'        : 'SMALLINT',
                          'startYear'      : 'INTEGER',
                          'endYear'        : 'INTEGER',
                          'runtimeMinutes' : 'FLOAT',
                          'genres'         : 'VARCHAR'
                        }
          )")

[1] 12621902

tictoc::toc()

6.4 sec elapsed

tictoc::tic()
tbl(bd, "tb_films") %>% glimpse()

Rows: ??
Columns: 9
Database: DuckDB 1.4.4 [PC@Windows 10 x64:R 4.5.3/C:\Users\PC\Documents\pro\linkedin\fichier_stockage_permanent.duckdb]
$ tconst         <chr> "tt0000001", "tt0000002", "tt0000003", "tt0000004", "tt…
$ titleType      <chr> "short", "short", "short", "short", "short", "short", "…
$ primaryTitle   <chr> "Carmencita", "Le clown et ses chiens", "Poor Pierrot",…
$ originalTitle  <chr> "Carmencita", "Le clown et ses chiens", "Pauvre Pierrot…
$ isAdult        <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
$ startYear      <int> 1894, 1892, 1892, 1892, 1893, 1894, 1894, 1894, 1894, 1…
$ endYear        <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
$ runtimeMinutes <dbl> 1, 5, 5, 12, 1, 1, 1, 1, 45, 1, 1, 1, 1, 1, 2, 1, 1, 1,…
$ genres         <chr> "Documentary,Short", "Animation,Short", "Animation,Come…

tictoc::toc()

0.03 sec elapsed

tictoc::tic()
dbSendQuery(bd,
            "COPY(
               SELECT *
               FROM read_csv('C:/olivier/imdb/title.basics.tsv',
                             delim='\t',
                             nullstr='\\N',
                             header=true,
                             columns={
                               'tconst'         : 'VARCHAR',
                               'titleType'      : 'VARCHAR',
                               'primaryTitle'   : 'VARCHAR',
                               'originalTitle'  : 'VARCHAR',
                               'isAdult'        : 'SMALLINT',
                               'startYear'      : 'INTEGER',
                               'endYear'        : 'INTEGER',
                               'runtimeMinutes' : 'FLOAT',
                               'genres'         : 'VARCHAR'
                             })
               ) TO 'c:/olivier/imdb/films/films.parquet' 
                 (FORMAT parquet, COMPRESSION zstd)")
tictoc::toc()

3.25 sec elapsed

tictoc::tic()
duckdb_register_arrow(bd, "tb_films",
                      arrow::open_dataset("c:/olivier/imdb/films"))

films <- tbl(bd, "tb_films")
tictoc::toc()

0.17 sec elapsed

Construire des requêtes

La suite de cette histoire est assez classique puisqu’elle passe par le package {dbplyr} qui traduit du code {dplyr} en SQL pour que DuckDB l’exécute. La syntaxe est ici la même qu’avec d’autres bases de données et les documentations sur Internet ne manquent pas.

Notons que DuckDB sait également réaliser des opérations de pivots comme pivot_longer et pivot_wider de {tidyr} que {dbplyr} traduira.

Autre point important : il faut toujours activer les packages dont sont issues les fonctions à traduire. Par exemple pour travailler sur des dates, il faut activer {dbplyr} et {lubridate} pour qu’une fonction comme month ou year soit traduite.

library(dbplyr)

tictoc::tic()
films %>% 
  mutate(decennie = floor(startYear/10)*10) %>% 
  filter( ! is.na(decennie)) %>% 
  count(decennie) %>% 
  arrange(desc(decennie))

# Source:     SQL [?? x 2]
# Database:   DuckDB 1.4.4 [PC@Windows 10 x64:R 4.5.3/C:\Users\PC\Documents\pro\linkedin\fichier_stockage_permanent.duckdb]
# Ordered by: desc(decennie)
   decennie       n
      <dbl>   <dbl>
 1     2110       1
 2     2030      21
 3     2020 3020433
 4     2010 3953305
 5     2000 1681824
 6     1990  794647
 7     1980  502336
 8     1970  447882
 9     1960  361829
10     1950  179321
11     1940   31519
12     1930   33840
13     1920   37025
14     1910   72627
15     1900   25799
16     1890    6117
17     1880      79
18     1870      32

tictoc::toc()

0.26 sec elapsed

Trois stratégies sont possibles en fin de requête :

• on ne fait rien de spécial et la requête est traduite en SQL. Si on l’affecte à un objet, c’est une lazy query, c’est à dire que rien n’est exécuté sur le moment. Le SQL est mémorisé, et quand DuckDB sera obligé de l’exécuter (pour afficher le résultat, faire une jointure, créer un data.frame R) tout l’enchaînement des requêtes passées sera considéré, optimisé et exécuté. Si on ne l’affecte pas, les premières lignes du résultat sont calculées et affichées dans la Console
• on ajoute collect() en fin de requête. Dans ce cas la requête est exécutée et le résultat envoyé intégralement à R. Attention si ce résultat est volumineux !
• on ajoute compute("nom_table_duckdb") en fin de requête. La requête est exécutée et son résultat, stocké dans DuckDB. La table DuckDB (une vraie table, cette fois, pas une série de liens) sera stockée dans le fichier défini par dbdir.

library(stringr)
tictoc::tic()
lazy <- films %>% 
          filter(str_detect(str_to_lower(originalTitle),"superman") 
                 & ! str_detect(str_to_lower(titleType), "tv")) %>% 
          select(startYear, titleType, originalTitle, 
                 runtimeMinutes, genres) %>% 
          arrange(startYear, titleType)
# à ce stade, rien n'est calculé
tictoc::toc()

0.01 sec elapsed

tictoc::tic()
lazy %>% show_query() # affiche le SQL

<SQL>
SELECT startYear, titleType, originalTitle, runtimeMinutes, genres
FROM tb_films
WHERE (REGEXP_MATCHES(LOWER(originalTitle), 'superman') AND NOT(REGEXP_MATCHES(LOWER(titleType), 'tv')))
ORDER BY startYear, titleType

tictoc::toc()

0.04 sec elapsed

tictoc::tic()
lazy %>% count() # nombre de lignes de la requête

# Source:     SQL [?? x 1]
# Database:   DuckDB 1.4.4 [PC@Windows 10 x64:R 4.5.3/C:\Users\PC\Documents\pro\linkedin\fichier_stockage_permanent.duckdb]
# Ordered by: startYear, titleType
      n
  <dbl>
1   304

tictoc::toc()

0.31 sec elapsed

tictoc::tic()
lazy # affichage des premiers résultats

# Source:     SQL [?? x 5]
# Database:   DuckDB 1.4.4 [PC@Windows 10 x64:R 4.5.3/C:\Users\PC\Documents\pro\linkedin\fichier_stockage_permanent.duckdb]
# Ordered by: startYear, titleType
   startYear titleType originalTitle                 runtimeMinutes genres      
       <int> <chr>     <chr>                                  <dbl> <chr>       
 1      1941 short     Superman                                  10 Action,Adve…
 2      1948 movie     Superman                                  88 Sci-Fi      
 3      1948 movie     Superman                                 244 Action,Sci-…
 4      1951 movie     Superman and the Mole Men                 58 Action,Adve…
 5      1954 movie     Superman Flies Again                      77 Action,Fant…
 6      1954 movie     Superman and the Jungle Devil             77 Action,Fant…
 7      1954 movie     Superman in Exile                         77 Action,Fant…
 8      1954 movie     Superman and Scotland Yard                90 Action,Fant…
 9      1954 movie     Superman's Peril                          77 Action,Fant…
10      1954 short     Stamp Day for Superman                    18 Action,Adve…
# ℹ more rows

tictoc::toc()

0.31 sec elapsed

tictoc::tic()
(superman <- lazy %>% collect()) # récupération dans R (l'en-tête diffère)

# A tibble: 304 × 5
   startYear titleType originalTitle                 runtimeMinutes genres      
       <int> <chr>     <chr>                                  <dbl> <chr>       
 1      1941 short     Superman                                  10 Action,Adve…
 2      1948 movie     Superman                                  88 Sci-Fi      
 3      1948 movie     Superman                                 244 Action,Sci-…
 4      1951 movie     Superman and the Mole Men                 58 Action,Adve…
 5      1954 movie     Superman Flies Again                      77 Action,Fant…
 6      1954 movie     Superman and the Jungle Devil             77 Action,Fant…
 7      1954 movie     Superman in Exile                         77 Action,Fant…
 8      1954 movie     Superman and Scotland Yard                90 Action,Fant…
 9      1954 movie     Superman's Peril                          77 Action,Fant…
10      1954 short     Stamp Day for Superman                    18 Action,Adve…
# ℹ 294 more rows

tictoc::toc()

0.36 sec elapsed

Performances comparées des différentes approches

Sur les données IMDB, à titre d’exemple, on va comparer les différentes configurations évoquées ci-dessous, et des imports R classiques complétés par des requêtes {dplyr}.

library(microbenchmark)
library(vroom)
library(dplyr)
library(stringr)
library(dbplyr)
library(arrow)
library(duckdb)

import_films_csv <- function(){
  vroom::vroom("c:/olivier/imdb/title.basics.tsv",
               delim = "\t",
               na = "\\N",
               col_types = cols(
                 'tconst'         = col_character(),
                 'titleType'      = col_character(),
                 'primaryTitle'   = col_character(),
                 'originalTitle'  = col_character(),
                 'isAdult'        = col_integer(),
                 'startYear'      = col_integer(),
                 'endYear'        = col_integer(),
                 'runtimeMinutes' = col_double(),
                 'genres'         = col_character()
               ))
}

requete <- function(tb){
  tb %>% 
    filter(str_detect(str_to_lower(originalTitle),"superman") 
           & ! str_detect(str_to_lower(titleType), "tv")) %>% 
    select(startYear, titleType, originalTitle, 
           runtimeMinutes, genres) %>% 
    arrange(startYear, titleType)
}


bd <- dbConnect(duckdb(
  dbdir = "fichier_stock.duckdb",
  config = list(
    temp_directory = "fichier_temp.duckdb",
    threads = "4", 
    memory_limit = "4GB")
))

microbenchmark(
  "import CSV + dplyr"={
    films <- import_films_csv()
    superman <- films %>% requete()
    rm(films, superman)
    gc()
  },
  "import Parquet + dplyr"={
    films <- arrow::read_parquet("c:/olivier/imdb/films/films.parquet")
    superman <- films %>% requete()
    rm(films, superman)
    gc()
  },
  "DuckDB sur import CSV"={
    films <- import_films_csv()
    duckdb_register(bd, "tb_films", films)
    superman <- tbl(bd, "tb_films") %>% 
      requete() %>% 
      collect()
    duckdb_unregister(bd, "tb_films")
    rm(films, superman)
    gc()
  },
  "DuckDB sur import Parquet"={
    films <- arrow::read_parquet("c:/olivier/imdb/films/films.parquet")
    duckdb_register(bd, "tb_films", films)
    superman <- tbl(bd, "tb_films") %>% 
      requete() %>% 
      collect()
    duckdb_unregister(bd, "tb_films")
    rm(films, superman)
    gc()
  },
  "DuckDB direct sur CSV"={
    dbExecute(bd,
              "CREATE TABLE tb_films AS
          SELECT *
          FROM read_csv('C:/olivier/imdb/title.basics.tsv',
                        delim='\t',
                        nullstr='\\N',
                        header=true,
                        columns={
                          'tconst'         : 'VARCHAR',
                          'titleType'      : 'VARCHAR',
                          'primaryTitle'   : 'VARCHAR',
                          'originalTitle'  : 'VARCHAR',
                          'isAdult'        : 'SMALLINT',
                          'startYear'      : 'INTEGER',
                          'endYear'        : 'INTEGER',
                          'runtimeMinutes' : 'FLOAT',
                          'genres'         : 'VARCHAR'
                        }
          )")
    superman <- tbl(bd, "tb_films") %>% 
      requete() %>% 
      collect()
    rm(films, superman)
    dbExecute(bd, "DROP TABLE IF EXISTS tb_films")
    gc()
  },
  "DuckDB direct sur Parquet"={
    pqt <- arrow::open_dataset("c:/olivier/imdb/films")
    duckdb_register_arrow(bd, "tb_films", pqt)
    superman <- tbl(bd, "tb_films") %>% 
      requete() %>% 
      collect()
    duckdb_unregister_arrow(bd, "tb_films")
    dbExecute(bd, "DROP VIEW IF EXISTS tb_films")
    rm(superman, pqt)
    gc()
  },
  "Arrow direct sur Parquet"={
    pqt <- arrow::open_dataset("c:/olivier/imdb/films")
    superman <- pqt %>% 
      requete() %>% 
      collect()
    rm(superman, pqt)
    gc()
  },
  times=50
)
dbDisconnect(bd)

Unit: seconds

On voit que le constat est sans appel : les phases d’import dans R, même d’un fichier Parquet, sont coûteuses en temps (et en RAM). Faire travailler DuckDB au plus près des données source (CSV ou Parquet) est intéressant sur tous les plans. Et si votre source est un CSV, la conversion en Parquet, à n’effectuer qu’une fois, générera un temps de traitement largement amorti par la suite !

A titre de comparaison j’ai également inclus le travail par {arrow} et {dplyr} sur des données Parquet non importées, sans utiliser DuckDB. On voit que l’ajout de DuckDB accélère les traitements également dans cette configuration.

Remarque :

Volontairement je n’ai pas proposé de code {data.table} pour les solutions en “pur R”. Principalement parce qu’au-delà de la vitesse d’exécution, l’atout majeur de DuckDB reste l’absence d’import dans l’Environnement, et donc l’impact limité sur la RAM d’un serveur. Là-dessus {data.table} ne propose pas vraiment de solution satisfaisante.

sessionInfo()

R version 4.5.3 (2026-03-11 ucrt)
Platform: x86_64-w64-mingw32/x64
Running under: Windows 10 x64 (build 19045)

Matrix products: default
  LAPACK version 3.12.1

locale:
[1] LC_COLLATE=French_France.utf8  LC_CTYPE=French_France.utf8   
[3] LC_MONETARY=French_France.utf8 LC_NUMERIC=C                  
[5] LC_TIME=French_France.utf8    

time zone: Europe/Paris
tzcode source: internal

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] stringr_1.6.0      dbplyr_2.5.2       dplyr_1.2.1        nycflights13_1.0.2
[5] duckdb_1.4.4       DBI_1.3.0         

loaded via a namespace (and not attached):
 [1] bit_4.6.0         jsonlite_2.0.0    compiler_4.5.3    tidyselect_1.2.1 
 [5] blob_1.2.4        assertthat_0.2.1  arrow_23.0.1.1    yaml_2.3.12      
 [9] fastmap_1.2.0     R6_2.6.1          generics_0.1.4    knitr_1.51       
[13] htmlwidgets_1.6.4 tibble_3.3.0      pillar_1.11.0     tzdb_0.5.0       
[17] rlang_1.2.0       utf8_1.2.6        stringi_1.8.7     xfun_0.59        
[21] bit64_4.6.0-1     otel_0.2.0        cli_3.6.5         withr_3.0.3      
[25] magrittr_2.0.3    tictoc_1.2.1      digest_0.6.37     rstudioapi_0.19.0
[29] lifecycle_1.0.5   vctrs_0.7.3       evaluate_1.0.5    glue_1.8.1       
[33] rmarkdown_2.29    purrr_1.2.2       tools_4.5.3       pkgconfig_2.0.3  
[37] htmltools_0.5.8.1