Utilisation de fonctions de métrique des données¶

Créez votre propre DMF¶

S’il n’existe pas de fonction de mesure de la qualité des données du système (DMF) qui puisse effectuer vos contrôles de qualité des données, vous pouvez utiliser la commande CREATE DATA METRIC FUNCTION pour créer votre propre DMF.

Exemple : DMF définie par l’utilisateur avec un seul argument de table

Créer une DMF qui appelle la fonction COUNT pour renvoyer le nombre total de lignes contenant des nombres positifs dans les trois premières colonnes de la table :

CREATE OR REPLACE DATA METRIC FUNCTION governance.dmfs.count_positive_numbers(
  arg_t TABLE(
    arg_c1 NUMBER,
    arg_c2 NUMBER,
    arg_c3 NUMBER
  )
)
RETURNS NUMBER
AS
$$
  SELECT
    COUNT(*)
  FROM arg_t
  WHERE
    arg_c1>0
    AND arg_c2>0
    AND arg_c3>0
$$;

Copy

Exemple : Utilisation de plusieurs arguments de table pour effectuer des contrôles de référence

Une DMF définie par l’utilisateur peut avoir plusieurs arguments acceptant une table. Lorsque vous ajoutez la DMF à une table, cette table est utilisée comme premier argument. Si un argument supplémentaire accepte une table, vous devez également spécifier le nom complet de la seconde table. Cette capacité simplifie l’intégrité référentielle, la mise en correspondance et la comparaison, ou le contrôle conditionnel entre différents ensembles de données.

Supposons que vous souhaitiez valider l’intégrité référentielle telle qu’elle est définie par une relation clé primaire/clé étrangère. Dans ce cas, vous pouvez créer une DMF pour valider que tous les enregistrements d’une table source ont des enregistrements correspondants dans la table référencée. La DMF suivante, définie par l’utilisateur, renvoie le nombre d’enregistrements pour lesquels la valeur d’une colonne d’une table n’a pas de valeur correspondante dans la colonne d’une autre table :

CREATE OR REPLACE DATA METRIC FUNCTION governance.dmfs.referential_check(
  arg_t1 TABLE (arg_c1 INT), arg_t2 TABLE (arg_c2 INT))
RETURNS NUMBER AS
 'SELECT COUNT(*) FROM arg_t1
  WHERE arg_c1 NOT IN (SELECT arg_c2 FROM arg_t2)';

Copy

Supposons maintenant que vous souhaitiez vérifier que chaque commande, identifiée par son sp_id, dans la table salesorders, est mappée à un sp_id dans la table salespeople. Vous pouvez ajouter la DMF à la table salesorders tout en spécifiant la table salespeople comme autre argument de la table.

ALTER TABLE salesorders
  ADD DATA METRIC FUNCTION governance.dmfs.referential_check
    ON (sp_id, TABLE (my_db.sch1.salespeople(sp_id)));

Copy

La sortie renvoie le nombre de lignes de la table salesorders dont la colonne sp_id contient une valeur qui n’apparaît pas dans la colonne sp_id de la table salespeople. Une valeur supérieure à 0 indique qu’il y a des valeurs sp_id dans salesorders qui ne sont pas mappées à des enregistrements dans salespeople.

Appeler manuellement votre DMF¶

Utilisez la syntaxe suivante pour appeler une DMF :

SELECT <data_metric_function>(<query>)

Où :

data_metric_function

Spécifie une DMF définie par le système ou par l’utilisateur.

query

Spécifie une requête SQL sur une table ou une vue.

Les colonnes de la requête doivent correspondre aux arguments des colonnes dans la signature de DMF.

Appelez directement la DMF et affichez les résultats :

SELECT governance.dmfs.count_positive_numbers(
    SELECT c1,
           c2,
           c3
    FROM t
  );

Lorsque vous définissez une DMF sur une table ou une vue et que vous planifiez son exécution, vous pouvez effectuer une requête sur la vue construite à partir de la table d’événements pour afficher les résultats. Pour plus d’informations, voir Afficher les résultats de la DMF.

Ajouter ou supprimer une DMF sur une table ou une vue¶

Utilisez une commande ALTER TABLE ou ALTER VIEW pour effectuer les opérations suivantes :

