TripPin partie 6 - Schéma

Ce tutoriel en plusieurs parties traite de la création d’une nouvelle extension de source de données pour Power Query. Le didacticiel est destiné à être effectué de manière séquentielle : chaque leçon s’appuie sur le connecteur créé dans les leçons précédentes, en ajoutant de manière incrémentielle de nouvelles fonctionnalités à votre connecteur.

Dans cette leçon, vous allez :

L’un des principaux avantages d’un service OData sur une API REST standard est sa définition $metadata. Le document $metadata décrit les données trouvées sur ce service, y compris le schéma de toutes ses entités (tables) et champs (colonnes). La OData.Feed fonction utilise cette définition de schéma pour définir automatiquement les informations de type de données. Par conséquent, au lieu d’obtenir tous les champs de texte et de nombre (comme vous le feriez depuis Json.Document), les utilisateurs finaux bénéficient de dates, de nombres entiers, d'heures, etc., offrant ainsi une expérience utilisateur globale améliorée.

De nombreuses API REST n’ont pas de moyen de déterminer par programme leur schéma. Dans ces cas, vous devez inclure des définitions de schéma dans votre connecteur. Dans cette leçon, vous définissez un schéma simple et codé en dur pour chacune de vos tables et appliquez le schéma sur les données que vous lisez à partir du service.

Dans l’ensemble, l’application d’un schéma sur les données retournées par votre connecteur présente plusieurs avantages, tels que :

• Définition des types de données corrects
• La suppression de colonnes qui n’ont pas besoin d’être affichées aux utilisateurs finaux (telles que les ID internes ou les informations d’état)
• S’assurer que chaque page de données a la même forme en ajoutant toutes les colonnes qui peuvent être manquantes dans une réponse (un moyen courant pour les API REST d’indiquer qu’un champ doit être null)

Affichage du schéma existant avec Table.Schema

Le connecteur créé dans la leçon précédente affiche trois tables du service TripPin : Airlines, Airportset People. Exécutez la requête suivante pour afficher la Airlines table :

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    data

Dans les résultats, quatre colonnes sont retournées :

• @odata.id
• @odata.editLink
• AirlineCode
• Nom

Les colonnes « @odata.* » font partie du protocole OData, et non quelque chose que vous souhaitez ou devez afficher aux utilisateurs finaux de votre connecteur. AirlineCode et Name sont les deux colonnes que vous souhaitez conserver. Si vous examinez le schéma de la table (à l’aide de la fonction pratique Table.Schema), toutes les colonnes de la table ont un type de données de Any.Type.

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

Table.Schema retourne de nombreuses métadonnées sur les colonnes d’une table, notamment les noms, les positions, les informations de type et de nombreuses propriétés avancées, telles que Precision, Scale et MaxLength. Les futures leçons fournissent des modèles de conception pour définir ces propriétés avancées, mais pour le moment, vous n’avez besoin que de vous soucier du type inscrit (TypeName), du type primitif (Kind) et de la valeur null de colonne (IsNullable).

Définition d’une table de schéma simple

Votre table de schémas va être composée de deux colonnes :

La table de schéma codée en dur pour Airlines définit ses colonnes AirlineCode et Name sur text et ressemble à ceci :

Airlines = #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}
    });

La Airports table comporte quatre champs que vous souhaitez conserver (y compris l’un des types record) :

Airports = #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}
    });

Enfin, la People table comporte sept champs, y compris des listes (Emails, AddressInfo), une colonne nullable (Gender) et une colonne avec un type inscrit (Concurrency).

People = #table({"Name", "Type"}, {
        {"UserName", type text},
        {"FirstName", type text},
        {"LastName", type text},
        {"Emails", type list},
        {"AddressInfo", type list},
        {"Gender", type nullable text},
        {"Concurrency", Int64.Type}
    })

Fonction d’assistance SchemaTransformTable

La SchemaTransformTable fonction d’assistance décrite dans cette section est utilisée pour appliquer des schémas sur vos données. Il prend les paramètres suivants :

La logique de cette fonction ressemble à ceci :

1. Déterminez s’il existe des colonnes manquantes dans la table source.
2. Déterminez s’il existe des colonnes supplémentaires.
3. Ignorer les colonnes structurées (de type list, recordet table) et les colonnes définies sur type any.
4. Utilisez Table.TransformColumnTypes pour définir chaque type de colonne.
5. Réorganisez les colonnes en fonction de l’ordre dans lequel elles apparaissent dans la table de schémas.
6. Définissez le type sur la table elle-même à l’aide de Value.ReplaceType.

