suggestion fiche 21 Manipuler des données avec duckdb - ajout import données postgres duckDB

[gschone-data]

Merci pour la fiche très intéressante https://book.utilitr.org/03_Fiches_thematiques/Fiche_duckdb.html.

Je suis en train d'experimenter DuckDB dans un environnement où cohabite du postgres, du parquet et des csv, et je n'ai pas trouver sur la fiche de guide pour importer des données postgres. vous pourriez ajouter un paragraphe ainsi :

21.4.3.3 Chargement de données provenant d'une base postgres

Deux options :

1. Utilisation des données de la base postgres via duckDB :

Ici on ne créera pas d'objet persistant dans la base duckdb créé.

#creation du driver et connexion vers duckdb
drv <- duckdb::duckdb(dbdir = "<dir>",config=list('allow_unsigned_extensions'='true'))
conDB <- dbConnect(drv)

#ajout du module postgres necessaire à la connexion vers postgres
dbExecute(conDB,"load postgres;")

#ici on attache toute la base de donnée (donc tous les schemas et les tables) 
dbExecute(conDB,
          "ATTACH 'host=<dbname_address> port=<port> dbname=<dbname>' AS <dbname> (TYPE postgres);")
   
#On peut ensuite explorer comme sur un environnement postgres :
tbl(conDB,"<dbname>.<dbtable>)

2. Creation d'une table duckdb à partir d'informations collectées sur postgres :

Ici on va créer un nouvel objet sur la connexion duckDB à partir d'informations récupérées sur la connexion postgres.
En effet, on peut avoir besoin de créer directement une table dans la connexion duckdb pour disposer de tables intermédiaires ou pour stocker l'objet avec la connexion pour un usage ultérieur.

#création du driver et connexion vers duckdb
drv <- duckdb::duckdb(dbdir = "<dir>",config=list('allow_unsigned_extensions'='true'))
conDB <- dbConnect(drv)

#ajout du module postgres nécessaire à la connexion vers postgres
dbExecute(conDB,"load postgres;")

#on attache le schéma qui nous intéresse (ou toute la table si nécéssaire, cf. ci dessus) : 
dbExecute(conDB,
          "ATTACH 'host=<dbname_address> port=<port> dbname=<dbname>' AS <dbname> (TYPE postgres, SCHEMA '<schema>');")

#on crée directement la table dans l'environnement duckdb
sql<- "CREATE TABLE <db_table> AS FROM (
  SELECT <liste des champs de la base postgres>
    FROM <dbname>.<schema>.<table> #JOIN IF NEEDED ...
      WHERE ......);"

#envoi de la commande sql dans l'environnement duckdb
dbSendQuery(conDB,sql)

#la table est ensuite disponible via  la commande : 
tbl(conDB,db_table)

A noter cette méthode est 3 fois plus rapide qu'un collect() sur la base postgres suivi d'un dbWritetable sur la base duckDB.

Pour aller plus loin :
https://duckdb.org/docs/stable/extensions/postgres.html
https://duckdb.org/2022/09/30/postgres-scanner.html