Calculs pushdown dans PolyBase

  • Article

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 Jointures 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 Certains** Certains** Oui
Stockage Blob Azure Non Non Non Non Oui

* La prise en charge pushdown d’Azure Cosmos DB est activée via l’API Azure Cosmos DB pour MongoDB.

** Consultez le calcul pushdown et les 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.

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

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

Pour utiliser la fonctionnalité de calcul pushdown avec Hadoop, le cluster Hadoop cible doit être doté des principaux composants de HDFS, de YARN et de MapReduce, avec le serveur de l’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
Somme 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 améliorer considérablement les performances.

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

Pushdown des jointures

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

Si la jointure peut être effectuée au niveau de la source de données externe, cela réduit la quantité de déplacements des données et améliore les performances de la requête. Sans le pushdown des jointures, les données des tables à joindre doivent être copiées en local dans tempdb, puis jointes.

Notez que dans le cas de jointures distribuées (jointure d’une table locale à une table externe), sauf s’il existe des critères de filtrage sur la table externe appliquées à la condition de jointure, toutes les données de la table externe doivent être introduites 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 se trouve sur E.id la colonne de la table externe, si une condition de filtre est ajoutée à cette colonne, le filtre peut être poussé vers le bas, réduisant ainsi 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 map-reduce 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. Cela permet d’économiser beaucoup de temps et nécessite moins d’espace de stockage temporaire lorsque le nombre de soldes < clients 200000 est faible par rapport au nombre de clients avec 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

SQL Server autorise les opérateurs et expressions de base suivants pour une poussée vers le bas 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, NOT, IN et LIKE peuvent être refoulés. Le comportement réel dépend de la façon dont l’optimiseur de requête réécrit les expressions des opérateurs sous la forme d’une série d’instructions qui utilisent 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 les fonctions suivantes pour le pushdown de prédicat.

Fonctions de chaînes

  • 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 avec 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

Les fonctions ou la syntaxe T-SQL suivantes empêchent le calcul 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

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

Clause de filtre avec variable

Si vous spécifiez une variable dans une clause de filtre, cela empêche par défaut la sélection de la clause de filtre. Par exemple, si vous exécutez la requête suivante, la clause de filtre n’est pas envoyée :

DECLARE @BusinessEntityID INT

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

Pour envoyer la variable, vous devez activer la fonctionnalité des correctifs logiciels de l’optimiseur de requête. Cela peut être fait de l’une des manières 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 contenant les objets PolyBase externes, exécutez ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON
  • Niveau de la requête : utilisez l’indicateur de requête OPTION (QUERYTRACEON 4199) ou OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

Cette limitation s’applique à l’exécution de sp_executesql. La limitation s’applique également à l’utilisation de certaines fonctions dans la clause de filtre.

Remarque : La possibilité d’appliquer un pushdown à la variable a été introduite pour la première fois dans SQL Server 2019 CU5.

Conflit de classement

Lorsque vous travaillez avec des données avec un pushdown de classement différent peut ne pas être possible, les opérateurs comme COLLATE ceux-ci peuvent également interférer avec le résultat. Les classements égaux ou les classements binaires sont pris en charge. Pour plus d’informations, consultez Guide pratique pour savoir si un pushdown s’est produit.

Pushdown pour les fichiers Parquet

À compter 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. Lorsque vous utilisez des fichiers Parquet, les opérations suivantes peuvent être poussées vers le bas :

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

La présence des éléments suivants empêche le pushdown pour les fichiers Parquet :

  • Colonnes virtuelles.
  • Comparaison de colonnes.
  • Conversion de type de paramètre.

Types de données pris 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

  • Money
  • SmallMoney
  • Date/Heure
  • SmallDateTime

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

Étapes suivantes

Voir aussi