• Ajouter une DMF à une table à l’aide de ALTER TABLE.

  ALTER TABLE t
    ADD DATA METRIC FUNCTION governance.dmfs.count_positive_numbers
      ON (c1, c2, c3);
  

  Copy

• Supprimez une DMF d’une table à l’aide d’une commande ALTER TABLE.

  ALTER TABLE t
    DROP DATA METRIC FUNCTION governance.dmfs.count_positive_numbers
      ON (c1, c2, c3);
  

  Copy

Planifiez l’exécution de vos DMFs¶

Vous pouvez planifier l’exécution de vos DMFs pour automatiser les mesures de qualité des données sur une table.

Snowflake utilise le rôle de propriétaire de la table, qui est le rôle disposant du privilège OWNERSHIP sur la table, pour appeler la DMF planifiée. Le rôle de propriétaire de la table doit disposer de ces attributions :

• Le privilège USAGE sur la DMF définie par l’utilisateur et le privilège USAGE sur la base de données et le schéma qui stockent la DMF définie par l’utilisateur.

• Le rôle de base de données DATA_METRIC_USER si la DMF est une DMF système.

• Le privilège EXECUTE DATA METRIC FUNCTION sur le compte.

Le paramètre d’objet DATA_METRIC_SCHEDULE d’une table, d’une vue ou d’une vue matérialisée vous permet de planifier l’exécution de vos DMFs. Toutes les fonctions de métrique des données de la table ou de la vue suivent la même planification.

Il existe trois façons de planifier l’exécution de votre DMF :

• Définissez la DMF pour qu’elle s’exécute après un certain nombre de minutes.

• Utilisez une expression cron pour planifier l’exécution de la DMF à une fréquence particulière.

• Utilisez un déclencheur pour planifier l’exécution de DMF en cas de modification d’une DML dans la table, par exemple l’insertion d’une nouvelle ligne dans la table. Cependant :

  • Le reclustering des tables ne déclenche pas l’exécution d’une DMF.

  • L’approche de déclenchement n’est disponible que pour certains types de tables. Pour plus d’informations, voir ALTER TABLE … SET DATA_METRIC_SCHEDULE.

Par exemple :

Réglez la planification de la fonction de métrique des données de sorte qu’elle s’exécute toutes les 5 minutes :

ALTER TABLE hr.tables.empl_info SET
  DATA_METRIC_SCHEDULE = '5 MINUTE';

Copy

Réglez la planification de la fonction de métrique des données de sorte qu’elle s’exécute tous les jours à 8:00 AM :

ALTER TABLE hr.tables.empl_info SET
  DATA_METRIC_SCHEDULE = 'USING CRON 0 8 * * * UTC';

Copy

Réglez la planification de la fonction de métrique des données de sorte qu’elle s’exécute à 8:00 AM les jours de la semaine uniquement :

ALTER TABLE hr.tables.empl_info SET
  DATA_METRIC_SCHEDULE = 'USING CRON 0 8 * * MON,TUE,WED,THU,FRI UTC';

Copy

Réglez la planification de la fonction de métrique des données de sorte qu’elle s’exécute trois fois par jour à 06:00, 12:00, et 18:00 UTC :

ALTER TABLE hr.tables.empl_info SET
  DATA_METRIC_SCHEDULE = 'USING CRON 0 6,12,18 * * * UTC';

Copy

Réglez la fonction de métrique des données de sorte qu’elle s’exécute lorsqu’une opération DML générale telle que l’insertion d’une nouvelle ligne modifie la table :

ALTER TABLE hr.tables.empl_info SET
  DATA_METRIC_SCHEDULE = 'TRIGGER_ON_CHANGES';

Copy

Vous pouvez utiliser la commande SHOW PARAMETERS pour visualiser la planification d’une DMF pour un objet de table pris en charge.

SHOW PARAMETERS LIKE 'DATA_METRIC_SCHEDULE' IN TABLE hr.tables.empl_info;

Copy

+----------------------+--------------------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+
| key                  | value                          | default | level | description                                                                                                                  | type   |
+----------------------+--------------------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+
| DATA_METRIC_SCHEDULE | USING CRON 0 6,12,18 * * * UTC |         | TABLE | Specify the schedule that data metric functions associated to the table must be executed in order to be used for evaluation. | STRING |
+----------------------+--------------------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+

