TripPin partie 10 - Pliage de requête de base

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 :

• $top
• $skip
• $count
• $select
• $orderby

L’une des fonctionnalités puissantes du langage M est sa capacité à envoyer (push) la transformation à une ou plusieurs sources de données sous-jacentes. Cette fonctionnalité est appelée repli de requête (d’autres outils/technologies font également référence à une fonction similaire en tant que pushdown de prédicat ou délégation de requête).

Lors de la création d’un connecteur personnalisé qui utilise une fonction M avec des fonctionnalités de pliage de requêtes intégrées, telles que OData.Feed ou Odbc.DataSource, votre connecteur hérite automatiquement de cette fonctionnalité gratuitement.

Ce didacticiel réplique le comportement de repli de requête intégré pour OData en implémentant des gestionnaires de fonctions pour la fonction Table.View . Cette partie du didacticiel implémente certains des gestionnaires plus faciles à implémenter (c’est-à-dire ceux qui ne nécessitent pas d’analyse d’expression et de suivi d’état).

Pour en savoir plus sur les fonctionnalités de requête qu’un service OData peut offrir, accédez aux conventions d’URL OData v4.

Accédez à Vue d’ensemble de l’évaluation des requêtes et du pliage des requêtes dans Power Query pour plus d’informations sur le pliage des requêtes.

Utilisation de Table.View

La fonction Table.View permet à un connecteur personnalisé de remplacer les gestionnaires de transformation par défaut pour votre source de données. Une implémentation de Table.View fournit une fonction pour un ou plusieurs des gestionnaires pris en charge. Si un gestionnaire n’est pas implémenté ou retourne une error valeur pendant l’évaluation, le moteur M revient à son gestionnaire par défaut.

Lorsqu’un connecteur personnalisé utilise une fonction qui ne prend pas en charge le pliage implicite des requêtes, comme Web.Contents, les gestionnaires de transformation par défaut sont toujours effectués localement. Si l’API REST à laquelle vous vous connectez prend en charge les paramètres de requête dans le cadre de la requête, Table.View vous permet d’ajouter des optimisations qui permettent au travail de transformation d’être envoyées au service.

La fonction Table.View a la signature suivante :

Table.View(table as nullable table, handlers as record) as table

Votre implémentation encapsule votre fonction principale de source de données. Il existe deux gestionnaires requis pour Table.View :

• GetType: retourne le résultat attendu table type de la requête.
• GetRows: retourne le résultat réel table de votre fonction de source de données.

L’implémentation la plus simple serait similaire à l’exemple suivant :

TripPin.SuperSimpleView = (url as text, entity as text) as table =>
    Table.View(null, [
        GetType = () => Value.Type(GetRows()),
        GetRows = () => GetEntity(url, entity)
    ]);

Mettez à jour la TripPinNavTable fonction pour appeler TripPin.SuperSimpleView plutôt que GetEntity:

withData = Table.AddColumn(rename, "Data", each TripPin.SuperSimpleView(url, [Name]), type table),

Si vous réexécutez les tests unitaires, le comportement de votre fonction n’est pas modifié. Dans ce cas, votre implémentation Table.View passe simplement l’appel à GetEntity. Comme vous n’avez pas encore implémenté de gestionnaires de transformation, le paramètre d’origine url reste inchangé.

Implémentation initiale de Table.View

L’implémentation précédente de Table.View est simple, mais pas très utile. L’implémentation suivante est utilisée comme base de référence : elle n’implémente aucune fonctionnalité de pliage, mais a la structure dont vous avez besoin pour le faire.

