Power Query 自定义连接器中的本机查询支持

注意

本文将围绕实现自定义连接器的本机查询支持以及基于本机查询进行查询折叠主题进行深入探讨。 本文假定你已经对这些概念有所了解。

若要详细了解 Power Query 自定义连接器，请前往 Power Query SDK 概述。

在 Power Query 中，可以针对数据源执行自定义本机查询，从而检索要查找的数据。 还可以启用在整个过程和 Power Query 内部完成的后续转换过程中保持查询折叠的功能。

本文旨在展示如何为自定义连接器实现这种功能。

先决条件

本文从演示使用 SQL ODBC 驱动程序作为数据源的示例开始。 本机查询功能目前只支持符合 SQL-92 标准的 ODBC 连接器。

示例连接器使用 SQL Server Native Client 11.0 驱动程序。 请确保已安装此驱动程序，以便按照本教程进行操作。

还可以在 GitHub 存储库中的“完成”文件夹查看示例连接器的完成版本。

修改连接器的 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"
]),

在继续之前，请确保此字段显示在连接器中。 如果未显示，则稍后会在使用不受支持功能时遇到警告和错误，因为连接器未声明该功能。

生成连接器文件（如 .mez 或.pqx），并将其加载到 Power BI Desktop 中进行手动测试，并定义本机查询的目标。

手动测试连接器的本机查询功能

注意

在本文中，我们将使用 AdventureWorks2019 示例数据库。 不过，你也可以使用自己选择的任何 SQL Server 数据库，并根据所选数据库的具体情况进行必要的更改。

本文实现本地查询支持的方式是，要求用户输入三个值：

• 服务器名称
• 数据库名称
• 数据库级别的本机查询

现在，转到 Power BI Desktop 中的“获取数据”体验，找到名为 SqlODBC 示例的连接器。

对于连接器对话框，请输入服务器和数据库名称的参数。 然后选择“确定”。

此时将显示新导航器窗口。 在 导航器中，可以从 SQL 驱动程序查看本机导航行为，该驱动程序显示服务器的分层视图及其中的数据库。 右键单击 AdventureWorks2019 数据库，然后选择“转换数据”。

选择该选项后，将进入 Power Query 编辑器，并预览本地查询的目标，因为所有本机查询都应在数据库级别运行。 检查最后一步的编辑栏，以便更好地理解连接器在执行本机查询前应如何导航到目标。 本例中编辑栏将显示以下信息：

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

源是上一步的名称，在本例中，源只是连接器的已发布函数，并已传递参数。 列表及其内部的记录仅有助于将表导航到特定行。 该行由记录中的条件定义，其中“名称”字段必须等于 AdventureWorks2019，“种类”字段必须等于“数据库”。 找到行后，列表 {} 外的 [Data] 允许 Power Query 访问 “数据”字段中的值，在本例中为表。 可以返回上一步（源）更好地了解此导航。

测试本机查询

现在确定目标后，可通过在编辑栏中选择 fx 图标，在导航步骤后创建自定义步骤。

将编辑栏中的公式替换为以下公式，然后选择 Enter。

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

应用此更改后，会在编辑栏下面显示警告，请求允许对数据源运行本机查询。

选择“编辑权限”。 将显示新的“本机数据库查询”对话框，试图提醒你可能运行本地查询。 在本例中，我们知道此 SQL 语句是安全的，因此请选择“运行”来执行命令。

运行查询后，Power Query 编辑器中会显示查询预览。 此预览版验证连接器是否能够运行本机查询。

在连接器中实现本机查询逻辑

有了前面章节收集的信息，现在的目标是将这些信息转化为连接器的代码。

记录在定义连接器如何与功能交互方面起着至关重要的作用。 在定义连接器如何与Value.NativeQuery函数交互时，NativeQueryProperties记录起着至关重要的作用。

新记录字段包含两个字段：

• NavigationSteps此字段定义连接器应如何执行或处理导航。 它包含一个记录列表，概述了使用Value.NativeQuery功能导航到要查询的特定数据的步骤。 在每个记录内，它还定义了此类导航依靠或需要哪些参数才能达到预期目标。
• DefaultOptions：此字段有助于确定如何将某些可选参数包含或添加到 Value.NativeQuery 选项记录中。 它提供了一组可在查询数据源时使用的默认选项。

NavigationSteps

导航步骤可以分为两个组。 第一个组包含最终用户输入的值，例如本例中的服务器或数据库名称。 第二个组包含由特定连接器实现派生的值，例如在获取数据体验期间未向用户显示字段的名称。 这些字段可以包括 Name、Kind、Data 和其他，具体取决于连接器实现。

本例中仅一个导航步骤包含两个字段：

• 名称：此字段是最终用户传递的数据库的名称。 本例中为 AdventureWorks2019，但该字段应始终按照最终用户在获取数据过程中输入的内容原样传递。
• 种类：此字段是最终用户不可见的信息，特定于连接器或驱动程序实现。 在本例中，此值表示应访问的对象类型。 对于此实现，该字段是包含字符串 Database 的固定值。

此信息将转换为以下代码。 此代码应作为新字段添加到 SqlODBC.Publish 记录中。

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

重要

字段的名称区分大小写，必须如上例所示使用。 传递给字段的所有信息，无论是 ConstantValue、IndexName 还是 FieldDisplayName 必须派生自连接器的 M 代码。

因此，FieldDisplayNameNavigationStepsIndexName记录由两个字段组成： 对于固定或预定义且不能由最终用户传递的值，可以使用 ConstantValue 和 IndexName 对。 从这个意义上说，navigationSteps 记录由两个字段组成：

• 索引：定义用于导航到包含 Value.NativeQuery 函数目标的记录的字段和值。
• FieldAccess：定义包含目标（通常是表）的字段。

DefaultOptions

使用连接器的本机查询功能时，该 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 中，从“获取数据”体验启动连接器。 启动连接器时，你会注意到对话框现在有一个名称为“本机查询”的长文本字段，在括号中，该对话框具有要使用的必填字段。 为测试连接器时输入的服务器、数据库和 SQL 语句的相同值。

选择“确定”后，新对话框中会显示已执行的本机查询的表预览。

选择“确定”。 新查询现在将加载到 Power Query 编辑器中，可以根据需要对连接器执行进一步测试。

注意

如果连接器具有查询折叠功能，并且已显式定义 EnableFolding=true 为 Value.NativeQuery 可选记录的一部分，则可以在 Power Query 编辑器中进一步测试连接器，检查进一步转换是否折叠回源。