Az Azure Synapse Analytics kiszolgáló nélküli SQL-készleteihez ajánlott eljárások

Ebben a cikkben a kiszolgáló nélküli SQL-készlet használatának ajánlott eljárásait találja. A kiszolgáló nélküli SQL-készlet az Azure Synapse Analytics egyik erőforrása. Ha dedikált SQL-készlettel dolgozik, további útmutatásért tekintse meg a dedikált SQL-készletek ajánlott eljárásait.

A Kiszolgáló nélküli SQL-készlet lehetővé teszi az Azure Storage-fiókok fájljainak lekérdezését. Nem rendelkezik helyi tárolási vagy betöltési képességekkel. A lekérdezések által megcélzott összes fájl a kiszolgáló nélküli SQL-készleten kívüli. Minden, ami a fájlok tárolóból történő beolvasásával kapcsolatos, befolyásolhatja a lekérdezés teljesítményét.

Néhány általános irányelv:

• Győződjön meg arról, hogy az ügyfélalkalmazások kiszolgáló nélküli SQL-készlettel vannak csoportosítva.
  • Ha az Azure-on kívüli ügyfélalkalmazásokat használ, győződjön meg róla, hogy az ügyfélszámítógéphez közeli régióban használja a kiszolgáló nélküli SQL-készletet. Az ügyfélalkalmazások például a Power BI Desktop, az SQL Server Management Studio és az Azure Data Studio.
• Győződjön meg arról, hogy a tároló és a kiszolgáló nélküli SQL-készlet ugyanabban a régióban található. A tárolók példái közé tartozik az Azure Data Lake Storage és az Azure Cosmos DB.
• Próbálja optimalizálni a tárolási elrendezést particionálással, és a fájlok 100 MB és 10 GB közötti tartományban tartásával.
• Ha nagyszámú eredményt kap vissza, győződjön meg róla, hogy az SQL Server Management Studio vagy az Azure Data Studio programot használja, és nem az Azure Synapse Studiót. Az Azure Synapse Studio egy olyan webes eszköz, amelyet nem nagy eredményhalmazokhoz terveztek.
• Ha karakterlánc oszlop alapján szűri az eredményeket, próbáljon meg BIN2_UTF8 kollációt használni. A rendezés módosításáról további információt a Synapse SQL által támogatott rendezési típusok című témakörben talál.
• Mérlegelje az eredmények kliensoldali gyorsítótárazását a Power BI importálási mód vagy az Azure Analysis Services használatával, és frissítse ezeket rendszeresen. A kiszolgáló nélküli SQL-készletek nem tudnak interaktív felületet biztosítani a Power BI Direct Query módjában, ha összetett lekérdezéseket használ, vagy nagy mennyiségű adatot dolgoz fel.
• A maximális egyidejűség nem korlátozott, és a lekérdezés összetettségétől és a beolvasott adatok mennyiségétől függ. Egy kiszolgáló nélküli SQL-készlet egyszerre 1000 aktív munkamenetet képes kezelni, amelyek egyszerű lekérdezéseket hajtanak végre. A számok csökkennek, ha a lekérdezések összetettebbek, vagy nagyobb mennyiségű adatot vizsgálnak, ezért ebben az esetben fontolja meg az egyidejűség csökkentését, és ha lehetséges, hosszabb ideig futtassa a lekérdezéseket.

Ügyfélalkalmazások és hálózati kapcsolatok

Győződjön meg arról, hogy az ügyfélalkalmazás a legközelebbi lehetséges Azure Synapse-munkaterülethez csatlakozik az optimális kapcsolattal.

• Az ügyfélalkalmazást telepíteni az Azure Synapse-munkaterülettel. Ha olyan alkalmazásokat használ, mint a Power BI vagy az Azure Analysis Service, győződjön meg arról, hogy ugyanabban a régióban vannak, ahol az Azure Synapse-munkaterületet elhelyezte. Szükség esetén hozza létre az ügyfélalkalmazásokkal párosított különálló munkaterületeket. Az ügyfélalkalmazás és az Azure Synapse-munkaterület különböző régiókban való elhelyezése nagyobb késést és az eredmények lassabb streamelését okozhatja.
• Ha a helyszíni alkalmazásból olvas adatokat, győződjön meg arról, hogy az Azure Synapse-munkaterület a tartózkodási helyéhez közeli régióban található.
• Ügyeljen arra, hogy nagy mennyiségű adat olvasása közben ne legyen hálózati sávszélesség-probléma.
• Ne használja az Azure Synapse Studiót nagy mennyiségű adat visszaadására. Az Azure Synapse Studio egy webes eszköz, amely a HTTPS protokollt használja az adatok átviteléhez. Nagy mennyiségű adat olvasásához használja az Azure Data Studiót vagy az SQL Server Management Studiót.

Tárolás és tartalomelrendezés

Az alábbiakban a kiszolgáló nélküli SQL-készlet tárolási és tartalomelrendezési ajánlott eljárásait találja.

A tároló és a kiszolgáló nélküli SQL-készlet egy helyre helyezése

A késés minimalizálása érdekében helyezze át az Azure Storage-fiókot vagy az Azure Cosmos DB elemzési tárat és a kiszolgáló nélküli SQL-készlet végpontját. A munkaterület létrehozásakor kiépített tárfiókok és végpontok ugyanabban a régióban találhatók.

Az optimális teljesítmény érdekében, ha kiszolgáló nélküli SQL-készlettel fér hozzá más tárfiókokhoz, győződjön meg arról, hogy ugyanabban a régióban vannak. Ha nem ugyanabban a régióban vannak, az adatok hálózati átvitele nagyobb késést fog eredményezni a távoli régió és a végpont régiója között.

Az Azure Cosmos DB elemzési tárterületének és kiszolgáló nélküli SQL-készletének áthelyezése

Győződjön meg arról, hogy az Azure Cosmos DB elemzési tárterülete ugyanabban a régióban van, mint egy Azure Synapse-munkaterület. A régiók közötti lekérdezések hatalmas késéseket okozhatnak. A kapcsolati sztring régiótulajdonságával explicit módon megadhatja azt a régiót, ahol az elemzési tár található (lásd: Azure Cosmos DB lekérdezése kiszolgáló nélküli SQL-készlet használatával):account=<database account name>;database=<database name>;region=<region name>'

Azure Storage-korlátozás

Előfordulhat, hogy több alkalmazás és szolgáltatás is hozzáfér a tárfiókhoz. A tárterület szabályozása akkor történik, ha az alkalmazások, szolgáltatások és kiszolgáló nélküli SQL-készlet számítási feladatai által létrehozott kombinált IOPS vagy átviteli sebesség meghaladja a tárfiók korlátait. Ennek eredményeképpen jelentős negatív hatással lesz a lekérdezési teljesítményre.

Korlátozás észlelésekor a kiszolgáló nélküli SQL-szolgáltatás beépített kezelési mechanizmussal rendelkezik annak megoldásához. A kiszolgáló nélküli SQL-készlet lassabb tempóban küld tárolási kéréseket, amíg a szabályozás meg nem oldódik.

Jótanács

Az optimális lekérdezésvégrehajtás érdekében ne terhelje a tárfiókot más számítási feladatokkal a lekérdezés végrehajtása során.

Fájlok előkészítése a lekérdezéshez

Ha lehetséges, előkészítheti a fájlokat a jobb teljesítmény érdekében:

• Konvertálja a nagy CSV- és JSON-fájlokat Parquetre. A Parquet egy oszlopos formátum. Mivel tömörítve van, a fájlmérete kisebb, mint az azonos adatokat tartalmazó CSV- vagy JSON-fájloké. A kiszolgáló nélküli SQL-készlet kihagyja a lekérdezésekben nem szükséges oszlopokat és sorokat, ha Parquet-fájlokat olvas. A kiszolgáló nélküli SQL-készlet olvasásához kevesebb időre és kevesebb tárolási kérelemre van szükség.
• Ha egy lekérdezés egyetlen nagy fájlt céloz meg, akkor több kisebb fájlra is feloszthatja.
• Próbálja meg megtartani a CSV-fájlméretet 100 MB és 10 GB között.
• Az azonos méretű fájlok előnyösebbek egyetlen OPENROWSET-elérési úthoz vagy egy külső tábla HELY megadásához.
• Az adatok particionálása partíciók különböző mappákba vagy fájlnevekbe való tárolásával. Lásd: A fájlnév és a filepath függvény használata adott partíciók megcélzásához.

CSV-optimalizálás

Az alábbiakban ajánlott eljárásokat talál a CSV-fájlok kiszolgáló nélküli SQL-készletben való használatához.

