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