查询折叠示例

项目
01/18/2024

本文为查询折叠的三种可能结果各提供了一些示例方案。它还包括有关如何充分利用查询折叠机制的建议，以及它在查询中可能产生的效果。

场景

假设有使用适用于 Azure Synapse Analytics SQL 数据库的 Wide World Importers 数据库，你将负责在 Power Query 中创建一个连接到 fact_Sale 表的查询，并检索仅包含以下字段的最后 10 个销售额：

销售密钥
客户密钥
发票日期关键字
说明
数量

注意

为便于演示，本文使用本教程中概述的数据库，介绍如何将 Wide World Importers 数据库加载到 Azure Synapse Analytics 中。本文的主要区别在于 fact_Sale 表仅保存 2000 年的数据，总共 3,644,356 行。

虽然结果可能与按照 Azure Synapse Analytics 文档中的教程获得的结果不完全一致，但本文的目标是展示查询折叠的核心概念及其对查询的影响。

示例输出表，派生自“Wide World Importers”Azure Synapse Analytics 数据库“fact_Sale”表。

本文展示了通过不同级别的查询折叠实现相同输出的三种方法：

无查询折叠
部分查询折叠
完整查询折叠

无查询折叠示例

重要

仅依赖于非结构化数据源或没有计算引擎（如 CSV 或 Excel 文件）的查询不具备查询折叠功能。这意味着 Power Query 会使用 Power Query 引擎评估所有必需的数据转换。

连接到数据库并导航到 fact_Sale 表后，选择“主页”选项卡的“减少行”组中的“保留底部行”转换。

“主页”选项卡的“减少行”组中的“保留底部行”转换。

选择此转换后，会出现一个新对话框。在此新对话框中，可以输入要保留的行数。在本例种，请输入值 10，然后选择“确定”。

在“保留底部行”对话框中输入值“10”。

提示

在本例种，执行此操作将得到最后 10 个销售额的结果。在大多数情况下，建议提供更明确的逻辑，通过在表上应用排序操作来定义哪些行是最后一行。

接下来，选择在“主页”选项卡的“管理列”组中找到的“选择列”转换。然后，可以选择要从表中保留的列并删除其余列。

为无查询折叠示例选择“选择列”转换。

最后，在“选择列”对话框中，选择 Sale Key、Customer Key、Invoice Date Key、Description 和Quantity 列，然后选择“确定”。

为无查询折叠示例选择“Sale Key”、“Customer Key”、“Invoice Date Key”、“Description”和“Quantity”列。

以下代码示例是所创建查询的完整 M 脚本：

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Kept bottom rows" = Table.LastN(Navigation, 10),
  #"Choose columns" = Table.SelectColumns(#"Kept bottom rows", {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"})
in
  #"Choose columns""

无查询折叠：了解查询评估

在 Power Query 编辑器中的“已应用步骤”下，你会注意到，“保留底部行”和“选择列”的查询折叠指示器被标记为将在数据源外部或者由 Power Query 引擎评估的步骤。

查询的“已应用的步骤”窗格，其中包含查询折叠指示器，其显示“保留底部行”和“删除其他列”步骤。

可以右键单击查询的最后一步，即名为“选择列”的步骤，然后选择“查看查询计划”的选项。查询计划的目标是让你详细了解查询是如何运行的。若要了解有关此功能的详细信息，请转至我们的查询计划。

创建的查询的“查询计划”，具有多个节点，其中两个节点位于一个矩形中，表示将由 Power Query 引擎评估这些节点。

上图种的每个框称为一个节点。节点代表要完成此查询的操作分解。表示数据源的节点，如上述示例中的 SQL Server 和 Value.NativeQuery 节点，代表将查询的哪个部分卸载到数据源。在本例中，其余节点由 Power Query 引擎评估，上图中的矩形突出显示了这些节点 Table.LastN 和 Table.SelectColumns。这两个节点表示添加的两个转换，即“保留底部行”和“选择列”。其余节点表示在数据源级别进行的操作。

若要查看发送到数据源的确切请求，请选择 Value.NativeQuery 节点中的“查看详细信息”。

在“Value.NativeQuery”中找到的 SQL 语句，表示数据库中“fact_Sale”表中所有字段和记录的请求。

此数据源请求采用数据源的本机语言。在本例种，该语言为 SQL，此语句表示对表中所有行和字段 fact_Sale 的请求。

查看此数据源请求有助于更好地了解查询计划视图传达的信息：

Sql.Database：此节点表示数据源访问权限。连接到数据库并发送元数据请求，了解其功能。
Value.NativeQuery：表示 Power Query 为完成查询而生成的请求。 Power Query 通过本机 SQL 语句向数据源提交数据请求。在本例种，它代表 fact_Sale 表中的所有记录和字段（列）。对于此方案，这种情况并不可取，因为该表包含数百万行，而我们感兴趣的只是最后 10 行。
Table.LastN：Power Query 从 fact_Sale 表中接收所有记录后，会使用 Power Query 引擎筛选表，只保留最后 10 行。
Table.SelectColumns：Power Query 将使用 Table.LastN节点的输出并应用名为 Table.SelectColumns 的新转换，该转换选择要从表中保留的特定列。

为了进行评估，此查询必须下载 fact_Sale 表中的所有行和字段。在 Power BI 数据流的标准实例中，处理该查询平均花费 6 分 1 秒（考虑到了数据流的评估和数据加载）。

部分查询折叠示例

连接到数据库并导航到 fact_Sale 表后，首先选择要从表中保留的列。从“主页”选项卡的“管理列”组中选择“选择列”转换。此转换可帮助你明确选择要从表中保留的列并删除其余列。

为部分查询折叠示例选择“选择列”转换。

在“选择列”对话框中，选择 Sale Key、Customer Key、Invoice Date Key、Description 和Quantity 列，然后选择“确定”。

为部分查询折叠示例选择“Sale Key”、“Customer Key”、“Invoice Date Key”、“Description”和“Quantity”列。

现在，可以创建逻辑，以便对表进行排序，仅保留表底部的最后一个销售额。选择作为主键和递增序列或表索引的 Sale Key 列。从列的上下文菜单中，仅使用该字段按升序对表进行排序。

使用“自动筛选”字段关联菜单按升序对表的“Sale Key”字段进行排序。

接下来，选择表上下文菜单，然后选择“保留底部行”转换。

选择表关联菜单中的“保留底部行”选项。

在“保留底部行”中，输入值 10，然后选择“确定”。

“保留底部行”对话框，其中以值“10”作为输入值，以仅保留表的底部十行。

以下代码示例是所创建查询的完整 M 脚本：

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Choose columns" = Table.SelectColumns(Navigation, {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}),
  #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Ascending}}),
  #"Kept bottom rows" = Table.LastN(#"Sorted rows", 10)
in
  #"Kept bottom rows"

部分查询折叠示例：了解查询评估

查看已应用步骤窗格时，你会发现查询折叠指示器显示你添加的最后一个转换 Kept bottom rows，被标记为将在数据源外部评估或者换句话说，由 Power Query 引擎进行评估的步骤。

查询的“已应用的步骤”窗格，其中包含查询折叠指示器，其显示“保留底部行”标记为将在数据源外部评估的步骤。

可以右键单击查询的最后一步（名为 Kept bottom rows）并选择“查询计划”选项，以更好地了解查询的评估方式。

显示多个节点的查询计划，其中“Table.LastN”节点在矩形内显示，这是由 Power Query 引擎而不是数据源评估的节点。

上图种的每个框称为一个节点。节点表示需要为了评估查询需要执行的每个进程（从左到右）。其中一些节点（如 Table.LastN 的节点）可以在数据源中进行评估，而其他节点（如“保留底部行”步骤所表示的节点）则使用 Power Query 引擎进行评估。