CSV-fájlok lekérdezése a PARSER_VERSION 2.0 használatával

CSV-fájlok lekérdezéséhez teljesítményoptimalizált elemzőt használhat. További részletekért lásd: PARSER_VERSION.

Statisztika manuális létrehozása CSV-fájlokhoz

A kiszolgáló nélküli SQL-készlet statisztikákra támaszkodik az optimális lekérdezés-végrehajtási tervek létrehozásához. A mintavételezést használó oszlopok statisztikai adatai automatikusan létrejönnek, és a mintavételezési arány a legtöbb esetben kevesebb, mint 100%. Ez a folyamat minden fájlformátum esetében ugyanaz. Ne feledje, hogy ha a CSV-t az elemző 1.0-s verziójával olvassa, a mintavételezés nem támogatott, és a statisztikák automatikus létrehozása nem történik meg 100%-nál kisebb mintavételezési százalékkal. A becsült alacsony számosságot (sorok számát) tartalmazó kis táblák esetében az automatikus statisztikák létrehozása 100%-os mintavételezési százalékkal aktiválódik. Ez azt jelenti, hogy a fullscan aktiválódik, és az automatikus statisztikák még a CSV-hez is létrejönnek az elemző 1.0-s verziójával. Ha a statisztikák nem jönnek létre automatikusan, hozzon létre manuálisan statisztikákat a lekérdezésekben használt oszlopokhoz, különösen a DISTINCT, a JOIN, a WHERE, a ORDER BY és a GROUP BY függvényben használt oszlopokhoz. A részletekért tekintse meg a kiszolgáló nélküli SQL-készlet statisztikáit.

Delta Lake-optimalizálás

Az alábbi ajánlott eljárások a Delta Lake-fájlok kiszolgáló nélküli SQL-készletben való használatára szolgálnak.

Ellenőrzőpontok optimalizálása

A Delta Lake formátum lekérdezési teljesítményét a _delta_log könyvtárban található JSON-fájlok száma befolyásolja. Az optimális teljesítmény biztosítása érdekében ne gyűjtsön túl sok JSON-fájlt. Ideális esetben a naplónak csak a legújabb Parquet ellenőrzőpontfájlt kell tartalmaznia további JSON-fájlok nélkül. Előfordulhat azonban, hogy ez a beállítás nem optimális írási terhelésekhez.

A kiegyensúlyozott megközelítés körülbelül 10 JSON-fájl fenntartása az ellenőrzőpontok között, ami általában jó teljesítményt nyújt mind az olvasók, mind az írók számára. Legyen óvatos az ellenőrzőpontok létrehozását késleltető konfigurációkkal, mivel ezek túlzott JSON-fájlfelhalmozódáshoz és a lekérdezési teljesítmény romlásához vezethetnek.

A következő táblatulajdonság beállításával ellenőrizze, hogy minden 10 JSON-naplófájl után létrejön-e ellenőrzőpont:

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

Adattípusok

Az alábbi ajánlott eljárások az adattípusok kiszolgáló nélküli SQL-készletben való használatára szolgálnak.

Megfelelő adattípusok használata

A lekérdezésben használt adattípusok hatással vannak a teljesítményre és az egyidejűségre. Jobb teljesítményt érhet el, ha követi ezt az útmutatást:

• Használja a legkisebb adatméretet, amely a lehető legnagyobb értéket képes befogadni.
  • Ha a karakter maximális hossza 30 karakter, használjon 30 hosszúságú karakter adattípust.
  • Ha minden karakteroszlop értéke rögzített méretű, használjon karaktert vagy nchart. Ellenkező esetben használjon varchart vagy nvarchart.
  • Ha a maximális egész szám oszlopértéke 500, használjon smallint-et, mert ez a legkisebb adattípus, amely képes befogadni ezt az értéket. További információ: egész szám adattípustartományok.
• Ha lehetséges, használjon varchar és char az nvarchar és nchar helyett.
  • Használja a varchar típust valamilyen UTF-8 rendezéssel, ha Parquet, Azure Cosmos DB, Delta Lake vagy CSV adatokat olvas UTF-8 kódolással.
  • Használja a varchar típust UTF8-rendezés nélkül, ha nem Unicode formátumú CSV-fájlokból (például ASCII- ből) olvas adatokat.
  • Használja az nvarchar típust, ha egy CSV UTF-16-fájlból olvas adatokat.
