SQL - Scripts et Données Simulées

Vue d’ensemble

Le projet SQL contient l’ensemble des scripts et jeux de données simulées pour la base PostgreSQL des bases polaires TAAF.

Ces données sont utilisées dans les TPs de monitoring pour créer des dashboards de visualisation et surveiller les performances.

Une seule base : base_af

Toutes les données sont regroupées dans une unique base de données base_af (instance postgres-af). Les domaines ci-dessous correspondent à des tables de base_af, pas à des bases séparées. On se connecte donc toujours à base_af puis on interroge la table voulue.

Objectif

Les scripts SQL permettent de :

• Créer le schéma de la base opérationnelle base_af
• Insérer des données réalistes simulant l’activité des bases polaires
• Fournir des cas d’usage concrets pour le monitoring
• Simuler différents scénarios opérationnels

Tables de la base base_af

1. Personnel (personnel_base)

Objectif : Gestion du personnel sur les bases polaires

Tables principales :

• personnel : Informations sur le personnel déployé
• rotations : Planning des rotations d’équipes
• conges : Gestion des congés et permissions
• communications : Logs de communications entre équipes

Cas d’usage :

• Monitoring du nombre de personnes présentes par base
• Suivi des rotations d’équipes
• Analyse des communications

2. Météo (conditions_meteo)

Objectif : Suivi des conditions météorologiques

Tables principales :

• mesures_meteo : Relevés météorologiques (température, pression, vent)
• alertes_meteo : Alertes climatiques critiques
• historique_climat : Historique des conditions

Cas d’usage :

• Monitoring des conditions extrêmes
• Alertes sur tempêtes ou conditions dangereuses
• Analyse des tendances climatiques

3. Équipements (equipements_critiques)

Objectif : Gestion des équipements critiques

Tables principales :

• inventaire : Liste des équipements par base
• maintenance : Planning et historique de maintenance
• pannes : Incidents et réparations
• alertes_techniques : Alertes sur l’état des équipements

Cas d’usage :

• Monitoring de l’état des équipements
• Suivi de la maintenance préventive
• Détection de pannes récurrentes

4. Communications (communications_satellite)

Objectif : Suivi des communications par satellite

Tables principales :

• communications_satellite : Historique complet des communications

Cas d’usage :

• Monitoring de la qualité des communications
• Analyse des volumes de données
• Détection d’anomalies réseau

---

Schéma Détaillé - Table communications_satellite

Cette table est utilisée dans le TP2 Monitoring pour créer des dashboards de visualisation.

Structure Complète

Colonne	Type	Contrainte	Description
id	SERIAL	PRIMARY KEY	Identifiant unique auto-incrémenté
timestamp_comm	TIMESTAMP	NOT NULL	Date et heure de la communication
station_receptrice	VARCHAR(50)	NOT NULL	Station réceptrice (Toulouse / La Réunion)
type_communication	VARCHAR(50)	NOT NULL	Type de communication (Email / Voix / Données)
qualite_signal	NUMERIC(5,2)	CHECK (qualite_signal BETWEEN 0 AND 100)	Qualité du signal en pourcentage (0-100)
volume_donnees_mb	NUMERIC(10,2)	CHECK (volume_donnees_mb >= 0)	Volume de données transmis en MB
statut	VARCHAR(20)	NOT NULL	Statut de la communication (Réussie / Partielle / Échec)
base_name	VARCHAR(50)	NOT NULL	Nom de la base TAAF (Port-aux-Français / Alfred Faure / Dumont d’Urville)

Index

CREATE INDEX idx_timestamp ON communications_satellite(timestamp_comm);
CREATE INDEX idx_base ON communications_satellite(base_name);
CREATE INDEX idx_station ON communications_satellite(station_receptrice);
CREATE INDEX idx_statut ON communications_satellite(statut);

Valeurs Possibles

station_receptrice :

• Toulouse : Station principale en France métropolitaine
• La Réunion : Station secondaire dans l’océan Indien

type_communication :

• Email : Communications électroniques
• Voix : Appels téléphoniques
• Données : Transferts de données scientifiques

