Créer une analyse de table avec des règles métier SQL

Talend Real-time Big Data Platform Studio Guide utilisateur

EnrichVersion
6.4
EnrichProdName
Talend Real-Time Big Data Platform
task
Qualité et préparation de données
Création et développement
EnrichPlatform
Studio Talend

Vous pouvez définir des règles métier SQL basées sur des clauses WHERE et les ajouter en tant qu'indicateurs dans des analyses de tables. Vous pouvez également définir des seuils attendus sur la valeur de l'indicateur de la règle métier SQL. La plage définie est utilisée pour mesurer la qualité des donnés de la table sélectionnée.

Note

Il est également possible de créer une analyse avec des règles métier SQL sur des vues dans une base de données. La procédure est exactement la même que pour les tables. Pour plus d'informations, consultez Créer une analyse de table avec une règle métier SQL comprenant une condition de jointure.

Note

Lorsque vous utilisez le moteur Java pour exécuter une analyse de colonne(s) sur des ensembles de données volumineux ou contenant de nombreux problèmes, il est recommandé de configurer une limite de taille de la mémoire allouée pour exécuter l'analyse, puisqu'il est possible d'avoir une erreur Java heap space. Pour plus d'informations, consultez Définir le seuil maximal de la mémoire.

Créer une règle métier SQL

Les règles métier SQL peuvent être des règles simples contenant des clauses WHERE. Elles peuvent également comprendre des conditions de jointure et combiner les valeurs communes à différentes tables d'une base de données et fournir un ensemble de résultats.

Pour un exemple d'analyse de table avec une règle métier simple, consultez Créer une analyse de table avec une règle métier SQL simple. Pour un exemple d'analyse de table avec une règle métier comprenant une condition de jointure, consultez Créer une analyse de table avec une règle métier SQL comprenant une condition de jointure.

Prérequis : La perspective Profiling du studio doit être ouverte.