TripPin.View = (baseUrl as text, entity as text) as table =>
    let
        // Implementation of Table.View handlers.
        //
        // We wrap the record with Diagnostics.WrapHandlers() to get some automatic
        // tracing if a handler returns an error.
        //
        View = (state as record) => Table.View(null, Diagnostics.WrapHandlers([
            // Returns the table type returned by GetRows()
            GetType = () => CalculateSchema(state),

            // Called last - retrieves the data from the calculated URL
            GetRows = () => 
                let
                    finalSchema = CalculateSchema(state),
                    finalUrl = CalculateUrl(state),

                    result = TripPin.Feed(finalUrl, finalSchema),
                    appliedType = Table.ChangeType(result, finalSchema)
                in
                    appliedType,

            //
            // Helper functions
            //
            // Retrieves the cached schema. If this is the first call
            // to CalculateSchema, the table type is calculated based on
            // the entity name that was passed into the function.
            CalculateSchema = (state) as type =>
                if (state[Schema]? = null) then
                    GetSchemaForEntity(entity)
                else
                    state[Schema],

            // Calculates the final URL based on the current state.
            CalculateUrl = (state) as text => 
                let
                    urlWithEntity = Uri.Combine(state[Url], state[Entity])
                in
                    urlWithEntity
        ]))
    in
        View([Url = baseUrl, Entity = entity]);

Si vous examinez l’appel à Table.View, il existe une fonction wrapper supplémentaire autour de l’enregistrement handlers—Diagnostics.WrapHandlers. Cette fonction d’assistance se trouve dans le module Diagnostics (introduit dans la leçon ajout de diagnostics ) et vous fournit un moyen utile de suivre automatiquement les erreurs générées par des gestionnaires individuels.

Les fonctions GetType et GetRows sont mises à jour pour utiliser deux nouvelles fonctions d'auxiliaire : CalculateSchema et CalculateUrl. À l’heure actuelle, les implémentations de ces fonctions sont assez simples. Notez qu’ils contiennent des parties de ce qui a été fait précédemment par la GetEntity fonction.

Enfin, notez que vous définissez une fonction interne (View) qui accepte un state paramètre. Lorsque vous implémentez davantage de gestionnaires, ils appellent de manière récursive la fonction interne View, mettent à jour et transmettent state au fur et à mesure.

Mettez à jour la fonction TripPinNavTable une fois de plus, en remplaçant l'appel à TripPin.SuperSimpleView par un appel à la nouvelle fonction TripPin.View, puis relancez les tests unitaires. Il n’existe pas encore de nouvelles fonctionnalités, mais vous disposez maintenant d’une base de référence solide pour les tests.

Implémentation de la fusion des requêtes

Étant donné que le moteur M revient automatiquement au traitement local lorsqu’une requête ne peut pas être pliée, vous devez effectuer des étapes supplémentaires pour vérifier que vos gestionnaires Table.View fonctionnent correctement.

La façon manuelle de valider le comportement de pliage consiste à surveiller les demandes d’URL que vos tests unitaires effectuent à l’aide d’un outil comme Fiddler. La journalisation des diagnostics que vous avez ajoutée à TripPin.Feed émet l'URL complète exécutée, qui devrait inclure les paramètres de chaîne de requête OData ajoutés par vos gestionnaires.

Un moyen automatisé de valider le pliage des requêtes consiste à forcer l’exécution de votre test unitaire à échouer si une requête ne se plie pas entièrement. Pour que le test unitaire échoue lorsqu’une requête ne se plie pas complètement, ouvrez les propriétés du projet et définissez Error on Folding Failure to True. Avec ce paramètre activé, toute requête nécessitant un traitement local entraîne l’erreur suivante :

We couldn't fold the expression to the source. Please try a simpler expression.

Vous pouvez tester cette modification en ajoutant un nouveau Fact dans votre fichier de test unitaire contenant une ou plusieurs transformations de table.

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
)

Les sections restantes de ce didacticiel ajoutent chacun un nouveau gestionnaire Table.View . Vous adoptez une approche TDD (Test Driven Development), où vous ajoutez d’abord des tests unitaires défaillants, puis implémentez le code M pour les résoudre.

Les sections de gestionnaire suivantes décrivent les fonctionnalités fournies par le gestionnaire, la syntaxe de requête équivalente OData, les tests unitaires et l’implémentation. À l'aide du code de structure décrit précédemment, chaque implémentation de gestionnaire nécessite deux modifications :

• Ajout du gestionnaire à Table.View qui met à jour l’enregistrement state .
• Modifier CalculateUrl pour récupérer les valeurs de state et les ajouter aux paramètres de l'URL et/ou de la chaîne de requête.

Gestion de Table.FirstN avec OnTake

Le OnTake gestionnaire reçoit un paramètre count, qui est le nombre maximal de lignes à prendre de GetRows. En termes OData, vous pouvez le traduire en paramètre de requête $top .

Vous utilisez les tests unitaires suivants :

// Query folding tests
Fact("Fold $top 1 on Airlines", 
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ), 
    Table.FirstN(Airlines, 1)
),
Fact("Fold $top 0 on Airports", 
    #table( type table [Name = text, IataCode = text, Location = record] , {} ), 
    Table.FirstN(Airports, 0)
),

Ces tests utilisent Table.FirstN pour filtrer le jeu de résultats sur le premier nombre X de lignes. Si vous exécutez ces tests avec Error on Folding Failure défini False sur (valeur par défaut), les tests doivent réussir, mais si vous exécutez Fiddler (ou vérifiez les journaux de trace), notez que la demande que vous envoyez ne contient aucun paramètre de requête OData.

Si vous définissez Error on Folding Failure sur True, les tests échouent avec l’erreur Please try a simpler expression. . Pour corriger cette erreur, vous devez définir votre premier gestionnaire Table.View pour OnTake.

Le OnTake gestionnaire ressemble au code suivant :

OnTake = (count as number) =>
    let
        // Add a record with Top defined to our state
        newState = state & [ Top = count ]
    in
        @View(newState),

La CalculateUrl fonction est mise à jour pour extraire la Top valeur de l’enregistrement state et définir le paramètre approprié dans la chaîne de requête.

// Calculates the final URL based on the current state.
CalculateUrl = (state) as text => 
    let
        urlWithEntity = Uri.Combine(state[Url], state[Entity]),

        // Uri.BuildQueryString requires that all field values
        // are text literals.
        defaultQueryString = [],

        // Check for Top defined in our state
        qsWithTop =
            if (state[Top]? <> null) then
                // add a $top field to the query string record
                defaultQueryString & [ #"$top" = Number.ToText(state[Top]) ]
            else
                defaultQueryString,

        encodedQueryString = Uri.BuildQueryString(qsWithTop),
        finalUrl = urlWithEntity & "?" & encodedQueryString
    in
        finalUrl

Lorsque vous réexécutez les tests unitaires, notez que l’URL à laquelle vous accédez contient maintenant le $top paramètre. En raison de l’encodage d’URL, $top apparaît comme %24top, mais le service OData est suffisamment intelligent pour le convertir automatiquement.

Gestion de Table.Skip avec OnSkip

Le OnSkip gestionnaire est beaucoup comme OnTake. Il reçoit un count paramètre, qui correspond au nombre de lignes à ignorer du jeu de résultats. Ce gestionnaire se traduit correctement en paramètre de requête OData $skip.

Tests unitaires :

// OnSkip
Fact("Fold $skip 14 on Airlines",
    #table( type table [AirlineCode = text, Name = text] , {{"EK", "Emirates"}} ), 
    Table.Skip(Airlines, 14)
),
Fact("Fold $skip 0 and $top 1",
    #table( type table [AirlineCode = text, Name = text] , {{"AA", "American Airlines"}} ),
    Table.FirstN(Table.Skip(Airlines, 0), 1)
),

Implémentation:

// OnSkip - handles the Table.Skip transform.
// The count value should be >= 0.
OnSkip = (count as number) =>
    let
        newState = state & [ Skip = count ]
    in
        @View(newState),

Mises à jour correspondantes à CalculateUrl:

qsWithSkip = 
    if (state[Skip]? <> null) then
        qsWithTop & [ #"$skip" = Number.ToText(state[Skip]) ]
    else
        qsWithTop,

Pour plus d’informations, accédez à Table.Skip.

Utilisation de Table.SelectColumns avec OnSelectColumns

Le OnSelectColumns gestionnaire est appelé lorsque l’utilisateur sélectionne ou supprime des colonnes du jeu de résultats. Le gestionnaire reçoit une list de valeurs text, représentant une ou plusieurs colonnes à sélectionner.

En termes OData, cette opération est mappée à l’option de requête $select .

L'avantage de la sélection de colonnes pliables devient apparent lorsque vous traitez des tableaux avec de nombreuses colonnes. L’opérateur $select supprime les colonnes non sélectionnées du jeu de résultats, ce qui entraîne des requêtes plus efficaces.

Tests unitaires :

// OnSelectColumns
Fact("Fold $select single column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode"}), 1)
),
Fact("Fold $select multiple column", 
    #table( type table [UserName = text, FirstName = text, LastName = text],{{"russellwhyte", "Russell", "Whyte"}}), 
    Table.FirstN(Table.SelectColumns(People, {"UserName", "FirstName", "LastName"}), 1)
),
Fact("Fold $select with ignore column", 
    #table( type table [AirlineCode = text] , {{"AA"}} ),
    Table.FirstN(Table.SelectColumns(Airlines, {"AirlineCode", "DoesNotExist"}, MissingField.Ignore), 1)
),

