Leküldéses számítások a PolyBase-ben

A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók

A továbbított számítás javítja a külső adatforrásokon lévő lekérdezések teljesítményét. Az SQL Server 2016-tól (13.x verzió) kezdődően az adat-továbbításos számítások elérhetők voltak a külső Hadoop adatforrásokhoz. Az SQL Server 2019 (15.x) bevezette a leküldéses számításokat más típusú külső adatforrások esetében.

Megjegyzés:

Annak megállapításához, hogy a PolyBase leküldéses számítása hasznos-e a lekérdezésben, olvassa el a Hogyan állapíthatja meg, hogy történt-e külső leküldés.

Leküldéses számítások engedélyezése

Az alábbi cikkek a leküldéses számítások konfigurálásával kapcsolatos információkat tartalmaznak bizonyos külső adatforrástípusokhoz:

• Pushdown számítás engedélyezése a Hadoopban
• A PolyBase konfigurálása külső adatokhoz való hozzáféréshez az Oracle-ben
• A PolyBase konfigurálása külső adatok eléréséhez a Teradata-ban
• A PolyBase konfigurálása külső adatok eléréséhez a MongoDB-ben
• A PolyBase konfigurálása külső adatok ODBC általános típusokkal való eléréséhez
• A PolyBase konfigurálása külső adatok sql serveren való eléréséhez

Ez a táblázat a pushdown számítások támogatását foglalja össze különféle külső adatforrások esetében.

Adatforrás	Joins	Előrejelzések	Összesítések	Szűrők	statisztika
Általános ODBC	Igen	Igen	Igen	Igen	Igen
Oracle	Igen+	Igen	Igen	Igen	Igen
SQL Server	Igen	Igen	Igen	Igen	Igen
Teradata	Igen	Igen	Igen	Igen	Igen
MongoDB*	No	Igen	Igen***	Igen***	Igen
Hadoop	No	Igen	Néhány**	Néhány**	Igen
Azure Blob-tároló	Nem	Nem	Nem	Nem	Igen

* Az Azure Cosmos DB leküldéses támogatása a MongoDB-hez készült Azure Cosmos DB API-val engedélyezve van.

** Lásd a Pushdown-számításokat és a Hadoop-szolgáltatókat.

Az SQL Server 2019-hez készült MongoDB ODBC-összekötő összesítéseinek és szűrőinek leküldéses támogatása az SQL Server 2019 CU18-ban jelent meg.

+ Az Oracle támogatja az illesztések leküldését, de előfordulhat, hogy a leküldés eléréséhez statisztikákat kell létrehoznia az illesztési oszlopokról.

Megjegyzés:

A leküldéses számítást blokkolhatja néhány T-SQL-szintaxis. További információért tekintse meg a leküldést megakadályozó szintaxist.

Leküldéses feldolgozás és Hadoop szolgáltatók

A PolyBase jelenleg két Hadoop-szolgáltatót támogat: a Hortonworks Data Platformot (HDP) és a Cloudera Distributed Hadoopot (CDH). A pushdown számítás szempontjából nincs különbség a két szolgáltató között.

Ha a számítási teheráthelyezési funkciót a Hadooppal szeretné használni, a cél Hadoop-fürtnek rendelkeznie kell a következő alapvető összetevőkkel: HDFS, YARN és MapReduce, valamint a feladatelőzmény-kiszolgálónak engedélyezettnek kell lennie. A PolyBase a MapReduce-on keresztül küldi el az átfuttatott lekérdezést, és lekéri az állapotot a feladattörténet-kiszolgálóról. Bármelyik összetevő nélkül a lekérdezés meghiúsul.

Az adatok SQL Serverre való elérése után bizonyos összesítésnek kell történnie. Az összesítés egy része azonban a Hadoopban történik. Ez a módszer gyakori a masszívan párhuzamos feldolgozási rendszerekben az összesítések számításában.

A Hadoop-szolgáltatók az alábbi összesítéseket és szűrőket támogatják.

Összesítések	Szűrők (bináris összehasonlítás)
Count_Big	Nem egyenlő
Összeg	Kevesebb mint
Átlag	Kisebb vagy egyenlő
Max	Nagyobb vagy egyenlő
Min	GreaterThan
Approx_Count_Distinct	Van
	NemEz

A "pushdown computation" legfontosabb hasznos forgatókönyvei

A PolyBase leküldéses számítás funkciójával számítási feladatokat delegálhat külső adatforrásokhoz. Ez csökkenti az SQL Server-példány számítási feladatait, és jelentősen javíthatja a teljesítményt.

Az SQL Server leküldheti az illesztéseket, előrejelzéseket, összesítéseket és szűrőket külső adatforrásokra, kihasználva a távoli számítás előnyeit, és korlátozhatja a hálózaton keresztül küldött adatokat.

Csatlakozás leküldéshez

A PolyBase lehetővé teszi az illesztési operátor leküldését, amikor két külső táblát csatlakoztat ugyanazon a külső adatforráson, ami jelentősen javítja a teljesítményt.

Amikor a külső adatforrás végrehajtja az illesztéseket, csökkenti az adatáthelyezés mennyiségét, és javítja a lekérdezési teljesítményt. Illesztés leküldésének hiányában az SQL Servernek mindkét táblából be kell vinnie az adatokat helyileg a tempdb-ba, majd végre kell hajtania az illesztést.

Elosztott illesztések (helyi tábla külső táblához való csatlakoztatása) esetén, kivéve, ha a szűrő az összekapcsolt külső táblára vonatkozik, az SQL Servernek helyileg tempdb kell bevinnie a külső táblából származó összes adatot az illesztési művelet végrehajtásához. Az alábbi lekérdezésben például nincs szűrés a külső táblaillesztési feltételre, ami a külső tábla összes adatának beolvasását eredményezi.

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

Mivel az illesztés a E.id külső tábla oszlopát használja, amikor szűrőfeltételt ad hozzá az oszlophoz, az SQL Server le tudja küldeni a szűrőt, csökkentve a külső táblából beolvasott sorok számát.

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

Sorok részhalmazának kijelölése

Predikátumleküldéssel javíthatja egy olyan lekérdezés teljesítményét, amely egy külső tábla sorainak egy részhalmazát választja ki.

Ebben a példában az SQL Server egy térkép-csökkentési feladatot kezdeményez a Hadoop-predikátumnak customer.account_balance < 200000 megfelelő sorok lekéréséhez. Mivel a lekérdezés sikeresen befejeződhet a tábla összes sorának vizsgálata nélkül, a rendszer csak a predikátumi feltételeknek megfelelő sorokat másolja az SQL Serverre. Ez jelentős időt takarít meg, és kevesebb ideiglenes tárterületet igényel, ha a 200000 ügyfélegyenlegek < száma kicsi a számlaegyenleggel >rendelkező ügyfelek számához képest = 200000.

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

Oszlopok részhalmazának kijelölése

Predikátumleküldéssel javíthatja a teljesítményt egy olyan lekérdezés esetében, amely egy külső tábla oszlopainak egy részhalmazát választja ki.

Ebben a lekérdezésben az SQL Server egy MapReduce feladatot kezdeményez, hogy a Hadoop által tagolt szövegfájlt előfeldolgozza, így csak a customer.name és customer.zip_code oszlopok adatai kerülnek átmásolásra az SQL Serverbe.

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

Leküldés az alapszintű kifejezésekhez és operátorokhoz

Az SQL Server az alábbi alapkifejezéseket és operátorokat teszi lehetővé a predikátumleküldéshez:

• Bináris operátorok az összehasonlításhoz (<, >, =, !=, <>, >=, <=) numerikus, dátum- és időértékekhez.
• Számtani operátorok (+, -, *, /, %).
• Logikai operátorok (AND, OR).
• Unary operátorok (NOT, IS NULL, IS NOT NULL).

Az operátorok BETWEEN, NOT, IN és LIKE leküldhetők attól függően, hogy a lekérdezésoptimalizáló hogyan írja át az operátorkifejezéseket utasítássorozatként alapvető relációs operátorok használatával.

A példában szereplő lekérdezés több predikátumot is tartalmazhat, amelyeket le lehet küldeni a Hadoopba. Az SQL Server le tudja küldeni a térkép-csökkentési feladatokat a Hadoopba a predikátum customer.account_balance <= 200000végrehajtásához. A kifejezés BETWEEN 92656 AND 92677 bináris és logikai műveletekből is áll, amelyek leküldhetők a Hadoopba. A logikai AND in customer.account_balance AND customer.zipcode egy végső kifejezés.

A predikátumok ezen kombinációját figyelembe véve a térkép-csökkentési feladatok az összes WHERE záradékot végrehajthatják. A rendszer csak a SELECT feltételeknek megfelelő adatokat másolja vissza az SQL Serverre.

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

Támogatott függvények leküldéshez

Az SQL Server a következő függvényeket teszi lehetővé a predikátum leküldéséhez:

Karakterlánc függvények:

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

Matematikai függvények:

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

Általános funkciók:

• COALESCE *
• NULLIF

* A COLLATE használata bizonyos esetekben megakadályozhatja a leküldést. További információért lásd: Rendezési ütközés.

Dátum- és időfüggvények:

• DATEADD
• DATEDIFF
• DATEPART

Szintaxis, amely megakadályozza a pushdown-t

Ezek a T-SQL-függvények vagy szintaxiselemek megakadályozzák a leküldéses feldolgozást.

• 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

Az SQL Server 2019 (15.x) CU10-ben bevezettük a leküldéses támogatást a FORMAT és TRIM szintaxis számára.

Szűrő feltétel változóval

Ha változót ad meg egy szűrőzáradékban, az SQL Server alapértelmezés szerint nem küldi le a szűrő záradékot. A következő lekérdezés például nem küldi le a szűrőzáradékot:

DECLARE @BusinessEntityID INT

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

