Azure SQL 受控執行個體的資料虛擬化
適用於：
Azure SQL 受控執行個體
使用 Azure SQL 受控執行個體的資料虛擬化功能，可讓您針對儲存在 Azure Data Lake Storage Gen2 或 Azure Blob 儲存體中以常用資料格式儲存資料的檔案執行 Transact-SQL (T-SQL) 查詢，並使用聯結將其與本機儲存的關聯式資料合併。 如此一來，您就可用透明方式存取外部資料 (唯讀模式下)，同時保留其原始格式和位置，這也稱為資料虛擬化。
概觀
資料虛擬化提供兩種方式來查詢用於不同情節組合的檔案：
- OPENROWSET 語法 – 最佳化檔案的特定查詢。 通常用於快速探索一組新檔案的內容和結構。
- CREATE EXTERNAL TABLE 語法 – 最佳化檔案的重複查詢，使用的語法與資料儲存在本機資料庫所使用的語法相同。 相較於 OPENROWSET 語法，外部資料表需要進行多項準備工作，但可提高您對資料存取的控制權。 外部資料表通常用於分析工作負載和報告。
不論是哪一種情況，都必須使用本文示範的 CREATE EXTERNAL DATA SOURCE T-SQL 語法建立外部資料來源。
此外，也可針對 Azure SQL 受控執行個體使用 CREATE EXTERNAL TABLE AS SELECT 語法，將 T-SQL SELECT 陳述式的結果匯出至 Azure Blob 儲存體或 Azure Data Lake Storage (ADLS) Gen 2 中的 Parquet 或 CSV 檔案，並在這些檔案之上建立外部表格。
檔案格式
直接支援 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
--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet
重要
提供的位置類型前置詞可用來選擇最佳的通訊協定，並利用特定儲存體類型所提供的任何進階功能。
已停用使用一般 https:// 前置詞。 一律使用端點特定的前置詞。
開始使用
如果您不熟悉資料虛擬化且想要快速測試功能，請從 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
您可以根據第一個查詢的結果集，附加 WHERE、GROUP BY 和其他子句，以繼續執行資料集探索。
如果受控執行個體上的第一個查詢失敗，該執行個體的 Azure 儲存體帳戶存取權可能受到限制，您應向網路專家諮詢以啟用存取權，然後才能繼續進行查詢。
當您熟悉查詢公用資料集之後，請考慮切換至需要提供認證、授與存取權限及設定防火牆規則的的非公用資料集。 在許多實際案例中，您的主要操作都是使用私人資料集。
存取非公用儲存體帳戶
登入受控執行個體的使用者必須獲得授權，才能存取和查詢儲存在非公用儲存體帳戶中的檔案。 授權步驟取決於受控執行個體向儲存體進行驗證的方式。 每個查詢都不會直接提供驗證類型和任何相關參數。 這些資料會封裝在資料庫範圍的認證物件中，而該物件儲存在使用者資料庫中。 只要執行查詢，該資料庫就會使用認證來存取儲存體帳戶。 Azure SQL 受控執行個體支援下列驗證類型：
受控識別是 Microsoft Entra ID (之前稱為 Azure Active Directory) 的一項功能，可為 Azure 服務 (例如 Azure SQL 受控執行個體) 提供在 Microsoft Entra ID 中管理的身分識別。 此身分識別可用來授權非公用儲存體帳戶中資料存取要求。 Azure SQL 受控執行個體等服務具有系統指派的受控識別，並且可以有一個或多個使用者指派的受控識別。 您在執行 Azure SQL 受控執行個體的資料虛擬化功能時，可以使用系統指派的受控識別或使用者指派的受控識別。
Azure 儲存體管理員必須首先將權限授與受控識別，才能存取資料。 將權限授與受控執行個體之系統指派的受控識別的方式，與對任何其他 Microsoft Entra 使用者授與權限的方式一樣。 例如：
- 在 Azure 入口網站中，請在儲存體帳戶的 [存取控制 (IAM)] 頁面中選取 [新增角色指派]。
- 選擇 [儲存體 Blob 資料讀者] 內建 Azure RBAC 角色。 這會為必要的 Azure Blob 儲存體容器提供受控識別的讀取存取權。
- 您可以授與更細緻的檔案子集權限，而不是將儲存體 Blob 資料讀者 Azure RBAC 角色授與受控識別。 所有需要權限讀取此容器中個別檔案資料的使用者，也必須具備到上溯到根目錄 (容器) 為止所有父代資料夾的執行權限。 深入了解如何在 Azure Data Lake Storage Gen2 中設定 ACL。
- 在下一個頁面上，選取 [存取權指派對象] [受控識別]。 + 選取成員，然後在 [成員] 下拉式清單下方，選取需要的受控識別。 如需詳細資訊，請參閱使用 Azure 入口網站指派 Azure 角色。
- 然後，為受控識別驗證建立資料庫範圍認證的步驟很簡單。 請注意，在下列範例中
'Managed Identity'是硬式編碼字串。
-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity'
外部資料來源
外部資料來源是一種抽象概念，可讓您在多個查詢中輕鬆參考某一檔案位置。 若要查詢公用位置，則在建立外部資料來源時只需要指定檔案位置：
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://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
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;
--List all paths:
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder]
FROM OPENROWSET(
BULK 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
FORMAT = 'parquet') AS filerows;
若在沒有參數的情況下進行呼叫時，filepath() 函式會傳回資料列來源的檔案路徑。 在 OPENROWSET 中使用 DATA_SOURCE 時，其會傳回相對於 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
)
GO
--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
);
GO
外部資料表建立後，您可像任何其他資料表一樣加以查詢：
SELECT TOP 10 *
FROM tbl_TaxiRides;
如同 OPENROWSET，外部資料表允許使用萬用字元查詢多個檔案和資料夾。 外部表格不支援結構描述推斷。
效能考量
對於可以查詢的檔案數目或資料量，並沒有任何硬性限制，但查詢效能需視查詢和聯結的資料量、資料格式、資料組織方式和複雜度而定。
查詢分割區的資料
資料的組織方式通常會分成不同的子資料夾，也稱為分割區。 您可以指示受控執行個體僅查詢特定資料夾和檔案。 這麼做會減少查詢需要讀取和處理的檔案數目和資料量，進而提升效能。 這種類型的查詢最佳化稱為資料分割剪除或資料分割刪除。 您可以在查詢的 WHERE 子句中使用中繼資料函式 filepath()，這樣就不需要對分割區執行查詢。
下列範例查詢只會讀取 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 子句中。 如果在計算資料列中使用，則仍然可按 filename 或 filepath 篩選。 下列範例為其示範：
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,
[Year] AS CAST(filepath(1) AS INT), --use filepath() for partitioning
[Month] AS CAST(filepath(2) AS INT) --use filepath() for partitioning
)
WITH (
LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = NYCTaxiExternalDataSource,
FILE_FORMAT = DemoFileFormat
);
GO
SELECT *
FROM tbl_TaxiRides
WHERE
[year]=2017
AND [month] in (10,11,12);
如果您的儲存資料未分割，請考慮進行資料分割以提升查詢效能。
統計資料
收集外部資料的統計資料，是最佳化查詢的最重要工作之一。 執行個體愈了解您的資料，執行查詢的速度就愈快。 SQL 引擎查詢最佳化工具是以成本為基礎的最佳化工具。 它會比較各種查詢方案的成本，然後選擇成本最低的方案。 在大多數情況下，它會選擇執行最快的方案。
自動建立統計資料
Azure SQL 受控執行個體會分析傳入的使用者查詢是否缺少統計資料。 如果缺少統計資料，查詢最佳化工具會自動在查詢述詞或聯結條件中的個別資料行上建立統計資料，以改善查詢計畫的基數估計值。 自動建立統計資料是同步進行的，因此，如果您的資料行缺少統計資料，查詢效能可能會略為降低。 為單一資料行建立統計資料的時間，取決於目標檔案的大小。
OPENROWSET 手動統計資料
您可以使用 sys.sp_create_openrowset_statistics 預存程序來建立 OPENROWSET 路徑的單一資料行統計資料，方法是使用單一資料行作為參數，傳遞所選取查詢：
EXEC sys.sp_create_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
FORMAT = ''parquet'') AS filerows
';
根據預設，執行個體會使用資料集中提供的 100% 資料來建立統計資料。 您可以選擇性地使用 TABLESAMPLE 選項，將取樣大小指定為百分比。 若要為多個資料行建立單一資料行統計資料，請針對每個資料行執行 sys.sp_create_openrowset_statistics。 您無法為 OPENROWSET 路徑建立多資料行統計資料。
若要更新現有的統計資料，請先使用 sys.sp_drop_openrowset_statistics 預存程序卸除統計資料，然後使用 sys.sp_create_openrowset_statistics 重新建立統計資料：
EXEC sys.sp_drop_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
FORMAT = ''parquet'') AS filerows
';
外部資料表手動統計資料
在外部資料表上建立統計資料的語法，類似於用於一般使用者資料表的語法。 若要建立資料行的統計資料，請提供統計資料物件名稱和資料行名稱：
CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendorID)
WITH FULLSCAN, NORECOMPUTE;
WITH 選項是必要項目，而且針對取樣大小的允許選項為 FULLSCAN 和 SAMPLE n 百分比。
- 若要為多個資料行建立單一資料行統計資料，請針對每個資料行執行
CREATE STATISTICS。 - 不支援多重資料行統計資料。
疑難排解
查詢執行的問題，通常是由受控執行個體無法存取檔案位置所造成。 相關錯誤訊息可能會指出沒有足夠的存取權限、非現有的位置或檔案路徑、檔案由其他程序所使用，或是無法列出目錄。 在大部分情況下，這表示網路流量控制原則封鎖了對檔案的存取，或是沒有存取權限。 這是應該檢查的內容：
- 錯誤或拼字錯誤的位置路徑。
- SAS 金鑰有效性：可能已過期、包含錯字，或以問號開頭。
- 允許的 SAS 金鑰權限：至少有讀取權，若使用萬用字元則有列出權。
- 封鎖了儲存體帳戶上的輸入流量。 查看管理 Azure 儲存體的虛擬網路規則以取得更多詳細資料，並確定允許從受控執行個體 VNet 存取。
- 使用儲存體端點原則在受控執行個體上封鎖了輸出流量。 允許通往儲存體帳戶的輸出流量。
- 受控識別存取權限：確定執行個體的受控識別已被授與儲存體帳戶的存取權限。
- 資料庫的相容性層級必須是 130 或更高，才能執行資料虛擬化查詢。
CREATE EXTERNAL TABLE AS SELECT (CETAS)
CREATE EXTERNAL TABLE AS SELECT (CETAS) 可讓您將資料從 SQL 受控執行個體匯出至外部儲存體帳戶。 您可以使用 CETAS，在 Parquet 或 CSV 檔案 Azure Blob 儲存體或 Azure Data Lake Storage (ADLS) Gen2 上建立外部表格。 CETAS 也可以平行匯出 T-SQL SELECT 陳述式的結果到建立的外部表格。 這些功能可能會有資料外流風險，因此根據預設，會針對 Azure SQL 受控執行個體停用 CETAS。 要啟用，請參閱 CREATE EXTERNAL TABLE AS SELECT (CETAS)。
限制
已知問題
- 在 SQL Server Management Studio (SSMS) 中啟用 Always Encrypted 的參數化時，資料虛擬化查詢失敗並顯示
Incorrect syntax near 'PUSHDOWN'錯誤訊息。
相關內容
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應