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

  • Článek

V tomto článku najdete kolekci osvědčených postupů pro používání bezserverového fondu SQL. Bezserverový fond SQL je prostředek ve službě Azure Synapse Analytics. Pokud pracujete s vyhrazeným fondem SQL, pro konkrétní pokyny se podívejte na osvědčené postupy vyhrazených fondů 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, které cílí na dotazy, jsou externí pro bezserverový fond SQL. Výkon dotazů může mít vliv na 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 kompletované s bezserverovým fondem 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 vracíte 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 sloupce řetězců, zkuste použít BIN2_UTF8 kolaci. Další informace o změně kolací najdete v tématu Typy kolace podporované pro Synapse SQL.
  • Zvažte ukládání výsledků do mezipaměti na straně klienta pomocí režimu importu Power BI nebo služby Azure Analysis Services a pravidelně je aktualizujte. Bezserverové fondy SQL nemůžou v režimu Direct Query Power BI poskytovat interaktivní prostředí, 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í zjednodušené 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ění dotazů 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.

Společné přidělení úložiště a bezserverového fondu SQL

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

Pokud při přístupu k jiným účtům úložiště s bezserverovým fondem SQL přistupujete k optimálnímu výkonu, ujistěte se, že jsou ve stejné oblasti. Pokud nejsou ve stejné oblasti, zvýší se latence přenosu dat mezi vzdálenou oblastí a oblastí koncového bodu.

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 dotazů.

Při zjištění omezování má bezserverový fond SQL integrovanou manipulaci s řešením. Bezserverový fond SQL provádí požadavky na úložiště pomalejším tempem, dokud se nepřeloží omezování.

Tip

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 stejně velké soubory pro jednu cestu OPENROWSET 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.

Společné přidělení analytického úložiště Azure Cosmos DB a bezserverového fondu 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 oblasti v připojovací řetězec explicitně určete oblast, ve které je analytické úložiště umístěné (viz Dotazování služby Azure Cosmos DB pomocí bezserverového fondu SQL):account=<database account name>;database=<database name>;region=<region name>'

Optimalizace sdíleného svazku clusteru

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 s vzorkováním verze 1.0 se nepodporuje vzorkování a automatické vytváření statistik se nestane s procentem vzorkování nižším než 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. Podrobnosti najdete ve statistikách bezserverového fondu SQL.

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 najdete v tématu Rozsahy datových typů celé číslo.
  • Pokud je to možné, použijte místo nvarchar a nchar varchar a char.
    • Pokud čtete data z Parquet, Azure Cosmos DB, Delta Lake nebo CSV s kódováním UTF-8, použijte typ varchar s určitou kolací UTF8.
    • 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é, přepište je explicitně s menšími typy.

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 pohodlí jsou, že odvozené datové typy můžou být 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ů. Bezserverový fond SQL ho proto odvodí 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 je sada výsledků:

is_hidden column_ordinal name system_type_name max_length
0 1 vendor_id varchar(8000) 8000
0 2 pickup_datetime datetime2(7) 8
0 3 passenger_count int 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.

Nasdílení zástupných znaků do nižších úrovní v cestě

Pomocí zástupných znaků v cestě můžete dotazovat 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žití funkcí název souboru a cesty k cílení na konkrétní oddíly

Data jsou často uspořádaná do oddílů. 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ů.

Tip

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. Při dotazování na dělené tabulky Apache Spark pro Azure Synapse z bezserverového fondu SQL dotaz automaticky cílí pouze na potřebné soubory.

Použití správné kolace k využití predikátu 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 vyšší výkon dotazů.

Predikát pro sloupce znaků v souborech Parquet je podporován 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 dotazovat 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