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.

Úprava závazků SQLCapabilities vašeho 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.

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

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 pak vyberte Transformovat data.

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. Prověřte řádek vzorců posledního kroku, abyste lépe pochopili, jak má váš konektor před spuštěním přejít k cíli nativních dotazů. 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 uvnitř tohoto záznamu vám pomůžou přecházet do tabulky 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 se řádek nachází, [Data] umožní {} Power Query 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).

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 .

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.

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 .

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.

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 se vyžadují nebo vyžadují, 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.

NavigationSteps

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 tak, jak je to, 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é

V názvu polí se rozlišují malá a velká písmena a musí se použít, jak je znázorněno v ukázce 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 od zadaného uživatele, 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 funkci předat volitelné parametry Value.NativeQuery při použití nativní funkce 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.

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.

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 posouvání dotazů a explicitně je definovaný EnableFolding=true jako součást volitelného záznamu, Value.NativeQuerymůžete konektor dále otestovat v editoru Power Query tak, že zkontrolujete, jestli se další transformace přeloží zpět do zdroje nebo ne.