CHILL - Reprise de données

Ce dépôt contient un script d'import qui s'applique à un canevas excel présenté au client. Le client remplit le fichier excel, puis le script insère les données dans la base de donnée.

L'opération est semi-automatique et réduit considérablement le temps dédié à l'import en structurant le format des données en entrée. Par contre il y a toujours une série de manipulations, pour préparer et insérer les données correctement.

Ces manipulations sont décrites ici.

Le client a rempli le canevas. Une relecture du fichier est toujours nécessaire afin de repérer les éventuelles irrégularités.

1. Préparer les fichiers csv

Le fichier se compose de plusieurs feuilles, chacune doit être sauvée au format csv.

Pour préparer les fichiers on va:

• nettoyer le fichier pour ne laisser en étiquette que les noms de colonnes en anglais. Il faut donc supprimer les 4 premières lignes de chaque fichier + la 6ème ligne. (- ajouter une colonne de contôle en fin de ligne, par sécurité. Par exemple une colonne 'endcol' qui contient pour chaque cellule 'endrow'.)
• ajouter les doublequote lors de la sauvegarde du csv,
• enlever tous les line breaks et caractères spéciaux.

# Exemple de remplacements exécutés sur les fichiers csv pour un import spécifique :
$ sed -e :1 -e '$q' -e "/$CR\$/b" -e 'N;s/\n//;b1' < file.2.csv > file.3.csv
$ sed -e 's#"end"#"end"\n#g' < file.3.csv > file.4.csv

# Exemple pour un autre import:
$ cat file2.csv | sed -e 'N; s#_x000D_##g; s#\n##g; s/$CR//g' | tr "\n" " " > file3.csv
$ sed -e 's#"endcol"#"endcol"\n#g; s#"endrow"#"endrow"\n#g' < file3.csv > file4.csv
$ sed -e 's#^,##g; s#^ ##g' < file4.csv > file5.csv

2. Insérer les csv dans la base de donnée

On va insérer chaque feuille csv comme table à part entière d'un nouveau schéma import. On aura:

• import.choix_personnes
• import.personnes
• import.choix_periodes
• import.periodes

Pour réaliser cet import, on peut utiliser des outils tels que pgfutter, mais celui-ci peut s'avérer capricieux selon le fichier. La meilleure méthode pour moi est de réaliser cette étape en local avec phpstorm, puis d'exporter le schéma import avec pg_dump avant de le transférer sur le serveur.

2.a Manipulations dans phpstorm

• S'il n'existe pas, créer le schéma import; s'il existe, s'assurer qu'il ne contient pas de tables ni de données.

Importer le csv dans la db

• ouvre le fichier csv > passe en onglet text > edit as table > set options:
  • cocher 'first row is header'
  • 'null value text': undefined (pas de champs null dans la table, mais un texte vide)
  • then > open table
• import to database > set options:
  • régler target/schema: import
  • et table: même nom que le csv
  • DDL: TEXT pour tous les champs
  • then > import

Exporter en sql

• créer un fichier <client>-data.sql vide
• depuis chaque table du schéma import:
  • copier le DDL de la table dans le fichier (s'assurer d'ajouter le préfixe import. sur chaque requête)
  • export data > extractor: SQL-insert-multirow > copy to clipboard
• coller les données dans <client>-data.sql

2.b Avec des fonctions Postgresql

On peut aussi utiliser la fonction \copy de psql pour charger un fichier csv dans les tables temporaires du schéma "import".

Tout d'abord, les tables du schéma "import" doivent être préparées avec le script prepare-import.sql.

Puis on importe les 4 fichiers csv:

psql chill-import
chill-import=# \copy "import".choix_personnes FROM 'choix_personnes.csv' DELIMITER ',' CSV HEADER
chill-import=# \copy "import".personnes FROM 'personnes.csv' DELIMITER ',' CSV HEADER
chill-import=# \copy "import".choix_periodes FROM 'choix_periodes.csv' DELIMITER ',' CSV HEADER
chill-import=# \copy "import".periodes FROM 'periodes.csv' DELIMITER ',' CSV HEADER

Enfin, on exporte la base de données en sql:

pg_dump chill-import --no-owner > <client>-data.sql

3. Import du schéma 'import' sur le serveur (safran)

• transférer le fichier <client>-data.sql sur le serveur (avec scp):

$ scp cyclo-data.sql debian@safran:~/data/tmp/

• faire une sauvegarde de la base sur laquelle on va réaliser l'insertion

debian@safran:~/bin$ bash backup_now_db.sh 5436 cycloprod
debian@safran:~/bin$ ls -l dump/ | tail -1
-rw-r--r-- 1 postgres postgres 234954230 Mar 15 10:40 20230315-104003_cycloprod.sql

ou bien simplement:

sudo su postgres
cd
pg_dump -p 5436 laplateformereunionprod > laplateformereunionprod.sql

• importer le fichier sql sur la base cible: $ sudo su postgres -c 'psql -p5436'

postgres=# \c cycloprod
You are now connected to database "cycloprod" as user "postgres".

cycloprod=# \dt import.*
Did not find any relation named "import.*".

cycloprod=# CREATE SCHEMA import;

-- insertion
cycloprod=# \i '/home/debian/data/tmp/cyclo-data.sql'

-- vérifier que le schéma import est en place
cycloprod=# \dt import.*
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 import | choix_periodes  | table | postgres
 import | choix_personnes | table | postgres
 import | periodes        | table | postgres
 import | personnes       | table | postgres
(4 rows)

ou bien simplement:

sudo su postgres
psql -p 5436 laplateformereunionprod < /tmp/chill-import-lpreunion.sql

4. Exécution du script de migration

Se fait dans la console postgresql, en tant que user postgres, en étant connecté à la base de donnée cible.

On joue pas-à-pas les blocs de la section 'Up' du script sql/import.sql

Tips

• Dans phpstorm, si on veut renommer le schéma pour ne pas tout mélanger, il vaut mieux faire 'Modify schema', car 'Rename' va faire des remplacements partout