共用方式為

Power Query 自訂連接器的原生查詢支援

備註

本文將探討自訂連接器原生 查詢 支援實作的進階主題，以及在其上 進行查詢摺疊 。 本文假設你已經對這些概念有基本認識。

想了解更多關於 Power Query 自訂連接器的資訊，請前往 Power Query SDK 概覽。

在 Power Query 中，你可以對資料來源執行自訂的原生查詢，取得你想要的資料。 你也可以啟用在 Power Query 中持續維持查詢摺疊的功能，並完成後續轉換過程。

本文的目標是展示如何為你的客製化連接器實作這些功能。

先決條件

本文以一個使用 SQL ODBC 驅動程式作為資料來源的 範例 作為起點。 目前原生查詢功能的實作僅支援遵循 SQL-92 標準的 ODBC 連接器。

範例連接器使用 SQL Server Native Client 11.0 驅動程式。 請確保你安裝了這個驅動程式，才能跟著這個教學操作。

你也可以從 GitHub 倉庫的 Finish 資料夾 查看完成版的樣品連接器。

修改你的連接器的 SQLCapabilities

在 SqlCapabilities 範例連接器的記錄中，你可以找到一個記錄欄位，裡面有名稱 Sql92Translation 和 PassThrough 的值。 這個新欄位是使用 Power Query 傳遞原生查詢且無需驗證的必要條件。

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

在繼續前，請確保這個欄位出現在你的連接器中。 如果沒有，之後您可能會遇到警告和錯誤，因為連接器未宣告該功能而導致其不受支援。

建立 connector 檔案（.mez 或 .pqx），載入 Power BI Desktop 進行手動測試，並定義你原生查詢的目標。

手動測試你連接器的原生查詢功能

備註

本文將使用 AdventureWorks2019範例資料庫。 但你可以跟隨任何你選擇的 SQL Server 資料庫，並在所選資料庫的細節上做出必要的調整。

本文將透過原生查詢支援，要求使用者輸入三個值：

  • 伺服器名稱
  • 資料庫名稱
  • 資料庫層級的原生查詢

現在在 Power BI Desktop 裡面，進入 「取得資料 」體驗，找到名為 SqlODBC 範例的連接器。

Power BI Desktop 取得資料體驗中連接器的截圖。

在連接器對話框中，輸入伺服器參數和資料庫名稱。 然後選擇確定。

連接器對話截圖，參數為伺服器和資料庫。

新的導航視窗出現。 在 Navigator 中，你可以從 SQL 驅動程式中看到伺服器及其資料庫的階層式檢視，顯示出原生的導航行為。 右鍵點選 AdventureWorks2019 資料庫，然後選擇 「資料轉換」。

這是導航視窗內情境選單中轉換資料選項的截圖。

此選擇會帶你進入 Power Query 編輯器，預覽原生查詢的實際目標，因為所有原生查詢都應該在資料庫層級執行。 檢查最後一步的公式列，以更了解你的連結器在執行前應該如何導向原生查詢的目標。 此時公式列顯示以下資訊：

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

Source 是前一步驟的名稱，在此情況下，這僅是指你連接器已發佈的函式以及傳遞的參數。 清單和裡面的記錄只是幫助你把資料表導到特定列。 該列依據記錄中欄位名稱必須等於AdventureWorks2019以及欄位種類必須等於資料庫的條件來定義。 一旦找到 [Data] 該列，清單 {} 外側允許 Power Query 存取 資料 欄位內的值，此處指的是資料表。 你可以回到前一步（來源）來更好地理解這個導航。

一張顯示導航步驟所用值和欄位的表格截圖。

測試原生查詢

目標確定後，在導覽步驟之後，選擇公式列中的 fx 圖示以建立自訂步驟。

這是用來建立自訂步驟的公式內的效果按鈕截圖。

將公式列中的公式替換成以下公式，然後選擇 Enter。

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

套用此變更後，公式欄下方應該會出現警告，請求允許對你的資料來源執行原生查詢。

執行此原生資料庫查詢警告訊息需提供權限截圖。

選擇 編輯權限。 會顯示一個新的 原生資料庫查詢 對話框，試圖警告你執行原生查詢的可能性。 在這種情況下，我們知道這個 SQL 陳述是安全的，所以選擇 執行 來執行該指令。

示範如何核准原生資料庫查詢對話框的截圖。

