TripPin parte 10 – Dobramento de consulta básica

Este tutorial de várias partes aborda a criação de uma nova extensão de fonte de dados para o Power Query. O tutorial deve ser feito sequencialmente — cada lição se baseia no conector criado nas lições anteriores, adicionando progressivamente novos recursos ao conector.

Nesta lição, você:

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

Um dos recursos avançados da linguagem M é a capacidade de enviar por push o trabalho de transformação para uma ou mais fontes de dados subjacentes. Essa funcionalidade é conhecida como Dobramento de Consulta (outras ferramentas e tecnologias também se referem a funções semelhantes como "Pushdown de Predicado" ou "Delegação de Consulta").

Ao criar um conector personalizado que usa uma função M com recursos internos de dobragem de consulta, como OData.Feed ou Odbc.DataSource, seu conector herda automaticamente essa funcionalidade gratuitamente.

Este tutorial replica o comportamento de dobramento de consulta interno para OData implementando manipuladores de funções para a função Table.View . Esta parte do tutorial implementa alguns dos manipuladores mais fáceis de implementar (ou seja, os que não exigem análise de expressão e acompanhamento de estado).

Para entender mais sobre os recursos de consulta que um serviço OData pode oferecer, acesse as Convenções de URL do OData v4.

Vá para Visão geral da avaliação de consulta e da dobragem de consultas no Power Query para obter mais informações sobre a dobragem de consultas.

Usando Table.View

A função Table.View permite que um conector personalizado substitua manipuladores de transformação padrão para sua fonte de dados. Uma implementação de Table.View fornece uma função para um ou mais manipuladores com suporte. Se um manipulador não for implementado ou retornar um error durante a avaliação, o mecanismo M recorre ao manipulador padrão.

Quando um conector personalizado usa uma função que não dá suporte à dobra de consulta implícita, como Web.Contents, os manipuladores de transformação padrão sempre são executados localmente. Se a API REST à qual você está se conectando dá suporte a parâmetros de consulta como parte da consulta, Table.View permite adicionar otimizações que permitem que o trabalho de transformação seja enviado por push para o serviço.

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

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

Sua implementação encapsula sua função de fonte de dados principal. Há dois manipuladores necessários para Table.View:

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

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

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 você executar novamente os testes de unidade, o comportamento da função não será alterado. Nesse caso, sua implementação Table.View está simplesmente passando pela chamada para GetEntity. Como você não implementou nenhum manipulador de transformação (ainda), o parâmetro original url permanece intocado.

Implementação inicial de Table.View

A implementação anterior de Table.View é simples, mas não muito útil. A implementação a seguir é usada como sua linha de base; ela não implementa nenhuma funcionalidade de dobra, mas possui a estrutura necessária para isso.

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 você examinar a chamada para Table.View, há uma função de wrapper extra em torno do registro handlers—Diagnostics.WrapHandlers. Essa função auxiliar é encontrada no módulo Diagnóstico (introduzido na lição de diagnóstico de adição ) e fornece uma maneira útil de rastrear automaticamente os erros gerados por manipuladores individuais.

As funções GetType e GetRows são atualizadas para usar duas novas funções auxiliares: CalculateSchema e CalculateUrl. No momento, as implementações dessas funções são bastante simples. Observe que eles contêm partes do que foi feito anteriormente pela GetEntity função.

Por fim, observe que você está definindo uma função interna (View) que aceita um state parâmetro. À medida que você implementa mais manipuladores, eles continuamente chamam de forma recursiva a função interna View, atualizando e transmitindo state durante o processo.

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 de unidade. Ainda não há nenhuma nova funcionalidade, mas agora você tem uma linha de base sólida para teste.

Implementando a dobragem de consulta

Como o mecanismo M retorna automaticamente ao processamento local quando uma consulta não pode ser dobrada, você deve executar algumas etapas extras para validar se os manipuladores Table.View estão funcionando corretamente.

