使用 PolyBase 將 CSV 檔案虛擬化
適用於：
SQL Server 2022 (16.x) 和更新版本
SQL Server 2022 (16.x) 可以直接從 CSV 檔案查詢資料。 這個概念 (通常稱為資料虛擬化) 允許資料保留在其原始位置，但可以像任何其他資料表一樣使用 T-SQL 命令從 SQL Server 執行個體中查詢。 此功能使用 PolyBase 連接器，並可將透過 ETL 程序複製資料的需求降到最低。
在下方範例中，CSV 檔案儲存在 Azure Blob 儲存體上，並透過 OPENROWQUERY 或外部表格存取。
如需資料虛擬化的相關詳細資訊，請參閱簡介使用 PolyBase 進行資料虛擬化。
預先設定
1. 在 sp_configure 啟用 PolyBase
exec sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;
2. 建立使用者資料庫
此動作會建立一個具有預設設定和位置的範例資料庫。 可使用此空的範例資料庫來處理資料，並儲存限定範圍的認證。 在此範例中，使用名為 CSV_Demo 的新空白資料庫。
CREATE DATABASE [CSV_Demo];
3. 建立主要金鑰和資料庫限定範圍的認證
需要使用者資料庫中的資料庫主要金鑰，才能加密資料庫有限範圍認證的祕密 blob_storage。
USE [CSV_Demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE DATABASE SCOPED CREDENTIAL blob_storage
WITH IDENTITY = '<user_name>', Secret = '<password>';
4. 建立外部資料來源
資料庫限定範圍的認證用於外部資料來源。 在此範例中，CSV 檔案位於 Azure Blob 儲存體中，因此請使用前置詞 abs 和 SHARED ACCESS SIGNATURE 身分識別方法。 如需連接器和前置詞的詳細資訊 (包括 SQL Server 2022 (16.x) 的新設定)，請參閱 CREATE EXTERNAL DATA SOURCE (機器翻譯)。
CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net'
,CREDENTIAL = blob_storage
);
舉例來說，如果您的儲存體帳戶命名為 s3sampledata，且容器命名為 import，則程式碼會是：
CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
LOCATION = 'abs://import@s3sampledata.blob.core.windows.net'
,CREDENTIAL = blob_storage
)
使用 OPENROWSET 存取資料
在此範例中，檔案命名為 call_center.csv，資料從第二個資料列開始。
因為外部資料來源 Blob_CSV 會對應至容器層級。 call_center.csv 位於容器根目錄的 2022 子資料夾中。 若要查詢資料夾結構中的檔案，請提供與外部資料來源 LOCATION 參數相關的資料夾對應。
SELECT * FROM OPENROWSET
(
BULK '/2022/call_center.csv',
FORMAT = 'CSV',
DATA_SOURCE = 'Blob_CSV',
FIRSTROW = 2
)
WITH (
cc_call_center_sk INT,
cc_call_center_id CHAR(16),
cc_rec_start_date DATE,
cc_rec_end_date DATE,
cc_closed_date_sk INT,
cc_open_date_sk INT,
cc_name VARCHAR(50),
cc_class VARCHAR(50),
cc_employees INT,
cc_sq_ft INT,
cc_hours CHAR(20),
cc_manager VARCHAR(40),
cc_mkt_id INT,
cc_mkt_class CHAR(50),
cc_mkt_desc VARCHAR(100),
cc_market_manager VARCHAR(40),
cc_division INT,
cc_division_name VARCHAR(50),
cc_company INT,
cc_company_name CHAR(50),
cc_street_number CHAR(10),
cc_street_name VARCHAR(60),
cc_street_type CHAR(15),
cc_suite_number CHAR(10),
cc_city VARCHAR(60),
cc_county VARCHAR(30),
cc_state CHAR(2),
cc_zip CHAR(10),
cc_country VARCHAR(20),
cc_gmt_offset DECIMAL(5, 2),
cc_tax_percentage DECIMAL(5, 2)
) AS [cc];
使用外部表格查詢資料
CREATE EXTERNAL TABLE 也可以用來將 SQL Server 中的 CSV 資料虛擬化。 必須定義資料行且設定為強類型。 雖然外部表格需要更多心力來建立，但它們也可透過使用 OPENROWSET 查詢外部資料來源來提供額外的好處。 您可以：
- 強化指定資料行之資料類型的定義
- 定義可 NULL 性
- 定義定序
- 建立資料行的統計資料，以最佳化查詢計畫的品質
- 在 SQL Server 內建立更精細的存取資料模型，以增強安全性模型
如需詳細資訊，請參閱 CREATE EXTERNAL TABLE。
針對下列範例，會使用相同的資料來源。
1. 建立外部檔案格式
若要定義檔案的格式設定，則需要外部檔案格式。 由於再使用性，也建議使用外部檔案格式。
在下列範例中，資料會從第二個資料列開始。
CREATE EXTERNAL FILE FORMAT csv_ff
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS(
FIELD_TERMINATOR = ',',
STRING_DELIMITER = '"',
FIRST_ROW = 2)
);
2. 建立外部表格
LOCATION 是 call_center.csv 檔案的資料夾和檔案路徑，與 DATA_SOURCE 定義的外部資料來源中的位置路徑相關。 在此情況下，檔案位於 2022 子資料夾中。 使用 FILE_FORMAT，指定 SQL Server 中 csv_ff 外部檔案格式的路徑。
CREATE EXTERNAL TABLE extCall_Center_csv (
cc_call_center_sk INT NOT NULL,
cc_call_center_id CHAR(16) NOT NULL,
cc_rec_start_date DATE,
cc_rec_end_date DATE,
cc_closed_date_sk INT,
cc_open_date_sk INT,
cc_name VARCHAR(50),
cc_class VARCHAR(50),
cc_employees INT,
cc_sq_ft INT,
cc_hours CHAR(20),
cc_manager VARCHAR(40),
cc_mkt_id INT,
cc_mkt_class CHAR(50),
cc_mkt_desc VARCHAR(100),
cc_market_manager VARCHAR(MAX),
cc_division VARCHAR(50),
cc_division_name VARCHAR(50),
cc_company VARCHAR(60),
cc_company_name CHAR(50),
cc_street_number CHAR(10),
cc_street_name VARCHAR(60),
cc_street_type CHAR(15),
cc_suite_number CHAR(10),
cc_city VARCHAR(60),
cc_county VARCHAR(30),
cc_state CHAR(20),
cc_zip CHAR(20),
cc_country VARCHAR(MAX),
cc_gmt_offset DECIMAL(5, 2),
cc_tax_percentage DECIMAL(5, 2)
)
WITH (
LOCATION = '/2022/call_center.csv',
DATA_SOURCE = Blob_CSV,
FILE_FORMAT = csv_ff
);
GO