Créer la règle métier

  1. Dans la vue DQ Repository, développez successivement les nœuds Libraries et Rules.

  2. Cliquez-droit sur SQL.

  3. Dans le menu contextuel, sélectionnez New Business Rule afin d'ouvrir l'assistant [New Business Rule].

    Considérons par exemple que vous souhaitez créer une règle métier pour faire correspondre l'âge de tous les clients listés dans la colonne age de la table définie. Filtrez tous les enregistrements concernant l'âge afin d'identifier les personnes répondant aux critères spécifiés.

  4. Dans le champ Name, saisissez un nom pour cette nouvelle règle métier SQL.

    Note

    Il est recommandé de ne pas utiliser les caractères spéciaux suivants dans le nom de l'élément, notamment :

    "~", "!", "`", "#", "^", "&", "*", "\\", "/", "?", ":", ";", "\"", ".", "(", ")", "'", "¥", "'", """, "«", "»", "<", ">".

    Ces caractères seront remplacés par un "_" dans le système de fichiers. Vous risquez ainsi de créer des éléments en doublon.

  5. Définissez les autres métadonnées : Purpose, Description et Author (Objectif, description et nom de l'auteur) dans les champs correspondants puis cliquez sur Next.

  6. Dans le champ Where clause, saisissez la clause WHERE à utiliser dans l'analyse.

    Dans cet exemple, la clause WHERE est utilisée pour faire correspondre les enregistrements dont l'âge des clients est supérieur à 18.

  7. Cliquez sur Finish pour fermer l'assistant [New Business Rule].

    Un sous-dossier pour la nouvelle règle métier SQL s'affiche sous le dossier Rules dans la vue DQ Repository. L'éditeur de règle métier SQL s'ouvre avec les métadonnées définies.

    Note

    Dans l'éditeur de règles métier SQL, vous pouvez modifier la clause WHERE ou ajouter une nouvelle directement dans la vue Data quality rule.

  8. Si nécessaire, définissez une valeur dans le champ Criticality Level. Cela jouera le rôle d'indicateur pour mesurer l'importance de la règle métier SQL. Cette valeur est sauvegardée dans la base de données et peut être utilisée ultérieurement dans Talend Data Quality Portal.

    Pour plus d'informations concernant le portail, consultez le Guide utilisateur et administrateur de Talend Data Quality Portal. Pour plus d'informations concernant l'installation du portail, consultez le Guide d'installation Talend.

Créer une condition de jointure

Cette étape n'est pas obligatoire. Vous pouvez décider de créer une règle métier sans condition de jointure et de l'utiliser avec la clause WHERE dans l'analyse de table.

Pour un exemple d'analyse de table avec une règle métier simple, consultez Créer une analyse de table avec une règle métier SQL simple. Pour un exemple d'analyse de table avec une règle métier comprenant une condition de jointure, consultez Créer une analyse de table avec une règle métier SQL comprenant une condition de jointure.

  1. Dans l'éditeur de règle métier SQL, cliquez sur Join Condition pour ouvrir la vue correspondante.

  2. Cliquez sur le bouton [+] pour ajouter une ligne à la table Join Condition.

  3. Développez le nœud Metadata dans la vue DQ Repository et parcourez l'arborescence jusqu'aux colonnes sur lesquelles vous souhaitez créer la condition de jointure.

    La condition de jointure définit la relation entre une table A et une table B, en utilisant un opérateur de comparaison sur une colonne spécifique dans chaque table. Dans cet exemple, la condition de jointure compare la valeur "name" des tables Person et Person_Ref ayant une colonne name commune.

    Note

    Lorsque vous définissez la clause de la jointure, assurez-vous que les tables de la jointure n'aient pas de valeurs en doublon, afin que les résultats soient lisibles. Pour plus d'informations, consultez Créer une analyse de table avec une règle métier SQL comprenant une condition de jointure.

  4. Déposez les colonnes de la vue DQ Repository à la table Join Condition.

    Une boîte de dialogue s'ouvre et vous demande de choisir où placer la colonne : dans la TableA ou dans la TableB.

  5. Sélectionnez le signe souhaité dans la liste Operator puis sauvegardez vos modifications.

    Dans l'éditeur d'analyse, vous pouvez déposer cette nouvelle règle métier SQL dans une table contenant une colonne age. Lorsque vous exécutez l'analyse, la jointure de la seconde colonne est automatiquement effectuée.

    Avertissement

    La table à laquelle vous souhaitez ajouter la règle métier doit contenir au moins une des colonnes utilisées dans la règle métier SQL.

Pour plus d'informations concernant les règles métier SQL définies comme indicateurs dans une analyse de table, consultez Créer une analyse de table avec des règles métier SQL.

Editer une règle métier SQL

Prérequis : La perspective Profiling du studio doit être ouverte.

Pour éditer une règle métier SQL, procédez comme suit :

  1. Dans la vue DQ Repository, développez successivement les nœuds Libraries, Rules et SQL.

  2. Cliquez-droit sur la règle métier SQL que vous souhaitez ouvrir et sélectionnez Open dans le menu contextuel.

    L'éditeur de règle métier SQL s'ouvre et affiche les métadonnées de la règle.

  3. Modifiez les métadonnées de la règle métier ou la clause WHERE selon vos besoins.

  4. Cliquez sur l'icône de sauvegarde, en haut de l'éditeur, afin de sauvegarder vos modifications.

    La règle métier SQL est alors modifiée.

Créer une analyse de table avec une règle métier SQL simple

La perspective Profiling dans le studio vous permet de créer des analyses sur des tables ou des vues dans une base de données à l'aide de règles métier SQL. La procédure de création de ces analyses est la même pour les analyses de tables ou de vues.

Prérequis :

  • Au moins une règle métier SQL doit avoir été créée dans la perspective Profiling du studio. Pour plus d'informations concernant la création de règles métier SQL, consultez Créer une règle métier SQL.

  • Au moins une connexion à une base de données doit avoir été configurée dans la perspective Profiling du studio. Pour plus d'informations, consultez Se connecter à une base de données.

Dans cet exemple, vous souhaitez ajouter la règle métier SQL créée dans Créer une règle métier SQL à une table top_custom contenant une colonne age. Cette règle métier SQL va mettre en correspondance l'âge des clients afin de définir ceux dont l'âge est supérieur à 18.

Paramétrer l'analyse

  1. Dans la vue DQ Repository, développez le nœud Data Profiling.

  2. Cliquez-droit sur le dossier Analyses et sélectionnez New Analysis.

    L'assistant [Create New Analysis] s'ouvre.

  3. Dans le champ de filtre, commencez à saisir business rule analysis, sélectionnez Business Rule Analysis et cliquez sur Next. Si votre studio est en français, commencez à saisir analyse de règle métier.

  4. Dans le champ Name, saisissez un nom pour l'analyse.

    Note

    Il est recommandé de ne pas utiliser les caractères spéciaux suivants dans le nom de l'élément, notamment :

    "~", "!", "`", "#", "^", "&", "*", "\\", "/", "?", ":", ";", "\"", ".", "(", ")", "'", "¥", "'", """, "«", "»", "<", ">".

    Ces caractères seront remplacés par un "_" dans le système de fichiers. Vous risquez ainsi de créer des éléments en doublon.

  5. Définissez les métadonnées de l'analyse (objectif, description et nom de l'auteur) dans les champs Purpose, Description et Author. Cliquez sur Next.

Sélectionner la table que vous souhaitez analyser

  1. Développez DB Connections, parcourez l'arborescence jusqu'à la table à analyser et sélectionnez-la.

  2. Cliquez sur Finish pour fermer l'assistant [Create New Analysis].

    Note

    Vous pouvez sélectionner directement la règle de qualité de données que vous souhaitez ajouter à l'analyse en cliquant sur le bouton Next dans l'assistant [New Analysis], ou vous pouvez le faire plus tard dans la vue Analyzed Tables, comme décrit dans les étapes suivantes.

    Un dossier s'affiche pour la nouvelle analyse de table dans le dossier Analyses de la vue DQ Repository. L'éditeur d'analyse s'ouvre avec les métadonnées définies.

  3. Si nécessaire :

    • Cliquez sur Select Tables pour ouvrir la boîte de dialogue [Table Selection] et sélectionner la (les) table(s) à analyser.

      Vous pouvez filtrer la liste des tables ou colonnes en saisissant le texte de votre choix dans les champs Table filter ou Column filter, respectivement. Les listes affichent alors uniquement les tables/colonnes correspondant au texte saisi.

    • Sélectionnez une autre connexion dans la liste Connection afin de vous connecter à une base de données différente. Cette liste comprend toutes les connexions crées dans le Studio. Si les tables listées dans la vue Analyzed Tables n'existent pas dans la nouvelle connexion définie, un message d'avertissement s'affiche, vous permettant de continuer l'opération ou de l'annuler.

  4. Cliquez-droit sur l'une des colonnes listées dans la vue Analyzed Columns et sélectionnez Show in DQ Repository view. La colonne sélectionnée est automatiquement située sous la connexion correspondante dans l'arborescence.

Sélectionner la règle métier

  1. Cliquez sur l'icône à côté de la table à laquelle vous souhaitez ajouter la règle métier SQL.

    La boîte de dialogue [Business Rule Selector] s'ouvre.

  2. Développez le dossier Rules et cochez la case de la (des) règle(s) métier SQL prédéfinie(s) que vous souhaitez utiliser sur la (les) table(s) correspondante(s).

  3. Cliquez sur OK.

    La règle métier sélectionnée s'affiche sous le nom de la table dans la vue Analyzed Tables.

    Vous pouvez également déposer la règle métier directement de la vue DQ Repository dans la table dans l'éditeur d'analyse.

  4. Cliquez-droit sur la règle métier et sélectionnez View executed query.

    L'éditeur SQL s'ouvre dans le Studio pour afficher la requête.

  5. Si besoin, cliquez sur Data Filter dans l'éditeur d'analyse afin d'ouvrir la vue dans laquelle vous pouvez configurer un filtre sur les données de la (des) table(s) analysée(s).

  6. Sauvegardez l'analyse de table et appuyez sur F6 pour l'exécuter.

    Une fenêtre d'informations s'ouvre pour confirmer que l'opération est en cours. L'éditeur d'analyse passe à la vue Analysis Results.

    Tous les enregistrements d'âges de la table sélectionnée sont évalués par rapport à la règle métier SQL définie. Les résultats d'analyse comprennent deux diagrammes en barres : le premier est un indicateur de compte de lignes affichant le nombre de lignes dans la table analysée et le second est un indicateur de correspondance et non correspondance indiquant en rouge les enregistrements d'âge de l'ensemble de résultats analysés ne correspondant pas aux critères (âge inférieur à 18).

  7. Cliquez-droit sur les résultats de la règle métier dans la seconde table, ou cliquez-droit sur la barre du diagramme et sélectionnez :

    Option

    Pour...

    View valid rows

    accéder à une liste dans l'éditeur SQL de toutes les lignes valides par rapport au modèle utilisé sur la table sélectionnée.

    View invalid rows

    accéder à une liste dans l'éditeur SQL de toutes les lignes invalides par rapport au modèle utilisé dans la table sélectionnée.

    Analyze duplicates

    générer une analyse prête à utiliser analysant les doublons d'une table, s'il y en a, et donnant le nombre de lignes et de doublons. Pour plus d'informations, consultez Générer une analyse sur les résultats d'une jointure pour analyser les doublons.

    Generate Job

    générer un Job utilisant le processus ELT (Extract Load Transform) pour écrire les lignes valides ou invalides de la table analysées dans un fichier de sortie. Pour plus d'informations, consultez Récupérer les lignes valides/invalides dans une analyse de table .

    Vous pouvez également effectuer une analyse de table d'une manière plus directe et simplifiée. Pour plus d'informations, consultez Créer une analyse de table(s) avec une règle métier SQL via des raccourcis.

Créer une analyse de table avec une règle métier SQL comprenant une condition de jointure

Dans certains cas, vous devez analyser des tables ou des vues de bases de données à l'aide d'une règle métier SQL comprenant une clause de jointure combinant des enregistrements de deux tables d'une base de données. Cette clause de jointure compare les valeurs communes à deux colonnes et donne un ensemble de données de résultat. Ces données sont ensuite analysées par rapport à la règle métier.

Selon les données analysées et la clause de jointure, différents résultats de jointure sont possibles, par exemple #match + #no match > #row count,  #match + #no match < #row count ou #match + #no match = #row count.

L'exemple ci-dessous explique en détails un cas où l'ensemble de données dans le résultat de la jointure est plus volumineux que le nombre de lignes (#match + #no match > #row count), ce qui indique que les données traitées contiennent des doublons.

Prérequis :

  • Au moins une règle métier SQL doit avoir été créée dans la perspective Profiling du studio. Pour plus d'informations concernant la création de règles métier SQL, consultez Créer une règle métier SQL.

  • Au moins une connexion à une base de données doit avoir été définie dans la perspective Profiling du studio. Pour plus d'informations, consultez Se connecter à une base de données.

Dans cet exemple, ajoutez la règle métier SQL créée dans la Créer une règle métier SQL à une table Person contenant les colonnes age et name. Cette règle métier SQL met en correspondance l'âge des clients afin de définir lesquels ont plus de 18 ans. La règle métier possède également une condition de jointure comparant la valeur "name" des tables Person et Person_Ref en analysant la colonne name commune.

Voici une capture d'écran des deux tables :

Voici une capture d'écran du résultat de la condition de jointure entre les deux tables :

L'ensemble de résultats peut présenter des lignes en doublon, comme c'est le cas dans cet exemple. Dans ce cas, les résultats de l'analyse sont plus difficiles à comprendre. L'analyse n'analyse pas les lignes de la table répondant à la règle métier mais est exécutée sur l'ensemble de résultats donnés par la règle métier. Consultez la fin de la section pour des explications détaillées concernant les résultats d'analyse.

  1. Configurez l'analyse de table et sélectionnez la table que vous souhaitez analyser, comme décrit dans Créer une analyse de table avec une règle métier SQL simple.

    La table sélectionnée s'affiche dans la vue Analyzed Tables.

  2. Ajoutez la règle métier contenant la condition de jointure à la table sélectionnée, en cliquant sur l'icône à côté du nom de la table.

    Cette règle métier possède une condition de jointure comparant la valeur du nom ("name") de deux tables différentes en analysant une colonne commune. Pour plus d'informations concernant les règles métier SQL, consultez Créer une règle métier SQL.

  3. Sauvegardez l'analyse de table et appuyez sur F6 pour l'exécuter.

    Une fenêtre d'informations s'ouvre pour confirmer que l'opération est en cours. L'éditeur d'analyse passe à la vue Analysis Results.

    Tous les enregistrements d'âge dans la table sélectionnée sont évalués par rapport à la règle métier SQL définie. Les résultats retournés comprennent deux diagrammes en barre : le premier, relatif à l'indicateur Row Count indique le nombre de lignes analysées. Le second indique les lignes qui correspondent et les lignes qui ne correspondent pas. Dans ce diagramme, les enregistrements d'âge ne correspondant pas aux critères (âge inférieur à 18 ans) sont indiqués.

    Note

    Si une condition de jointure est utilisée dans la règle métier SQL, le nombre de lignes de la jointure (#Match + #No Match) peut être différent du nombre des lignes analysées (Row Count). Pour plus d'informations, consultez Créer une analyse de table avec une règle métier SQL comprenant une condition de jointure.

  4. Cliquez-droit sur la ligne Row Count de la première table et sélectionnez View rows.

    L'éditeur SQL s'ouvre dans le Studio pour afficher une liste de lignes analysées.

  5. Cliquez-droit sur les résultats de la règle métier dans la seconde table, ou cliquez-droit sur la barre de résultat dans le diagramme et sélectionnez :

    Option

    Pour...

    View valid rows

    accéder à une liste, dans l'éditeur SQL, de toutes les lignes valides mesurées par rapport au modèle utilisé sur la table sélectionnée.

    View invalid rows

    accéder à une liste, dans l'éditeur SQL, de toutes les lignes invalides mesurées par rapport au modèle utilisé pour la table sélectionnée.

    Analyze duplicates

    génère une analyse prête à l'emploi analysant les doublons dans la table et donnant le nombre de lignes et de doublons. Pour plus d'informations, consultez Générer une analyse sur les résultats d'une jointure pour analyser les doublons.

    Generate Job

    génère un Job utilisant le processus Extract Load Transform pour écrire les lignes valides ou invalides de la table analysée dans un fichier de sortie. Pour plus d'informations, consultez Récupérer les lignes valides/invalides dans une analyse de table .

    Ci-dessous se trouve la liste des lignes invalides dans la table analysée.

  6. Dans l'éditeur SQL, cliquez sur l'icône de sauvegarde dans la barre d'outils afin de sauvegarder la requête exécutée sur la règle métier SQL et la lister dans le dossier Libraries > Source Files de la vue DQ Repository.

    Pour plus d'informations, consultez Sauvegarder les requêtes exécutées sur les indicateurs.

Afin de mieux comprendre le diagramme en barre Business Rule Statistics dans les résultats d'analyse, procédez comme suit :

  1. Dans l'éditeur d'analyse, cliquez-droit sur la règle métier et sélectionnez View executed query.

    L'éditeur SQL s'ouvre dans le Studio.