Toegang tot externe opslag met behulp van een serverloze SQL-pool in Azure Synapse Analytics

  • Artikel

In dit artikel wordt beschreven hoe gebruikers gegevens kunnen lezen uit de bestanden die zijn opgeslagen in Azure Storage in een serverloze SQL-pool. Gebruikers hebben de volgende opties om toegang te krijgen tot de opslag:

  • De functie OPENROWSET die ad-hocquery's voor de bestanden in Azure Storage mogelijk maakt.
  • Externe tabel dat een vooraf gedefinieerde gegevensstructuur is die boven op de set externe bestanden is gebouwd.

De gebruiker kan verschillende verificatiemethoden gebruiken, zoals Microsoft Entra passthrough-verificatie (standaard voor Microsoft Entra-principals) en SAS-verificatie (standaard voor SQL-principals).

Query's uitvoeren op bestanden met OPENROWSET

Met OPENROWSET kunnen gebruikers externe bestanden in Azure Storage opvragen als ze toegang hebben tot de opslag. Een gebruiker die is verbonden met de serverloze SQL-pool, moet de volgende query gebruiken om de inhoud van de bestanden in Azure Storage te kunnen lezen:

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

Een gebruiker kan toegang krijgen tot de opslag met de volgende toegangsregels:

  • Microsoft Entra-gebruiker : OPENROWSET gebruikt de Microsoft Entra-identiteit van de beller voor toegang tot Azure Storage of toegang tot opslag met anonieme toegang.
  • SQL-gebruiker: OPENROWSET gebruikt anonieme toegang voor toegang tot de opslaglocatie, of kan worden geïmiteerd met behulp van een SAS-token of een beheerde identiteit van een werkruimte.

SQL-principals kunnen ook OPENROWSET gebruiken om rechtstreeks bestanden op te vragen die zijn beveiligd met SAS-tokens of beheerde identiteit van de werkruimte. Als een SQL-gebruiker deze functie uitvoert, moet een hoofdgebruiker met de machtiging ALTER ANY CREDENTIAL referenties binnen serverbereik maken die overeenkomen met de URL in de functie (met behulp van de opslagnaam en container) en de machtiging REFERENCES heeft verleend voor de aanroeper van de OPENROWSET-functie:

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

Als er geen CREDENTIAL op serverniveau is die overeenkomt met de URL, of als de SQL-gebruiker geen referentiemachtiging voor deze referentie heeft, wordt de fout geretourneerd. SQL-principals kunnen zich niet imiteren met behulp van een Microsoft Entra-identiteit.

Notitie

Deze versie van OPENROWSET is ontworpen om snel en eenvoudig gegevens te verkennen met behulp van standaardauthenticatie. Als u imitatie of beheerde identiteit wilt gebruiken, gebruikt u OPENROWSET met DATA_SOURCE beschreven in de volgende sectie.

Query's uitvoeren op gegevensbronnen met OPENROWSET

Met OPENROWSET kunnen gebruikers een query uitvoeren op bestanden die in een externe gegevensbron zijn geplaatst:

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

De gebruiker die deze query uitvoert, moet toegang tot de bestanden kunnen hebben. De gebruikers moeten worden geïmiteerd met behulp van een SAS-token of een beheerde identiteit van de werkruimte als ze de bestanden niet rechtstreeks kunnen openen met hun Microsoft Entra-identiteit of anonieme toegang.

Met DATABASE SCOPED CREDENTIAL geeft u op hoe gebruikers toegang kunnen krijgen tot bestanden op de gegevensbron waarnaar wordt verwezen (momenteel SAS en beheerde identiteit). Een hoofdgebruiker met de machtiging CONTROL DATABASE moet DATABASE SCOPED CREDENTIAL maken, dat wordt gebruikt om toegang te krijgen tot opslag, en EXTERNAL DATA SOURCE, waarmee de URL van de gegevensbron en te gebruiken referenties worden opgegeven:

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&amp;sp=rwac&amp;se=2017-02-01T00:55:34Z&amp;st=201********' ;

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

