Partager via

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 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 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 considérablement améliorer les performances.

SQL Server peut pousser des jointures, des projections, des agrégations et des filtres verfs des sources de données externes pour bénéficier du calcul à distance 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éliorera fortement 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.

Dans le cas de jointures distribuées (jointure d’une table locale à une table externe), sauf s’il existe un filtre sur la table externe jointe, toutes les données de la table externe doivent être insérées localement dans 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 la colonne E.id 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 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

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 un 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 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

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 du pushdown pour la syntaxe FORMAT et TRIM a été introduite dans SQL Server 2019 (15.x) CU10.

Clause de filtre avec variable

Lorsque l’on spécifie une variable dans une clause de filtre, cela empêche par défaut le pushdown 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 avec les objets externes PolyBase, exécutez ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON
  • Niveau de requête : utiliser 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.

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

Conflit de classement

Le pushdown peut ne pas être possible avec des données ayant des classements différents. Les opérateurs comme ceux-ci COLLATE peuvent également interférer avec le résultat. Les classements égaux ou les classements binaires sont pris en charge. Pour plus d’informations, consultez Comment savoir si un pushdown 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. Avec les fichiers parquet, les opérations suivantes peuvent être repoussées :

  • 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).
  • IS NOT 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 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)
  • Binary
  • 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);

Contenu connexe