Adatvirtualizálás felügyelt Azure SQL-példánysal

  • Cikk

A következőre vonatkozik: Felügyelt Azure SQL-példány

A felügyelt Azure SQL-példány adatvirtualizálási funkciója lehetővé teszi Transact-SQL -lekérdezések (T-SQL) végrehajtását az adatokat az Azure Data Lake Storage Gen2-ben vagy az Azure Blob Storage-ban közös adatformátumokban tároló fájlokon, és összekapcsolások használatával 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:

  • OPENROW Standard kiadás T 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 OPENROW Standard kiadás T 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ó.

Emellett elérhető a CREATE EXTERNAL TABLE AS Standard kiadás LECT szintaxis az Azure SQL Managed Instance esetében, amely egy T-SQL Standard kiadás LECT utasítás eredményeit exportálja az Azure Blob Storage vagy az Azure Data Lake Storage (ADLS) Gen 2 parquet- vagy CSV-fájljaiba, és külső táblázatot hoz létre ezekre a fájlokra.

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 a következővel JSON_VALUE elemezheti tovább: és OPENJSON.

Tároló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

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

Fontos

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 használata le van tiltva. Mindig használjon végpontspecifikus előtagokat.

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:

  • Parquet: 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 OPENROW Standard kiadás T 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

Folytathatja az adatkészletek feltárását a WHERE, a GROUP BY és más záradékok hozzáfűzésével az első lekérdezés eredményhalmaza alapján.

Ha az első lekérdezés sikertelen a felügyelt példányon, az adott példány valószínűleg korlátozott hozzáféréssel rendelkezik az Azure Storage-fiókokhoz, és a lekérdezés folytatása előtt forduljon a hálózatkezelési szakértőhöz, hogy engedélyezze a hozzáférést.

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

A felügyelt példányba 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 a felügyelt példány hogyan hitelesíti magát a tárterületen. A hitelesítés típusa é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. A felügyelt Azure SQL-példány a következő hitelesítési típusokat támogatja:

A felügyelt identitás a Microsoft Entra ID (korábbi nevén Azure Active Directory) szolgáltatása, amely Azure-szolgáltatásokat – például felügyelt Azure SQL-példányt – biztosít a Microsoft Entra ID-ban felügyelt identitással. Ez az identitás használható a nem nyilvános tárfiókok adathozzáférési kérelmeinek engedélyezésére. Az olyan szolgáltatások, mint az Azure SQL Managed Instance, rendszer által hozzárendelt felügyelt identitással rendelkeznek, és egy vagy több felhasználó által hozzárendelt felügyelt identitással is rendelkezhetnek. Rendszer által hozzárendelt felügyelt identitásokat vagy felhasználó által hozzárendelt felügyelt identitásokat használhat adatvirtualizáláshoz a felügyelt Azure SQL-példányokkal.

Az Azure Storage rendszergazdájának először engedélyt kell adnia a felügyelt identitásnak az adatok eléréséhez. Adjon engedélyeket a felügyelt példány rendszer által hozzárendelt felügyelt identitásának, ugyanúgy, ahogyan az engedélyeket bármely más Microsoft Entra-felhasználó megkapja. Példa:

  1. Az Azure Portalon, egy tárfiók Hozzáférés-vezérlés (IAM) lapján válassza a Szerepkör-hozzárendelés hozzáadása lehetőséget.
  2. Válassza ki a Beépített Azure RBAC-szerepkört a Storage Blob Data Readerben . Ez olvasási hozzáférést biztosít a felügyelt identitáshoz a szükséges Azure Blob Storage-tárolókhoz.
    • A felügyelt identitás azure RBAC-szerepkörének megadása helyett részletesebb engedélyeket is adhat a fájlok egy részhalmazához. Minden felhasználónak, akinek hozzá kell férnie az egyes fájlok olvasásához, a tárolóban lévő adatoknak végrehajtási engedéllyel kell rendelkezniük az összes szülőmappán egészen a gyökérig (a tárolóig). További információ arról , hogyan állíthat be ACL-eket az Azure Data Lake Storage Gen2-ben.
  3. A következő lapon válassza a Hozzáférés hozzárendelése felügyelt identitáshoz lehetőséget. + Tagok kiválasztása, majd a Felügyelt identitás legördülő lista alatt válassza ki a kívánt felügyelt identitást. További információ: Azure-szerepkörök hozzárendelése a Azure Portal.
  4. Ezután egyszerű létrehozni az adatbázis hatókörébe tartozó hitelesítő adatokat a felügyelt identitás hitelesítéséhez. Az alábbi példában jegyezze fel, hogy 'Managed Identity' ez egy kemény kóddal rendelkező sztring.
-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity'

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 és a hely elérésekor az adatbázis hatókörébe tartozó hitelesítő adatokra is hivatkoznia kell a beágyazott hitelesítési paraméterekkel. 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://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
        CREDENTIAL = [MyCredential];
)

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

Az OPENROW Standard kiadás T 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.

OPENROWSETcsak 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 elő van állítva a BULK paraméterre, hogy a fájl teljes elérési útját képezhesse.

Ha a fájl formátumát adja OPENROWSET meg, például az alábbi példát, 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 nyitott adatkészlethez.

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 a filepath() fájl metaadatainak olvasására és filename() a fájl elérési útjának vagy teljes elérési útjának és nevének egy részét lekérheti, amelybő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;
--List all paths:
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

Ha paraméter nélkül hívjuk meg, a függvény a filepath() sor által visszaadott fájl elérési útját adja vissza. 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 OPENROW tetején Standard kiadás T