A maneira manual de validar o comportamento de dobragem é, usando uma ferramenta como o Fiddler, observar as solicitações de URLs que os testes de unidade fazem. Como alternativa, o log de diagnóstico que você adicionou para TripPin.Feed emitir a URL completa que está sendo executada, que deve incluir os parâmetros de cadeia de caracteres de consulta OData que seus manipuladores adicionam.

Uma forma automatizada de verificar a dobragem de consulta é fazer com que a execução do teste de unidade falhe se uma consulta não dobrar completamente. Para fazer o teste de unidade falhar quando uma consulta não for completamente compilada, abra as propriedades do projeto e defina Erro em Falha de Compilação como Verdadeiro. Com essa configuração habilitada, qualquer consulta que exija o processamento local resulta no seguinte erro:

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

Você pode testar essa alteração adicionando uma nova Fact ao arquivo de teste de unidade que contém 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 seções restantes deste tutorial adicionam um novo manipulador Table.View . Você está adotando uma abordagem de TDD (Desenvolvimento Controlado por Teste), em que primeiro você adiciona testes de unidade com falha e, em seguida, implementa o código M para resolvê-los.

As seções do manipulador a seguir descrevem a funcionalidade fornecida pelo manipulador, a sintaxe de consulta equivalente OData, os testes de unidade e a implementação. Usando o código scaffolding descrito anteriormente, cada implementação de manipulador requer duas alterações:

• Adicionando o manipulador ao Table.View que atualiza o state registro.
• Modificando CalculateUrl para recuperar os valores de state e adicionar aos parâmetros de URL e/ou da string de consulta.

Como lidar com Table.FirstN com OnTake

O OnTake manipulador recebe um parâmetro count, que é o número máximo de linhas a serem obtidas de GetRows. Em termos OData, você pode traduzir isso para o parâmetro de consulta $top .

Você usa os seguintes testes de unidade:

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

Esses testes usam Table.FirstN para filtrar o conjunto de resultados para o primeiro número X de linhas. Se você executar esses testes com Erro em Falha de Dobra configurado para False (padrão), os testes deverão ser bem-sucedidos, mas, se você executar o Fiddler (ou verificar os logs de rastreamento), observe que a solicitação que você envia não contém nenhum parâmetro de consulta OData.

Se você definir Erro em Falha de DobragemTrue, os testes falharão com o erro Please try a simpler expression.. Para corrigir esse erro, você precisa definir seu primeiro manipulador Table.View para OnTake.

O OnTake manipulador se parece com o 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 função CalculateUrl é atualizada para extrair o valor Top do registro state e definir o parâmetro correto na string 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

Ao executar novamente os testes de unidade, observe que a URL que você está acessando agora contém o $top parâmetro. Devido à codificação de URL, $top aparece como %24top, mas o serviço OData é inteligente o suficiente para convertê-lo automaticamente.

Manipulando Table.Skip com OnSkip

O manipulador OnSkip é muito parecido com OnTake. Ele recebe um count parâmetro, que é o número de linhas a serem ignoradas do conjunto de resultados. Esse manipulador traduz-se bem para o parâmetro de consulta $skip OData.

Testes de unidade:

// 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 a CalculateUrl:

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

Para obter mais informações, acesse Table.Skip.

Manipulando Table.SelectColumns com OnSelectColumns

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

Em termos OData, essa operação é mapeada para a opção de consulta $select .

A vantagem de dobrar a seleção de coluna se torna evidente quando você está lidando com tabelas com muitas colunas. O $select operador remove colunas não selecionadas do conjunto de resultados, resultando em consultas mais eficientes.

Testes de unidade:

// 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 números diferentes de colunas com Table.SelectColumns e incluem uma chamada Table.FirstN para simplificar o caso de teste.

