在 PolyBase 中下推计算

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

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

注意

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

启用下推计算

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

• 在 Hadoop 中启用下推计算
• 配置 PolyBase 以访问 Oracle 中的外部数据
• 配置 PolyBase 以访问 Teradata 中的外部数据
• 配置 PolyBase 以访问 MongoDB 中的外部数据
• 配置 PolyBase 以使用 ODBC 泛型类型访问外部数据
• 配置 PolyBase 以访问 SQL Server 中的外部数据

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

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

* 通过 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
求和	LessThan
Avg	LessOrEqual
最大值	GreaterOrEqual
Min	GreaterThan
Approx_Count_Distinct	Is
	IsNot

下推计算的主要有用方案

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

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

加入下推

当在同一外部数据源上联接两个外部表时，PolyBase 可以促进联接运算符的下推，这大大提高了性能。

当外部数据源执行联接时，它会减少数据移动量并提高查询性能。 如果没有联接下推，SQL Server 必须将两个表中的数据传输到本地 tempdb ，再执行联接。

如果 分布式联接 （将本地表联接到外部表），除非筛选器适用于联接的外部表，否则 SQL Server 必须将外部表中的所有数据从本地 tempdb 引入以执行联接作。 例如，以下查询没有对外部表联接条件进行筛选，这会导致从外部表读取所有数据。

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

由于联接使用 E.id 外部表的列，因此向该列添加筛选器条件时，SQL Server 可以向下推送筛选器，从而减少从外部表读取的行数。

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 的二进制和逻辑运算符组成。 中的逻辑 ANDcustomer.account_balance AND customer.zipcode 是最终表达式。

鉴于这种谓词组合，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 子句

在筛选器子句中指定变量时，默认情况下 SQL Server 不会向下推送筛选器子句。 例如，以下查询不会向下推送过滤条件：

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 首先引入了向下推送变量的功能。

有关详细信息，请参阅 sp_executesql。

排序规则冲突

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

parquet 文件的下推

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

支持的外部数据源

支持 Parquet 下推的外部数据源包括：

• S3 兼容的对象存储
• Azure Blob 存储
• Azure Data Lake Storage Gen2

有关配置详细信息，请参阅：

• 配置 PolyBase 以访问 S3 兼容的对象存储中的外部数据
• 使用 PolyBase 虚拟化 S3 兼容对象存储中 parquet 文件

下推操作

SQL Server 可以将这些操作下推到 Parquet 文件中：

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

这些因素阻止 parquet 文件的下推优化：

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

支持的数据类型

• bit
• tinyint
• smallint
• bigint
• 真实
• 漂浮
• varchar （Bin2Collation， CodePageConversion， BinCollation）
• nvarchar （Bin2Collation， BinCollation）
• 二元
• datetime2 （默认值和 7 位精度）
• date
• 时间 （默认值和 7 位精度）
• 数字 *

* 当参数刻度与列刻度对齐或参数显式强制转换为 十进制时，支持。

阻止 parquet 下推的数据类型

• money
• smallmoney
• datetime
• smalldatetime

使用文件夹结构的分区消除

PolyBase 可以使用文件夹结构来消除分区，从而减少在查询期间扫描的数据量。 在分层文件夹中排列 parquet 文件时（如按年份、月份或其他分区键），PolyBase 可以跳过与查询条件不匹配的整个文件夹。

例如，如果将数据结构化为：

/data/year=2024/month=01/*.parquet
/data/year=2024/month=02/*.parquet
/data/year=2025/month=01/*.parquet

使用通配符在 OPENROWSET 或外部表位置查询特定分区：

-- Query only January 2025 data
SELECT *
FROM OPENROWSET(
    BULK '/data/year=2025/month=01/*.parquet',
    DATA_SOURCE = 's3_ds',
    FORMAT = 'PARQUET'
) AS [data];

对于动态文件夹消除，请查询更广泛的文件夹路径，并使用 filepath() 谓词在运行时消除分区：

SELECT
    r.filepath(1) AS [year],
    r.filepath(2) AS [month],
    COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK '/data/year=*/month=*/*.parquet',
    DATA_SOURCE = 's3_ds',
    FORMAT = 'PARQUET'
) WITH (
    customer_id INT,
    amount DECIMAL(10, 2)
) AS [r]
WHERE
    r.filepath(1) = '2025'
    AND r.filepath(2) = '01'
GROUP BY
    r.filepath(1),
    r.filepath(2);

此方法将文件夹级分区消除与 parquet 文件级下推相结合，以实现最佳的查询性能。 有关使用文件夹模式查询 parquet 文件的完整教程，请参阅 使用 PolyBase 在与 S3 兼容的对象存储中虚拟化 parquet 文件。

示例

强制下推

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

禁用下推

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

相关内容

• 在 SQL Server 中使用 PolyBase 进行数据虚拟化
• 如何判断是否发生了外部下推
• 配置 PolyBase 以访问 S3 兼容的对象存储中的外部数据
• 使用 PolyBase 虚拟化 S3 兼容对象存储中 parquet 文件