Leggere in inglese

Condividi tramite


Supporto di query native nei connettori personalizzati di Power Query

Nota

Questo articolo illustra gli argomenti avanzati relativi all'implementazione del supporto di query native per i connettori personalizzati, oltre alla riduzione delle query. Questo articolo presuppone che tu abbia già una conoscenza funzionante di questi concetti.

Per altre informazioni sui connettori personalizzati di Power Query, vedere Panoramica di Power Query SDK.

In Power Query è possibile eseguire query native personalizzate sull'origine dati per recuperare i dati desiderati. È anche possibile abilitare la funzionalità per mantenere la riduzione delle query in questo processo e i processi di trasformazione successivi eseguiti all'interno di Power Query.

L'obiettivo di questo articolo è illustrare come implementare tale funzionalità per il connettore personalizzato.

Prerequisiti

Questo articolo usa come punto di partenza un esempio che usa il driver ODBC SQL per l'origine dati. L'implementazione della funzionalità di query nativa è attualmente supportata solo per i connettori ODBC conformi allo standard SQL-92.

Il connettore di esempio usa il driver SQL Server Native Client 11.0 . Assicurarsi di avere installato questo driver per seguire questa esercitazione.

È anche possibile visualizzare la versione completata del connettore di esempio dalla cartella Fine nel repository GitHub.

Modificare sqlCapabilities del connettore

SqlCapabilities Nel record del connettore di esempio è possibile trovare un campo di record con il nome Sql92Translation e il valore PassThrough. Questo nuovo campo è necessario per passare la query nativa usando Power Query senza alcuna convalida.

SqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", defaultConfig[SqlCapabilities] & [
    // Place custom overrides here
    // The values below are required for the SQL Native Client ODBC driver, but might
    // not be required for your data source.
        SupportsTop = false,
        SupportsDerivedTable = true,
        Sql92Conformance = 8 /* SQL_SC_SQL92_FULL */,
        GroupByCapabilities = 4 /* SQL_GB_NO_RELATION */,
        FractionalSecondsScale = 3,
        Sql92Translation = "PassThrough"
]),

Assicurarsi che questo campo venga visualizzato nel connettore prima di procedere. In caso contrario, verranno visualizzati avvisi ed errori in un secondo momento quando si tratta di usare una funzionalità non supportata perché non è dichiarata dal connettore.

Compilare il file del connettore (come .mez o.pqx) e caricarlo in Power BI Desktop per il test manuale e definire la destinazione per la query nativa.

Testare manualmente le funzionalità di query native del connettore

Nota

Per questo articolo si userà il database di esempio AdventureWorks2019. Tuttavia, è possibile seguire qualsiasi database di SQL Server a scelta e apportare le modifiche necessarie quando si tratta delle specifiche del database scelto.

Il modo in cui verrà implementato il supporto delle query native in questo articolo è che all'utente verrà richiesto di immettere tre valori:

  • Nome server
  • Nome database
  • Query nativa a livello di database

Ora all'interno di Power BI Desktop passare all'esperienza Recupera dati e trovare il connettore con il nome SqlODBC Sample.

Screenshot of the connector found inside the get data experience of Power BI Desktop.

Per la finestra di dialogo del connettore immettere i parametri per il server e il nome del database. Selezionare OK.

Screenshot of connector dialog with server and database as parameters.

Verrà visualizzata una nuova finestra dello strumento di navigazione. In Strumento di navigazione è possibile visualizzare il comportamento di spostamento nativo dal driver SQL che visualizza la visualizzazione gerarchica del server e dei database al suo interno. Fare clic con il pulsante destro del mouse sul database AdventureWorks2019 e quindi scegliere Trasforma dati.

Screenshot of the transform data option from the contextual menu inside the Navigator window.

Questa selezione consente di accedere all'editor di Power Query e a un'anteprima di ciò che è effettivamente la destinazione della query nativa, perché tutte le query native devono essere eseguite a livello di database. Esaminare la barra della formula dell'ultimo passaggio per comprendere meglio come il connettore deve passare alla destinazione delle query native prima di eseguirle. In questo caso la barra della formula visualizza le informazioni seguenti:

= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]

Source è il nome del passaggio precedente che, in questo caso, è semplicemente la funzione pubblicata del connettore con i parametri passati. L'elenco e il record all'interno di esso consentono di spostarsi in una tabella in una riga specifica. La riga è definita dai criteri del record in cui il campo Name deve essere uguale a AdventureWorks2019 e il campo Kind deve essere uguale a Database. Quando la riga si trova, l'esterno [Data] dell'elenco {} consente a Power Query di accedere al valore all'interno del campo Dati , che in questo caso è una tabella. È possibile tornare al passaggio precedente (Origine) per comprendere meglio questo spostamento.

Screenshot of a table that shows the values and fields that were used for the navigation step.

Testare la query nativa

Con la destinazione ora identificata, creare un passaggio personalizzato dopo il passaggio di spostamento selezionando l'icona fx nella barra della formula.

Screenshot of the fx button inside the formula that's used to create a custom step.

Sostituire la formula all'interno della barra della formula con la formula seguente e quindi selezionare INVIO.

= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
  FROM [Person].[Address]")

Dopo aver applicato questa modifica, viene visualizzato un avviso sotto la barra della formula che richiede l'autorizzazione per eseguire la query nativa sull'origine dati.

Screenshot of the permission is required to run this native database query warning message.

Selezionare Modifica autorizzazione. Viene visualizzata una nuova finestra di dialogo Query database nativa che tenta di avvisare l'utente delle possibilità di esecuzione di query native. In questo caso, si sa che questa istruzione SQL è sicura, quindi selezionare Esegui per eseguire il comando.

Screenshot showing how to approve a native database query dialog.

Dopo aver eseguito la query, viene visualizzata un'anteprima della query nell'editor di Power Query. Questa anteprima convalida che il connettore sia in grado di eseguire query native.

Screenshot of the native query executed in initial connector development and testing.

Implementare la logica di query nativa nel connettore

Con le informazioni raccolte dalle sezioni precedenti, l'obiettivo è ora tradurre tali informazioni nel codice per il connettore.

Il modo in cui è possibile eseguire questa conversione consiste nell'aggiungere un nuovo campo record NativeQueryProperties al record Publish del connettore, che in questo caso è il SqlODBC.Publish record. Il NativeQueryProperties record svolge un ruolo fondamentale nella definizione del modo in cui il connettore interagirà con la Value.NativeQuery funzione.

Il nuovo campo record è costituito da due campi:

  • NavigationSteps: questo campo definisce il modo in cui lo spostamento deve essere eseguito o gestito dal connettore. Contiene un elenco di record che descrivono i passaggi per passare ai dati specifici su cui eseguire una query usando la Value.NativeQuery funzione . All'interno di ogni record, definisce quali parametri sono necessari o necessari affinché tale spostamento raggiunga la destinazione desiderata.
  • DefaultOptions: questo campo consente di identificare il modo in cui determinati parametri facoltativi devono essere inclusi o aggiunti al record delle Value.NativeQuery opzioni. Fornisce un set di opzioni predefinite che possono essere usate durante l'esecuzione di query sull'origine dati.

I passaggi di spostamento possono essere classificati in due gruppi. Il primo contiene i valori immessi dall'utente finale, ad esempio il nome del server o del database, in questo caso. Il secondo contiene i valori derivati dall'implementazione specifica del connettore, ad esempio il nome dei campi che non vengono visualizzati all'utente durante l'esperienza di recupero dei dati. Questi campi possono includere Name, Kind, Datae altri a seconda dell'implementazione del connettore.

Per questo caso, era presente un solo passaggio di navigazione costituito da due campi:

  • Nome: questo campo è il nome del database passato dall'utente finale. In questo caso, era AdventureWorks2019, ma questo campo deve essere sempre passato così com'è da quello immesso dall'utente finale durante l'esperienza di recupero dei dati.
  • Tipo: questo campo è informazioni che non sono visibili all'utente finale ed è specifico per l'implementazione del connettore o del driver. In questo caso, questo valore identifica il tipo di oggetto a cui accedere. Per questa implementazione, questo campo sarà un valore fisso costituito dalla stringa Database.

Tali informazioni verranno convertite nel codice seguente. Questo codice deve essere aggiunto come nuovo campo al SqlODBC.Publish record.

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    }
]

Importante

Il nome dei campi fa distinzione tra maiuscole e minuscole e deve essere usato come illustrato nell'esempio precedente. Tutte le informazioni passate ai campi, ConstantValue, IndexNameo FieldDisplayName devono essere derivate dal codice M del connettore.

Per i valori che verranno passati da quello immesso dall'utente, è possibile usare la coppia FieldDisplayName e IndexName. Per i valori fissi o predefiniti e non possono essere passati dall'utente finale, è possibile usare la coppia ConstantValue e IndexName. In questo senso, il record NavigationSteps è costituito da due campi:

  • Indici: definisce i campi e i valori da usare per passare al record che contiene la destinazione per la Value.NativeQuery funzione.
  • FieldAccess: definisce il campo che contiene la destinazione, che in genere è una tabella.

DefaultOptions

Il DefaultOptions campo consente di passare parametri facoltativi alla Value.NativeQuery funzione quando si usa la funzionalità di query nativa per il connettore.

Per mantenere la riduzione delle query dopo una query nativa e presupponendo che il connettore abbia funzionalità di riduzione delle query, è possibile usare il codice di esempio seguente per EnableFolding = true.

NativeQueryProperties = [
    NavigationSteps = {
        [
            Indices = {
                [
                    FieldDisplayName = "database",
                    IndexName = "Name"
                ],
                [
                    ConstantValue = "Database",
                    IndexName = "Kind"
                ]
            },
            FieldAccess = "Data"
        ]
    },

    DefaultOptions = [
        EnableFolding = true
    ]
]

Con queste modifiche sul posto, compilare il connettore e caricarlo in Power BI Desktop per il test e la convalida.

Testare e convalidare il connettore

In Power BI Desktop con il nuovo connettore personalizzato sul posto avviare il connettore dall'esperienza Recupera dati . Quando si avvia il connettore, si noterà che la finestra di dialogo include ora un campo di testo lungo con il nome Query nativa e, tra parentesi, contiene i campi necessari per il funzionamento. Immettere gli stessi valori per il server, il database e l'istruzione SQL immessa in precedenza durante il test del connettore.

Screenshot of the connector dialog with the native query long text field shown.

Dopo aver selezionato OK, viene visualizzata un'anteprima della tabella della query nativa eseguita in una nuova finestra di dialogo.

Screenshot of the dialog with a table preview of the executed native query.

Seleziona OK. Una nuova query verrà ora caricata all'interno dell'editor di Power Query in cui è possibile eseguire ulteriori test del connettore in base alle esigenze.

Nota

Se il connettore dispone di funzionalità di riduzione delle query e ha definito EnableFolding=true in modo esplicito come parte del record facoltativo per Value.NativeQuery, è possibile testare ulteriormente il connettore nell'editor di Power Query controllando se ulteriori trasformazioni ripiegano nell'origine o meno.