Osvědčené postupy pro načítání dat do vyhrazeného fondu SQL v Azure Synapse Analytics

  • Článek

V tomto článku najdete doporučení a optimalizace výkonu pro načítání dat.

Příprava dat ve službě Azure Storage

Pokud chcete minimalizovat latenci, přidělte vrstvu úložiště společně s vyhrazeným fondem SQL.

Při exportu dat do formátu souboru ORC může dojít k chybám s nedostatkem paměti Java, pokud se zde nacházejí velké textové sloupce. Toto omezení můžete obejít tím, že importujete jen podmnožinu sloupců.

PolyBase nemůže načíst řádky, které mají více než 1 000 000 bajtů dat. Když vkládáte data do textových souborů v úložišti Azure Blob nebo ve službě Azure Data Lake Store, musí tyto soubory obsahovat méně než 1 000 000 bajtů dat. Toto omezení platí bez ohledu na schéma tabulky.

Všechny formáty souborů mají jiné výkonové charakteristiky. Pokud chcete docílit nejrychlejšího načtení, použijte komprimované textové soubory s oddělovači. Rozdíl mezi výkonem kódování UTF-8 a UTF-16 je minimální.

Velké komprimované soubory rozdělte do menších komprimovaných souborů.

Spouštění zatížení s dostatečným výpočetním výkonem

Největší rychlosti při načítání dosáhnete, když budete spouštět vždy jen jednu úlohu načtení dat. Pokud to není možné, spouštějte souběžně co nejmenší počet úloh. Pokud očekáváte velkou úlohu načítání, zvažte vertikální navýšení kapacity vyhrazeného fondu SQL před načtením.

Pokud chcete spouštět načítání s odpovídajícími výpočetními prostředky, vytvořte uživatele načítání vyhrazené pro spouštění načítání. Přiřaďte každého uživatele, který se načítá, ke konkrétní třídě prostředků nebo skupině úloh. Pokud chcete spustit načtení, přihlaste se jako jeden z načítacích uživatelů a spusťte načtení. Načítání se spustí s využitím třídy prostředků tohoto uživatele. Tato metoda je jednodušší než se pokoušet o změnu třídy prostředků uživatele podle aktuálních potřeb třídy prostředků.

Vytvoření načítácí uživatele

Tento příklad vytvoří načítající uživatele klasifikovaného do konkrétní skupiny úloh. Prvním krokem je připojení k předloze a vytvoření přihlášení.

   -- Connect to master
   CREATE LOGIN loader WITH PASSWORD = 'a123STRONGpassword!';

Připojte se k vyhrazenému fondu SQL a vytvořte uživatele. Následující kód předpokládá, že jste připojení k databázi mySampleDataWarehouse. Ukazuje, jak vytvořit uživatele s názvem zavaděč a dává uživateli oprávnění k vytváření tabulek a načítání pomocí příkazu COPY. Potom klasifikuje uživatele do skupiny úloh DataLoads s maximálním počtem prostředků.

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );



Důležité

Toto je extrémní příklad přidělení 100% prostředků fondu SQL na jedno zatížení. Tím získáte maximální souběžnost 1. Mějte na paměti, že tato možnost by se měla používat pouze pro počáteční zatížení, kdy budete muset vytvořit další skupiny úloh s vlastními konfiguracemi, aby se prostředky mezi vašimi úlohami vyvažovaly.

Pokud chcete spustit zatížení s prostředky pro skupinu úloh načítání, přihlaste se jako zavaděč a spusťte zatížení.

Povolit načtení více uživatelů

Často je potřeba, aby data do datového skladu načítalo více uživatelů. Načítání pomocí příkazu CREATE TABLE AS SELECT (Transact-SQL) vyžaduje oprávnění CONTROL databáze. Oprávnění CONTROL poskytuje přístup pro řízení ke všem schématům. Pravděpodobně ale nebudete chtít, aby všichni uživatelé, kteří načítají data, měli oprávnění CONTROL pro přístup ke všem schématům. K omezení oprávnění slouží příkaz DENY CONTROL.

Představte si například schémata databáze schema_A pro oddělení A a schema_B pro oddělení B. Uživatelé databáze user_A a user_B budou uživateli pro načítání PolyBase v oddělení A, respektive oddělení B. Oba uživatelé mají k databázi udělená oprávnění CONTROL. Autoři schémat A a B nyní svá schémata uzamknou pomocí příkazu DENY:

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

User_A a user_B jsou teď ve schématu jiného oddělení uzamčeny.

Načítání do pracovní tabulky

Pokud chcete při přesunu dat do tabulky datového skladu dosáhnout největší načítací rychlosti, načtěte data do pracovní tabulky. Pracovní tabulku definujte jako haldu a jako způsob distribuce použijte kruhové dotazování (round robin).

Myslete na to, že načítání je většinou dvoufázový proces, kdy napřed načtete data do pracovní tabulky a pak je vložíte do tabulky v provozním datovém skladu. Pokud provozní tabulka používá k distribuci algoritmus hash, může být celková doba načtení a vložení dat kratší, než když k definici pracovní tabulky použijete distribuci hash. Načítání do pracovní tabulky trvá déle, ale druhý krok, který spočívá ve vkládání řádků do provozní tabulky, nepřesouvá data prostřednictvím distribuce.

Načtení do indexu columnstore

Indexy columnstore vyžadují hodně paměti, aby mohly komprimovat data do vysoce kvalitních skupin řádků. Kvůli zajištění co nejlepší účinnosti komprese a indexování musí index columnstore do každé skupiny řádků zkomprimovat maximální počet 1 048 576 řádků. V případě nedostatku paměti nemusí index columnstore dosahovat maximální míry komprese. To má vliv na výkon dotazů. Podrobné informace najdete v tématu Optimalizace paměti pro columnstore.

  • Pokud chcete zajistit, aby měl nahrávající uživatel dostatek paměti pro dosažení maximální míry komprese, použijte uživatele načítání, kteří jsou členy střední nebo velké třídy prostředků.
  • Načtěte dostatek dat pro úplně naplnění nových skupin řádků. Během hromadného načítání se každých 1 048 576 řádků zkomprimuje přímo do columnstore jako úplná skupina řádků. Při načítání méně než 102 400 řádků se řádky odesílají do tabulky deltastore, kde se řádky uchovávají v indexu B-stromu. Pokud načtete příliš málo řádků, můžou se všechny dostat do indexu deltastore, a nebudou se okamžitě komprimovat do formátu columnstore.

Zvětšení velikosti dávky při použití rozhraní SQLBulkCopy API nebo BCP

Načtení pomocí příkazu COPY zajistí nejvyšší propustnost s vyhrazenými fondy SQL. Pokud nemůžete použít copy k načtení a musíte použít rozhraní API SqLBulkCopy nebo bcp, měli byste zvážit zvětšení velikosti dávky pro zajištění lepší propustnosti.

Tip

Velikost dávky mezi 100 K až 1M řádky je doporučeným výchozím plánem pro určení optimální velikosti dávky.

Správa selhání načítání

Načtení s použitím externí tabulky může selhat s chybou Query aborted-- the maximum reject threshold was reached while reading from an external source (Dotaz byl přerušen – při čtení z externího zdroje byla dosažena maximální prahová hodnota pro odmítnutí). Tato zpráva znamená, že vaše externí data obsahují nezapsané záznamy. Datový záznam se považuje za nezapsaný, pokud se typy dat nebo čísla sloupců neshodují s definicemi sloupců externí tabulky nebo pokud data neodpovídají zadanému formátu externího souboru.

Pokud chcete nezapsané záznamy opravit, ujistěte se, že jsou definice formátů externí tabulky a externího souboru správné a že externí data těmto definicím odpovídají. V případě, že je podmnožina externích datových záznamů zašpiněná, můžete tyto záznamy pro vaše dotazy odmítnout pomocí možností odmítnutí v části CREATE EXTERNAL TABLE (VYTVOŘIT EXTERNÍ TABULKU).

Vložení dat do produkční tabulky

Při jednorázovém načtení malé tabulky příkazem INSERT nebo i při pravidelně se opakujícím načítání funkcí look-up pravděpodobně vystačíte s následujícím příkazem: INSERT INTO MyLookup VALUES (1, 'Type 1'). Jednoúčelové vložky ale nejsou tak efektivní jako hromadné načítání.

Pokud máte za den tisíce nebo více samostatných vložení, vytvořte z nich dávku, abyste je mohli načíst hromadně. Vyvíjejte své procesy tak, aby samostatná vkládání připojovaly do souboru, a pak vytvořte další proces, který tento soubor bude pravidelně načítat.

Vytvoření statistiky po načtení

Pokud chcete zvýšit výkon dotazů, je důležité vytvořit statistiky pro všechny sloupce všech tabulek po prvním načtení, jinak dojde v datech k významným změnám. Statistiky můžete vytvořit ručně nebo můžete povolit automatické vytváření statistik.

Podrobné vysvětlení statistiky najdete v tématu Statistika. Následující příklad ukazuje, jak ručně vytvořit statistiky pro pět sloupců tabulky Customer_Speed.

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

Obměna klíčů úložiště

Osvědčeným postupem zabezpečení je pravidelně měnit přístupový klíč k úložišti objektů blob. Ke svému účtu úložiště objektů Blob máte dva klíče úložiště, abyste je mohli střídat.

Obměna klíčů účtu služby Azure Storage:

Pro každý účet úložiště, jehož klíč se změnil, vydejte příkaz ALTER DATABASE SCOPED CREDENTIAL.

Příklad:

Původní klíč je vytvořený.

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

Proveďte otočení klíče 1 do klíče 2.

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2'

V příslušných externích zdrojích dat se nevyžadují žádné další změny.

Další kroky