TripPin parte 10 - Dobra básica de consultas

Observação

Este conteúdo atualmente faz referência ao conteúdo de uma implementação antiga para logs no Visual Studio. O conteúdo será atualizado em um futuro próximo para abranger o novo SDK do Power Query no Visual Studio Code.

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 seguido sequencialmente; cada lição se baseia no conector criado nas lições anteriores, adicionando incrementalmente novos recursos a ele.

Nesta lição, você vai:

• Conhecer os conceitos básicos de dobragem de consultas
• Saber mais sobre a função Table.View
• Replicar manipuladores de dobragem de consultas OData para:

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

Um dos recursos avançados da linguagem M é sua capacidade de transmitir o trabalho de transformação para uma ou mais fontes de dados subjacentes. Essa funcionalidade é conhecida como Dobragem de consultas (outras ferramentas/tecnologias também se referem a uma função semelhante como Pushdown de Predicado ou Delegação de Consultas).

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

Este tutorial replica o comportamento interno de dobragem de consultas do 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 nem acompanhamento de estado).

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

Observação

Conforme indicado anteriormente, a função OData.Feed fornecerá automaticamente recursos de dobragem de consultas. Como a série TripPin está tratando o serviço OData como uma API REST regular, usando Web.Contents em vez de OData.Feed, você precisará implementar os manipuladores de dobragem de consultas por conta própria. No mundo real, recomendamos que você use OData.Feed sempre que possível.

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

Usando Table.View

A função Table.View permite que um conector personalizado substitua manipuladores de transformação padrão em sua fonte de dados. Uma implementação da Table.View fornecerá 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 da linguagem M fará fallback no manipulador padrão.

Quando um conector personalizado usa uma função que não oferece suporte implícito à dobragem de consultas, como Web.Contents, 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, a Table.View permite adicionar otimizações que possibilitam 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 a função de fonte de dados principal. Há dois manipuladores necessários para a Table.View:

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

A implementação mais simples seria semelhantes 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 função TripPinNavTable 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, verá que o comportamento da função não mudou. Nesse caso, a implementação de Table.View está simplesmente passando a chamada para GetEntity. Como você ainda não implementou nenhum manipulador de transformação, o parâmetro original url permanece intocado.

Implementação inicial de Table.View

A implementação acima da 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 dobragem, mas tem o scaffolding de que você precisa para fazê-lo.

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 à Table.View, verá uma função de wrapper extra ao redor do registro handlers: Diagnostics.WrapHandlers. Essa função auxiliar é encontrada no módulo Diagnóstico (que foi apresentado na lição de adição de diagnóstico) e fornece uma forma útil de rastrear automaticamente os erros gerados por manipuladores individuais.

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

Por fim, você observará que está definindo uma função interna (View) que aceita um parâmetro state. À medida que você implementa mais manipuladores, eles chamarão recursivamente a função interna View, atualizando e passando state à medida que são executados.

Atualize a função TripPinNavTable mais uma vez, substituindo a chamada à TripPin.SuperSimpleView por uma chamada para a nova função TripPin.View e execute novamente os testes de unidade. Você ainda não verá nenhuma nova funcionalidade, mas agora tem uma linha de base sólida para testar.

Implementação da dobra de consultas

Como o mecanismo da M faz fallback automaticamente no processamento local quando não é possível dobrar uma consulta, é necessário realizar algumas etapas adicionais para validar se os manipuladores de Table.View estão funcionando corretamente.

A forma manual de validar o comportamento de dobragem é observar as solicitações de URL que os testes de unidade fazem usando uma ferramenta como o Fiddler. Como alternativa, o log de diagnóstico que você adicionou ao TripPin.Feed emite a URL completa que está sendo executada, a qual deve incluir os parâmetros da sequência de consulta OData adicionados por seus manipuladores.

Uma forma automatizada de validar a dobragem de consultas é forçar a execução do teste de unidade a falhar caso uma consulta não seja totalmente dobrada. Você pode fazer isso abrindo as propriedades do projeto e definindo Erro na Falha de Dobragem como Verdadeiro. Com essa configuração habilitada, qualquer consulta que exija o processamento local resultara no seguinte erro:

Não foi possível dobrar a expressão para a fonte. Tente uma expressão mais simples.

Você pode testar isso adicionando um novo 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)
)

Observação

A configuração Erro na Falha de Dobragem é uma abordagem "tudo ou nada". Se você quiser testar consultas que não foram projetadas para serem dobradas como parte dos testes de unidade, será necessário adicionar alguma lógica condicional para habilitar/desabilitar testes de acordo.

