Compartilhar via

Dobragem de consultas em consultas nativas

  • Artigo

No Power Query, é possível definir uma consulta nativa e executá-la na fonte 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. Entretanto, com o uso do processo descrito nesse artigo, a consulta não usufrui de nenhuma dobra de consulta das etapas de consulta subsequentes.

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

Observação

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

Conectores de dados com suporte

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

Conectar-se ao destino da fonte de dados

Observação

Para mostrar esse processo, este artigo usa o conector SQL Server e o banco de dados de exemplo AdventureWorks2019. A experiência pode variar de conector para conector, mas este artigo mostra os conceitos básicos sobre como habilitar recursos de dobramento de consulta em consultas nativas para os conectores com suporte.

Ao se conectar à fonte de dados, é importante que você se conecte ao nó ou ao nível em que deseja executar sua consulta nativa. Para o exemplo neste artigo, esse nó é o nível de 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 da fonte de dados aparece. A caixa de diálogo de navegação contém todos os objetos disponíveis aos quais a conexão é possível.

Nessa lista, você precisa selecionar o objeto em que a consulta nativa é executada (também conhecida como destino). Para este exemplo, esse objeto é o nível do banco de dados.

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

Captura de tela da 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 a opção Transformar Dados enfatizada.

Depois que a consulta chegar ao editor de Power Query, somente a etapa De origem deverá ser exibida no painel Etapas Aplicadas. Esta etapa contém uma tabela com todos os objetos disponíveis em seu banco de dados, semelhante à forma como eles foram exibidos na janela Navegador.

Captura de tela da consulta apenas com a etapa de origem.

Usar a função Value.NativeQuery

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

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

A primeira etapa foi definir o destino correto, que nesse caso é o banco de dados em que o código SQL é executado. Depois que uma etapa tiver o destino correto, você poderá selecionar essa etapa , nesse caso, origem nas 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 próximo parâmetro da função que tem o campo de registro EnableFolding definido como true.

Captura de tela da nova fórmula de etapa personalizada com o uso da função Value.NativeQuery e a consulta SQL explícita.

Observação

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

Depois de inserir a fórmula, é mostrado um aviso que exige que você habilite as consultas nativas a serem executadas para sua etapa específica. Selecione continuar para que esta etapa seja avaliada.

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

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

Testar dobramento de consulta

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

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

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

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

Para validar ainda mais qual consulta está sendo enviada para a fonte de dados, selecione e segure a etapa Linhas Filtradas (ou clique com o botão direito do mouse nela) e selecione a opção Exibir plano de consulta para verificar o plano de consulta para essa etapa.

No modo de exibição do plano de consulta, há um nó com o nome Value.NativeQuery que tem um texto de hiperlink Detalhes da exibição. Selecione este hiperlink para exibir a consulta exata que está sendo enviada para o banco de dados SQL Server.

A consulta nativa é encapsulada em torno de outra instrução SELECT para criar uma subconsulta do original. O Power Query busca sempre criar a consulta mais otimizada, considerando as transformações usadas e a consulta nativa fornecida.

Captura de tela do plano de consulta para a etapa Linhas filtradas.

Dica

Para cenários com erros porque não foi possível realizar a dobragem de consultas, é recomendável validar as etapas como uma subconsulta da consulta nativa original para verificar se há conflitos de sintaxe ou de contexto.