若要查看发送到数据源的确切请求，请选择 Value.NativeQuery 节点中的“查看详细信息”。

“Value.NativeQuery”中的 SQL 语句，表示所有记录的请求，其中只有数据库中“fact_Sales”表中请求的字段根据“Sale Key”字段按升序排序。

此请求采用数据源的本机语言。在本例中，该语言为 SQL，此语句表示对所有行的请求，仅包含按 Sale Key 字段排序的 fact_Sale 表中请求的字段。

查看此数据源请求有助于更好地了解完整查询计划视图传达的信息：节点的顺序是一个从数据源请求数据开始的顺序过程：

Sql.Database：连接到数据库并发送元数据请求，了解其功能。
Value.NativeQuery：表示 Power Query 为完成查询而生成的请求。 Power Query 通过本机 SQL 语句向数据源提交数据请求。在本例中，这代表所有记录，其中只包含数据库 fact_Sale 表中按 Sales Key 字段升序排序的请求字段。
Table.LastN：Power Query 从 fact_Sale 表中接收所有记录后，会使用 Power Query 引擎筛选表，只保留最后 10 行。

为了进行评估，此查询必须从 fact_Sale 表下载所有行，而且职能下载必填字段。在 Power BI 数据流的标准实例中，处理平均花费 3 分 4 秒（考虑到了数据流的评估和数据加载）。

完整查询折叠示例

连接到数据库并导航到 fact_Sale 表后，请首先选择要从表中保留的列。从“主页”选项卡的“管理列”组中选择“选择列”转换。此转换可帮助你明确选择要从表中保留的列并删除其余列。

为完整查询折叠示例选择“选择列”转换。

在“选择列”中，选择 Sale Key、Customer Key、Invoice Date Key、Description 和 Quantity 列，然后选择“确定”。

为完整查询折叠示例选择“Sale Key”、“Customer Key”、“Invoice Date Key”、“Description”和“Quantity”列。

现在，可以创建逻辑，以便对表进行排序，仅保留表顶部的最后一个销售额。选择作为主键和递增序列或表索引的 Sale Key 列。从列的上下文菜单中，仅使用该字段按降序对表进行排序。

使用关联菜单，对表的“Sale Key”字段按降序排序。

接下来，选择表上下文菜单，然后选择“保留顶部行”转换。

表关联菜单中的“保留顶部行”选项。

在“保留顶部行”中，输入值 10，然后选择“确定”。

“保留顶部行”对话框，其中以值“10”作为输入值，以仅保留表的顶部十行。

以下代码示例是所创建查询的完整 M 脚本：

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Choose columns" = Table.SelectColumns(Navigation, {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}),
  #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Descending}}),
  #"Kept top rows" = Table.FirstN(#"Sorted rows", 10)
in
  #"Kept top rows"

完整查询折叠示例：了解查询评估

检查已应用步骤窗格时，你会发现查询折叠指示器显示你添加的转换，选择列、已排序行和保留前几行的转换被标记为将在数据源中评估的步骤。

所有查询步骤都有一个图标，显示它们可以折叠回数据源。

可以右键单击查询的最后一步，即名为“保留前几行”的步骤，然后选择“查询计划”的选项。

在“Value.NativeQuery”中找到的 SQL 语句，表示使用“Sale Key”字段排序的“fact_Sale”表的前十条记录的请求，并且只包含“Sale Key”、“Customer Key”、“invoice Date Key”、“Description”和“Quantity”字段。

此请求采用数据源的本机语言。在本例种，该语言为 SQL，此语句表示对表中所有行和字段 fact_Sale 的请求。

查看此数据源查询有助于更好地了解完整查询计划视图传达的信息：

