通过

在 PolyBase 中下推计算

  • 项目

适用于：SQL Server 2016 (13.x) 及更高版本

下推计算提高了外部数据源上的查询性能。 从 SQL Server 2016 (13.x) 开始，下推计算可用于 Hadoop 外部数据源。 SQL Server 2019 (15.x) 为其他类型的外部数据源引入了下推计算功能。

注意

若要确定 PolyBase 下推计算是否能够优化查询，请参见如何判断是否发生了外部下推。

启用下推计算

可在下列文章中了解如何为特定类型的外部数据源配置下推计算：

下表汇总了对不同外部数据源的下推计算支持：

数据源 联接 投影 聚合 筛选器 统计信息
泛型 ODBC 是 是 是 是 是
Oracle 是+ 是 是 是 是
SQL Server 是 是 是 是 是
Teradata 是 是 是 是 是
MongoDB* 否 是 是*** 是*** 是
Hadoop 否 是 一些** 一些** 是
Azure Blob 存储 否 No No No 是

* 通过 Azure Cosmos DB API for MongoDB 启用 Azure Cosmos DB 下推支持。

** 请参阅下推计算和 Hadoop 提供程序。

*** SQL Server 2019 CU18 引入了适用于 SQL Server 2019 的 MongoDB ODBC 连接器聚合和筛选器的下推支持。

+ Oracle 支持联接下推，但可能需要在联接列上创建统计信息才能实现下推。

注意

某些 T-SQL 语法可能会阻止下推计算。 有关详细信息，请查看阻止下推的语法。

下推计算和 Hadoop 提供程序

PolyBase 目前支持两个 Hadoop 提供程序：Hortonworks 数据平台 (HDP) 和 Cloudera 分布式 Hadoop (CDH)。 对于下推计算，这两项功能之间没有区别。

要将计算下推功能用于 Hadoop，目标 Hadoop 群集必须具有 HDFS、YARN 和 MapReduce 的核心组件且已启用作业历史记录服务器。 PolyBase 通过 MapReduce 提交下推查询，并且从作业历史记录服务器拉取状态。 缺少任一组件，查询都会失败。

部分聚合必须在数据到达 SQL Server 后开始执行。 但是一部分聚合发生在 Hadoop 中。 这是在大规模并行处理系统中计算聚合的常见方法。

Hadoop 提供程序支持以下聚合和筛选器。

聚合 筛选（二进制比较）
Count_Big NotEqual
Sum LessThan
Avg LessOrEqual
最大值 GreaterOrEqual
Min GreaterThan
Approx_Count_Distinct Is
IsNot

下推计算的主要有用方案

借助 PolyBase 下推计算，可以将计算任务委托给外部数据源。 这会减少 SQL Server 实例上的工作负载，可显著提高性能。

SQL Server 可以将联接、投影、聚合和筛选推送到外部数据源，以便利用远程计算并限制通过网络发送的数据。

下推联接

在许多情况下，PolyBase 可以简化对同一外部数据源上两个外部表的联接的联接运算符的下推，从而大大提高性能。

如果可在外部数据源执行联接，这会减少数据移动量并提高查询性能。 如果不将联接下推，则必须将要联接的表中的数据置于本地 tempdb 中，然后再联接。

对于分布式联接（将本地表联接到外部表），除非联接的外部表存在筛选器，否则外部表中的所有数据都必须在本地引入 tempdb 才能执行联接操作。 例如，以下查询并未对外部表联接条件进行筛选，因此需要从外部表读取所有数据。

SELECT * FROM LocalTable L
JOIN ExternalTable E on L.id = E.id

由于联接位于外部表的 E.id 列中，因此如果向该列添加筛选条件，则可以下推筛选器，从而减少从外部表读取的行数。

SELECT * FROM LocalTable L
JOIN ExternalTable E on L.id = E.id
WHERE E.id = 20000

选择行的子集

使用谓词下推来提高从外部表中选择行子集的查询的性能。

在此示例中，SQL Server 启动 map-reduce 作业，以检索与 Hadoop 上的谓词 customer.account_balance < 200000 相匹配的行。 由于查询无需扫描表中的所有行也能成功完成，因此只有满足谓词条件的行才会复制到 SQL Server。 与帐户余额 >= 200000 的客户数相比，余额 < 200000 的客户数是很小的，这样可以节省大量时间，并且需要的临时存储空间更少。

SELECT * FROM customer WHERE customer.account_balance < 200000;
SELECT * FROM SensorData WHERE Speed > 65;

选择列的子集

使用谓词下推来提高从外部表中选择列子集的查询的性能。

在此查询中，SQL Server 启动了一个 map-reduce 作业来预处理 Hadoop 带分隔符的文本文件，因此只有 customer.name 和 customer.zip_code 这两列数据会复制到 SQL Server。

SELECT customer.name, customer.zip_code
FROM customer
WHERE customer.account_balance < 200000;

基本表达式和运算符的下推

SQL Server 允许以下谓词下推的基本表达式和运算符。

  • 数字、日期和时间值的二进制比较运算符（<、>、=、!=、<>、>=、<=）。
  • 算术运算符（+、-、*、/、%）。
  • 逻辑运算符（AND、OR）。
  • 一元运算符（NOT、IS NULL、IS NOT NULL）。

