Sdílet prostřednictvím

Nativní podpora dotazů ve vlastních konektorech Power Query

Poznámka:

Tento článek popisuje pokročilá témata týkající se implementace nativní podpory dotazů pro vlastní konektory a posouvání dotazů nad nimi. Tento článek předpokládá, že už máte funkční znalosti těchto konceptů.

Další informace o vlastních konektorech Power Query najdete v přehledu sady Power Query SDK.

V Power Query můžete spouštět vlastní nativní dotazy na zdroj dat a načítat data, která hledáte. Můžete také povolit možnost udržovat posouvání dotazů v průběhu tohoto procesu a následných transformačních procesů provedených v Power Query.

Cílem tohoto článku je ukázat, jak můžete takové funkce implementovat pro vlastní konektor.

Požadavky

Tento článek používá jako výchozí bod ukázku , která používá ovladač SQL ODBC pro svůj zdroj dat. Implementace nativní funkce dotazu je v současné době podporována pouze pro konektory ODBC, které odpovídají standardu SQL-92.

Ukázkový konektor používá ovladač SQL Server Native Client 11.0 . Ujistěte se, že máte tento ovladač nainstalovaný, abyste mohli postupovat podle tohoto kurzu.

Dokončenou verzi ukázkového konektoru můžete zobrazit také ze složky Dokončit v úložišti GitHub.

Upravte schopnosti SQL vašeno konektoru

SqlCapabilities V záznamu ukázkového konektoru najdete pole záznamu s názvem Sql92Translation a hodnotou PassThrough pro něj. Toto nové pole je nezbytné pro předání nativního dotazu pomocí Power Query bez ověření.

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

Před přechodem vpřed se ujistěte, že se toto pole zobrazuje ve vašem konektoru. Pokud ne, zobrazí se vám upozornění a chyby později, pokud jde o použití funkce, která není podporována, protože není deklarována konektorem.

Sestavte soubor konektoru (jako .mez nebo.pqx) a načtěte ho do Power BI Desktopu pro ruční testování a definujte cíl nativního dotazu.

Ruční testování nativních možností dotazů vašeho konektoru

Poznámka:

V tomto článku budeme používat ukázkovou databázi AdventureWorks2019. Můžete ale postupovat společně s libovolnou databází SQL Serveru podle vašeho výběru a provést potřebné změny, pokud jde o specifika zvolené databáze.

Způsob implementace nativní podpory dotazů v tomto článku spočívá v tom, že uživatel bude požádán o zadání tří hodnot:

  • Název serveru
  • Název databáze
  • Nativní dotaz na úrovni databáze

Teď v Power BI Desktopu přejděte do prostředí Získat data a najděte konektor s názvem SqlODBC Sample.

Snímek obrazovky s konektorem, který najdete v prostředí pro získání dat v Power BI Desktopu

V dialogovém okně konektoru zadejte parametry pro váš server a název databáze. Pak vyberte OK.

Snímek obrazovky dialogového okna konektoru se serverem a databází jakožto parametry.

Zobrazí se nové okno navigátoru. V navigátoru můžete zobrazit nativní navigační chování z ovladače SQL, který zobrazuje hierarchické zobrazení serveru a databází v něm. Klikněte pravým tlačítkem myši na databázi AdventureWorks2019 a poté vyberte Transformovat data.

Snímek obrazovky s možností transformovat data z místní nabídky v okně Navigátor

Tento výběr vám přináší editor Power Query a náhled toho, co je v podstatě cílem nativního dotazu, protože všechny nativní dotazy by se měly spouštět na úrovni databáze. Prozkoumejte řádek vzorců v posledním kroku, abyste lépe porozuměli tomu, jak by měl váš konektor navigovat k cíli vašich nativních dotazů dříve, než je spustíte. V tomto případě řádek vzorců zobrazí následující informace:

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

Zdrojem je název předchozího kroku, který v tomto případě představuje jednoduše publikovanou funkci konektoru s předanými parametry. Seznam a záznam v něm vám pomohou navigovat v tabulce na konkrétní řádek. Řádek je definován kritérii ze záznamu, kde musí být pole Název rovna AdventureWorks2019 a pole Kind musí být rovno Databázi. Jakmile je řádek nalezen, [Data] mimo seznam {} umožní Power Query získat přístup k hodnotě v poli Data, což je v tomto případě tabulka. Pokud chcete lépe porozumět této navigaci, můžete se vrátit k předchozímu kroku (zdroj).

Snímek obrazovky s tabulkou, která zobrazuje hodnoty a pole použitá pro navigační krok

Testování nativního dotazu

Po identifikaci cíle vytvořte vlastní krok po navigačním kroku tak, že v řádku vzorců vyberete ikonu fx .

Snímek obrazovky s tlačítkem fx uvnitř vzorce, který se používá k vytvoření vlastního kroku

Nahraďte vzorec v řádku vzorců následujícím vzorcem a pak vyberte Enter.

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

Po použití této změny by se mělo pod řádkem vzorců zobrazit upozornění s žádostí o oprávnění ke spuštění nativního dotazu ve zdroji dat.

Snímek obrazovky s oprávněním je nutný ke spuštění této zprávy upozornění nativního databázového dotazu.

Vyberte Upravit oprávnění. Zobrazí se nové dialogové okno nativního databázového dotazu , které vás pokusí upozornit na možnosti spouštění nativních dotazů. V tomto případě víme, že tento příkaz SQL je bezpečný, takže příkaz spusťte výběrem příkazu Spustit .

