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

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 voor toegang tot opslag:

• De OPENROWSET-functie die het mogelijk maakt om ad-hocvragen over de bestanden in Azure Storage uit te voeren.
• Externe tabel die een vooraf gedefinieerde gegevensstructuur is die is gebouwd op basis van een set externe bestanden.

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 query's uitvoeren op externe bestanden in Azure Storage als ze toegang hebben tot de opslag. Een gebruiker die is verbonden met een serverloze SQL-pool, moet de volgende query gebruiken om de inhoud van de bestanden in Azure Storage te lezen:

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

Gebruiker heeft toegang tot opslag met behulp van 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 krijgt toegang tot opslag met anonieme toegang of kan worden geïmiteerd met behulp van een SAS-token of een beheerde identiteit van de werkruimte.

imitatie

SQL-principals kunnen ook OPENROWSET gebruiken om rechtstreeks query's uit te voeren op bestanden die zijn beveiligd met SAS-tokens of beheerde identiteit van de werkruimte. Als een SQL-gebruiker deze functie uitvoert, moet een hoofdgebruiker met ALTER ANY CREDENTIAL machtiging een servergeoriënteerde referentie maken die overeenkomt met de URL in de functie (gebruikmakend van opslagnaam en container) en moet de machtiging REFERENCES voor deze referentie toegekend zijn aan degene die de OPENROWSET-functie aanroept.

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 REFERENTIE op serverniveau is die overeenkomt met de URL of als de SQL-gebruiker geen referenties voor deze referentie heeft, wordt de fout geretourneerd. SQL-principals kunnen zich niet imiteren met behulp van een Microsoft Entra-identiteit.

Directe toegang

Er is geen extra installatie nodig om Microsoft Entra-gebruikers in staat te stellen toegang te krijgen tot de bestanden met hun identiteiten. Elke gebruiker heeft toegang tot Azure Storage die anonieme toegang toestaat (aanvullende installatie is niet nodig).

Opmerking

Deze versie van OPENROWSET is ontworpen voor snelle en eenvoudige gegevensverkenning met behulp van standaardverificatie. 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 BEHULP van OPENROWSET

Met OPENROWSET kan de gebruiker query's uitvoeren op de bestanden die zijn geplaatst op een externe gegevensbron:

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

De gebruiker die deze query uitvoert, moet toegang hebben tot de bestanden. 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.

imitatie

DATABASE SCOPED CREDENTIAL hiermee geeft u op hoe u toegang wilt krijgen tot bestanden in de gegevensbron waarnaar wordt verwezen (momenteel SAS en Beheerde identiteit). Geavanceerde gebruiker met CONTROL DATABASE machtigingen moet DATABASE SCOPED CREDENTIAL maken die wordt gebruikt voor toegang tot opslag en EXTERNAL DATA SOURCE die de URL van de gegevensbron en referentie specificeert die gebruikt moeten worden:

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

Aanroeper moet een van de volgende machtigingen hebben om de OPENROWSET-functie uit te voeren:

• 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 naar de referentie waarnaar wordt verwezen in EXTERNAL DATA SOURCE.

Directe toegang

Gebruiker kan EXTERNE GEGEVENSBRON maken zonder REFERENTIE die verwijst naar opslag voor openbare toegang OF Microsoft Entra passthrough-verificatie gebruiken:

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

EXTERNE TABEL

Gebruiker met de machtigingen voor het lezen van tabellen heeft toegang tot externe bestanden met behulp van een EXTERNE TABEL die is gemaakt boven op de set Azure Storage-mappen en -bestanden.

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

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

Gebruiker die gegevens uit deze tabel leest, moet toegang hebben tot de bestanden. De gebruikers moeten worden geïmpersonificeerd 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.

imitatie

DATABASE SCOPED CREDENTIAL geeft aan hoe toegang verkregen wordt tot bestanden op de aangegeven gegevensbron. De gebruiker met de machtiging CONTROL DATABASE moet een DATABASE SCOPED CREDENTIAL maken die wordt gebruikt voor toegang tot opslag en een EXTERNE GEGEVENSBRON die de URL van de gegevensbron en de referentie specificeert die moet worden gebruikt:

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

Directe toegang

Gebruiker kan EXTERNE GEGEVENSBRON maken zonder REFERENTIE die verwijst naar opslag voor openbare toegang OF Microsoft Entra passthrough-verificatie gebruiken:

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

Externe bestanden lezen met EXTERNAL TABLE

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

SELECT *
FROM dbo.DimProductsExternal

De beller moet over de volgende machtigingen beschikken om gegevens te lezen:

• SELECT machtiging op externe tabel
• REFERENCES DATABASE SCOPED CREDENTIALmachtiging indien DATA SOURCECREDENTIAL

Machtigingen

De volgende tabel bevat de vereiste machtigingen voor de bovenstaande bewerkingen.

Zoekopdracht	Vereiste toestemmingen
OPENROWSET(BULK) zonder gegevensbron	ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS, of SQL-login moet REFERENCES CREDENTIAL::<URL> hebben voor opslag die met SAS is beveiligd.
OPENROWSET(BULK) met gegevensbron zonder referentie	ADMINISTER BULK OPERATIONS of ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) met gegevensbron met referentie	REFERENCES DATABASE SCOPED CREDENTIAL en een van ADMINISTER BULK OPERATIONS of ADMINISTER DATABASE BULK OPERATIONS
MAAK EXTERNE GEGEVENSBRON	ALTER ANY EXTERNAL DATA SOURCE en REFERENCES DATABASE SCOPED CREDENTIAL
AANMAKEN EXTERNE TABEL	CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMAT en ALTER ANY EXTERNAL DATA SOURCE
SELECTEER UIT EXTERNE TABEL	SELECT TABLE en REFERENCES DATABASE SCOPED CREDENTIAL
CETAS	Tabel maken - CREATE TABLE, ALTER ANY SCHEMA, en ALTER ANY DATA SOURCEALTER ANY EXTERNAL FILE FORMAT. Gegevens lezen: ADMINISTER BULK OPERATIONS of REFERENCES CREDENTIAL of SELECT TABLE per tabel/weergave/functie in de query + R/W machtiging op opslag

Volgende stappen

U bent nu klaar om verder te lezen met de volgende How To-artikelen:

• Query's uitvoeren op gegevens in opslag

• Query uitvoeren op CSV-bestand

• Queries uitvoeren van Parquet-bestanden

• JSON-bestanden opvragen

• Query uitvoeren op mappen en meerdere bestanden

• Partitionerings- en metagegevensfuncties gebruiken

• Geneste typen opvragen

• Weergaven maken en gebruiken