Külső tároló elérése kiszolgáló nélküli SQL-készlet használatával a Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse egy nagyvállalati szintű relációs raktár egy Data Lake-alaprendszeren, jövőre kész architektúrával, beépített AI-vel és új funkciókkal. Ha még nem ismerkedik adattárházzal, kezdje a Fabric Data Warehouse. A meglévő dedikált SQL-készlet számítási feladatai frissíthetők Fabric az adatelemzés, a valós idejű elemzés és a jelentéskészítés új képességeinek eléréséhez.

• A Fabric ingyenes próbaverzió indítása.
• Migration Assistant Fabric Data Warehouse.

Ez a cikk azt ismerteti, hogy a felhasználók hogyan olvashatnak adatokat a kiszolgáló nélküli SQL-készletben Azure Storage tárolt fájlokból. A felhasználók a következő lehetőségeket kínálják a tárterület eléréséhez:

• OPENROWSET függvény, amely alkalmi lekérdezéseket tesz lehetővé a Azure Storage fájljain.
• Külső tábla , amely egy előre definiált adatstruktúra, amely külső fájlokra épül.

A felhasználó különböző hitelesítési módszereket használhat, például Microsoft Entra átengedéses hitelesítést (a Microsoft Entra-tagok alapértelmezett módja) és SAS-hitelesítést (az SQL-tagok esetében alapértelmezés szerint).

Fájlok lekérdezése AZ OPENROWSET használatával

Az OPENROWSET lehetővé teszi, hogy a felhasználók külső fájlokat kérdezhessenek le Azure tárterületen, ha hozzáféréssel rendelkeznek a tárolóhoz. A kiszolgáló nélküli SQL-készlethez csatlakozó felhasználóknak az alábbi lekérdezéssel kell beolvasni a fájlok tartalmát Azure tárterületen:

SELECT * FROM
 OPENROWSET(BULK 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/*.parquet', format= 'parquet') as rows

A felhasználó a következő hozzáférési szabályokkal férhet hozzá a tárolóhoz:

• Microsoft Entra felhasználó – OPENROWSET a hívó Microsoft Entra identitását fogja használni a Azure Storage eléréséhez, vagy névtelen hozzáféréssel fér hozzá a tárolóhoz.
• SQL-felhasználó – OPENROWSET névtelen hozzáféréssel fér hozzá a tárolóhoz, vagy a munkaterület SAS-jogkivonatával vagy felügyelt identitásával megszemélyesíthető.

Impersonation

Az SQL-tagok az OPENROWSET használatával közvetlenül is lekérdezhetik a munkaterület SAS-jogkivonatokkal vagy felügyelt identitással védett fájljait. Ha egy SQL-felhasználó végrehajtja ezt a függvényt, egy ALTER ANY CREDENTIAL engedéllyel rendelkező power usernek létre kell hoznia egy kiszolgáló hatókörű hitelesítő adatot, amely megfelel a függvényben található URL-címnek (a tároló neve és tároló használatával), és REFERENCES jogosultságot kell adnia ehhez a hitelesítő adathoz az OPENROWSET függvény hívójának.

EXECUTE AS somepoweruser

CREATE CREDENTIAL [https://<storage_account>.dfs.core.windows.net/<container>]
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sas token';

GRANT REFERENCES ON CREDENTIAL::[https://<storage_account>.dfs.core.windows.net/<container>] TO sqluser

Ha nincs az URL-címnek megfelelő kiszolgálószintű HITELESÍTŐ ADAT, vagy ha az SQL-felhasználó nem rendelkezik hivatkozási engedéllyel ehhez a hitelesítő adathoz, a rendszer visszaadja a hibát. Az SQL-azonosítók nem tudnak megszemélyesítést végezni Microsoft Entra identitással.

Közvetlen hozzáférés

Nincs szükség további beállításra ahhoz, hogy Microsoft Entra felhasználók hozzáférhessenek a fájlokhoz az identitásuk használatával. Bármely felhasználó hozzáférhet Azure névtelen hozzáférést lehetővé tevő tárolóhoz (nincs szükség további telepítésre).

Megjegyzés:

Az OPENROWSET ezen verziója az alapértelmezett hitelesítést használó gyors és egyszerű adatfeltárásra lett tervezve. A megszemélyesítés vagy a felügyelt identitás (Managed Identity) kihasználásához használja az OPENROWSET-et az alábbiakban részletezett DATA_SOURCE-tal.

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

Az OPENROWSET lehetővé teszi, hogy a felhasználó lekérdezhesse a külső adatforráson elhelyezett fájlokat:

SELECT * FROM
 OPENROWSET(BULK 'file/path/*.parquet',
 DATA_SOURCE = MyAzureInvoices,
 FORMAT= 'parquet') as rows

A lekérdezést végrehajtó felhasználónak hozzá kell tudnia férni a fájlokhoz. A felhasználókat a SAS-jogkivonat vagy a munkaterület felügyelt identitásának használatával kell megszemélyesíteni, ha nem tudnak közvetlenül hozzáférni a fájlokhoz a Microsoft Entra identitásukkal vagy anonim hozzáféréssel.

Impersonation

DATABASE SCOPED CREDENTIAL meghatározza, hogyan érheti el a hivatkozott adatforrás fájljait (jelenleg SAS és felügyelt identitás). A CONTROL DATABASE engedéllyel rendelkező power-felhasználónak létre kell hoznia DATABASE SCOPED CREDENTIAL, amelyet a tárhely elérésére használnak, valamint EXTERNAL DATA SOURCE, amely megadja az adatforrás URL-címét és a szükséges hitelesítő adatokat.

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=201********' ;

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/' ,
 CREDENTIAL = AccessAzureInvoices) ;

A hívónak az alábbi engedélyekkel kell rendelkeznie az OPENROWSET függvény végrehajtásához:

• Az OPENROWSET végrehajtásának egyik engedélye:
  • ADMINISTER BULK OPERATIONS – lehetővé teszi a bejelentkezést az OPENROWSET függvény végrehajtásához.
  • ADMINISTER DATABASE BULK OPERATIONS – lehetővé teszi az adatbázishoz tartozó felhasználó számára az OPENROWSET függvény végrehajtását.
• REFERENCES DATABASE SCOPED CREDENTIAL a EXTERNAL DATA SOURCE hivatkozott hitelesítő adatokra.

Közvetlen hozzáférés

A felhasználó hitelesítő adatok nélkül hozhat létre külső adatforrást, amely nyilvános hozzáférésű tárolóra hivatkozik, vagy Microsoft Entra átmenő hitelesítést használ:

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>') ;

KÜLSŐ TÁBLA

A táblázat olvasására jogosult felhasználó a külső fájlokhoz Azure Storage mappák és fájlok készletén létrehozott KÜLSŐ TÁBLA használatával férhet hozzá.

A külső tábla létrehozására engedélyekkel rendelkező felhasználó (például CREATE TABLE és ALTER ANY CREDENTIAL vagy REFERENCES DATABASE SCOPED CREDENTIAL) a következő szkripttel hozhat létre táblát egy Azure Storage adatforráson.

CREATE EXTERNAL TABLE [dbo].[DimProductexternal]
( ProductKey int, ProductLabel nvarchar, ProductName nvarchar )
WITH
(
LOCATION='/DimProduct/year=*/month=*' ,
DATA_SOURCE = AzureDataLakeStore ,
FILE_FORMAT = TextFileFormat
) ;

