Natívna podpora dotazov v Power Query vlastných konektoroch

Nota

Tento článok pokrýva pokročilé témy týkajúce sa implementácie natívnej podpory dotazov pre vlastné konektory, ako aj query folding nad nimi. Tento článok predpokladá, že už máte praktické znalosti týchto konceptov.

Ak sa chcete dozvedieť viac o vlastných konektoroch Power Query, navštívte prehľad Power Query SDK.

V Power Query môžete vykonávať vlastné natívne dotazy na vašom dátovom zdroji, aby ste získali požadované údaje. Môžete tiež povoliť schopnosť udržiavať query folding počas celého tohto procesu a následných transformačných procesov vykonaných v Power Query.

Cieľom tohto článku je ukázať, ako môžete takúto schopnosť implementovať pre svoj vlastný konektor.

Požiadavky

Tento článok používa ako východiskový bod ukážku , ktorá používa SQL ODBC ovládač ako zdroj dát. Implementácia natívnej možnosti dotazovania je momentálne podporovaná iba pre ODBC konektory, ktoré spĺňajú štandard SQL-92.

Ukážkový konektor používa SQL Server Native Client 11.0 ovládač. Uistite sa, že máte nainštalovaný tento ovládač, aby ste mohli sledovať tento návod.

Hotovú verziu sample connectoru si môžete pozrieť aj z priečinka Finish v GitHub Repository.

Upravte SQLCapability vášho konektora

V SqlCapabilities zázname sample connectoru nájdete pole záznamu s názvom Sql92Translation a hodnotou PassThrough pre ňu. Toto nové pole je nevyhnutné na to, aby natívny dotaz mohol byť odoslaný pomocou Power Query bez akejkoľvek validácie.

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"
]),

Uistite sa, že toto pole sa objaví vo vašom konektore predtým, než pôjdete ďalej. Ak nie, neskôr budete čeliť varovaniam a chybám, keď príde na použitie funkcie, ktorá nie je podporovaná, pretože nie je deklarovaná konektorom.

Vytvorte konektorový súbor (ako .mez alebo .pqx) a nahrajte ho do Power BI Desktop na manuálne testovanie a definovanie cieľa pre váš natívny dotaz.

Manuálne otestujte natívne možnosti dotazovania vášho konektora

Nota

Pre tento článok použijeme ukážkovú databázu AdventureWorks2019. Ale môžete sa riadiť ktoroukoľvek SQL Server databázou podľa vlastného výberu a robiť potrebné zmeny, pokiaľ ide o špecifiká vybranej databázy.

Spôsob, akým bude natívna podpora dotazov implementovaná v tomto článku, je, že používateľ bude požiadaný o zadanie troch hodnôt:

  • Názov servera
  • Názov databázy
  • Natívny dotaz na úrovni databázy

Teraz vo vnútri Power BI Desktop choďte do Get Data experience a nájdite konektor s názvom SqlODBC Sample.

Snímka obrazovky konektora nájdeného vo vnútri zážitku s získaním dát v Power BI Desktop.

Pre dialógové okno konektora zadajte parametre servera a názov databázy. Potom vyberte OK.

Snímka obrazovky dialógu konektora so serverom a databázou ako parametrami.

Objaví sa nové okno navigátora. V Navigatori môžete vidieť natívne správanie navigácie z SQL ovládača, ktorý zobrazuje hierarchický pohľad na server a databázy v ňom. Kliknite pravým tlačidlom na databázu AdventureWorks2019 a vyberte Transformovať dáta.

Snímka obrazovky možnosti transformačných dát z kontextového menu v okne Navigátora.

Tento výber vás zavedie do Power Query editora a k náhľadu toho, čo je v podstate cieľom vášho natívneho dotazu, keďže všetky natívne dotazy by mali bežať na úrovni databázy. Skontrolujte vzorec posledného kroku, aby ste lepšie pochopili, ako by mal váš konektor navigovať k cieľu natívnych dotazov pred ich vykonaním. V tomto prípade ukazovateľ vzorca zobrazuje nasledujúce informácie:

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

Zdroj je názov predchádzajúceho kroku, ktorý je v tomto prípade jednoducho publikovanou funkciou vášho konektora s odovzdanými parametrami. Zoznam a záznam v ňom len pomáhajú navigovať tabuľku k konkrétnemu riadku. Riadok je definovaný kritériami zo záznamu, kde pole Názov musí byť rovné AdventureWorks2019 a pole Kind musí byť rovné Database. Keď je riadok nájdený, vonkajšia [Data] strana zoznamu {} umožňuje Power Query prístup k hodnote vo vnútri dátového poľa, čo je v tomto prípade tabuľka. Môžete sa vrátiť k predchádzajúcemu kroku (Zdroj), aby ste lepšie pochopili túto navigáciu.

Snímka obrazovky tabuľky, ktorá ukazuje hodnoty a polia použité v navigačnom kroku.

Testujte natívny dotaz

Keď je cieľ teraz identifikovaný, vytvorte vlastný krok po navigačnom kroku výberom ikony efektov v lište vzorcov.

Screenshot tlačidla fx vo vnútri vzorca, ktorý sa používa na vytvorenie vlastného kroku.

Nahraďte vzorec v riadku vzorca nasledujúcim vzorcom a potom vyberte Enter.

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

Po aplikácii tejto zmeny by sa pod riadkom vzorca mala objaviť výstraha žiadajúca povolenie spustiť natívny dotaz na vašom dátovom zdroji.

Na spustenie tejto natívnej varovnej správy o databázovom dotaze je potrebný screenshot povolenia.

Vyberte Upraviť povolenie. Zobrazí sa nové okno natívneho databázového dotazu , ktoré sa vás snaží varovať pred možnosťou spúšťania natívnych dotazov. V tomto prípade vieme, že tento SQL príkaz je bezpečný, preto vyberte Run na vykonanie príkazu.

Snímka obrazovky ukazujúca, ako schváliť natívny dialóg v databázovom dotaze.

Po spustení dotazu sa v editore Power Query zobrazí náhľad vášho dotazu. Tento náhľad overuje, že váš konektor je schopný spúšťať natívne dotazy.

Snímka obrazovky natívneho dotazu vykonaného pri počiatočnom vývoji a testovaní konektora.

Implementujte natívnu logiku dotazov vo vašom konektore

S informáciami získanými z predchádzajúcich sekcií je teraz cieľom preložiť tieto informácie do kódu pre váš konektor.

Tento preklad môžete dosiahnuť pridaním nového poľa záznamu NativeQueryProperties do záznamu Publish vášho konektora, čo je v tomto prípade záznam.SqlODBC.Publish Záznam NativeQueryProperties zohráva kľúčovú úlohu pri definovaní, ako bude konektor interagovať s funkciou Value.NativeQuery .

Nové pole záznamov pozostáva z dvoch polí:

  • NavigationSteps: Toto pole definuje, ako by mala byť navigácia vykonávaná alebo spravovaná vaším konektorom. Obsahuje zoznam záznamov, ktoré načrtávajú kroky na navigáciu ku konkrétnym dátam, ktoré chcete dotazovať pomocou funkcie Value.NativeQuery . V každom zázname definuje, aké parametre sú potrebné alebo potrebné, aby takáto navigácia dosiahla požadovaný cieľ.
  • DefaultOptions: Toto pole pomáha určiť, ako by mali byť určité voliteľné parametre zahrnuté alebo pridané do záznamu Value.NativeQuery možností. Poskytuje súbor predvolených možností, ktoré možno použiť pri dotazovaní na zdroj dát.

Vaše navigačné kroky možno rozdeliť do dvoch skupín. Prvá obsahuje hodnoty, ktoré zadá koncový používateľ, napríklad názov servera alebo databázy, v tomto prípade. Druhá obsahuje hodnoty, ktoré sú odvodené konkrétnou implementáciou konektora, napríklad názvy polí, ktoré sa používateľovi počas získavania dát nezobrazujú. Tieto polia môžu zahŕňať Name, Kind, Data, a ďalšie v závislosti od implementácie konektora.

V tomto prípade existoval len jeden navigačný krok, ktorý pozostával z dvoch polí:

  • Meno: Toto pole je názov databázy, ktorú odovzdal koncový používateľ. V tomto prípade to bolo AdventureWorks2019, ale toto pole by sa malo vždy odovzdať as-is toho, čo koncový používateľ zadal počas získania dát.
  • Kind: Toto pole je informácia, ktorá nie je viditeľná pre koncového používateľa a je špecifická pre konektor alebo implementáciu ovládača. V tomto prípade táto hodnota určuje, aký typ objektu by mal byť prístupný. Pre túto implementáciu bude toto pole pevnou hodnotou, ktorá pozostáva z reťazca Database.

Tieto informácie budú preložené do nasledujúceho kódu. Tento kód by mal byť pridaný ako nové pole do vášho SqlODBC.Publish záznamu.

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

Dôležité

Názvy polí sú rozlíšené od veľkých písmen a musia sa použiť, ako je uvedené vo vzorke vyššie. Všetky informácie odovzdávané poliam, buď ConstantValue, IndexName, alebo FieldDisplayName musia byť odvodené z M kódu konektora.

Pre hodnoty, ktoré budú odovzdané z toho, čo používateľ zadal, môžete použiť pár FieldDisplayName a IndexName. Pre hodnoty, ktoré sú pevné alebo preddefinované a koncový používateľ ich nemôže odovzdať, môžete použiť dvojicu ConstantValue a IndexName. V tomto zmysle záznam NavigationSteps pozostáva z dvoch polí:

  • Indexy: Definuje, ktoré polia a hodnoty použiť na prechod k záznamu, ktorý obsahuje cieľ funkcie Value.NativeQuery .
  • FieldAccess: Definuje, ktoré pole obsahuje cieľ, čo je zvyčajne tabuľka.

DefaultOptions

Pole DefaultOptions vám umožňuje posielať voliteľné parametre funkcii Value.NativeQuery pri použití natívnej dotazovacej funkcie pre váš konektor.

Aby ste zachovali query folding po natívnom dotaze a za predpokladu, že váš konektor má schopnosti query foldingu, môžete použiť nasledujúci ukážkový kód pre EnableFolding = true.

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

S týmito zmenami vytvorte konektor a nahrajte ho do Power BI Desktop na testovanie a validáciu.

Otestujte a overte konektor

V Power BI Desktop, keď máte nový vlastný konektor, spustite konektor cez Get Data experience. Pri spustení konektora si všimnete, že dialóg má teraz dlhé textové pole s názvom Native query a v zátvorke obsahuje potrebné polia na jeho fungovanie. Zadajte rovnaké hodnoty pre server, databázu a SQL príkaz, ktoré ste predtým zadali pri testovaní konektora.

Screenshot dialógu konektora s natívnym poľom dlhého textu dotazu.

Po výbere OK sa v novom dialógu zobrazí náhľad tabuľky vykonaného natívneho dotazu.

Snímka obrazovky dialógu s náhľadom tabuľky vykonaného natívneho dotazu.

Vyberte položku OK. Nový dotaz sa teraz načíta v Power Query editore, kde môžete podľa potreby ďalej testovať konektor.

Nota

Ak váš konektor má schopnosti query folding a explicitne EnableFolding=true ho definoval ako súčasť voliteľného záznamu pre Value.NativeQuery, môžete svoj konektor ďalej otestovať v Power Query editore tým, že skontrolujete, či sa ďalšie transformácie vrátia späť do zdroja alebo nie.

  • Last updated on