Виртуализация 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

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