共用方式為


在 Azure Synapse Analytics 中使用無伺服器 SQL 集區存取外部儲存體

本文說明使用者如何從儲存在無伺服器 SQL 集區Azure 儲存體上的檔案讀取資料。 使用者有下列選項可存取儲存體:

  • OPENROWSET 函式,可針對 Azure 儲存體 中的檔案啟用臨機操作查詢。
  • 外部資料表 ,這是建置在一組外部檔案之上的預先定義資料結構。

使用者可以使用 不同的驗證方法 ,例如 Microsoft Entra 傳遞驗證(Microsoft Entra 主體的預設值)和 SAS 驗證(SQL 主體的預設值)。

使用 OPENROWSET 查詢檔案

OPENROWSET 可讓使用者在 Azure 儲存體上查詢外部檔案,如果他們具有儲存體的存取權。 連線到無伺服器 SQL 集區的使用者應該使用下列查詢來讀取 Azure 儲存體上的檔案內容:

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

使用者可以使用下列存取規則來存取儲存體:

  • Microsoft Entra 使用者 - OPENROWSET 將使用呼叫者的 Microsoft Entra 身分識別來存取Azure 儲存體或存取具有匿名存取權的儲存體。
  • SQL 使用者 – OPENROWSET 將存取具有匿名存取權的儲存體,或使用工作區的 SAS 權杖或受控識別來模擬。

SQL 主體也可以使用 OPENROWSET 直接查詢受 SAS 權杖或工作區受控識別保護的檔案。 如果 SQL 使用者執行此函式,具有 ALTER ANY CREDENTIAL 許可權的進階使用者必須建立符合函式中 URL 的伺服器範圍認證(使用儲存體名稱和容器),並將此認證的 REFERENCES 許可權授與此認證給 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

如果沒有符合 URL 的伺服器層級 CREDENTIAL,或 SQL 使用者沒有此認證的參考許可權,則會傳回錯誤。 SQL 主體無法使用某些 Microsoft Entra 身分識別模擬。

注意

此版本的 OPENROWSET 是專為使用預設驗證快速且簡單的資料探索而設計。 若要利用模擬或受控識別,請使用 OPENROWSET 搭配下一節所述的DATA_SOURCE。

使用 OPENROWSET 查詢資料來源

OPENROWSET 可讓使用者查詢放在某些外部資料源上的檔案:

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

執行此查詢的使用者必須能夠存取檔案。 如果使用者無法使用其 Microsoft Entra 身分識別或 匿名存取 直接存取檔案,則必須使用 SAS 權杖 工作區 的受控識別來模擬使用者。

DATABASE SCOPED CREDENTIAL 指定如何存取參考資料源上的檔案(目前 SAS 和受控識別)。 具有 CONTROL DATABASE 許可權的 Power 使用者必須建立 DATABASE SCOPED CREDENTIAL ,以用來存取儲存體,以及 EXTERNAL DATA SOURCE 指定應使用的資料來源和認證 URL:

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

呼叫端必須具有下列其中一個許可權,才能執行 OPENROWSET 函式:

  • 執行 OPENROWSET 的其中一個許可權:
    • ADMINISTER BULK OPERATIONS 可讓登入執行 OPENROWSET 函式。
    • ADMINISTER DATABASE BULK OPERATIONS 可讓資料庫範圍的使用者執行 OPENROWSET 函式。
  • REFERENCES DATABASE SCOPED CREDENTIAL 至 中 EXTERNAL DATA SOURCE 參考的認證。

EXTERNAL TABLE

具有讀取資料表許可權的使用者可以使用在一組Azure 儲存體資料夾和檔案上建立的 EXTERNAL TABLE 來存取外部檔案。

具有 建立外部資料表 許可權的使用者(例如 CREATE TABLE 和 ALTER ANY CREDENTIAL 或 REFERENCES DATABASE SCOPED CREDENTIAL)可以使用下列腳本,在Azure 儲存體資料來源之上建立資料表:

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

讀取此資料表資料的使用者必須能夠存取檔案。 如果使用者無法使用其 Microsoft Entra 身分識別或 匿名存取 直接存取檔案,則必須使用 SAS 權杖 工作區 的受控識別來模擬使用者。

DATABASE SCOPED CREDENTIAL 會指定如何存取參考資料源上的檔案。 具有 CONTROL DATABASE 許可權的使用者必須建立 DATABASE SCOPED CREDENTIAL,以用來存取儲存體和 EXTERNAL DATA SOURCE,以指定應使用的資料來源和認證 URL:

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

使用 EXTERNAL TABLE 讀取外部檔案

EXTERNAL TABLE 可讓您使用標準 SQL SELECT 語句,從透過資料來源參考的檔案讀取資料:

SELECT *
FROM dbo.DimProductsExternal

呼叫端必須具有下列許可權才能讀取資料:

  • SELECT 許可權 ON 外部資料表
  • REFERENCES DATABASE SCOPED CREDENTIAL 如果 DATA SOURCE 具有,則為 permission CREDENTIAL

權限

下表列出上述作業的必要許可權。

Query 所需的權限
不含資料來源的 OPENROWSET(BULK) ADMINISTER BULK OPERATIONSADMINISTER DATABASE BULK OPERATIONS 或 SQL 登入必須具有 SAS 保護儲存體的 REFERENCES CREDENTIAL:: < URL >
不含認證的資料來源 OPENROWSET(BULK) ADMINISTER BULK OPERATIONSADMINISTER DATABASE BULK OPERATIONS
具有認證之資料來源的 OPENROWSET(BULK) REFERENCES DATABASE SCOPED CREDENTIAL 和其中一個 ADMINISTER BULK OPERATIONSADMINISTER DATABASE BULK OPERATIONS
CREATE EXTERNAL DATA SOURCE ALTER ANY EXTERNAL DATA SOURCEREFERENCES DATABASE SCOPED CREDENTIAL
CREATE EXTERNAL TABLE CREATE TABLEALTER ANY SCHEMAALTER ANY EXTERNAL FILE FORMATALTER ANY EXTERNAL DATA SOURCE
SELECT FROM EXTERNAL TABLE SELECT TABLEREFERENCES DATABASE SCOPED CREDENTIAL
CETAS 若要建立資料表 - CREATE TABLE 、、 ALTER ANY DATA SOURCE ALTER ANY SCHEMAALTER ANY EXTERNAL FILE FORMAT 。 若要讀取資料: ADMINISTER BULK OPERATIONSREFERENCES CREDENTIAL SELECT TABLE 每個資料表/檢視/函式在儲存體上的查詢 + R/W 許可權

下一步

您現在已準備好繼續進行下列操作說明文章: