Ler em inglês

Partilhar via


Plano de consulta para o Power Query (Pré-visualização)

O plano de consulta para o Power Query é uma funcionalidade que fornece uma melhor vista da avaliação da sua consulta. É útil para ajudar a determinar por que uma consulta específica pode não ser dobrada em uma etapa específica.

Através de um exemplo prático, este artigo demonstrará o principal caso de uso e os benefícios potenciais do uso do recurso de plano de consulta para revisar suas etapas de consulta. Os exemplos usados neste artigo foram criados usando o banco de dados de exemplo AdventureWorksLT para o SQL Server do Azure, que você pode baixar dos bancos de dados de exemplo AdventureWorks.

Nota

A funcionalidade de plano de consulta para o Power Query só está disponível no Power Query Online.

Processo sugerido para utilizar a funcionalidade de plano de consulta no Power Query revendo os indicadores de dobragem da consulta, depois reveja o plano de consulta para um passo selecionado e, finalmente, implemente quaisquer alterações derivadas da revisão do plano de consulta.

Este artigo foi dividido em uma série de etapas recomendadas para interpretar o plano de consulta. Estes passos são:

  1. Analise os indicadores de dobragem da consulta.
  2. Selecione a etapa de consulta para revisar seu plano de consulta.
  3. Implemente alterações na sua consulta.

Utilize os seguintes passos para criar a consulta no seu próprio ambiente do Power Query Online.

  1. No Power Query - Escolha a fonte de dados, selecione Consulta em branco.

  2. Substitua o script da consulta em branco pela consulta a seguir.

    let
      Source = Sql.Database("servername", "database"),
      Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
      #"Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "SalesOrderNumber", "PurchaseOrderNumber", "AccountNumber", "CustomerID", "TotalDue"}),
      #"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
      #"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
    in
      #"Kept bottom rows"
    
  3. Altere servername e database com os nomes corretos para o seu próprio ambiente.

  4. (Opcional) Se você estiver tentando se conectar a um servidor e banco de dados para um ambiente local, certifique-se de configurar um gateway para esse ambiente.

  5. Selecione Seguinte.

  6. No Editor do Power Query, selecione Configurar ligação e forneça as credenciais à sua origem de dados.

Nota

Para obter mais informações sobre como se conectar a um SQL Server, vá para o banco de dados do SQL Server.

Depois de seguir estes passos, a sua consulta será semelhante à da imagem seguinte.

Consulta de exemplo com indicadores de dobragem de consulta ativados. Essa consulta se conecta à tabela SalesOrderHeader e seleciona algumas colunas dos últimos cinco pedidos com um valor TotalDue acima de 1000.

Nota

Este artigo usa um exemplo simplificado para mostrar esse recurso, mas os conceitos descritos neste artigo se aplicam a todas as consultas. Recomendamos que você tenha um bom conhecimento de dobragem de consulta antes de ler o plano de consulta. Para saber mais sobre dobragem de consultas, vá para Noções básicas de dobragem de consultas.

1. Analise os indicadores de dobragem da consulta

Nota

Antes de ler esta seção, recomendamos que você revise o artigo sobre Indicadores de dobragem de consulta.

O primeiro passo neste processo é rever a sua consulta e prestar muita atenção aos indicadores de dobragem da consulta. O objetivo é rever as etapas que estão marcadas como não dobradas. Em seguida, você pode ver se fazer alterações na consulta geral pode fazer com que essas transformações se dobrem completamente.

Indicadores de dobragem de consulta para a consulta de exemplo dentro do painel Etapas aplicadas.

Neste exemplo, a única etapa que não pode ser dobrada é Linhas inferiores mantidas, que é fácil de identificar através do indicador de etapa não dobrada . Esta etapa também é a última etapa da consulta.

O objetivo agora é revisar essa etapa e entender o que está sendo dobrado de volta para a fonte de dados e o que não pode ser dobrado.

2. Selecione a etapa de consulta para revisar seu plano de consulta

Você identificou a etapa Linhas inferiores mantidas como uma etapa de interesse, uma vez que ela não se dobra para a fonte de dados. Clique com o botão direito do mouse na etapa e selecione a opção Exibir plano de consulta. Esta ação exibe uma nova caixa de diálogo que contém um diagrama para o plano de consulta da etapa selecionada.

Caixa de diálogo do plano de consulta que mostra uma exibição de diagrama para o plano de consulta com nós conectados por linhas. O Power Query tenta otimizar a sua consulta tirando partido da avaliação preguiçosa e da dobragem de consultas, conforme mencionado em Noções básicas de dobragem de consultas. Este plano de consulta representa a tradução otimizada da sua consulta M para a consulta nativa que é enviada para a fonte de dados. Também inclui quaisquer transformações executadas pelo Motor do Power Query. A ordem em que os nós aparecem segue a ordem da sua consulta a partir da última etapa ou saída da sua consulta, que é representada na extremidade esquerda do diagrama e, neste caso, é o nó Table.LastN que representa a etapa Linhas inferiores mantidas.

Na parte inferior da caixa de diálogo, há uma barra com ícones que ajudam você a ampliar ou reduzir o modo de exibição do plano de consulta e outros botões para ajudá-lo a gerenciar a exibição. Para a imagem anterior, a opção Ajustar para visualizar desta barra foi usada para apreciar melhor os nós.

Caixa de diálogo do plano de consulta com os nós ampliados para uma melhor visualização.

Nota

O plano de consulta representa o plano otimizado. Quando o mecanismo está avaliando uma consulta, ele tenta dobrar todos os operadores em uma fonte de dados. Em alguns casos, pode até fazer alguma reordenação interna dos passos para maximizar a dobragem. Com isso em mente, os nós/operadores deixados neste plano de consulta otimizado normalmente contêm a consulta de fonte de dados "dobrada" e quaisquer operadores que não puderam ser dobrados e são avaliados localmente.

