Uzyskiwanie dostępu do magazynu zewnętrznego przy użyciu bezserwerowej puli SQL w usłudze Azure Synapse Analytics

W tym artykule opisano, jak użytkownicy mogą odczytywać dane z plików przechowywanych w usłudze Azure Storage w bezserwerowej puli SQL. Użytkownicy mają następujące opcje dostępu do magazynu:

• Funkcja OPENROWSET, która umożliwia wykonywanie zapytań ad hoc dotyczących plików w usłudze Azure Storage.
• Tabela zewnętrzna , która jest wstępnie zdefiniowaną strukturą danych utworzoną na podstawie zestawu plików zewnętrznych.

Użytkownik może używać różnych metod uwierzytelniania, takich jak uwierzytelnianie przepustowe Microsoft Entra (ustawienie domyślne dla podmiotów zabezpieczeń Microsoft Entra) i uwierzytelnianie SAS (ustawienie domyślne dla podmiotów zabezpieczeń SQL).

Wykonywanie zapytań o pliki przy użyciu zestawu OPENROWSET

Funkcja OPENROWSET umożliwia użytkownikom wykonywanie zapytań dotyczących plików zewnętrznych na Azure Storage, jeśli mają dostęp do tego. Użytkownik połączony z bezserwerową pulą SQL powinien użyć następującego zapytania, aby odczytać zawartość plików w usłudze Azure Storage:

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

Użytkownik może uzyskać dostęp do magazynu przy użyciu następujących reguł dostępu:

• Microsoft Entra user - OPENROWSET will use Microsoft Entra identity of caller to access Azure Storage or access storage with anonymous access.
• SQL user – OPENROWSET will access storage with anonymous access or can be impersonated using SAS token or Managed identity of workspace.

Podszywanie się

SQL principals can also use OPENROWSET to directly query files protected with SAS tokens or Managed Identity of the workspace. If a SQL user executes this function, a power user with ALTER ANY CREDENTIAL permission must create a server-scoped credential that matches URL in the function (using storage name and container) and granted REFERENCES permission for this credential to the caller of OPENROWSET function:

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

Jeśli nie ma żadnych poświadczeń na poziomie serwera pasujących do adresu URL lub użytkownik SQL nie ma uprawnień odwołania do tego poświadczenia, zostanie zwrócony błąd. SQL principals can't impersonate using some Microsoft Entra identity.

Bezpośredni dostęp

Aby umożliwić użytkownikom firmy Microsoft Entra uzyskiwanie dostępu do plików przy użyciu ich tożsamości, nie jest wymagana żadna dodatkowa konfiguracja. Każdy użytkownik może uzyskać dostęp do usługi Azure Storage, która zezwala na dostęp anonimowy (dodatkowa konfiguracja nie jest wymagana).

Uwaga

Ta wersja zestawu OPENROWSET została zaprojektowana do szybkiej i łatwej eksploracji danych przy użyciu uwierzytelniania domyślnego. To leverage impersonation or Managed Identity, use OPENROWSET with DATA_SOURCE described in the next section.

Wykonywanie zapytań dotyczących źródeł danych przy użyciu zestawu OPENROWSET

FUNKCJA OPENROWSET umożliwia użytkownikowi wykonywanie zapytań dotyczących plików umieszczonych w zewnętrznym źródle danych:

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

Użytkownik wykonujący to zapytanie musi mieć dostęp do plików. The users must be impersonated using SAS token or Managed Identity of workspace if they can't directly access the files using their Microsoft Entra identity or anonymous access.

Podszywanie się

DATABASE SCOPED CREDENTIAL specifies how to access files on the referenced data source (currently SAS and Managed Identity). Power user with CONTROL DATABASE permission would need to create DATABASE SCOPED CREDENTIAL that will be used to access storage and EXTERNAL DATA SOURCE that specifies URL of data source and credential that should be used:

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

Caller must have one of the following permissions to execute OPENROWSET function:

• One of the permissions to execute OPENROWSET:
  • ADMINISTER BULK OPERATIONS enables login to execute OPENROWSET function.
  • ADMINISTER DATABASE BULK OPERATIONS umożliwia użytkownikowi, którego zakres ogranicza się do bazy danych, wykonywanie funkcji OPENROWSET.
• REFERENCES DATABASE SCOPED CREDENTIAL to the credential that is referenced in EXTERNAL DATA SOURCE.

Bezpośredni dostęp

User can create EXTERNAL DATA SOURCE without CREDENTIAL that will reference public access storage OR use Microsoft Entra passthrough authentication:

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

EXTERNAL TABLE

Użytkownik z uprawnieniami do odczytu tabeli może uzyskać dostęp do plików zewnętrznych przy użyciu tabeli ZEWNĘTRZNEj utworzonej na podstawie zestawu folderów i plików usługi Azure Storage.

Użytkownik, który ma uprawnienia do tworzenia tabeli zewnętrznej (na przykład CREATE TABLE i ALTER ANY CREDENTIAL lub REFERENCES DATABASE SCOPED CREDENTIAL) może użyć następującego skryptu, aby utworzyć tabelę na podstawie źródła danych usługi Azure Storage:

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

Użytkownik, który odczytuje dane z tej tabeli, musi mieć dostęp do plików. The users must be impersonated using SAS token or Managed Identity of workspace if they cannot directly access the files using their Microsoft Entra identity or anonymous access.

Podszywanie się

DATABASE SCOPED CREDENTIAL specifies how to access files on the referenced data source. User with CONTROL DATABASE permission would need to create DATABASE SCOPED CREDENTIAL that will be used to access storage and EXTERNAL DATA SOURCE that specifies URL of data source and credential that should be used:

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

Bezpośredni dostęp

User can create EXTERNAL DATA SOURCE without CREDENTIAL that will reference public access storage OR use Microsoft Entra passthrough authentication:

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

Odczytywanie plików zewnętrznych za pomocą TABELI ZEWNĘTRZNEJ

TABELA ZEWNĘTRZNA umożliwia odczytywanie danych z plików, do których odwołuje się źródło danych przy użyciu standardowej instrukcji SQL SELECT:

SELECT *
FROM dbo.DimProductsExternal

Obiekt wywołujący musi mieć następujące uprawnienia do odczytywania danych:

• SELECT permission ON external table
• REFERENCES DATABASE SCOPED CREDENTIAL permission if DATA SOURCE has CREDENTIAL

Uprawnienia

W poniższej tabeli wymieniono wymagane uprawnienia dla operacji wymienionych powyżej.

Zapytanie	Wymagane uprawnienia
OPENROWSET(BULK) bez źródła danych	ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS, or SQL login must have REFERENCES CREDENTIAL::<URL> for SAS-protected storage
OPENROWSET(BULK) ze źródłem danych bez poświadczenia	ADMINISTER BULK OPERATIONS lub ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) with datasource with credential	REFERENCES DATABASE SCOPED CREDENTIAL i jeden z ADMINISTER BULK OPERATIONS lub ADMINISTER DATABASE BULK OPERATIONS
TWORZENIE ZEWNĘTRZNEGO ŹRÓDŁA DANYCH	ALTER ANY EXTERNAL DATA SOURCE i REFERENCES DATABASE SCOPED CREDENTIAL
TWORZENIE TABELI ZEWNĘTRZNEJ	CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMATi ALTER ANY EXTERNAL DATA SOURCE
WYBIERANIE Z TABELI ZEWNĘTRZNEJ	SELECT TABLE i REFERENCES DATABASE SCOPED CREDENTIAL
CETAS	Aby utworzyć tabelę — CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY DATA SOURCE i ALTER ANY EXTERNAL FILE FORMAT. To read data: ADMINISTER BULK OPERATIONS or REFERENCES CREDENTIAL or SELECT TABLE per each table/view/function in query + R/W permission on storage

Następne kroki

You're now ready to continue on with the following How To articles:

• Query data on storage

• Wykonywanie zapytań względem pliku CSV

• Query Parquet files

• Wykonywanie zapytań dotyczących plików JSON

• Wykonywanie zapytań względem folderów i wielu plików

• Korzystanie z funkcji partycjonowania i metadanych

• Query nested types

• Tworzenie i używanie widoków