Victor Landeau

Passionné par le développement de solutions à base de Machine Learning, de l'analyse du problème à l'utilisation en production

Ou comment rendre maintenable vos requêtes SQL par toute l'équipe

TL;DR

• Il n’existe pas aujourd’hui de framework pour créer des tests automatisés pour les requêtes SQL écrites pour les systèmes cloud tels que BigQuery, RedShift ou Snowflake
• SQL Test Kit est une bibliothèque python qui vous permet de facilement tester vos requêtes en les exécutant sur le système visé
• SQL Test Kit n’utilise que du code SQL standard et peut donc être utilisé avec n’importe quel dialecte SQL
• SQL Test Kit vous permet de lancer vos tests sans avoir à créer de tables dans votre base de données, ce qui vous permet de gagner en temps d’exécution et d’éviter de toucher à votre base de données

Introduction

Au cours des quinze dernières années, le choix entre infrastructure on-premise et cloud est devenu de plus en plus évident pour les entreprises. Pourquoi maintenir des dizaines de serveurs, ainsi que toutes les équipes nécessaires à leur fonctionnement, lorsqu’en quelques clics vous pouvez avoir accès à une infrastructure complète, fiable, sécurisée, et qui s’adapte automatiquement à vos besoins ?

Côté Analytics et Big Data, cette évolution est en train de sonner le glas des clusters Hadoop on-premise, qui étaient encore très utilisés à la fin des années 2010. En effet, pour ce qui est de la création de Data Lakes permettant aux Data Analysts et Data Scientists de tirer partis de l’ensemble des données récoltées par l’entreprise, des solutions telles que AWS RedShift, Google BigQuery ou Snowflake nécessitent beaucoup moins de connaissance en terme d’API et d’optimisation des requêtes par rapport à Apache Spark : quoi de plus simple qu’une bonne vieille requête SQL ?

Il manque pourtant quelque chose d’essentiel à ces systèmes si simples d’utilisation : la possibilité d’écrire des tests automatiques. Contrairement à d’autres dialectes SQL tels que PostgreSQL, SQLite ou MySQL, il n’existe aujourd’hui aucun moyen facile et fiable de tester des requêtes écrites pour ces systèmes, ce qui fait que la plupart des équipes qui les utilisent se contentent aujourd’hui généralement de tests manuels. Au cours de cet article, nous allons voir comment SQL Test Kit, une bibliothèque python développée par Ekinox, permet de combler ce manque.

Les tests automatiques : un besoin vital pour la maintenabilité du code

Lorsque les requêtes écrites servent uniquement à faire des analyses ponctuelles, afin par exemple d’analyser l’impact d’une campagne marketing ou d’une nouvelle offre, le fait de se contenter de tests manuels ne pose aucun problème. Néanmoins, ces requêtes finissent souvent par être réutilisées, soit pour générer des rapports de manière régulière, soit comme une base de code permettant de développer des analyses plus complexes.

Les développeurs qui modifient ces requêtes, qu’ils soient Data Analysts, Data Scientists ou Data ingénieurs, doivent alors être en mesure de bien comprendre ce que fait la requête initiale, et de la modifier afin d’obtenir le comportement voulu. Cela peut paraître simple, mais lorsque la personne qui doit modifier la requête n’est pas celle qui l’a écrite, ou qu’elle l’a écrite il y a plusieurs mois, ou encore que la requête fait plusieurs centaines de lignes et utilise des noms de variables peu explicites, cela peut vite devenir un vrai casse-tête.

Une première solution peut ici consister à améliorer les pratiques de code et de travail en équipe : on peut par exemple décider d’écrire les requêtes en pair programming. On peut aussi mettre en place des revues de code systématiques afin de s’assurer que le code est compréhensible pour d’autres personnes que celle qui vient de l’écrire, et également de diffuser la connaissance et les pratiques au sein de l’équipe. Cela peut clairement aider, et n’est aujourd’hui pas appliqué dans la grande majorité des entreprises, mais cela n’est pas suffisant.

En effet, bien comprendre et maîtriser le fonctionnement d’une grande quantité de base de code de manière à pouvoir la modifier sans risque demande beaucoup trop d’effort mental pour que cela soit réaliste, même si le code est clair et bien organisé, voire documenté. Il est aujourd’hui universellement admis dans le monde du développement informatique que la seule manière de maintenir la qualité d’une base de code est de l’accompagner de tests automatisés. Cela permet de s’assurer que le code fonctionne correctement, de documenter ce fonctionnement attendu et de détecter immédiatement les régressions qui pourraient être introduites par des modifications ultérieures.

SQL Test Kit : un framework pour tester n’importe quel dialecte SQL

Ce principe bien compris de la plupart des développeurs a amené par le passé au développement de divers frameworks permettant de tester le code SQL des systèmes open source. On peut par exemple citer les bases de données en mémoire tel que H2, qui permettent de faire tourner les tests sans avoir à installer une base de données complète, ou encore TestContainers, qui permet de facilement lancer une vraie base de données dans un conteneur Docker.

Malgré certaines tentatives de développer des frameworks similaires pour les systèmes cloud, parmi lesquelles on peut par exemple citer tinyquery, aucune alternative fiable et complète n’existe aujourd’hui pour Redshift, Bigquery ou Snowflake. En effet, dans ce cas seuls AWS, Google ou Snowflake ont la maîtrise du dialecte et de son évolution, et seraient donc en mesure de créer un framework de test dédié ; ce qu’ils n’ont apparemment pas eu l’intention de faire jusqu’à ce jour.

