Étape I : Charger les données

Dans les différentes équipes de production à l’Insee, on travaille à partir d’une base de données PostgreSQL ou bien à partir d’un fichier Parquet.

Depuis une base PostgreSQL, on convertit la table de travail en fichier Parquet (un format de fichier compact, colonne par colonne, fait pour l’analyse de données), puis on importe ce fichier pour le retraiter afin de produire les statistiques souhaitées.

Ce fichier Parquet peut être stocké à deux endroits différents, selon votre environnement de travail :

Le code diffère un peu selon le cas : les sections ci-dessous sont donc découpées en deux onglets, un par type de stockage.

Ce tuto traite aussi du cas où l’utilisateur travaille directement depuis un fichier parquet. Dans ce cas, il faut tout de même faire le préalable de configuration ci-dessous (pour S3 uniquement, un répertoire classique ne demande aucune configuration), puis aller directement à la partie B (la partie A, spécifique à PostgreSQL, peut être ignorée).

Toutes les commandes DuckDB peuvent être lancées depuis R via DBI::dbExecute() (pour une commande sans résultat à afficher) ou DBI::dbGetQuery() (pour récupérer un résultat sous forme de data.frame).

Préalable : configurer l’accès au stockage

Cette configuration sert pour les deux usages ci-dessous : écrire un Parquet sur S3, et en importer un.

DuckDB s’appuie sur l’extension httpfs pour accéder à des fichiers stockés sur S3. On commence par l’installer :

DBI::dbExecute(con, "INSTALL httpfs; LOAD httpfs;")
NoteGestion des variables d’environnement S3

Une variable d’environnement est une information stockée en dehors de votre script (dans la session R, le conteneur, le serveur…), accessible depuis le code avec Sys.getenv("NOM_DE_LA_VARIABLE"). C’est une manière courante de mettre à disposition des identifiants sans avoir à les écrire en clair dans le script. Par exemple, les variables d’authentification et les jetons personnels associés au compte gitlab ou les variables d’accès au stocage S3.

Sur SSP Cloud ou LS3, les identifiants d’accès au S3 (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_SESSION_TOKEN) sont déjà disponibles dans des variables d’environnement : un secret de type credential_chain demande à DuckDB d’aller les chercher lui-même, sans les écrire en clair dans le script.

ImportantLe paramétrage des accès au S3 via DuckDB

credential_chain ne récupère automatiquement que les identifiants. Il ne connaît pas l’adresse du stockage S3 de SSP Cloud (ce n’est pas un serveur Amazon standard) : il faut donc lui préciser explicitement l’ENDPOINT, sans quoi DuckDB irait chercher sur les serveurs Amazon par défaut. On en profite pour indiquer aussi la région et le style d’URL.

On crée le secret pour accéder aux données stockée dans le S3 :

install.packages("glue")

library(glue)

