Přístup k externímu úložišti pomocí bezserverového fondu SQL v Azure Synapse Analytics

Tento článek popisuje, jak uživatelé můžou číst data ze souborů uložených na Azure Storage v bezserverovém fondu SQL. Uživatelé mají pro přístup k úložišti následující možnosti:

  • Funkce OPENROWSET, která umožňuje ad hoc dotazy na soubory v Azure Storage.
  • Externí tabulka , která je předdefinovaná datová struktura založená na sadě externích souborů.

Uživatel může použít různé metody ověřování, jako je Azure AD předávací ověřování (výchozí pro objekty zabezpečení Azure AD) a ověřování SAS (výchozí pro objekty zabezpečení SQL).

Dotazování souborů pomocí OPENROWSET

OpenROWSET umožňuje uživatelům dotazovat se na externí soubory v úložišti Azure, pokud mají přístup k úložišti. Uživatel, který je připojený k bezserverové SQL fondu, by měl ke čtení obsahu souborů v úložišti Azure použít následující dotaz:

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

Uživatel má přístup k úložišti pomocí následujících pravidel přístupu:

  • Azure AD uživatel – OPENROWSET použije Azure AD identitu volajícího pro přístup k Azure Storage nebo přístupu k úložišti s anonymním přístupem.
  • SQL uživatel – OPENROWSET bude mít přístup k úložišti s anonymním přístupem nebo může být zosobněný pomocí tokenu SAS nebo spravované identity pracovního prostoru.

SQL objekty zabezpečení můžou také použít OPENROWSET k přímému dotazování souborů chráněných pomocí tokenů SAS nebo spravované identity pracovního prostoru. Pokud uživatel SQL tuto funkci spustí, musí power uživatel s ALTER ANY CREDENTIAL oprávněním vytvořit přihlašovací údaje s oborem serveru, které odpovídají adrese URL ve funkci (pomocí názvu úložiště a kontejneru), a udělit odkazy na tyto přihlašovací údaje volající funkci 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

Pokud neexistuje přihlašovací údaje na úrovni serveru, které odpovídají adrese URL, nebo SQL uživatel nemá oprávnění odkazy na tyto přihlašovací údaje, vrátí se chyba. SQL objekty zabezpečení nemůžou zosobnit pomocí některé identity Azure AD.

Poznámka

Tato verze OPENROWSET je určená pro rychlé a snadné zkoumání dat pomocí výchozího ověřování. Pokud chcete využít zosobnění nebo spravovanou identitu, použijte OPENROWSET s DATA_SOURCE popsané v další části.

Provádění dotazů do zdrojů dat funkcí OPENROWSET

OPENROWSET umožňuje uživateli dotazovat se na soubory umístěné v některém externím zdroji dat:

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

Uživatel, který tento dotaz spustí, musí mít přístup k souborům. Uživatelé musí být zosobněni pomocí tokenu SAS nebo spravované identity pracovního prostoru, pokud nemají přímý přístup k souborům pomocí své Azure AD identity nebo anonymního přístupu.

DATABASE SCOPED CREDENTIAL určuje, jak získat přístup k souborům v odkazovaném zdroji dat (aktuálně SAS a spravovaná identita). Power user with CONTROL DATABASE permission would need to create DATABASE SCOPED CREDENTIAL that will used to access storage and EXTERNAL DATA SOURCE that specifies URL of data source and credential that 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&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) ;

Volající musí mít k provedení funkce OPENROWSET jednu z následujících oprávnění:

  • Jedno z oprávnění ke spuštění OPENROWSET:
    • ADMINISTER BULK OPERATIONS povoluje přihlášení spouštět funkci OPENROWSET.
    • ADMINISTER DATABASE BULK OPERATIONS povoluje uživateli s oborem databáze spouštět funkci OPENROWSET.
  • REFERENCES DATABASE SCOPED CREDENTIALpřihlašovací údaje, na které odkazuje .EXTERNAL DATA SOURCE

EXTERNÍ TABULKA

Uživatel s oprávněními ke čtení tabulky má přístup k externím souborům pomocí EXTERNÍ TABULKY vytvořené nad sadou Azure Storage složek a souborů.

Uživatel s oprávněními k vytvoření externí tabulky (například CREATE TABLE a ALTER ANY CREDENTIAL nebo REFERENCES DATABASE SCOPED CREDENTIAL) může pomocí následujícího skriptu vytvořit tabulku nad Azure Storage zdrojem dat:

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

Uživatel, který čte data z této tabulky, musí mít přístup k souborům. Uživatelé musí být zosobnění pomocí tokenu SAS nebo spravované identity pracovního prostoru, pokud nemají přímý přístup k souborům pomocí jejich Azure AD identity nebo anonymního přístupu.

DATABÁZOVÁ SCOPED CREDENTIAL určuje, jak přistupovat k souborům v odkazovaném zdroji dat. Uživatel s oprávněním CONTROL DATABASE by měl vytvořit PŘIHLAŠOVACÍ ÚDAJE S OBOREM DATABÁZE, které se použijí pro přístup k úložišti a externímu zdroji dat, který určuje adresu URL zdroje dat a přihlašovacích údajů, které by se měly použít:

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

Čtení externích souborů pomocí EXTERNÍ TABULKY

EXTERNÍ TABULKA umožňuje číst data ze souborů odkazovaných prostřednictvím zdroje dat pomocí standardního příkazu SQL SELECT:

SELECT *
FROM dbo.DimProductsExternal

Volající musí mít pro čtení dat následující oprávnění:

  • SELECT oprávnění K externí tabulce
  • REFERENCES DATABASE SCOPED CREDENTIAL oprávnění, pokud DATA SOURCECREDENTIAL

Oprávnění

Následující tabulka uvádí požadovaná oprávnění pro výše uvedené operace.

Dotaz Požadovaná oprávnění
OPENROWSET(BULK) bez zdroje dat ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONSnebo SQL přihlášení musí obsahovat ODKAZY CREDENTIAL::<URL> pro úložiště chráněného SAS.
OPENROWSET(BULK) se zdroji dat bez přihlašovacích údajů ADMINISTER BULK OPERATIONS nebo ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) se zdroji dat s přihlašovacími údaji REFERENCES DATABASE SCOPED CREDENTIAL a jeden z ADMINISTER BULK OPERATIONS nebo ADMINISTER DATABASE BULK OPERATIONS
VYTVOŘENÍ EXTERNÍHO ZDROJE DAT ALTER ANY EXTERNAL DATA SOURCE a REFERENCES DATABASE SCOPED CREDENTIAL
VYTVOŘENÍ EXTERNÍ TABULKY CREATE TABLE, , ALTER ANY SCHEMAALTER ANY EXTERNAL FILE FORMATaALTER ANY EXTERNAL DATA SOURCE
VÝBĚR Z EXTERNÍ TABULKY SELECT TABLE a REFERENCES DATABASE SCOPED CREDENTIAL
CETAS Vytvoření tabulky - CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY DATA SOURCEa ALTER ANY EXTERNAL FILE FORMAT. Čtení dat: ADMINISTER BULK OPERATIONS nebo REFERENCES CREDENTIALSELECT TABLE pro každou tabulku, zobrazení nebo funkci v dotazu + R/W oprávnění k úložišti

Další kroky

Teď jste připraveni pokračovat v následujících článcích s postupy: