TripPin parte 10 - Dobragem básica de consultas

Este tutorial em várias partes aborda a criação de uma nova extensão de fonte de dados para o Power Query. O tutorial destina-se a ser feito sequencialmente — cada lição baseia-se no conector criado nas aulas anteriores, adicionando gradualmente novas capacidades ao seu conector.

Nesta lição, vocês:

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

Uma das características poderosas da linguagem M é a sua capacidade de direcionar o trabalho de transformação para uma ou mais fontes de dados subjacentes. Esta funcionalidade é conhecida como Query Folding (outras ferramentas/tecnologias também se referem a funções semelhantes como Predicate Pushdown ou Query Delegation).

Ao criar um conector personalizado que utiliza uma função M com capacidades integradas de folding de consultas, como OData.Feed ou Odbc.DataSource, o seu conector herda automaticamente esta funcionalidade gratuitamente.

Este tutorial replica o comportamento incorporado de dobramento de consultas para OData, implementando manipuladores de funções para a função Table.View . Esta parte do tutorial implementa alguns dos handlers mais fáceis de implementar (ou seja, aqueles que não exigem análise de expressões nem rastreamento de estados).

Para compreender melhor as capacidades de consulta que um serviço OData pode oferecer, consulte as Convenções de URL OData v4.

Consulte a Visão Geral da avaliação de consultas e dobra de consultas no Power Query para mais informações sobre dobramento de consultas.

Usando o Table.View

A função Table.View permite que um conector personalizado substitua os manipuladores de transformação predefinidos para a sua fonte de dados. Uma implementação do Table.View fornece uma função para um ou mais dos manipuladores suportados. Se um handler não for implementado, ou devolver um error durante a avaliação, o motor M volta ao seu handler padrão.

Quando um conector personalizado utiliza uma função que não suporta o folding implícito de consultas, tal como Web.Contents, os handlers de transformação padrão são sempre executados localmente. Se a API REST à qual se está a ligar suportar parâmetros de consulta como parte da consulta, o Table.View permite-lhe adicionar otimizações que permitem que o trabalho de transformação seja enviado para o serviço.

A função Table.View tem a seguinte assinatura:

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

A sua implementação encapsula a função principal da sua fonte de dados. Existem dois handlers obrigatórios para o Table.View:

• GetType: Devolve o esperado table type do resultado da consulta.
• GetRows: Devolve o resultado real table da sua função fonte de dados.

A implementação mais simples seria semelhante ao seguinte exemplo:

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

Atualize a TripPinNavTable função para chamar TripPin.SuperSimpleView em vez de GetEntity:

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

Se voltares a executar os testes unitários, o comportamento da tua função não muda. Neste caso, a sua implementação Table.View está simplesmente a encaminhar a chamada para GetEntity. Como ainda não implementaste nenhum handler de transformação, o parâmetro original url permanece inalterado.

Implementação inicial do Table.View

A implementação anterior do Table.View é simples, mas não muito útil. A implementação a seguir é usada como a sua base — não inclui qualquer funcionalidade de folding, mas tem a estrutura de suporte necessária para implementá-la.

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]);

Se olhares para a chamada para Table.View, há uma função adicional de wrapper à volta do handlers registo—Diagnostics.WrapHandlers. Esta função auxiliar encontra-se no módulo de Diagnóstico (introduzido na lição de adição de diagnóstico) e fornece-lhe uma forma útil de rastrear automaticamente quaisquer erros gerados por cada manipulador.

As GetType funções e GetRows são atualizadas para utilizar duas novas funções auxiliares —CalculateSchema e CalculateUrl. Neste momento, as implementações dessas funções são bastante simples. Repara que contêm partes do que era anteriormente feito pela GetEntity função.

Finalmente, repare que está a definir uma função interna (View) que aceita um state parâmetro. À medida que implementas mais manipuladores, eles chamam recursivamente a função interna View, atualizando state e passando-o à medida que avançam.

Atualize a função TripPinNavTable mais uma vez, substituindo a chamada para TripPin.SuperSimpleView por uma chamada para a nova função TripPin.View, e execute novamente os testes unitários. Ainda não há nenhuma funcionalidade nova, mas agora tem uma base sólida para testar.

Implementação do dobramento de consulta

Como o motor M recorre automaticamente ao processamento local quando uma consulta não pode ser dobrada, deve tomar alguns passos adicionais para validar que os seus handlers Table.View estão a funcionar corretamente.

