Présenation générale de l'outil

Dans cet article, nous allons détailler ensemble la création de cette application. L’objectifs : tester son code SQL en direct sur une base en production.

Génération de la base de donnée

La maîtrise d’un langage de programmation est basé sur 2 choses :

• La théorie : connaître les règles et la syntaxe
• La pratique

Autant il est facile de trouver des ressources pour appendre les commandes SQL en ligne autant lorsqu’on veut passer à la partie pratique cela devient vite compliqué. Il faut en effet trouver des données, les intégrer dans un base que l’on met en production pour enfin la requêter. Pour cet outil, je suis parti d’une base factice appelée Chinook. La première étape est la création de la base, pour cela il faut :

• Créer une base de données
• Créer les différentes tables et leurs connexions
• Intégrer les données

Pour se faire, nous disposons d’un script SQL, qu’il est possible de télécharger sur ce repo Github : chinook-database

Voici le diagramme de la base Chinook :


Pour ce qui est du contenu des requêtes, la difficulté est croissante en moyenne, au programme les clauses de bases : WHERE, ORDER BY, GROUP BY, HAVING, les jointures, les window functions, les CTE, les dates, etc.

Hébergement de la base de donnée

La base de donnée doit être accessible en ligne. J’ai donc opté pour Render qui offre la possibilité de créer une base de donnée PostgreSQL gratuitement pendant 3 mois. Le processus de création est très simple, il suffit de donner de créer un projet et une base. Nous aurons besoins pour la suite des informations suivantes :

• username : votre nom d’utilisateur
• password : votre mot de passe pour accéder à la base
• database : nom de la base de données
• hostname : nom du serveur (adresse externe)

L'interface vous permet de récupérer simplement les informations :

Application Gradio

Python propose de nombreuses librairies pour créer des web apps simples. On peut en citer 3 : streamlit, gradio et shiny. shiny est à la base une librairie R et une version python existe depuis quelques mois. streamlit et gradio sont assez similaire, mais on va opter pour gradio pour changer un peu. J’ai déjà développé quelques apps avec streamlit sur ce blog, il faut se diversifier ! Le principe de l’application est le suivant :

• On créer un fichier xls contenant : le libellé de la requête, la requête SQL et le chemin du résultat de la requête
• On choisi un libellé de questions, on écrit la requête et on l’exécute
• L’application compare le résultat de la requête écrite par l’utilisateur avec la réponse
• Si les 2 dataframes sont équivalentes, alors la réponse est correcte, sinon non
• Il est ensuite possible à l’utilisateur d’afficher la réponse pour comprendre son erreur s’il le souhaite

Remarque : j’ai crée un script qui exécute chaque requête, convertie le résultat en dataframe et enregistre le résultat au format .pkl Je détaillerai dans un autre article le détail de l’app, les différentes fonctions, etc. Pour le moment il s’agit d’une version beta.

Déploiement de l’app

Il est important de penser au déploiement de l’outil. Il n’existe pas d’équivalent au “streamlit cloud”, cependant, il est possible d’heberger notre outil chez sur un espace HuggingFace gratuitement. Pour cela rien de plus simple :

git clone git@hf.co:spaces/gpenessot/SQL_training_for_job_interviews
cd SQL_training_for_job_interviews
git add *
git commit -m "first commit"
git push

Et voila ! L’app est en ligne sur l’espace HuggingFace, il est maintenant possible de la tester en direct ! SQL Questions for Job Interviews. Pensez à ne pas laisser d'informations confidentielles dans les scripts, les informations de connexions sont stockées directement dans les variables d'environnement de l'espace HuggingFace.

Le résultat en image :

Développements futurs

Je pense ajouter une IA de type LLM pour expliquer à l’utilisateur pourquoi la requête qu’il a écrit n’est pas correcte, cette fonctionnalité peut être intéressante pour les débutant(e)s.