A változó leküldésének engedélyezéséhez engedélyezze a lekérdezés-optimalizáló gyorsjavítások funkcióját az alábbi módszerek egyikének alkalmazásával:

• Példányszint: A 4199-es nyomkövetési jelző engedélyezése a példány indítási paramétereként.
• Adatbázisszint: A PolyBase külső objektumokat tartalmazó adatbázis kontextusában hajtsa végre a elemet ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON.
• Lekérdezési szint: Lekérdezési tipp OPTION (QUERYTRACEON 4199) vagy OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES')).

Ez a korlátozás a sp_executesql végrehajtására és a szűrőzáradék bizonyos függvényeire vonatkozik.

Az SQL Server 2019 CU5 először bevezette a változó leküldésének lehetőségét.

További információ: sp_executesql.

Rendezési ütközés

Előfordulhat, hogy a leküldés nem működik különböző rendezésű adatokkal. A hasonló COLLATE operátorok is zavarhatják az eredményt. Az SQL Server támogatja az egyenlő rendezéseket és a bináris rendezéseket. További információért lásd: Hogyan állapíthatja meg, hogy külső leküldés történt-e.

Parquet-fájlok optimalizálása

Az SQL Server 2022-től kezdve (16.x) a PolyBase bevezette a parquet-fájlok támogatását. Az SQL Server képes sor- és oszlopeliminálást végrehajtani, miközben a leküldést a Parquet formátummal hajtja végre.

Támogatott külső adatforrások

A Parquet pushdown támogatott a következő külső adatforrásokhoz:

• S3-kompatibilis objektumtároló
• Azure Blob Storage
• Azure Data Lake Storage Gen2

A konfiguráció részleteiért lásd:

• A PolyBase konfigurálása külső adatok eléréséhez az S3-kompatibilis objektumtárolóban
• Parquet-fájl virtualizálása S3-kompatibilis objektumtárolóban PolyBase használatával

Leküldéses műveletek

Az SQL Server le tudja küldeni ezeket a műveleteket parquet-fájlokkal:

• Bináris összehasonlító operátorok (>, >=, <=, <) numerikus, dátum- és időértékekhez.
• Összehasonlító operátorok kombinációja (> AND <, >= AND <, > AND <=, <= AND >=).
• Listaszűrőben (col1 = val1 VAGY col1 = val2 VAGY vol1 = val3).
• NEM NULL érték egy oszlopon keresztül.

A pushdown megakadályozása érdekében a következő elemek szükségesek a parquet-fájlok esetében:

• Virtuális oszlopok.
• Oszlop-összehasonlítás.
• Paramétertípus konvertálása.

Támogatott adattípusok

• bit
• tinyint
• smallint
• bigint
• valódi
• float
• varchar (Bin2Collation, CodePageConversion, BinCollation)
• nvarchar (Bin2Collation, BinCollation)
• binary
• datetime2 (alapértelmezett és 7 jegyű pontosság)
• date
• idő (alapértelmezett és 7 jegyű pontosság)
• Numerikus *

* Támogatott, ha a paraméterskálázás az oszlopmérethez igazodik, vagy ha a paraméter explicit módon tizedesjelre van adva.

Adattípusok, amelyek megakadályozzák a Parquet szűkítését

• pénz
• smallmoney
• datetime
• smalldatetime

Partícióeltörlés mappastruktúrákkal

A PolyBase mappastruktúrákat használhat a partíciók eltávolításához, csökkentve a lekérdezések során beolvasott adatok mennyiségét. Ha hierarchikus mappákba (például év, hónap vagy egyéb particionálási kulcsok szerint) rendez parquet fájlokat, a PolyBase kihagyhatja a lekérdezési predikátumokkal nem egyező teljes mappákat.

Ha például a következőképpen strukturálja az adatokat:

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

Adott partíciók lekérdezése helyettesítő karakterekkel OPENROWSET vagy külső táblahelyekkel történik:

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

A dinamikus mappák eltávolításához lekérdezhet egy kiterjedtebb mappa elérési útját, és filepath() predikátumokat használhat a partíciók futásidejű eltávolításához.

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

Ez a megközelítés egyesíti a mappaszintű partíciók eltávolítását a parquet fájlszintű optimalizálással az optimális lekérdezési teljesítmény érdekében. A mappamintákkal rendelkező parquet-fájlok lekérdezéséről szóló teljes oktatóanyagért lásd: Parquet-fájl virtualizálása egy S3-kompatibilis objektumtárolóban a PolyBase-lel.

Példák

Leküldés kényszerítése

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

Pushdown letiltása

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

Kapcsolódó tartalom

• Adatvirtualizálás a PolyBase használatával az SQL Serveren
• Hogyan ismerheti fel, hogy külső nyomás-leadás történt-e
• A PolyBase konfigurálása külső adatok eléréséhez az S3-kompatibilis objektumtárolóban
• Parquet-fájl virtualizálása S3-kompatibilis objektumtárolóban PolyBase használatával