Pushdown-berekeningen in PolyBase

Van toepassing op: SQL Server 2016 (13.x) en latere versies

Pushdownberekening verbetert de prestaties van query's op externe gegevensbronnen. Vanaf SQL Server 2016 (13.x) waren pushdownberekeningen beschikbaar voor externe Hadoop-gegevensbronnen. SQL Server 2019 (15.x) heeft pushdownberekeningen geïntroduceerd voor andere typen externe gegevensbronnen.

Pushdownberekening inschakelen

De volgende artikelen bevatten informatie over het configureren van pushdownberekeningen voor specifieke typen externe gegevensbronnen:

• Pushdownberekening in Hadoop inschakelen
• PolyBase configureren voor toegang tot externe gegevens in Oracle
• PolyBase configureren voor toegang tot externe gegevens in Teradata
• PolyBase configureren voor toegang tot externe gegevens in MongoDB
• PolyBase configureren voor toegang tot externe gegevens met algemene ODBC-typen
• PolyBase configureren voor toegang tot externe gegevens in SQL Server

Deze tabel bevat een overzicht van ondersteuning voor pushdownberekeningen voor verschillende externe gegevensbronnen:

* Pushdown-ondersteuning voor Azure Cosmos DB is ingeschakeld via de Azure Cosmos DB-API voor MongoDB.

** Zie Pushdown-berekeningen en Hadoop-providers.

Pushdown-ondersteuning voor aggregaties en filters voor de MongoDB ODBC-connector voor SQL Server 2019 is geïntroduceerd met SQL Server 2019 CU18.

+ Oracle ondersteunt pushdown voor joins; maar mogelijk moet u statistieken maken voor de joinkolommen om gebruik te maken van pushdown.

Pushdown-berekeningen en Hadoop-providers

PolyBase ondersteunt momenteel twee Hadoop-providers: Hortonworks Data Platform (HDP) en Cloudera Distributed Hadoop (CDH). Er zijn geen verschillen tussen de twee functies in termen van pushdownberekeningen.

Als u de pushdown-functionaliteit voor berekeningen met Hadoop wilt gebruiken, moet het Hadoop-doelcluster de kernonderdelen van HDFS, YARN en MapReduce hebben, waarbij de taakgeschiedenisserver is ingeschakeld. PolyBase verzendt de pushdownquery via MapReduce en haalt de status op van de taakgeschiedenisserver. Zonder een van beide onderdelen mislukt de query.

Sommige aggregatie moet plaatsvinden nadat de gegevens SQL Server hebben bereikt. Maar een deel van de aggregatie vindt plaats in Hadoop. Deze methode wordt vaak gebruikt voor gegevensaggregaties in massaal parallelle verwerkingssystemen.

Hadoop-providers ondersteunen de volgende aggregaties en filters.

Belangrijke nuttige scenario's voor pushdownberekening

Met polyBase-pushdownberekening kunt u rekentaken delegeren aan externe gegevensbronnen. Dit vermindert de workload op het SQL Server-exemplaar en kan de prestaties aanzienlijk verbeteren.

SQL Server kan joins, projecties, aggregaties en filters naar externe gegevensbronnen pushen om te profiteren van externe berekeningen en de gegevens die via het netwerk worden verzonden, te beperken.

Pushdown van samenvoegingen

In veel gevallen kan PolyBase de pushdown van de join-operator vergemakkelijken voor de join van twee externe tabellen in dezelfde externe gegevensbron, waardoor de prestaties aanzienlijk worden verbeterd.

Als de join kan worden uitgevoerd bij de externe gegevensbron, vermindert dit de hoeveelheid gegevensverplaatsing en verbetert de prestaties van de query. Zonder joinpushdown moeten de gegevens uit de tabellen die moeten worden gekoppeld, lokaal in tempdb worden gebracht en vervolgens worden samengevoegd.

In het geval van gedistribueerde joins (een lokale tabel toevoegen aan een externe tabel), tenzij er een filter is op de gekoppelde externe tabel, moeten alle gegevens in de externe tabel lokaal tempdb worden overgebracht om de joinbewerking uit te voeren. De volgende query heeft bijvoorbeeld geen filter op de voorwaarde voor het samenvoegen van externe tabellen, waardoor alle gegevens uit de externe tabel worden gelezen.

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

Omdat de join zich in E.id de kolom van de externe tabel bevindt en er een filtervoorwaarde aan die kolom wordt toegevoegd, kan het filter omlaag worden gepusht, waardoor het aantal rijen dat uit de externe tabel wordt gelezen, wordt verminderd.

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

Een subset met rijen selecteren

Gebruik predicaatpushdown om de prestaties van een query te verbeteren die een subset van rijen uit een externe tabel selecteert.

In dit voorbeeld start SQL Server een map-reduce-taak om de rijen op te halen die overeenkomen met het predicaat customer.account_balance < 200000 op Hadoop. Omdat de query kan worden voltooid zonder alle rijen in de tabel te scannen, worden alleen de rijen die voldoen aan de predicaatcriteria gekopieerd naar SQL Server. Dit bespaart aanzienlijke tijd en vereist minder tijdelijke opslagruimte wanneer het aantal klantsaldi < 200000 klein is in vergelijking met het aantal klanten met rekeningsaldi >= 200000.

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

Een subset van kolommen selecteren

Gebruik predicaatpushdown om de prestaties voor een query te verbeteren die een subset kolommen uit een externe tabel selecteert.

In deze query initieert SQL Server een map-reduce-taak om het Hadoop-bestand met scheidingstekens voor te verwerken, zodat alleen de gegevens voor de twee kolommen customer.name en customer.zip_code worden gekopieerd naar SQL Server.

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

Pushdown voor basale expressies en operatoren

SQL Server staat de volgende basisexpressies en operators toe voor predicate pushdown.

• Binaire vergelijkingsoperatoren (<, >, =, !=, <>, , ) >=<=voor numerieke, datum- en tijdwaarden.
• Rekenkundige operatoren (+, -, *, /, ). %
• Logische operators (AND, OR).
• Unaire operators (NOT, IS NULL, IS NOT NULL).

De operatorsBETWEEN, NOTen INLIKE kunnen omlaag worden geschoven. Het werkelijke gedrag is afhankelijk van hoe de queryoptimalisatie de operatorexpressies herschrijft als een reeks instructies die gebruikmaken van relationele basisoperators.

De query in dit voorbeeld heeft meerdere predicaten die naar Hadoop kunnen worden doorgestuurd. SQL Server kan map-reduce taken naar Hadoop verplaatsen om het predicaat customer.account_balance <= 200000 uit te voeren. De expressie BETWEEN 92656 AND 92677 bestaat ook uit binaire en logische bewerkingen die naar Hadoop kunnen worden gepusht. De logische AND in customer.account_balance AND customer.zipcode is een definitieve expressie.

Gezien deze combinatie van predicaten kunnen de map-reduce taken de volledige WHERE-clausule uitvoeren. Alleen de gegevens die voldoen aan de SELECT criteria, worden terug gekopieerd naar SQL Server.

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

Ondersteunde functies voor pushdown

SQL Server staat de volgende functies toe voor predicaatpushdown.

Stringfuncties

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

Wiskundige functies

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

Algemene functies

• COALESCE *
• NULLIF

* Het gebruik met COLLATE kan in sommige scenario's pushdown voorkomen. Zie Collatieconflict voor meer informatie.

Datum- en tijdfuncties

• DATEADD
• DATEDIFF
• DATEPART

Syntaxis die pushdown voorkomt

Met de volgende T-SQL-functies of -syntaxis voorkomt u pushdownberekeningen:

• 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

Pushdown-ondersteuning voor de FORMAT en TRIM syntaxis is geïntroduceerd in SQL Server 2019 (15.x) CU10.

Filtercomponent met variabele

Wanneer u een variabele opgeeft in een filtercomponent, voorkomt u standaard pushdown van de filtercomponent. Als u bijvoorbeeld de volgende query uitvoert, wordt de filtercomponent niet omlaag gepusht:

DECLARE @BusinessEntityID INT

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

Als u het pushdown van de variabele wilt bereiken, moet u de hotfixes functionaliteit voor queryoptimalisatie inschakelen. Dit kan op een van de volgende manieren:

• Instantieniveau: traceringsvlag 4199 inschakelen als opstartparameter voor het exemplaar
• Databaseniveau: Voer in de context van de database met de externe PolyBase-objecten uit ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON
• Queryniveau: gebruik query hint OPTION (QUERYTRACEON 4199) of OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

Deze beperking geldt voor de uitvoering van sp_executesql. De beperking geldt ook voor het gebruik van bepaalde functies in de filtercomponent.

De functionaliteit om de variabele te verplaatsen is voor het eerst geïntroduceerd in SQL Server 2019 CU5.

Sorteringsconflict

Pushdown is misschien niet mogelijk met gegevens met verschillende sorteringen. Operators zoals COLLATE kunnen ook de uitkomst verstoren. Gelijke sorteringen of binaire sorteringen worden ondersteund. Zie Hoe u kunt zien of pushdown heeft plaatsgevonden voor meer informatie.

Pushdown voor Parquet-bestanden

Vanaf SQL Server 2022 (16.x) heeft PolyBase ondersteuning geïntroduceerd voor Parquet-bestanden. SQL Server kan zowel rij- als kolomeliminatie uitvoeren bij het uitvoeren van pushdown met Parquet. Met parquet-bestanden kunnen de volgende bewerkingen omlaag worden gepusht:

• Binaire vergelijkingsoperatoren (>, >=, <=, <) voor numerieke, datum- en tijdwaarden.
• Combinatie van vergelijkingsoperatoren (> EN <, >= EN <, > EN <= , <= EN >= ).
• In lijstfilter (col1 = val1 OR col1 = val2 OR vol1 = val3).
• IS NIET NULL voor kolom.

Aanwezigheid van het volgende voorkomt pushdown voor Parquet-bestanden:

• Virtuele kolommen.
• Kolomvergelijking.
• Conversie van parametertype.

Ondersteunde gegevenstypen

• Bit
• TinyInt
• SmallInt
• BigInt
• Echt
• zweven
• VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
• NVARCHAR (Bin2Collation, BinCollation)
• Binary
• DateTime2 (standaard en 7-cijferige precisie)
• Datum
• Tijd (standaard en 7-cijferige precisie)
• Numerieke*

* Ondersteund wanneer de parameterschaal wordt uitgelijnd met kolomschaal of wanneer de parameter expliciet wordt omgezet in decimalen.

Gegevenstypen die parquet-pushdown voorkomen

• Geld
• SmallMoney
• DateTime
• SmallDateTime

Voorbeelden

Pushdown afdwingen

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

Pushdown uitschakelen

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

Verwante inhoud

• Zie Inleiding tot gegevensvirtualisatie met PolyBase voor meer informatie over PolyBase
• Hoe kunt u zien of er een externe pushdown is opgetreden