277 lines
10 KiB
Markdown
277 lines
10 KiB
Markdown
Projet INSEE -- base de données avancée
|
||
---------------------------------------
|
||
|
||
Ce projet est disponible sur notre serveur git [à cette
|
||
adresse](https://git.epha.se/ephase/projet_INSEE). Le projet va continuer à
|
||
vivre après le rendu. En effet nos obligations professionnelles[^note] ont joué
|
||
contre nous et on a manqué de temps malgrès notre travail acahné pour produire
|
||
des élements de qualité.
|
||
|
||
[^note]:notamment une semaine d'astreinte ou il y a eu des interventions 3 nuits
|
||
de rang
|
||
|
||
## Installation
|
||
|
||
Il est vivement conseiller de faire fonctionner ce projet dans un
|
||
**environnement virtuel python** :
|
||
|
||
1. cloner le dépôt de code suivant:
|
||
```
|
||
git clone https://git.epha.se/ephase/projet_INSEE
|
||
```
|
||
2. créer l'environnement virtuel:
|
||
```
|
||
cd projet_INSEE
|
||
python -m venv .venv
|
||
source .venv/bin/activate
|
||
```
|
||
3. intaller les prérequis:
|
||
```
|
||
pip install -r requirements.txt
|
||
```
|
||
|
||
L'installation est maintenant opérationnelle. Il faut penser à activer
|
||
l'environnement virtuel à chaque nouvelle ouverture d'un terminal.
|
||
|
||
### ATTENTION!
|
||
|
||
L'ensemble des script présent ici nécessitent **IMPÉRATIVEMENT** Python3. Il est
|
||
parfois nécessaire d'adapter les commandes ci-dessus en fonction de votre
|
||
configuration.
|
||
|
||
Au CREMI par exemple, la création de l'environnement virtuel doit se faire aevc
|
||
la commande suivante:
|
||
|
||
```
|
||
python3 -m venv .venv
|
||
```
|
||
|
||
## Les éléments du projet
|
||
|
||
L'ensemble des scripts présents dans ce dépôt permettent l'affichage
|
||
d'informations supplémentaires via des paramètres de la ligne de commandes:
|
||
|
||
* *messages d'informations* via l'option -V. Ces informations renseignent
|
||
l'utilisateurs sur les temps d'exécutions des différentes parties de chacun
|
||
des scripts.
|
||
* *message de debug* via l'option `--debug`. Affiche des iunformations
|
||
détaillées sur les actions et résultats des scripts.
|
||
|
||
## Consolider les fichiers bruts
|
||
|
||
C'est le script `csvprocess.py` qui se charge de l'analyse des fichiers `csv`
|
||
bruts et de la génération des fichiers `csv` consolidés.
|
||
|
||
Ce script accepte plusieurs arguments qu'il est possible de visualiser avec
|
||
l'aide en ligne intégrée:
|
||
|
||
```sh
|
||
./csvprocess -help
|
||
```
|
||
|
||
## Créer la base de donnée
|
||
|
||
C'est le script `createdatabase.py` qui se charge de la création de la base de
|
||
données et de l'importation des fichiers `csv` consolidés créés par le précédent
|
||
script.
|
||
|
||
|
||
La création des éléments se fait dans un nouveau schéma, il se nomme par défaut
|
||
`insee` mais peut être changé via le paramètre `--schema-name`.
|
||
|
||
Une aide en ligne est aussi disponible pour les détails des options
|
||
disponibles.
|
||
|
||
```sh
|
||
./createdatabase.py --help
|
||
```
|
||
|
||
### Nom dynamique de schéma et injection SQL
|
||
|
||
La definition dynamique du nom du schema nous oblige à utiliser la fonction
|
||
`AsIs`. Cette fonction ne permet pas à `cur.execute()` la mise en place de
|
||
sécuritées afin de prévenir les injections SQL.
|
||
|
||
Lors de la définition de la valeur de ce paramètre, nous le vérifions avec la
|
||
fonction `check_schema_name()`. Afin de définir le nombre maximum de caractères
|
||
composant le nom d'un schéma, nous avons vérifié dans le code source de
|
||
Postgre : il est de 63 caractètes (fichier pg_config_manual.h).
|
||
|
||
Cette vérification nous permet donc d'éviter le problème des injections et
|
||
est utilisée dans chacun des scripts accèdant à la base.
|
||
|
||
### Paramètres de connexion à la base
|
||
|
||
Un fichier de configuration avec les paramètres de connection à la base
|
||
PostgreSQL est nécessaire . Ce fichier prend la forme suivante:
|
||
|
||
```
|
||
host=<host> user=<user> password=<password>
|
||
```
|
||
|
||
Les valeurs sont à remplacer par les votres. Par défaut les scripts qui
|
||
nécessite une connexion à la base utilisent le fichier `.pgconn` dans le
|
||
répertoire courant. Il est possible de le changer avec le paramètre `-f`
|
||
(version longe `--connexion-file`) de chacun des scripts.
|
||
|
||
## Question 1: mettre en place des requêtes
|
||
|
||
Le script `get_state_statistics` permet d'afficher des informations sur une
|
||
région en particulier. Il suffit de lui passer via l'option `-t` (ou `--state`
|
||
en version longue) la région désirée. Il est aussi possible de lui demander une
|
||
année en particulier pour l'affichage des informations demographiques viam
|
||
l'option `--year` comme pour l'exemple ci-dessous:
|
||
|
||
```
|
||
./get_states_statistics.py --state 'Nouvelle-Aquitaine' --year 1999
|
||
```
|
||
|
||
## Question 2: ajouter des vues
|
||
|
||
C'est le script `create_view.py` qui se charge de la création des deux vues. Il
|
||
est possible de tester les vues créer en les interrogeant avec l'option `-t`
|
||
(version longue `--test`):
|
||
|
||
```
|
||
./create_view.py -t
|
||
```
|
||
|
||
## Question 3: création d'une procédure
|
||
|
||
Le script `create_procedure.py` permet de créer les éléments nécessaire à
|
||
l'exécution de la procédure stockée demandée: les colonnes dans les tables
|
||
région et département et la procédure. Avec l'option `-c` (version longe
|
||
`--call`) le script applique manuellement la procédure sur la base.
|
||
|
||
```
|
||
./create_procedure.py --call
|
||
```
|
||
|
||
## Question 4: création d'un Trigger
|
||
|
||
Le script `create_trigger.py` permet de mettre en place plusieurs chose:
|
||
|
||
* Le blocage de ma modification des éléments des tables *département* et
|
||
*région*
|
||
* Le calcul de la population totale d'un département puis d'une région et la
|
||
modification de la colonne dans ces même tables.
|
||
|
||
Pour la modification des colonnes, nous désactivons les *triggers* empêchant la
|
||
modification de ces tables le temps de la modification puis les réactivons.
|
||
|
||
L'utilisation du script est simple:
|
||
|
||
```
|
||
./create_trigger.py
|
||
```
|
||
|
||
## Question 5: Trigger avancés
|
||
|
||
Le script `create_advanced_trigger.py` permet de répondre à la question 6. Il
|
||
permet de créer une seconde procédure pour mettre à jour les colonnes
|
||
*population* de nos tables *departement* et *région* en fonction des
|
||
informations de la table *commune*.
|
||
|
||
L'utilisation du script est simple:
|
||
|
||
```
|
||
./create_advanced_trigger.py
|
||
```
|
||
|
||
Vous remarquerez que nous acvons créé une nouvelle fonction `fun_mag_pop_2` et
|
||
`prc_pop_reg_dep_2` au lieu de remplacer les anciennent. Ce n;est pas optimisé
|
||
mais le maue de temps nous a poussé à laisser tel quel.
|
||
|
||
## Question 6: EXPLAIN Afin d'étudier le fonctionnement de l;ordonnanceur de
|
||
PostGreSQL, nous avons exécuter trois requêtes de taille différente que se soit
|
||
en terme de jointures ou de nombre de ligne retournées.
|
||
|
||
```sql
|
||
-- petite requête : une jointure
|
||
explain SELECT ID_REGION, r.REG, r.NCC,d.DEP
|
||
FROM INSEE.REGION r
|
||
INNER JOIN INSEE.DEPARTEMENT d ON d.REG = r.REG
|
||
WHERE r.NCC = 'BRETAGNE';
|
||
```
|
||
|
||
|
||
```
|
||
QUERY PLAN
|
||
Hash Join (cost=1.24..6.58 rows=6 width=23)
|
||
Hash Cond: ((d.reg)::text = (r.reg)::text)
|
||
-> Seq Scan on departement d (cost=0.00..5.01 rows=101 width=6)
|
||
-> Hash (cost=1.23..1.23 rows=1 width=20)
|
||
-> Seq Scan on region r (cost=0.00..1.23 rows=1 width=20)
|
||
Filter: ((ncc)::text = 'BRETAGNE'::text)
|
||
```
|
||
|
||
```sql
|
||
-- requête moyenne : deux jointures
|
||
explain SELECT ID_REGION, r.REG, r.NCC
|
||
FROM INSEE.REGION r
|
||
INNER JOIN INSEE.DEPARTEMENT d ON d.REG = r.REG
|
||
INNER JOIN INSEE.COMMUNE c ON c.DEP = d.DEP
|
||
GROUP BY ID_REGION,r.REG, r.NCC;
|
||
```
|
||
|
||
```
|
||
QUERY PLAN
|
||
HashAggregate (cost=990.76..990.94 rows=18 width=20)
|
||
Group Key: r.id_region
|
||
-> Hash Join (cost=7.68..903.35 rows=34965 width=20)
|
||
Hash Cond: ((d.reg)::text = (r.reg)::text)
|
||
-> Hash Join (cost=6.27..788.55 rows=34965 width=3)
|
||
Hash Cond: ((c.dep)::text = (d.dep)::text)
|
||
-> Seq Scan on commune c (cost=0.00..686.65 rows=34965 width=3)
|
||
-> Hash (cost=5.01..5.01 rows=101 width=6)
|
||
-> Seq Scan on departement d (cost=0.00..5.01 rows=101 width=6)
|
||
-> Hash (cost=1.18..1.18 rows=18 width=20)
|
||
-> Seq Scan on region r (cost=0.00..1.18 rows=18 width=20)
|
||
```
|
||
|
||
```sql
|
||
-- grosse : trois jointures avec la table STATISTIQUES qui contient beaucoup de données
|
||
explain SELECT ID_REGION, r.REG, r.NCC, s.ID_INDICATEUR, s.DATE_DEBUT, SUM(s.VALEUR)
|
||
FROM INSEE.REGION r
|
||
INNER JOIN INSEE.DEPARTEMENT d ON d.REG = r.REG
|
||
INNER JOIN INSEE.COMMUNE c ON c.DEP = d.DEP
|
||
INNER JOIN INSEE.STATISTIQUE s ON s.COM = c.COM
|
||
GROUP BY ID_REGION,r.REG, s.DATE_DEBUT, r.NCC, s.ID_INDICATEUR
|
||
ORDER BY ID_INDICATEUR;
|
||
```
|
||
|
||
```
|
||
QUERY PLAN
|
||
Finalize GroupAggregate (cost=26461.68..26660.78 rows=756 width=34)
|
||
Group Key: s.id_indicateur, r.id_region, s.date_debut
|
||
-> Gather Merge (cost=26461.68..26638.10 rows=1512 width=34)
|
||
Workers Planned: 2
|
||
-> Sort (cost=25461.66..25463.55 rows=756 width=34)
|
||
Sort Key: s.id_indicateur, r.id_region, s.date_debut
|
||
-> Partial HashAggregate (cost=25417.95..25425.51 rows=756 width=34)
|
||
Group Key: s.id_indicateur, r.id_region, s.date_debut
|
||
-> Hash Join (cost=1131.39..19884.52 rows=553343 width=34)
|
||
Hash Cond: ((d.reg)::text = (r.reg)::text)
|
||
-> Hash Join (cost=1129.99..18088.60 rows=553343 width=17)
|
||
Hash Cond: ((c.dep)::text = (d.dep)::text)
|
||
-> Hash Join (cost=1123.71..16568.85 rows=553343 width=17)
|
||
Hash Cond: ((s.com)::text = (c.com)::text)
|
||
-> Parallel Seq Scan on statistique s (cost=0.00..13992.43 rows=553343 width=20)
|
||
-> Hash (cost=686.65..686.65 rows=34965 width=9)
|
||
-> Seq Scan on commune c (cost=0.00..686.65 rows=34965 width=9)
|
||
-> Hash (cost=5.01..5.01 rows=101 width=6)
|
||
-> Seq Scan on departement d (cost=0.00..5.01 rows=101 width=6)
|
||
-> Hash (cost=1.18..1.18 rows=18 width=20)
|
||
-> Seq Scan on region r (cost=0.00..1.18 rows=18 width=20)
|
||
```
|
||
|
||
|
||
* Plus le nombre de jointure est importante et plus la requête va être longue à
|
||
s'exécuter et coûteuse. C'est même la première source de consommation de
|
||
ressource pour des jointures impliquant le plus de lignes dans nos exemples.
|
||
|
||
* Le nombre de ligne dans les tables jouent également sur le temps d’exécution
|
||
et le coût qu'elle génère.
|
||
|
||
* Il faut prioriser de petites requête en évitant des jointure inutile en
|
||
utilisant un EXISTS par exemple.
|