Les deux premiers tests sélectionnent différents nombres de colonnes avec Table.SelectColumns et incluent un appel Table.FirstN pour simplifier le cas de test.

Le troisième test utilise l’option MissingField.Ignore , qui indique au moteur M d’ignorer les colonnes sélectionnées qui n’existent pas dans le jeu de résultats. Le OnSelectColumns gestionnaire n’a pas besoin de vous soucier de cette option : le moteur M le gère automatiquement (autrement dit, les colonnes manquantes ne sont pas incluses dans la columns liste).

L’implémentation pour OnSelectColumns effectue deux choses :

• Ajoute la liste des colonnes sélectionnées au state.
• Recalcule la Schema valeur pour que vous puissiez définir le type de table approprié.

OnSelectColumns = (columns as list) =>
    let
        // get the current schema
        currentSchema = CalculateSchema(state),
        // get the columns from the current schema (which is an M Type value)
        rowRecordType = Type.RecordFields(Type.TableRow(currentSchema)),
        existingColumns = Record.FieldNames(rowRecordType),
        // calculate the new schema
        columnsToRemove = List.Difference(existingColumns, columns),
        updatedColumns = Record.RemoveFields(rowRecordType, columnsToRemove),
        newSchema = type table (Type.ForRecord(updatedColumns, false))
    in
        @View(state & 
            [ 
                SelectColumns = columns,
                Schema = newSchema
            ]
        ),

CalculateUrl est mis à jour pour récupérer la liste des colonnes de l’état et les combiner (avec un séparateur) pour le $select paramètre.

