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. Ab SQL Server 2016 (13.x) waren Pushdownberechnungen für Hadoop externe Datenquellen verfügbar. SQL Server 2019 (15.x) führte Pushdownberechnungen für andere Arten externer Datenquellen ein.

Hinweis

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

Aktivieren der Pushdownberechnung

Die folgenden Artikel enthalten Informationen zum Konfigurieren von Weitergabeberechnungen 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 Nein Nr. Nr. Nein Ja

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

** Siehe Pushdownberechnung und Hadoop-Anbieter.

Pushdownunterstützung für Aggregationen und Filter für den MongoDB ODBC-Connector für SQL Server 2019 wurde mit SQL Server 2019 CU18 eingeführt.

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 derzeit 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.

Einige Aggregationen müssen auftreten, nachdem die Daten SQL Server erreicht haben. 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 NotEqual
Sum LessThan
Durchn. 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. Dadurch wird die Arbeitsauslastung in der SQL Server-Instanz reduziert und die Leistung erheblich verbessert.

SQL Server kann Verknüpfungen, Projektionen, Aggregationen und Filter an externe Datenquellen übertragen, um die Remoteberechnung zu nutzen und die über das Netzwerk gesendeten Daten einzuschränken.

Weitergabe von Verknüpfungen

In vielen Fällen kann PolyBase den Pushdown des Verknüpfungsoperators für die Verknüpfung zweier externer Tabellen in derselben externen Datenquelle erleichtern, 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.

Beachten Sie, dass bei verteilten Verknüpfungen (Verknüpfen einer lokalen Tabelle mit einer externen Tabelle) nur einige Filterkriterien für die externe Tabelle vorhanden sind, die auf die Verknüpfungsbedingung angewendet wird, alle Daten in der externen Tabelle lokal tempdb zur Durchführung des Verknüpfungsvorgangs übertragen werden müssen. Die folgende Abfrage hat z. B. keine Filterung für die Bedingung für die Verknüpfung 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 E.id einer Spalte der externen Tabelle befindet, kann der Filter gedrückt werden, wenn dieser Spalte eine Filterbedingung hinzugefügt wird, wodurch die Anzahl der aus der externen Tabelle gelesenen Zeilen reduziert wird.

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 Zuordnungsverkürzungsauftrag, um die Zeilen abzurufen, 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 erhebliche Zeit und erfordert weniger temporären Speicherplatz, wenn die Anzahl der Kundensaldos < 200000 im Vergleich zur Anzahl der Kunden mit Kontoguthaben >= 200000 gering 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 ermöglicht die folgenden Funktionen für Prädikat-Pushdown.

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 "Pushdown" kann in einigen Szenarien verhindert werden. 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 Pushdownberechnung:

  • 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 die Syntax wurde in SQL Server 2019 (15.x) CU10 eingeführt.

Filterklausel mit Variablen

Wenn Sie eine Variable in einer Filterklausel angeben, verhindert dies 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 den 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 einiger Funktionen in der Filterklausel.

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

Kollatierungskonflikt

Bei der Arbeit mit Daten mit unterschiedlichen Sortierungs-Pushdowns ist möglicherweise nicht möglich, operatoren wie COLLATE können auch das Ergebnis beeinträchtigen. Gleiche Sortierungen oder binäre Sortierungen werden unterstützt. Weitere Informationen finden Sie unter How to tell if pushdown occurred.

Pushdown für Parkettdateien

Ab SQL Server 2022 (16.x) hat PolyBase Unterstützung für Parkettdateien eingeführt. SQL Server ist in der Lage, sowohl zeilen- als auch Spaltenausscheidung beim Durchführen von Pushdown mit Parkett durchzuführen. Beim Arbeiten mit Parkettdateien können die folgenden Vorgänge gedrückt werden:

  • Binäre Vergleichsoperatoren (>=>, =, <<) für numerische, Datums- und Uhrzeitwerte.
  • Kombination von Vergleichsoperatoren ( AND , = UND <> , AND <= , <= UND = UND >= ). ><>
  • In Listenfilter (Col1 = val1 OR col1 = val2 OR vol1 = val3).
  • IS NOT NULL over column.

Das Vorhandensein der folgenden Verhindert den Pushdown für Parkettdateien:

  • Virtuelle Spalten.
  • Spaltenvergleich.
  • Parametertypkonvertierung.

Unterstützte Datentypen

  • bit
  • TinyInt
  • SmallInt
  • BigInt
  • Real
  • Float
  • VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
  • NVARCHAR (Bin2Collation, BinCollation)
  • Binary
  • DateTime2 (Standard- und 7-stellige Genauigkeit)
  • Datum
  • Zeit (Standard- und 7-stellige Genauigkeit)
  • Numerischen*

* Wird unterstützt, wenn die Parameterskala am Spaltenmaßstab ausgerichtet ist oder wenn der Parameter explizit in eine Dezimalzahl umgegossen wird.

Datentypen, die Denkrückung von Parkett verhindern

  • Zahlung
  • SmallMoney
  • Datetime
  • SmallDateTime

Beispiele

Weitergabe erzwingen

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

Weitergabe deaktivieren

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

Nächste Schritte

Siehe auch