Sdílet prostřednictvím

Osvědčené postupy pro bezserverový fond SQL ve službě Azure Synapse Analytics

V tomto článku najdete kolekci osvědčených postupů pro používání bezserverového fondu SQL. SQL fond bez serveru je zdroj ve službě Azure Synapse Analytics. Pokud pracujete s vyhrazeným fondem SQL, konkrétní pokyny najdete v osvědčených postupech pro vyhrazené fondy SQL.

Bezserverový fond SQL umožňuje dotazovat se na soubory v účtech Azure Storage. Nemá místní úložiště ani možnosti příjmu dat. Všechny soubory, na které cílí dotazy, jsou externí vůči bezserverovému fondu SQL. Na výkon dotazů může mít vliv všechno související se čtením souborů z úložiště.

Tady jsou některé obecné pokyny:

  • Ujistěte se, že jsou klientské aplikace umístěny v blízkosti bezserverového fondu SQL.
    • Pokud používáte klientské aplikace mimo Azure, ujistěte se, že používáte bezserverový fond SQL v oblasti, která je blízko klientského počítače. Příklady klientských aplikací zahrnují Power BI Desktop, SQL Server Management Studio a Azure Data Studio.
  • Ujistěte se, že úložiště a bezserverový fond SQL jsou ve stejné oblasti. Příklady úložiště zahrnují Azure Data Lake Storage a Azure Cosmos DB.
  • Pokuste se optimalizovat rozložení úložiště pomocí dělení a udržování souborů v rozsahu mezi 100 MB a 10 GB.
  • Pokud se vám vrací velký počet výsledků, ujistěte se, že používáte SQL Server Management Studio nebo Azure Data Studio, a ne Azure Synapse Studio. Azure Synapse Studio je webový nástroj, který není určený pro velké sady výsledků.
  • Pokud filtrujete výsledky podle řetězcového sloupce, zkuste použít BIN2_UTF8 kolaci. Další informace o změně kolací naleznete v tématu Typy kolace podporované pro Synapse SQL.
  • Zvažte ukládání výsledků do mezipaměti na straně klienta s použitím režimu importu Power BI nebo Azure Analysis Services a pravidelně je aktualizujte. Bezserverové fondy SQL nemůžou poskytovat interaktivní prostředí v režimu Power BI Direct Query, pokud používáte složité dotazy nebo zpracováváte velké množství dat.
  • Maximální souběžnost není omezená a závisí na složitosti dotazu a množství kontrolovaných dat. Jeden bezserverový fond SQL může souběžně zpracovávat 1 000 aktivních relací, které provádějí nenáročné dotazy. Čísla se sníží, pokud jsou dotazy složitější nebo prohledávají větší množství dat, takže v takovém případě zvažte snížení souběžnosti a provádějte dotazy po delší dobu, pokud je to možné.

Klientské aplikace a síťová připojení

Ujistěte se, že je klientská aplikace připojená k nejbližšímu možnému pracovnímu prostoru Azure Synapse s optimálním připojením.

  • Spolulokujte klientskou aplikaci s pracovním prostorem Azure Synapse. Pokud používáte aplikace, jako je Power BI nebo Azure Analysis Service, ujistěte se, že jsou ve stejné oblasti, ve které jste umístili pracovní prostor Azure Synapse. V případě potřeby vytvořte samostatné pracovní prostory spárované s klientskými aplikacemi. Umístění klientské aplikace a pracovního prostoru Azure Synapse do různých oblastí může způsobit větší latenci a pomalejší streamování výsledků.
  • Pokud čtete data z místní aplikace, ujistěte se, že je pracovní prostor Azure Synapse v oblasti, která je blízko vaší poloze.
  • Při čtení velkého množství dat se ujistěte, že nemáte problémy se šířkou pásma sítě.
  • Nepoužívejte Azure Synapse Studio k vrácení velkého množství dat. Azure Synapse Studio je webový nástroj, který k přenosu dat používá protokol HTTPS. Ke čtení velkého množství dat použijte Azure Data Studio nebo SQL Server Management Studio.

Rozložení úložiště a obsahu

Tady jsou osvědčené postupy pro ukládání a rozložení obsahu v bezserverovém fondu SQL.

Umístění úložiště a serverless SQL fondu

Pokud chcete minimalizovat latenci, umístěte svůj účet Azure Storage nebo analytické úložiště Azure Cosmos DB a koncový bod bezserverového fondu SQL blízko sebe. Účty úložiště a koncové body zřízené během vytváření pracovního prostoru se nacházejí ve stejné oblasti.

