Sdílet prostřednictvím

Posun výpočtů v PolyBase

platí pro: SQL Server 2016 (13.x) a novější verze

Zpracování s využitím pushdown zlepšuje výkon dotazů na externí zdroje dat. Počínaje SQL Serverem 2016 (13.x) byly pro externí Hadoop zdroje dat k dispozici výpočty přiřazené na nižší úrovni. SQL Server 2019 (15.x) zavedl výpočty pushdownu pro jiné typy externích zdrojů dat.

Poznámka:

Chcete-li zjistit, zda vaše dotazování těží z výpočtů PolyBase pushdown, přečtěte si Jak zjistit, zda došlo k externímu pushdownu.

Povolit počítání pushdown

Následující články obsahují informace o konfiguraci propagace výpočtů pro konkrétní typy externích zdrojů dat:

Tato tabulka shrnuje podporu výpočtů typu pushdown na různých externích zdrojích dat:

Zdroj dat Joins Projekce Aggregations Filtry Statistika
Obecná rozhraní ODBC Ano Ano Ano Ano Ano
Oracle Ano+ Ano Ano Ano Ano
SQL Server Ano Ano Ano Ano Ano
Teradata Ano Ano Ano Ano Ano
MongoDB* Ne Ano Ano*** Ano*** Ano
Hadoop Ne Ano Některé** Některé** Ano
Azure Blob Storage Ne Ne Ne Ne Ano

* Podpora nabízení změn ve službě Azure Cosmos DB je povolená prostřednictvím rozhraní API služby Azure Cosmos DB pro MongoDB.

** Viz výpočetní funkce Pushdown a poskytovatelé Hadoopu.

Podpora nabízení pro agregace a filtry pro konektor MongoDB ODBC pro SQL Server 2019 byla zavedena s SQL Serverem 2019 CU18.

+ Oracle podporuje průchod dolů pro spojení, ale možná budete muset vytvořit statistiky pro spojené sloupce, abyste dosáhli průchodu dolů.

Poznámka:

Výpočet odsdílení změn je možné zablokovat určitou syntaxí T-SQL. Další informace najdete v syntaxi, která brání posunu.

Výpočty s redukcí dat a poskytovatelé Hadoopu

PolyBase aktuálně podporuje dva poskytovatele Hadoop: Hortonworks Data Platform (HDP) a Cloudera Distributed Hadoop (CDH). Mezi těmito dvěma funkcemi nejsou žádné rozdíly, pokud jde o výpočet posunu.

Aby bylo možné s Hadoopem použít funkci nabízení výpočtů, musí mít cílový cluster Hadoop základní komponenty HDFS, YARN a MapReduce s povoleným serverem historie úloh. PolyBase odešle dotaz pushdownu prostřednictvím MapReduce a načítá stav ze serveru historie úloh. Bez obou komponent dotaz selže.

K určité agregaci musí dojít, jakmile data dosáhnou SQL Serveru. Část agregace se ale vyskytuje v Hadoopu. Tato metoda je běžná v výpočetních agregacích v systémech masivního paralelního zpracování.

Poskytovatelé Systému Hadoop podporují následující agregace a filtry.

Agregace Filtry (binární porovnání)
Count_Big NotEqual
Suma LessThan
Průměr menší nebo rovno
Max Větší nebo rovno
Minuta GreaterThan
Approx_Count_Distinct Je
IsNot

Klíčové užitečné scénáře výpočtu odsdílení změn

Při delegování výpočtů pomocí PolyBase můžete úkoly výpočtů svěřit externím zdrojům dat. Tím se sníží zatížení instance SQL Serveru a výrazně se zlepší výkon.

SQL Server může odesílat spojení, projekce, agregace a filtry externím zdrojům dat, aby mohl využívat vzdálené výpočetní prostředky a omezit data odesílaná přes síť.

Posun spojení

V mnoha případech může PolyBase usnadnit posun operátoru spojení pro spojení dvou externích tabulek ve stejném externím zdroji dat, což výrazně zlepší výkon.

Pokud je možné spojení provést v externím zdroji dat, sníží se tím objem přesunu dat a zlepší se výkon dotazu. Bez optimalizace spojení musí být data z tabulek, které se mají spojit, přenesena místně do tempdb a následně spojena.

V případě distribuovaných spojení (spojování místní tabulky k externí tabulce), pokud není v připojené externí tabulce filtr, musí se všechna data v externí tabulce převést místně, tempdb aby bylo možné provést operaci spojení. Například následující dotaz nemá žádné filtrování pro podmínku spojení externí tabulky, což způsobí, že se načtou všechna data z externí tabulky.

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

Vzhledem k tomu, že spojení je ve E.id sloupci externí tabulky, je-li do tohoto sloupce přidána podmínka filtru, lze filtr posunout dolů a snížit tak počet řádků přečtených z externí tabulky.

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

Vyberte podmnožinu řádků

Pomocí predikátového tlačení můžete zlepšit výkon dotazu, který vybírá podmnožinu řádků z externí tabulky.

V tomto příkladu SQL Server zahájí úlohu redukce mapování, která načte řádky, které odpovídají predikátu customer.account_balance < 200000 v Hadoopu. Vzhledem k tomu, že se dotaz může úspěšně dokončit bez kontroly všech řádků v tabulce, zkopírují se do SQL Serveru pouze řádky, které splňují kritéria predikátu. Ušetří se tím značný čas a vyžaduje méně dočasného prostoru úložiště, když je počet zůstatků < zákazníků 200000 malý oproti počtu zákazníků s zůstatky >na účtu = 2 00000.

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

