Share via

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.

Opmerking

Als u wilt bepalen of PolyBase-pushdownberekeningen voordeel bieden voor uw query, raadpleegt u Hoe u kunt zien of er een externe pushdown heeft plaatsgevonden.

Pushdownberekening inschakelen

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

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

Gegevensbron [Samenvoegingen] Prognoses Aggregations Filteren Statistics
Algemene ODBC Yes Yes Yes Yes Yes
Oracle Ja+ Yes Yes Yes Yes
SQL Server Yes Yes Yes Yes Yes
Teradata Yes Yes Yes Yes Yes
MongoDB* No Yes Ja*** Ja*** Yes
Hadoop No Yes Sommige** Sommige** Yes
Azure Blob-opslagruimte Nee. Nee. Nee. Nee. Yes

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

Opmerking

De pushdown-berekening kan door bepaalde T-SQL-syntaxis worden geblokkeerd. Raadpleeg syntaxis die pushdown voorkomt voor meer informatie.

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 providers in termen van pushdownberekeningen.

Als u de pushdown-functionaliteit voor berekeningen met Hadoop wilt gebruiken, moet het Hadoop-doelcluster beschikken over de kernonderdelen HDFS, YARN en MapReduce, 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.

Aggregations Filters (binaire vergelijking)
Count_Big NotEqual
Som LessThan
Gemiddelde KleinerOfGelijk
Max GroterOfGelijk
Minuut GreaterThan
Approx_Count_Distinct Is
Is Niet

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 en profiteren van externe rekenkracht, waardoor de hoeveelheid gegevens die via het netwerk wordt verzonden, wordt beperkt.

Pushdown samenvoegen

PolyBase kan het pushdown-mechanisme van de joinoperator vergemakkelijken wanneer je twee externe tabellen koppelt die dezelfde externe gegevensbron gebruiken, wat de prestaties aanzienlijk verbetert.

Wanneer de externe gegevensbron de join uitvoert, vermindert deze de hoeveelheid gegevensverplaatsing en verbetert de queryprestaties. Zonder join pushdown moet SQL Server de gegevens uit beide tabellen lokaal ophalen tempdb en vervolgens de join uitvoeren.

In het geval van gedistribueerde joins (een lokale tabel toevoegen aan een externe tabel), tenzij uw filter van toepassing is op de gekoppelde externe tabel, moet SQL Server alle gegevens uit de externe tabel lokaal tempdb overbrengen om de joinbewerking uit te voeren. De volgende query heeft bijvoorbeeld geen filter op de voorwaarde voor het samenvoegen van externe tabellen, wat resulteert in het lezen van alle gegevens uit de externe tabel.

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

Omdat de join gebruikmaakt van de E.id kolom van de externe tabel, kan SQL Server, wanneer u een filtervoorwaarde aan die kolom toevoegt, het filter omlaag pushen, 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 deze basisexpressies en operators voor predicaat-pushdown toe:

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

De operatorsBETWEEN, NOTINen LIKE kunnen omlaag worden gepusht, afhankelijk van hoe de queryoptimalisatie de operatorexpressies herschrijft als een reeks instructies met behulp van eenvoudige relationele operators.

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 deze functies toe voor predicaatpushdown:

Tekenreeksfuncties:

  • 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

Deze T-SQL-functies of syntaxelementen verhinderen pushdown-berekeningen:

  • 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 filtervoorwaarde, wordt de filtervoorwaarde standaard niet door SQL Server gepusht. Met de volgende query wordt bijvoorbeeld de filterclausule niet doorgevoerd:

DECLARE @BusinessEntityID INT

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

Als u pushdown van de variabele wilt inschakelen, schakelt u de hotfixes-functionaliteit voor queryoptimalisatie in met behulp van een van de volgende methoden:

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

Deze beperking is van toepassing op de uitvoering van sp_executesql en op sommige functies in de filtercomponent.

SQL Server 2019 CU5 heeft voor het eerst de mogelijkheid geïntroduceerd om de variabele omlaag te pushen.

Zie sp_executesql voor meer informatie.

Sorteringsconflict

Pushdown werkt mogelijk niet met gegevens met verschillende sorteringen. Operators zoals COLLATE kunnen ook de uitkomst verstoren. SQL Server ondersteunt gelijke sorteringen of binaire sorteringen. Zie Hoe u kunt zien of externe pushdown heeft plaatsgevondenvoor 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.

Ondersteunde externe gegevensbronnen

Parquet-pushdown wordt ondersteund voor de volgende externe gegevensbronnen:

  • S3-compatibele objectopslag
  • Azure Blob Storage (opslagdienst van Azure)
  • Azure Data Lake Storage Gen2

Zie voor configuratiedetails:

Pushdownbewerkingen

SQL Server kan deze bewerkingen doorvoeren met parquet-bestanden:

  • 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 een kolom.

Deze items voorkomen pushdown voor Parquet-bestanden:

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

Ondersteunde gegevenstypen

  • bit
  • tinyint
  • smallint
  • bigint
  • echte
  • float
  • varchar (Bin2Collation, CodePageConversion, BinCollation)
  • nvarchar (Bin2Collation, BinCollation)
  • binary
  • datetime2 (standaard en 7-cijferige precisie)
  • date
  • 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
  • kleingeld
  • datetime
  • smalldatetime

Partitie-verwijdering met mapstructuren

PolyBase kan mapstructuren gebruiken voor het verwijderen van partities, waardoor de hoeveelheid gescande gegevens tijdens query's wordt verminderd. Wanneer u parquet-bestanden in hiërarchische mappen ordent (zoals per jaar, maand of andere partitioneringssleutels), kan PolyBase volledige mappen overslaan die niet overeenkomen met uw querypredicaten.

Als u bijvoorbeeld uw gegevens structureert als:

/data/year=2024/month=01/*.parquet
/data/year=2024/month=02/*.parquet
/data/year=2025/month=01/*.parquet

U kunt query's uitvoeren op specifieke partities met behulp van jokertekens in OPENROWSET of externe tabellocaties:

-- Query only January 2025 data
SELECT *
FROM OPENROWSET(
    BULK '/data/year=2025/month=01/*.parquet',
    DATA_SOURCE = 's3_ds',
    FORMAT = 'PARQUET'
) AS [data];

Voor het verwijderen van dynamische mappen: voer een query uit op een breder mappad en gebruik predicaten met filepath() om partities tijdens runtime te elimineren.

SELECT
    r.filepath(1) AS [year],
    r.filepath(2) AS [month],
    COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK '/data/year=*/month=*/*.parquet',
    DATA_SOURCE = 's3_ds',
    FORMAT = 'PARQUET'
) WITH (
    customer_id INT,
    amount DECIMAL(10, 2)
) AS [r]
WHERE
    r.filepath(1) = '2025'
    AND r.filepath(2) = '01'
GROUP BY
    r.filepath(1),
    r.filepath(2);

Deze benadering combineert partitieverwijdering op mapniveau met pushdown op parquet-bestandsniveau voor optimale queryprestaties. Zie Parquet-bestand virtualiseren in een S3-compatibele objectopslag met PolyBase voor een volledige zelfstudie over het uitvoeren van query's op Parquet-bestanden met mappatronen.

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

  • Last updated on