Snímek obrazovky znázorňující, jak schválit dialogové okno nativního databázového dotazu

Po spuštění dotazu se v editoru Power Query zobrazí náhled dotazu. Tato verze Preview ověří, že váš konektor dokáže spouštět nativní dotazy.

Snímek obrazovky s nativním dotazem spuštěným při počátečním vývoji a testování konektoru

Implementace nativní logiky dotazů v konektoru

S informacemi shromážděnými z předchozích částí je teď cílem tyto informace přeložit do kódu vašeho konektoru.

Tento překlad můžete provést tak, že do záznamu Publish vašeho konektoru přidáte nové pole záznamu NativeQueryProperties, což je SqlODBC.Publish v tomto případě záznam. Záznam NativeQueryProperties hraje zásadní roli při definování způsobu interakce konektoru Value.NativeQuery s funkcí.

Nové pole záznamu se skládá ze dvou polí:

  • NavigationSteps: Toto pole definuje, jak má být navigace provedena nebo zpracována konektorem. Obsahuje seznam záznamů, které popisují kroky pro navigaci na konkrétní data, která chcete pomocí Value.NativeQuery funkce dotazovat. V rámci každého záznamu definuje, jaké parametry jsou potřeba, aby tato navigace dosáhla požadovaného cíle.
  • DefaultOptions: Toto pole pomáhá určit, jak mají být určité volitelné parametry zahrnuty nebo přidány do záznamu Value.NativeQuery možností. Poskytuje sadu výchozích možností, které lze použít při dotazování zdroje dat.

Navigační kroky je možné kategorizovat do dvou skupin. První obsahuje hodnoty zadané koncovým uživatelem, například název serveru nebo databáze, v tomto případě. Druhá obsahuje hodnoty odvozené konkrétní implementací konektoru, například název polí, která se uživateli nezobrazují během získávání dat. Tato pole můžou zahrnovat Name, Kind, Dataa další v závislosti na implementaci konektoru.

V tomto případě došlo pouze k jednomu navigačnímu kroku, který se skládá ze dvou polí:

  • Název: Toto pole je název databáze, kterou předal koncový uživatel. V tomto případě to bylo AdventureWorks2019, ale toto pole by mělo být vždy předáno as-is z toho, co koncový uživatel zadal během získávání dat.
  • Druh: Toto pole je informace, které nejsou viditelné pro koncového uživatele a jsou specifické pro implementaci konektoru nebo ovladače. V tomto případě tato hodnota identifikuje, k jakému typu objektu se má přistupovat. Pro tuto implementaci bude toto pole pevnou hodnotou, která se skládá z řetězce Database.

Tyto informace se přeloží do následujícího kódu. Tento kód by se měl přidat jako nové pole do záznamu SqlODBC.Publish .

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

Důležité

Názvy polí rozlišují malá a velká písmena a musí být použity, jak je uvedeno v příkladě výše. Všechny informace předávané do polí , buď ConstantValue, IndexNamenebo FieldDisplayName musí být odvozeny z kódu M konektoru.

Pro hodnoty, které budou předány z toho, co uživatel zadal, můžete použít dvojici FieldDisplayName a IndexName. U hodnot, které jsou pevné nebo předdefinované a nelze je předat koncovému uživateli, můžete použít dvojici ConstantValue a IndexName. V tomto smyslu se záznam NavigationSteps skládá ze dvou polí:

  • Indexy: Definuje, která pole a jaké hodnoty se mají použít k přechodu na záznam obsahující cíl funkce Value.NativeQuery .
  • FieldAccess: Definuje, jaké pole obsahuje cíl, což je obvykle tabulka.

DefaultOptions

Pole DefaultOptions umožňuje předat funkci Value.NativeQuery volitelné parametry při použití nativní schopnosti dotazu pro váš konektor.

Pokud chcete zachovat posouvání dotazů po nativním dotazu a za předpokladu, že váš konektor má možnosti posouvání dotazů, můžete použít následující ukázkový kód pro EnableFolding = true.

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

Po provedení těchto změn sestavte konektor a načtěte ho do Power BI Desktopu pro účely testování a ověřování.

Testování a ověření konektoru

V Power BI Desktopu s novým vlastním konektorem spusťte konektor z prostředí Získat data . Při spuštění konektoru si všimnete, že dialogové okno teď obsahuje dlouhé textové pole s názvem Nativní dotaz a v závorkách má požadovaná pole, aby fungovala. Zadejte stejné hodnoty pro server, databázi a příkaz SQL, který jste předtím zadali při testování konektoru.

Snímek obrazovky s dialogovým oknem konektoru se zobrazeným textovým polem s dlouhou délkou nativního dotazu

Po výběru možnosti OK se v novém dialogovém okně zobrazí náhled tabulky spuštěného nativního dotazu.

Snímek obrazovky s dialogovým oknem s náhledem tabulky spuštěného nativního dotazu

Vyberte OK. Nový dotaz se teď načte v editoru Power Query, kde můžete podle potřeby provést další testování konektoru.

Poznámka:

Pokud má váš konektor možnosti skládání dotazů a explicitně definuje EnableFolding=true jako součást volitelného záznamu pro Value.NativeQuery, můžete konektor dále testovat v editoru Power Query tím, že zkontrolujete, zda se další transformace vrátí zpět ke zdroji nebo ne.

  • Last updated on