Nézeteket hozhat létre és használhat az OPENROW Standard kiadás T-lekérdezések tördeléséhez, hogy könnyen újra felhasználhassa a mögöttes 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ézet tetején lévő lekérdezés által a kívánt adatok mennyiségét, amikor az oszlopok bármelyike szűr:

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 analitikus eszközöket, például a Power BI-t a találatok OPENROWSETfelhasználásához.

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
)
GO

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

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.

A teljesítménnyel kapcsolatos megfontolások

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 felügyelt példányt, hogy csak bizonyos mappákat és fájlokat kérdez le. 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ók a lekérdezés WHERE záradékában található metaadatfüggvény filepath() használatával kiküszöbölhetők a 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, és filename() a függvények támogatottak, filepath() de a WHERE záradékban nem. Továbbra is szűrhet a filename számított oszlopokban, vagy filepath ha használja őket. Az alábbi példa a következőket mutatja be:

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,
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

SELECT *
      FROM tbl_TaxiRides
WHERE
      [year]=2017            
      AND [month] in (10,11,12);

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.

Statisztika

A külső adatok statisztikáinak gyűjtése az egyik legfontosabb teendő a lekérdezésoptimalizáláshoz. Minél többet tud a példány az adatokról, annál gyorsabban hajthat végre lekérdezéseket. Az SQL-motor lekérdezésoptimalizálója egy költségalapú optimalizáló. Összehasonlítja a különböző lekérdezési tervek költségeit, majd a legalacsonyabb költséggel választja ki a csomagot. A legtöbb esetben a leggyorsabban végrehajtó tervet választja ki.

Statisztikák automatikus létrehozása

A felügyelt Azure SQL-példány a hiányzó statisztikák bejövő felhasználói lekérdezéseit elemzi. Ha a statisztikák hiányoznak, a lekérdezésoptimalizáló automatikusan létrehozza a lekérdezési predikátum vagy illesztés feltétel egyes oszlopainak statisztikáit a lekérdezésterv számosságbecsléseinek javítása érdekében. A statisztikák automatikus létrehozása szinkron módon történik, így kismértékben csökkenhet a lekérdezési teljesítmény, ha az oszlopokból hiányoznak statisztikák. Az egyetlen oszlop statisztikáinak létrehozásához szükséges idő a megcélzott fájlok méretétől függ.

OPENROW Standard kiadás T manuális statisztikák

Az elérési út egyoszlopos statisztikái a sys.sp_create_openrowset_statistics tárolt eljárással hozhatók OPENROWSET létre úgy, hogy a választó lekérdezést paraméterként egyetlen oszlopmal továbbítja:

EXEC sys.sp_create_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

Alapértelmezés szerint a példány az adathalmazban megadott adatok 100%-át használja statisztikák létrehozásához. A beállításokkal TABLESAMPLE megadhatja a minta méretét százalékként. Több oszlop egyoszlopos statisztikáinak létrehozásához hajtsa végre sys.sp_create_openrowset_statistics az egyes oszlopokra vonatkozó statisztikát. Nem hozhat létre többoszlopos statisztikát az OPENROWSET elérési úthoz.

A meglévő statisztikák frissítéséhez először helyezze el őket a sys.sp_drop_openrowset_statistics tárolt eljárással, majd hozza létre újra azokat a sys.sp_create_openrowset_statisticskövetkezővel:

EXEC sys.sp_drop_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

Külső táblázat manuális statisztikái

A külső táblák statisztikáinak létrehozásához használt szintaxis a szokásos felhasználói táblákhoz hasonló. Ha statisztikákat szeretne létrehozni egy oszlopon, adja meg a statisztikai objektum nevét és az oszlop nevét:

CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendorID)
WITH FULLSCAN, NORECOMPUTE;

A WITH beállítások kötelezőek, a mintamérethez pedig az engedélyezett beállítások és SAMPLE n százalékértékekFULLSCAN.

  • Több oszlop egyoszlopos statisztikáinak létrehozásához hajtsa végre CREATE STATISTICS az egyes oszlopokra vonatkozó statisztikát.
  • A többoszlopos statisztikák nem támogatottak.

Hibaelhárítás

A lekérdezések végrehajtásával kapcsolatos problémákat általában az okozza, hogy a felügyelt példány nem fér hozzá 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: Legalább olvasás , helyettesítő karakterek használata esetén pedig lista .
  • Blokkolt bejövő forgalom a tárfiókban. További részletekért tekintse meg az Azure Storage virtuális hálózati szabályainak kezelését, és győződjön meg arról, hogy engedélyezett a felügyelt példány virtuális hálózatáról való hozzáférés.
  • A felügyelt példányon a tárvégpont-szabályzat használatával blokkolt kimenő forgalom. Engedélyezze a tárfiók kimenő forgalmát.
  • Felügyelt identitás hozzáférési jogosultságai: győződjön meg arról, hogy a példány felügyelt identitása hozzáférési jogosultságot 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.

CREATE EXTERNAL TABLE AS SELECT (CETAS)

A CREATE EXTERNAL TABLE AS Standard kiadás LECT (CETAS) lehetővé teszi adatok exportálását a felügyelt SQL-példányból egy külső tárfiókba. A CETAS használatával létrehozhat egy külső táblát a Parquet- vagy CSV-fájlokon az Azure Blob Storage vagy az Azure Data Lake Storage (ADLS) Gen2 fölé. A CETAS ezzel párhuzamosan egy T-SQL Standard kiadás LECT utasítás eredményeit is exportálhatja a létrehozott külső táblába. Ezekkel a képességekkel adatkiszivárgási kockázat áll fenn, ezért a CETAS alapértelmezés szerint le van tiltva a felügyelt Azure SQL-példány esetében. Az engedélyezéshez lásd a CREATE EXTERNAL TABLE AS Standard kiadás LECT (CETAS) című témakört.

Korlátozások

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