As seções restantes deste tutorial adicionarão um novo manipulador Table.View. Você usará uma abordagem TDD (Desenvolvimento Orientado por Testes) em que primeiro deve adicionar testes de unidade com falha e, em seguida, implementar o código M para resolvê-los.

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

• Adição do manipulador a Table.View que atualiza o registro state.
• Modificação do CalculateUrl para recuperar os valores do state e adicionar à URL e/ou aos parâmetros da cadeia de caracteres de consulta.

Manipulando Table.FirstN com OnTake

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

Os seguintes testes de unidade serão usados:

// 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 na Falha de Dobragem definido como False (o padrão), os testes deverão ser bem-sucedidos, mas, se você executar o Fiddler (ou verificar os logs de rastreamento), observará que a solicitação enviada não contém nenhum parâmetro de consulta OData.

Se você definir Erro na Falha de Dobragem como True, os testes falharão com o erro Please try a simpler expression.. Para corrigir esse erro, é necessário definir seu primeiro manipulador Table.View para OnTake.

O manipulador OnTake deve ser semelhante 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 função CalculateUrl é atualizada para extrair o valor Top do registro state e definir o parâmetro correto na cadeia de caracteres 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 parâmetro $top. Devido à codificação da 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 parâmetro count, que é o número de linhas a serem ignoradas no conjunto de resultados. Esse manipulador se traduz bem no parâmetro de consulta $skip de 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),

Correspondência das atualizações com CalculateUrl:

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

Mais informações: Table.Skip

Manipulando Table.SelectColumns com OnSelectColumns

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

Nos termos do OData, essa operação é mapeada na opção de consulta $select.

A vantagem da seleção de dobragem de colunas se torna aparente quando você está lidando com tabelas com muitas colunas. O operador $select remove as 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.

Observação

Se o teste simplesmente retornar os nomes de coluna (usando Table.ColumnNames e não dados, a solicitação para o serviço OData nunca será realmente enviada. Isso ocorre porque a chamada para GetType retornará o esquema, que contém todas as informações necessárias pelo mecanismo da M para calcular o resultado.

O terceiro teste usa a opção MissingField.Ignore, que informa ao mecanismo da M para ignorar as colunas selecionadas que não existem no conjunto de resultados. O manipulador OnSelectColumns não precisa se preocupar com essa opção – o mecanismo da linguagem M cuida disso automaticamente (ou seja, colunas ausentes não são incluídas na lista columns).

Observação

A outra opção para Table.SelectColumns, MissingField.UseNull, requer um conector para implementar o manipulador OnAddColumn. Isso será feito em uma lição subsequente.

A implementação para OnSelectColumns faz duas coisas:

• Adiciona a lista de colunas selecionadas ao state.
• Calcula novamente o valor de Schema 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 manipulador OnSort recebe uma lista de registros do tipo:

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

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

Em termos de OData, essa operação será mapeada para na 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 a classificação é feita por várias colunas, os valores são separados com uma vírgula. Observe que, se o parâmetro columns 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ção no CalculateUrl:

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

Manipulando Table.RowCount com GetRowCount

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

Você tem algumas opções diferentes para 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 precisará enviar toda a consulta para o serviço OData. Como a contagem retorna embutida como parte do conjunto de resultados, é necessário processar a primeira página de dados do conjunto de resultados. Embora esse processo ainda seja mais eficiente que a leitura de todo o conjunto de resultados e a contagem das linhas, provavelmente ainda será mais trabalho do que você desejaria fazer.

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

Neste tutorial, você implementou o manipulador GetRowCount usando a abordagem do segmento de caminho. Para evitar os erros que você receberia se outros parâmetros de consulta fossem incluídos, você verificou se havia outros valores de estado e retornou um "erro não implementado" (...) se encontrou algum. O retorno de qualquer erro de um manipulador Table.View informa ao mecanismo da M que a operação não pode ser dobrada e que ele deve fazer fallback no 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 caminho /$count 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;

A implementação então usa essa função (se nenhum outro parâmetro de consulta é encontrado em 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 será atualizada para acrescentar /$count à URL se o campo RowCountOnly 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,

Agora, o novo teste de unidade Table.RowCount deve ser aprovado.

Para testar o caso de fallback, adicione outro teste para forçar o erro.

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

// 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 retornará um erro se Erro no Erro de Dobragem estiver definido como false, pois a operação Table.RowCount fará fallback no manipulador local (padrão). Executar os testes com Erro no Erro de Dobragem definido como true causa falha no Table.RowCount e permitirá que o teste tenha êxito.

Conclusão

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

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