Bertrand Fabre“Est-ce qu’il y a un moyen de limiter automatiquement les agrégations faites sur les données, par exemple, ne la faire que si une condition est remplie ?”.
En effet, BigQuery propose une fonctionnalité qui permet de n’effectuer une agrégation que s’il y a un certain nombre de valeurs distinctes dans une colonne.
Ce nombre est fixé manuellement. C’est donc un seuil à respecter pour réaliser l’agrégation.
Le cas d’usage principal que je vois est de préserver un minimum de confidentialité sur les données.
Par exemple, s’il y a très peu de client, faire une agrégation sur eux pourrait permettre d’avoir des informations presque précises. Alors que s’il y a des milliers de clients, une statistique globale ne donnera pas d’éléments sur un individu en particulier.
Je penses qu’il est nécessaire de vous en montrer un peu plus pour comprendre la fonctionnalité et en imaginer des cas d’usage spécifiques.
Tout d’abord, on va créer une table classique.
CREATE OR REPLACE TABLE datayoka-dev.DEMO.F_superHeroSlipperSales AS (
SELECT "C001" AS customerId, "Iron Man" AS productName, "2024-11-01" AS purchaseDate, 25.00 AS turnover UNION ALL
SELECT "C002", "Spider-Man", "2024-11-02", 30.00 UNION ALL
SELECT "C003", "Hulk", "2024-11-02", 20.00 UNION ALL
SELECT "C004", "Iron Man", "2024-11-03", 25.00 UNION ALL
SELECT "C002", "Captain America", "2024-11-04", 35.00 UNION ALL
SELECT "C005", "Spider-Man", "2024-11-05", 30.00 UNION ALL
SELECT "C006", "Iron Man", "2024-11-05", 25.00 UNION ALL
SELECT "C007", "Thor", "2024-11-06", 40.00 UNION ALL
SELECT "C008", "Hulk", "2024-11-06", 20.00 UNION ALL
SELECT "C009", "Captain America", "2024-11-07", 35.00
); Par dessus cette table, nous allons créer une vue, qui n’est, en quelque sorte, qu’un moyen d’accès indirect à la table. Et c’est dans cette vue que l’on va initialiser la restriction sur les agrégations.
CREATE OR REPLACE VIEW datayoka-dev.DEMO.F_superHeroSlipperSalesView
OPTIONS (
privacy_policy = '{"aggregation_threshold_policy": {"threshold": 3,"privacy_unit_column": "customerId"}}'
) AS
SELECT
*
FROM datayoka-dev.DEMO.F_superHeroSlipperSales;Vous avez pu remarquer l’option “privacy_policy”. Cette option attend un paramètre au format JSON et dont la 1ère valeur du dictionnaire contient un autre dictionnaire. C’est dans ce dernier que l’on va paramétrer la restriction d’agrégation.
- threshold : on lui indique le nombre d’occurrences distinctes au minimum que doit contenir la colonne définie dans le deuxième paramètre ;
- privacy_unit_column : et donc là c’est la colonne sur laquelle on applique une restriction.
L’interprétation est donc : il n’est pas possible de ressortir un résultat agrégé sur les données qui n’ont pas au moins 3 customerId différents sur la ou les dimensions d’agrégation.
On va donc tester de faire une agrégation pour obtenir le chiffre d’affaires total par produit.
SELECT WITH AGGREGATION_THRESHOLD
productName,
SUM(turnover) AS globalTurnover
FROM datayoka-dev.DEMO.F_superHeroSlipperSalesView
GROUP BY ALL
;Résultat : une ligne pour “Iron Man” avec 75.0 de “globalTurnover”.
Pourtant, comme vous avez pu le remarquer, il y a plus qu’un seul produit vendu dans les données initiales. MAIS, il n’y a qu’un seul produit dont les ventes ont été réalisées par au moins 3 customerId différents ! Et c’est donc bien l’Iron Man.
Si nous avions voulu faire par la purchaseDate nous n’aurions aucun résultat car il n’y a jamais au moins trois clients différents par jour.
Il est à noter que l’utilisation obligatoire de la clause “WITH AGGREGATION_THRESHOLD” juste après le SELECT.
Si vous l’oubliez, vous aurez un joli message pour vous le rappeler : You must use SELECT WITH AGGREGATION_THRESHOLD for this query because a privacy policy has been set by a data owner.
De la même manière si vous voulez faire une sélection des données sans agrégation, du genre SELECT * : You must use GROUP BY or aggregation function in a SELECT WITH AGGREGATION_THRESHOLD query, but it was absent at [2:3].
Et dernier exemple, si vous voulez faire une agrégation en utilisant la colonne indiquée en “privacy” comme dimension :
SELECT WITH AGGREGATION_THRESHOLD
customerId,
SUM(turnover) AS globalTurnover
FROM datayoka-dev.DEMO.F_superHeroSlipperSalesView
GROUP BY ALL
;Résultat : You cannot GROUP BY privacy unit column when using SELECT WITH AGGREGATION_THRESHOLD.
Merci de votre lecture !