Sql.Database：连接到数据库并发送元数据请求，了解其功能。
Value.NativeQuery：表示 Power Query 为完成查询而生成的请求。 Power Query 通过本机 SQL 语句向数据源提交数据请求。对于本例，表示仅对 fact_Sale 表的前 10 条记录的请求，仅使用 Sale Key 字段按降序对必填字段进行排序。

注意

虽然在 T-SQL 语言中没有子句可以用来 SELECT 表的底部行，但有一个 TOP 子句可以检索表的顶部行。

在对其进行评估时，此查询仅下载 10 行，并且只下载从 fact_Sale 表中请求的字段。在 Power BI 数据流的标准实例中，处理该查询平均花费 31 秒（考虑到了数据流的评估和数据加载）。

性能比较

为了更好地了解查询折叠对这些查询的影响，可以刷新查询，记录完全刷新每个查询所需的时间并进行比较。为简单起见，本文提供了在连接到以 DW2000c 为服务级别的专用 Azure Synapse Analytics 环境时使用 Power BI 数据流刷新机制捕获的平均刷新时间。

每个查询的刷新时间如下所示：

示例	Label	以秒为单位的时间
无查询折叠	无	361
部分查询折叠	部分	184
完整查询折叠	完全	31

图表，其中将无折叠查询的刷新时间与 361 秒进行比较，将部分查询折叠与 184 秒进行比较，将完全折叠查询与 31 秒进行比较。

通常，完全折叠回数据源的查询性能优于不完全折叠回数据源的类似查询。造成这种情况的原因可能有很多。这些原因包括查询执行的转换的复杂性、数据源实施的查询优化（如索引和专用计算）以及网络资源等。不过，查询折叠仍尝试使用两个特定的关键进程，以尽量减少这两个进程对 Power Query 的影响：

传输中的数据
Power Query 引擎执行的转换

下文将说明上述查询中这两个进程产生的影响。

传输中的数据

执行查询时，第一步是尝试从数据源获取数据。从数据源获取哪些数据由查询折叠机制定义。该机制可确定可卸载到数据源的查询步骤。

下表列出了从数据库 fact_Sale 表请求的行数。该表还包括为从数据源请求此类数据而发送的 SQL 语句的简要说明。

示例	Label	请求的行	说明
无查询折叠	无	3644356	请求 `fact_Sale` 表中的所有字段和所有记录
部分查询折叠	部分	3644356	请求所有记录，但在按 `Sale Key` 字段排序后只查询 `fact_Sale` 表中的必填字段
完整查询折叠	完全	10	仅请求 `fact_Sale` 表中按 `Sale Key` 字段降序排序后的必填字段和前 10 条记录

图表，其中包含从数据库中收集的无查询折叠、部分查询折叠和完整查询折叠的行数量。

从数据源请求数据时，数据源需要计算请求的结果，然后将数据发送到请求者。虽然前面已经提到计算资源，但将数据从数据源移动到 Power Query 的网络资源，然后让 Power Query 有效接收数据并为本地转换做好准备的网络资源则需要一些时间，具体取决于数据的大小。

在展示的示例中，Power Query 必须从数据源请求超过 360 万行，才能获取无查询折叠和部分查询折叠示例。而对于完整查询折叠示例，它只请求了 10 行。对于请求的字段，无查询折叠示例请求表中的所有可用字段。部分查询折叠和完整查询折叠示例都只提交了对所需字段的请求。

注意

建议实施增量刷新解决方案，利用查询折叠来刷新具有大量数据的查询或表。 Power Query 的不同产品集成会实施超时，以终止长时间运行的查询。某些数据源也会对长时间运行的会话实施超时，这些会话试图对其服务器执行高消耗查询。详细信息：使用数据流的增量刷新和语义模型的增量刷新

Power Query 引擎执行的转换

本文展示了如何使用查询计划更好地了解查询的评估方式。在查询计划中，可以看到 Power Query 引擎将执行的转换操作的确切节点。

