Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

meonais · 23/03/2017 11:40:54

Bonjour à tous,

Je me forme petit à petit à postgresql en fonction des besoins... Donc ce n'est probablement pas la meilleure méthode pour éviter les erreurs, je l'admets.

Je souhaite faciliter certaines analyses bisannuelles sur ma base de données. Certaines données devront être mises à jour avant l'analyse. Pour cela j'aimerais faire des imports de csv via la requête sql.

J'ai trois demandes :

1 - J'ai solutionné pas mal d'erreurs au fur et à mesure de mes essais pour l'import mais là je bloque, je tourne un peu en rond entre deux erreurs, dont la dernière est la suivante :

État SQL :22P02

Sur la requête (création de table et import csv) suivante (simplifiée vu le nombre de champs...) :

DROP TABLE IF EXISTS roe."obstecoul_rmc" CASCADE ;
CREATE TABLE roe."obstecoul_rmc"
(
champ1 text,
champ2 numeric,
champ3 text,
...etc
geom geometry
) ;
SET client_encoding = 'LATIN1';
COPY roe."table"
FROM 'C:\Users\Public\Documents\chemin\donnees_entrees\table.csv'
WITH
CSV HEADER
DELIMITER ','
NULL 'NULL'
;

Quelle(s) est(sont) les erreurs envisageables ?
Je suis d'ailleurs étonnée, je pensais que le csv avait comme délimiteur ";'...

Voici quelques lignes de mon csv (import via Notepad++)

champ1,champ2,champ3,champ4,champ5,champ6,champ7,champ8,champ9,champ10,champ11,x_l93,y_l93,champ14,champ15,champ16,champ17,champ18,champ19,champ20,champ21,champ22
DEG621,0,Validé en ser,2,Existant,BAR ,,1.2,Bar en rivière,,,884734.49,6735182.71,,,,,,IND,2009-07-17 14:36:50.737,2009-07-17 14:37:02.88,52
DEG626,0,Validé en val,2,Existant,Bar,,1.2,Bar rivière,,,884471.64,6735123.20,,,,,,IND,2009-07-17 14:30:50.769,2009-07-17 14:35:37.015,52

2/ Sur la base de cette requête, comment auto incrémenter/importer l'integer (identifiant unique "gid" sous Postgresql ?) sachant que le champ1 est bien un identifiant unique pour mes objets mais il est au format text ; il vaut mieux avoir un identifiant numeric, non ?

3/ J'ai pas mal cherché et j'ai cru qu'il était possible de n'importer que quelques champs du csv, par exemple en faisant bien correspondre les noms des champs du csv (header) avec ceux de la table créée ? Mais je n'ai pas trouvé l'astuce.
Ça me serait très utile pour éviter de traiter le csv (qui a 109 colonnes dont seulement une vingtaine à conserver pour cet exemple)...

D'avance merci de votre aide et conseils !

Dernière modification par meonais (10/04/2017 16:19:07)

gleu · 23/03/2017 15:27:23

1. Merci de donner le texte de l'erreur entier et non modifié.

2. Il faut associer à cette colonne une valeur par défaut qui sera calculé par une fonction que vous devrez écrire parce que PostgreSQL ne propose qu'une fonction d'incrément (nextval) pour les séquences, et c'est dans ce cas un entier, et non pas du texte.

3. Il faut indiquer la liste des colonnes à la commande COPY, du style COPY la_table (la liste des colonnes) FROM le_fichier etc...

ruizsebastien · 23/03/2017 15:52:16

si vous mettez dans votre colonne ID un type serial ce sera un integer sinon il faudra écrire votre propre fonction.

Donnez à COPY la liste de vos colonnes. Vous pouvez ne pas lui indiquer toutes les colonnes (comme dans un insert).

meonais · 24/03/2017 11:08:41

Bonjour,

Merci de vos réponses !

Je continue donc les échanges par point :

1- @gleu : je ne comprends par la votre demande ; le texte de l'erreur est complet... je n'ai qu'un code d'erreur pour ce problème.... J'ai effectivement eu plus de détails dans le cas d'autres erreurs mais pas là ... Y a t il une manipulation à faire pour obtenir ce que souhaité ?

2- @gleu et @ruizsebastien : merci pour l'astuce, après consultation de la doc je pense avoir compris comment faire ; je testerai dès que le point 1 sera réglé (ou en parallèle)

3- @gleu et @ruizsebastien : j'avais trouvé cette technique en cherchant un peu, mais je n'arrive pas à l'appliquer. Ca m'a occasionné pas mal d'erreurs. Comme c'est la première fois que je fais un import de CSV, j'ai préféré avancer et contourner en traitant le csv et en important ensuite toutes les colonnes...
y a t il des paramètres particuliers à vérifier ? Comme le nom des champs identiques entre Postgresql et csv (casse, nombre de caractères...), le traitement des champs vides (là je pensais avoir résolu), le format du fichier et de la base ? ...

Merci encore,

dverite · 24/03/2017 14:05:55

État SQL :22P02

Sous quel logiciel le COPY est-il lancé?