DBI::dbExecute(con, glue::glue("
  CREATE OR REPLACE SECRET secret_s3 (
    TYPE s3,
    PROVIDER credential_chain,
    ENDPOINT '{Sys.getenv(\"AWS_S3_ENDPOINT\")}',
    REGION '{Sys.getenv(\"AWS_DEFAULT_REGION\")}',
    URL_STYLE 'path'
  );
"))

Rien à configurer ! DuckDB lit et écrit directement sur le système de fichiers (disque local, répertoire réseau monté sur votre poste…) sans extension ni identifiants : il suffit d’utiliser un chemin classique, par exemple X:/mon_repertoire/mon_fichier.parquet.

Astuce

On préfère écrire les chemins avec des slashs (/), même sous Windows (X:/mon_repertoire) : ça fonctionne partout, contrairement à l’antislash (\) qui peut être mal interprété dans une chaîne de caractères.

ImportantUtilisation de glue

Le package glue sert à insérer directement des variables R dans une chaîne de caractères, entre accolades {}, par exemple glue::glue("ENDPOINT '{Sys.getenv(\"AWS_S3_ENDPOINT\")}'"). C’est plus lisible qu’un sprintf(), où il faut faire correspondre chaque %s à l’ordre des arguments ou qu’un paste().

A. De PostgreSQL vers un fichier Parquet

1. Charger l’extension et se connecter à PostgreSQL

DuckDB peut se connecter directement à une base PostgreSQL grâce à l’extension postgres, et la voir comme si ses tables étaient des tables DuckDB.

DBI::dbExecute(con, "INSTALL postgres; LOAD postgres;")

Comme pour S3, on protège les identifiants dans un secret plutôt que de construire une chaîne de connexion en clair. On peut aller plus loin en demandant le nom d’utilisateur et le mot de passe interactivement, plutôt que de les écrire dans le script :

  • rstudioapi::showPrompt() pour le nom d’utilisateur (saisie visible),
  • rstudioapi::askForPassword() pour le mot de passe (saisie masquée). Ainsi, rien n’est jamais écrit dans le script, une variable d’environnement, ou l’historique des commandes.
DBI::dbExecute(con, glue::glue("
  CREATE OR REPLACE SECRET secret_pg (
    TYPE postgres,
    HOST 'mon-serveur-postgres',
    PORT 5432,
    DATABASE 'ma_base',
    USER '{rstudioapi::showPrompt(\"Utilisateur\", \"Nom d'utilisateur PostgreSQL :\")}',
    PASSWORD '{rstudioapi::askForPassword(\"Mot de passe PostgreSQL :\")}'
  );
  ATTACH '' AS pg (TYPE postgres, SECRET secret_pg, READ_ONLY);
"))

pg est le nom donné à la base attachée (après AS) : c’est un alias, libre à vous de le choisir. C’est ce nom qui sert ensuite de préfixe pour accéder à ses tables (pg.ma_table), comme on le ferait avec un schéma.

NoteBonnes pratiques de connexion à une base de données

READ_ONLY est recommandé dès qu’on fait uniquement de l’analyse : cela évite de modifier la base source par erreur.

Personnalisez toujours le message des invites (showPrompt(), askForPassword()) : par défaut, elles affichent un texte générique identique, et on ne sait plus laquelle demande quoi si on en enchaîne plusieurs.

Ces deux fonctions n’ouvrent une invite que dans une session interactive (RStudio…). Pour un script exécuté automatiquement, sans interaction (batch, tâche planifiée), remplacez-les par des valeurs lues depuis des variables d’environnement, par exemple Sys.getenv("PG_USER") et Sys.getenv("PG_PASSWORD").

2. Vérifier la table source

Une fois attachée, la base PostgreSQL s’utilise comme n’importe quelle table DuckDB, préfixée par le nom donné (pg) :

DBI::dbGetQuery(con, "SELECT * FROM pg.ma_table LIMIT 5")

3. Exporter la table en Parquet

DBI::dbExecute(con, "
  COPY pg.ma_table
  TO 's3://mon-bucket/mon-etude/ma_table.parquet'
  (FORMAT parquet , OVERWRITE_OR_IGNORE)
")

ou, pour extraire directement une table issue d’une requête

DBI::dbExecute(con, "
  COPY (
    SELECT *
    FROM pg.ma_table
    )
  TO 's3://mon-bucket/mon-etude/ma_table.parquet'
  (FORMAT parquet , OVERWRITE_OR_IGNORE)
")
DBI::dbExecute(con, "
  COPY pg.ma_table
  TO 'X:/mon_repertoire/ma_table.parquet'
  (FORMAT parquet, OVERWRITE_OR_IGNORE)
")

ou, pour extraire directement une table issue d’une requête

DBI::dbExecute(con, "
  COPY (
    SELECT *
    FROM pg.ma_table
    )
  TO 'X:/mon_repertoire/ma_table.parquet'
  (FORMAT parquet , OVERWRITE_OR_IGNORE)
")

Ce fichier Parquet est désormais disponible, prêt à être réutilisé (par vous ou par d’autres) sans repasser par PostgreSQL.

4. Fermer l’accès à PostgreSQL

Une fois l’extraction terminée, on n’a plus besoin de PostgreSQL pour la suite du pipeline (manipulation, export). On détache la base avec DETACH :

DBI::dbExecute(con, "DETACH pg")
Astuce

DETACH ferme la connexion à PostgreSQL, contrairement à DBI::dbDisconnect(con) qui fermerait toute la session DuckDB (y compris les données déjà chargées). Le faire dès que possible libère une connexion sur le serveur PostgreSQL, ce qui compte s’il a un nombre de connexions limité.

B. Importer un Parquet déjà stocké

Si le fichier existe déjà (déposé par vous ou par quelqu’un d’autre), il suffit de le lire, à condition que l’accès au stockage ait été configuré comme ci-dessus (uniquement nécessaire pour S3).

Astuce

Tant que le nom du fichier se termine par .parquet, DuckDB détecte automatiquement le format : pas besoin d’appeler read_parquet(), un simple chemin entre guillemets dans le FROM suffit (vérifié ci-dessous). Ce qui compte, c’est que les fichiers puissent être capturés par un motif (*, ou ** pour inclure les sous-répertoires) — peu importe qu’ils soient dans le même dossier ou non. read_parquet() reste nécessaire seulement pour une extension différente (ex. .parq), ou pour cibler une liste précise de fichiers qui ne suivent aucun motif commun, comme read_parquet(['a.parquet', 'dossier2/b.parquet']).

donnees <- DBI::dbGetQuery(con, "
  SELECT *
  FROM 's3://mon-bucket/mon-etude/ma_table.parquet'
")

On peut aussi le transformer en vue DuckDB, pratique pour le réutiliser plusieurs fois dans l’étape suivante sans le charger en mémoire R :

DBI::dbExecute(con, "
  CREATE OR REPLACE VIEW ma_table AS
  SELECT * FROM 's3://mon-bucket/mon-etude/ma_table.parquet'
")
donnees <- DBI::dbGetQuery(con, "
  SELECT *
  FROM 'X:/mon_repertoire/ma_table.parquet'
")

On peut aussi le transformer en vue DuckDB, pratique pour le réutiliser plusieurs fois dans l’étape suivante sans le charger en mémoire R :

DBI::dbExecute(con, "
  CREATE OR REPLACE VIEW ma_table AS
  SELECT * FROM 'X:/mon_repertoire/ma_table.parquet'
")
Important

L’utilisation des VIEW avec duckDb s’inscrit dans une utilisation plus poussée de duckDB, en vue d’optimiser les ressources utilisées. Pour un utilisateur novice, le premier chargement suffit

AstuceImport de plusieurs fichiers parquet

Pour lire plusieurs fichiers Parquet d’un coup (par exemple un export découpé en plusieurs morceaux), on utilise un motif avec *, que ce soit sur S3 ou dans un répertoire, toujours sans read_parquet() :

DBI::dbGetQuery(con, "SELECT * FROM 's3://mon-bucket/mon-etude/*.parquet'")
DBI::dbGetQuery(con, "SELECT * FROM 'X:/mon_repertoire/*.parquet'")

Avec ** à la place de *, le motif devient récursif et capte aussi les fichiers dans les sous-répertoires :

DBI::dbGetQuery(con, "SELECT * FROM 'X:/mon_repertoire/**/*.parquet'", hive_partitioning = true)
AstucePartitionnement des fichiers Parquet

Les variables de partitionnement ne figurent pas dans les fichiers Parquet : elles sont encodées dans les chemins, sous la forme variable=valeur. L’option hive_partitioning = true demande à DuckDB de les interpréter, ce qui les restitue sous forme de colonnes et lui évite d’ouvrir les fichiers exclus par les filtres de la requête.

Sources

Retour au sommet