Pro dosažení optimálního výkonu se ujistěte, že jiné účty úložiště, ke kterým přistupujete pomocí bezserverového SQL fondu, jsou ve stejném regionu. Pokud nejsou ve stejné oblasti, zvýší se latence přenosu dat mezi vzdálenou oblastí a oblastí koncového bodu.

Umístěte společně své analytické úložiště Azure Cosmos DB a bezserverový fond SQL

Ujistěte se, že analytické úložiště Azure Cosmos DB je umístěné ve stejné oblasti jako pracovní prostor Azure Synapse. Dotazy napříč oblastmi můžou způsobit velkou latenci. Pomocí vlastnosti regionu v rámci připojovacího řetězce explicitně určete region, ve kterém je umístěno analytické úložiště (viz Dotazování služby Azure Cosmos DB prostřednictvím bezserverového SQL fondu):account=<database account name>;database=<database name>;region=<region name>'

Omezování služby Azure Storage

K vašemu účtu úložiště může přistupovat více aplikací a služeb. K omezování úložiště dochází v případě, že kombinované IOPS nebo propustnost generované aplikacemi, službami a úlohami bezserverového fondu SQL překročí limity účtu úložiště. V důsledku toho dojde k významnému negativnímu dopadu na výkon dotazování.

Když je zjištěno omezení, SQL fond bez serveru má zabudovaný mechanismus pro jeho řešení. Bezserverový fond SQL provádí požadavky na úložiště pomalejším tempem, dokud se nevyřeší omezení.

Návod

Při optimálním provádění dotazů nezatěžujte účet úložiště s jinými úlohami během provádění dotazů.

Příprava souborů pro dotazování

Pokud je to možné, můžete soubory připravit na lepší výkon:

  • Převeďte velké soubory CSV a JSON na Parquet. Parquet je sloupcový formát. Protože je komprimovaný, jeho velikosti souborů jsou menší než soubory CSV nebo JSON, které obsahují stejná data. Bezserverový fond SQL přeskočí sloupce a řádky, které nejsou potřeba v dotazu, pokud čtete soubory Parquet. Bezserverový fond SQL potřebuje k přečtení méně času a méně požadavků na úložiště.
  • Pokud dotaz cílí na jeden velký soubor, můžete ho rozdělit na několik menších souborů.
  • Zkuste zachovat velikost souboru CSV mezi 100 MB a 10 GB.
  • Je lepší mít soubory stejné velikosti pro jednu OPENROWSET cestu nebo umístění externí tabulky.
  • Rozdělte data tak, že uložíte oddíly do různých složek nebo názvů souborů. Pokud chcete cílit na konkrétní oddíly, podívejte se na použití funkcí název souboru a cesty k souborům.

Optimalizace CSV souborů

Tady jsou osvědčené postupy pro používání souborů CSV v bezserverovém fondu SQL.

Použití PARSER_VERSION 2.0 k dotazování souborů CSV

Při dotazování na soubory CSV můžete použít analyzátor optimalizovaný pro výkon. Podrobnosti najdete v PARSER_VERSION.

Ruční vytváření statistik pro soubory CSV

Bezserverový fond SQL spoléhá na statistiky pro generování optimálních plánů provádění dotazů. Statistiky se automaticky vytvářejí pro sloupce používající vzorkování a ve většině případů bude procento vzorkování menší než 100 %. Tento tok je stejný pro každý formát souboru. Mějte na paměti, že při čtení souboru CSV pomocí parseru verze 1.0 není vzorkování podporováno a automatické vytváření statistik se neprovádí, pokud není procento vzorkování 100 %. U malých tabulek s odhadovanou nízkou kardinalitou (počet řádků) se automatické vytváření statistik aktivuje s procentem vzorkování 100 %. To znamená, že se aktivuje fullscan a vytvoří se automatické statistiky i pro CSV s analyzátorem verze 1.0. V případě, že se statistiky nevytvoří automaticky, vytvořte statistiky ručně pro sloupce, které používáte v dotazech, zejména pro sloupce používané v DISTINCT, JOIN, WHERE, ORDER BY a GROUP BY. Zkontrolujte statistiky v bezserverovém fondu SQL pro podrobnosti.

Optimalizace Delta Lake

Tady jsou osvědčené postupy pro používání souborů Delta Lake v bezserverovém fondu SQL.

Optimalizace kontrolních bodů

Výkon dotazů ve formátu Delta Lake má vliv na počet souborů JSON v _delta_log adresáři. Abyste zajistili optimální výkon, vyhněte se shromažďování příliš velkého počtu souborů JSON. V ideálním případě by protokol měl obsahovat pouze nejnovější soubor kontrolního bodu Parquet bez dalších souborů JSON. Toto nastavení ale nemusí být optimální pro úlohy náročné na zápis.

