SQL Server 2022 PolyBase 使用者入門

文章
03/07/2024

適用於：SQL Server 2016 (13.x) - Windows 和更新版本 SQL Server 2017 (14.x) - Linux 和更新版本

本文將逐步指導您完成在 SQL Server 2022 (16.x) 中透過 PolyBase 使用多個資料夾和檔案的教學課程。這組教學課程查詢示範了 PolyBase 的各種功能。

在 SQL Server 中透過 PolyBase 執行資料虛擬化可以允許利用中繼資料檔案函式來查詢多個資料夾、檔案或執行資料夾刪除。結構探索與資料夾和檔案消除的組合構成一項強大的功能，可讓 SQL 只從任何 Azure 儲存體帳戶或 S3 相容的物件儲存體解決方案擷取所需的資料。

必要條件

在本教學課程中，使用 PolyBase 之前，您必須：

在 Windows 上安裝 PolyBase 或在 Linux 上安裝 PolyBase。
如有必要，在 sp_configure 中啟用 PolyBase。
允許外部網路存取，以在 pandemicdatalake.blob.core.windows.net 和 azureopendatastorage.blob.core.windows.net 中存取公開可用的 Azure Blob 儲存體。

範例資料集

如果您不熟悉資料虛擬化且想要快速測試功能，請從 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 來查詢儲存在公開可用儲存體帳戶中的檔案：

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 和其他 T-SQL 子句，以繼續執行資料集探索。

如果 SQL Server 執行個體上的第一個查詢失敗，則對公用 Azure 儲存體帳戶進行網路存取時可能會受阻。請洽詢您的網路專家，以啟用存取權，才能繼續進行查詢。

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

外部資料來源

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

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

注意

如果您收到錯誤訊息 46530，External data sources are not supported with type GENERIC, 請檢查 SQL Server 執行個體中的組態選項 PolyBase Enabled。此屬性應該是 1。

執行下列命令，在您的 SQL Server 執行個體中啟用 PolyBase：

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

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

--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 的檔案。例如，這裡只會查詢符合名稱模式的檔案：

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

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

結構描述推斷

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

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

使用 sys.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 的路徑，否則會傳回完整的檔案路徑。
若以參數進行呼叫，則 filepath() 函式會傳回路徑的一部分，該部分會與參數中所指定位置上的萬用字元相符。例如，首個參數值會傳回符合第一個萬用字元的路徑部分。

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 查詢，以便輕鬆地重複使用基礎查詢。檢視也會啟用報告和分析工具 (例如 Power BI) 來取用 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;

外部表格

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

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

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

SELECT TOP 10 * 
FROM tbl_TaxiRides;

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

外部資料來源

如需將外部資料來源和外部表格建立到各種資料來源的更多教學課程，請參閱 PolyBase Transact-SQL 參考資料 (機器翻譯)。

如需各種外部資料來源的更多教學課程，請參閱：

SQL Server 2022 PolyBase 使用者入門

必要條件

範例資料集

外部資料來源

使用 OPENROWSET 查詢資料來源

查詢多個檔案和資料夾

結構描述推斷

檔案中繼資料函式

在 OPENROWSET 頂端建立檢視

外部表格

外部資料來源

意見反映

意見反映

更多資源

SQL Server 2022 PolyBase 使用者入門

必要條件

範例資料集

外部資料來源

使用 OPENROWSET 查詢資料來源

查詢多個檔案和資料夾

結構描述推斷

檔案中繼資料函式

在 OPENROWSET 頂端建立檢視

外部表格

外部資料來源

相關內容

意見反映

意見反映

更多資源