使用 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

相關內容