Identificar nós dobrados de outros nós

Você pode identificar os nós neste diagrama como dois grupos:

  • Nós dobrados: este nó pode ser um ou Value.NativeQuery nós de "fonte de dados", como Sql.Database. Estes também podem ser identificados com o rótulo remoto sob o nome da sua função.
  • Nós não dobrados: outros operadores de tabela, como Table.SelectRows, Table.SelectColumnse outras funções que não puderam ser dobradas. Estes também podem ser identificados com os rótulos Full scan e Streaming.

A imagem a seguir mostra os nós dobrados dentro do retângulo vermelho. O restante dos nós não pôde ser dobrado de volta para a fonte de dados. Você precisará revisar o restante dos nós, pois o objetivo é tentar fazer com que esses nós sejam dobrados de volta para a fonte de dados.

Controles de exibição de plano de consulta na parte inferior da caixa de diálogo com a opção de ajuste para exibição selecionada. Você pode selecionar Exibir detalhes na parte inferior de alguns nós para exibir informações estendidas. Por exemplo, os Value.NativeQuery detalhes do nó mostram a consulta nativa (em SQL) que será enviada para a fonte de dados.

Vista de detalhes para o nó Value.NativeQuery no plano de consulta. A consulta mostrada aqui pode não ser exatamente a mesma consulta enviada para a fonte de dados, mas é uma boa aproximação. Nesse caso, ele informa exatamente quais colunas serão consultadas da tabela SalesOrderHeader e, em seguida, como ele filtrará essa tabela usando o campo TotalDue para obter apenas linhas em que o valor desse campo é maior que 1000. O nó ao lado, Table.LastN, é calculado localmente pelo mecanismo do Power Query, pois não pode ser dobrado.

Nota

Os operadores podem não corresponder exatamente às funções usadas no script da consulta.

Revise nós não dobrados e considere ações para fazer sua transformação dobrar

Agora você determinou quais nós não podem ser dobrados e serão avaliados localmente. Este caso só tem o Table.LastN nó, mas em outros cenários poderia ter muitos mais.

O objetivo é aplicar alterações à sua consulta para que a etapa possa ser dobrada. Algumas das alterações que você pode implementar podem variar desde a reorganização de suas etapas até a aplicação de uma lógica alternativa à sua consulta que seja mais explícita para a fonte de dados. Isso não significa que todas as consultas e todas as operações sejam dobráveis aplicando algumas alterações. Mas é uma boa prática determinar por tentativa e erro se sua consulta pode ser dobrada.

Como a fonte de dados é um banco de dados do SQL Server, se o objetivo for recuperar as últimas cinco ordens da tabela, uma boa alternativa seria aproveitar as cláusulas TOP e ORDER BY no SQL. Como não há nenhuma cláusula BOTTOM no SQL, a Table.LastN transformação no PowerQuery não pode ser traduzida em SQL. Você pode remover a Table.LastN etapa e substituí-la por:

  • Uma etapa decrescente de classificação pela coluna SalesOrderID na tabela, uma vez que essa coluna determina qual ordem vai primeiro e qual foi inserida por último.
  • Selecione as cinco primeiras linhas desde que a tabela foi classificada, esta transformação realiza o mesmo como se fosse uma linha inferior mantida (Table.LastN).

Esta alternativa é equivalente à consulta original. Embora essa alternativa em teoria pareça boa, você precisa fazer as alterações para ver se essa alternativa fará com que esse nó se dobre totalmente para a fonte de dados.

3. Implementar alterações à sua consulta

Implemente a alternativa discutida na seção anterior:

  1. Feche a caixa de diálogo do plano de consulta e volte ao Editor do Power Query.

  2. Remova a etapa Linhas inferiores mantidas .

  3. Classifique a coluna SalesOrderID em ordem decrescente.

    Classificando a coluna SalesOrderID em ordem decrescente usando o menu de filtro automático.

  4. Selecione o ícone da tabela no canto superior esquerdo da vista de pré-visualização de dados e selecione a opção que lê Manter linhas superiores. Na caixa de diálogo, passe o número cinco como argumento e pressione OK.

    Usando o menu de contexto da tabela para selecionar a transformação Manter linhas superiores para manter apenas as cinco linhas superiores.

Depois de implementar as alterações, verifique os indicadores de dobragem da consulta novamente e veja se ele está lhe dando um indicador dobrado.

Todos os indicadores de dobragem de consulta são verdes e mostram que podem ser dobrados. A tabela final fornece as mesmas linhas, mas em uma ordem diferente. Agora é hora de rever o plano de consulta da última etapa, que agora é Manter as linhas superiores. Agora há apenas nós dobrados. Selecione Exibir detalhes em Value.NativeQuery para verificar qual consulta está sendo enviada ao banco de dados.

Novo plano de consulta depois de fazer as alterações na consulta, que agora mostra apenas nós dobrados, com Value.NativeQuery mostrando a instrução SQL completa que avalia a consulta.

Embora este artigo esteja sugerindo qual alternativa aplicar, o objetivo principal é que você aprenda a usar o plano de consulta para investigar a dobragem de consulta. Este artigo também fornece visibilidade do que está sendo enviado para sua fonte de dados e quais transformações serão feitas localmente.

Você pode ajustar seu código para ver o impacto que ele tem em sua consulta. Ao usar os indicadores de dobragem de consulta, você também terá uma ideia melhor de quais etapas estão impedindo que sua consulta seja dobrada.