Виртуализация CSV-файла с помощью PolyBase

Область применения: SQL Server 2022 (16.x) и более поздних версий

SQL Server 2022 (16.x) может запрашивать данные непосредственно из CSV-файлов. Эта концепция, обычно называемая виртуализацией данных, позволяет данным оставаться в исходном расположении, но может запрашиваться из экземпляра SQL Server с командами T-SQL, такими как любая другая таблица. Эта функция использует соединители PolyBase и сводит к минимуму потребность в копировании данных с помощью процессов ETL.

В следующем примере CSV-файл хранится в Azure Blob Storage и доступ к нему осуществляется с помощью OPENROWSET или внешней таблицы.

Дополнительные сведения о виртуализации данных см. в этой записи: введение в виртуализацию данных с помощью PolyBase.

Предварительная конфигурация

Включите 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-файл находится в объектном хранилище 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

Связанный контент

• Справочник по Transact-SQL PolyBase
• СОЗДАТЬ ВНЕШНИЙ ИСТОЧНИК ДАННЫХ (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• СОЗДАТЬ ВНЕШНЮЮ ТАБЛИЦУ (Transact-SQL)