Ajánlott eljárások az adatok dedikált SQL-készletbe való betöltéséhez az Azure Synapse Analyticsben

  • Cikk

Ebben a cikkben javaslatokat és teljesítményoptimalizálásokat talál az adatok betöltéséhez.

Adatok előkészítése az Azure Storage-ban

A késés minimalizálása érdekében helyezze át a tárolási réteget és a dedikált SQL-készletet.

Az adatok ORC fájlformátumba való exportálásakor Java memóriahiány-hibák jelentkezhetnek, ha a szövegoszlopok túl nagyok. Ezt a korlátozást úgy küszöbölheti ki, ha az oszlopok csak egy részhalmazát exportálja.

A PolyBase nem tudja betölteni az 1 000 000 bájtnál több adatot tartalmazó sorokat. Az Azure Blob Storage-ba vagy az Azure Data Lake Store-ba helyezett szöveges fájlok nem tartalmazhatnak 1 000 000 bájtnál több adatot. Ez a bájtkorlátozás a táblasémától függetlenül érvényes.

Minden fájlformátum eltérő teljesítményjellemzővel rendelkezik. A leggyorsabb betöltés érdekében használjon tömörített, tagolt szövegfájlokat. Az UTF-8 és UTF-16 formátum teljesítménye között minimális a különbség.

A nagy tömörített fájlokat ossza fel kisebb tömörített fájlokra.

Terhelések futtatása elegendő számítási kapacitással

A leggyorsabb betöltési sebesség érdekében egyszerre egy betöltési feladatot futtasson. Ha ez nem lehetséges, egyszerre a lehető legkevesebb betöltést futtassa. Ha nagy betöltési feladatra számít, fontolja meg a dedikált SQL-készlet vertikális felskálázását a betöltés előtt.

A betöltések megfelelő számítási erőforrásokkal való futtatásához hozzon létre betöltések futtatására kijelölt felhasználókat. Rendelje hozzá az egyes betöltési felhasználókat egy adott erőforrásosztályhoz vagy számítási feladatcsoporthoz. A betöltés futtatásához jelentkezzen be a betöltési felhasználók egyikeként, majd futtassa a betöltést. A betöltés a felhasználó erőforrásosztályával fut. Ez a módszer egyszerűbb, mint a felhasználó erőforrásosztályának módosításával próbálkozni, hogy az megfeleljen az aktuális erőforrásosztály-igénynek.

Betöltő felhasználó létrehozása

Ez a példa egy adott számítási feladatcsoportba besorolt betöltési felhasználót hoz létre. Ennek első lépése a főkiszolgálóhoz való csatlakozás és egy bejelentkezés létrehozása.

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

Csatlakozzon a dedikált SQL-készlethez, és hozzon létre egy felhasználót. Az alábbi kód feltételezi, hogy csatlakozik a mySampleDataWarehouse nevű adatbázishoz. Bemutatja, hogyan hozhat létre egy betöltő nevű felhasználót, és hogyan ad engedélyt a felhasználónak táblák létrehozására és betöltésére a COPY utasítással. Ezután a felhasználót a Maximális erőforrásokkal rendelkező DataLoads számítási feladatcsoportba sorolja be.

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



Fontos

Ez egy szélsőséges példa arra, hogy az SQL-készlet 100%-os erőforrásait egyetlen terhelésre kell kiosztani. Ez 1 maximális egyidejűséget biztosít. Vegye figyelembe, hogy ezt csak a kezdeti terheléshez kell használni, ahol további számításifeladat-csoportokat kell létrehoznia saját konfigurációkkal az erőforrások számítási feladatok közötti elosztásához.

Ha terhelést szeretne futtatni a betöltési számítási feladatcsoport erőforrásaival, jelentkezzen be betöltőként, és futtassa a terhelést.

Több felhasználó betöltésének engedélyezése

Gyakran van szükség több olyan felhasználóra, akik adatokat töltenek egy adattárházba. A CREATE TABLE AS SELECT (Transact-SQL) paranccsal történő betöltéshez az adatbázis CONTROL engedélyei szükségesek. A CONTROL engedély az összes séma vezérlését biztosítja. Előfordulhat, hogy nem szeretné, hogy minden betöltést végző felhasználó vezérelési jogot kapjon az összes sémához. Az engedélyek korlátozására használja a DENY CONTROL utasítást.

Vegyünk például két adatbázissémát: schema_A az A részleghez, és schema_B a B részleghez. Legyen user_A és user_B két PolyBase-betöltést végző adatbázis-felhasználó az A, illetve a B részlegen. Mindkét felhasználó kapott adatbázisszintű CONTROL jogosultságokat. Az A és B séma létrehozói zárolják a sémáikat a DENY utasítás segítségével:

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

User_A és user_B ki vannak zárva a másik osztály sémájából.

Betöltés előkészítési táblába

Az adattárház táblájába való adatáthelyezés leggyorsabb betöltési sebességének eléréséhez töltse be az adatokat egy előkészítési táblába. Határozza meg az előkészítési táblát halomként, és használjon ciklikus időszeletelést a terjesztési beállításhoz.

Vegye figyelembe, hogy a betöltés általában két lépésből álló folyamat, amely során először az előkészítési táblába tölti be, majd beszúrja az adatokat egy éles adattárháztáblába. Ha az éles tábla kivonatoló terjesztést használ, a betöltés és a beszúrás teljes ideje gyorsabb lehet, ha meghatároz egy előkészítési táblát a kivonatoló terjesztéssel. Az előkészítési táblába való betöltés több időt vesz igénybe, de a sorok az éles táblába való beszúrásának második lépése nem jár a disztribúciók közötti adatmozgatással.

Betöltés oszlopcentrikus indexbe

Az oszlopcentrikus indexek sok memóriát igényelnek az adatok jó minőségű sorcsoportokba való tömörítéséhez. A legjobb tömörítési és indexelési hatékonyság érdekében az oszlopcentrikus indexnek a maximális 1 048 576 sort kell tömörítenie az egyes sorcsoportokba. Ha korlátozott a rendelkezésre álló memória mennyisége, előfordulhat, hogy az oszlopcentrikus index nem éri el a maximális tömörítési sebességet. Ez hatással van a lekérdezési teljesítményre. A témakör részletes bemutatása: Oszloptár memóriájának optimalizálása.

  • Annak érdekben, hogy elég memória álljon a betöltést végző felhasználók rendelkezésére a maximális tömörítési sebesség eléréséhez, használjon olyan betöltést végző felhasználókat, akik közepes vagy nagy erőforrásosztály tagjai.
  • Töltsön be elég sort az új sorcsoportok teljes feltöltéséhez. Tömeges betöltés során 1 048 576 sorból minden sor közvetlenül az oszloptárba lesz tömörítve teljes sorcsoportként. A 102 400 sornál kisebb betöltések a deltatárba küldik a sorokat, ahol a sorok B-fában vannak tárolva. Ha kevesebb sort tölt be, előfordulhat, hogy mind a deltatárba kerül, és a rendszer nem tömöríti azokat azonnal oszloptár formátumba.

A köteg méretének növelése az SQLBulkCopy API vagy a BCP használatakor

A COPY utasítással való betöltés a legmagasabb átviteli sebességet biztosítja dedikált SQL-készletekkel. Ha nem tudja betölteni a COPY-t, és az SqLBulkCopy API-t vagy a bcp-t kell használnia, érdemes lehet növelni a kötegméretet a jobb átviteli sebesség érdekében.

Tipp

Az optimális kötegméret-kapacitás meghatározásához 100 K–1 M sor közötti kötegméret ajánlott alapkonfiguráció.

Betöltési hibák kezelése

Egy külső táblát használó betöltés meghiúsulhat a következő hibával: „A lekérdezés megszakadt – a rendszer elérte a felső visszautasítási küszöbértéket külső forrásból való beolvasás során”. Ez az üzenet azt jelzi, hogy a külső adatok szabálytalan rekordokat tartalmaznak. Az adatrekord akkor számít „szabálytalannak”, ha az oszlopok adattípusai és száma nem felel meg a külső tábla definícióinak, vagy ha az adatok nem felelnek meg a megadott külső fájlformátumnak.

A szabálytalan rekordok kijavításához győződjön meg arról, hogy a külső tábla- és fájlformátum-definíciók helyesek, és hogy a külső adatok megfelelnek ezeknek a definícióknak. Ha a külső adatrekordok egy részhalmaza nem megfelelő, elutasíthatja ezeket a rekordokat a lekérdezésekhez a "CREATE EXTERNAL TABLE" (KÜLSŐ TÁBLA LÉTREHOZÁSA) elvetési lehetőségével.

Adatok beszúrása éles táblába

A kis táblák INSERT utasítással végzett egyszeri feltöltése vagy akár egy keresés rendszeres újratöltése is megfelelő lehet, ha egy, a következőhöz hasonló utasítást használ: INSERT INTO MyLookup VALUES (1, 'Type 1'). Az egyszeri beszúrások azonban nem olyan hatékonyak, mint a tömeges betöltés.

Ha több ezer egyszeres beszúrást hajt végre egy nap, kötegelje a beszúrásokat, hogy kötegelve tölthesse be őket. Fejlesszen folyamatokat, amelyek az egyszeres beszúrásokat egy fájlhoz fűzik, majd hozzon létre egy másik folyamatot, amely időszakosan betölti a fájlt.

Statisztika létrehozása a betöltés után

A lekérdezési teljesítmény javítása érdekében fontos, hogy statisztikákat hozzon létre az összes tábla összes oszlopára az első betöltés után, vagy jelentős változások következnek be az adatokban. A statisztikákat manuálisan is létrehozhatja, vagy engedélyezheti a statisztikák automatikus létrehozását.

A statisztika részletes ismertetése: Statisztika. Az alábbi példa bemutatja, hogyan hozhat létre manuálisan statisztikákat a Customer_Speed tábla öt oszlopában.

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

Tárkulcsok rotálása

Biztonsági szempontból érdemes rendszeresen módosítani a Blob Storage hozzáférési kulcsát. A Blob Storage-fiókhoz két tárkulcs tartozik, amely lehetővé teszi a kulcsok közötti váltást.

Az Azure Storage-fiók kulcsainak rotálása:

Adja ki az ALTER DATABASE SCOPED CREDENTIAL parancsot minden olyan tárfiókhoz, amelynek módosult a kulcsa.

Példa:

Létrejön az eredeti kulcs

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

A kulcs rotálása az 1. kulcsból a 2. kulcsba

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

A mögöttes külső adatforrásokban nem kell más módosítást elvégezni.

Következő lépések