J'ai pas mal cherché et j'ai cru qu'il était possible de n'importer que quelques champs du csv, par exemple en faisant bien correspondre les noms des champs du csv (header) avec ceux de la table créée ? Mais je n'ai pas trouvé l'astuce.
Ça me serait très utile pour éviter de traiter le csv (qui a 109 colonnes dont seulement une vingtaine à conserver pour cet exemple)...

Il y a 2 choses ici que le COPY de postgresql ne sait pas faire:

- interpréter les champs du header csv pour les associer avec des noms de colonne de la table. Quand on met l'option HEADER, ça veut juste dire que la 1ere ligne sera filtrée au lieu d'être considérée comme une ligne de données. Le contenu de cette 1ere ligne est ignoré.

- éliminer des colonnes du contenu. En ajoutant des noms de colonnes derrière le nom de table, on indique que la destination du COPY ne comprend pas toutes les colonnes de la table, mais ça ne change pas la source. Dans la source du COPY, toutes les colonnes sont prises en compte.

Il faut préfiltrer le fichier avant de le donner à COPY pour supprimer des colonnes, ou bien il faut utiliser qqch de plus avancé que copy, comme pgloader: http://pgloader.io/howto/csv.html

Je ne sais pas si pgloader existe prêt à l'emploi sous Windows.

meonais · 07/04/2017 10:08:45

Bonjour à tous,

Je reprends ce fil après quelques jours de pose.

Merci pour vos réponses très claires, je comprends mieux les possibilités !

Alors, j'ai repris un test, en fonction de vos remarques mais je ne suis pas plus avancée.
- Je souhaite toujours importer un csv dans une table postgresql précédemment créée et ensuite incrementer un identifiant pour l'utiliser comme clé primaire (mon identifiant unique actuel est en varchar dans le csv).
- Ensuite, utiliser les champs x et y pour spatialiser le tout (j'ai déjà des tables spatialisées dans la base, que j'importe sous QGis)

Avec la requête suivante, j'ai une autre erreur qu'il y a quelques jours, toujours pas plus documentée (sous Query de PgAdminIII)

DROP TABLE IF EXISTS ouv."obstecoul_test" CASCADE ;
CREATE TABLE roe."obstecoul_test"
(
idauto SERIAL PRIMARY KEY,
identifian    character varying(254),
statut_cod    numeric(10),
statut_nom    character varying(254)

) ;
DROP SEQUENCE IF EXISTS ma_sequence;
CREATE SEQUENCE ma_sequence INCREMENT 1 NO CYCLE;
SET client_encoding = 'LATIN1';
COPY roe."table"
FROM 'C:\Users\dupont.dupond\Documents\OUV\bilan\table.csv'
WITH
CSV HEADER
DELIMITER ','
NULL 'NULL'
;
UPDATE roe."obstecoul_test" SET idauto = nextval('ma_sequence');

********** Erreur **********

État SQL :42501

Si j'ai la bonne documentation, il s'agit de problèmes de droits d'écriture etc. ? J'ai cherché un peu mais j'avoue avoir du mal à comprendre quelles modifications à faire (entre client, serveur, postgresql, windows...)

- Quelles sont les manipulations à faire pour que les droits soient automatiques ? En effet, je prépare un petit outil pour des personnes encore moins initiées que moi, à utiliser sur d'autres PC de préférence après

- L'approche pour l'identifiant automatique est-elle la bonne ?

- Avez-vous de la doc simple pour spatialiser la couche ensuite via le SQL (je veux éviter les manipulations QGIS/PGADMIN aux prochains utilisateurs) ?

En vous remerciant ,

Dernière modification par meonais (10/04/2017 11:20:31)

jmarsac · 07/04/2017 13:55:25

Bonjour,

La définition d'idauto lors de la création de la table (pseudo-type serial et PRIMARY KEY) permet de créer automatiquement la séquence obstecoul_test_idauto_seq et définir la valeur, non nulle, par défaut au résultat de nextval('obstecoul_test_idauto_seq')
Il suffit donc de créer la table avec

    DROP TABLE  IF EXISTS  obstecoul_test  CASCADE  ;

        CREATE TABLE obstecoul_test
        (
        idauto SERIAL PRIMARY KEY,
        identifian     character varying(254),
        statut_cod     numeric(10),
        statut_nom     character varying(254)
        ) ;

puis importer les données avec COPY en précisant bien les colonnes correspondant au contenu du csv (sinon la première colonne du csv ira dans la colonne idauto générant probablement une erreur due à une incohérence de types).
idauto sera renseignée autmatiquement
Concernant l'erreur, il faut vérifier les droits accordés à l'utilisateur exécutant la requête ainsi que la valeur du search_path

dverite · 07/04/2017 14:35:26

Pour les droits d'accès, il faut être superutilisateur pour utiliser COPY avec les fichiers sur le serveur, et il faut que les fichiers soient à un endroit du disque que postgresql a le droit de lire.

Le "État SQL :42501" n'est pas suffisamment parlant mais normalement il devrait être accompagné d'un vrai message d'erreur.

Par example si je fais un COPY avec pgadmin3 sans être superutilisateur, voici ce qu'il m'affiche dans le panneau "Messages":

ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

********** Erreur **********

ERROR: must be superuser to COPY to or from a file
État SQL :42501
Astuce : Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Je ne sais pas trop comment on peut arriver à la situation où on a uniquement "État SQL :42501". Peut-être avoir client_min_messages sous le niveau ERROR mais il faut vraiment le vouloir, ou peut-être un problème de traduction du message au niveau du serveur dans la langue de Molière, mais là je m'attendrais plutôt à ce que le message sorte en anglais au lieu d'être carrément absent.

meonais · 07/04/2017 16:08:48

Bonjour,

Merci pour les corrections et compléments d'information.

Pour le message d'erreur incomplet , je ne sais quoi faire car je n'ai rien changé à l'installation de Postgresql/PgAdminIII et surtout j'ai bien toutes les textes complets lors d'autres erreurs sur d'autres requêtes...

J'ai cherché un peu et j'ai l'impression que c'est bien paramétré dans les fichiers conf :

Dans tous les fichiers Postgresql.conf que j'ai trouvé, j'ai cet élément de code pour client_min_messages

#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error

Pour les droits utilisateurs
J'ai trouvé la commande psql (\du) pour avoir les informations sur les droits de l'utilisateur et voici ce que j'obtiens :

Liste des r¶les
Nom du r¶le | Attributs
| Membre de
-------------+------------------------------------------------------------------
---------------+-----------
postgres | Superutilisateur, CrÚer un r¶le, CrÚer une base, RÚplication, Con
tournement RLS | {}

Je suis donc bien en superutilisateur...

Mais peut-être n'est-ce pas la bonne solution compte-tenu de l'utilisation envisagée par la suite :
Je suis en train de créer une opération qui devra être réalisable par une tierce personne, sur un autre ordinateur, avec des fichiers mis à jour régulièrement : l'idée est de faire enregistrer les csv toujours au même endroit de chaque ordinateur et de les nommer selon les appellations utilisée dans la requête SQL ; afin de faire les importer et lancer la requête sur l'ensemble des tables présentent dans la base postgre/postgis (tables permanentes et nouvelles.csv) . je me pose quelques questions du coup sachant que les droits d'utilisateurs peuvent bloquer sur certaines requêtes :

** si je lis bien le message d'erreur complet de 42501, il faudrait utiliser la console psql pour s'en affranchir ; le hic c'est que ça ferait une manipulation de plus pour la tierce personne pas forcément initiée à ce type d'outil (et qui aurait déjà à utiliser PgAdmin et QGis en suivant un "tutoriel" à défaut d'avoir un SIGiste sous la main...)
** j'ai trouvé GRANT en cherchant un peu ; est-ce une piste à creuser pour contourner systématiquement les blocages ?

Qu'en pensez-vous ?
Merci

dverite · 07/04/2017 17:11:34

Concernant le message d'erreur tronqué, je pense que c'est une incompatibilité entre le client_encoding à LATIN1 (nécessaire pour le COPY, j'imagine que le fichier a des accents en iso-8859-1) et le lc_messages du serveur qui doit probablement être en français utf-8.

En guise de contournement, je suggèrerais d'ajouter

SET lc_messages = 'C';

avant le COPY. Ca devrait donner un message d'erreur en anglais mais sans problème d'encodage. Il faut être superutilisateur pour faire ça mais justement vous l'êtes.

Concernant la raison du problème, étant donné que le compte est superutilisateur, il reste l'explication que le processus postgres ne pourrait pas accéder au fichier vu qu'il est dans le répertoire perso d'un utilisateur (c:\Users\etc....\)
Le plus simple est de mettre ces fichiers à importer ailleurs sur le disque, dans un répertoire avec des droits "publics" en tout cas en lecture.

dverite · 07/04/2017 17:58:26

meonais a écrit :

Mais peut-être n'est-ce pas la bonne solution compte-tenu de l'utilisation envisagée par la suite :
Je suis en train de créer une opération qui devra être réalisable par une tierce personne, sur un autre ordinateur, avec des fichiers mis à jour régulièrement : l'idée est de faire enregistrer les csv toujours au même endroit de chaque ordinateur et de les nommer selon les appellations utilisée dans la requête SQL ; afin de faire les importer et lancer la requête sur l'ensemble des tables présentent dans la base postgre/postgis (tables permanentes et nouvelles.csv) . je me pose quelques questions du coup sachant que les droits d'utilisateurs peuvent bloquer sur certaines requêtes :

Oui ça pose un problème si pgadmin3 ne tourne pas sur la même machine que le serveur postgresql.
Avec COPY le serveur ne reçoit pas le contenu du fichier, il reçoit le chemin du fichier.
Si le chemin du fichier correspond au disque local d'un autre ordinateur, il ne pourra pas l'ouvrir.

Utiliser psql et son \copy résoud ce problème là (dans ce cas c'est les données qui transitent, et non pas le chemin du fichier), et apparemment pgadmin3 n'a pas d'équivalent.

psql permet aussi d'automatiser les traitements, si les fichiers s'appellent toujours pareil il est possible d'appeler ça de manière non interactive avec

psql -f script.sql

. Mais l'environnement console parait hostile à certains utilisateurs, ça dépend des habitudes et s'ils ont plutôt une culture windows/souris ou unix/clavier.

jmarsac · 07/04/2017 18:23:18

dverite a écrit :

Mais l'environnement console parait hostile à certains utilisateurs, ça dépend des habitudes et s'ils ont plutôt une culture windows/souris ou unix/clavier.

Il est toujours possible de créer un script (batch ou powershell) sur lequel on crée un raccourci avec une icone adaptée. Dans ce cas, il faudrait que ce script :
- vérifie l'existence du fichier csv
- lance l'import avec psql
- supprime le fichier csv si l'import s'est bien déroulé afin d'éviter d'importer plusieurs fois les données

meonais · 10/04/2017 12:14:59

Bonjour,

Et merci à jmarsac et dverite pour ces éléments :

Pour l'import par SQL
- l'ajout de

 SET lc_messages = 'C';

comme le proposais dverite est parfait, j'ai les messages d'erreur en anglais
- la récupération du fichier se fait sur le dossier "Public" de C:/ et ça fonctionne également
- j'ai bien précisé les colonnes dans le COPY comme jmarsac l'a spécifié, et pas de problème non plus : importation des données et autoincrement de l'idauto
- j'ai dû modifier le delimiteur, sans pourtant avoir changé le fichier csv... En tout cas il ne me semble pas !
- j'ai pu faire une extraction (COPY TO) sans problème dans les mêmes conditions.

Pour l'import / export via psql

Si je comprends bien, et je m'excuse par avance de la demande de confirmation sur des choses qui doivent vous sembler basiques... :

- Tout ce que je produit actuellement sur mon PC à l'aide de Postgresql/postgis, PgAdmin et QGIS (ainsi qu'avec mes fichiers shape et csv initiaux) ne pourra pas être reproduit (et donc les résultats de requêtes - tables ou vues ) par une autre personne sur un autre PC ?
Même si cette autre personne installe également Postgresql/postgis, PgAdmin et QGIS, et utilise un projet de QGIS qui charge les vues ?
Il ne pourra pas y avoir d'import/export des fichiers csv si le chemin est toujours C:\Users\Public\Documents par exemple ?

- Si ce n'est effectivement pas possible, d'après est-il "facile" de créer ce fameux script avec raccourci pour importer les fichiers ?
Ce script peut-il intégrer tout le travail de traitement codé en SQL (et qui permettrait un export de tables et de vues) ?
Ce script / raccourci peut-il être installé facilement sur n'importe quel PC ?
Faut-il également (je suppose que oui) que le PC ait Postgresql/postgis (et PgAdmin?)

Merci beaucoup pour votre aide,

jmarsac · 10/04/2017 13:14:45

Bonjour,

meonais a écrit :

Pour l'import / export via psql
Si je comprends bien, et je m'excuse par avance de la demande de confirmation sur des choses qui doivent vous sembler basiques... :

- Tout ce que je produit actuellement sur mon PC à l'aide de Postgresql/postgis, PgAdmin et QGIS (ainsi qu'avec mes fichiers shape et csv initiaux) ne pourra pas être reproduit (et donc les résultats de requêtes - tables ou vues ) par une autre personne sur un autre PC ?
Même si cette autre personne installe également Postgresql/postgis, PgAdmin et QGIS, et utilise un projet de QGIS qui charge les vues ?
Il ne pourra pas y avoir d'import/export des fichiers csv si le chemin est toujours C:\Users\Public\Documents par exemple ?

Du fait que tout (serveur et client) est installé sur votre machine les choses restent confuses.
Il faut distinguer Postgresql/Postgis, le serveur de base de données et les clients qui peuvent l'exploiter (psql, PgAdmin,QGIS...). Si vous avez la possibilité d'installer Postgres sur une autre machine, vous y verrez plus clair et (à peu près) tout ce que vous pourrez faire sur votre poste sera également possible sur n'importe quel poste: un utilisateur potentiel n'a besoin que du (des) client(s) sur son poste, par exemple :

serveur : postgres/postgis (psql est forcément présent)

client1 : psql
client2 : PgAdmin et QGIS
client3: psql et QGIS
client4: PgAdmin
client5: QGIS

Si ce n'est effectivement pas possible, d'après est-il "facile" de créer ce fameux script avec raccourci pour importer les fichiers ?

oui
un exemple de fichier batch (.bat) lancement de psql (windows 64 bits):

@set PG_VERSION=9.3
@set PG_PORT=5432
@set PG_PATH=C:\Program Files (x86)\PostgreSQL\%PG_VERSION%\

"%PG_PATH%\bin\"psql.exe -p %PG_PORT% -h serveur -f monscript.sql

Ce script peut-il intégrer tout le travail de traitement codé en SQL (et qui permettrait un export de tables et de vues) ?

oui, vous pouvez lancer n'importe quel script sql

Ce script / raccourci peut-il être installé facilement sur n'importe quel PC ?

oui, en local ou sur un disque réseau

Faut-il également (je suppose que oui) que le PC ait Postgresql/postgis (et PgAdmin?)

non, seulement psql (cf. ci-dessus)

Pour simplifier le problème des droits d'accès aux fichiers, il peut être utile d'utiliser la commande \copy de psql qui est exécutée avec les droits de l'utilisateur connecté (il faut que l'emplacement des csv soit accessible en lecture (import) et/ou écriture (export) à cet utilisateur

Merci beaucoup pour votre aide

Avec plaisir

Dernière modification par jmarsac (10/04/2017 14:11:16)

jmarsac · 10/04/2017 13:43:36

Pour installer psql sur un poste, vous pouvez :
- installer PgAdmin qui inclut psql
ou bien
- copier dans un répertoire défini dans le PATH les fichiers suivants: psql.exe, libiconv-2.dll, lib-intl8.dll, libpq.dll. Sous windows vous trouverez ces fichiers dans le répertoire contenant pgadmin.exe

meonais · 10/04/2017 14:49:39

C'est génial !

Bon, je n'ai pas encore finalisé le script bat, mais j'ai démarré le fichier avec ce que vous m'avez indiqué et mes premiers apprentissages sur le web (si vous avez un très bon tuto, je prends avec plaisir).

Il me reste une incompréhension par rapport à ce .bat et les histoires de serveur/client. Pouvez-vous me confirmer ce que j'ai encore reformulé ci-dessous ?
- le serveur sera, sur toutes les machines, postgresql/postgis (même version que le mien car elle est précisée dans le fichier .bat) - et du coup le port sera également le même pour que ça puisse fonctionner
- les clients seront psql pour le traitement sql, et QGis pour l'affichage des vues géomatiques sur toutes les machines
- les fichiers à importer/exporter devront être sur toutes les machines dans le même dossier et chemin, accessible à tous les utilisateurs (par exemple C:\Users\Public\Documents\import\ et C:\Users\Public\Documents\export)
- le fichier .bat devra être lancé depuis... tout compte utilisateur des machines ou depuis le compte de l'administrateur ?
- le fichier sql devra préciser le nom des bases et des schémas pour qu'il s'exécute correctement ?
- le fichier sql devra utiliser \copy plutôt que copy ?

J'aurais une autre question concernant les fichiers .csv importés et traités (ils servent à faire quelques analyses et quelques mises à jours de tables de la bdd). Je la formule ici car elle est dans la continuité du projet, mais peut-être qu'il faudra que je la pose dans un nouveau sujet ?
J'ai plusieurs fichiers qui correspondent à des extractions régulières d'une base de données. Je cherche à actualiser ma propre base de donnée avec des traitements de ces fichiers. J'aimerai par exemple obtenir une synthèse (requête sql) de chaque fichier et regrouper les résultats en fonction de la date du fichier source :

id | date_extract | nb_condition1 | nb_condition2
1 | 2017-04 | 125 | 27
2 | 2017-01 | 100 | 29
...

Avec par exemple, deux fichiers d'extractions nommés 20170412_extract_ouv.csv et 20170122_extract_ouv.csv

-- Est-il possible de récupérer le nom du fichier (dans une variable ?) pour ensuite appliquer la requête sql ? Auriez-vous un exemple ?
-- Est-il possible d'importer plusieurs fichiers de ce type, tous enregistrés dans un seul et même dossier, mais tous avec un nom de fichier différent, pour leur appliquer ensuite la requête ? Auriez-vous un exemple ?

Merci encore de votre patience et de votre aide !

jmarsac · 10/04/2017 15:28:08

meonais a écrit :

- le serveur sera, sur toutes les machines, postgresql/postgis (même version que le mien car elle est précisée dans le fichier .bat) - et du coup le port sera également le même pour que ça puisse fonctionner

Non ! Il n'y a qu'un seul serveur auquel se connectent les différents clients (à moins que j'ai mal compris votre objectif; l'intérêt d'un SGBD est, entre autres de centraliser les données)
Voir à ce sujet Architecture Client-Serveur

les clients seront psql pour le traitement sql, et QGis pour l'affichage des vues géomatiques sur toutes les machines

oui

les fichiers à importer/exporter devront être sur toutes les machines dans le même dossier et chemin, accessible à tous les utilisateurs (par exemple C:\Users\Public\Documents\import\ et C:\Users\Public\Documents\export)

oui, chemin accessible au moins à l'utilisateur connecté et utilisant le script

le fichier .bat devra être lancé depuis... tout compte utilisateur des machines ou depuis le compte de l'administrateur ?

depuis un compte utilisateur (il faut distinguer l'utilisateur système (windows) qui a ouvert la session de l'utilisateur postgres qui se connectera à la base de données)

le fichier sql devra préciser le nom des bases et des schémas pour qu'il s'exécute correctement ?

oui, le fichier sql ou le fichier batch avec les options de la commande psql

le fichier sql devra utiliser \copy plutôt que copy ?

oui

J'aurais une autre question concernant les fichiers .csv importés et traités (ils servent à faire quelques analyses et quelques mises à jours de tables de la bdd). Je la formule ici car elle est dans la continuité du projet, mais peut-être qu'il faudra que je la pose dans un nouveau sujet ?
J'ai plusieurs fichiers qui correspondent à des extractions régulières d'une base de données. Je cherche à actualiser ma propre base de donnée avec des traitements de ces fichiers. J'aimerai par exemple obtenir une synthèse (requête sql) de chaque fichier et regrouper les résultats en fonction de la date du fichier source :
id | date_extract | nb_condition1 | nb_condition2
1 | 2017-04 | 125 | 27
2 | 2017-01 | 100 | 29
...
Avec par exemple, deux fichiers d'extractions nommés 20170412_extract_ouv.csv et 20170122_extract_ouv.csv

-- Est-il possible de récupérer le nom du fichier (dans une variable ?) pour ensuite appliquer la requête sql ? Auriez-vous un exemple ?
-- Est-il possible d'importer plusieurs fichiers de ce type, tous enregistrés dans un seul et même dossier, mais tous avec un nom de fichier différent, pour leur appliquer ensuite la requête ? Auriez-vous un exemple ?

effectivement, la plupart des échanges se sont éloignés du sujet initial du post (que vous pourriez peut-être renommer). Il serait préférable d'en créer un nouveau pour cette dernière question et la détailler.

dverite · 10/04/2017 16:46:32

meonais a écrit :

- Tout ce que je produit actuellement sur mon PC à l'aide de Postgresql/postgis, PgAdmin et QGIS (ainsi qu'avec mes fichiers shape et csv initiaux) ne pourra pas être reproduit (et donc les résultats de requêtes - tables ou vues ) par une autre personne sur un autre PC ?
Même si cette autre personne installe également Postgresql/postgis, PgAdmin et QGIS, et utilise un projet de QGIS qui charge les vues ?
Il ne pourra pas y avoir d'import/export des fichiers csv si le chemin est toujours C:\Users\Public\Documents par exemple ?

Il n'y a pas d'impossibilité mais il y a des choix d'organisation à faire en fonction des objectifs.
Dans la description ci-dessus il est difficile de cerner qui produit des données, qui doit les importer, qui les consomme, et quelles sont les données communes ou non communes entre intervenants.

En tout cas une organisation assez classique est d'avoir un seul serveur PostgreSQL (une seule "instance") pour pouvoir mettre en commun les données, et des postes clients multiples. Les postes client n'ont pas de base de données PostgreSQL mais ils ont des applications comme pgAdmin qui se connectent à la base. Ca a l'avantage qu'il y a un seul serveur à administrer, et que les données sont en commun.

meonais · 11/04/2017 09:03:27

Bonjour,

Effectivement, le lien de jmarsac m'a remémoré quelques éléments !
Et j'en profite pour vous remercier vivement tous les deux pour vos réponses toujours très adaptées et explications vraiment très didactiques et claires !

Je vais me renseigner pour savoir s'il existe déjà un serveur SGBD unique pour plusieurs utilisateurs.

Mais aujourd'hui la configuration actuelle (et donc celle que j'imaginais utiliser) est :

- une personne transmet des données par messagerie (extraction d'une autre base, sous format csv et/ou shape)

- une personne les importe et les consomme sur sa machine, grâce au script. Cette personne peut différer selon le territoire voire la période. C'est pourquoi je demandais si tout était transposable à partir du moment où on gardait tous la même configuration des logiciels sur chaque machine = chaque machine est un serveur (postgres) et ses clients (psql/QGis)...
A ce sujet j'avoue être encore un peu perdue avec les utilisateurs windows et postgresql. Mais je pense que je reviendrais en temps voulu là dessus.

- par contre j'ai accès à un dossier dit "d'échanges" sur un serveur, et j'envisageais (selon les droits) de regrouper les fichiers à importer et exportés sur ce dossier, ainsi que le script si possible... Avec une ou plusieurs copies en local tout de même au cas où bien sûr.

Merci,

meonais · 13/04/2017 16:13:15

Bonjour,

Je reviens sur ce sujet car j'ai un peu avancé mais il me reste encore des incompréhensions qui génèrent des erreurs (évidemment...)

Donc j'aurais la configuration suivante :
- un besoin d'analyser des données par différentes personnes, sur différents territoires, qui ne sont pas forcément très au fait des SIG/SGBD ==> création d'un batch
- plusieurs personnes susceptibles d'utiliser le batch, sur des machines PC Windows différentes ==> transmission du batch et des chemins + dossiers/fichiers à avoir sur sa machine (PC Windows)
- le serveur postgresql n'est pas commun ==> j'envisage de faire un tutoriel d'installation de postgresql (+PgAdmin) et d'améliorer le batch pour créer la bdd spatiale (donc PostGis) à chaque analyse ?=>? Est-ce possible ? Y-a-t-il un risque de dysfonctionnement d'un PC à l'autre ? Quel serait la base de cette ligne de code ?

Merci de vos retours/avis sur ce fonctionnement.

De plus, j'ai démarré le fichier .bat , avec l'aide des informations de jmarsac et du Net...

J'arrive à importer des shapes dans Postgres, et mon fichier sql (ensemble de requêtes d'analyses pour créer des tables et des vues) est également trouvé par le batch mais j'ai pas mal d'erreurs annoncées (a priori un problème d'encodage, sans certitude).
Pourtant, lorsque j'exécute ce fichier via PgAdminIII, tout se passe bien (import, remplacement, création tables et vues)
D'autant que via le batch j'ai tout de même quelques tables qui se créent avec ou sans enregistrement à l'intérieur (elles devraient toutes en avoir). Les erreurs semblent être souvent du même ordre. Pouvez-vous m'aider à améliorer le résultat ?

Au premier lancement du fichier sql via batch, j'ai ces erreurs :

Erreur 1 :

 C:\Users\Public\Documents\BILAN\01_Etapes_bilan>"C:\Program Files\PostgreSQL
\9.5\bin\psql.exe" -h localhost -p 5432  -d postgres -U postgres -f "C:\Users\Pu
blic\Documents\BILAN\01_Etapes_bilan\00_requetes"\synthz_region_xxxx.sql
psql:C:/Users/Public/Documents/BILAN/01_Etapes_bilan/00_requetes/synthz_regi
on_xxxx.sql:68: ERREUR:  erreur de syntaxe sur ou près de « ï»¿ »
LIGNE 1 : ï»¿
          ^

Code : la première ligne du fichier sql synthz-region_xxxx.sql est une ligne de commentaire

-- Réalisé sous/avec PostgreSQL P.5 (...)

Erreur type 2 : Elle se répète sur plusieurs des tables que je veux créer

psql:C:/Users/Public/Documents/BILAN/01_Etapes_bilan/00_requetes/synthz_regi
on_xxxx.sql:1299: ERROR:  syntax error at or near "DROP"
LIGNE 6 : DROP TABLE  IF EXISTS  schema.t00_historique_synthz  CASCADE  ;
          ^

Et est suivi directement par

 psql:C:/Users/Public/Documents/BILAN/01_Etapes_bilan/00_requetes/synthz_regi
on_xxxx.sql:1315: ERROR:  relation "t00_historique_synthz" already exists

Le code SQL :

 DROP TABLE  IF EXISTS  schema.t00_historique_synthz  CASCADE  ;
CREATE TABLE schema.t00_historique_synthz AS
(SELECT ... etc...

Erreur type 3 qui se répète aussi sur plusieurs VUES que je veux créer :

 psql:C:/Users/Public/Documents/BILAN/01_Etapes_bilan/00_requetes/synthz_regi
on_xxxx.sql:1411: ERROR:  syntax error at or near "CREATE"
LIGNE 9 : CREATE OR REPLACE VIEW schema.v01_synthz_dept AS
          ^

Cette vue est créée avec le code SQL

 CREATE OR REPLACE VIEW schema.v01_synthz_dept AS
(SELECT * FROM schema.t01_synthz_dept);

Un grand merci si vous avez un moment pour m'aider à solutionner / comprendre tout ça !

dverite · 13/04/2017 16:37:04

Pour l'erreur 1 c'est manifestement un BOM en début de fichier
Voir https://fr.wikipedia.org/wiki/Indicateu … des_octets
La solution est de paramétrer l'éditeur de texte pour ne pas mettre de BOM.

Pour les erreurs 2 et 3 il faudrait voir les lignes précédentes, par exemple quand il dit LIGNE 9, il faut regarder au moins 9 lignes au-dessus pour voir ce que lui interprète comme le début de l'instruction SQL.

meonais · 13/04/2017 17:14:19

Bonjour dverite,

Et merci pour ces indications ! C'est parfait !

- pour l'erreur 1 : j'ai relancé la requête avec un fichier sql sans BOM (Via Notepad++ > Encodage > Encoder en UTF8 (sans BOM))
- pour les erreurs types 2 et 3, j'avais oublié le caractère de fin de requête (;) sur quelques lignes...

Pour l'aspect 'logistique' ou 'configuration', qu'en pensez-vous ?

Je sais que ce n'est pas le top du tout mais bon... je voyais bien la solution d'un petit dossier qui se balade d'une personne à l'autre, avec tous les fichiers nécessaires à l'analyse souhaitée :
- QGis avec tutoriel d'installation,
- postgresql 9.5 (PgAdmin pas forcément) avec tutoriel d'installation ?,
- psql.exe (comme proposais jmarsac),
- fichier batch (lancement postgresql 9.5 sur port 5432, création/modification base de donnée, lancement des requêtes de création/modification de tables et vues = d'analyse des données),
- fichiers csv et shape,
- projet QGis,
- tutoriel pour tout ça (où enregistrer les fichiers shapes et csv sources notamment)

... mouais...hein ?

jmarsac · 13/04/2017 17:19:26

Comme vous utilisez Notepad++, vous pouvez facilement convertir votre fichier csv avec Encodage > Convertir en UTF8 (sans BOM)
[EDIT] remarque inutile, désolé ![/EDIT]

Dernière modification par jmarsac (13/04/2017 17:21:01)

jmarsac · 13/04/2017 17:30:00

Difficile de vous conseiller en connaissant mal votre organisation, vos besoins et contraintes exacts et les profils utilisateurs.
De ce que j'ai compris, peut-être pourriez-vous envisager de passer pas des sauvegardes avec pgdump d'une base complète équivalente au jeu de fichiers csv produits. Ces sauvegardes pourraient être restaurées sur un autre poste en écrasant si nécessaire la version précédente. Mais le contexte demande à être précisé avant d'aller plus loin

Dernière modification par jmarsac (14/04/2017 08:42:28)

meonais · 20/04/2017 16:47:43

Bonjour,

Merci pour l'intérêt porté et les réponses proposées !

Je suis dans une structure organisée en agences sur différentes régions.
L'idée est de proposer un outil qui puisse tourner sur n'importe quel poste, qu'il soit connecté au réseau de la structure ou non, pour réaliser un certain nombre de calculs et d'analyses géographiques. Sachant que de toutes façons il n'y a pas de serveur de base de données géographiques unique, chacun se crée son propre serveur sur son propre ordinateur au besoin... (j'espère utiliser les bons termes et me faire comprendre... )

Les utilisateurs de l'outil ne sont pas forcément adeptes de logiciels cartographiques, ou de gestionnaires de base de données, et encore moins de SQL...

C'est pourquoi je cherche à créer cet outil le plus simple possible pour les utilisateurs à venir, sans trop de manipulations et de termes ou noms de logiciels abracadabrantesques
Ils sont tous en Windows mais avec une grande disparité de version (de XP -oui oui- à 10?)

L'installation de Postgresql/PostGis + la disponibilité de psql.exe + la disponibilité de shp2pgsql.exe + le script batch + le(s) fichier(s) .sql + les fichiers csv ou shp me semble une bonne alternative.

Ainsi, je souhaitais transmettre l'outil au travers d'un dossier de fichiers [BILAN] avec une arborescence fixe contenant :
- les .exe nécessaires (Postgresql, psql, shp2pgsql, PgAdminIII pour les plus avertis - avec les bonnes versions car elles sont appelées dans le batch)
- les scripts batch et sql
- les fichiers de données permanents au format csv et/ou shp que les scripts importeront dans la base de données
- les fichiers de "données d'entrées" au format csv et/ou shp que l'utilisateur aura à remplacer et que les scripts importeront dans la base de données
- les fichiers résultats produits par les scripts

Et demander à l'utilisateur :
- d'installer Postgresql/PostGis sur leur poste (et PgAdminIII pour les plus avertis)
- de créer la base avec les paramètres indiqués dans mon script batch (ou alors je peux le faire par le script peut-être plus simple ?)
- d'enregistrer le dossier de fichiers [BILAN] au bon endroit (C:\Users\Public\Documents\) afin que les scripts puissent bien tourner
- de lancer le fichier batch

Les "données d'entrée" pour l'analyse sont :
- soit fournies par l'outil : je les ai inclus dans les dossiers de l'outil pour qu'ils soient importés (fichiers shp ou csv) à chaque lancement des analyses. J'avais aussi pensé à un dump, et je ne jette pas l'idée car je pense que j'ai finalement tout intérêt (en complément du script de création de la base ? ... je n'ai encore pas fait ce genre de code )
- soit récupérées auprès d'autres contributeurs : l'utilisateur doit les enregistrer au bon format au bon endroit et l'outil vient les récupérer pour les importer dans la base postgresql.

J'espère avoir été "limpide" dans ces explications ?...
Qu'en pensez-vous ? Est-il possible de simplifier encore ?

D'avance merci

PS : j'ai encore un gros problème de compréhension et donc de gestion des jeux de caractères, entre le serveur, le client, les fichiers bat et sql... Je suppose qu'il vaudrait mieux poser la question dans un autre post ? Encore merci !

Dernière modification par meonais (20/04/2017 16:57:41)

#1 23/03/2017 11:40:54

Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#2 23/03/2017 15:27:23

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#3 23/03/2017 15:52:16

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#4 24/03/2017 11:08:41

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#5 24/03/2017 14:05:55

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#6 07/04/2017 10:08:45

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#7 07/04/2017 13:55:25

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#8 07/04/2017 14:35:26

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#9 07/04/2017 16:08:48

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#10 07/04/2017 17:11:34

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#11 07/04/2017 17:58:26

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#12 07/04/2017 18:23:18

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#13 10/04/2017 12:14:59

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#14 10/04/2017 13:14:45

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#15 10/04/2017 13:43:36

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#16 10/04/2017 14:49:39

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#17 10/04/2017 15:28:08

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#18 10/04/2017 16:46:32

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#19 11/04/2017 09:03:27

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#20 13/04/2017 16:13:15

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#21 13/04/2017 16:37:04

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#22 13/04/2017 17:14:19

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#23 13/04/2017 17:19:26

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#24 13/04/2017 17:30:00

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

#25 20/04/2017 16:47:43

Re : Import CSV: droits, clé primaire, choix champs - sql ou psql et batch?

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux