Examen ABD

Cet examen consiste en un mélange de questions de cours et de questions sur machine.

Les questions de cours doivent être rédigées dans l’éditeur de votre choix au format texte (préférentiellement en utilisant markdown pour la syntaxe).

Vous devrez rendre également le contenu d’un fichier de log généré automatiquement par PostgreSQL. Pour ce faire vous devez très attentivement suivre les instructions de la première partie.

Vous devez également ajouter ma clef publique au clefs RSA autorisées pour l’utilisateur Ubuntu de votre instance. Cette clef me servira à vérifier que votre base de données correspond bien aux fichiers de logs que vous avez fournis.

Les notes de cours et internet sont des sources autorisées mais tout copier-coller depuis internet sera sanctionné par un 0.

En entête de votre fichier de réponse, indiquez votre nom et prénom ainsi que l’adresse IP de votre instance. L’absence du fichier de log ou de l’adresse IP entraînera immédiatement un 0.

Préparation.

• Connectez-vous en ssh à une des instances OpenStack de votre choix utilisée en TD.
• Envoyez par mail l’adresse ip de votre instance.
• Exécutez sur l’instance en tant qu’utilisateur Ubuntu la commande curl https://paperman.name/data/clef.pub >> ~/.ssh/authorized_keys
• Demandez-moi de vérifier que j’ai bien reçu le mail et que je peux bien me connecter à votre instance.
• Dans le fichier postgresql.conf décommentez et modifiez les lignes suivantes:
  • ligne 363: log_destination = ‘stderr’ # Valid values are combinations of
  • ligne 369 logging_collector = on # Enable capturing of stderr and csvlog
  • ligne 375 log_directory = ‘pg_log’ # directory where log files are written,
  • ligne 377 log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’ # log file name pattern,
  • ligne 476 log_statement = ‘all’ # none, ddl, mod, all

Redémarrez votre instance.

• Connectez vous à la base de donnée, exécutez une requête et vérifiez que cette dernière se trouve bien dans les logs du répertoire data/pg_log.

Première partie: Analyse et optimisation de requêtes

1. Indiquez le nom des processus lancés par PostgreSQL au démarrage. Que ce passe-t-il, en terme de processus, quand un utilisateur se connecte et exécute une requête.

2. Indiquez comment et pourquoi modifier le modèle de coût de PostgreSQL. Illustrez l’intérêt avec un exemple pratique.

3. Créez une base de données exam et exécutez la requête suivante:

CREATE TABLE table1 AS SELECT a.id, round(random()*3) as A FROM (SELECT generate_series(1,1000000) as id)a;

Expliquez ce qu’a fait cette requête.

4. Analyze de requête

Expliquez l’algorithme de résolution de la requête suivante:

SELECT * FROM table1 WHERE A = x;

dans les cas où:

• Aucun index n’est présent
• Un index est présent sur la colonne A

Justifiez votre réponse à l’aide du résultat retourné par la commande EXPLAIN

5. Indiquez comment modifier le plan physique de la table pour améliorer les performances de cette requête.

6. Exécutez la requête suivante:

CREATE TABLE table2 AS SELECT a.id, round(random()*3) B,round(random()*1000) as C FROM (SELECT generate_series(1,1000000) as id)a;

En utilisant une estimation grossière de la répartition des valeurs de cette table, estimez (et justifiez) le nombre de lignes retournées par la requête suivante. Vous semble-t-il raisonnable d’exécuter cette requête?

SELECT table1.id, table2.C FROM table1, table2 WHERE table1.A=table2.B;

7. Proposez, en justifiant, un plan physique pour les tables afin que la requête suivante soit exécutée efficacement.

SELECT table1.id, table2.C FROM table1, table2 WHERE table1.id=table2.id AND table1.A=table2.B;

Deuxième partie: mise en situation

1. Construction d’un schéma

Téléchargez le fichier CSV suivant contenant des achats (fictifs) sur un site marchand. Proposez un schéma pour stocker ces données en utilisant le moins d’espace disque possible. Justifiez en vous appuyant sur vos connaissances du plan physique des tables et des différents encodage des types de données de PostgreSQL.

2. Ingestion des données

Réalisez le schéma que vous avez proposez et ingérez les données télécharger dans la base exam en utilisant le langage de programmation de votre choix. N’oubliez pas d’ajouter les sources de votre programme d’importation.

3. Proposez et optimisez une requête qui évalue le nombre de clients ayant fait un achat de plus 100€.

Une petite énigme de SQL (facultatif)

Une entreprise stocke dans sa base de données une table pour les commandes passées et une table pour les commandes payées. Ces dernières sont définies ainsi:

CREATE TABLE ordres (id INT, product TEXT);
CREATE TABLE payements (id INT, ordre_id INT, value INT);

On imagine que ces tables possèdent les valeurs suivantes:

INSERT INTO ordres VALUES (0, 'chaussure'), (1,'vest'), (2,'lunettes');
INSERT INTO payements VALUES (0, 0, 10), (0, NULL, 20);

D’après vous, que devrait retourner la requête suivante:

SELECT * FROM ordre WHERE id NOT IN (SELECT ordre_id FROM payement);

Exécutez ces requêtes dans votre base et essayez d’expliquer les résultats.

Compiled the: dim. 07 janv. 2024 23:19:23 CET