使用 PolyBase 虚拟化 CSV 文件

  • 项目

适用于：SQL Server 2022 (16.x) 及更高版本

SQL Server 2022 (16.x) 可以直接从 CSV 文件查询数据。 此概念通常称为数据虚拟化，它允许将数据保留在其原始位置，但可以使用 T-SQL 命令从 SQL Server 实例进行查询，就像任何其他表一样。 此功能使用 PolyBase 连接器，并最大程度地减少通过 ETL 进程复制数据的需求。

在下面的示例中，CSV 文件夹存储在 Azure Blob 存储上，并通过 OPENROWSET 或外部表进行访问。

有关数据虚拟化的详细信息，请参阅 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 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 中创建更精细的模型，以便进行数据访问，以增强安全模型

有关详细信息，请参阅创建外部表。

以下示例将使用相同的数据源。

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

相关内容