Dobramento da parte 10 da — consulta básica

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

Nesta lição, você vai:

  • Aprenda as noções básicas do dobramento de consulta
  • Saiba mais sobre a Table.View função
  • Replicar manipuladores de dobramento de consulta OData para:
  • $top
  • $skip
  • $count
  • $select
  • $orderby

Um dos recursos poderosos da linguagem M é a sua capacidade de enviar por push o trabalho de transformação para as fontes de dados subjacentes. Esse recurso é conhecido como dobramento de consulta (outras ferramentas/tecnologias também se referem à função semelhante como aplicação de predicado ou delegação de consulta). Ao criar um conector personalizado que usa uma função M com recursos internos de dobramento de consulta, como OData.Feed ou Odbc.DataSource , seu conector herdará automaticamente essa funcionalidade gratuitamente.

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

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

Observação

Como mencionado acima, a OData.Feed função fornecerá automaticamente recursos de dobramento de consulta. Como a série de etapas 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 dobramento de consulta por conta própria. Para uso do mundo real, recomendamos que você use OData.Feed sempre que possível.

Consulte a documentação de tabela. View para obter mais informações sobre o dobramento de consulta em M.

Usando Table. View

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

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

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

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

Sua implementação encapsulará 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 table resultado real da função de fonte de dados

A implementação mais simples seria semelhante à seguinte:

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 a ser chamada 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 foi alterado. Nesse caso, a implementação de Table. View está simplesmente passando pela chamada para GetEntity . Como seu não implementou nenhum manipulador de transformação (ainda), o url parâmetro original permanece inalterado.

Implementação inicial de Table. View

A implementação acima do Table.View é simples, mas não muito útil. A implementação a seguir será usada como sua linha de base — , ela não implementa nenhuma funcionalidade de dobra, mas tem o scaffolding que você precisará fazer.

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 , verá uma função adicional de wrapper em volta do handlers registro — Diagnostics.WrapHandlers . Essa função auxiliar é encontrada no módulo de diagnóstico (que foi introduzido em um tutorial anterior) e fornece uma maneira útil de rastrear automaticamente todos os erros gerados por manipuladores individuais.

As GetType funções e foram GetRows atualizadas para fazer uso de duas novas funções auxiliares — CalculateSchema e CaculateUrl . No momento, as implementações dessas funções são bem simples — . você notará que elas contêm partes do que foi feito anteriormente pela GetEntity função.

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

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

Implementando o dobramento de consulta

Como o mecanismo M voltará automaticamente para o processamento local quando uma consulta não puder ser dobrada, você deverá executar algumas etapas adicionais para validar que seus Table.View manipuladores estão funcionando corretamente.

A maneira manual de validar o comportamento de dobramento é 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 para TripPin.Feed emitirá a URL completa que está sendo executada, o que deve incluir os parâmetros de cadeia de caracteres de consulta OData que seus manipuladores adicionarão.

Uma maneira automatizada de validar o dobramento de consulta é forçar a falha da execução do teste de unidade se uma consulta não dobrar completamente. Você pode fazer isso abrindo as propriedades do projeto e definindo o erro ao dobrar falha para true. Com essa configuração habilitada, qualquer consulta que exija processamento local resultará no seguinte erro:

Não foi possível dobrar a expressão na origem. Tente uma expressão mais simples.

Você pode testá-lo 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

O erro ao dobrar a configuração de falha é uma abordagem "tudo ou nada". Se você quiser testar consultas que não são projetadas para dobrar como parte de seus testes de unidade, você precisará 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 de TDD (desenvolvimento controlado por teste) , na qual você primeiro adiciona testes de unidade com falha e, em seguida, implementa o código M para resolvê-los.

Cada seção do manipulador abaixo descreverá a funcionalidade fornecida pelo manipulador, a sintaxe de consulta do OData equivalente, os testes de unidade e a implementação. Usando o código scaffolding descrito acima, cada implementação de manipulador requer duas alterações:

  • Adicionando o manipulador a Table. View que atualizará o state registro.
  • Modificando CalculateUrl para recuperar os valores de state e adicionar aos parâmetros URL e/ou cadeia de caracteres de consulta.

Manipulando a tabela. primeiro com Ontake

O manipulador Ontake recebe um count parâmetro, que é o número máximo de linhas a serem executadas. Em termos de OData, você pode converter isso para o $Top parâmetro de consulta.

Você usará 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 X número de linhas. Se você executar esses testes com erro na falha de dobramento definida como False (o padrão), os testes deverão ser bem-sucedidos, mas se você executar o Fiddler (ou verificar os logs de rastreamento), verá que a solicitação enviada não contém nenhum parâmetro de consulta OData.

Rastreamento de diagnóstico.

Se você definir erro ao dobrar falha para True , eles falharão com a "Tente uma expressão mais simples". . Para corrigir isso, você definirá seu primeiro manipulador Table. View para OnTake .

O manipulador Ontake tem esta aparência:

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

Executando novamente os testes de unidade, você pode ver que a URL que você está acessando agora contém o $top parâmetro. (Observe que, devido à codificação de URL, $top aparece como %24top , mas o serviço OData é inteligente o suficiente para convertê-lo automaticamente).

Rastreamento de diagnóstico com superior.

Manipulando tabela. ignorar por onskip

O manipulador onignore é muito parecido com o take. Ele recebe um count parâmetro, que é o número de linhas a serem ignoradas do conjunto de resultados. Isso é bem traduzido para o parâmetro de consulta OData $Skip .

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 para CalculateUrl :

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

Manipulando Table. SelectColumns com OnSelectColumns

O manipulador OnSelectColumns é chamado quando o usuário seleciona ou remove colunas do conjunto de resultados. O manipulador recebe um list dos text valores, representando as colunas a serem selecionadas. Em termos de OData, esta operação será mapeada para a opção de consulta $Select . A vantagem de dobrar a seleção de colunas torna-se aparente quando você está lidando com tabelas com muitas colunas. O $select operador removerá 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 diferentes números de colunas com Table.SelectColumns e incluem uma Table.FirstN chamada para simplificar o caso de teste.

Observação

Se o teste fosse simplesmente retornar os nomes de coluna (usando Table.ColumnNames ) e não todos os 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 de que o mecanismo M precisa para calcular o resultado.

O terceiro teste usa a MissingField.Ignore opção, que informa ao mecanismo M para ignorar todas 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 irá tratá-lo automaticamente (ou seja, as colunas ausentes não serão incluídas na columns lista).

Observação

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

A implementação de OnSelectColumns faz duas coisas:

  • Adiciona a lista de colunas selecionadas ao state .
  • Calcula o valor de novo 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 $select parâmetro .

// 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 um list de record valores. Cada registro contém um campo , indicando o nome da coluna e um campo que Name é igual a ou Order Order.Ascending Order.Descending . Em termos de OData, essa operação será mapeado para a $orderby de consulta. A $orderby sintaxe tem o nome da coluna seguido por ou para indicar ordem crescente ou asc desc decrescente. Ao classificar em várias colunas, os valores são separados por uma vírgula. Observe que, se o parâmetro contiver mais de um item, será importante manter a ordem na columns 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 Table.RowCount com GetRowCount

Ao contrário dos outros manipuladores de consulta que você implementou, o manipulador GetRowCount retornará um único valor o número de linhas — esperado no conjunto de resultados. Em uma consulta M, isso normalmente seria o resultado da Table.RowCount transformação. Você tem algumas opções diferentes sobre como lidar com isso como parte de uma consulta OData.

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

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

Neste tutorial, você implementará o manipulador usando a GetRowCount abordagem de segmento de caminho. Para evitar os erros que você obteria se outros parâmetros de consulta são incluídos, você verificará se há outros valores de estado e retornará um "erro não simplificado" ( ) se encontrar ... algum. Retornar qualquer erro de um manipulador informa ao mecanismo M que a operação não pode ser dobrada e deve fazer fallback para o manipulador padrão (que, nesse caso, estaria contando o número total de Table.View linhas).

Primeiro, adicione um teste de unidade simples:

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

Como o segmento de caminho retorna um único valor (em formato sem formatação/texto) em vez de um conjunto de resultados JSON, você também terá que adicionar uma nova função interna ( ) para fazer a solicitação e manipular o /$count TripPin.Scalar 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 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 /$count anexar à URL se o campo estiver RowCountOnly 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 ser aprovado.

Para testar o caso de fallback, você adicionará 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 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;

Em seguida, adicione um teste que Table.RowCount usa e para forçar o Table.FirstN 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 esse teste agora retornará um erro se Erro ao Dobrar Erro estiver definido como , porque a operação retornará para o manipulador false local Table.RowCount (padrão). Executar os testes com Erro ao Dobrar Erro definido como causará falha e permitirá que o teste seja true Table.RowCount bem-sucedido.

Conclusão

Implementar para Table.View 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 não habilita novos cenários para seus usuários, mas resultará em processamento mais eficiente (e, potencialmente, usuários mais Table.View satisfeitos). Uma das principais vantagens dos manipuladores que são opcionais é que ele permite adicionar uma nova funcionalidade incrementalmente sem afetar a compatibilidade com Table.View vertida para seu conector.

Para a maioria dos conectores, um manipulador importante (e básico) a ser implementado é (que se traduz em em OData), pois limita a quantidade de OnTake $top linhas retornadas. A Power Query sempre executará uma das 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 OnTake 1000 maiores.