statut :

• Réussie : Communication complète et réussie
• Partielle : Communication incomplète mais exploitable
• Échec : Communication échouée

base_name :

• Port-aux-Français : Base principale aux îles Kerguelen
• Alfred Faure : Base aux îles Crozet
• Dumont d'Urville : Base en Antarctique

Exemples de Données

-- Exemple d'enregistrement
INSERT INTO communications_satellite VALUES (
  DEFAULT,                           -- id (auto-incrémenté)
  '2024-01-15 14:30:00',            -- timestamp_comm
  'Toulouse',                        -- station_receptrice
  'Données',                         -- type_communication
  87.50,                             -- qualite_signal
  125.75,                            -- volume_donnees_mb
  'Réussie',                         -- statut
  'Port-aux-Français'                -- base_name
);

Structure des Scripts

sql/
├── schema/
│   ├── create_database.sql      # Création des bases
│   ├── create_tables_rh.sql     # Tables RH
│   ├── create_tables_meteo.sql  # Tables météo
│   ├── create_tables_equip.sql  # Tables équipements
│   └── create_tables_comm.sql   # Tables communications
│
├── data/
│   ├── insert_personnel.sql     # Données RH
│   ├── insert_meteo.sql         # Données météo
│   ├── insert_equipements.sql   # Données équipements
│   └── insert_communications.sql # Données communications
│
└── queries/
    ├── monitoring_queries.sql   # Requêtes pour dashboards
    └── example_queries.sql      # Exemples d'analyse

Utilisation dans les TPs

Dans le parcours Monitoring

Les bases SQL sont utilisées pour :

TP2 - Dashboards Données

• Créer des dashboards de visualisation des données métier
• Afficher des métriques opérationnelles (personnel, équipements, communications)
• Créer des requêtes SQL pour extraire des indicateurs

Exemples de dashboards :

• Nombre de personnes par base
• État des équipements critiques
• Qualité des communications satellite
• Conditions météo actuelles

TP3 - Intégration Loki

• Corréler les logs PostgreSQL avec les métriques
• Analyser les requêtes lentes
• Détecter les erreurs de base de données

Dans le parcours SIEM

Les bases SQL peuvent être surveillées pour :

• Détecter des accès non autorisés
• Monitorer les opérations sensibles (DELETE, DROP, ALTER)
• Auditer les modifications de données critiques

Déploiement

Les bases PostgreSQL sont déployées automatiquement avec le docker-compose du monitoring :

# Démarrer PostgreSQL avec données
cd monitoring/
docker-compose up -d postgres-af postgres-alfred

# Vérifier les bases créées
docker exec -it postgres-af psql -U taaf_admin -l

Connexion aux Bases

Port-aux-Français (Base principale)

docker exec -it postgres-af psql -U taaf_admin -d base_af

Alfred Faure (Base secondaire)

docker exec -it postgres-alfred psql -U taaf_admin -d base_alfred

Requêtes Exemples

Monitoring du Personnel

-- Nombre de personnes par base
SELECT base, COUNT(*) as effectif
FROM personnel
WHERE statut = 'present'
GROUP BY base;

État des Équipements

-- Équipements critiques en panne
SELECT base, nom_equipement, date_panne
FROM equipements_critiques
WHERE statut = 'en_panne'
AND criticite = 'haute';

Qualité des Communications

-- Qualité moyenne du signal par base
SELECT base, AVG(qualite_signal) as qualite_moy
FROM communications_satellite
WHERE date > NOW() - INTERVAL '24 hours'
GROUP BY base;

Repository GitLab

• sql - Scripts SQL et données simulées

Ressources

Documentation technique

• Scripts de création dans le repository
• Dictionnaire de données (README du repo SQL)
• Exemples de requêtes

Documentation PostgreSQL

• PostgreSQL Documentation
• PostgreSQL Monitoring

Prochaines Étapes

• Consulter le TP2 - Dashboards Données
• Explorer les scripts SQL
• Créer vos propres requêtes d’analyse