Bertrand Fabre“J’aimerai bien tester la validité de mes données et avoir un message d’erreur personnalisé le cas échéant. Tu connais quelque chose ?”
Sur BigQuery, il y a effectivement une fonction qui pourrait répondre à ce cas d’usage. C’est ASSERT().
Voici sa syntaxe :
ASSERT(
SELECT COUNT(*) > unNombre FROM maTable -- COUNT() ou autres
) AS 'Attention, le nombre de lignes semble louche';
ASSERT EXISTS(
SELECT * FROM maTable WHERE maColonne < unNombre -- Le QUALIFY est ok aussi
) AS 'Attention, comportement étrange pour au moins un nombre';L’ASSERT simple permet de spécifier des conditions dans le SELECT qui retourne un seul résultat. C’est pour cela que l’on va surtout utiliser des fonctions d’agrégat.
L’ASSERT EXISTS permet quant à lui de directement exprimer une condition sur des colonnes de la table. Au sein d’un WHERE ou même d’un QUALIFY. Ce que ne pouvait pas faire l’ASSERT simple qui n’accepte pas une colonne qui a plus d’une ligne.
On va s’attarder sur un exemple de chaque, afin d’en expliquer la lecture.
🎄 Nous travaillons sur les données de ventes d’accessoires de Noël.
Je vais créer une table pour que les différentes manipulations soient plus simples.
CREATE TABLE datayoka-dev.DEMO.F_christmasSales AS
WITH christmasSales AS (
SELECT 'Guirlande lumineuse' AS product, 20 AS price, 100 AS quantity, DATE '2023-12-01' AS saleDate UNION ALL
SELECT 'Boule de Noël', 5, 250, DATE '2023-12-05' UNION ALL
SELECT 'Sapin artificiel', 50, 10, DATE '2023-11-20' UNION ALL
SELECT 'Étoile pour sapin', 15, NULL, DATE '2023-12-10' UNION ALL
SELECT 'Guirlande lumineuse', 0, 200, DATE '2023-12-12' UNION ALL
SELECT 'Bougie parfumée', 8, 300, DATE '2023-12-15'
)
SELECT *
FROM christmasSales
;Pour l’exemple, nous voulons vérifier que le nombre de lignes de la table est bien supérieur à 7.
ASSERT (
SELECT COUNT(*) > 7
FROM datayoka-dev.DEMO.F_christmasSales
) AS 'ATTENTION : la table doit avoir plus de 7 lignes'
;Résultat
‼ ATTENTION : la table doit avoir plus de 7 lignes
Interprétation
Le message d’erreur n’est affiché que s’il n’y a que moins de 8 lignes dans la table. C’est bien le cas. Le test n’est donc pas validé.
Prenons un autre exemple. Nous voulons vérifier qu’il n’existe pas de valeurs manquantes pour la colonne des quantités vendues.
Nous avons deux possibilités :
-- 1ère possibilité
ASSERT (
SELECT COUNTIF(quantity IS NULL) = 0
FROM datayoka-dev.DEMO.F_christmasSales
) AS 'ATTENTION : il ne doit pas y avoir de quantité manquantes'
;
-- 2ème possibilité
ASSERT NOT EXISTS(
SELECT * FROM datayoka-dev.DEMO.F_christmasSales
WHERE quantity IS NULL
) AS 'ATTENTION : au moins une quantité est manquante'
;La première possibilité indique que le nombre de quantité manquantes doit être égal à 0 sinon on retourne une erreur.
La deuxième possibilité indique qu’il ne doit pas y avoir de quantité manquantes dans la colonne quantity sinon on retourne une erreur.
😅 Evidemment il y a une quantité manquante et les deux possibilités retournent une erreur.
Et la question que tout le monde se pose (ou pas). Que retourne les ASSERT lorsque le test est validé ?
“L'assertion a bien été effectuée.”
Merci de votre lecture !