// Check for explicitly selected columns
qsWithSelect =
    if (state[SelectColumns]? <> null) then
        qsWithSkip & [ #"$select" = Text.Combine(state[SelectColumns], ",") ]
    else
        qsWithSkip,

Gestion de Table.Sort avec OnSort

Le OnSort gestionnaire reçoit une liste d’enregistrements de type :

type [ Name = text, Order = Int16.Type ]

Chaque enregistrement contient un Name champ, indiquant le nom de la colonne et un Order champ égal à Order.Ascending ou Order.Descending.

En termes OData, cette opération est mappée à l’option de requête $orderby . La syntaxe $orderby comporte le nom de colonne suivi de asc ou desc pour indiquer l’ordre croissant ou décroissant. Lorsque vous triez sur plusieurs colonnes, les valeurs sont séparées par une virgule. Si le columns paramètre contient plusieurs éléments, il est important de conserver l’ordre dans lequel ils apparaissent.

Tests unitaires :

// OnSort
Fact("Fold $orderby single column",
    #table( type table [AirlineCode = text, Name = text], {{"TK", "Turkish Airlines"}}),
    Table.FirstN(Table.Sort(Airlines, {{"AirlineCode", Order.Descending}}), 1)
),
Fact("Fold $orderby multiple column",
    #table( type table [UserName = text], {{"javieralfred"}}),
    Table.SelectColumns(Table.FirstN(Table.Sort(People, {{"LastName", Order.Ascending}, {"UserName", Order.Descending}}), 1), {"UserName"})
)

Implémentation:

// OnSort - receives a list of records containing two fields: 
//    [Name]  - the name of the column to sort on
//    [Order] - equal to Order.Ascending or Order.Descending
// If there are multiple records, the sort order must be maintained.
//
// OData allows you to sort on columns that do not appear in the result
// set, so we do not have to validate that the sorted columns are in our 
// existing schema.
OnSort = (order as list) =>
    let
        // This will convert the list of records to a list of text,
        // where each entry is "<columnName> <asc|desc>"
        sorting = List.Transform(order, (o) => 
            let
                column = o[Name],
                order = o[Order],
                orderText = if (order = Order.Ascending) then "asc" else "desc"
            in
                column & " " & orderText
        ),
        orderBy = Text.Combine(sorting, ", ")
    in
        @View(state & [ OrderBy = orderBy ]),

Mises à jour de CalculateUrl:

qsWithOrderBy = 
    if (state[OrderBy]? <> null) then
        qsWithSelect & [ #"$orderby" = state[OrderBy] ]
    else
        qsWithSelect,

Utilisation de Table.RowCount avec GetRowCount

Contrairement aux autres gestionnaires de requêtes que vous implémentez, le GetRowCount gestionnaire retourne une valeur unique , le nombre de lignes attendues dans le jeu de résultats. Dans une requête M, cette valeur est généralement le résultat de la transformation Table.RowCount .

Vous avez quelques options différentes sur la façon de gérer cette valeur dans le cadre d’une requête OData :

• Paramètre de requête $count, qui retourne le nombre en tant que champ distinct dans le jeu de résultats.
• Segment de chemin /$count, qui retourne uniquement le nombre total, sous forme de valeur scalaire.

L’inconvénient de l’approche des paramètres de requête est que vous devez toujours envoyer la requête entière au service OData. Étant donné que le nombre revient inline dans le cadre du jeu de résultats, vous devez traiter la première page de données du jeu de résultats. Bien que ce processus soit encore plus efficace que de lire l’ensemble du jeu de résultats et de compter les lignes, il est probablement plus efficace que vous ne le souhaitez.

L’avantage de l’approche de segment de chemin est que vous ne recevez qu’une seule valeur scalaire dans le résultat. Cette approche rend l’ensemble de l’opération beaucoup plus efficace. Toutefois, comme décrit dans la spécification OData, le segment de /$count chemin retourne une erreur si vous incluez d’autres paramètres de requête, tels que $top ou $skip, qui limite son utilité.

Dans ce tutoriel, vous avez implémenté le GetRowCount gestionnaire à l’aide de l’approche de segment de chemin. Pour éviter les erreurs que vous obtenez si d’autres paramètres de requête sont inclus, vous avez vérifié d’autres valeurs d’état et retourné une « erreur non implémentée » (...) si vous en avez trouvé un. Le renvoi d’une erreur à partir d’un gestionnaire Table.View indique au moteur M que l’opération ne peut pas être optimisée et qu'il faut alors se rabattre sur le gestionnaire par défaut (ce qui, dans ce cas, consiste à compter le nombre total de lignes).

Tout d’abord, ajoutez un test unitaire :

// GetRowCount
Fact("Fold $count", 15, Table.RowCount(Airlines)),

Étant donné que le segment de /$count chemin retourne une valeur unique (au format brut/texte) plutôt qu’un jeu de résultats JSON, vous devez également ajouter une nouvelle fonction interne (TripPin.Scalar) pour effectuer la requête et gérer le résultat.

// Similar to TripPin.Feed, but is expecting back a scalar value.
// This function returns the value from the service as plain text.
TripPin.Scalar = (url as text) as text =>
    let
        _url = Diagnostics.LogValue("TripPin.Scalar url", url),

        headers = DefaultRequestHeaders & [
            #"Accept" = "text/plain"
        ],

        response = Web.Contents(_url, [ Headers = headers ]),
        toText = Text.FromBinary(response)
    in
        toText;

L’implémentation utilise ensuite cette fonction (si aucun autre paramètre de requête n’est trouvé dans le statefichier ) :

GetRowCount = () as number =>
    if (Record.FieldCount(Record.RemoveFields(state, {"Url", "Entity", "Schema"}, MissingField.Ignore)) > 0) then
        ...
    else
        let
            newState = state & [ RowCountOnly = true ],
            finalUrl = CalculateUrl(newState),
            value = TripPin.Scalar(finalUrl),
            converted = Number.FromText(value)
        in
            converted,

La CalculateUrl fonction est mise à jour pour ajouter /$count à l’URL si le RowCountOnly champ est défini dans le state.

// Check for $count. If all we want is a row count,
// then we add /$count to the path value (following the entity name).
urlWithRowCount =
    if (state[RowCountOnly]? = true) then
        urlWithEntity & "/$count"
    else
        urlWithEntity,

Ce nouveau Table.RowCount test unitaire doit maintenant passer.

Pour tester le cas de repli, vous ajoutez un autre test qui force l'erreur.

Tout d’abord, ajoutez une méthode auxiliaire qui vérifie le résultat d’une opération pour une erreur de pliage de try.

// Returns true if there is a folding error, or the original record (for logging purposes) if not.
Test.IsFoldingError = (tryResult as record) =>
    if ( tryResult[HasError]? = true and tryResult[Error][Message] = "We couldn't fold the expression to the data source. Please try a simpler expression.") then
        true
    else
        tryResult;

Ajoutez ensuite un test qui utilise Table.RowCount et Table.FirstN pour forcer l’erreur.

// test will fail if "Fail on Folding Error" is set to false
Fact("Fold $count + $top *error*", true, Test.IsFoldingError(try Table.RowCount(Table.FirstN(Airlines, 3)))),

Une remarque importante ici est que ce test retourne maintenant une erreur si Error on Folding Error est défini à false, car l’opération Table.RowCount retourne au gestionnaire local (par défaut). L'exécution des tests avec Error on Folding Error réglée sur true provoque l'échec de Table.RowCount et permet au test de réussir.

Conclusion

L’implémentation de Table.View pour votre connecteur ajoute une grande complexité à votre code. Étant donné que le moteur M peut traiter toutes les transformations localement, l’ajout de gestionnaires Table.View n’active pas de nouveaux scénarios pour vos utilisateurs, mais entraîne un traitement plus efficace (et potentiellement plus heureux). L’un des principaux avantages des gestionnaires Table.View étant facultatifs, il vous permet d’ajouter de manière incrémentielle de nouvelles fonctionnalités sans affecter la compatibilité descendante pour votre connecteur.

Pour la plupart des connecteurs, un gestionnaire important (et de base) à implémenter est OnTake (qui se traduit par $top OData), car il limite le nombre de lignes retournées. L’expérience Power Query effectue toujours une OnTake série de lignes lors de l’affichage d’aperçus dans le navigateur et l’éditeur de requête, de sorte que vos utilisateurs peuvent voir des améliorations significatives des performances lors de l’utilisation de 1000 jeux de données plus volumineux.