Partilhar via

Dobragem de consultas em consultas nativas

  • Artigo

No Power Query, pode definir uma consulta nativa e executá-la na sua origem de dados. O artigo Importar dados de um banco de dados usando consulta de banco de dados nativo explica como fazer esse processo com várias fontes de dados. Mas, ao usar o processo descrito nesse artigo, sua consulta não aproveita nenhuma dobragem de consulta das etapas de consulta subsequentes.

Este artigo apresenta um método alternativo para criar consultas nativas em sua fonte de dados usando a função Value.NativeQuery e manter o mecanismo de dobragem de consulta ativo para as etapas subsequentes da consulta.

Nota

Recomendamos que você leia a documentação sobre dobragem de consulta e os indicadores de dobragem de consulta para entender melhor os conceitos usados ao longo deste artigo.

Conectores de dados suportados

O método descrito nas próximas seções se aplica aos seguintes conectores de dados:

Conectar-se ao destino a partir da fonte de dados

Nota

Para mostrar esse processo, este artigo usa o conector do SQL Server e o banco de dados de exemplo AdventureWorks2019. A experiência pode variar de conector para conector, mas este artigo mostra os fundamentos sobre como habilitar recursos de dobragem de consulta em consultas nativas para os conectores suportados.

Ao se conectar à fonte de dados, é importante que você se conecte ao nó ou nível em que deseja executar sua consulta nativa. Para o exemplo neste artigo, esse nó é o nível do banco de dados dentro do servidor.

Captura de tela da caixa de diálogo de configurações de conexão para a conexão com o banco de dados AdventureWorks2019 em uma instância local do SQL Server.

Depois de definir as configurações de conexão e fornecer as credenciais para sua conexão, a caixa de diálogo de navegação para sua fonte de dados é aberta. A caixa de diálogo de navegação contém todos os objetos disponíveis aos quais você pode se conectar.

Nessa lista, você precisa selecionar o objeto onde a consulta nativa é executada (também conhecido como destino). Neste exemplo, esse objeto é o nível do banco de dados.

Na janela do navegador no Power Query, selecione e mantenha pressionado (ou clique com o botão direito do mouse) o nó do banco de dados na janela do navegador e selecione a opção Transformar Dados . Selecionar essa opção cria uma nova consulta da exibição geral do seu banco de dados, que é o destino que você precisa para executar sua consulta nativa.

Captura de tela de escolha de dados em que o usuário clicou com o botão direito do mouse no nó do banco de dados no navegador, com ênfase em Transformar dados.

Assim que a consulta chegar ao editor do Power Query, apenas o passo Origem deverá ser apresentado no painel Passos aplicados. Esta etapa contém uma tabela com todos os objetos disponíveis em seu banco de dados, semelhante a como eles foram exibidos na janela Navegador.

Captura de ecrã da consulta apenas com o passo de origem.

Usar a função Value.NativeQuery

O objetivo deste processo é executar o seguinte código SQL e aplicar mais transformações com o Power Query que podem ser dobradas de volta para a origem.

SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'

O primeiro passo foi definir o destino correto, que neste caso é o banco de dados onde o código SQL é executado. Quando uma etapa tiver o destino correto, você poderá selecioná-la — neste caso, Origem em Etapas Aplicadas — e, em seguida, selecionar o botão fx na barra de fórmulas para adicionar uma etapa personalizada. Neste exemplo, substitua a Source fórmula pela seguinte fórmula:

Value.NativeQuery(Source, "SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'  ", null, [EnableFolding = true])

O componente mais importante dessa fórmula é o uso do registro opcional para o quarto parâmetro da função que tem o campo de registro EnableFolding definido como true.

Captura de ecrã da nova fórmula de passo personalizada com a utilização da função Value.NativeQuery e da consulta SQL explícita.

Nota

Você pode ler mais sobre a função Value.NativeQuery no artigo de documentação oficial.

Depois de inserir a fórmula, é exibido um aviso que exige que você habilite a execução de consultas nativas para sua etapa específica. Selecione continuar para que esta etapa seja avaliada.

Esta instrução SQL produz uma tabela com apenas três linhas e duas colunas.

Captura de tela com os resultados da consulta nativa avaliados em relação ao banco de dados de destino.

Dobragem de consulta de teste

Para testar a dobragem da consulta, você pode tentar aplicar um filtro a qualquer uma das colunas e ver se o indicador de dobragem da consulta na seção de etapas aplicadas mostra a etapa como dobrada. Nesse caso, você pode filtrar a coluna DepartmentID para ter valores que não sejam iguais a dois.

Captura de tela demonstrando como filtrar a coluna DepartmentID para ter apenas os valores que não são iguais a dois.

Depois de adicionar esse filtro, você pode verificar se os indicadores de dobragem da consulta ainda mostram a dobragem da consulta acontecendo nesta nova etapa.

Etapa de filtro mostrada como dobrada de volta para a fonte de dados na seção de etapas aplicadas.

Para validar ainda mais qual consulta está sendo enviada para a fonte de dados, você pode selecionar e segurar (ou clicar com o botão direito do mouse) a etapa Linhas filtradas e selecionar a opção Exibir plano de consulta para verificar o plano de consulta para essa etapa.

No modo de exibição de plano de consulta, você pode ver que um nó com o nome Value.NativeQuery que tem um hiperlink Exibir Detalhes . Você pode selecionar esse hiperlink para exibir a consulta exata que está sendo enviada para o banco de dados do SQL Server.

A consulta nativa é encapsulada em torno de outra instrução SELECT para criar uma subconsulta do original. O Power Query faz o seu melhor para criar a consulta mais otimizada, dadas as transformações utilizadas e a consulta nativa fornecida.

Captura de ecrã do plano de consulta para o passo Linhas filtradas.

Gorjeta

Para cenários em que você obtém erros porque a dobragem de consulta não foi possível, recomendamos que você tente validar suas etapas como uma subconsulta da consulta nativa original para verificar se pode haver conflitos de sintaxe ou contexto.