Une autre solution adoptée par certains consiste à traduire la requête SQL dans un dialecte pour lequel il existe un framework de test, puis à faire tourner le test sur ce dialecte. Cela peut cependant être assez complexe à mettre en place et dépend fortement de la qualité de la librairie de traduction utilisée. De plus, le fait que cela ne soit pas la version originale de la requête qui soit testée peut amener les développeurs à douter de la fiabilité des tests obtenus.

C’est pour répondre à ce besoin qu’a été développé SQL Test Kit. Ce framework a été imaginé dans le but de tester n’importe quelle requête SQL de manière isolée, quel que soit le dialecte SQL dans lequel elle est écrite. Pour ce faire, le principe consiste à faire tourner la requête sur le système cible, par exemple sur BigQuery ou Redshift, puis à comparer le résultat obtenu avec celui attendu.

De plus, afin d’éviter d’avoir à créer des tables spécifiques pour les tests, ce qui est consommateur en temps et en resources, ce framework permet de remplacer à la volée les tables sur lesquelles se base larequête par des données fournies par l’utilisateur en entrée du test. Cela permet de réduire significativement le temps d’exécution des tests, puisqu’il n’est plus nécessaire de créer des tables temporaires pour chaque test.

Comment l’utiliser ?

SQL Test Kit est une bibliothèque python disponible sur PyPi, ce qui la rend facilement installable avec pip ou poetry :

pip install sql-test-kit

poetry add sql-test-kit

Une fois installée, SQL Test Kit vous demande de créer des objets de type Table afin de stocker l’information du chemin et du schéma des tables que vous allez vouloir manipuler dans vos requêtes SQL. En plus de vous permettre d’utiliser le framework, ces classes pourront aussi vous être utiles au sein de votre code python, si vous souhaitez manipuler les données que vous aurez récupérées à partir de votre base tout en maîtrisant leur schéma.

Pour créer un tel objet, il vous suffit d’exécuter un code de ce type :

from sql_test_kit import Table, Column

sales_amount_col = "SALES_AMOUNT"
sales_date_col = "SALES_DATE"
sales_table = Table(
    table_path="table.path",
    columns=[
        Column(sales_amount_col, "FLOAT64"),
        Column(sales_date_col, "STRING"),
    ],
)

Le format du chemin des tables dépendant du système utilisé, vous pouvez créer de nouvelles classes héritant de AbstractTable afin de plus facilement gérer les spécificités de votre système. C’est par exemple ce qui a été fait pour BigQuery, avec la classe BigQueryTable.

Une fois vos tables créées, je vous recommande de les utiliser pour créer vos requêtes, même si cela n’est pas obligatoire. Cela vous permettra néanmoins de pouvoir facilement impacter des changements sur vos tables, tel que le renommage d’une colonne ou le changement de son type, sans avoir à modifier toutes vos requêtes. Pour cela, vous pouvez par exemple créer des strings interpolés de cette manière :

current_year_sales_by_day_query = f"""
    SELECT {sales_date_col}, SUM({sales_amount_col}) AS {sales_amount_col}
    FROM {sales_table}
    WHERE {sales_date_col} >= "2023-01-01"
    GROUP BY {sales_date_col}
"""

Enfin, pour tester votre requête, il vous suffit d’utiliser l’objet QueryInterpolator afin de remplacer les tables par les données que vous souhaitez utiliser pour le test. Vous pourrez ensuite lancer votre requête sur le système ciblé, et comparer le résultat obtenu avec celui attendu. Votre test pourrait par exemple ressembler à ceci :

import pandas as pd
from google.cloud.bigquery import Client

from sql_test_kit import QueryInterpolator


def test_current_year_sales_by_day_query():
    # Given
    sales_data = pd.DataFrame(
        {
            "SALES_ID": [1, 2, 3, 4],
            sales_date_col: ["2022-12-31", "2023-01-01", "2023-01-01", "2023-01-02"],
            sales_amount_col: [10, 20, 30, 40],
        }
    )

    # When
    interpolated_query = QueryInterpolator() \
        .add_input_table(sales_table, sales_data) \
        .interpolate_query(current_year_sales_by_day_query)
    current_year_sales_by_day_data = Client().query(interpolated_query).to_dataframe()

    # Then
    expected_current_year_sales_by_day_data = pd.DataFrame(
        {
            sales_date_col: ["2023-01-01", "2023-01-02"],
            sales_amount_col: [50, 40],
        }
    )

    pd.testing.assert_frame_equal(
        current_year_sales_by_day_data,
        expected_current_year_sales_by_day_data,
        check_dtype=False,
    )

Conclusion

Dans cet article, nous avons vu pourquoi il est tout aussi important de tester votre code SQL que le code que vous pourriez écrire dans d’autres langages. Nous avons également évoqué pourquoi cela est difficile aujourd’hui lorsque vous utilisez des bases de données analytiques sur le cloud telles que BigQuery ou Redshift. Enfin, nous avons vu comment SQL Test Kit peut vous aider à tester vos requêtes SQL quelque soit votre système de base de données, en vous permettant de les exécuter sur le système ciblé.

S’il est vrai que cette pratique n’est pas idéale, puisqu’elle ne vous permet pas de tester votre code sans accès à votre système de base de données, et qu’elle reste beaucoup plus lente que des tests unitaires classiques, il s’agit à ce jour de la meilleure solution que j’ai pu trouver pour tester du code SQL écrit pour les services cloud sus-cités. N’hésitez pas à me faire part de vos retours en commentaire, ou à suggérer d’autres solutions que vous auriez trouvé pour répondre à cette problématique.