Riduzione delle query nelle query native

In Power Query è possibile definire una query nativa ed eseguirla nell'origine dati. L'articolo Importare dati da un database usando una query di database nativa illustra come eseguire questo processo con più origini dati. Tuttavia, usando il processo descritto in questo articolo, la query non sfrutta la riduzione delle query dai passaggi successivi della query.

Questo articolo illustra un metodo alternativo per creare query native sull'origine dati usando la funzione Value.NativeQuery e mantenere attivo il meccanismo di riduzione delle query per i passaggi successivi della query.

Nota

È consigliabile leggere la documentazione sulla riduzione delle query e sugli indicatori di riduzione delle query per comprendere meglio i concetti usati in questo articolo.

Connettori dati supportati

Il metodo descritto nelle sezioni successive si applica ai connettori dati seguenti:

• Amazon Redshift
• Dataverse(quando si usa un ambiente di calcolo avanzato)
• Google BigQuery
• PostgreSQL
• SAP HANA
• Snowflake
• SQL Server

Connessione di destinazione dall'origine dati

Nota

Per illustrare questo processo, questo articolo usa il connettore SQL Server e il database di esempio AdventureWorks2019. L'esperienza può variare dal connettore al connettore, ma questo articolo illustra i concetti fondamentali su come abilitare le funzionalità di riduzione delle query sulle query native per i connettori supportati.

Quando ci si connette all'origine dati, è importante connettersi al nodo o al livello in cui si vuole eseguire la query nativa. Per l'esempio riportato in questo articolo, tale nodo sarà a livello di database all'interno del server.

Dopo aver definito le impostazioni di connessione e aver specificato le credenziali per la connessione, verrà visualizzata la finestra di dialogo di spostamento per l'origine dati. In questa finestra di dialogo verranno visualizzati tutti gli oggetti disponibili a cui è possibile connettersi.

Da questo elenco è necessario selezionare l'oggetto in cui viene eseguita la query nativa (nota anche come destinazione). Per questo esempio, l'oggetto è a livello di database.

Nella finestra dello strumento di navigazione in Power Query fare clic con il pulsante destro del mouse sul nodo del database nella finestra dello strumento di navigazione e selezionare l'opzione Trasforma dati . Se si seleziona questa opzione, viene creata una nuova query della visualizzazione complessiva del database, ovvero la destinazione necessaria per eseguire la query nativa.

Una volta completata la query nell'editor di Power Query, nel riquadro Passaggi applicati verrà visualizzato solo il passaggio Origine . Questo passaggio contiene una tabella con tutti gli oggetti disponibili nel database, in modo analogo a come sono stati visualizzati nella finestra Strumento di navigazione.

Usare la funzione Value.NativeQuery

L'obiettivo di questo processo è eseguire il codice SQL seguente e applicare più trasformazioni con Power Query che possono essere ripiegate all'origine.

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

Il primo passaggio consiste nel definire la destinazione corretta, che in questo caso è il database in cui verrà eseguito il codice SQL. Una volta che un passaggio ha la destinazione corretta, è possibile selezionare tale passaggio, in questo caso Source in Applied Steps, e quindi selezionare il pulsante fx nella barra della formula per aggiungere un passaggio personalizzato. In questo esempio sostituire la Source formula con la formula seguente:

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

Il componente più importante di questa formula è l'uso del record facoltativo per il parametro forth della funzione con il campo del record EnableFolding impostato su true.

Nota

Per altre informazioni sulla funzione Value.NativeQuery, vedere l'articolo della documentazione ufficiale.

Dopo aver immesso la formula, verrà visualizzato un avviso che richiederà l'esecuzione delle query native per il passaggio specifico. È possibile fare clic su Continua per valutare questo passaggio.

Questa istruzione SQL restituisce una tabella con solo tre righe e due colonne.

Riduzione delle query di test

Per testare la riduzione della query della query, è possibile provare ad applicare un filtro a una delle colonne e verificare se l'indicatore di riduzione della query nella sezione passaggi applicati mostra il passaggio come piegato. In questo caso, è possibile filtrare la colonna DepartmentID in modo che abbia valori non uguali a due.

Dopo aver aggiunto questo filtro, è possibile verificare che gli indicatori di riduzione della query visualizzino ancora la riduzione della query in questo nuovo passaggio.

Per convalidare ulteriormente la query inviata all'origine dati, è possibile fare clic con il pulsante destro del mouse sul passaggio Righe filtrate e selezionare l'opzione Visualizza piano di query per controllare il piano di query per tale passaggio.

Nella visualizzazione piano di query è possibile notare che un nodo con il nome Value.NativeQuery a sinistra della schermata con un testo collegamento ipertestuale che legge i dettagli della visualizzazione. È possibile fare clic su questo testo del collegamento ipertestuale per visualizzare la query esatta inviata al database di SQL Server.

La query nativa viene sottoposta a wrapping intorno a un'altra istruzione edizione Standard LECT per creare una sottoquery dell'originale. Power Query farà del suo meglio per creare la query più ottimale in base alle trasformazioni usate e alla query nativa fornita.

Suggerimento

Per gli scenari in cui si verificano errori perché la riduzione delle query non è possibile, è consigliabile provare a convalidare i passaggi come sottoquery della query nativa originale per verificare se potrebbero verificarsi conflitti di sintassi o contesto.