使用 Azure SQL Database 進行資料虛擬化（預覽）

2025-06-18

Azure SQL Database 的數據虛擬化功能可讓您對以 CSV 等通用數據格式儲存數據的檔案執行 Transact-SQL （T-SQL）查詢（不需要使用分隔符文字）、Parquet 和 Delta （1.0）。您可以在 Azure Data Lake Storage Gen2 或 Azure Blob 記憶體中查詢此數據，並使用聯結將其與本機儲存的關係型數據合併。如此一來，您就可用透明方式存取外部資料 (唯讀模式下)，同時保留其原始格式和位置，這也稱為資料虛擬化。

概觀

資料虛擬化提供兩種方式來查詢用於不同情節組合的檔案：

OPENROWSET 語法 – 最佳化檔案的特定查詢。通常用於快速探索一組新檔案的內容和結構。
CREATE EXTERNAL TABLE 語法 – 最佳化檔案的重複查詢，使用的語法與資料儲存在本機資料庫所使用的語法相同。相較於 OPENROWSET 語法，外部資料表需要進行多項準備工作，但可提高您對資料存取的控制權。外部資料表通常用於分析工作負載和報告。

不論是哪一種情況，都必須使用本文示範的 CREATE EXTERNAL DATA SOURCE T-SQL 語法建立外部資料來源。

檔案格式

直接支援 Parquet 和分隔符號文字 (CSV) 檔案格式。當查詢以個別資料列的形式傳回每份文件時，您可藉由指定 CSV 檔案格式，間接支援 JSON 檔案格式。您可以使用 JSON_VALUE 和 OPENJSON，進一步剖析資料列。

儲存體類型

檔案可以儲存在 Azure Data Lake Storage Gen2 或 Azure Blob Storage。若要查詢檔案，您必須以特定格式提供位置，並使用對應外部來源和端點/通訊協定類型的位置類型前置詞，如下列範例所示：

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet
--or
abs://<storage_account_name>.blob.core.windows.net/<container_name>/

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet
--or
adls://<storage_account_name>.dfs.core.windows.net/<container_name>/

這很重要

一律使用端點特定的前置詞。提供的位置類型前置詞可用來選擇最佳的通訊協定，並利用特定儲存體類型所提供的任何進階功能。

僅在https://中支援泛型BULK INSERT前綴詞，但不支援其他使用案例，包括OPENROWSET或EXTERNAL TABLE。

開始吧

如果您不熟悉資料虛擬化且想要快速測試功能，請從 Azure 開放資料集中查詢可用的公開資料集開始，例如允許匿名存取的 Bing COVID-19 資料集。

使用下列端點來查詢 BING COVID-19 資料集：

Parquet：abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
CSV：abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

如需快速開始，請執行這個簡單的 T-SQL 查詢，以初步瞭解資料集。此查詢會使用 OPENROWSET 來查詢儲存在公開可用儲存體帳戶中的檔案：

--Quick query on a file stored in a publicly available storage account:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
 FORMAT = 'parquet'
) AS filerows;

您可以根據第一個查詢的結果集，附加 WHEREGROUP BY 和其他子句，以繼續探索數據集。

當您熟悉查詢公用資料集之後，請考慮切換至需要提供認證、授與存取權限及設定防火牆規則的的非公用資料集。在許多實際案例中，您的主要操作都是使用私人資料集。

存取非公用儲存體帳戶

登入 Azure SQL Database 的用戶必須獲得授權，才能存取和查詢儲存在非公用記憶體帳戶中的檔案。授權步驟取決於 Azure SQL Database 驗證記憶體的方式。每個查詢都不會直接提供驗證類型和任何相關參數。這些資料會封裝在資料庫範圍的認證物件中，而該物件儲存在使用者資料庫中。只要執行查詢，該資料庫就會使用認證來存取儲存體帳戶。

Azure SQL Database 支援下列驗證類型：

共用存取簽章 (SAS)
受管理的識別
透過使用者身分識別Microsoft Entra 傳遞驗證

共用存取簽章（SAS）提供對記憶體帳戶中檔案的委派存取權。 SAS 可讓您更細微地控制您授與的存取類型，包括有效間隔、授與的許可權，以及可接受的 IP 位址範圍。建立 SAS 令牌之後，就無法撤銷或刪除它，而且允許存取，直到其有效期限到期為止。

您可以透過多種方式取得 SAS 令牌：
- 流覽至 Azure 入口網站 -> 您的記憶體帳戶 ->共用存取簽章 - 設定許可權 ->> 產生 SAS 和連接字串。如需詳細資訊，請參閱產生共用存取簽章。
- 使用 Azure 記憶體總管建立和設定 SAS。
- 您可以透過PowerShell、Azure CLI、.NET和 REST API，以程式設計方式建立 SAS 令牌。如需詳細資訊，請參閱使用共用存取簽章 (SAS) 對 Azure 儲存體資源授與有限存取權。
透過 SAS 授與讀取和列出權限，以存取外部資料。目前，使用 Azure SQL Database 的數據虛擬化是唯讀的。
若要在 Azure SQL Database 中建立資料庫範圍認證，如果資料庫主要密鑰不存在，您必須先建立資料庫主要密鑰。當認證需要 SECRET時，需要資料庫主要密鑰。
```
-- Create MASTER KEY if it doesn't exist in the database:
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>';
```
產生 SAS 令牌時，它會在令牌開頭包含問號（?）。若要使用權杖，您必須在建立認證時移除問號 (?)。例如：
```
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=secret string here';
```

受管理的身分識別是 Microsoft Entra ID（前稱為 Azure Active Directory）的一項功能，這功能為 Azure 服務（如 Azure SQL 資料庫）提供一個在 Microsoft Entra ID 中管理的身分識別。此身分識別可用來授權非公用儲存體帳戶中資料存取要求。 Azure SQL 資料庫可以有系統指派的受控識別和一或多個使用者指派的受控識別。但一次只能使用一個受控識別。

Azure 儲存體管理員必須首先將權限授與受控識別，才能存取資料。授予 SQL 資料庫的系統指派受控身分識別許可權，方式與授予任何其他 Microsoft Entra 使用者的許可權相同。例如：

在 Azure 入口網站中，瀏覽至您的儲存體帳戶。
在記憶體帳戶的 [ 存取控制（IAM）] 頁面中，選取 [新增角色指派]。
選擇儲存體Blob 資料讀取器 Azure RBAC 角色。選取 下一步。此角色會針對必要的 Azure Blob 記憶體容器，提供受控識別的讀取許可權。
- 您也可以對檔案子集授與更細微的許可權，而不是將成員資格授與受控識別的記憶體 Blob 資料讀取器 Azure RBAC 角色。所有需要存取權才能讀取此容器中個別檔案的使用者， 也必須擁有根 目錄上所有父資料夾的執行許可權（容器）。如需詳細資訊，請參閱在 Azure Data Lake Storage Gen2 中設定 ACL。
在下一頁的 [存取權指派對象] 中，選取 [受控識別]。
選擇 + 選取成員。搜尋並選取受管理的身份識別。如需詳細資訊，請參閱使用 Azure 入口網站指派 Azure 角色。
使用 CREATE DATABASE SCOPED CREDENTIAL （Transact-SQL），在 Azure SQL 資料庫中建立受控識別驗證的資料庫範圍認證。在下列範例中， IDENTITY = 'Managed Identity' 是硬式編碼字串。相同的IDENTITY = 'Managed Identity' 同時用於系統指派的受控識別和使用者指派的受控識別。使用受控識別時，不需要資料庫主要金鑰，因為認證不包含 SECRET。
```
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity';
```

使用者身分，也稱為「Microsoft Entra 傳遞」，是一種授權方法，其中 Microsoft Entra 使用者的身分具有 Azure SQL Database 的存取權限。

授予 Microsoft Entra ID 使用者許可權。
- 您必須是記憶體 Blob 資料擁有者、記憶體 Blob 數據參與者或記憶體 Blob 資料讀取者角色的成員，才能使用身分識別來存取數據。
- 或者，您可以指定更細緻的 ACL 規則來存取檔案和資料夾。即使您是記憶體帳戶的擁有者，您仍然需要將自己新增至其中一個記憶體 Blob 數據角色。若要深入瞭解 Azure Data Lake Store Gen2 中的訪問控制，請參閱 Azure Data Lake Storage Gen2 中的訪問控制。
- Microsoft Entra 傳遞驗證不支援 SQL 驗證登入類型。
為具有 Azure SQL Database 存取權的 Microsoft Entra ID 帳戶建立資料庫範圍認證。將 <UserCredential> 替換為身分識別名稱，例如 identity-<random string>。在下列範例中， IDENTITY = 'User Identity' 是硬式編碼字串。使用受控使用者識別時，不需要資料庫主要金鑰，因為認證不包含 SECRET。
```
CREATE DATABASE SCOPED CREDENTIAL <UserCredential>
WITH IDENTITY = 'User Identity';
```

