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

Artykuł
10/18/2023

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 przekazywane firmy Microsoft (ustawienie domyślne dla podmiotów zabezpieczeń firmy Microsoft) i uwierzytelnianie SYGNATURy dostępu współdzielonego (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 w usłudze Azure Storage, jeśli mają dostęp do magazynu. 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:

Użytkownik Firmy Microsoft Entra — OPENROWSET użyje tożsamości firmy Microsoft Entra obiektu wywołującego, aby uzyskać dostęp do usługi Azure Storage lub uzyskać dostęp do magazynu z dostępem anonimowym.
Użytkownik SQL — OPENROWSET będzie uzyskiwać dostęp do magazynu z dostępem anonimowym lub może być personifikowany przy użyciu tokenu SAS lub tożsamości zarządzanej obszaru roboczego.

Personifikacji
Bezpośredni dostęp

Podmioty zabezpieczeń SQL mogą również używać funkcji OPENROWSET do bezpośredniego wykonywania zapytań dotyczących plików chronionych za pomocą tokenów SAS lub tożsamości zarządzanej obszaru roboczego. Jeśli użytkownik SQL wykonuje tę funkcję, użytkownik zasilania z ALTER ANY CREDENTIAL uprawnieniami musi utworzyć poświadczenie o zakresie serwera, które pasuje do adresu URL w funkcji (przy użyciu nazwy magazynu i kontenera) i przyznane uprawnienie REFERENCES dla tego poświadczenia do obiektu wywołującego funkcji 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

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. Podmioty zabezpieczeń SQL nie mogą personifikować się przy użyciu niektórych tożsamości firmy Microsoft Entra.

Uwaga

Ta wersja zestawu OPENROWSET została zaprojektowana do szybkiej i łatwej eksploracji danych przy użyciu uwierzytelniania domyślnego. Aby skorzystać z personifikacji lub tożsamości zarządzanej, użyj funkcji OPENROWSET z DATA_SOURCE opisanymi w następnej sekcji.

Wykonywanie zapytań dotyczących źródeł danych przy użyciu funkcji 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. Użytkownicy muszą być personifikowani przy użyciu tokenu SAS lub tożsamości zarządzanej obszaru roboczego, jeśli nie mogą bezpośrednio uzyskać dostępu do plików przy użyciu tożsamościmicrosoft Entra lub dostępu anonimowego.

Personifikacji
Bezpośredni dostęp

DATABASE SCOPED CREDENTIAL określa sposób uzyskiwania dostępu do plików w przywoływanych źródła danych (obecnie sygnatura dostępu współdzielonego i tożsamość zarządzana). Użytkownik power z CONTROL DATABASE uprawnieniami musi utworzyć DATABASE SCOPED CREDENTIAL , który będzie używany do uzyskiwania dostępu do magazynu i EXTERNAL DATA SOURCE który określa adres URL źródła danych i poświadczeń, które powinny być używane:

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

Obiekt wywołujący musi mieć jedno z następujących uprawnień do wykonywania funkcji OPENROWSET:

Jedno z uprawnień do wykonywania zestawu OPENROWSET:
- ADMINISTER BULK OPERATIONS umożliwia logowanie w celu wykonania funkcji OPENROWSET.
- ADMINISTER DATABASE BULK OPERATIONS umożliwia użytkownikowi wykonywanie funkcji OPENROWSET dla zakresu bazy danych.
REFERENCES DATABASE SCOPED CREDENTIALdo poświadczeń, do których odwołuje się element .EXTERNAL DATA SOURCE

Użytkownik może utworzyć zewnętrzne źródło danych bez poświadczeń, które będzie odwoływać się do magazynu dostępu publicznego LUB użyć uwierzytelniania przekazywanego firmy Microsoft:

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

TABELA ZEWNĘTRZNA

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. Użytkownicy muszą być personifikowani przy użyciu tokenu SAS lub tożsamości zarządzanej obszaru roboczego , jeśli nie mogą bezpośrednio uzyskać dostępu do plików przy użyciu tożsamości firmy Microsoft lub dostępu anonimowego.

Personifikacji
Bezpośredni dostęp

POŚWIADCZENIE O ZAKRESIE BAZY DANYCH określa sposób uzyskiwania dostępu do plików w przywoływaniu źródła danych. Użytkownik z uprawnieniem CONTROL DATABASE musi utworzyć POŚWIADCZENIA O ZAKRESIE BAZY DANYCH, które będą używane do uzyskiwania dostępu do magazynu i zewnętrznego źródła danych określającego adres URL źródła danych i poświadczeń, które powinny być używane:

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

Użytkownik może utworzyć zewnętrzne źródło danych bez poświadczeń, które będzie odwoływać się do magazynu dostępu publicznego LUB użyć uwierzytelniania przekazywanego firmy Microsoft:

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 uprawnienie W tabeli zewnętrznej
REFERENCES DATABASE SCOPED CREDENTIAL uprawnienie, jeśli DATA SOURCE ma 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`lub identyfikator logowania SQL musi mieć poświadczenia REFERENCES CREDENTIAL::<URL> dla magazynu chronionego przez sygnaturę dostępu współdzielonego
OPENROWSET(BULK) ze źródłem danych bez poświadczeń	`ADMINISTER BULK OPERATIONS` lub `ADMINISTER DATABASE BULK OPERATIONS`,
OPENROWSET(BULK) z źródłem danych z poświadczeniami	`REFERENCES DATABASE SCOPED CREDENTIAL`i jeden z lub `ADMINISTER BULK OPERATIONSADMINISTER 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 FORMAT`i `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 SCHEMAALTER ANY DATA SOURCE`i `ALTER ANY EXTERNAL FILE FORMAT`. Aby odczytać dane: `ADMINISTER BULK OPERATIONS` lub `SELECT TABLEREFERENCES CREDENTIAL` na każdą tabelę/widok/funkcję w zapytaniu + uprawnienie R/W do magazynu

Następne kroki

Teraz możesz kontynuować pracę z następującymi artykułami z instrukcjami:

Share via