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.
Este artigo foi dividido em uma série de etapas recomendadas para interpretar o plano de consulta. Estes passos são:
- Analise os indicadores de dobragem da consulta.
- Selecione a etapa de consulta para revisar seu plano de consulta.
- Implemente alterações na sua consulta.
Utilize os seguintes passos para criar a consulta no seu próprio ambiente do Power Query Online.
No Power Query - Escolha a fonte de dados, selecione Consulta em branco.
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"
Altere
servername
edatabase
com os nomes corretos para o seu próprio ambiente.(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.
Selecione Seguinte.
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.
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.
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.
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.
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.
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.
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.
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", comoSql.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.SelectColumns
e 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.
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.
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.
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.
Implemente a alternativa discutida na seção anterior:
Feche a caixa de diálogo do plano de consulta e volte ao Editor do Power Query.
Remova a etapa Linhas inferiores mantidas .
Classifique a coluna SalesOrderID em ordem decrescente.
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.
Depois de implementar as alterações, verifique os indicadores de dobragem da consulta novamente e veja se ele está lhe dando um indicador dobrado.
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.
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.