Pour les objets de vue et de vue matérialisée, indiquez TABLE comme domaine d’objet et vérifiez la planification comme suit :

SHOW PARAMETERS LIKE 'DATA_METRIC_SCHEDULE' IN TABLE mydb.public.my_view;

Copy

Afficher les résultats de la DMF¶

Pour voir les résultats de la DMF, vous devez d’abord gérer l’accès aux résultats, puis choisir comment afficher les résultats :

• Gestion de l’accès aux résultats de la DMF

• Options

Voir les propriétés d’une DMF¶

Décrivez la DMF pour en voir les propriétés :

DESC FUNCTION governance.dmfs.count_positive_numbers(
  TABLE(
    NUMBER, NUMBER, NUMBER
  )
);

+-----------+---------------------------------------------------------------------+
| property  | value                                                               |
+-----------+---------------------------------------------------------------------+
| signature | (ARG_T TABLE(ARG_C1 NUMBER, ARG_C2 NUMBER, ARG_C3 NUMBER))          |
| returns   | NUMBER(38,0)                                                        |
| language  | SQL                                                                 |
| body      | SELECT COUNT(*) FROM arg_t WHERE arg_c1>0 AND arg_c2>0 AND arg_c3>0 |
+-----------+---------------------------------------------------------------------+

Sécuriser la DMF¶

Vous pouvez utiliser la commande ALTER FUNCTION pour sécuriser un DMF. Pour plus d’informations sur la signification d’une fonction en termes de sécurité, voir Protection des informations sensibles avec les UDFs et les procédures stockées sécurisées.

ALTER FUNCTION governance.dmfs.count_positive_numbers(
 TABLE(
   NUMBER,
   NUMBER,
   NUMBER
))
SET SECURE;

Dresser la liste de vos DMFs¶

Utilisez la commande SHOW DATA METRIC FUNCTIONS ou SHOW FUNCTIONS pour dresser la liste des DMFs de votre compte, votre base de données ou votre schéma :

SHOW DATA METRIC FUNCTIONS IN ACCOUNT;

Vous pouvez également interroger la vue Information Schema FUNCTIONS ou la vue Account Usage FUNCTIONS pour obtenir la liste de vos DMFs dans la base de données spécifiée ou dans votre compte.

La colonne is_data_metric s’il s’agit d’une fonction DMF.

Définir une balise sur une DMF¶

Utilisez la commande ALTER FUNCTION pour définir une balise sur une DMF :

ALTER FUNCTION governance.dmfs.count_positive_numbers(
  TABLE(NUMBER, NUMBER, NUMBER))
  SET TAG governance.tags.quality = 'counts';

Suivre les références des DMF¶

Vous pouvez interroger la vue DATA_METRIC_FUNCTION_REFERENCES Account Usage pour voir les références DMF dans votre compte. Vous pouvez également appeler la fonction de table DATA_METRIC_FUNCTION_REFERENCES Information Schema pour identifier les DMFs associées à une table ou à une vue donnée ou les tables ou vues associées à une DMF donnée.

Par exemple :

Renvoie une ligne pour chaque objet (table ou vue) pour lequel la DMF nommée count_positive_numbers a été définie sur cette table ou cette vue :

USE DATABASE governance;
USE SCHEMA INFORMATION_SCHEMA;
SELECT *
  FROM TABLE(
    INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
      METRIC_NAME => 'governance.dmfs.count_positive_numbers'
    )
  );

Copy

Renvoie une ligne pour chaque DMF affectée à la table nommée hr.tables.empl_info :

USE DATABASE governance;
USE SCHEMA INFORMATION_SCHEMA;
SELECT *
  FROM TABLE(
    INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
      REF_ENTITY_NAME => 'hr.tables.empl_info',
      REF_ENTITY_DOMAIN => 'table'
    )
  );

Copy

Supprimer la DMF du système¶

Supprimez la DMF du système :

DROP FUNCTION governance.dmfs.count_positive_numbers(
  TABLE(
    NUMBER, NUMBER, NUMBER
  )
);

Limitations¶

Pour plus d’informations, consultez la section sur les limitations de la qualité des données.