A forma manual de validar o comportamento de dobragem é observar os pedidos de URL que os seus testes unitários fazem usando uma ferramenta como o Fiddler. Alternativamente, o registo de diagnóstico que adicionou a TripPin.Feed emite a URL completa que está a ser processada, a qual deverá incluir os parâmetros de consulta OData que os seus handlers adicionam.

Uma forma automatizada de validar a dobragem de consultas é forçar a execução do teste unitário a falhar se uma consulta não se dobrar completamente. Para fazer o teste unitário falhar quando uma consulta não se dobrar totalmente, abra as propriedades do projeto e defina Erro na falha de dobra para Verdadeiro. Com esta configuração ativada, qualquer consulta que exija processamento local resulta no seguinte erro:

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

Pode testar esta alteração adicionando uma nova Fact ao seu ficheiro de teste unitário que contenha uma ou mais transformações de tabela.

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

As secções restantes deste tutorial adicionam cada uma um novo handler Table.View . Estás a adotar uma abordagem de Desenvolvimento Orientado por Testes (TDD ), onde primeiro adicionas testes unitários que falham e depois implementas o código M para os resolver.

As secções seguintes do handler descrevem a funcionalidade fornecida pelo handler, a sintaxe de consulta equivalente ao OData, os testes unitários e a implementação. Usando o código de andaime descrito anteriormente, cada implementação de handler requer duas alterações:

• Adicionar o handler ao Table.View que atualiza o state registo.
• Modificar CalculateUrl para recuperar os valores do state e adicioná-los à URL e/ou aos parâmetros da string de consulta.

Tratamento de Tabela.FirstN com OnTake

O OnTake manipulador recebe um count parâmetro, que é o número máximo de linhas a retirar de GetRows. Em termos de OData, podes traduzir isto para o parâmetro de consulta $top .

Utiliza-se os seguintes testes unitários:

// 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)
),

Estes testes ambos utilizam o Table.FirstN para filtrar o conjunto de resultados para as primeiras X linhas. Se executares estes testes com o Erro na Falha de Dobragem definido para False (o padrão), os testes deverão ter sucesso, mas se executares o Fiddler (ou consultares os registos de traços), repara que o pedido que envias não contém quaisquer parâmetros de consulta OData.

Se definires o Erro ao Dobrar para True, os testes falham com o erro Please try a simpler expression.. Para corrigir este erro, precisa de definir o seu primeiro handler Table.View para OnTake.

O OnTake handler assemelha-se ao seguinte código:

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

A CalculateUrl função é atualizada para extrair o Top valor do state registo e definir o parâmetro correto na cadeia de consulta.

// 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

Quando voltar a executar os testes unitários, repare que o URL a que está a aceder agora contém o $top parâmetro. Devido à codificação de URLs, $top aparece como %24top, mas o serviço OData é suficientemente inteligente para o converter automaticamente.

Tabela de Manipulação. Skip com OnSkip

O OnSkip handler é muito parecido com OnTake. Recebe um count parâmetro, que é o número de linhas a saltar do conjunto de resultados. Este handler traduz-se bem para o parâmetro de consulta OData $skip .

Testes unitários:

// 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)
),

Implementação:

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

Atualizações correspondentes para CalculateUrl:

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

Para mais informações, consulte Table.Skip.

Gestão do Table.SelectColumns com o OnSelectColumns

O OnSelectColumns handler é chamado quando o utilizador seleciona ou remove colunas do conjunto de resultados. O manipulador recebe um list de text valores, representando uma ou mais colunas que devem ser selecionadas.

Em termos de OData, esta operação corresponde à opção de consulta $select .

A vantagem de manipular a seleção de colunas torna-se evidente quando se lida com tabelas com muitas colunas. O $select operador remove colunas não selecionadas do conjunto de resultados, resultando em consultas mais eficientes.

Testes unitários:

// 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)
),

Os dois primeiros testes selecionam diferentes números de colunas com o Table.SelectColumns, e incluem uma chamada Table.FirstN para simplificar o caso de teste.

O terceiro teste utiliza a opção MissingField.Ignore , que diz ao motor M para ignorar quaisquer colunas selecionadas que não existam no conjunto de resultados. O OnSelectColumns handler não precisa de se preocupar com esta opção—o motor M trata disso automaticamente (ou seja, as colunas em falta não estão incluídas na columns lista).

A implementação para OnSelectColumns faz duas coisas:

• Adiciona a lista de colunas selecionadas ao state.
• Recalcula o Schema valor para poderes definir o tipo de tabela correto.

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 é atualizado para obter a lista de colunas do estado e combiná-las (com um separador) para o parâmetro $select.

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

