通过

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

注释

本文探讨了关于实现自定义连接器的本机查询支持，以及在其基础上实现查询折叠的高级主题。 本文假设你已经了解了这些概念。

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

在 Power Query 中，可以针对数据源执行自定义本机查询，以检索要查找的数据。 您还可以在整个过程中及随后的 Power Query 转换过程中启用保持查询折叠的功能。

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

先决条件

本文以示例为起点，该示例使用 SQL ODBC 驱动程序作为其数据源。 目前，符合 SQL-92 标准的 ODBC 连接器仅支持本机查询功能的实现。

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

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

修改连接器的 SQL 功能

在 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 示例的连接器。

在 Power BI Desktop 获取数据体验中找到的连接器的屏幕截图。

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

连接器对话框的屏幕截图，其中服务器和数据库作为参数。

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

导航器窗口中上下文菜单中的转换数据选项的屏幕截图。

此选项将带您转到 Power Query 编辑器，并预览您本机查询的实际目标，因为所有本机查询应始终在数据库级别运行。 检查最后一步的公式编辑栏，以更好地了解在执行本机查询之前，连接器应如何导航到查询目标。 在这种情况下，公式栏将显示以下信息：

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

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

显示用于导航步骤的值和字段的表的屏幕截图。

测试本机查询

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

用于创建自定义步骤的公式中的 fx 按钮的屏幕截图。

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

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

应用此更改后，在编辑栏下方应会显示警告，要求对数据源运行本地查询的权限。

运行此本机数据库查询警告消息所需的权限的屏幕截图。

选择 “编辑权限”。 将显示一个新的 “本机数据库查询 ”对话框，该对话框会尝试警告你运行本机查询的可能性。 在这种情况下，我们知道此 SQL 语句是安全的，因此请选择 “运行” 以执行命令。

显示如何批准本机数据库查询对话框的屏幕截图。

运行查询后，Power Query 编辑器中会显示查询预览。 此预览验证您的连接器是否可以运行本地查询。

初始连接器开发和测试中执行的本机查询的屏幕截图。

在连接器中实现原生查询逻辑

从前面的部分收集的信息后，现在的目标是将此类信息转换为连接器的代码。

实现该翻译的方法是将新的 NativeQueryProperties 记录字段添加到连接器的 Publish 记录中，在本例中，这是 SqlODBC.Publish 记录。 该 NativeQueryProperties 记录在定义连接器如何与 Value.NativeQuery 函数交互方面发挥了关键作用。

新记录字段由两个字段组成：

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

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

在这种情况下，只有一个导航步骤包含两个字段：

  • 名称：此字段是最终用户传递的数据库的名称。 在这种情况下，尽管是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 编辑器中进一步测试连接器，方法是检查后续转换是否折叠回到源。

  • Last updated on