Tárolófájlok lekérdezése kiszolgáló nélküli SQL-készlettel a Azure Synapse Analyticsben

Cikk
06/01/2023

A kiszolgáló nélküli SQL-készlet lehetővé teszi az adatok lekérdezését a data lake-ben. Ez egy T-SQL-lekérdezési felületet kínál, amely félig strukturált és strukturálatlan adat lekérdezéseket fogad. Lekérdezés esetén a következő T-SQL-szempontok támogatottak:

Teljes SELECT felület, beleértve az SQL-függvények és operátorok többségét.
A CREATE EXTERNAL TABLE AS SELECT (CETAS) létrehoz egy külső táblát , majd párhuzamosan exportálja egy Transact-SQL SELECT utasítás eredményeit az Azure Storage-ba.

A jelenleg nem támogatott szolgáltatással kapcsolatos további információkért olvassa el a kiszolgáló nélküli SQL-készlet áttekintéséről szóló cikket vagy az alábbi cikkeket:

Tárhozzáférés fejlesztése , ahol megtudhatja, hogyan olvashat adatokat a tárolóból a Külső tábla és az OPENROWSET függvény használatával.
Szabályozhatja a tárhozzáférést , ahol megtudhatja, hogyan engedélyezheti a Synapse SQL-nek, hogy SAS-hitelesítéssel vagy a munkaterület felügyelt identitásával férhessen hozzá a tárolóhoz.

Áttekintés

Az Azure Storage-fájlokban található adatok zökkenőmentes lekérdezésének támogatása érdekében a kiszolgáló nélküli SQL-készlet az OPENROWSET függvényt használja további képességekkel:

Több fájl vagy mappa lekérdezése
PARQUET-fájlformátum
CSV lekérdezése és tagolt szöveg (mezőmegjelenítő, sorkizáró, escape karakter)
DELTA LAKE formátum
Az oszlopok kiválasztott részhalmazának olvasása
Sémakövetkeztetés
filename függvény
filepath függvény
Összetett típusok és beágyazott vagy ismétlődő adatstruktúrák használata

PARQUET-fájlok lekérdezése

Parquet-forrásadatok lekérdezéséhez használja a FORMAT = 'PARQUET' parancsot:

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Használati példákért tekintse át a Lekérdezési parquet-fájlok című cikket.

CSV-fájlok lekérdezése

A CSV-forrásadatok lekérdezéséhez használja a FORMAT = "CSV" parancsot. A CSV-fájlok lekérdezésekor a függvény részeként megadhatja a CSV-fájl sémáját OPENROWSET :

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Az elemzési szabályok egyéni CSv-formátumhoz való módosításához további lehetőségek is használhatók:

ESCAPE_CHAR = "char" A fájlban azt a karaktert adja meg, amely önmagát és a fájl összes elválasztóértékét tartalmazza. Ha a feloldó karaktert nem önmagától vagy a határoló értékek bármelyikétől eltérő érték követi, a feloldó karakter el lesz dobva az érték olvasásakor. A ESCAPE_CHAR paraméter akkor lesz alkalmazva, ha a FIELDQUOTE engedélyezve van vagy nincs. Nem fogja használni az idézőjelek elől való meneküléshez. Az idézőjelet egy másik idézőjellel kell feloldani. Az idézőjel csak akkor jelenhet meg az oszlopértékben, ha az érték idéző karakterekkel van beágyazva.
FIELDTERMINATOR ='field_terminator' A használni kívánt mezőkifejezést adja meg. Az alapértelmezett mezőmegjelenítő egy vessző (",")
ROWTERMINATOR ='row_terminator' A használni kívánt sor terminátorát adja meg. Az alapértelmezett sorazonosító egy újvonalas karakter: \r\n.

DELTA LAKE-formátum lekérdezése

A Delta Lake forrásadatainak lekérdezéséhez használja a FORMAT = "DELTA" parancsot, és hivatkozzon a Delta Lake-fájlokat tartalmazó gyökérmappára.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

A gyökérmappának tartalmaznia kell egy nevű _delta_logalmappát. A használati példákért tekintse át a Delta Lake-formátumra vonatkozó lekérdezési cikket.

Fájlséma

A Synapse SQL SQL-nyelve lehetővé teszi a fájl sémájának definiálását a függvény részeként OPENROWSET , és beolvassa az oszlopok teljes vagy részhalmazát, vagy sémakövetkeztetés használatával automatikusan megpróbálja meghatározni az oszloptípusokat a fájlból.

Az oszlopok kiválasztott részhalmazának olvasása

Az elolvasni kívánt oszlopok megadásához megadhat egy opcionális WITH záradékot a utasításban OPENROWSET .