• Ha lehetséges, egész számalapú adattípusokat használjon. A SORT, a JOIN és a GROUP BY műveletek gyorsabban befejeződnek egész számokon, mint a karakteradatokon.
• Ha sémakövetkeztetést használ, ellenőrizze a kikövetkeztetett adattípusokat, és ha lehetséges, felülbírálja őket explicit módon a kisebb típusokkal.

A következtetett adattípusok ellenőrzése

A sémakövetkeztetés segít a lekérdezések gyors írásában és az adatok feltárásában a fájlséma ismerete nélkül. Ennek a kényelemnek a költsége az, hogy a következtetett adattípusok nagyobbak lehetnek, mint a tényleges adattípusok. Ez az eltérés akkor fordul elő, ha nincs elegendő információ a forrásfájlokban a megfelelő adattípus használatához. A parquet-fájlok például nem tartalmaznak metaadatokat a karakteroszlopok maximális hosszáról. A kiszolgáló nélküli SQL-készlet varchar(8000) típusként ismeri fel.

Ne feledje, hogy a helyzet eltérő lehet az SQL-motorban külső táblákként közzétett megosztható felügyelt és külső Spark-táblák esetében. A Spark-táblák különböző adattípusokat biztosítanak, mint a Synapse SQL-motorok. A Spark-tábla adattípusai és az SQL-típusok közötti megfeleltetés itt található.

A rendszer által tárolt eljárás sp_describe_first_results_set használatával ellenőrizheti a lekérdezés eredményként kapott adattípusait.

Az alábbi példa bemutatja, hogyan optimalizálhatja a következtetett adattípusokat. Ez az eljárás a következő adattípusok megjelenítésére szolgál:

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

Íme az eredményhalmaz:

el van rejtve	oszlop sorszám	név	rendszer_típus_neve	maximális hosszúság
0	1	szállító_azonosító	varchar(8000)	8 000
0	2	felvétel_időpontja	datetime2(7)	8
0	3	utasok száma	Int	4

Miután megismerte a lekérdezésre vonatkozó következtetési adattípusokat, megadhatja a megfelelő adattípusokat:

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;

Szűrők optimalizálása

Az alábbi ajánlott eljárások a lekérdezések kiszolgáló nélküli SQL-készletben való használatára szolgálnak.

Helyettesítő karakterek mozgatása az elérési út alacsonyabb szintjeire

Használhat helyettesítő karaktereket az útvonalaiban a több fájl és mappa lekérdezéséhez. A kiszolgáló nélküli SQL-készlet a tárfiókban lévő fájlokat sorolja fel az első csillagtól (*) kezdve a storage API használatával. Kiszűri azokat a fájlokat, amelyek nem felelnek meg a megadott elérési útnak. A fájlok kezdeti listájának leszűkítésével javíthatja a teljesítményt, ha sok fájl felel meg a megadott elérési útnak az első helyettesítő karakterig.

Adott partíciók megcélzása fájlnév- és filepath-függvényekkel

Az adatokat gyakran partíciókba rendezik. Utasíthatja a kiszolgáló nélküli SQL-készletet bizonyos mappák és fájlok lekérdezésére. Ezzel csökkenti a fájlok számát, valamint a lekérdezés olvasásához és feldolgozásához szükséges adatok mennyiségét. További bónusz, hogy jobb teljesítményt érhet el.

További információkért olvassa el a fájlnév és a filepath függvényeket, és tekintse meg az adott fájlok lekérdezésére vonatkozó példákat.

Jótanács

A filepath és a filename függvény eredményeit mindig a megfelelő adattípusokra öntötte. Ha karakteradatokat használ, mindenképpen válassza a megfelelő hosszt.

A partíciók eltávolításához, a filepathhoz és a fájlnévhez használt függvények jelenleg nem támogatottak külső táblák esetében, kivéve azokat, amelyek automatikusan jönnek létre az Azure Synapse Analyticshez készült Apache Sparkban létrehozott táblákhoz.

Ha a tárolt adatok nincsenek particionálva, fontolja meg a particionálást. Ezekkel a függvényekkel optimalizálhatja a fájlokat célzó lekérdezéseket. Amikor particionált Apache Spark for Azure Synapse-táblákat kérdez le kiszolgáló nélküli SQL-készletből, a lekérdezés automatikusan csak a szükséges fájlokat célozza meg.

Használjon megfelelő rendezést a karakteroszlopok predikátum leküldésének kihasználásához

A Parquet-fájlokban lévő adatok sorcsoportokba vannak rendezve. A kiszolgáló nélküli SQL-készlet kihagyja a sorcsoportokat a WHERE utasításban megadott predikátum alapján, ami csökkenti az I/O-terhelést. Az eredmény a lekérdezési teljesítmény javulása.

A Parquet-fájlok karakteroszlopainak predikátumleküldése csak Latin1_General_100_BIN2_UTF8 rendezés esetén támogatott. Egy adott oszlop rendezését a WITH utasítással határozhatja meg. Ha nem adja meg ezt a rendezést WITH utasítással, a rendszer az adatbázis rendezését használja.

Ismétlődő lekérdezések optimalizálása

Az alábbi ajánlott eljárások a CETAS kiszolgáló nélküli SQL-készletben való használatára használhatók.

A CETAS használata a lekérdezési teljesítmény és az illesztések javításához

A CETAS a kiszolgáló nélküli SQL-készlet egyik legfontosabb funkciója. A CETAS egy párhuzamos művelet, amely külső tábla metaadatait hozza létre, és exportálja a SELECT lekérdezés eredményeit a tárfiókban lévő fájlok egy csoportjába.

A CETAS használatával a lekérdezések gyakran használt részeit, például az összekapcsolt referenciatáblákat egy új fájlkészletbe helyezheti át. Ezután csatlakozhat ehhez az egyetlen külső táblához ahelyett, hogy több lekérdezésben megismételte a közös illesztéseket.

Mivel a CETAS Parquet-fájlokat hoz létre, a rendszer automatikusan létrehozza a statisztikákat, amikor az első lekérdezés erre a külső táblára irányul. Az eredmény a CETAS-sel létrehozott táblát célzó későbbi lekérdezések teljesítményének javítása.

Azure-adatok lekérdezése

A kiszolgáló nélküli SQL-készletek lehetővé teszik az Adatok lekérdezését az Azure Storage-ban vagy az Azure Cosmos DB-ben külső táblák és az OPENROWSET függvény használatával. Győződjön meg arról, hogy megfelelő engedély van beállítva a tárolóban.

CSV-adatok lekérdezése

Megtudhatja, hogyan kérdezhet le egyetlen CSV-fájlt vagy -mappát és több CSV-fájlt. Particionált fájlokat is lekérdezhet

Parquet-adatok lekérdezése

Tudja meg, hogyan hajthat végre lekérdezéseket a Parquet-fájlokonbeágyazott típusokkal. Particionált fájlokat is lekérdezhet.

Delta Lake lekérdezése

Megtudhatja, hogyan kérdezhet le Delta Lake-fájlokat beágyazott típusok használatával.

Azure Cosmos DB-adatok lekérdezése

Megtudhatja, hogyan kérdezheti le az Azure Cosmos DB elemzési tárát. Online generátor használatával létrehozhatja a WITH záradékot egy Azure Cosmos DB-mintadokumentum alapján. Az Azure Cosmos DB-tárolókon hozhat létre nézeteket.

JSON-adatok lekérdezése

Megtudhatja, hogyan kérdezhet le JSON-fájlokat. Particionált fájlokat is lekérdezhet.

Nézetek, táblák és egyéb adatbázis-objektumok létrehozása

Megtudhatja, hogyan hozhat létre és használhat nézeteket és külső táblákat, illetve hogyan állíthatja be a sorszintű biztonságot. Ha particionált fájlokkal rendelkezik, győződjön meg arról, hogy particionált nézeteket használ.

Adatok másolása és átalakítása (CETAS)

Megtudhatja, hogyan tárolhatja a lekérdezési eredményeket a tárolóban a CETAS paranccsal.

Következő lépések

• A gyakori problémák megoldásához tekintse át a kiszolgáló nélküli SQL-készletek hibaelhárításáról szóló cikket.
• Ha nem kiszolgáló nélküli SQL-készlettel, hanem dedikált SQL-készlettel dolgozik, a dedikált SQL-készletek ajánlott eljárásait ismertető cikkben talál útmutatást.
• Az Azure Synapse Analytics gyakori kérdések
• Engedélyek megadása munkaterület által felügyelt identitásnak