Dela via

Pushdown-beräkningar i PolyBase

gäller för: SQL Server 2016 (13.x) och senare versioner

Pushdown-beräkningen förbättrar prestandan för förfrågningar på externa datakällor. Från och med SQL Server 2016 (13.x) var pushdown-beräkningar tillgängliga för hadoop-externa datakällor. SQL Server 2019 (15.x) introducerade pushdown-beräkningar för andra typer av externa datakällor.

Anmärkning

För att avgöra om PolyBase-pushdown-beräkning gynnar din fråga, se Så här ser du om extern pushdown har inträffat.

Aktivera pushdown-beräkning

Följande artiklar innehåller information om hur du konfigurerar pushdown-beräkning för specifika typer av externa datakällor:

Den här tabellen sammanfattar stöd för pushdown-beräkning på olika externa datakällor:

Datakälla Ansluter sig Prognoser Aggregations Filterar Statistics
ODBC (allmän) 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 Några** Några** Yes
Azure Blob Storage Nej Nej Nej Nej Yes

* Stöd för Azure Cosmos DB-pushdown aktiveras via Azure Cosmos DB API för MongoDB.

** Se Pushdown-beräkning och Hadoop-leverantörer.

Pushdown-stöd för aggregeringar och filter för MongoDB ODBC-anslutningsappen för SQL Server 2019 introducerades med SQL Server 2019 CU18.

+ Oracle stöder pushdown för kopplingar, men du kan behöva skapa statistik för kopplingskolumnerna för att uppnå pushdown.

Anmärkning

Pushdown-beräkningen kan blockeras av viss T-SQL-syntax. Mer information finns i Syntax som förhindrar nedtryckning.

Pushdown-beräkning och Hadoop-leverantörer

PolyBase stöder för närvarande två Hadoop-leverantörer: Hortonworks Data Platform (HDP) och Cloudera Distributed Hadoop (CDH). Det finns inga skillnader mellan de två leverantörerna när det gäller pushdown-beräkning.

Om du vill använda beräkningens pushdown-funktioner med Hadoop måste hadoop-målklustret ha kärnkomponenterna HDFS, YARN och MapReduce, med jobbhistorikservern aktiverad. PolyBase skickar pushdown-frågan via MapReduce och hämtar status från jobbhistorikservern. Utan någon av komponenterna misslyckas frågan.

En viss aggregering måste ske när data når SQL Server. Men en del av aggregeringen sker i Hadoop. Den här metoden är vanlig vid beräkning av aggregeringar i massivt parallella bearbetningssystem.

Hadoop-leverantörer stöder följande sammansättningar och filter.

Aggregations Filter (binär jämförelse)
Count_Big NotEqual
Summa Mindre än
Avg LessOrEqual
Max StörreEllerLikaMed
Min GreaterThan
Approx_Count_Distinct är
IsNot

Nyckelfördelaktiga scenarier för pushdownberäkningar

Med PolyBase pushdown-beräkning kan du delegera beräkningsuppgifter till externa datakällor. Detta minskar arbetsbelastningen på SQL Server-instansen och kan avsevärt förbättra prestandan.

SQL Server kan skicka kopplingar, projektioner, aggregeringar och filter till externa datakällor, dra nytta av fjärrberäkning och begränsa data som skickas via nätverket.

Gå med i pushdown

PolyBase kan underlätta pushdown för kopplingsoperatorn när du ansluter två externa tabeller till samma externa datakälla, vilket avsevärt förbättrar prestandan.

När den externa datakällan utför kopplingen minskar mängden dataförflyttning och förbättrar frågeprestandan. Utan join pushdown måste SQL Server hämta data från båda tabellerna lokalt in i tempdb och sedan utföra kopplingen.

När det gäller distribuerade kopplingar (koppla en lokal tabell till en extern tabell), såvida inte filtret gäller för den anslutna externa tabellen, måste SQL Server ta med alla data från den externa tabellen lokalt för tempdb att utföra kopplingsåtgärden. Följande fråga har till exempel ingen filtrering av villkoret för extern tabellkoppling, vilket resulterar i läsning av alla data från den externa tabellen.

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

Eftersom kopplingen använder kolumnen i E.id den externa tabellen kan SQL Server, när du lägger till ett filtervillkor i kolumnen, trycka ned filtret, vilket minskar antalet rader som lästs från den externa tabellen.

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

Välj en delmängd av rader

Använd pushdown för att förbättra prestanda för en sökfråga som väljer en delmängd rader från en extern tabell.

I det här exemplet initierar SQL Server ett map-reduce-jobb för att hämta de rader som matchar predikatet customer.account_balance < 200000 på Hadoop. Eftersom frågan kan slutföras utan att genomsöka alla rader i tabellen kopieras endast de rader som uppfyller predikatvillkoren till SQL Server. Detta sparar betydande tid och kräver mindre tillfälligt lagringsutrymme när antalet kundsaldon < 200000 är litet jämfört med antalet kunder med kontosaldon >= 200000.

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

Välj en delmängd av kolumner

Använd predikatpushdown för att förbättra prestandan för en fråga som väljer ut en del av kolumnerna från en extern tabell.

I den här frågan initierar SQL Server ett map-reduce-jobb för att förbearbeta hadoop-avgränsad textfil så att endast data för de två kolumnerna, customer.name och customer.zip_code, kopieras till SQL Server.

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

Pushdown för grundläggande uttryck och operatorer

SQL Server tillåter dessa grundläggande uttryck och operatorer för predikatoptimering:

  • Binära jämförelseoperatorer (<, >, =, !=, <>, >=, <=) för numeriska, datum- och tidsvärden.
  • Aritmetiska operatorer (+, -, *, /, %).
  • Logiska operatorer (AND, OR).
  • Unära operatorer (NOT, IS NULL, IS NOT NULL).