Ha vannak CSV-adatfájlok, az összes oszlop olvasásához adja meg az oszlopneveket és azok adattípusait. Ha az oszlopok egy részhalmazát szeretné használni, sorszámokkal válassza ki az oszlopokat az eredeti adatfájlokból sorszám szerint. Az oszlopokat a sorszám megjelölése köti össze.
Ha vannak Parquet-adatfájlok, adja meg azokat az oszlopneveket, amelyek megfelelnek az eredeti adatfájlok oszlopneveinek. Az oszlopok név szerint lesznek kötve.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows

Minden oszlophoz meg kell adnia az oszlop nevét, és be kell gépelnie a záradékot WITH . Mintákért tekintse meg a CSV-fájlok olvasása az összes oszlop megadása nélkül című témakört.

Sémakövetkeztetés

Ha kihagyja a WITH záradékot az OPENROWSET utasításból, utasíthatja a szolgáltatást, hogy automatikusan észlelje (következtetve) a sémát az alapul szolgáló fájlokból.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

Győződjön meg arról, hogy a megfelelő következtetési adattípusokat használja az optimális teljesítmény érdekében.

Több fájl vagy mappa lekérdezése

Ha egy mappán vagy mappakészleten belüli fájlkészleten keresztül szeretne T-SQL-lekérdezést futtatni, miközben egyetlen entitásként vagy sorkészletként kezeli őket, adjon meg egy elérési utat egy mappához vagy mintához (helyettesítő karakterek használatával) fájlok vagy mappák halmazán keresztül.

A következő szabályok érvényesek:

A minták megjelenhetnek egy könyvtár elérési útjának egy részében vagy egy fájlnévben.
Több minta is megjelenhet ugyanabban a könyvtárlépésben vagy fájlnévben.
Ha több helyettesítő karakter is létezik, akkor az összes egyező elérési úton belüli fájlok bekerülnek az eredményül kapott fájlkészletbe.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

A használati példákért tekintse meg a Lekérdezésmappák és több fájl című témakört.

Fájl metaadat-függvényei

Filename függvény

Ez a függvény azt a fájlnevet adja vissza, amelyből a sor származik.

Adott fájlok lekérdezéséhez olvassa el az Adott fájlok lekérdezése című cikk Fájlnév szakaszát.

A visszaadott adattípus nvarchar(1024). Az optimális teljesítmény érdekében a filename függvény eredményét mindig a megfelelő adattípusra kell leadni. Ha karakteres adattípust használ, győződjön meg arról, hogy a megfelelő hosszt használja.

Filepath függvény

Ez a függvény egy teljes elérési utat vagy az elérési út egy részét adja vissza:

Paraméter nélkül meghívva a sor teljes elérési útját adja vissza.
Ha paraméterrel hívjuk meg, az az elérési út egy részét adja vissza, amely megfelel a paraméterben megadott helyettesítő karakternek. Az 1. paraméterérték például az elérési út azon részét adja vissza, amely megfelel az első helyettesítő karakternek.

További információkért olvassa el a Konkrét fájlok lekérdezése című cikk Fájlútvonal szakaszát.

A visszaadott adattípus nvarchar(1024). Az optimális teljesítmény érdekében a filepath függvény eredményét mindig a megfelelő adattípusra kell leadni. Ha karakteres adattípust használ, győződjön meg arról, hogy a megfelelő hosszt használja.

Összetett típusok és beágyazott vagy ismétlődő adatstruktúrák használata

A beágyazott vagy ismétlődő adattípusokban (például Parquet-fájlokban ) tárolt adatok zökkenőmentes használatához a kiszolgáló nélküli SQL-készlet hozzáadta a következő bővítményeket.

Projektbe ágyazott vagy ismétlődő adatok

Az adatok kivetítéséhez futtasson egy SELECT utasítást a Parquet-fájlon keresztül, amely beágyazott adattípusok oszlopait tartalmazza. A kimenetben a beágyazott értékek JSON-ba lesznek szerializálva, és varchar(8000) SQL-adattípusként lesznek visszaadva.

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

További információt a Beágyazott Lekérdezés parquet-típusok című cikk Projekt beágyazott vagy ismétlődő adatok című szakaszában talál.

Elemek elérése beágyazott oszlopokból

Ha beágyazott oszlopból(például Struct) szeretne beágyazott elemeket elérni, használja a "pontjelezést" a mezőneveknek az elérési útba való összefűzéséhez. Adja meg az elérési utat column_name a OPENROWSET függvény WITH záradékában.

A szintaxistöredék példája a következő:

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ({'column_name' 'column_type',})
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