Zvolte podmnožinu sloupců

Použití predikát pushdownu ke zlepšení výkonu dotazu, který vybere podmnožinu sloupců z externí tabulky.

V tomto dotazu SQL Server zahájí úlohu redukce mapování, která předzpracuje textový soubor s oddělovači Hadoop, aby se do SQL Serveru zkopírovala pouze data pro dva sloupce, customer.name a customer.zip_code.

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

Pushdown pro základní výrazy a operátory

SQL Server umožňuje následující základní výrazy a operátory pro predikát pushdown.

  • Binární relační operátory (<, >, =, !=, <>, >=<=) pro číselné hodnoty, datum a čas.
  • Aritmetické operátory (+, -, *, /, %).
  • Logické operátory (AND, OR).
  • Unární operátory (NOT, IS NULL, IS NOT NULL).

Operátory BETWEEN, , NOTINa LIKE mohou být posunut dolů. Skutečné chování závisí na tom, jak optimalizátor dotazů přepisuje výrazy operátoru jako řadu příkazů, které používají základní relační operátory.

Dotaz v tomto příkladu obsahuje několik predikátů, které lze odeslat do Hadoopu. SQL Server může odesílat úlohy redukce mapování do systému Hadoop, aby provedl predikát customer.account_balance <= 200000. Výraz BETWEEN 92656 AND 92677 se také skládá z binárních a logických operací, které lze odeslat do Hadoopu. Logický AND v customer.account_balance AND customer.zipcode je finální výraz.

Vzhledem k této kombinaci predikátů mohou úlohy redukce mapování provádět všechny klauzule WHERE. Do SQL Serveru se zkopírují jenom data, která splňují SELECT kritéria.

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

Podporované funkce pro pushdown

SQL Server umožňuje následující funkce pro posun predikátu.

Řetězcové funkce

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

Matematické funkce

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

Obecné funkce

  • COALESCE *
  • NULLIF

* Použití s COLLATE může zabránit odsunutí v některých scénářích. Další informace naleznete v tématu Konflikt kolace.

Funkce data a času

  • DATEADD
  • DATEDIFF
  • DATEPART

Syntaxe, která brání posunu

Následující funkce nebo syntaxe T-SQL zabraňuje posunutí výpočtu:

  • 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

Podpora zpracování na nižší úrovni pro syntaxi FORMAT a TRIM byla zavedena v SQL Serveru 2019 (15.x) CU10.

Klauzule Filter s proměnnou

Při zadávání proměnné v klauzuli filtru se ve výchozím nastavení zabrání posunu klauzule filtru. Pokud například spustíte následující dotaz, klauzule filtru se nedostane dolu.

DECLARE @BusinessEntityID INT

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

Chcete-li dosáhnout pushdownu proměnné, musíte povolit funkci optimalizace dotazů pomocí opravných balíčků hotfix. Můžete to provést některým z následujících způsobů:

  • Úroveň instance: Povolení příznaku trasování 4199 jako spouštěcího parametru instance
  • Úroveň databáze: V kontextu databáze, která obsahuje externí objekty PolyBase, spusťte ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON
  • Úroveň dotazu: Použijte nápovědu k dotazu OPTION (QUERYTRACEON 4199) nebo OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

Toto omezení platí pro provádění sp_executesql. Toto omezení platí také pro využití některých funkcí v klauzuli filtru.

Možnost předání proměnné byla poprvé zavedena v SQL Serveru 2019 CU5.

Konflikt kolace

Pushdown nemusí být možné u dat s různými kolacemi. Operátory, jako jsou COLLATE , můžou také kolidovat s výsledkem. Podporují se stejné kolace nebo binární kolace. Další informace naleznete v tématu Jak poznat, zda nastalo seskupení.

Optimalizace dotazů pro soubory ve formátu Parquet

Počínaje verzí SQL Server 2022 (16.x) zavedla PolyBase podporu pro soubory „parquet“. SQL Server je schopen provést odstranění řádků i sloupců při přeneseném zpracování v parquet. U souborů parquet je možné prosadit následující operace:

  • Binární relační operátory (>, >=, <=, <) pro číselné hodnoty, datum a čas
  • Kombinace relačních operátorů (> AND <, >= AND <, > AND <=, <= AND >=).
  • Ve filtru seznamu (sloupec1 = val1 NEBO sloupec1 = val2 OR vol1 = val3).
  • IS NOT NULL nad sloupec.

Přítomnost následujících možností zabraňuje posunu souborů parquet:

  • Virtuální sloupce.
  • Porovnání sloupců
  • Převod typu parametru

Podporované datové typy

  • Bit
  • TinyInt
  • SmallInt
  • BigInt
  • real
  • Plovat
  • VARCHAR (Bin2Collation, CodePageConversion, BinCollation)
  • NVARCHAR (Bin2Collation, BinCollation)
  • Binary
  • DateTime2 (výchozí a 7místná přesnost)
  • Date
  • Čas (výchozí a 7místná přesnost)
  • Číselné*

* Podporováno, když se měřítko parametru zarovná se měřítkem sloupce nebo když je parametr explicitně přetypován na desetinné číslo.

Datové typy, které brání posunu parquet

  • Peníze
  • Smallmoney
  • DateTime
  • Smalldatetime

Examples

Vynucení posunu

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

Zakázání nabízení změn

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

Související obsah

  • Last updated on