Виртуализация CSV-файла с помощью PolyBase
Область применения: 
SQL Server 2022 (16.x) и более поздних версий
SQL Server 2022 (16.x) может запрашивать данные непосредственно из CSV-файлов. Эта концепция, обычно называемая виртуализацией данных, позволяет данным оставаться в исходном расположении, но может запрашиваться из экземпляра SQL Server с командами T-SQL, такими как любая другая таблица. Эта функция использует соединители PolyBase и сводит к минимуму потребность в копировании данных с помощью процессов ETL.
В следующем примере CSV-файл хранится в Хранилище BLOB-объектов Azure и обращается через OPENROWSET или внешнюю таблицу.
Дополнительные сведения о виртуализации данных см. в этой записи: введение в виртуализацию данных с помощью PolyBase.
Ответов
1. Включение PolyBase в sp_configure
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-файл находится в Хранилище BLOB-объектов Azure, поэтому используйте префикс 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 также можно использовать для виртуализации данных CSV в SQL Server. Столбцы должны быть определены и строго типизированы. Хотя внешние таблицы выполняют больше усилий по созданию, они также предоставляют дополнительные преимущества при запросе внешнего источника данных с помощью 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, чтобы указать путь к формату csv_ff внешнего файла в SQL Server.
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