Power Query 自訂連接器中的原生查詢支援
注意
本文涵蓋自訂連接器 的原生查詢 支援實 作以及頂端查詢 折迭的進階主題。 本文假設您已經具備這些概念的工作知識。
若要深入瞭解 Power Query 自訂連接器,請移至 Power Query SDK 概觀 。
在 Power Query 中,您可以針對資料來源執行自訂原生查詢,以擷取您要尋找的資料。 您也可以啟用在 Power Query 內維護查詢折迭的功能,以及後續在 Power Query 內完成的轉換程式。
本文的目標是要示範如何為自訂連接器實作這類功能。
本文使用 做為範例的起點 ,其資料來源使用 SQL ODBC 驅動程式。 目前只有遵循 SQL-92 標準的 ODBC 連接器才支援原生查詢功能的實作。
此範例連接器會使用 SQL Server Native Client 11.0 驅動程式。 請確定您已安裝此驅動程式,以遵循本教學課程。
您也可以從 GitHub 存放庫中的 Finish 資料夾 檢視範例連接器的完成版本。
在 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"
]),
在繼續進行之前,請確定此欄位會出現在您的連接器中。 如果沒有,您稍後會在使用不支援的功能時遇到警告和錯誤,因為連接器並未宣告此功能。
建置連接器檔案 (作為 .mez 或.pqx),並將其載入 Power BI Desktop 以進行手動測試,並定義原生查詢的目標。
注意
在本文中,我們將使用 AdventureWorks2019 範例資料庫 。 但是,您可以跟著您選擇的任何 SQL Server 資料庫,並在變更所選資料庫的特定資料時進行必要的變更。
本文中實作原生查詢支援的方式是,系統會要求使用者輸入三個值:
- 伺服器名稱
- 資料庫名稱
- 資料庫層級的原生查詢
現在,在 Power BI Desktop 內,移至 [取得資料] 體驗,並尋找名稱為 SqlODBC 範例 的連接器。
針對 [連接器] 對話方塊,輸入伺服器和資料庫名稱的參數。 然後選取確定。
新的導覽器視窗隨即出現。 在 [導覽器 ] 中,您可以從 SQL 驅動程式檢視原生導覽行為,以顯示伺服器及其內資料庫的階層式檢視。 以滑鼠右鍵按一下 AdventureWorks2019 資料庫,然後選取 [ 轉換資料 ]。
此選取專案會帶您前往 Power Query 編輯器,並預覽原生查詢的目標,因為所有原生查詢都應該在資料庫層級執行。 檢查最後一個步驟的公式列,以進一步瞭解如何在執行原生查詢之前,先流覽至原生查詢的目標。 在此情況下,公式列會顯示下列資訊:
= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]
來源 是上一個步驟的名稱,在此案例中,只是已傳遞參數的連接器已發佈函式。 清單及其內的記錄只會協助將資料表巡覽至特定資料列。 資料列是由記錄中的準則所定義,其中 Name 欄位 必須等於 AdventureWorks2019 ,而 Kind 欄位必須等於 Database 。 資料列找到之後, [Data]
清單 {}
外部可讓 Power Query 存取 [資料 ] 欄位中的值 ,在此案例中為數據表。 您可以回到上一個步驟( 來源 ),以進一步瞭解此流覽。
現在已識別目標之後,選取公式列中的 fx 圖示,在導覽步驟之後建立自訂步驟。
將公式列內的公式取代為下列公式,然後選取 Enter 。
= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
FROM [Person].[Address]")
套用這項變更之後,公式列下方應該會出現警告,要求對資料來源執行原生查詢的許可權。
選取 [ 編輯許可權 ]。 隨即顯示新的 [原生資料庫查詢 ] 對話方塊,會嘗試警告您執行原生查詢的可能性。 在此情況下,我們知道此 SQL 語句是安全的,因此請選取 [ 執行 ] 來執行命令。
執行查詢之後,您的查詢預覽會出現在 Power Query 編輯器中。 此預覽會驗證連接器是否能夠執行原生查詢。
透過先前各節收集的資訊,現在的目標是將這類資訊轉譯為連接器的程式碼。
您可以完成此轉譯的方式是將新的 NativeQueryProperties 記錄欄位新增至連接器的 [發佈 ] 記錄,在此案例中為 SqlODBC.Publish
記錄。 記錄 NativeQueryProperties
在定義連接器如何與 Value.NativeQuery
函式互動方面扮演重要角色。
新的記錄欄位包含兩個欄位:
- NavigationSteps :此欄位會定義連接器應該如何執行或處理導覽。 其中包含記錄清單,概述使用
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 查詢 的長文字欄位 ,而且,在括弧中,其具有運作所需的欄位。 輸入您先前在測試連接器時輸入的伺服器、資料庫和 SQL 語句的相同值。
選取 [ 確定 ] 之後,執行之原生查詢的資料表預覽會顯示在新的對話方塊中。
選取 [確定]。 新的查詢現在會在 Power Query 編輯器內載入,您可以視需要進一步測試連接器。
注意
如果您的連接器具有查詢折迭功能,且已明確定義為 EnableFolding=true
選擇性記錄 Value.NativeQuery
的一部分,則可以在 Power Query 編輯器中進一步測試連接器,方法是檢查是否進一步轉換折回來源。