A táblából adatokat olvasó felhasználónak hozzá kell tudnia férni a fájlokhoz. A felhasználókat a SAS-jogkivonat vagy a munkaterület felügyelt identitása használatával kell megszemélyesíteni, ha nem tudnak közvetlenül hozzáférni a fájlokhoz a Microsoft Entra identitásuk vagy névtelen hozzáféréssel.

Impersonation

A DATABASE SCOPED CREDENTIAL a hivatkozott adatforrás fájljainak elérését határozza meg. A CONTROL DATABASE engedéllyel rendelkező felhasználónak létre kell hoznia egy ADATBÁZIS HATÓKÖRŰ HITELESÍTŐ ADATOT, amely a tárolóhoz és a külső adatforráshoz való hozzáféréshez lesz használva, amely megadja az adatforrás URL-címét és a használni kívánt hitelesítő adatokat:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL cred
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=201********' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>' ,
 CREDENTIAL = cred
 ) ;

Közvetlen hozzáférés

A felhasználó hitelesítő adatok nélkül hozhat létre külső adatforrást, amely nyilvános hozzáférésű tárolóra hivatkozik, vagy Microsoft Entra átmenő hitelesítést használ:

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>') ;

Külső fájlok olvasása KÜLSŐ TÁBLÁZATtal

A KÜLSŐ TÁBLA lehetővé teszi az adatforráson keresztül hivatkozott fájlokból származó adatok beolvasását szabványos SQL SELECT utasítással:

SELECT *
FROM dbo.DimProductsExternal

A hívónak a következő engedélyekkel kell rendelkeznie az adatok olvasásához:

• SELECT engedély a külső táblához
• REFERENCES DATABASE SCOPED CREDENTIAL engedély, ha DATA SOURCE rendelkezik CREDENTIAL

Engedélyek

Az alábbi táblázat a fent felsorolt műveletekhez szükséges engedélyeket sorolja fel.

Lekérdezés	Szükséges engedélyek
OPENROWSET(BULK) adatforrás nélkül	ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONSvagy az SQL-bejelentkezéshez HIVATKOZÓ HITELESÍTŐ ADATOKnak::<AZ SAS által védett tároló URL-címének> kell lennie
OPENROWSET(BULK) hitelesítő adatok nélküli adatforrással	ADMINISTER BULK OPERATIONS vagy ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) hitelesítő adatokkal rendelkező adatforrással	REFERENCES DATABASE SCOPED CREDENTIALés egy vagy több ADMINISTER BULK OPERATIONSADMINISTER DATABASE BULK OPERATIONS
KÜLSŐ ADATFORRÁS LÉTREHOZÁSA	ALTER ANY EXTERNAL DATA SOURCE és REFERENCES DATABASE SCOPED CREDENTIAL
KÜLSŐ TÁBLA LÉTREHOZÁSA	CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMATés ALTER ANY EXTERNAL DATA SOURCE
Válasszon ki külső táblából	SELECT TABLE és REFERENCES DATABASE SCOPED CREDENTIAL
CETAS	Tábla létrehozása – CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY DATA SOURCEés ALTER ANY EXTERNAL FILE FORMAT. Adatok olvasása: ADMINISTER BULK OPERATIONS vagy REFERENCES CREDENTIAL vagy SELECT TABLE minden egyes tábla/nézet/függvény lekérdezése esetén + R/W engedély a tárterületen

Következő lépések

Most már készen áll a következő how-to cikkek elolvasására:

• Adatok lekérdezése a tárterületen

• CSV-fájl lekérdezése

• Parquet-fájlok lekérdezése

• JSON-fájlok lekérdezése

• Mappák és több fájl lekérdezése

• Particionálási és metaadat-függvények használata

• Beágyazott típusok lekérdezése

• Nézetek létrehozása és használata