Operatorerna BETWEEN, NOT, INoch LIKE kan tryckas ned beroende på hur frågeoptimeraren skriver om operatoruttrycken som en serie instruktioner med hjälp av grundläggande relationsoperatorer.

Frågan i det här exemplet har flera predikat som kan skickas ned till Hadoop. SQL Server kan skicka map-reduce-jobb till Hadoop för att utföra predikatet customer.account_balance <= 200000. Uttrycket BETWEEN 92656 AND 92677 består också av binära och logiska åtgärder som kan skickas till Hadoop. Det logiska AND i customer.account_balance AND customer.zipcode är ett slutligt uttryck.

Med den här kombinationen av predikat kan map-reduce-jobben utföra alla WHERE-satser. Endast de data som uppfyller SELECT kriterierna kopieras tillbaka till SQL Server.

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

Funktioner som stöds för pushdown

SQL Server tillåter dessa funktioner för predikat-pushdown:

Strängfunktioner:

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

Matematiska funktioner:

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

Allmänna funktioner:

  • COALESCE *
  • NULLIF

* Användning med COLLATE kan förhindra pushdown i vissa scenarier. Mer information finns i Sorteringskonflikt.

Datum- och tidsfunktioner:

  • DATEADD
  • DATEDIFF
  • DATEPART

Syntax som förhindrar nedtryckning

Dessa T-SQL-funktioner eller syntaxobjekt förhindrar pushdown-beräkning:

  • 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-stöd för syntaxen FORMAT och TRIM introducerades i SQL Server 2019 (15.x) CU10.

Filtersats med variabel

När du anger en variabel i en filtersats skjuter SQL Server inte ned filtersatsen som standard. Följande fråga trycker till exempel inte ned filtersatsen:

DECLARE @BusinessEntityID INT

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

För att aktivera push-nedtryckning av variabeln, aktivera funktionen för snabbkorrigeringar av frågeoptimeraren med hjälp av någon av dessa metoder:

  • Instansnivå: Aktivera spårningsflagga 4199 som startparameter för instansen.
  • Databasnivå: I sammanhanget av databasen som har PolyBase externa objekt, kör ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON.
  • Frågenivå: Använd frågetips OPTION (QUERYTRACEON 4199) eller OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES')).

Den här begränsningen gäller för körning av sp_executesql och för vissa funktioner i filterklausulen.

SQL Server 2019 CU5 introducerade först möjligheten att push-överföra variabeln.

Mer information finns i sp_executesql.

Sorteringskonflikt

Pushdown kanske inte fungerar med data som har olika kollationer. Operatorer som COLLATE kan också störa resultatet. SQL Server stöder lika sortering eller binär sortering. Mer information finns i Så här anger du om en extern nedtryckning har inträffat.

Pushdown för parquetfiler

Från och med SQL Server 2022 (16.x) introducerade PolyBase stöd för parquet-filer. SQL Server kan utföra både rad- och kolumneliminering när pushdown utförs med parquet-formatet.

Externa datakällor som stöds

Parquet-pushdown stöds för följande externa datakällor:

  • S3-kompatibel objektlagring
  • Azure Blob Storage-lagringstjänst
  • Azure Data Lake Storage Gen2

Mer information om konfiguration finns i:

Pushdown-åtgärder

SQL Server kan delegera dessa åtgärder med Parquet-filer:

  • Binära jämförelseoperatorer (>, >=, <=, <) för numeriska värden, datum- och tidsvärden.
  • Kombination av jämförelseoperatorer (> AND <, >= AND <, > AND <=, <= AND >=).
  • I listfiltret (col1 = val1 OR col1 = val2 OR col1 = val3).
  • ÄR INTE NULL över en kolumn.

Dessa objekt förhindrar pushdown för parquet-filer:

  • Virtuella kolumner.
  • Kolumnjämförelse.
  • Konvertering av parametertyp.

Datatyper som stöds

  • bit
  • tinyint
  • smallint
  • bigint
  • riktiga
  • float
  • varchar (Bin2Collation, CodePageConversion, BinCollation)
  • nvarchar (Bin2Collation, BinCollation)
  • binary
  • datetime2 (standard och 7-siffrig precision)
  • date
  • tid (standard och 7-siffrig precision)
  • Numerisk *

* Stöds när parameterskalan överensstämmer med kolumnskalan, eller när parametern uttryckligen omvandlas till decimal.

Datatyper som förhindrar parquet-pushdown

  • pengar
  • småpengar
  • datetime
  • smalldatetime

Partitionseliminering med mappstrukturer

PolyBase kan använda mappstrukturer för partitionseliminering, vilket minskar mängden data som genomsöks under frågor. När du organiserar parquet-filer i hierarkiska mappar (till exempel efter år, månad eller andra partitioneringsnycklar) kan PolyBase hoppa över hela mappar som inte matchar dina frågepredikat.

Om du till exempel strukturerar dina data som:

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

Du kan köra frågor mot specifika partitioner med jokertecken på OPENROWSET eller externa tabellplatser:

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

För dynamisk mappeliminering utför du en sökning på en bredare mappsökväg och använder filepath() villkor för att eliminera partitioner vid körning.

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);

Den här metoden kombinerar partitionseliminering på mappnivå med parquet-pushdown på filnivå för optimal frågeprestanda. En komplett handledning för hur du ställer frågor mot parquet-filer med mappmönster finns att se i Virtualisera parquet-fil i en S3-kompatibel objektlagring med PolyBase.

Examples

Framtvinga nedtryckning

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

Avaktivera pushdown

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

Relaterat innehåll

  • Last updated on