Freigeben über

Weitergabeberechnungen in PolyBase

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höhere Versionen

Weitergabeberechnungen verbessern die Leistung von Abfragen in externen Datenquellen. Seit SQL Server 2016 (13.x) sind PushDown-Berechnungen für externe Hadoop-Datenquellen verfügbar. In SQL Server 2019 (15.x) wurden PushDown-Berechnungen für andere Arten von externen Datenquellen eingeführt.

Hinweis

Informationen dazu, ob Ihre Abfrage von der PolyBase-Pushdownberechnung profitiert oder nicht, finden Sie unter Identifizieren eines externen Pushdowns.

Aktivieren der PushDown-Berechnung

Die folgenden Artikel enthalten Informationen zum Konfigurieren von PushDown-Berechnungen für bestimmte Typen von externen Datenquellen:

Diese Tabelle fasst die Unterstützung der Pushdownberechnung für verschiedene externe Datenquellen zusammen:

Data source Joins Projektionen Aggregationen Filter Statistik
Generisches ODBC Ja Ja Ja Ja Ja
Oracle Ja+ Ja Ja Ja Ja
SQL Server Ja Ja Ja Ja Ja
Teradata Ja Ja Ja Ja Ja
MongoDB* Nein Ja Ja*** Ja*** Ja
Hadoop Nein Ja Einige** Einige** Ja
Azure Blob Storage No Nr. Nr. Nein Ja

* Die Pushdown-Unterstützung von Azure Cosmos DB wird über die Azure Cosmos DB-API für MongoDB aktiviert.

* Siehe Pushdownberechnung und Hadoop-Anbieter.

*** Pushdown-Unterstützung für Aggregationen und Filter für den MongoDB-ODBC-Konnektor für SQL Server 2019 wurde mit SQL Server 2019 CU18 eingeführt.

+ Oracle unterstützt Pushdowns für Verknüpfungen, aber möglicherweise müssen Sie Statistiken zu den Verknüpfungsspalten erstellen, um einen Pushdown zu erzielen.

Hinweis

Die Pushdownberechnung kann durch eine T-SQL-Syntax blockiert werden. Weitere Informationen finden Sie unter Syntax, die die Weitergabe verhindert.

Pushdownberechnung und Hadoop-Anbieter

PolyBase unterstützt aktuell zwei Hadoop-Anbieter: Hortonworks Data Platform (HDP) und Cloudera Distributed Hadoop (CDH). Es gibt keine Unterschiede zwischen den beiden Features hinsichtlich der Pushdownberechnung.

Wenn Sie die Pushdownberechnungsfunktion für Hadoop verwenden möchten, muss der Hadoop-Zielcluster über die Kernkomponenten von HDFS (Hadoop Distributed File System), YARN und MapReduce verfügen. Dabei muss der Auftragsverlaufserver aktiviert sein. PolyBase übermittelt die Weitergabeabfrage über MapReduc und ruft den Status über den Auftragsverlaufserver ab. Wenn keine dieser Komponenten vorhanden ist, tritt bei der Abfrage ein Fehler auf.

Nachdem die Daten den SQL Server erreicht haben, muss eine Aggregation durchgeführt werden. Ein Teil dieser Aggregation ist in Hadoop verfügbar. Dies ist eine häufig verwendete Methode zum Berechnen von Aggregationen in MPP-Systemen (Massively Parallel Processing = massive Parallelverarbeitung).

Hadoop-Anbieter unterstützen die folgenden Aggregationen und Filter.

Aggregationen Filter (binärer Vergleich)
Count_Big Ungleich
Sum LessThan
Avg LessOrEqual
Max GreaterOrEqual
Min GreaterThan
Approx_Count_Distinct Is
IsNot

Wichtige nützliche Szenarios der Pushdownberechnung.

Mit der PolyBase-Pushdownberechnung können Sie Berechnungsaufgaben an externe Datenquellen delegieren. Somit wird die Workload auf der SQL Server-Instanz vermindert, und die Leistung wird dadurch erheblich verbessert.

SQL Server kann Verknüpfungen, Projektionen, Aggregationen und Filter an externe Datenquellen pushen, um von Remotecompute-Funktionen zu profitieren und die über das Netzwerk gesendete Daten einzuschränken.

Weitergabe von Verknüpfungen

In vielen Fällen kann PolyBase den Pushdown des Verknüpfungsoperators bei der Verknüpfung zweier externer Tabellen auf der selben externen Datenquelle unterstützen, wodurch die Leistung erheblich verbessert wird.

Wenn die Verknüpfung bei den externen Datenquellen ausgeführt werden kann, wird dadurch die Menge der Datenverschiebung reduziert und die Abfrageleistung erhöht. Ohne den Verknüpfungspushdown müssen die zu verknüpfenden Daten aus den Tabellen lokal in tempdb verschoben und dann erst verknüpft werden.

Bei verteilten Verknüpfungen (Verknüpfen einer lokalen mit einer externen Tabelle) müssen alle Daten in der externen Tabelle lokal in tempdb zusammengeführt werden, damit der Verknüpfungsvorgang ausgeführt werden kann; es sei denn, auf die externe Tabelle wird ein Filter angewendet. Die folgende Abfrage enthält z. B. keinen Filter für die Verknüpfungsbedingung der externer Tabellen, was dazu führt, dass alle Daten aus der externen Tabelle gelesen werden.

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

Da sich die Verknüpfung auf der E.id-Spalte der externen Tabelle befindet, kann auf den Filter ein Pushdown angewendet werden, wenn dieser Spalte eine Filterbedingung hinzugefügt wird. Dadurch wird die Anzahl der Zeilen reduziert, die aus der externen Tabelle gelesen werden.

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

Auswählen einer Teilmenge von Zeilen

Verwenden Sie die Prädikatweitergabe zum Verbessern der Leistung für eine Abfrage, die eine Teilmenge von Zeilen aus einer externen Tabelle auswählt.

In diesem Beispiel initiiert SQL Server einen MapReduce-Auftrag zum Abrufen der Zeilen, die dem Prädikat customer.account_balance < 200000 auf Hadoop entsprechen. Da die Abfrage nicht erfolgreich abschließen kann, ohne alle Zeilen der Tabelle zu scannen, werden nur die Zeilen, die den Prädikatskriterien entsprechen, in SQL Server kopiert. Dies spart Zeit und erfordert weniger temporären Speicherplatz, wenn die Anzahl der Debitorensalden < 200000 im Vergleich zur Anzahl der Kunden mit Kontensalden >= 200000 klein ist.

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

Auswählen einer Teilmenge von Spalten

Verwenden Sie die Prädikatweitergabe zum Verbessern der Leistung für eine Abfrage, die eine Teilmenge von Spalten aus einer externen Tabelle auswählt.

In dieser Abfrage initiiert SQL Server einen MapReduce-Auftrag, um die durch Trennzeichen getrennte Hadoop-Textdatei vorab zu verarbeiten, sodass nur die Daten der zwei Spalten „customer.name“ und „customer.zip_code“ in SQL Server kopiert werden.

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

Weitergabe für grundlegende Ausdrücke und Operatoren

SQL Server erlaubt die folgenden grundlegenden Ausdrücke und Operatoren für Prädikatweitergabe.

  • Binäre Vergleichsoperatoren (<, >, =, !=, <>, >=, <=) für die Zahlen-, Datums- und Zeitwerte.
  • Arithmetische Operatoren (+, -, *, /, %)
  • Logische Operatoren (AND, OR)
  • Unäre Operatoren (NOT, IS NULL, IS NOT NULL)

Die Operatoren BETWEEN, NOT, IN und LIKE werden möglicherweise weitergegeben. Das aktuelle Verhalten hängt davon ab, wie der Abfrageoptimierer die Operatorausdrücke als eine Reihe von Anweisungen neu schreibt, die grundlegende relationale Operatoren verwenden.

Die Abfrage in diesem Beispiel verfügt über mehrere Prädikate, die an Hadoop weitergegeben werden können. SQL Server kann map-reduce-Aufträge an Hadoop weitergeben, um das Prädikat customer.account_balance <= 200000 auszuführen. Der Ausdruck BETWEEN 92656 AND 92677 besteht auch aus binären und logischen Operationen, die an Hadoop weitergegeben werden können. Das logische AND in customer.account_balance AND customer.zipcode ist ein finaler Ausdruck.

Mit dieser Kombination von Prädikaten können also die map-reduce-Aufträge alle Bedingungen der WHERE-Klausel vollständig bearbeiten. Nur die Daten, die die Kriterien von SELECT erfüllen, werden in SQL Server zurückkopiert.

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

Unterstützte Funktionen für Pushdown

SQL Server erlaubt die folgenden Funktionen für die Prädikatweitergabe.

