具有Azure SQL 受控執行個體的資料虛擬化

適用于:Azure SQL 受控執行個體

Azure SQL 受控執行個體的資料虛擬化功能可讓您執行 Transact-SQL (T-SQL) 查詢,以Azure Data Lake Storage Gen2或Azure Blob 儲存體儲存資料的檔案,並使用聯結將其與本機儲存的關聯式資料結合。 如此一來,您就可用透明方式存取外部資料,同時保留其原始格式和位置,這也稱為資料虛擬化。

概觀

資料虛擬化提供兩種方式來查詢適用于不同案例集的檔案:

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

檔案格式

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

儲存體類型

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

--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 查詢,以取得資料集的第一個見解:

--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 受控執行個體支援下列驗證類型:

受控識別也稱為 MSI 是 Azure Active Directory (Azure AD) 的功能,可提供 Azure 服務實例,例如Azure SQL 受控執行個體,並在 Azure AD 中使用自動受控識別。 此身分識別可用來授權非公用儲存體帳戶中資料存取的要求。

存取資料之前,Azure 儲存體系統管理員必須先將許可權授與受控識別,才能存取資料。 將許可權授與受控實例的受控識別,與將許可權授與其他任何 Azure AD 使用者的方式相同。

建立受控識別驗證的資料庫範圍認證非常簡單:

-- 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 DemoPrivateExternalDataSource
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 = 'DemoPublicExternalDataSource',
 FORMAT = 'parquet'
) AS filerows

查詢多個檔案和資料夾

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

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

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',--You need to create the data source first
 FORMAT = 'parquet'
) AS filerows

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

結構描述推斷

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

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

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

EXEC sp_describe_first_result_set N'
 SELECT
 vendor_id, pickup_datetime, passenger_count
 FROM 
 OPENROWSET(
  BULK ''taxi/*/*/*'',
  DATA_SOURCE = ''NYCTaxiDemoDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

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

SELECT TOP 100
 vendor_id, pickup_datetime, passenger_count
FROM
OPENROWSET(
 BULK 'taxi/*/*/*',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendor_id varchar(4), -- we're using length of 4 instead of the inferred 8000
pickup_datetime datetime2,
passenger_count int
) AS nyc;

因為無法自動判斷 CSV 檔案的結構描述,所以使用 WITH 子句明確指定資料行:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'population/population.csv',
 DATA_SOURCE = 'PopulationDemoDataSourceCSV',
 FORMAT = 'CSV')
WITH (
 [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
 [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
 [year] smallint,
 [population] bigint
) AS filerows

檔案中繼資料函式

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

--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 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet') AS filerows
--List all paths:
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder]
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 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 'taxi/year=*/month=*/*.parquet',
DATA_SOURCE = 'NYCTaxiDemoDataSource',
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 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 FORMAT = 'parquet'
) AS filerows

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

CREATE VIEW TaxiRides AS
SELECT *
 ,filerows.filepath(1) AS [year]
 ,filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiDemoDataSource',
 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(
 vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
 pickup_datetime DATETIME2,
 dropoff_datetime DATETIME2,
 passenger_count INT,
 trip_distance FLOAT,
 fare_amount FLOAT,
 extra FLOAT,
 mta_tax FLOAT,
 tip_amount FLOAT,
 tolls_amount FLOAT,
 improvement_surcharge FLOAT,
 total_amount FLOAT
)
WITH (
 LOCATION = 'taxi/year=*/month=*/*.parquet',
 DATA_SOURCE = DemoDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

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

SELECT TOP 10 *
FROM tbl_TaxiRides

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

效能考量

檔案數目或可查詢的資料量沒有硬性限制,但查詢效能取決於資料量、資料格式、資料組織方式,以及查詢和聯結的複雜度。

查詢分割的資料

資料通常會組織在子資料夾中,也稱為分割區。 您可以指示受控實例只查詢特定資料夾和檔案。 這樣做可減少檔案數目,以及查詢需要讀取和處理的資料量,進而提升效能。 這種類型的查詢優化稱為資料分割剪除或資料分割刪除。 您可以在查詢的 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 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',        
        FIRSTROW = 2
    )
WITH (
    vendor_id 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;

如果您的預存資料未分割,請考慮將資料分割以改善查詢效能。

統計資料

收集外部資料的統計資料,是最佳化查詢的最重要工作之一。 執行個體愈了解您的資料,執行查詢的速度就愈快。 SQL 引擎查詢最佳化工具是以成本為基礎的最佳化工具。 它會比較各種查詢方案的成本,然後選擇成本最低的方案。 在大部分的情況下,它會選擇執行最快的方案。

自動建立統計資料

Azure SQL 受控執行個體分析傳入的使用者查詢是否有遺漏的統計資料。 如果缺少統計資料,查詢最佳化工具會自動在查詢述詞或聯結條件中的個別資料行上建立統計資料,以改善查詢計畫的基數估計值。 自動建立統計資料是同步進行的,因此,如果您的資料行缺少統計資料,查詢效能可能會略為降低。 為單一資料行建立統計資料的時間,取決於目標檔案的大小。

OPENROWSET 手動統計資料

您可以使用 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 選項,將取樣大小指定為百分比。 若要為多個資料行建立單一資料行統計資料,請針對每個資料行執行預存程序。 您無法為 OPENROWSET 路徑建立多資料行統計資料。

若要更新現有的統計資料,請先使用 sp_drop_openrowset_statistics 預存程序卸除統計資料,然後使用 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 (vendor_id)
WITH FULLSCAN, NORECOMPUTE

WITH 選項是必要項目,而且針對取樣大小的允許選項為 FULLSCANSAMPLE n 百分比。 若要為多個資料行建立單一資料行統計資料,請針對每個資料行執行預存程序。 不支援多重資料行統計資料。

疑難排解

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

  • 錯誤或拼字錯誤的位置路徑。
  • SAS 金鑰有效性:可能已過期 (也就是已超過有效期間)、包含錯字,或以問號開頭。
  • 允許的 SAS 金鑰許可權:至少讀取,以及使用萬用字元時列出
  • 封鎖了儲存體帳戶上的輸入流量。 查看管理 Azure 儲存體的虛擬網路規則以取得更多詳細資料,並確定允許從受控執行個體 VNet 存取。
  • 使用儲存體端點原則在受控執行個體上封鎖了輸出流量。 允許通往儲存體帳戶的輸出流量。
  • 受控識別存取權限:請確定代表實例受控識別的 Azure AD 服務主體具有在儲存體帳戶上授與的存取權限。

後續步驟