Gestão do Table.Sort com o OnSort

O OnSort manipulador recebe uma lista de registos do tipo:

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

Cada registo contém um Name campo, indicando o nome da coluna, e um Order campo igual a Order.Ascending ou Order.Descending.

Em termos de OData, esta operação corresponde à opção de consulta $orderby . A $orderby sintaxe tem o nome da coluna seguido de asc ou desc para indicar ordem ascendente ou descendente. Quando ordenas em várias colunas, os valores são separados por uma vírgula. Se o columns parâmetro contiver mais do que um item, é importante manter a ordem em que aparecem.

Testes unitários:

// 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"})
)

Implementação:

// 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 ]),

Atualizações para CalculateUrl:

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

Gestão do Table.RowCount com o GetRowCount

Ao contrário dos outros gestores de consultas que estás a implementar, o GetRowCount handler devolve um único valor — o número de linhas esperadas no conjunto de resultados. Numa consulta M, este valor seria tipicamente o resultado da transformação Table.RowCount .

Tem algumas opções diferentes sobre como lidar com este valor como parte de uma consulta OData:

• O parâmetro de consulta $count, que devolve a contagem como um campo separado no conjunto de resultados.
• O segmento de caminho /$count, que devolve apenas a contagem total, como valor escalar.

A desvantagem da abordagem do parâmetro de consulta é que ainda precisas de enviar toda a consulta para o serviço OData. Como a contagem regressa em linha como parte do conjunto de resultados, tens de processar a primeira página de dados do conjunto de resultados. Embora este processo continue a ser mais eficiente do que ler todo o conjunto de resultados e contar as linhas, provavelmente ainda é mais trabalho do que gostarias.

A vantagem da abordagem do segmento de caminho é que se recebe um único valor escalar no resultado. Esta abordagem torna toda a operação muito mais eficiente. No entanto, conforme descrito na especificação OData, o segmento /$count de caminho devolve um erro se incluir outros parâmetros de consulta, como $top ou $skip, o que limita a sua utilidade.

Neste tutorial, implementaste o GetRowCount handler usando a abordagem do segmento de caminho. Para evitar os erros que surgem se outros parâmetros de consulta forem incluídos, verificava outros valores de estado e devolveu um "erro não implementado" (...) se encontrasse algum. Devolver qualquer erro de um handler Table.View indica ao motor M que a operação não pode ser dobrada, e que deve voltar ao handler padrão (que neste caso estaria a contar o número total de linhas).

Primeiro, adicione um teste unitário:

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

Como o segmento /$count de caminho devolve um único valor (em formato simples/texto) em vez de um conjunto de resultados JSON, também tem de adicionar uma nova função interna (TripPin.Scalar) para fazer o pedido e tratar do resultado.

// 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;

A implementação utiliza então esta função (se não forem encontrados outros parâmetros de consulta no state):

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,

A função CalculateUrl é atualizada para adicionar /$count à URL se o campo RowCountOnly estiver definido em 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,

O novo Table.RowCount teste unitário deve agora passar.

Para testar o caso de recurso, adiciona-se outro teste que força o erro.

Primeiro, adicione um método auxiliar que verifique o resultado de uma try operação para um erro de dobramento.

// 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;

Adicione um teste que use ambos Table.RowCount e Table.FirstN para forçar o erro.

// 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)))),

Uma nota importante aqui é que este teste agora devolve um erro se o Erro ao Erro de Dobragem for definido para false, porque a Table.RowCount operação volta ao manipulador local (por defeito). Executar os testes com Erro de Dobragem definido para true faz com que Table.RowCount falhe, o que permite que o teste geral tenha sucesso.

Conclusion

Implementar o Table.View para o seu conector adiciona uma quantidade significativa de complexidade ao seu código. Como o motor M pode processar todas as transformações localmente, adicionar handlers Table.View não permite novos cenários para os seus utilizadores, mas resulta num processamento mais eficiente (e, potencialmente, utilizadores mais satisfeitos). Uma das principais vantagens de os handlers Table.View serem opcionais é que permitem adicionar novas funcionalidades de forma incremental sem afetar a retrocompatibilidade do seu conector.

Para a maioria dos conectores, um handler importante (e básico) a implementar é OnTake (que se traduz em $top OData), pois limita o número de linhas devolvidas. A experiência Power Query executa sempre uma OnTake série de 1000 linhas ao mostrar pré-visualizações no navegador e no editor de consultas, pelo que os seus utilizadores podem notar melhorias significativas de desempenho ao trabalhar com conjuntos de dados maiores.