Vyváženým přístupem je udržovat přibližně 10 souborů JSON mezi kontrolními body, což obvykle nabízí dobrý výkon pro čtenáře i zapisovače. Buďte opatrní u konfigurací, které zpozdí vytváření kontrolních bodů, protože můžou vést k nadměrnému akumulace souborů JSON a snížení výkonu dotazů.

Nastavte následující vlastnost tabulky, aby se zajistilo vytvoření kontrolního bodu po každých 10 souborech protokolu JSON:

ALTER TABLE tableName SET TBLPROPERTIES ('delta.checkpointInterval' = '10')

Datové typy

Tady jsou osvědčené postupy pro používání datových typů v bezserverovém fondu SQL.

Použití vhodných datových typů

Datové typy, které používáte v dotazu, ovlivňují výkon a souběžnost. Pokud budete postupovat podle těchto pokynů, můžete dosáhnout lepšího výkonu:

  • Použijte nejmenší velikost dat, která může obsahovat největší možnou hodnotu.
    • Pokud je maximální délka znaku 30 znaků, použijte datový typ znaku o délce 30 znaků.
    • Pokud mají všechny hodnoty sloupců znaků pevnou velikost, použijte znak nebo nchar. V opačném případě použijte varchar nebo nvarchar.
    • Pokud je maximální celočíselná hodnota sloupce 500, použijte smallint , protože se jedná o nejmenší datový typ, který může tuto hodnotu přizpůsobit. Další informace naleznete v tématu rozsahy datového typu celé číslo.
  • Pokud je to možné, použijte varchar a char místo nvarchar a nchar.
    • Použijte typ varchar s určitou kolací typu UTF8, pokud čtete data z Parquet, Azure Cosmos DB, Delta Lake nebo CSV s kódováním UTF-8.
    • Pokud čtete data ze souborů CSV bez kolace UTF8 (například ASCII), použijte typ varchar bez kolace UTF8.
    • Pokud čtete data ze souboru CSV UTF-16, použijte typ nvarchar.
  • Pokud je to možné, použijte datové typy založené na celých číslech. Operace SORT, JOIN a GROUP BY jsou u celých čísel dokončeny rychleji než u znakových dat.
  • Pokud používáte odvození schématu, zkontrolujte odvozené datové typy a pokud je to možné, menšími typy je přepište explicitně.

Kontrola odvozených datových typů

Odvozování schématu pomáhá rychle psát dotazy a zkoumat data bez znalosti schémat souborů. Náklady na tuto výhodu spočívají v tom, že odvozené datové typy mohou být rozměrově větší než skutečné datové typy. K této nesrovnalosti dochází v případě, že ve zdrojových souborech není dostatek informací, aby se zajistilo použití příslušného datového typu. Například soubory Parquet neobsahují metadata o maximální délce sloupce znaků. SQL fond bez serveru ho proto vyhodnotí jako varchar(8000).

Mějte na paměti, že situace se může lišit v případě spravovaných a externích tabulek Spark vystavených v modulu SQL jako externích tabulek. Tabulky Sparku poskytují různé datové typy než moduly Synapse SQL. Tady najdete mapování datových typů tabulek Sparku a typů SQL.

K kontrole výsledných datových typů dotazu můžete použít systémovou uloženou proceduru sp_describe_first_results_set .

Následující příklad ukazuje, jak můžete optimalizovat odvozené datové typy. Tento postup slouží k zobrazení odvozených datových typů:

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

Tady jsou výsledky:

je_skrytý pořadí sloupce jméno název typu systému max_délka
0 1 ID dodavatele varchar(8000) osmtisíc
0 2 Datum a čas vyzvednutí datetime2(7) 8
0 3 počet_cestujících int (integer) 4

Jakmile znáte odvozené datové typy dotazu, můžete zadat příslušné datové typy:

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

Optimalizace filtrů

Tady jsou osvědčené postupy pro používání dotazů v bezserverovém fondu SQL.

Přenést zástupné znaky na nižší úrovně cesty

Pomocí zástupných znaků v cestě můžete prohledávat více souborů a složek. Bezserverový fond SQL uvádí soubory ve vašem účtu úložiště počínaje první hvězdičkou (*) pomocí rozhraní API úložiště. Soubory, které neodpovídají zadané cestě, vynechá. Snížení počátečního počtu souborů může zvýšit výkon, pokud existuje velké množství souborů, které odpovídají zadané cestě až do prvního zástupného znaku.

Použijte funkce název souboru a cesta k cílení na konkrétní oddíly.

Data jsou často uspořádaná do particí. Bezserverový fond SQL můžete instruovat, aby se dotazoval na konkrétní složky a soubory. Tím se sníží počet souborů a množství dat, která dotaz potřebuje ke čtení a zpracování. Přidaný bonus je, že dosáhnete lepšího výkonu.

Další informace najdete v tématu o funkcích názvu souboru a cesty k souborům a podívejte se na příklady pro dotazování konkrétních souborů.

Návod

Vždy přetypujte výsledky funkcí cesty k souboru a názvu souboru na příslušné datové typy. Pokud používáte datové typy znaků, nezapomeňte použít odpovídající délku.

Funkce používané k odstranění oddílů, cestě k souborům a názvu souboru se v současné době nepodporují pro externí tabulky, jiné než funkce vytvořené automaticky pro každou tabulku vytvořenou v Apache Sparku pro Azure Synapse Analytics.

Pokud uložená data nejsou rozdělená na oddíly, zvažte jejich rozdělení. Díky tomu můžete tyto funkce použít k optimalizaci dotazů, které cílí na tyto soubory. Když dotazujete dělené tabulky Apache Spark v Azure Synapse z bezserverového SQL prostředí, dotaz automaticky cílí pouze na potřebné soubory.

Použijte správnou kolaci k využití predikátového pushdownu pro sloupce znaků

Data v souboru Parquet jsou uspořádaná do skupin řádků. Bezserverový fond SQL přeskakuje skupiny řádků na základě zadaného predikátu v klauzuli WHERE, což snižuje počet vstupně-výstupních operací. Výsledkem je zlepšený výkon dotazů.

Podpora předávání predikátů pro znakové sloupce je v souborech typu Parquet poskytována pouze pro kolaci Latin1_General_100_BIN2_UTF8. Kolaci pro konkrétní sloupec můžete zadat pomocí klauzule WITH. Pokud tuto kolaci nezadáte pomocí klauzule WITH, použije se kolace databáze.

Optimalizace opakujících se dotazů

Tady jsou osvědčené postupy pro používání CETAS v bezserverovém fondu SQL.

Použití CETAS k vylepšení výkonu dotazů a spojení

CETAS je jednou z nejdůležitějších funkcí dostupných v bezserverovém fondu SQL. CETAS je paralelní operace, která vytvoří metadata externí tabulky a exportuje výsledky dotazu SELECT do sady souborů ve vašem účtu úložiště.

CETAS můžete použít k materializaci často používaných částí dotazů, jako jsou spojené referenční tabulky, k nové sadě souborů. Místo opakování běžných spojení ve více dotazech se pak můžete připojit k této jedné externí tabulce.

Protože CETAS generuje soubory Parquet, statistiky se automaticky vytvoří, když první dotaz cílí na tuto externí tabulku. Výsledkem je lepší výkon pro následné dotazy, které cílí na tabulku generovanou pomocí CETAS.

Dotazování dat Azure

Bezserverové fondy SQL umožňují dotazovat data ve službě Azure Storage nebo Azure Cosmos DB pomocí externích tabulek a funkce OPENROWSET. Ujistěte se, že máte v úložišti nastavená správná oprávnění.

Dotazování dat CSV

Zjistěte, jak dotazovat jeden soubor CSV nebo složky a několik souborů CSV. Můžete se také dotazovat na dělené soubory.

Dotazování dat Parquet

Naučte se dotazovat soubory Parquet pomocí vnořených typů. Můžete se také dotazovat na dělené soubory.

Dotaz na Delta Lake

Zjistěte, jak provádět dotazy na soubory Delta Lake pomocí vnořených typů.

Dotazování dat azure Cosmos DB

Zjistěte, jak dotazovat analytické úložiště Azure Cosmos DB. K vygenerování klauzule WITH na základě ukázkového dokumentu Azure Cosmos DB můžete použít online generátor . Zobrazení můžete vytvářet nad kontejnery Azure Cosmos DB.

Dotazování dat JSON

Zjistěte, jak dotazovat soubory JSON. Můžete se také dotazovat na dělené soubory.

Vytváření zobrazení, tabulek a dalších databázových objektů

Naučte se vytvářet a používat zobrazení a externí tabulky nebo nastavit zabezpečení na úrovni řádků. Pokud máte dělené soubory, ujistěte se, že používáte dělené zobrazení.

Kopírování a transformace dat (CETAS)

Zjistěte, jak ukládat výsledky dotazů do úložiště pomocí příkazu CETAS.

Další kroky

  • Last updated on