Zeichenfolgenfunktionen

  • CONCAT
  • DATALENGTH
  • LEN
  • LIKE
  • LOWER
  • LTRIM
  • RTRIM
  • SUBSTRING
  • UPPER

Mathematische Funktionen

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

Allgemeine Funktionen

  • COALESCE *
  • NULLIF

* Die Verwendung mit COLLATE kann Pushdown in einigen Szenarien verhindern. Weitere Informationen finden Sie unter Sortierungskonflikt.

Datums- und Uhrzeitfunktionen

  • DATEADD
  • DATEDIFF
  • DATEPART

Syntax, die den Pushdown verhindert

Die folgenden T-SQL-Funktionen oder -Syntax verhindern eine Pushdown-Berechnung:

  • 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

Pushdownunterstützung für die FORMAT- und TRIM-Syntax wurde in SQL Server 2019 (15.x) CU10 eingeführt.

Filterklausel mit Variablen

Wenn in einer Filterklausel eine Variable angegeben wird, verhindert diese standardmäßig das Pushdown der Filterklausel. Wenn Sie beispielsweise die folgende Abfrage ausführen, erfolgt kein Pushdown für die Filterklausel:

DECLARE @BusinessEntityID INT

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

Um ein Pushdown der Variablen zu erreichen, müssen Sie die Hotfixfunktionen des Abfrageoptimierers aktivieren. Dafür gibt es folgende Möglichkeiten:

  • Instanzebene: Aktivieren Sie das Ablaufverfolgungsflag 4199 als Startparameter für die Instanz.
  • Datenbankebene: Führen Sie im Kontext der Datenbank mit den externen PolyBase-Objekten ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON aus.
  • Abfrageebene: Verwenden Sie Abfragehinweis OPTION (QUERYTRACEON 4199) oder OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

Diese Begrenzung gilt für das Ausführen von sp_executesql. Die Einschränkung gilt auch für die Verwendung bestimmter Funktionen in der Filterklausel.

Die Möglichkeit zum Pushdown der Variablen wurde erstmals mit dem fünften kumulativen Update (CU5) von SQL Server 2019 eingeführt.

Sortierungskonflikt

Pushdown ist mit Daten, die unterschiedliche Sortierungen aufweisen, vielleicht nicht möglich. Operatoren wie COLLATE können ebenfalls das Ergebnis beeinträchtigen. Gleiche oder binäre Sortierungen werden unterstützt. Weitere Informationen finden Sie unter So finden Sie heraus, ob ein Pushdown stattgefunden hat.

Pushdown für Parquet-Dateien

Ab SQL Server 2022 (16.x) hat PolyBase Unterstützung für Parquet-Dateien eingeführt. SQL Server ist in der Lage, bei der Durchführung von Pushdown mit Parquet sowohl Zeilen- als auch Spalteneliminierung durchzuführen. Bei Parquet-Dateien kann für die folgenden Vorgänge ein Pushdown stattfinden:

  • Binäre Vergleichsoperatoren (>, >=, <=, <) für Zahlen-, Datums- und Zeitwerte.
  • Kombination von Vergleichsoperatoren (> UND <, >= UND <, > UND <=, <= UND >=).
  • In Listenfilter (Col1 = val1 ODER col1 = val2 ODER vol1 = val3).
  • IST NICHT NULL in der Spalte.

Die folgenden Gegebenheiten verhindern den Pushdown bei Parquet-Dateien:

  • Virtuelle Spalten.
  • .Vergleich von Spalten.
  • Parametertypen-Konvertierungen.

Unterstützte Datentypen

  • Bit
  • TinyInt
  • SmallInt
  • BigInt
  • Real
  • Gleitkomma
  • VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
  • NVARCHAR (Bin2Collation, BinCollation)
  • Binär
  • DateTime2 (Standard- und 7-stellige Genauigkeit)
  • Datum
  • Zeit (Standard- und 7-stellige Genauigkeit)
  • Numerisch *

* Wird unterstützt, wenn die Parameterskala dem Spaltenmaßstab entspricht oder wenn der Parameter explizit auf eine Dezimalzahl festgelegt wird.

Datentypen, die Parquet-Pushdown verhindern

  • Zahlung
  • SmallMoney
  • DateTime
  • SmallDateTime

Beispiele

Pushdown erzwingen

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

Pushdown deaktivieren

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

Zugehöriger Inhalt