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

Tárolófájlok lekérdezése kiszolgáló nélküli SQL-készlet használatával

  • Cikk

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

A jelenleg támogatott vagy nem támogatott szolgáltatásokról további információt a kiszolgáló nélküli SQL-készlet áttekintésében vagy a következő cikkekben talál:

Áttekintés

Az Azure Storage-fájlokban található adatok helyben történő lekérdezésének zökkenőmentessége érdekében a kiszolgáló nélküli SQL-készlet az OPENROWSET függvényt használja további funkciókkal:

PARQUET-fájlok lekérdezése

Parquet-forrásadatok lekérdezéséhez használja a következőt FORMAT = 'PARQUET':

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 meg a Lekérdezési parquet-fájlokat.

CSV-fájlok lekérdezése

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

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ó igazításához további lehetőségek is használhatók:

  • ESCAPE_CHAR = 'char' A fájl azon karakterét adja meg, amely önmagát és a fájl összes elválasztó értékét megszabja. Ha a feloldó karaktert nem önmagától, vagy az elválasztó értékek bármelyikétől eltérő érték követi, a rendszer elveti a feloldó karaktert az érték olvasásakor. A ESCAPE_CHAR paramétert akkor alkalmazza a rendszer, ha a FIELDQUOTE paraméter engedélyezve van vagy nincs. Nem használatos az idéző karakter elől való meneküléshez. Az idézőjelet egy másik idéző karakterrel kell megszűkíteni. 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' Megadja a használni kívánt mező-terminátort. Az alapértelmezett mezőkifejezés egy vessző (,).
  • ROWTERMINATOR ='row_terminator' A használni kívánt sor terminátorát adja meg. Az alapértelmezett sorkifejezés egy újsoros karakter (\r\n).

DELTA LAKE-formátum lekérdezése

A Delta Lake-forrásadatok lekérdezéséhez használja FORMAT = 'DELTA' é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 ._delta_log Használati példákért tekintse meg a Delta Lake-fájlok lekérdezését (v1).

Fájlséma

A Synapse SQL SQL-ben az SQL-nyelv lehetővé teszi a fájl sémájának definiálását a függvény részeként, valamint az OPENROWSET oszlopok teljes vagy részhalmazának beolvasását, vagy a fájlból származó oszloptípusokat sémakövetkeztetéssel próbálja automatikusan meghatározni.

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 az utasításban OPENROWSET .

  • Ha vannak CSV-adatfájlok, adja meg az oszlopok nevét és adattípusait az összes oszlop beolvasásához. Ha az oszlopok egy részhalmazát szeretné használni, az ordinális számokkal az eredeti adatfájlokból származó oszlopokat sorszám szerint választhatja ki. 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 vannak 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 a beírási záradékot WITH . Példákért lásd : CSV-fájlok olvasása az összes oszlop megadása nélkül.

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 a mögöttes 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 T-SQL-lekérdezést szeretne futtatni egy mappán vagy mappakészleten belüli fájlokon, miközben egyetlen entitásként vagy sorkészletként kezeli őket, adjon meg egy mappát vagy mintát (helyettesítő karakterek használatával) fájlok vagy mappák halmazán keresztül.

Az alábbi szabályokat kell betartani:

  • 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ő útvonalon belüli fájlok is 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

Használati példákért lásd : Lekérdezési mappák és több fájl.

Fájl metaadat-függvényei

Fájlnév 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ű cikkben található Fájlnév szakaszt .

A visszaadott adattípus nvarchar(1024). Az optimális teljesítmény érdekében a fájlnév 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.
  • Paraméterrel meghívva az elérési út azon részét adja vissza, amely megfelel a paraméterben megadott helyettesítő karakternek. Az 1. paraméter értéke például az első helyettesítő karakternek megfelelő elérési út egy részét adja vissza.

További információkért olvassa el az Adott fájlok lekérdezése című cikk Filepath szakaszát.

A visszaadott adattípus nvarchar (1024). Az optimális teljesítmény érdekében mindig a filepath függvény eredményét adja meg a megfelelő adattípusnak. 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álata érdekében a kiszolgáló nélküli SQL-készlet a következő bővítményeket adta hozzá.

Beágyazott vagy ismétlődő adatok kivetítve

Az adatok kivetítéséhez futtasson egy SELECT utasítást a Parquet-fájlon, amely beágyazott adattípusok oszlopait tartalmazza. Kimeneten a beágyazott értékek JSON-ba vannak szerializálva, és varchar(8000) SQL-adattípusként jelennek meg.

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

További információkért tekintse meg a Beágyazott lekérdezések lekérdezési parquet típusú cikk beágyazott vagy ismétlődő adatszakaszát.

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

Ha beágyazott oszlopból (például Struct) szeretne beágyazott elemeket elérni, a pont jelöléssel fűzze össze a mezőneveket az elérési útba. Adja meg az elérési utat column_name a WITH függvény záradékában leírtak szerint OPENROWSET .

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 OPENROWSET függvény megegyezik a forrásmező nevével és elérési útjával a záradékban WITH megadott oszlopnevekkel. Az ugyanazon forrásparquet-fájlban található különböző beágyazási szinteken található elemek a WITH záradék használatával érhetők el.

Értékek visszaadva

  • A függvény egy skaláris értéket ad vissza, például int, decimalés varchar, a megadott elemből, valamint a megadott útvonalon 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_namehelyen, a függvény hibát ad vissza.
  • Ha a tulajdonság nem található a megadott column_pathhelyen , 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ékre vált, ha lax módban van.

A lekérdezésmintákért tekintse meg a beágyazott objektumoszlopok olvasási tulajdonságait ismertető cikket a Lekérdezés parquet beágyazott típusok című cikkben.

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 vetítenie és meg kell adnia:

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

Ha ismétlődő oszlopból szeretné elérni a nem skálázható elemeket, használja a JSON_QUERY függvényt minden olyan nem skálázható elemhez, amelyet a projekthez és a következőhöz kell megadnia:

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

Tekintse meg a következő 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]

A lekérdezési parquet beágyazott típusok cikkének ismétlődő oszlopaiból származó elemek elérésére szolgáló lekérdezésmintákat talál.

Kapcsolódó tartalom

A különböző fájltípusok lekérdezésével, valamint a nézetek létrehozásával és használatával kapcsolatos további információkért tekintse meg az alábbi cikkeket: