Calculs pushdown dans PolyBase

S’applique à : SQL Server 2016 (13.x) et versions ultérieures

Le calcul pushdown améliore les performances des requêtes sur les sources de données externes. À compter de SQL Server 2016 (13.x), les calculs pushdown étaient disponibles pour les sources de données externes Hadoop. SQL Server 2019 (15.x) a introduit des calculs pushdown pour d’autres types de sources de données externes.

Remarque

Pour déterminer si le calcul pushdown PolyBase profite, ou non, à votre requête, consultez Guide pratique pour savoir si un pushdown externe s’est produit.

Activer le calcul pushdown

Les articles suivants contiennent des informations sur la configuration du calcul pushdown pour des types spécifiques de sources de données externes :

Ce tableau résume la prise en charge du calcul pushdown sur différentes sources de données externes :

Source de données	Joins	Projections	Agrégations	Filtres	Statistiques
ODBC générique	Oui	Oui	Oui	Oui	Oui
Oracle	Oui+	Oui	Oui	Oui	Oui
SQL Server	Oui	Oui	Oui	Oui	Oui
Teradata	Oui	Oui	Oui	Oui	Oui
MongoDB*	Aucun	Oui	Oui***	Oui***	Oui
Hadoop	Aucun	Oui	En partie**	En partie**	Oui
Stockage Blob Azure	Non	Non	Non	Non	Oui

* La prise en charge du pushdown Azure Cosmos DB est activée par le biais de l’API Azure Cosmos DB pour MongoDB.

** Consultez Calcul pushdown et fournisseurs Hadoop.

*** La prise en charge pushdown des agrégations et des filtres pour le connecteur ODBC MongoDB pour SQL Server 2019 a été introduite avec SQL Server 2019 CU18.

+ Oracle prend en charge le pushdown pour les jointures, mais vous devrez peut-être créer des statistiques sur les colonnes de jointure pour obtenir un pushdown.

Remarque

Le calcul pushdown peut être bloqué par la syntaxe T-SQL. Pour plus d’informations, consultez Syntaxe qui empêche le pushdown.

Calcul pushdown et fournisseurs Hadoop

Actuellement, PolyBase prend en charge deux fournisseurs Hadoop : HDP (Hortonworks Data Platform) et CDH (Cloudera Distributed Hadoop). Il n’existe aucune différence entre les deux fournisseurs en termes de calcul pushdown.

Pour utiliser la fonctionnalité pushdown de calcul avec Hadoop, le cluster Hadoop cible doit avoir les composants principaux : HDFS, YARN et MapReduce, avec le serveur d’historique des travaux activé. PolyBase envoie la requête émise via MapReduce et extrait l’état à partir du serveur Job History. L’absence de l’un ou l’autre composant entraîne l’échec de la requête.

Certaines agrégations doivent se produire une fois que les données atteignent SQL Server. mais qu’une partie de l’agrégation se produit dans Hadoop. Il s’agit d’une méthode courante de calcul des agrégations dans les systèmes à traitement parallèle massif.

Les fournisseurs Hadoop prennent en charge les agrégations et filtres suivants.

Agrégations	Filtres (comparaison binaire)
Count_Big	NotEqual
Sum	LessThan
Avg	LessOrEqual
Max	GreaterOrEqual
Min	GreaterThan
Approx_Count_Distinct	Est
	IsNot

Principaux scénarios propices au calcul pushdown

Avec le calcul pushdown PolyBase, vous pouvez déléguer des tâches de calcul à des sources de données externes. Cela réduit la charge de travail sur l’instance SQL Server et peut considérablement améliorer les performances.

SQL Server peut envoyer (push) des jointures, des projections, des agrégations et des filtres vers des sources de données externes, tirer parti du calcul distant et restreindre les données envoyées sur le réseau.

Optimisation de jointure par pushdown

PolyBase peut faciliter le pushdown de l’opérateur de jointure lorsque vous joignez deux tables externes sur la même source de données externe, ce qui améliore considérablement les performances.

Lorsque la source de données externe effectue la jointure, elle réduit la quantité de déplacement des données et améliore les performances des requêtes. Sans optimisation de jointure par pushdown, SQL Server doit importer les données des deux tables localement dans tempdb, puis effectuer la jointure.

Dans le cas de jointures distribuées (jointure d’une table locale à une table externe), sauf si votre filtre s’applique à la table externe jointe, SQL Server doit importer toutes les données de la table externe localement tempdb pour effectuer l’opération de jointure. Par exemple, la requête suivante n’a aucun filtrage sur la condition de jointure de table externe, ce qui entraîne la lecture de toutes les données de la table externe.

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

Étant donné que la jointure utilise la E.id colonne de la table externe, lorsque vous ajoutez une condition de filtre à cette colonne, SQL Server peut pousser le filtre vers le bas, ce qui réduit le nombre de lignes lues à partir de la table externe.

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

Sélectionner un sous-ensemble de lignes

Utilisez une poussée vers le bas de prédicat pour améliorer les performances d’une requête qui sélectionne un sous-ensemble de lignes d’une table externe.

Dans cet exemple, SQL Server lance un travail MapReduce pour récupérer les lignes qui correspondent au prédicat customer.account_balance < 200000 sur Hadoop. Comme la requête peut s’effectuer correctement sans analyser toutes les lignes de la table, seules les celles qui répondent aux critères du prédicat sont copiées sur SQL Server. Cette opération permet de gagner un temps considérable et nécessite moins d’espace de stockage temporaire quand le nombre de soldes clients < 200000 est faible par rapport au nombre de clients ayant des soldes de compte >= 200000.

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

Sélectionner un sous-ensemble de colonnes

Utilisez une poussée vers le bas de prédicat pour améliorer les performances d’une requête qui sélectionne un sous-ensemble de colonnes d’une table externe.

Dans cette requête, SQL Server lance une tâche Map/Reduce pour prétraiter le fichier texte délimité Hadoop afin que seules les données pour les deux colonnes, customer.name et customer.zip_code, soient copiées dans SQL Server.

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

Poussée vers le bas pour les opérateurs et expressions de base

Le SQL Server permet ces expressions et opérateurs de base pour la poussée/transfert de prédicat :

Opérateurs de comparaison binaire (<, >, =, !=, <>, >=, <=) pour les valeurs numériques, d’heure et de date.
Opérateurs arithmétiques (+, -, *, /, %).
Opérateurs logiques (AND, OR).
Opérateurs unaires (NOT, IS NULL, IS NOT NULL).

Les opérateurs BETWEEN, , NOTINet LIKE peuvent être poussés vers le bas en fonction de la façon dont l’optimiseur de requête réécrit les expressions d’opérateur sous la forme d’une série d’instructions utilisant des opérateurs relationnels de base.

La requête de cet exemple comporte plusieurs prédicats pouvant être refoulés vers Hadoop. SQL Server peut placer des travaux MapReduce dans Hadoop pour exécuter le prédicat customer.account_balance <= 200000. L’expression BETWEEN 92656 AND 92677 est également constituée d’opérations binaires et logiques qui peuvent être empilées vers Hadoop. Le AND logique dans customer.account_balance AND customer.zipcode est une expression finale.

Étant donnée cette combinaison de prédicats, les travaux MapReduce peuvent exécuter l’ensemble de la clause WHERE. Seules les données qui répondent aux critères SELECT seront recopiées dans SQL Server.

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

Fonctions prises en charge pour le pushdown

SQL Server autorise ces fonctions pour le pushdown de prédicat :

Fonctions de chaîne :

CONCAT
DATALENGTH
LEN
LIKE
LOWER
LTRIM
RTRIM
SUBSTRING
UPPER

Fonctions mathématiques :

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

Fonctions générales :

COALESCE *
NULLIF

* L’utilisation de COLLATE peut empêcher le pushdown dans certains scénarios. Pour plus d’informations, consultez Conflit de classement.

Fonctions de date et d’heure :

DATEADD
DATEDIFF
DATEPART

Syntaxe qui empêche le pushdown

Ces fonctions T-SQL ou éléments de syntaxe empêchent le calcul par poussée :

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

La prise en charge du pushdown pour la syntaxe FORMAT et TRIM a été introduite dans SQL Server 2019 (15.x) CU10.

Clause de filtre avec variable

Lorsque vous spécifiez une variable dans une clause de filtre, par défaut, SQL Server ne transmet pas la clause de filtre. Par exemple, la requête suivante ne transmet pas la clause de filtre :