這很重要

用戶端應用程式可能會快取Microsoft Entra 驗證令牌。例如，Power BI 會快取 Microsoft Entra 令牌，並在一小時內重複使用相同的令牌。如果令牌在查詢執行中間到期，長時間執行的查詢可能會失敗。如果您因 Microsoft Entra 存取令牌在查詢過程中到期而遭遇查詢失敗，請考慮切換至受控識別或共用存取簽章（SAS）。

透過匿名帳戶存取公用記憶體

如果所需的數據集允許公用存取（也稱為匿名存取），只要已正確設定 Azure 記憶體，就不需要認證，請參閱設定容器和 Blob 的匿名讀取存取權。

外部資料來源

外部資料來源是一種抽象概念，可讓您在多個查詢中輕鬆參考某一檔案位置。若要查詢公用位置，則在建立外部資料來源時只需要指定檔案位置：

CREATE EXTERNAL DATA SOURCE MyExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
);

存取非公用儲存體帳戶以及位置時，您也需要參考具有封裝驗證參數的資料庫範圍認證。下列指令碼會建立指向檔案路徑的外部資料來源，並參考資料庫範圍的認證。

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       CREDENTIAL = [MyCredential]
);

使用 OPENROWSET 查詢資料來源

OPENROWSET 語法可啟用即時特定查詢，同時只需建立最少量的必要資料庫物件。

OPENROWSET 只需要建立外部資料來源 (可能為認證)，而不是外部表格方法，這需要外部檔案格式和外部表格本身。

DATA_SOURCE 參數值會自動在前面加上 BULK 參數，以形成檔案的完整路徑。

使用 OPENROWSET 時會提供檔案的格式 (例如下列範例)，其會查詢單一檔案：

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

查詢多個檔案和資料夾

OPENROWSET 命令也允許使用 BULK 路徑中的萬用字元來查詢多個檔案或資料夾。

下列範例使用 NYC 黃色計程車車程記錄開放資料集。

首先，建立外部資料來源：

--Create the data source first:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

現在，我們可以查詢資料夾中所有擴展名為 .parquet 的檔案。例如，這裡只會查詢符合名稱模式的檔案：

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

查詢多個檔案或資料夾時，使用單一 OPENROWSET 存取的所有檔案都必須具有相同結構 (例如相同數目的資料行和資料類型)。資料夾無法以遞迴方式周遊。

架構推斷

自動結構描述推斷可協助您快速撰寫查詢，以及在不知道檔案結構描述時探索資料。結構描述推斷只適用於 Parquet 檔案。

雖然方便，但推斷的資料類型可能大於實際資料類型，因為來源檔案中可能有足夠的資訊，以確保使用適當的資料類型。這可能會導致查詢效能不佳。例如，Parquet 檔案不包含有關字元行長度上限的中繼資料，因此執行個體將其推斷為 varchar(8000)。

使用 sp_describe_first_results_set 預存程序來檢查您的查詢產生的資料類型，如下列範例所示：

EXEC sp_describe_first_result_set N'
 SELECT
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

一旦您知道資料類型之後，您就可以使用 WITH 子句來指定類型，以改善效能：

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount
FROM
OPENROWSET(
 BULK 'yellow/*/*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000
tpepPickupDateTime datetime2,
passengerCount int
) AS nyc;

因為無法自動判斷 CSV 檔案的結構描述，因此一律必須使用 WITH 子句指定資料行：

SELECT TOP 10 id, updated, confirmed, confirmed_change
FROM OPENROWSET(
 BULK 'bing_covid-19_data.csv',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'CSV',
 FIRSTROW = 2
)
WITH (
 id int,
 updated date,
 confirmed int,
 confirmed_change int
) AS filerows;

檔案中繼資料函式

查詢多個檔案或資料夾時，您可以使用 filepath() 和 filename() 函式來讀取檔案中繼資料，並取得結果集來源中資料列的檔案名稱、完整路徑或部分路徑：

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

若在沒有參數的情況下進行呼叫時，filepath() 函式會傳回資料列來源的檔案路徑。在 DATA_SOURCE 中使用 OPENROWSET 時，其會傳回相對於 DATA_SOURCE 的路徑，否則會傳回完整的檔案路徑。

若以參數進行呼叫，則會傳回路徑的一部分，該部分會與參數中所指定位置上的萬用字元相符。例如，參數值 1 會傳回符合第一個萬用字元的路徑部分。

filepath() 函式也可用於篩選和匯總資料列：

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

在 OPENROWSET 頂端建立檢視

您可以建立和使用檢視來包裝 OPENROWSET 查詢，以便輕鬆地重複使用基礎查詢：

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

使用 filepath() 函式將具有檔案位置資料的資料行新增至檢視也很方便，可讓您更容易且更有效能地進行篩選。當由下列任意資料行進行篩選時，使用檢視可減少檔案數目，以及檢視頂端查詢需要讀取和處理的資料量：

CREATE VIEW TaxiRides AS
SELECT *
 , filerows.filepath(1) AS [year]
 , filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

檢視也會啟用報告和分析工具 (例如 Power BI) 來取用 OPENROWSET 的結果。

外部數據表

外部表格會封裝對檔案的存取，讓查詢體驗幾乎與查詢儲存在使用者資料表中的本機關聯式資料完全相同。建立外部表格需要存在外部資料來源和外部檔案格式物件：

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
);

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);

外部資料表建立後，您可像任何其他資料表一樣加以查詢：

SELECT TOP 10 *
FROM tbl_TaxiRides;

如同 OPENROWSET，外部資料表允許使用萬用字元查詢多個檔案和資料夾。外部表格不支援結構描述推斷。

效能考量

對於可以查詢的檔案數目或資料量，並沒有任何硬性限制，但查詢效能需視查詢和聯結的資料量、資料格式、資料組織方式和複雜度而定。

查詢分割區的資料

資料的組織方式通常會分成不同的子資料夾，也稱為分割區。您可以指示查詢唯讀特定資料夾和檔案。這麼做會減少查詢需要讀取和處理的檔案數目和資料量，進而提升效能。這種類型的查詢最佳化稱為資料分割剪除或資料分割刪除。您可以在查詢的子句中使用元數據函filepath()WHERE式，從查詢執行中排除數據分割。

下列範例查詢只會讀取 2017 年最後三個月的 NYC Yellow Taxi 資料檔案：

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'yellow/puYear=*/puMonth=*/*.parquet',
        DATA_SOURCE = 'NYCTaxiExternalDataSource',
        FORMAT = 'parquet'
    )
WITH (
    vendorID INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

如果您的儲存資料未分割，請考慮進行資料分割以提升查詢效能。

如果您使用外部表格，filepath() 和 filename() 功能不受支援，但不在 WHERE 子句中。

疑難排解

查詢執行的問題通常是因為 Azure SQL Database 無法存取檔案位置所造成。相關錯誤訊息可能會指出沒有足夠的存取權限、非現有的位置或檔案路徑、檔案由其他程序所使用，或是無法列出目錄。在大部分情況下，這表示網路流量控制原則封鎖了對檔案的存取，或是沒有存取權限。這是應該檢查的內容：

錯誤或拼字錯誤的位置路徑。
SAS 金鑰有效性：可能已過期、包含錯字，或以問號開頭。
允許的 SAS 金鑰權限：至少有讀取權，若使用萬用字元則有列出權。
封鎖了儲存體帳戶上的輸入流量。檢查管理 Azure 記憶體的虛擬網路規則。
受控識別訪問許可權：請確定 Azure SQL Database 的受控識別已獲授與記憶體帳戶的訪問許可權。
資料庫的相容性層級必須是 130 或更高，才能執行資料虛擬化查詢。

局限性

目前，Azure SQL Database 不支援外部數據表的統計數據。
目前無法在 CREATE EXTERNAL TABLE AS SELECT Azure SQL Database 上使用。
外部資料表不支援資料列層級安全性功能。
無法為外部資料表中的資料行定義動態資料遮罩規則。
如果 Azure 儲存體帳戶位於不同的租用戶中，受控識別不支援跨租用戶場景，因此共用存取簽章是支援的方法。

已知問題

在 SQL Server Management Studio (SSMS) 中啟用 Always Encrypted 的參數化時，資料虛擬化查詢失敗並顯示 Incorrect syntax near 'PUSHDOWN' 錯誤訊息。