O terceiro teste usa a opção MissingField.Ignore , que informa ao mecanismo M para ignorar as colunas selecionadas que não existem no conjunto de resultados. O OnSelectColumns manipulador não precisa se preocupar com essa opção– o mecanismo M lida com ela automaticamente (ou seja, colunas ausentes não sã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 que você possa 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 recuperar 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,

Manipulando Table.Sort com OnSort

O OnSort manipulador recebe uma lista de registros do tipo:

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

Cada registro 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, essa operação corresponde à opção de consulta $orderby. A sintaxe $orderby tem o nome da coluna seguido por asc ou desc para indicar a ordem crescente ou decrescente. Quando você classifica várias colunas, os valores são separados com uma vírgula. Se o columns parâmetro contiver mais de um item, é importante manter a ordem na qual eles aparecem.

Testes de unidade:

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

Manipulando GetRowCount com Table.RowCount

Ao contrário dos outros manipuladores de consulta que você está implementando, o GetRowCount manipulador retorna um único valor: o número de linhas esperadas no conjunto de resultados. Em uma consulta M, esse valor normalmente seria o resultado da transformação Table.RowCount .

Você tem algumas opções diferentes sobre como lidar com esse valor como parte de uma consulta OData:

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

A desvantagem da abordagem do parâmetro de consulta é que você ainda precisa enviar a consulta inteira para o serviço OData. Como a contagem volta à linha como parte do conjunto de resultados, você precisa processar a primeira página de dados do conjunto de resultados. Embora esse processo ainda seja mais eficiente do que ler todo o conjunto de resultados e contar as linhas, provavelmente ainda é mais trabalho do que você deseja fazer.

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

Neste tutorial, você implementou o manipulador GetRowCount usando a abordagem do segmento de caminho. Para evitar os erros obtidos se outros parâmetros de consulta estiverem incluídos, você verificou outros valores de estado e retornou um "erro não implementado" (...) se tiver encontrado algum. Retornando qualquer erro de um manipulador Table.View informa ao mecanismo M que a operação não pode ser dobrada e deve voltar para o manipulador padrão (que, nesse caso, estaria contando o número total de linhas).

Primeiro, adicione um teste de unidade:

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

Como o segmento de /$count caminho retorna um único valor (em formato simples/texto) em vez de um conjunto de resultados JSON, você também precisa adicionar uma nova função interna (TripPin.Scalar) para fazer a solicitação e manipular o 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;

Em seguida, a implementação usará essa função (se nenhum outro parâmetro de consulta for encontrado 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 CalculateUrl função será atualizada para acrescentar /$count à URL se o RowCountOnly campo estiver definido no 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 de unidade agora deve passar.

Para testar o caso de fallback, adicione outro teste que força o erro.

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

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

Em seguida, adicione um teste que usa 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 observação importante aqui é que este teste agora retorna um erro se Erro de Dobragem estiver configurado como false, porque Table.RowCount operação volta para o manipulador local (padrão). Executar os testes com Error on Folding Error configurado como true resulta em falha de Table.RowCount e permite que o teste seja bem-sucedido.

Conclusion

Implementar Table.View para seu conector adiciona uma quantidade significativa de complexidade ao seu código. Como o mecanismo M pode processar todas as transformações localmente, adicionar manipuladores Table.View não habilita novos cenários para seus usuários, mas resulta em processamento mais eficiente (e, potencialmente, usuários mais felizes). Uma das principais vantagens dos manipuladores Table.View serem opcionais é que ele permite adicionar incrementalmente novas funcionalidades sem afetar a compatibilidade com versões anteriores para o conector.

Para a maioria dos conectores, um manipulador importante (e básico) a ser implementado é OnTake (que se traduz como $top no OData), pois limita o número de linhas retornadas. A experiência do Power Query sempre executa uma amostra das linhas ao exibir visualizações no navegador e no editor de consultas, para que os usuários possam ver melhorias significativas no desempenho ao trabalhar com conjuntos de dados maiores.