Een aanroeper moet een van de volgende machtigingen hebben voor het uitvoeren van de OPENROWSET-functie:

  • Een van de machtigingen voor het uitvoeren van OPENROWSET:
    • Met ADMINISTER BULK OPERATIONS kan een gebruiker via aanmelding de OPENROWSET-functie uitvoeren.
    • Met ADMINISTER DATABASE BULK OPERATIONS kan een gebruiker binnen een databasebereik de OPENROWSET-functie uitvoeren.
  • REFERENCES DATABASE SCOPED CREDENTIAL voor de referentie waarnaar wordt verwezen in EXTERNAL DATA SOURCE.

EXTERNAL TABLE

Een gebruiker met de machtigingen voor het lezen van de tabel kan toegang krijgen tot externe bestanden met behulp van een EXTERNAL TABLE die wordt gemaakt boven op de set Azure Storage-mappen en -bestanden.

Een gebruiker met -machtigingen voor het maken van een externe tabel (bijvoorbeeld CREATE TABLE en ALTER ANY CREDENTIAL of REFERENCES DATABASE SCOPED CREDENTIAL) kunnen het volgende script gebruiken om een tabel boven op Azure Storage-gegevensbron te maken:

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

De gebruiker die gegevens uit deze tabel leest, moet toegang tot de bestanden kunnen hebben. De gebruikers moeten worden geïmiteerd met behulp van een SAS-token of een beheerde identiteit van de werkruimte als ze de bestanden niet rechtstreeks kunnen openen met hun Microsoft Entra-identiteit of anonieme toegang.

Met DATABASE SCOPED CREDENTIAL geeft u op hoe gebruikers toegang kunnen krijgen tot bestanden op de gegevensbron waarnaar wordt verwezen. Een gebruiker met de machtiging CONTROL DATABASE moet DATABASE SCOPED CREDENTIAL maken dat wordt gebruikt om toegang te krijgen tot opslag en EXTERNAL DATA SOURCE waarmee de URL van de gegevensbron en te gebruiken referenties worden opgegeven:

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

Externe bestanden met EXTERNAL TABLE lezen

Met EXTERNAL TABLE kunt u gegevens lezen van de bestanden waarnaar wordt verwezen via de gegevensbron met behulp van de SQL SELECT-standaardinstructie:

SELECT *
FROM dbo.DimProductsExternal

Een aanroeper moet de volgende machtigingen hebben om gegevens te lezen:

  • SELECT-machtiging IN de externe tabel
  • REFERENCES DATABASE SCOPED CREDENTIAL-machtiging als DATA SOURCE over CREDENTIAL beschikt

Bevoegdheden

De volgende tabel geeft een lijst van de vereiste machtigingen voor de hierboven genoemde bewerkingen.

Query’s uitvoeren Vereiste machtigingen
OPENROWSET(BULK) zonder gegevensbron ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONSof SQL-aanmelding moet REFERENTIES::<URL> hebben voor met SAS beveiligde opslag
OPENROWSET(BULK) met gegevensbron zonder referenties ADMINISTER BULK OPERATIONS of ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) met gegevensbron en referenties REFERENCES DATABASE SCOPED CREDENTIAL en één van ADMINISTER BULK OPERATIONS of ADMINISTER DATABASE BULK OPERATIONS
CREATE EXTERNAL DATA SOURCE ALTER ANY EXTERNAL DATA SOURCE en REFERENCES DATABASE SCOPED CREDENTIAL
CREATE EXTERNAL TABLE CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMAT en ALTER ANY EXTERNAL DATA SOURCE
SELECT FROM EXTERNAL TABLE SELECT TABLE en REFERENCES DATABASE SCOPED CREDENTIAL
CETAS Voor het maken van een tabel - CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY DATA SOURCE en ALTER ANY EXTERNAL FILE FORMAT. Gegevens lezen: ADMINISTER BULK OPERATIONS of REFERENCES CREDENTIAL of SELECT TABLE per tabel/weergave/functie in query + lezen/schrijven-machtiging voor opslag

Volgende stappen

U kunt nu doorgaan met de volgende Help-artikelen: