Podpora natívneho dotazu vo vlastných konektoroch Doplnku Power Query
Poznámka
Tento článok zahŕňa pokročilé témy týkajúce sa implementácie podpory natívnych dotazov pre vlastné konektory, ako aj postupné posúvanie dotazov nad nimi. Tento článok predpokladá, že už máte pracovné vedomosti o týchto pojmoch.
Ďalšie informácie o vlastných konektoroch Power Query nájdete v téme Prehľad súpravy SDK doplnku Power Query.
V doplnku Power Query môžete vykonávať vlastné natívne dotazy voči zdroju údajov a načítať tak údaje, ktoré hľadáte. Môžete tiež umožniť možnosť spravovať postupné posúvanie dotazov v rámci tohto procesu a následných procesov transformácie vykonávaných v rámci doplnku Power Query.
Cieľom tohto článku je predstaviť, ako môžete implementovať takúto možnosť pre vlastný konektor.
Tento článok používa ako východiskový bod ukážku, ktorá používa ovládač SQL ODBC pre svoj zdroj údajov. Implementácia možnosti natívneho dotazu je v súčasnosti podporovaná len pre konektory ODBC, ktoré dodržiavajú štandard SQL-92.
Ukážkový konektor používa ovládač natívneho klienta SQL Servera 11.0 . Uistite sa, že máte nainštalovaný tento ovládač, aby ste mohli postupovať podľa tohto kurzu.
Môžete si tiež zobraziť hotovú verziu ukážkového konektora z priečinka Finish (Dokončiť) v odkladacom priestore GitHub.
V zázname SqlCapabilities
ukážkového konektora môžete nájsť pole záznamu s názvom Sql92Translation
a hodnotou PassThrough pre neho. Toto nové pole je potrebné na to, aby natívny dotaz bol odovzdaný pomocou doplnku Power Query bez akéhokoľvek overenia.
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"
]),
Pred presunom do konektora sa uistite, že toto pole sa zobrazuje v konektore. Ak nie, budete neskôr čeliť upozorneniam a chybám pri použití funkcie, ktorá nie je podporovaná, pretože nie je deklarovaná konektorom.
Vytvorte súbor konektora (vo formáte .mez alebo.pqx) a načítajte ho do aplikácie Power BI Desktop s cieľom manuálneho testovania a definujte cieľ svojho natívneho dotazu.
Poznámka
V tomto článku budeme používať ukážkovou databázu AdventureWorks2019. Môžete však postupovať podľa svojho výberu spolu s akoukoľvek databázou SQL Servera a vykonať potrebné zmeny, pokiaľ ide o konkrétne údaje vybratej databázy.
Podpora natívneho dotazu sa bude implementovať v tomto článku tak, že od používateľa sa bude žiadať, aby zadajte tri hodnoty:
- Názov servera
- Názov databázy
- Natívny dotaz na úrovni databázy
Teraz v aplikácii Power BI Desktop prejdite na prostredie Získať údaje a nájdite konektor s názvom SqlODBC Sample.
V dialógovom okne konektora zadajte parametre servera a názov databázy. Potom vyberte OK.
Zobrazí sa nové okno navigátora. V Navigátore môžete zobraziť natívne správanie navigácie z ovládača SQL, ktoré zobrazuje hierarchické zobrazenie servera a databáz v ňom. Kliknite pravým tlačidlom myši na databázu AdventureWorks2019 a potom vyberte položku Transformovať údaje.
Týmto výberom prejdete do editora Power Query a zobrazí sa ukážka toho, čo je v podstate cieľom natívneho dotazu, keďže všetky natívne dotazy by sa mali spúšťať na úrovni databázy. Skontrolujte riadok vzorcov posledného kroku, aby ste lepšie porozumeli tomu, ako by mal konektor prejsť na cieľ vašich natívnych dotazov pred ich vykonaním. V tomto prípade riadok vzorcov 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 konektora s parametrami, ktoré boli odovzdané. Zoznam a záznam v ňom sa jednoducho pomôžu navigovať v tabuľke na konkrétny riadok. Riadok je definovaný kritériami zo záznamu, kde sa pole Name musí rovnať databáze AdventureWorks2019 a pole Kind musí byť rovné databáze. Keď sa riadok nachádza, [Data]
vďaka vonkajšej časti zoznamu {}
umožní power query pristupovať k hodnote v rámci údajového poľa, čo je v tomto prípade tabuľka. Ak chcete tejto navigácii porozumieť lepšie, vráťte sa k predchádzajúcemu kroku (Zdroj).
Keď je cieľ teraz identifikovaný, vytvorte vlastný krok za krokom navigácie výberom ikony fx v riadku vzorcov.
Nahraďte vzorec v riadku vzorcov nasledujúcim vzorcom a potom vyberte položku Enter.
= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
FROM [Person].[Address]")
Po použití tejto zmeny by sa malo pod riadkom vzorcov zobraziť upozornenie požadujúce povolenie na spustenie natívneho dotazu vo vašom zdroji údajov.
Vyberte položku Upraviť povolenie. Zobrazí sa nové dialógové okno Natívny databázový dotaz , ktoré sa vás pokúsi upozorniť na možnosti spúšťania natívnych dotazov. V tomto prípade vieme, že tento príkaz SQL je bezpečný, takže výberom položky Spustiť spustite príkaz.
Po spustení dotazu sa v editore Power Query zobrazí ukážka dotazu. Táto ukážka overí, či je váš konektor schopný spúšťať natívne dotazy.
S informáciami zhromaždenými z predchádzajúcich častí je teraz cieľom preložiť tieto informácie do kódu konektora.
Tento preklad môžete dosiahnuť tak, že do záznamu publikovať konektora pridáte nové pole záznamu NativeQueryProperties, čo je SqlODBC.Publish
v tomto prípade záznam. Záznam NativeQueryProperties
hrá kľúčovú rolu pri definovaní spôsobu interakcie konektora s funkciou Value.NativeQuery
.
Nové pole záznamu sa skladá z dvoch polí:
- NavigationSteps (Navigácia) : Toto pole definuje, ako by mal váš konektor vykonávať alebo pracovať s navigáciou. Obsahuje zoznam záznamov, ktoré popisujú kroky na prechod ku konkrétnym údajom, ktoré chcete dotazovať pomocou
Value.NativeQuery
funkcie. V rámci každého záznamu definuje, ktoré parametre sú potrebné alebo potrebné na to, aby takáto navigácia dosiahla požadovaný cieľ. - DefaultOptions: Toto pole pomáha identifikovať, ako je potrebné zahrnúť alebo pridať určité voliteľné parametre do záznamu
Value.NativeQuery
možností. Poskytuje množinu predvolených možností, ktoré možno použiť pri dotazovaní zdroja údajov.
Navigačné kroky možno kategorizovať do dvoch skupín. Prvý obsahuje hodnoty, ktoré zadáva koncový používateľ, ako napríklad názov servera alebo databázy. Druhá časť obsahuje tie hodnoty, ktoré sú odvodené konkrétnou implementáciou konektora, ako napríklad názov polí, ktoré sa používateľovi počas načítania údajov nezobrazujú. Tieto polia môžu zahŕňať Name
, Kind
, Data
a ďalšie v závislosti od implementácie vášho konektora.
V tomto prípade bol len jeden krok navigácie, ktorý sa skladal z dvoch polí:
- Názov: toto pole je názov databázy, ktorú odovzdal koncový používateľ. V tomto prípade to bola
AdventureWorks2019
hodnota , ale toto pole by sa malo vždy odovzdávať tak, ako je z toho, čo zadal koncový používateľ počas získavania údajov. - Druh: Toto pole je informácia, ktorá sa nezobrazuje koncovému používateľovi a je špecifická pre konektor alebo implementáciu ovládača. V tomto prípade táto hodnota identifikuje, ku ktorému typu objektu by sa malo získať prístup. V tejto implementácii bude toto pole pevnou hodnotou, ktorá sa skladá z reťazca
Database
.
Tieto informácie budú preložené do nasledujúceho kódu. Tento kód by sa mal do SqlODBC.Publish
záznamu pridať ako nové pole.
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
}
]
Dôležité
V názve polí sa rozlišujú malé a veľké písmená a musia sa použiť tak, ako je to znázornené vo vyššie uvedenej ukážke. Všetky informácie odovzdané do polí, buď ConstantValue
, IndexName
alebo FieldDisplayName
, musia byť odvodené od kódu M konektora.
V prípade hodnôt, ktoré sa prenesú z toho, čo zadal používateľ, môžete použiť pár FieldDisplayName
a IndexName
. V prípade hodnôt, ktoré sú pevne alebo preddefinované a ktoré nemôžu byť odovzdané koncovému používateľovi, môžete použiť pár ConstantValue
a IndexName
. Záznam NavigationSteps sa v tomto zmysle skladá z dvoch polí:
- Indexy: Definuje, ktoré polia a aké hodnoty sa majú použiť na prechod do záznamu, ktorý obsahuje cieľ funkcie
Value.NativeQuery
. - FieldAccess: Definuje, aké pole bude obsahovať cieľ, čo je zvyčajne tabuľka.
Pole DefaultOptions
umožňuje odovzdať do Value.NativeQuery
funkcie voliteľné parametre pri použití možnosti natívneho dotazu pre konektor.
Ak chcete zachovať postupné posúvanie dotazov po natívnom dotaze a za predpokladu, že konektor obsahuje možnosti postupného posúvania dotazov, môžete pre použiť nasledujúci vzorový kód pre EnableFolding = true
.
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
},
DefaultOptions = [
EnableFolding = true
]
]
Po týchto zmenách vytvorte konektor a načítajte ho do aplikácie Power BI Desktop na testovanie a overenie.
V aplikácii Power BI Desktop s novým vlastným konektorom spustite konektor z prostredia Získať údaje . Pri spustení konektora si všimnete, že dialógové okno má teraz dlhé textové pole s názvom Natívny dotaz a v zátvorieku obsahuje požadované polia, aby fungoval. Zadajte rovnaké hodnoty pre server, databázu a príkaz SQL, ktorý ste predtým zadali pri testovaní konektora.
Po výbere tlačidla OK sa v novom dialógovom okne zobrazí ukážka tabuľky vykonaného natívneho dotazu.
Vyberte položku OK. Nový dotaz sa teraz načíta v editore Power Query, kde môžete podľa potreby vykonať ďalšie testovanie konektora.
Poznámka
Ak má konektor funkcie postupného posúvania dotazov a je explicitne definovaný EnableFolding=true
ako súčasť voliteľného záznamu pre Value.NativeQuery
, potom môžete svoj konektor ďalej otestovať v editore Power Query tak, že skontrolujete, či sa ďalšie transformácie posúvajú späť do zdroja alebo nie.