DECLARE @BusinessEntityID INT

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

Pour activer le pushdown de la variable, activez la fonctionnalité des correctifs logiciels de l’optimiseur de requête à l’aide de l’une des méthodes suivantes :

Niveau de l’instance : activez l’indicateur de trace 4199 en tant que paramètre de démarrage pour l’instance.
Niveau de la base de données : dans le contexte de la base de données qui a les objets externes PolyBase, exécutez ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON.
Niveau de requête : utiliser l’indicateur OPTION (QUERYTRACEON 4199) de requête ou OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES')).

Cette limitation s'applique à l'exécution de sp_executesql et à certaines fonctions de la clause de filtre.

SQL Server 2019 CU5 a d’abord introduit la possibilité de pousser la variable vers le bas.

Pour plus d’informations, consultez sp_executesql.

Conflit de classement

Le "pushdown" peut ne pas fonctionner avec des données ayant des collations différentes. Les opérateurs comme ceux-ci COLLATE peuvent également interférer avec le résultat. SQL Server prend en charge les classements égaux ou les classements binaires. Pour plus d’informations, consultez Comment savoir si un pushdown externe s’est produit.

Pushdown pour les fichiers Parquet

À partir de SQL Server 2022 (16.x), PolyBase a introduit la prise en charge des fichiers Parquet. SQL Server est capable d’effectuer à la fois l’élimination des lignes et des colonnes lors de l’exécution d’un pushdown avec parquet.

Sources de données externes prises en charge

La prise en charge du pushdown Parquet est assurée pour les sources de données externes suivantes :

Stockage d’objets compatible S3
Stockage Blob Azure
Azure Data Lake Storage Gen2

Pour plus d’informations sur la configuration, consultez :

Opérations de réduction en pile

SQL Server peut déléguer ces opérations à l'aide de fichiers Parquet :

Opérateurs de comparaison binaire (>, >=, <=, <) pour les valeurs numériques, d’heure et de date.
Combinaison d’opérateurs de comparaison (> ET <, >= ET <, > ET <=, <= ET >=).
Dans le filtre de liste (col1 = val1 OU col1 = val2 OU vol1 = val3).
N’EST PAS NULL sur une colonne.

Ces éléments empêchent le pushdown pour les fichiers Parquet :

Colonnes virtuelles.
Comparaison de colonnes.
Conversion des types de paramètres.

Types de données prises en charge

bit
tinyint
smallint
bigint
real
float
varchar (Bin2Collation, CodePageConversion, BinCollation)
nvarchar (Bin2Collation, BinCollation)
binaire
datetime2 (précision par défaut et à 7 chiffres)
date
heure (précision par défaut et à 7 chiffres)
Numérique *

* Pris en charge lorsque l’échelle des paramètres s’aligne sur l’échelle de colonne ou lorsque le paramètre est explicitement converti en décimale.

Types de données qui empêchent le pushdown parquet

argent
smallmoney
datetime
smalldatetime

Élimination de partitions avec des structures de dossiers

PolyBase peut utiliser des structures de dossiers pour l’élimination de partition, ce qui réduit la quantité de données analysées pendant les requêtes. Lorsque vous organisez des fichiers Parquet dans des dossiers hiérarchiques (par exemple, par année, mois ou d’autres clés de partitionnement), PolyBase peut ignorer des dossiers entiers qui ne correspondent pas à vos prédicats de requête.

Par exemple, si vous structurez vos données comme suit :

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

Vous pouvez interroger des partitions spécifiques à l’aide de caractères génériques dans OPENROWSET ou les emplacements de table externes :

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

Pour l’élimination dynamique des dossiers, interrogez un chemin d’accès de dossier plus large et utilisez des prédicats filepath() pour éliminer les partitions au moment de l’exécution.

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);

Cette approche combine l’élimination de partition au niveau du dossier avec le pushdown au niveau du fichier Parquet pour optimiser les performances des requêtes. Pour obtenir un didacticiel complet sur l’interrogation de fichiers Parquet avec des modèles de dossiers, consultez Virtualize parquet file in a S3-compatible object storage with PolyBase.

Exemples

Forcer la poussée vers le bas

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

Désactiver la poussée vers le bas

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

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2026-02-11