執行查詢後，Power Query 編輯器會顯示查詢預覽。 這個預覽驗證了你的連接器能夠執行原生查詢。

這是初始連接器開發與測試中執行的原生查詢截圖。

在你的連接器中實作原生查詢邏輯

根據前述章節收集的資訊，現在的目標是將這些資訊轉換成你的連接器程式碼。

你可以透過在連接器的Publish記錄中新增一個NativeQueryProperties記錄欄位來實現這項翻譯，這裡指的是SqlODBC.Publish記錄。 NativeQueryProperties記錄在定義連接器如何與Value.NativeQuery功能互動方面扮演關鍵角色。

新的紀錄欄位包含兩個欄位：

  • 導航步驟：此欄位定義了你的連接器應該如何執行或處理導航。 它包含一份記錄清單，說明如何透過函 Value.NativeQuery 式前往你想查詢的特定資料。 在每條紀錄中，它定義了為了讓該導航達到你想要的目標，所需的參數。
  • DefaultOptions：此欄位有助於判斷某些可選參數應如何包含或加入選項 Value.NativeQuery 紀錄。 它提供了一組預設選項，供查詢資料來源時使用。

你的導航步驟可以分為兩大類。 第一個包含終端使用者輸入的數值，例如伺服器名稱或資料庫名稱。 第二個則包含由特定連接器實作推導出的值，例如在取得資料體驗中未顯示給使用者的欄位名稱。 這些欄位可能包括 Name、 Kind、 Data、 以及其他，視你的連接器實作而定。

在此情況下，只有一個導航步驟包含兩個欄位：

  • 名稱：此欄位為最終使用者傳遞的資料庫名稱。 在這個案例中，是 AdventureWorks2019，但這個欄位應該總是原樣傳遞，按照最終使用者在獲取資料過程中輸入的內容。
  • 種類：此欄位為終端使用者無法看到的資訊，且專屬於連接器或驅動程式實作。 此值指應存取哪種類型的物件。 在此實作中，此欄位將是一個固定值，包含字串 Database。

此類資訊將被轉譯為以下代碼。 你的程式碼應該作為新欄位新增到你的 SqlODBC.Publish 紀錄中。

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

這很重要

欄位名稱需區分大小寫，必須如上方範例所示使用。 所有傳送給欄位的資訊，無論是 ConstantValue、 IndexName或 FieldDisplayName ，都必須從連接器的 M 碼中推導出來。

對於使用者輸入的數值，您可以使用 FieldDisplayName 與 IndexName 搭配使用。 對於固定或預先定義且終端使用者無法傳遞的值，你可以使用組合 ConstantValue 和 IndexName。 從這個意義上說， NavigationSteps 記錄包含兩個欄位：

  • 索引：定義使用哪些欄位及值來導航至包含函式目標 Value.NativeQuery 的記錄。
  • FieldAccess：定義包含目標的欄位，通常是表格。

預設選項

這個 DefaultOptions 欄位允許你在使用連接器的原生查詢功能時，將選擇性參數傳遞給 Value.NativeQuery 函式。

為了在原生查詢後保留查詢摺疊，且假設你的連接器具備查詢摺疊功能，你可以使用以下範例程式碼。EnableFolding = true

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

    DefaultOptions = [
        EnableFolding = true
    ]
]

完成這些變更後，建立連接器並載入 Power BI Desktop 進行測試與驗證。

測試並驗證連接器

在 Power BI Desktop 中，安裝好新的自訂連接器後，從 「取得資料 」體驗中啟動連接器。 啟動連接器時，你會注意到對話框現在有一個長文字欄位，名稱為 Native query ，括號內則包含了該對話所需的欄位。 輸入你之前測試連接器時輸入的伺服器、資料庫和 SQL 語句的數值。

連結器對話截圖，顯示原生查詢長文字欄位。

選擇 確定後，會以新對話框顯示已執行的原生查詢的表格預覽。

對話截圖，並附有已執行的原生查詢表格預覽。

請選擇 [確定]。 現在 Power Query 編輯器會載入一個新的查詢，你可以依需求進一步測試你的連接器。

備註

如果你的連接器具備查詢摺疊功能，且在可選記錄EnableFolding=true中明確定義了Value.NativeQuery，那麼你可以在 Power Query 編輯器中進一步測試這個連接器，以檢查後續的轉換是否會回歸到來源。

  • Last updated on