Få åtkomst till extern lagring via en serverlös SQL-pool i Azure Synapse Analytics

Den här artikeln beskriver hur användare kan läsa data från de filer som lagras på Azure Storage i en serverlös SQL-pool. Användarna har följande alternativ för att få åtkomst till lagring:

• Funktionen OPENROWSET som aktiverar ad hoc-frågor över filerna i Azure Storage.
• Extern tabell som är en fördefinierad datastruktur som bygger på en uppsättning externa filer.

Användaren kan använda olika autentiseringsmetoder som Microsoft Entra-genomströmningsautentisering (standard för Microsoft Entra-huvudnamn) och SAS-autentisering (standard för SQL-huvudnamn).

Fråga efter filer med OPENROWSET

OPENROWSET gör det möjligt för användare att fråga externa filer på Azure Storage om de har åtkomst till lagringen. En användare som är ansluten till en serverlös SQL-pool bör använda följande fråga för att läsa innehållet i filerna i Azure Storage:

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

Användaren kan komma åt lagring med hjälp av följande åtkomstregler:

• Microsoft Entra-användare – OPENROWSET använder Anroparens Microsoft Entra-identitet för att få åtkomst till Azure Storage eller få åtkomst till lagring med anonym åtkomst.
• SQL-användare – OPENROWSET kommer åt lagring med anonym åtkomst eller kan personifieras med hjälp av SAS-token eller hanterad identitet för arbetsytan.

Personifiering

SQL-huvudnamn kan också använda OPENROWSET för att direkt fråga efter filer som skyddas med SAS-token eller hanterad identitet på arbetsytan. Om en SQL-användare kör den här funktionen måste en power-användare med ALTER ANY CREDENTIAL behörighet skapa en autentiseringsuppgift med serveromfattning som matchar URL:en i funktionen (med lagringsnamn och container) och beviljas behörigheten REFERENSER för den här autentiseringsuppgiften till anroparen för funktionen OPENROWSET:

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

Om det inte finns någon CREDENTIAL på servernivå som matchar URL:en, eller om SQL-användaren inte har referensbehörighet för den här autentiseringsuppgiften, returneras felet. SQL-huvudnamn kan inte personifiera med hjälp av vissa Microsoft Entra-identiteter.

Direktåtkomst

Ingen ytterligare konfiguration krävs för att göra det möjligt för Microsoft Entra-användare att komma åt filerna med sina identiteter. Alla användare kan komma åt Azure Storage som tillåter anonym åtkomst (ytterligare installation behövs inte).

Kommentar

Den här versionen av OPENROWSET är utformad för snabb och enkel datautforskning med standardautentisering. Om du vill utnyttja personifiering eller hanterad identitet använder du OPENROWSET med DATA_SOURCE som beskrivs i nästa avsnitt.

Fråga datakällor med OPENROWSET

OPENROWSET gör det möjligt för användaren att köra frågor mot filerna som placeras på en extern datakälla:

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

Användaren som kör den här frågan måste kunna komma åt filerna. Användarna måste personifieras med hjälp av SAS-token eller hanterad identitet på arbetsytan om de inte kan komma åt filerna direkt med hjälp av sin Microsoft Entra-identitet eller anonym åtkomst.

Personifiering

DATABASE SCOPED CREDENTIAL anger hur du kommer åt filer på den refererade datakällan (för närvarande SAS och hanterad identitet). Power-användare med CONTROL DATABASE behörighet skulle behöva skapa DATABASE SCOPED CREDENTIAL som ska användas för att komma åt lagring och EXTERNAL DATA SOURCE som anger URL för datakälla och autentiseringsuppgifter som ska användas:

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

Anroparen måste ha någon av följande behörigheter för att köra funktionen OPENROWSET:

• En av behörigheterna för att köra OPENROWSET:
  • ADMINISTER BULK OPERATIONS aktiverar inloggning för att köra funktionen OPENROWSET.
  • ADMINISTER DATABASE BULK OPERATIONS gör att användaren med databasen som omfång kan köra funktionen OPENROWSET.
• REFERENCES DATABASE SCOPED CREDENTIAL till autentiseringsuppgifterna som refereras i EXTERNAL DATA SOURCE.

Direktåtkomst

Användaren kan skapa EXTERN DATAKÄLLA utan CREDENTIAL som refererar till lagring med offentlig åtkomst eller använda Microsoft Entra-genomströmningsautentisering:

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

EXTERN TABELL

Användare med behörighet att läsa tabellen kan komma åt externa filer med hjälp av en EXTERN TABELL som skapats ovanpå uppsättningen Azure Storage-mappar och -filer.

Användare som har behörighet att skapa en extern tabell (till exempel CREATE TABLE och ALTER ANY CREDENTIAL or REFERENCES DATABASE SCOPED CREDENTIAL) kan använda följande skript för att skapa en tabell ovanpå Azure Storage-datakällan:

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

Användare som läser data från den här tabellen måste kunna komma åt filerna. Användarna måste personifieras med hjälp av SAS-token eller hanterad identitet på arbetsytan om de inte kan komma åt filerna direkt med hjälp av sin Microsoft Entra-identitet eller anonym åtkomst.

Personifiering

DATABASE SCOPED CREDENTIAL anger hur du kommer åt filer på den refererade datakällan. Användare med behörigheten KONTROLLDATABAS skulle behöva skapa DATABASOMFÅNGAD CREDENTIAL som ska användas för att komma åt lagring och EXTERN DATAKÄLLA som anger URL för datakälla och autentiseringsuppgifter som ska användas:

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

Direktåtkomst

Användaren kan skapa EXTERN DATAKÄLLA utan CREDENTIAL som refererar till lagring med offentlig åtkomst eller använda Microsoft Entra-genomströmningsautentisering:

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

Läsa externa filer med EXTERN TABELL

MED EXTERN TABELL kan du läsa data från de filer som refereras via datakällan med hjälp av standard-SQL SELECT-instruktionen:

SELECT *
FROM dbo.DimProductsExternal

Anroparen måste ha följande behörigheter för att läsa data:

• SELECT permission ON external table
• REFERENCES DATABASE SCOPED CREDENTIAL behörighet om DATA SOURCE har CREDENTIAL

Behörigheter

I följande tabell visas nödvändiga behörigheter för de åtgärder som anges ovan.

Söka i data	Behörigheter som krävs
OPENROWSET(BULK) utan datakälla	ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS, eller SQL-inloggning måste ha REFERENSER CREDENTIAL::<URL> för SAS-skyddad lagring
OPENROWSET(BULK) med datakälla utan autentiseringsuppgifter	ADMINISTER BULK OPERATIONS eller ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) med datakälla med autentiseringsuppgifter	REFERENCES DATABASE SCOPED CREDENTIAL och en av ADMINISTER BULK OPERATIONS eller ADMINISTER DATABASE BULK OPERATIONS
SKAPA EN EXTERN DATAKÄLLA	ALTER ANY EXTERNAL DATA SOURCE och REFERENCES DATABASE SCOPED CREDENTIAL
SKAPA EXTERN TABELL	CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMAToch ALTER ANY EXTERNAL DATA SOURCE
VÄLJ FRÅN EXTERN TABELL	SELECT TABLE och REFERENCES DATABASE SCOPED CREDENTIAL
CETAS	Så här skapar du tabellen - CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY DATA SOURCEoch ALTER ANY EXTERNAL FILE FORMAT. Läsa data: ADMINISTER BULK OPERATIONS eller REFERENCES CREDENTIALSELECT TABLE per varje tabell/vy/funktion i fråga + R/W-behörighet för lagring

Nästa steg

Nu är du redo att fortsätta med följande instruktionsartiklar:

• Fråga efter data om lagring

• Fråga CSV-fil

• Fråga Parquet-filer

• Fråga efter JSON-filer

• Efterfråga mappar och flera filer

• Använda partitionerings- och metadatafunktioner

• Frågekapslade typer

• Skapa och använda vyer