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

Tip

Microsoft Fabric Data Warehouse to magazyn relacyjny w skali przedsiębiorstwa na podstawie bazy danych data lake z architekturą gotową do użycia w przyszłości, wbudowaną sztuczną inteligencją i nowymi funkcjami. Jeśli dopiero zaczynasz korzystać z magazynowania danych, zacznij od Fabric Data Warehouse. Istniejące obciążenia dedykowanej puli SQL mogą zostać zaktualizowane do Fabric, aby uzyskać dostęp do nowych możliwości w zakresie nauki o danych, analizy w czasie rzeczywistym i raportowania.

• Rozpocznij darmowy okres próbny Fabric.
• Asystent migracji dla Fabric Data Warehouse.

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:

• użytkownik Microsoft Entra — OPENROWSET użyje tożsamości dzwoniącego Microsoft Entra, aby uzyskać dostęp do Azure Storage lub uzyskać anonimowy dostęp.
• 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.

Podszywanie się

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ę, zaawansowany użytkownik z uprawnieniami ALTER ANY CREDENTIAL musi utworzyć poświadczenie o zakresie serwera, które pasuje do adresu URL w funkcji (przy użyciu nazwy magazynu i kontenera) i przyznać uprawnienia REFERENCES dla tego poświadczenia osobie wywołującej funkcję 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ć przy użyciu niektórych tożsamości Microsoft Entra.

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

Podszywanie się

DATABASE SCOPED CREDENTIAL określa sposób uzyskiwania dostępu do plików w przywoływanych źródłach danych (obecnie Shared Access Signature i tożsamość zarządzana). Zaawansowany użytkownik z CONTROL DATABASE uprawnieniami musi utworzyć DATABASE SCOPED CREDENTIAL, który będzie używany do uzyskania dostępu do magazynu, oraz EXTERNAL DATA SOURCE, który określa adres URL źródła danych i poświadczenie, które powinno 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&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) ;

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

• Jedno z uprawnień do wykonywania operacji OPENROWSET:
  • ADMINISTER BULK OPERATIONS umożliwia zalogowanie się w celu wykonania funkcji OPENROWSET.
  • ADMINISTER DATABASE BULK OPERATIONS umożliwia użytkownikowi, którego zakres ogranicza się do bazy danych, wykonywanie funkcji OPENROWSET.
• REFERENCES DATABASE SCOPED CREDENTIAL do poświadczenia, na które odwołuje się EXTERNAL DATA SOURCE.

Bezpośredni dostęp

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ć Microsoft Entra uwierzytelniania przekazywanego:

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ć uwierzytelniani przy użyciu tokenu SAS lub Zarządzanej Tożsamości przestrzeni roboczej Managed Identity of workspace, jeśli nie mogą bezpośrednio uzyskać dostępu do plików za pomocą swojej tożsamości Microsoft Entra lub w trybie dostępu anonimowego anonymous access.

Podszywanie się

Poświadczenie obejmujące bazę danych określa sposób dostępu do plików w odniesieniu do źródła danych. Użytkownik z uprawnieniem CONTROL DATABASE będzie musiał utworzyć poświadczenie o zakresie bazy danych, które będzie używane do uzyskiwania dostępu do magazynu, oraz ZEWNĘTRZNE ŹRÓDŁO DANYCH, które określa adres URL źródła i poświadczenie, które powinno 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
 ) ;

Bezpośredni dostęp

Użytkownik może utworzyć ZEWNĘTRZNE ŹRÓDŁO DANYCH bez danych uwierzytelniających, które będzie odwoływać się do magazynu z publicznym dostępem LUB użyć 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 uprawnienie dla 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 logowanie do SQL musi mieć zgodę REFERENCES CREDENTIAL::<URL> dla przechowywania chronionego przez SAS
OPENROWSET(BULK) ze źródłem danych bez poświadczenia	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 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. Aby odczytać dane: ADMINISTER BULK OPERATIONS lub REFERENCES CREDENTIAL lub SELECT TABLE dla każdej tabeli/widoku/funkcji w zapytaniu + uprawnienie R/W do magazynu

Następne kroki

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

• Zapytania dotyczące danych w pamięci

• Wykonywanie zapytań względem pliku CSV

• Wykonywanie zapytań względem plików Parquet

• Wykonywanie zapytań dotyczących plików JSON

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

• Korzystanie z funkcji partycjonowania i metadanych

• Typy zagnieżdżone zapytań

• Tworzenie i używanie widoków