Chargement de fichiers plats (csv etc.) dans PostgreSQL

La norme SQL évolue : tirez le meilleur d'un SGBD qui la respecte en écrivant du SQL moderne avec en complément PL/pgSQL, php, java etc.
Répondre
Phil
Administrateur du site
Messages : 291
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Chargement de fichiers plats (csv etc.) dans PostgreSQL

Message par Phil »

Merci à Guillaume pour ses questions :

J’aurais quelques petites question sur l’équivalent PostgreSQL des tables externes d’Oracle.
- Faut-il bien utiliser les foreign data ? (instruction create foreign table… ?)
Dans les exemples que j’ai pu voir, ils font systématiquement : create extension file_fdw et create server ….
- Est-ce que je dois le faire aussi pour chaque table externe que je crée ? ou est-ce un extension à installer une seule fois par schéma (ou base) ?
Oracle gère un fichier « bad » pour les lignes rejetées et un fichier « log » pour en connaitre les raisons.
- Y a-t-il un équivalent sous postgreSQL ou est-ce que c’est en mode « tout ou rien » ?



Réponse :
L'équivalent direct PostgreSQL de la table externe Oracle de type fichier plat est bien la table étrangère (standard SQL/MED) s'appuyant sur l'extension file_fdw.
Les créations de l'extension (create extension file_fdw) et du serveur (create server) sont des opérations à réaliser une fois par base. Ensuite, il est possible de créer des tables étrangères (create foreign table) et de donner des droits de lecture sur ces tables aux utilisateurs classiques.
L'extension file_fdw ne gère pas directement les lignes invalides par l'intermédiaire d'une badfile comme Oracle Database. Depuis PostgreSQL 10, la table peut toutefois être créée en s'appuyant sur la sortie standard d'un programme plutôt que directement sur un fichier. Il serait donc théoriquement possible d'éliminer à la volée les enregistrements non conformes et de les stocker dans un .bad.
Plus simplement, il est possible de charger les fichiers plats via pgloader. C'est un outil actuellement maintenu par Dimitri Fontaine. Il est très complet, présent dans le repository pgdg et intègre une gestion des lignes rejetées.
Cdlt. Phil - pgphil.ovh
Phil
Administrateur du site
Messages : 291
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Chargement de fichiers plats (csv etc.) dans PostgreSQL

Message par Phil »

A noter : COPY est BEAUCOUP plus rapide que pgloader.
Si le fichier en entrée ne comporte aucune erreur, il est préférable pour avoir les meilleures performances de travailler avec COPY.
Si le fichier en entrée comporte (quasi) systématiquement un faible pourcentage d'erreurs, il est possible de travailler avec pgloader.
Si le fichier en entrée comporte rarement des erreurs, il peut être intéressant de travailler avec COPY puis de se retourner vers pgloader en cas d'exception.
Si le fichier en entrée comporte (quasi) systématiquement un grand pourcentage d'erreurs, il est préférable de traiter ces erreurs différemment plutôt que de travailler avec pgloader. Tout dépend alors de la nature des erreurs à traiter.
Cdlt. Phil - pgphil.ovh
Phil
Administrateur du site
Messages : 291
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Chargement de fichiers plats (csv etc.) dans PostgreSQL

Message par Phil »

Sur le même thème, un article de Daniel Vérité : https://blog-postgresql.verite.pro/2018 ... facts.html

Sinon, une solution basique est de charger systématiquement le fichier en tant que TEXT (1 ligne => 1 champ text) dans une table puis de faire le travail de répartition et de contrôle (fonctions split_part etc.) en chargeant vers une table finale.

E.g en java :

Code : Tout sélectionner

Connection conn = DriverManager.getConnection("jdbc:postgresql://xxxxxx:xxxx/xxxx", "xxxx", "xxxx");
long rowsInserted = new CopyManager((BaseConnection) conn).copyIn("COPY table_intermediaire FROM STDIN WITH (format text, delimiter E'\001')", new BufferedReader(new FileReader("/xxx/xxx.txt")));

insert into table_finale
SELECT split_part(xxxx, ';', 1) as xxxx, split_part(xxxx, ';', 2) as xx, split_part(data, ';', 3) as xx, split_part(data, ';', 4) as xx 
...
from table_intermediaire;
Cela double potentiellement la volumétrie nécessaire mais cela permet de tout gérer en SQL, d'effectuer des contrôles supplémentaires etc. A noter que les tables intermédiaires peuvent être temporaires ou encore unlogged pour éviter la journalisation.
Cdlt. Phil - pgphil.ovh
Phil
Administrateur du site
Messages : 291
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Filtrage des données insérées depuis un fichier plat par COPY

Message par Phil »

PostgreSQL 12, en développement au 21/01/2019 et qui sortira avant fin 2019, ajoute une clause de restriction WHERE à la commande COPY .. FROM. Cela permet d'écarter certaines lignes lors du chargement.

Démonstration :

Code : Tout sélectionner

select version();
                                                                       version
-----------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12devel (Debian 12~~devel~20190119.2358-1~480.git31f3817.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.2.0-14) 8.2.0, 64-bit
(1 ligne)


cat /tmp/toto
2017,a
2018,b
2019,c
2020,d
2017,e

create table toto(exercice smallint, operation text);
CREATE TABLE

copy postgres.toto from '/tmp/toto' (format csv, delimiter ',') where exercice >= 2018;
COPY 3

select * from toto;
 exercice | operation
----------+-----------
     2018 | b
     2019 | c
     2020 | d
(3 lignes)
Cdlt. Phil - pgphil.ovh
Répondre