Вычисления pushdown в PolyBase

Статья
05/13/2024

Область применения: SQL Server 2016 (13.x) и более поздних версий

Вычисление pushdown повышает производительность запросов во внешних источниках данных. Начиная с SQL Server 2016 (13.x), вычисления pushdown были доступны для внешних источников данных Hadoop. SQL Server 2019 (15.x) представил вычисления pushdown для других типов внешних источников данных.

Примечание.

Чтобы определить, является ли вычисление pushdown в PolyBase выгодным для вашего запроса, см. статью Как определить, произошел ли внешний pushdown.

Включение вычислений pushdown

В следующих статьях содержатся сведения о настройке вычислений pushdown для конкретных типов внешних источников данных.

В таблице ниже приведена поддержка вычислений pushdown для различных внешних источников данных:

Источник данных	Объединения	Проекции.	Статистические схемы	Фильтры	Статистика
Базовый протокол ODBC	Да	Да	Да	Да	Да
Oracle	Да+	Да	Да	Да	Да
SQL Server	Да	Да	Да	Да	Да
Teradata	Да	Да	Да	Да	Да
MongoDB*	Нет	Да	Да***	Да***	Да
Hadoop	Нет	Да	Несколько**	Несколько**	Да
Хранилище BLOB-объектов Azure	No	No	No	No	Да

* Поддержка pushdown Azure Cosmos DB включена через API Azure Cosmos DB для MongoDB.

** См . инструкции pushdown и поставщики Hadoop.

Поддержка pushdown для агрегатов и фильтров для соединителя ODBC MongoDB для SQL Server 2019 была представлена с SQL Server 2019 CU18.

+ Oracle поддерживает pushdown для соединений, но может потребоваться создать статистику по столбцам соединения для достижения pushdown.

Примечание.

Вычисления pushdown могут блокироваться определенным синтаксисом T-SQL. Дополнительные сведения: Синтаксис для блокировки pushdown.

Вычисления с поддержкой pushdown и поставщики Hadoop

PolyBase в настоящее время поддерживает два поставщика Hadoop: Платформа данных Hortonworks (HDP) и Cloudera Distributed Hadoop (CDH). В плане вычислений pushdown между этими двумя компонентами нет различий.

Чтобы использовать функции pushdown вычислений с Hadoop, целевой кластер Hadoop должен иметь основные компоненты HDFS, YARN и MapReduce с включенным сервером журнала заданий. PolyBase отправляет запрос на передачу через MapReduce и получает сведения о состоянии c сервера журнала заданий. Без любого из этих компонентов запрос завершится сбоем.

Некоторые агрегаты должны возникать после достижения данных SQL Server. Однако часть статистического вычисления происходит в Hadoop. Это стандартный метод вычисления статистических выражений в системах обработки данных с массовым параллелизмом.

Поставщики Hadoop поддерживают следующие агрегаты и фильтры.

Агрегаты	Фильтры (двоичное сравнение)
Count_Big	NotEqual
Sum	LessThan;
Ср.	LessOrEqual
Макс.	GreaterOrEqual
Мин.	GreaterThan
Approx_Count_Distinct	Является
	IsNot

Ключевые сценарии эффективного использования вычислений pushdown

Вычисления pushdown в PolyBase позволяют делегировать задачи вычислений внешним источникам данных. Это снижает рабочую нагрузку на экземпляре SQL Server и может значительно повысить производительность.

SQL Server может отправлять соединения, проекции, агрегаты и фильтры во внешние источники данных, чтобы воспользоваться преимуществами удаленных вычислений и ограничить данные, отправленные по сети.

Pushdown-отправка соединений

Во многих случаях PolyBase может упростить отправку оператора соединения для соединения двух внешних таблиц в одном и том же внешнем источнике данных, что значительно повышает производительность.

Если соединение можно выполнить на внешнем источнике данных, это сокращает число перемещений данных и повышает производительность запроса. Если не выполнять pushdown-отправку соединения, то данные из соединяемых таблиц необходимо перенести в локальную базу 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 для получения строк, соответствующих предикату customer.account_balance < 200000 в Hadoop. Так как запрос можно выполнить и без сканирования всех строк в таблице, в 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 может отправлять в Hadoop задания map-reduce для выполнения предиката customer.account_balance <= 200000. Выражение BETWEEN 92656 AND 92677 также состоит из двоичных и логических операций, которые могут быть переданы в Hadoop. Логический оператор AND в столбцах customer.account_balance AND customer.zipcode является конечным выражением.