Alapértelmezés szerint a függvény megegyezik a OPENROWSET forrásmező nevével és elérési útjával a WITH záradékban megadott oszlopnevekkel. Az ugyanabban a forrás parquet-fájlban található különböző beágyazási szinteken található elemek a WITH záradékon keresztül érhetők el.

Visszatérési értékek

A függvény egy skaláris értéket ad vissza, például az int, a decimális és a varchar értéket a megadott elemből, valamint a megadott elérési útról az összes olyan Parquet-típushoz, amely nem szerepel a Beágyazott típus csoportban.
Ha az elérési út egy beágyazott típusú elemre mutat, a függvény egy JSON-töredéket ad vissza a megadott elérési út felső elemétől kezdve. A JSON-töredék varchar(8000) típusú.
Ha a tulajdonság nem található a megadott column_name, a függvény hibát ad vissza.
Ha a tulajdonság nem található a megadott column_path, az Elérési út módtól függően a függvény hibát ad vissza, ha szigorú módban vagy null értéken van, ha lax módban van.

A lekérdezésmintákért tekintse át a Beágyazott lekérdezési parquet-típusok című cikk Access-elemek beágyazott oszlopokból című szakaszát.

Ismétlődő oszlopok elemeinek elérése

Ha ismétlődő oszlop elemeit, például tömb vagy térkép elemét szeretné elérni, használja a JSON_VALUE függvényt minden olyan skaláris elemhez, amelyet ki kell számítania, és meg kell adnia:

Beágyazott vagy ismétlődő oszlop első paraméterként
Egy JSON-elérési út , amely a elérendő elemet vagy tulajdonságot adja meg második paraméterként

Ha nem skaláris elemeket szeretne elérni egy ismétlődő oszlopból, használja a JSON_QUERY függvényt minden olyan nem skaláris elemhez, amelyet kivetíteni kell, és meg kell adnia:

Beágyazott vagy ismétlődő oszlop első paraméterként
Egy JSON-elérési út , amely a elérendő elemet vagy tulajdonságot adja meg második paraméterként

Lásd alább a szintaxisrészletet:

    SELECT
       { JSON_VALUE (column_name, path_to_sub_element), }
       { JSON_QUERY (column_name [ , path_to_sub_element ]), )
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Az ismétlődő oszlopok elemeinek eléréséhez szükséges lekérdezésmintákat a Lekérdezés parquet beágyazott típusok című cikkben találja.

Példák lekérdezésekre

A mintalekérdezésekkel többet is megtudhat a különböző típusú adatok lekérdezéséről.

Eszközök

A lekérdezések kiadásához szükséges eszközök: - Azure Synapse Studio – Azure Data Studio – SQL Server Management Studio

Bemutató beállítása

Első lépésként hozzon létre egy adatbázist , amelyben végrehajtja a lekérdezéseket. Ezután inicializálni fogja az objektumokat az adatbázis beállítási szkriptjének végrehajtásával.

Ez a beállítási szkript létrehozza azokat az adatforrásokat, adatbázis-hatókörű hitelesítő adatokat és külső fájlformátumokat, amelyek az ezekben a mintákban lévő adatok beolvasására szolgálnak.

Megjegyzés

Az adatbázisok csak metaadatok megtekintésére használhatók, tényleges adatokra nem. Jegyezze fel a használt adatbázisnevet, később szüksége lesz rá.

CREATE DATABASE mydbname;

Megadott bemutatóadatok

A bemutatóadatok a következő adatkészleteket tartalmazzák:

NYC Taxi - Sárga Taxi Trip Records - része a nyilvános NYC adatkészlet CSV és Parquet formátumban
Statisztikai adatkészlet CSV formátumban
Minta Parquet-fájlok beágyazott oszlopokkal
Könyvek JSON formátumban

Mappa elérési útja	Description
/Csv/	Szülőmappa CSV formátumú adatokhoz
/csv/population/ /csv/population-unix/ /csv/population-unix-hdr/ /csv/population-unix-hdr-escape /csv/population-unix-hdr-quoted	A Population adatfájlokat tartalmazó mappák különböző CSV-formátumokban.
/csv/taxi/	Mappa NYC nyilvános adatfájlokkal CSV formátumban
/Parketta/	Parquet formátumú adatok szülőmappája
/parquet/taxi	NyC nyilvános adatfájlok Parquet formátumban, év és hónap szerint particionálva a Hive/Hadoop particionálási sémával.
/parquet/nested/	Minta Parquet-fájlok beágyazott oszlopokkal
/Json/	JSON formátumú adatok szülőmappája
/json/books/	JSON-fájlok könyvek adataival

Következő lépések

A különböző fájltípusok lekérdezéséről, valamint a nézetek létrehozásáról és használatáról az alábbi cikkekben talál további információt:

Megosztás a következőn keresztül: