Megosztás:

Adatvirtualizálás az Azure SQL Database-lel (előzetes verzió)

A következőkre vonatkozik:Azure SQL Database

Az Azure SQL Database adatvirtualizálási funkciója lehetővé teszi Transact-SQL (T-SQL) lekérdezések végrehajtását olyan fájlokon, amelyek olyan gyakori adatformátumokban tárolják az adatokat, mint a CSV (nincs szükség tagolt szöveg használatára), Parquet és Delta (1.0). Ezeket az adatokat lekérdezheti az Azure Data Lake Storage Gen2-ben vagy az Azure Blob Storage-ban, és összekapcsolásokkal kombinálhatja őket helyileg tárolt relációs adatokkal. Így transzparens módon érheti el a külső adatokat (írásvédett módban), miközben azok eredeti formátumában és helyén maradnak – más néven adatvirtualizálás.

Áttekintés

Az adatvirtualizálás kétféleképpen kérdezi le a különböző forgatókönyvekhez szánt fájlokat:

  • OPENROWSET szintaxis – fájlok alkalmi lekérdezésére optimalizálva. Általában egy új fájlkészlet tartalmának és szerkezetének gyors feltárására szolgál.
  • CREATE EXTERNAL TABLE szintaxis – a fájlok ismétlődő lekérdezésére van optimalizálva ugyanazzal a szintaxissal, mintha az adatokat helyben tárolták volna az adatbázisban. A külső táblák az OPENROWSET szintaxishoz képest több előkészítési lépést igényelnek, de lehetővé teszik az adathozzáférés nagyobb szabályozását. A külső táblákat általában elemzési számítási feladatokhoz és jelentéskészítéshez használják.

Mindkét esetben létre kell hozni egy külső adatforrást a CREATE EXTERNAL DATA SOURCE T-SQL szintaxissal, ahogyan az ebben a cikkben is látható.

Fájlformátumok

A parquet és a tagolt szöveg (CSV) fájlformátumok közvetlenül támogatottak. A JSON-fájlformátum közvetetten támogatott a CSV fájlformátum megadásával, ahol a lekérdezések minden dokumentumot külön sorként adnak vissza. A sorokat tovább feldolgozhatja JSON_VALUE és OPENJSON használatával.

Tárolási típusok

A fájlok az Azure Data Lake Storage Gen2-ben vagy az Azure Blob Storage-ban tárolhatók. A fájlok lekérdezéséhez meg kell adnia a helyet egy adott formátumban, és a külső forrás és végpont/protokoll típusának megfelelő helytípus-előtagot kell használnia, például a következő példákat:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet
--or
abs://<storage_account_name>.blob.core.windows.net/<container_name>/

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet
--or
adls://<storage_account_name>.dfs.core.windows.net/<container_name>/

Fontos

Mindig használjon végpontspecifikus előtagokat. A megadott Hely típusú előtag a kommunikáció optimális protokolljának kiválasztására és az adott tártípus által kínált speciális képességek kihasználására szolgál.

Az általános https:// előtag csak az BULK INSERT-hoz támogatott, de nem más használati esetekhez, beleértve OPENROWSET vagy EXTERNAL TABLE.

Első lépések

Ha még nem ismerkedik az adatvirtualizálással, és szeretné gyorsan tesztelni a funkciókat, kezdje az Azure Open Datasetsben elérhető nyilvános adatkészletek lekérdezésével, például a névtelen hozzáférést lehetővé tévő Bing COVID-19-adatkészlettel .

A Bing COVID-19-adatkészletek lekérdezéséhez használja a következő végpontokat:

  • Parketta: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Első lépésként futtassa ezt az egyszerű T-SQL-lekérdezést az adatkészlet első elemzéséhez. Ez a lekérdezés OPENROWSET használatával kérdez le egy nyilvánosan elérhető tárfiókban tárolt fájlt:

--Quick query on a file stored in a publicly available storage account:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
 FORMAT = 'parquet'
) AS filerows;

Az adathalmazok feltárását az első lekérdezés eredményhalmaza alapján hozzáfűzve WHEREGROUP BY és más záradékokkal folytathatja.

Miután megismerkedett a nyilvános adatkészletek lekérdezésével, érdemes lehet olyan nem nyilvános adatkészletekre váltani, amelyek hitelesítő adatok megadását, hozzáférési jogosultságok megadását és tűzfalszabályok konfigurálását igénylik. Számos valós forgatókönyvben elsősorban magánadatkészletekkel fog működni.

Hozzáférés nem nyilvános tárfiókokhoz

Az Azure SQL Database-be bejelentkezett felhasználóknak engedélyezni kell a nem nyilvános tárfiókokban tárolt fájlok elérését és lekérdezését. Az engedélyezési lépések attól függenek, hogy az Azure SQL Database hogyan hitelesíti a tárterületet. A hitelesítések típusai és a kapcsolódó paraméterek nincsenek közvetlenül megadva az egyes lekérdezésekhez. A rendszer belefoglalja őket a felhasználói adatbázisban tárolt adatbázis hatókörébe tartozó hitelesítőadat-objektumba. Az adatbázis a hitelesítő adatokat használja a tárfiók elérésére a lekérdezés végrehajtásakor.

Az Azure SQL Database a következő hitelesítési típusokat támogatja:

  • Közös hozzáférésű jogosultságkód (SAS)
  • Felügyelt identitás
  • Microsoft Entra átmenő hitelesítés felhasználói identitáson keresztül

A megosztott hozzáférésű jogosultságkód (SAS) delegált hozzáférést biztosít a tárfiókban lévő fájlokhoz. Az SAS részletes vezérlést biztosít a megadott hozzáférés típusáról, beleértve az érvényességi időtartamot, a megadott engedélyeket és az elfogadható IP-címtartományt. Az SAS-jogkivonat létrehozása után nem vonható vissza és nem törölhető, és az érvényességi idő lejártáig engedélyezi a hozzáférést.

  1. Az SAS-jogkivonatot többféleképpen is lekérheti:

  2. Adjon olvasási és listázási engedélyeket az SAS-en keresztül a külső adatok eléréséhez. Az Azure SQL Database-sel való adatvirtualizálás jelenleg csak olvasható.

  3. Ha adatbázis-hatókörű hitelesítő adatokat szeretne létrehozni az Azure SQL Database-ben, először létre kell hoznia az adatbázis főkulcsát, ha még nem létezik. Az adatbázis főkulcsára akkor van szükség, ha a hitelesítő adatok megkövetelik SECRET.

    -- Create MASTER KEY if it doesn't exist in the database:
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>';

  4. Amikor egy SAS token létrejön, tartalmaz egy kérdőjelet (?) a token elején. A token használatához el kell távolítania a kérdőjelet (?) a hitelesítő adatok létrehozásakor. Például:

    CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=secret string here';

Hozzáférés a nyilvános tárhoz névtelen fiókokon keresztül

Ha a kívánt adatkészlet nyilvános hozzáférést (más néven névtelen hozzáférést) tesz lehetővé, nem szükséges hitelesítő adatok megadása mindaddig, amíg az Azure Storage megfelelően van konfigurálva, lásd a tárolók és blobok névtelen olvasási hozzáférésének konfigurálását.

Külső adatforrás

A külső adatforrás egy absztrakció, amely lehetővé teszi a fájlhely egyszerű hivatkozását több lekérdezésre. A nyilvános helyek lekérdezéséhez a külső adatforrás létrehozásakor csak a fájl helyét kell megadnia:

CREATE EXTERNAL DATA SOURCE MyExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
);

A nem nyilvános tárfiókok elérésekor és hivatkozásakor hivatkoznia kell egy adatbázis hatókörébe tartozó hitelesítő adatra, amely beágyazott hitelesítési paramétereket tartalmaz. A következő szkript létrehoz egy külső adatforrást, amely a fájl elérési útjára mutat, és hivatkozik egy adatbázis-hatókörű hitelesítő adatokra.

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       CREDENTIAL = [MyCredential]
);

Adatforrások lekérdezése AZ OPENROWSET használatával

Az OPENROWSET szintaxis azonnali alkalmi lekérdezést tesz lehetővé, miközben csak a szükséges minimális számú adatbázis-objektumot hozza létre.

OPENROWSET csak a külső adatforrás (és esetleg a hitelesítő adatok) létrehozását igényli, szemben a külső tábla megközelítésével, amely külső fájlformátumot és magát a külső táblát igényli.

A DATA_SOURCE paraméter értéke automatikusan hozzáadódik a BULK paraméterhez, hogy a fájl teljes elérési útját képezze.

Ha a fájl formátumát adja meg, használja a OPENROWSET formátumot, például az alábbi példa, amely egyetlen fájlt lekérdez:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

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

A OPENROWSET parancs lehetővé teszi több fájl vagy mappa lekérdezését helyettesítő karakterek használatával a BULK elérési úton.

Az alábbi példa a NYC sárga taxiút rekordokat használja a nyílt adatkészletből.

Először hozza létre a külső adatforrást:

--Create the data source first:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Most már lekérdezhetjük a mappákban lévő .parquet kiterjesztésű összes fájlt. Itt például csak a névmintának megfelelő fájlokat kérdezzük le:

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Több fájl vagy mappa lekérdezésekor az egyetlen OPENROWSET fájlhoz hozzáférő összes fájlnak ugyanazzal a struktúrával kell rendelkeznie (például ugyanannyi oszlopnak és adattípusnak). A mappákat nem lehet rekurzív módon bejárni.

Sémakövetkeztetés

Az automatikus sémakövetkeztetés segít a lekérdezések gyors írásában és az adatok feltárásában, ha nem ismeri a fájlsémákat. A sémakövetkeztetés csak parquet-fájlokkal működik.

Bár kényelmes, a kikövetkeztetett adattípusok nagyobbak lehetnek a tényleges adattípusoknál, mivel a forrásfájlokban elegendő információ lehet a megfelelő adattípus használatához. Ez gyenge lekérdezési teljesítményt okozhat. A parquet-fájlok például nem tartalmaznak metaadatokat a karakteroszlopok maximális hosszáról, ezért a példány varchar(8000) értékként értelmezi.

Az sp_describe_first_results_set tárolt eljárással ellenőrizze a lekérdezés eredményeként kapott adattípusokat, mint az alábbi példában:

EXEC sp_describe_first_result_set N'
 SELECT
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

Miután megismerte az adattípusokat, a záradék használatával megadhatja őket a WITH teljesítmény javítása érdekében:

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount
FROM
OPENROWSET(
 BULK 'yellow/*/*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000
tpepPickupDateTime datetime2,
passengerCount int
) AS nyc;

Mivel a CSV-fájlok sémája nem határozható meg automatikusan, az oszlopokat mindig a WITH záradék használatával kell megadni:

SELECT TOP 10 id, updated, confirmed, confirmed_change
FROM OPENROWSET(
 BULK 'bing_covid-19_data.csv',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'CSV',
 FIRSTROW = 2
)
WITH (
 id int,
 updated date,
 confirmed int,
 confirmed_change int
) AS filerows;

Fájl metaadat-függvényei

Több fájl vagy mappa lekérdezésekor használhatja a filepath() és filename() funkciókat a fájl metaadatainak olvasására, illetve az elérési út egy részének vagy a teljes elérési út és fájlnév lekérésére, amelyekből az eredményhalmaz sora származik.

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

Ha paraméter nélkül hívjuk meg, a filepath() függvény visszaadja annak a fájlnak az elérési útját, amelyből a sor származik. Amikor DATA_SOURCE a rendszer használja OPENROWSET, az a fájl elérési útját adja vissza a DATA_SOURCEfájlhoz viszonyítva, ellenkező esetben a teljes fájl elérési útját adja vissza.

Ha paraméterrel hívjuk meg, az az elérési út azon részét adja vissza, amely megfelel a paraméterben megadott pozíció helyettesítő karakterének. 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.

A filepath() függvény sorok szűrésére és összesítésére is használható:

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

Nézet létrehozása az OPENROWSET tetején

Nézeteket hozhat létre és használhat az OPENROWSET-lekérdezések burkolásához, hogy könnyen újra felhasználhassa az alapul szolgáló lekérdezést:

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

A funkcióval a könnyebb és hatékonyabb szűrés érdekében a fájlhelyadatokat tartalmazó oszlopokat is hozzáadhatja egy filepath() nézethez. A nézetek használata csökkentheti a fájlok számát és a nézetre épülő lekérdezés által elolvasni és feldolgozni szükséges adatok mennyiségét, amikor ezeket a oszlopokat használva szűrjük.

CREATE VIEW TaxiRides AS
SELECT *
 , filerows.filepath(1) AS [year]
 , filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

A nézetek lehetővé teszik a jelentéskészítést és az analitikai eszközöket, például a Power BI-t, a OPENROWSET eredményeinek felhasználására.

Külső táblák

A külső táblák a fájlokhoz való hozzáférést tartalmazzák, így a lekérdezési élmény szinte megegyezik a felhasználói táblákban tárolt helyi relációs adatok lekérdezésével. Külső tábla létrehozásához a külső adatforrás és a külső fájlformátum objektumainak létezniük kell:

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
);

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);

A külső tábla létrehozása után ugyanúgy kérdezheti le, mint bármely más táblát:

SELECT TOP 10 *
FROM tbl_TaxiRides;

A külső táblákhoz hasonlóan OPENROWSETa külső táblák is lehetővé teszik több fájl és mappa lekérdezését helyettesítő karakterek használatával. A sémakövetkeztetés nem támogatott külső táblák esetén.

Teljesítménnyel kapcsolatos szempontok

A fájlok és a lekérdezhető adatok számának nincs szigorú korlátja, de a lekérdezési teljesítmény az adatok mennyiségétől, az adatformátumtól, az adatok rendszerezésének módjától és a lekérdezések és illesztések összetettségétől függ.

Particionált adatok lekérdezése

Az adatokat gyakran almappákban, más néven partíciókban rendezik. Utasíthatja a lekérdezést, hogy csak bizonyos mappákat és fájlokat olvasson be. Ez csökkenti a fájlok számát és a lekérdezéshez szükséges adatok mennyiségét, ami jobb teljesítményt eredményez. Ezt a lekérdezésoptimalizálási típust partíciómetszetnek vagy partícióeltörlésnek nevezzük. A partíciókat a lekérdezés záradékában található metaadat-függvény filepath() használatával távolíthatja el a WHERE lekérdezés végrehajtásából.

Az alábbi minta lekérdezés csak 2017 utolsó három hónapjára vonatkozóan olvassa be az NYC Yellow Taxi adatfájljait:

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'yellow/puYear=*/puMonth=*/*.parquet',
        DATA_SOURCE = 'NYCTaxiExternalDataSource',
        FORMAT = 'parquet'
    )
WITH (
    vendorID INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Ha a tárolt adatok nincsenek particionálva, fontolja meg a particionálást a lekérdezési teljesítmény javítása érdekében.

Ha külső táblákat használ, a filepath() és filename() függvények támogatottak, de a WHERE záradékban nem szerepelhetnek.

Hibaelhárítás

A lekérdezések végrehajtásával kapcsolatos problémákat általában az okozza, hogy az Azure SQL Database nem tud hozzáférni a fájl helyéhez. A kapcsolódó hibaüzenetek nem megfelelő hozzáférési jogosultságokat, nem létező helyet vagy fájlelérési útvonalat, egy másik folyamat által használt fájlt jelenthetnek, vagy a címtár nem szerepelhet a listában. A legtöbb esetben ez azt jelzi, hogy a fájlokhoz való hozzáférést a hálózati forgalomszabályozási szabályzatok vagy a hozzáférési jogosultságok hiánya blokkolja. Ezt kell ellenőrizni:

  • Hibás vagy elgépelt elérési út.
  • SAS-kulcs érvényessége: lehet, hogy lejárt, és elírást tartalmaz, kérdőjellel kezdve.
  • SAS-kulcsengedélyek engedélyezettek: Olvasás legalább, és Lista, ha helyettesítő karaktereket használnak.
  • Blokkolt bejövő forgalom a tárfiókban. Ellenőrizze az Azure Storage virtuális hálózati szabályainak kezelését.
  • Felügyelt identitás hozzáférési jogosultságai: győződjön meg arról, hogy az Azure SQL Database felügyelt identitása hozzáférési jogosultságokat kap a tárfiókhoz.
  • Az adatbázis kompatibilitási szintjének 130-nak vagy annál magasabbnak kell lennie ahhoz, hogy az adatvirtualizálási lekérdezések működjenek.

Korlátozások

  • Az Azure SQL Database jelenleg nem támogatja a külső táblák statisztikáit.
  • CREATE EXTERNAL TABLE AS SELECT Jelenleg nem érhető el az Azure SQL Database-ben.
  • A sorszintű biztonsági funkció külső táblákkal nem támogatott.
  • A dinamikus adatmaszkolási szabály nem definiálható külső tábla oszlopaihoz.
  • A felügyelt identitás nem támogatja a bérlők közötti forgatókönyveket, ha az Azure Storage-fiók másik bérlőben van, a közös hozzáférésű jogosultságkód a támogatott módszer.

Ismert problémák

  • Ha az Always Encrypted paraméterezése engedélyezve van az SQL Server Management Studióban (SSMS), az adatvirtualizálási lekérdezések hibaüzenettel Incorrect syntax near 'PUSHDOWN' meghiúsulnak.

Kapcsolódó tartalom

  • Last updated on