运算符 BETWEEN、NOT、IN 和 LIKE 可能会被向下推送。 实际具体行为取决于查询优化器如何将运算符表达式重写为一系列使用基本关系运算符的语句。

此示例中的查询有多个可以向下推送到 Hadoop 的谓词。 SQL Server 能够将 map-reduce 作业推送到 Hadoop，以便执行谓词 customer.account_balance <= 200000。 表达式 BETWEEN 92656 AND 92677 也是由可以推送到 Hadoop 的二进制和逻辑运算符组成。 customer.account_balance AND customer.zipcode 中的逻辑 AND 是最终表达式。

鉴于这种谓词组合，map-reduce 作业可以执行所有的 WHERE 子句。 只有满足 SELECT 条件的数据才会复制回到 SQL Server。

SELECT * FROM customer 
WHERE customer.account_balance <= 200000 
AND customer.zipcode BETWEEN 92656 AND 92677;

下推的支持函数

SQL Server 允许以下谓词下推的函数。

字符串函数

  • CONCAT
  • DATALENGTH
  • LEN
  • LIKE
  • LOWER
  • LTRIM
  • RTRIM
  • SUBSTRING
  • UPPER

数学函数

  • ABS
  • ACOS
  • ASIN
  • ATAN
  • CEILING
  • COS
  • EXP
  • FLOOR
  • POWER
  • SIGN
  • SIN
  • SQRT
  • TAN

常规函数

  • COALESCE *
  • NULLIF

* 在某些情况下，使用 COLLATE 可能会阻止下推。 有关详细信息，请参阅排序规则冲突。

日期和时间函数

  • DATEADD
  • DATEDIFF
  • DATEPART

阻止下推的语法

以下 T-SQL 函数或语法会阻止下推计算：

  • AT TIME ZONE
  • CONCAT_WS
  • TRANSLATE
  • RAND
  • CHECKSUM
  • BINARY_CHECKSUM
  • HASHBYTES
  • ISJSON
  • JSON_VALUE
  • JSON_QUERY
  • JSON_MODIFY
  • NEWID
  • STRING_ESCAPE
  • COMPRESS
  • DECOMPRESS
  • GREATEST
  • LEAST
  • PARSE

SQL Server 2019 (15.x) CU10 中引入了对 FORMAT 和 TRIM 语法的下推支持。

带有变量的 filter 子句

如果在 filter 子句中指定变量，则默认情况下这会阻止 filter 子句的下推。 例如，如果运行以下查询，则 filter 子句不会被向下推送：

DECLARE @BusinessEntityID INT

SELECT * FROM [Person].[BusinessEntity]  
WHERE BusinessEntityID = @BusinessEntityID;

若要实现变量的下推，需要启用查询优化器修补程序功能。 通过以下任一方式可实现此目的：

  • 实例级别：启用跟踪标志 4199 作为实例的启动参数
  • 数据库级别：在具有 PolyBase 外部对象的数据库的上下文中，执行 ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON
  • 查询级别：使用查询提示 OPTION (QUERYTRACEON 4199) 或 OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

该限制适用于执行 sp_executesql。 该限制也适用于筛选子句中一些函数的使用率。

在 SQL Server 2019 CU5 中首次引入了下推变量功能。

排序规则冲突

使用不同排序规则的数据可能无法进行下推。 COLLATE 等运算符也可能会干扰结果。 支持相等排序规则或二进制排序规则。 有关详细信息，请参阅如何判断是否发生了下推。

parquet 文件的下推

从 SQL Server 2022 (16.x) 开始，PolyBase 引入了对 parquet 文件的支持。 使用 parquet 执行下推时，SQL Server 能够同时执行行和列消除操作。 对于 parquet 文件，可以下推以下操作：

  • 数字、日期和时间值的二进制比较运算符（>、>=、<=、<）。
  • 比较运算符（> AND <、>= AND <、> AND <=、<= AND >=）的组合。
  • 列表筛选器中 (col1 = val1 OR col1 = val2 OR vol1 = val3)。
  • 指定列为 NOT NULL。

存在以下内容时会阻止 parquet 文件的下推：

  • 虚拟列。
  • 列比较。
  • 类型参数转换。

支持的数据类型

  • Bit
  • TinyInt
  • SmallInt
  • Bigint
  • Real
  • 浮点
  • VARCHAR（Bin2Collation、CodePageConversion、BinCollation）
  • NVARCHAR（Bin2Collation、BinCollation）
  • 二进制
  • DateTime2（默认值和 7 位精度）
  • 日期
  • 时间（默认值和 7 位精度）
  • 数字 *

* 当参数数值范围与列数值范围一致或参数显式强制转换为十进制时受支持。

阻止 parquet 下推的数据类型

  • 金钱
  • SmallMoney
  • DateTime
  • SmallDateTime

示例

强制下推

SELECT * FROM [dbo].[SensorData]
WHERE Speed > 65
OPTION (FORCE EXTERNALPUSHDOWN);

禁用下推

SELECT * FROM [dbo].[SensorData]
WHERE Speed > 65
OPTION (DISABLE EXTERNALPUSHDOWN);

相关内容