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 : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Chargement de fichiers plats (csv etc.) dans PostgreSQL

Message par Phil » mer. 5 sept. 2018 18:17

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 : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Chargement de fichiers plats (csv etc.) dans PostgreSQL

Message par Phil » lun. 10 sept. 2018 14:52

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 : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Chargement de fichiers plats (csv etc.) dans PostgreSQL

Message par Phil » lun. 10 sept. 2018 18:41

Merci à Guillaume pour sa question :

Actuellement, pour monter une table externe sous Oracle, je n’ai pas besoin de me connecter sur le serveur pour envoyer les commandes. Je me connecte en JDBC et je lance des commandes SQL.
A ton avis, est-ce qu’on pourrait lancer pgLoader par des commades SQL en PostgreSQL (en gros par JDBC), histoire de ne pas avoir à se connecter sur le serveur de base de données en ssh.


Réponse :

Il serait possible d'appeler pgloader comme toute autre commande depuis une fonction écrite dans un langage untrusted comme plpythonu.
Depuis PostgreSQL 10, il serait aussi possible d'utiliser une solution plus originale : créer une table étrangère avec program au lieu de filename et en fait appeler pgloader.

Exemple :

Code : Tout sélectionner

select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 ligne)

CREATE FOREIGN TABLE IF NOT EXISTS pwd (
           resultat text
)
SERVER fichiers
OPTIONS ( program 'pgloader /var/lib/postgresql/password.load' );

-- password.load
LOAD CSV
     FROM /var/lib/postgresql/passwords.txt
     INTO postgresql://NOM_SUPERUSER:MOT_DE_PASSE@SERVEUR:PORT/BASE?SCHEMA.password(id, number)
     WITH truncate,
          drop indexes,
          batch size = 1GB,
          batch rows = 1000000,
          skip header = 0,
          fields terminated by ':'

       SET work_mem to '128MB',
          standard_conforming_strings to 'on'

       AFTER LOAD DO
          $$ create index if not exists password_h1 on password using hash(id);$$

;

-- la prochaine version de pgloader ne nécessitera plus de fournir le mot de passe, c'est déjà développé et documenté mais encore implémenté dans les binaires du pgdg au 10/09/2018
Ici, j’ai utilisé un fichier .load mais il est aussi possible de passer tout le programme en ligne de commande.
Ensuite, appeler la table étrangère pwd avec « select » ou « table » déclenche le chargement.
Ça donne ça avec 10 lignes d'un fichier passwords (format TEXTE :ENTIER)

Code : Tout sélectionner


table pwd;
                                 resultat
--------------------------------------------------------------------------
2018-09-07T16:39:28.093000Z LOG report summary reset
              table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
                   fetch          0          0                     0.027s
-----------------------  ---------  ---------  ---------  --------------
   "postgres"."password"          0         10     0.4 kB          0.127s
-----------------------  ---------  ---------  ---------  --------------
         Files Processed          0          1                     0.069s
COPY Threads Completion          0          2                     0.127s
  Index Build Completion          0          0                     0.056s
          Create Indexes          0          1                     0.009s
             Constraints          0          0                     0.000s
              after load          0          1                     0.040s
-----------------------  ---------  ---------  ---------  --------------
       Total import time          ✓         10     0.4 kB          0.301s
(15 lignes)
Les lignes ont été chargées dans la table password qui est une table permanente unlogged ou non :

Code : Tout sélectionner

select * from password;

                    id                    | number
------------------------------------------+--------
BC5B82C7340EEB287A40292AB57B5818A536A146 |      1
F794C91F77BCD18397D4907A653C77ECE2561D81 |      1
4429FCF40F18C3D8AC33946756EB8C1CB675908E |      1
BA23DE9AA62AB815FEE0D3C1B4624B72620DCBC1 |      1
DC7358FE0884BFB40BC643393E6F01BFADB498CF |      1
966D4A2F5C7A761B30DB8AB710F0AA817358C9C2 |      1
C55E92CA9AEA06B4C38CA00E2B669BB7D3AB36A9 |      1
5A65B9D845FF03A656B9CB45C8743709691457C1 |      1
83F04F12342AAD465E02FACA8FDAB4F0D537B153 |      1
E7B205A82A884FD63853C2FE498FBFBF8BCA3E49 |      1
(10 lignes)
Si je mets une erreur ça donne ça :

Code : Tout sélectionner

table pwd;
                                                                        resultat
--------------------------------------------------------------------------------------------------------------------------------------------------------
2018-09-07T16:42:10.862000Z ERROR PostgreSQL [""postgres"."password""] Database error 22P02: syntaxe en entre invalide pour l'entier :  PAS_UN_NOMBRE
CONTEXT: COPY password, line 11, column number: "PAS_UN_NOMBRE"
2018-09-07T16:42:11.023000Z LOG report summary reset
              table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
                   fetch          0          0                     0.018s
-----------------------  ---------  ---------  ---------  --------------
   "postgres"."password"          1         10     0.5 kB          0.130s
-----------------------  ---------  ---------  ---------  --------------
         Files Processed          0          1                     0.070s
COPY Threads Completion          0          2                     0.131s
  Index Build Completion          0          0                     0.055s
          Create Indexes          0          1                     0.009s
             Constraints          0          0                     0.000s
              after load          0          1                     0.038s
-----------------------  ---------  ---------  ---------  --------------
       Total import time          1         10     0.5 kB          0.303s
(17 lignes)
Il est ensuite possible de déclarer le .bad en table étrangère etc.
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Chargement de fichiers plats (csv etc.) dans PostgreSQL

Message par Phil » mer. 19 sept. 2018 21:08

Merci à Guillaume pour sa question :

OK pour le principe mais la sécurité ? Est-il possible de créer dynamiquement les tables étrangères sans être SUPERUSER ? Avec Oracle on crée un objet DIRECTORY puis on donne uniquement READ et/ou WRITE à l'utilisateur faisant le chargement. Comment faire avec Postgres ?


Réponse :

PostgreSQL n'a pas l'équivalent des objets DIRECTORY d'Oracle Database.
Il faut normalement être superuser pour créer des tables étrangères via l'extension file_fdw. Ensuite, les privilèges select peuvent être donnés à des utilisateurs non superuser. C'est la manière la plus simple de faire.

S'il faut vraiment pouvoir créer ces tables sans être superuser, il existe heureusement des solutions.
PostgreSQL 11 crée de nouveaux rôles par défaut permettant de ne pas être superuser, ça peut être utile mais ça ne résout pas complètement le problème car ils sont malgré tout très puissants.
Le conseil est de créer une fonction ou une procédure pl/pgsql appartenant à un superuser ou à un utilisateur ayant le rôle pg_read_server_files avec PostgreSQL 11.
Cette fonction doit avoir l'attribut SECURITY DEFINER. Elle sera exécutée avec les droits de son propriétaire et non avec les droits de l'utilisateur qui l'exécute.
Cette fonction doit donc être très restrictive : elle doit seulement être capable de créer des tables étrangères dans un dossier particulier. Attention aux injections dans la constitution du DDL de la table (utiliser format etc.) Il est conseillé de la faire valider par un expert sécurité.
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Chargement de fichiers plats (csv etc.) dans PostgreSQL

Message par Phil » lun. 24 sept. 2018 14:38

Merci à un développeur anonyme pour sa question :

"On peut avoir un exemple ?"

Réponse :

OK... C'est fait rapidement et fourni sans garantie, il faudrait faire valider tout ça avant de l'utiliser en production.

Code : Tout sélectionner

-- fichier texte devant être chargé
cat /tmp/w/toto
1,A
2,B
3,C

-- connecte postgres 
create table extensions.directory(dossier text, utilisateur text);
CREATE TABLE

insert into extensions.directory(dossier, utilisateur) values('/tmp/w/', 'lambda');
INSERT 0 1

create or replace function extensions.cree_foreign_table(nom_table text, noms_colonnes text[], types_colonnes text[], fichier text, format text) returns void 
language plpgsql SECURITY DEFINER
as $$
declare
chemin text;
ddl_drop_table text;
ddl_cree_table text;
nom_colonne text;
type_colonne text;
longueur_noms_colonnes integer;
longueur_types_colonnes integer;
grant_table text;
grantee text;
begin
if fichier !~* '^[a-z0-9.]*$' or fichier like '%..%' then
	raise exception 'Nom du fichier incorrect';
end if;
select dossier from extensions.directory where session_user = utilisateur into chemin;
if chemin is null then
	raise exception 'Aucune directory pour %', session_user;
end if;
ddl_drop_table := format('DROP FOREIGN TABLE IF EXISTS %I.%I', session_user, nom_table);
ddl_cree_table := format('CREATE FOREIGN TABLE %I.%I (', session_user, nom_table);
longueur_noms_colonnes := array_length(noms_colonnes, 1);
longueur_types_colonnes := array_length(types_colonnes, 1);
if longueur_noms_colonnes != longueur_types_colonnes then
        	raise exception 'Les tableaux des noms de colonnes et des types de colonnes doivent etre de meme longueur';
end if;
nom_colonne := noms_colonnes[1];   
type_colonne := types_colonnes[1]; 		
FOR i in 2..longueur_noms_colonnes
   LOOP 
        if type_colonne in ('text', 'numeric', 'timestamp') then
   	ddl_cree_table := ddl_cree_table || format('%I %s, ', nom_colonne, type_colonne) ;   
        else
        	raise exception 'Types de donnees autorises : text, numeric, timestamp';
        end if;	
        nom_colonne := noms_colonnes[i];   
        type_colonne := types_colonnes[i];           	
   END LOOP;
if type_colonne in ('text', 'numeric', 'timestamp') then
   	ddl_cree_table := ddl_cree_table || format('%I %s)', nom_colonne, type_colonne) ;   
else
        	raise exception 'Types de donnees autorises : text, numeric, timestamp';
end if;	   
ddl_cree_table := ddl_cree_table || format('SERVER fichiers OPTIONS ( filename %L, format %L )', chemin || fichier, format);
grant_table := format('grant select on table %I.%I to %I', session_user, nom_table, session_user);
execute(ddl_drop_table);
execute(ddl_cree_table);
execute(grant_table);
END
$$;

grant execute on function extensions.cree_foreign_table to lambda;
GRANT

COMMIT;

-- connecte utilisateur lambda
select cree_foreign_table('titi', ARRAY['c1','c2'], ARRAY['numeric','text'], 'toto', 'csv');
 cree_foreign_table
--------------------

(1 ligne)


select * from titi;
 c1 | c2
----+----
  1 | A
  2 | B
  3 | C
(3 lignes)
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Chargement de fichiers plats (csv etc.) dans PostgreSQL

Message par Phil » lun. 7 janv. 2019 11:42

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 : 217
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 » lun. 21 janv. 2019 10:29

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