Master 2, Bases de données avancées, année 2023

Suite du TP3

L’objectif de l’examen est de vérifier votre compréhension de l’intégration de Python dans PostgreSQL

Rendu du TP3

Préparer ce que vous avez fait dans le TP3 pour être rendu.

Je veux avoir un unique archive (format zip ou tar.gz) avec:

Un répertoire racine prenom.nom
Un readme
Un sous repertoire TP2
Un sous repertoire Exam

Vous pouvez prendre un peu de temps pour remettre en forme votre TP mais attention, c’est du temps que vous ne mettrez pas sur le reste de l’examen.

Dans le repertoire Exam, vous pouvez copier vos fichiers de TP3 et repartir de cette base pour continuer.

La note prendra en compte la qualité des rendus dans Exam ET la qualité de TP3.

Le rendu est à faire par mail

Dans le README:

Ajoutez des commentaire qui m’aideront à évaluer votre travail (difficultés particulières, choses non réalisées, bugs mais qui pourrait marcher avec un peu plus de travail).
Ajoutez l’IP de votre VM de travail. En théorie, je dois pouvoir m’y connecter si vous avez bien executer le script.
Si ce n’est pas le cas, executer le script suivant dans une nouvelle VM

ssh ubuntu@ip-address "curl -s https://paperman.name/data/scripts/prepare.sh | sh"

Ajoutez le contenu du fichier machine-id

Openfood

Nous allons utiliser les données issues d’openfood qui est un site qui donne les informations nutritionnelles des aliments (mais les données sont incomplètes et assez peu normalisée, donc difficile à exploiter). Dans ce jeu de donnée, chaque ligne est un document JSON.

Télécharger les données, décompressez le fichier et charger la première ligne avec le module json pour l’explorer un peu.
Recherchez tous les produits avec le champ id à “2000000047064”, conclude sur la qualité des données en entrées.
Créez une nouvelle base de données openfood et créez dedans une table raw_openfood qui doit accueillir les données. Donnez un identifiant unique pour chaque document de cette table. Pour cela, vous devez utiliser le type JSON ou mieux le type JSONB.

Le type JSONB represente en interne le document parsé alors que le type JSON est une représentation textuel. La manipulation de JSONB est plus efficace car le parsing du document est fait une fois pour toute à l’insertion.

Écrire un script qui charge votre table avec les données. Mesurez et reporter le temps d’ingestion de votre script. Passez un peu (mais pas trop) de temps à l’améliorer.

Une table normalisée

Maintenant que nous avons les données dans la base, nous allons écrire un script pour normaliser ses données et les rendre interrogeable avec du SQL classique.

Creez une table openfood_normalise qui contient:

Un identifiant pour un produit que vous generez vous même (ignorez celui qui est dans les données)
Une référence vers l’identifiant du document JSON stocké dans la table raw_openfood
Le nom du produit (champ: product_name)
La marque du produit (champ: brand)
La catégorie de produit (champ: categories)

Ajoutez à votre table la contrainte que la catégorie est non-nul. Si la catégorie est nulle pour un produit, alors vous ne l’ignorez et ne le mettez pas dans la table. Attention, il ne faut pas modifier raw_openfood. Écrire un script en Python qui peuple cette table avec les données.

Accélérer avec des expression `JsonPath` coté BDD

Il est possible d’extraire des données d’un document JSON avec des fonctions dédiées. La doc est bien fournie mais pour cet exercice on a besoin uniquement de l’opérator ->> qui retourne la valeur associée à une clef au format textuel. Attention l’opérator -> retourne la valeur mais au format JSON(B).

Écrire une requête qui peuple votre table directement depuis la table raw_openfood et comparer les performances.

Extraction de la language

Installez fasttext sur (votre machine ET sur la VM pour l’utilisateur PostgreSQL).

pip install fasttext

Et téléchargez le model:

wget https://dl.fbaipublicfiles.com/fasttext/supervised-models/lid.176.ftz

Le code suivant permet de prédir la langue:

M = fasttext.load_model("path/to/lid.176.ftz")
(label,), (quality,) =  M.predict("Bonjour, je suis Charles Paperman")

Avec label qui contient __label__fr et quality qui contient 0.95.

Testez sur votre machine que cela fonctionne a peu prêt.
Créez une nouvelle table openfood_normalise_2 qui reprends le format de openfood_normalise en ajoutant:

langue: TEXT

qui doit être une colonne générée par une fonction Python et contenant le résultat de la fonction fasttext.

Ajoutez également une contrainte pour que la qualité de la prédiction soit supérieur à 0.5 (sinon on rejette le document).

Enfin, peupler la table avec une seule requête SQL.

La table nutriments

Les nutriments sont stockée dans une sous-relations nutriments de chaque documents. Il est possible de l’exploser en une table à l’aide de la construction suivante:

SELECT openfood_raw.id, j.key, j.value FROM openfood_raw, jsonb_each(data->'nutriments') as J

Créez une table nutriment qui associe chaque ligne de la table openfood ses informations nutritionnelles.
Produire un tableau récapitulant la quantité moyenne de sucre pour 100g dans chaque catégorie.

Compiled the: mar. 19 mars 2024 16:13:06 CET