С учетом этого сочетания предикатов задания map-reduce могут выполнять все условия, указанные в предложении WHERE. В SQL Server копируются только те данные, которые соответствуют условиям SELECT.

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

Поддерживаемые функции для pushdown

SQL Server позволяет выполнять следующие функции для pushdown предиката.

Строковые функции

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

Синтаксис для блокировки pushdown

Следующие функции или синтаксис T-SQL препятствуют вычислению pushdown:

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

Поддержка pushdown для синтаксиса FORMAT была TRIM представлена в ПАКЕТе обновления 10 (CU10) SQL Server 2019 (15.x).

Предложение фильтра с переменной

При указании переменной в предложении фильтра по умолчанию это предотвращает принудительное нажатие предложения фильтра. Например, если выполнить следующий запрос, pushdown для предложения фильтра не будет выполняться:

DECLARE @BusinessEntityID INT

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

Чтобы выполнить pushdown для переменной, необходимо включить функцию исправлений оптимизатора запросов. Это можно сделать любым из следующих способов:

Уровень экземпляра. Включите флаг трассировки 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 могут препятствовать результату. Поддерживаются равные параметры сортировки или двоичные параметры сортировки. Дополнительные сведения см. в разделе "Как определить, произошла ли отправка".

Pushdown для файлов parquet

Начиная с SQL Server 2022 (16.x), PolyBase представила поддержку файлов parquet. SQL Server может выполнять как удаление строк, так и столбцов при выполнении pushdown с помощью parquet. С помощью файлов parquet можно отправить следующие операции:

Операторы двоичного сравнения (>, >=, <=, <) для числовых, дат и значений времени.
Сочетание операторов сравнения (> AND <, >= AND<, > <AND = = AND = AND <= AND >=).
В фильтре списка (col1 = val1 OR col1 = val2 OR vol1 = val3).
ЗНАЧЕНИЕ IS NOT NULL по столбцу.

Наличие следующих средств предотвращает отправку файлов parquet:

Виртуальные столбцы.
Сравнение столбцов.
Преобразование типов параметров.

Поддерживаемые типы данных

бит
TinyInt
SmallInt
BigInt
Вещественное число
Тип с плавающей запятой
VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
NVARCHAR (Bin2Collation, BinCollation)
Binary
DateTime2 (точность по умолчанию и 7 цифр)
Дата
Время (точность по умолчанию и 7 цифр)
Числовой*

* Поддерживается, если шкала параметров соответствует шкале столбцов или если параметр явно приведение к десятичному разряду.

Типы данных, которые предотвращают отложение parquet

Money
SmallMoney
Дата/время
SmallDateTime

Примеры

Принудительное включение

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

Отключить включение

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

Дополнительные сведения о PolyBase см. в статье Общие сведения о виртуализации данных с помощью PolyBase.
Как определить, произошел ли внешний pushdown

Поделиться через

Вычисления pushdown в PolyBase

Включение вычислений pushdown

Вычисления с поддержкой pushdown и поставщики Hadoop

Ключевые сценарии эффективного использования вычислений pushdown

Pushdown-отправка соединений

Выбор подмножества строк

Выбор подмножества столбцов

Включение основных выражений и операторов

Поддерживаемые функции для pushdown

Синтаксис для блокировки pushdown

Предложение фильтра с переменной

Конфликт сортировки

Pushdown для файлов parquet

Поддерживаемые типы данных

Типы данных, которые предотвращают отложение parquet

Примеры

Принудительное включение

Отключить включение

Обратная связь

Дополнительные ресурсы

Поделиться через

Вычисления pushdown в PolyBase

Включение вычислений pushdown

Вычисления с поддержкой pushdown и поставщики Hadoop

Ключевые сценарии эффективного использования вычислений pushdown

Pushdown-отправка соединений

Выбор подмножества строк

Выбор подмножества столбцов

Включение основных выражений и операторов

Поддерживаемые функции для pushdown

Синтаксис для блокировки pushdown

Предложение фильтра с переменной

Конфликт сортировки

Pushdown для файлов parquet

Поддерживаемые типы данных

Типы данных, которые предотвращают отложение parquet

Примеры

Принудительное включение

Отключить включение

Связанный контент

Обратная связь

Дополнительные ресурсы