下表展示了 Power Query 引擎将评估的先前查询的查询计划节点。

示例	Label	Power Query 引擎转换节点
无查询折叠	无	`Table.LastN`、`Table.SelectColumns`
部分查询折叠	部分	`Table.LastN`
完整查询折叠	完全	–

图表，其中包含 Power Query 引擎运行的无查询折叠、部分查询折叠和完整查询折叠的转换总数。

对于本文中展示的示例，完整查询折叠示例不需要在 Power Query 引擎中进行任何转换，因为所需的输出表直接来自数据源。相比之下，其他两个查询需要在 Power Query 引擎中进行一些计算。由于这两个查询需要处理的数据量很大，因此这些示例的处理过程比完整查询折叠示例花费的时间要长。

转换可分为以下几类：

运算符类型	说明
远程	数据源节点的运算符。这些运算符的评估发生在 Power Query 之外。
流式处理	运算符是直通运算符。例如，`Table.SelectRows` 使用简单的筛选器通常可以在结果通过运算符时对其进行筛选，并且无需在移动数据之前收集所有行。 `Table.SelectColumns` 和 `Table.ReorderColumns` 是此类运算符的其他示例。
完全扫描	在数据进入链中的下个运算符之前，需要收集所有行的运算符。例如，若要对数据进行排序，Power Query 需要收集所有数据。完整扫描运算符的其他示例包括 `Table.Group`、`Table.NestedJoin` 和 `Table.Pivot`。

提示

虽然从性能角度来看，并非每个转换都是相同的，但在大多数情况下，转换较少通常更好。

注意事项和建议

在创建新查询时，请遵循 Power Query 最佳实践中所述的最佳实践。
使用查询折叠指示器检查哪些步骤阻止查询折叠。必要时重新排列这些步骤，以提高折叠率。
使用查询计划确定 Power Query 引擎对特定步骤进行了哪些转换。考虑通过重新排列步骤来修改现有查询。然后再次检查查询最后一步的查询计划，并查看查询计划相较前一步是否有所改善。例如，新查询计划的节点数比前一个节点少，而大多数节点是“流式处理”节点，而不是“完整扫描”节点。对于支持折叠的数据源，查询计划中除 Value.NativeQuery 和数据源访问节点以外的其他任何节点都代表未折叠的转换。
如果可用，可以使用查看本机查询（或查看数据源查询）选项来确保查询可以折叠回数据源。如果此选项在步骤中被禁用，而你使用源通常支持该选项，那么你已创建停止查询折叠的步骤。如果使用不支持此选项的源，则可以依赖查询折叠指示器和查询计划。
当连接器具有查询折叠功能时，使用查询诊断工具可以更好地了解发送到数据源的请求。
在结合使用多个连接器获取的数据时，Power Query 会尝试尽可能多地将工作推送到两个数据源，同时符合为每个数据源定义的隐私级别。
请阅读有关隐私级别的文章，以保护查询免受数据隐私防火墙错误的影响。
使用其他工具从数据源接收请求的角度检查查询折叠。根据本文中的示例，可以使用 Microsoft SQL Server Profiler 检查 Power Query 发送和 Microsoft SQL Server 接收的请求。
如果将新步骤添加到完全折叠的查询，并且新步骤也会折叠，Power Query 可能会向数据源发送新请求，而不是使用之前结果的缓存版本。实际上，此过程可能会导致对少量数据执行看似简单的操作，但在预览中刷新的时间却比预期的要长。刷新时间延长的原因是 Power Query 重新查询了数据源，而不是使用本地数据副本。

通过

查询折叠示例

场景

无查询折叠示例

无查询折叠：了解查询评估

部分查询折叠示例

部分查询折叠示例：了解查询评估

完整查询折叠示例

完整查询折叠示例：了解查询评估

性能比较

传输中的数据

Power Query 引擎执行的转换

注意事项和建议

反馈

其他资源