Le code d’assistance suivant peut être copié et collé dans votre extension :

EnforceSchema.Strict = 1;               // Add any missing columns, remove extra columns, set table type
EnforceSchema.IgnoreExtraColumns = 2;   // Add missing columns, do not remove extra columns
EnforceSchema.IgnoreMissingColumns = 3; // Do not add or remove columns

SchemaTransformTable = (table as table, schema as table, optional enforceSchema as number) as table =>
    let
        // Default to EnforceSchema.Strict
        _enforceSchema = if (enforceSchema <> null) then enforceSchema else EnforceSchema.Strict,

        // Applies type transforms to a given table
        EnforceTypes = (table as table, schema as table) as table =>
            let
                map = (t) => if Type.Is(t, type list) or Type.Is(t, type record) or t = type any then null else t,
                mapped = Table.TransformColumns(schema, {"Type", map}),
                omitted = Table.SelectRows(mapped, each [Type] <> null),
                existingColumns = Table.ColumnNames(table),
                removeMissing = Table.SelectRows(omitted, each List.Contains(existingColumns, [Name])),
                primativeTransforms = Table.ToRows(removeMissing),
                changedPrimatives = Table.TransformColumnTypes(table, primativeTransforms)
            in
                changedPrimatives,

        // Returns the table type for a given schema
        SchemaToTableType = (schema as table) as type =>
            let
                toList = List.Transform(schema[Type], (t) => [Type=t, Optional=false]),
                toRecord = Record.FromList(toList, schema[Name]),
                toType = Type.ForRecord(toRecord, false)
            in
                type table (toType),

        // Determine if we have extra/missing columns.
        // The enforceSchema parameter determines what we do about them.
        schemaNames = schema[Name],
        foundNames = Table.ColumnNames(table),
        addNames = List.RemoveItems(schemaNames, foundNames),
        extraNames = List.RemoveItems(foundNames, schemaNames),
        tmp = Text.NewGuid(),
        added = Table.AddColumn(table, tmp, each []),
        expanded = Table.ExpandRecordColumn(added, tmp, addNames),
        result = if List.IsEmpty(addNames) then table else expanded,
        fullList =
            if (_enforceSchema = EnforceSchema.Strict) then
                schemaNames
            else if (_enforceSchema = EnforceSchema.IgnoreMissingColumns) then
                foundNames
            else
                schemaNames & extraNames,

        // Select the final list of columns.
        // These will be ordered according to the schema table.
        reordered = Table.SelectColumns(result, fullList, MissingField.Ignore),
        enforcedTypes = EnforceTypes(reordered, schema),
        withType = if (_enforceSchema = EnforceSchema.Strict) then Value.ReplaceType(enforcedTypes, SchemaToTableType(schema)) else enforcedTypes
    in
        withType;

Mise à jour du connecteur TripPin

Pour utiliser le nouveau code d’application du schéma, apportez les modifications suivantes à votre connecteur.

1. Définissez une table de schéma maître (SchemaTable) qui contient toutes vos définitions de schéma.
2. Mettez à jour le TripPin.Feed, GetPageet GetAllPagesByNextLink pour accepter un schema paramètre.
3. Appliquer votre schéma dans GetPage.
4. Mettez à jour votre code de table de navigation pour encapsuler chaque table avec un appel à une nouvelle fonction (GetEntity). Cette fonction vous donne plus de flexibilité pour manipuler les définitions de table à l’avenir.

Table de schéma maître

Maintenant, consolidez vos définitions de schéma dans une table unique et ajoutez une fonction d’assistance (GetSchemaForEntity) qui vous permet de rechercher la définition en fonction d’un nom d’entité (par exemple, GetSchemaForEntity("Airlines")).

SchemaTable = #table({"Entity", "SchemaTable"}, {
    {"Airlines", #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}
    })},    
    
    {"Airports", #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}
    })},

    {"People", #table({"Name", "Type"}, {
        {"UserName", type text},
        {"FirstName", type text},
        {"LastName", type text},
        {"Emails", type list},
        {"AddressInfo", type list},
        {"Gender", type nullable text},
        {"Concurrency", Int64.Type}
    })}
});

GetSchemaForEntity = (entity as text) as table => try SchemaTable{[Entity=entity]}[SchemaTable] otherwise error "Couldn't find entity: '" & entity &"'";

Ajout de la prise en charge du schéma aux fonctions de données

Ajoutez maintenant un paramètre facultatif schema aux fonctions TripPin.Feed, GetPage et GetAllPagesByNextLink. Ce paramètre vous permet de transmettre le schéma (lorsque vous le souhaitez) aux fonctions de pagination, où il est appliqué aux résultats que vous obtenez du service.

TripPin.Feed = (url as text, optional schema as table) as table => ...
GetPage = (url as text, optional schema as table) as table => ...
GetAllPagesByNextLink = (url as text, optional schema as table) as table => ...

Vous devez également mettre à jour tous les appels à ces fonctions afin de garantir que le schéma est transmis correctement.

Application du schéma

L’application réelle du schéma est effectuée dans votre GetPage fonction.

GetPage = (url as text, optional schema as table) as table =>
    let
        response = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),        
        body = Json.Document(response),
        nextLink = GetNextLink(body),
        data = Table.FromRecords(body[value]),
        // enforce the schema
        withSchema = if (schema <> null) then SchemaTransformTable(data, schema) else data
    in
        withSchema meta [NextLink = nextLink];

Ajout de la fonction GetEntity

La GetEntity fonction encapsule votre appel à TripPin.Feed. Il recherche une définition de schéma basée sur le nom de l’entité et génère l’URL de requête complète.

GetEntity = (url as text, entity as text) as table => 
    let
        fullUrl = Uri.Combine(url, entity),
        schemaTable = GetSchemaForEntity(entity),
        result = TripPin.Feed(fullUrl, schemaTable)
    in
        result;

Vous mettez ensuite à jour votre TripPinNavTable fonction pour appeler GetEntity, plutôt que d’effectuer tous les appels inline. L’avantage principal de cette mise à jour est qu’elle vous permet de continuer à modifier votre code de génération d’entité, sans avoir à toucher votre logique de table de navigation.

TripPinNavTable = (url as text) as table =>
    let
        entitiesAsTable = Table.FromList(RootEntities, Splitter.SplitByNothing()),
        rename = Table.RenameColumns(entitiesAsTable, {{"Column1", "Name"}}),
        // Add Data as a calculated column
        withData = Table.AddColumn(rename, "Data", each GetEntity(url, [Name]), type table),
        // Add ItemKind and ItemName as fixed text values
        withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
        withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
        // Indicate that the node should not be expandable
        withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical),
        // Generate the nav table
        navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        navTable;

Tout assembler

Une fois que toutes les modifications du code sont apportées, compilez et réexécutez la requête de test qui appelle Table.Schema pour la table Airlines.

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

Votre table Airlines comporte désormais uniquement les deux colonnes que vous avez définies dans son schéma :

Si vous exécutez le même code sur la table People...

let
    source = TripPin.Contents(),
    data = source{[Name="People"]}[Data]
in
    Table.Schema(data)

Notez que le type inscrit que vous avez utilisé (Int64.Type) a également été correctement défini.

Une chose importante à noter est que cette implémentation de SchemaTransformTable ne modifie pas les types des colonnes list et record, mais les colonnes Emails et AddressInfo sont toujours typées comme list. Ce comportement se produit, car Json.Document mappe correctement des tableaux JSON à des listes M et des objets JSON aux enregistrements M. Si vous développez la liste ou la colonne d’enregistrement dans Power Query, toutes les colonnes développées sont de type any. Les didacticiels futurs améliorent l’implémentation pour définir de manière récursive les informations de type pour les types complexes imbriqués.

Conclusion

Ce tutoriel a fourni un exemple d’implémentation pour appliquer un schéma sur les données JSON retournées par un service REST. Bien que cet exemple utilise un format simple de table de schéma codé en dur, l’approche peut être développée en créant dynamiquement une définition de table de schéma à partir d’une autre source, telle qu’un fichier de schéma JSON, ou un service de métadonnées/point de terminaison exposé par la source de données.

En plus de modifier les types de colonnes (et les valeurs), votre code définit également les informations de type correctes sur la table elle-même. La définition de ces informations de type bénéficie des performances lors de l’exécution à l’intérieur de Power Query, car l’expérience utilisateur tente toujours de déduire les informations de type pour afficher les files d’attente d’interface utilisateur appropriées à l’utilisateur final, et les appels d’inférence peuvent déclencher d’autres appels aux API de données sous-jacentes.

Si vous affichez la table People à l’aide du connecteur TripPin de la leçon précédente, toutes les colonnes ont une icône « type any » (même les colonnes qui contiennent des listes) :

Si vous exécutez la même requête avec le connecteur TripPin à partir de cette leçon, les informations de type s’affichent maintenant correctement.

Étapes suivantes

TripPin Partie 7 - Schéma avancé avec types M