查询折叠示例

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

场景

假设有使用适用于 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 文档中的教程获得的结果不完全一致，但本文的目标是展示查询折叠的核心概念及其对查询的影响。

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

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

无查询折叠示例

重要

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

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

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

提示

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

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

最后，在“选择列”对话框中，选择 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 引擎评估的步骤。

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

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

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

此数据源请求采用数据源的本机语言。 在本例种，该语言为 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 列。 从列的上下文菜单中，仅使用该字段按升序对表进行排序。

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

在“保留底部行”中，输入值 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 引擎进行评估。

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

此请求采用数据源的本机语言。 在本例中，该语言为 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 列。 从列的上下文菜单中，仅使用该字段按降序对表进行排序。

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

在“保留顶部行”中，输入值 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"

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

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

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

此请求采用数据源的本机语言。 在本例种，该语言为 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

通常，完全折叠回数据源的查询性能优于不完全折叠回数据源的类似查询。 造成这种情况的原因可能有很多。 这些原因包括查询执行的转换的复杂性、数据源实施的查询优化（如索引和专用计算）以及网络资源等。 不过，查询折叠仍尝试使用两个特定的关键进程，以尽量减少这两个进程对 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 之外。
流式处理	运算符是直通运算符。 例如，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 重新查询了数据源，而不是使用本地数据副本。