Виртуализация разностной таблицы с помощью PolyBase

  • Статья

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

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

В следующем примере папка разностной таблицы хранится в Хранилище BLOB-объектов Azure и обращается через OPENROWSET или внешнюю таблицу.

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

Ответов

1. Включение PolyBase в sp_configure

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. Создание пользовательской базы данных

Это упражнение создает образец базы данных с параметрами и расположением по умолчанию. Эта пустая база данных используется для работы с данными и хранения учетных данных с областью действия. В этом примере используется новая пустая база данных Delta_demo .

CREATE DATABASE [Delta_demo];

3. Создание главного ключа и учетных данных базы данных

Главный ключ базы данных в пользовательской базе данных необходим для шифрования секрета delta_storage_dscучетных данных в области базы данных. В этом примере разностная таблица находится в Azure Data Lake Storage 2-го поколения.

USE [Delta_demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

4. Создание внешнего источника данных

Учетные данные базы данных используются для внешнего источника данных. В этом примере таблица Delta находится в Azure Data Lake Storage 2-го поколения, поэтому используйте префикс adls и метод удостоверения SHARED ACCESS SIGNATURE. Дополнительные сведения о соединителях и префиксах, включая новые параметры SQL Server 2022 (16.x), см. в статье CREATE EXTERNAL DATA SOURCE.

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Например, если ваша учетная запись хранения называется delta_lake_sample, а контейнер — sink, код будет выглядеть следующим образом:

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://sink@delta_lake_sample.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Использование OPENROWSET для доступа к данным

В этом примере называется Contosoпапка "Таблица данных".

Так как внешний источник Delta_ED данных сопоставляется с уровнем контейнера. Папка Contoso разностной таблицы находится в корневом каталоге. Чтобы запросить файл в структуре папок, укажите сопоставление папок относительно параметра LOCATION внешнего источника данных.

SELECT * FROM OPENROWSET
(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS [result];

Запрос данных с внешней таблицей

CREATE EXTERNAL TABLE также можно использовать для виртуализации данных разностной таблицы в SQL Server. Столбцы должны быть определены и строго типизированы. Хотя внешние таблицы выполняют больше усилий по созданию, они также предоставляют дополнительные преимущества при запросе внешнего источника данных с помощью OPENROWSET. Вы можете:

  • Укрепление определения типов данных для заданного столбца
  • Определение допустимости null
  • Определение сортировки
  • Создание статистики для столбца для оптимизации качества плана запроса
  • Создание более детализированной модели в SQL Server для доступа к данным для улучшения модели безопасности

Дополнительные сведения см. в статье CREATE EXTERNAL TABLE.

В следующем примере используется тот же источник данных.

1. Создание формата внешнего файла

Чтобы определить форматирование файла, требуется внешний формат файла. Внешние форматы файлов также рекомендуется использовать из-за повторного использования. Дополнительные сведения см. в разделе CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

2. Создание внешней таблицы

Файлы разностной таблицы находятся /delta/Delta_yob/ и внешний источник данных для этого примера — хранилище объектов, совместимое с S3, ранее настроенное в источнике s3_edsданных. PolyBase может использовать в качестве расположения папку разностной таблицы или абсолютный файл, в котором будет находиться.delta/Delta_yob/_delta_log/00000000000000000000.json

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta (
    id INT,
    name VARCHAR(200),
    dob DATE
)
WITH (
        LOCATION = '/delta/Delta_yob/',
        FILE_FORMAT = DeltaTableFormat,
        DATA_SOURCE = s3_eds
);
GO

Ограничения

При создании внешней таблицы, указывающей на секционированную разностную таблицу, столбец, используемый для секционирования, возвращается NULL при запросе внешней таблицы. Однако если вы используете запрос, значение столбца OPENROWSET возвращается правильно. Чтобы обойти это ограничение, создайте представление запроса OPENROWSET , а затем запросите представление, чтобы получить секционированные значения столбцов для правильного возврата.

При запросе внешней таблицы Delta могут возникнуть следующие ошибки:

Msg 2571, Level 14, State 3, Line 1
User '<user>' does not have permission to run DBCC TRACEON.
Msg 16513, Level 16, State 0, Line 1
Error reading external metadata.

Это может произойти, так как существует QUERYTRACEON указание запроса, которое можно добавить в запрос метаданных разностного файла и требует sysadmin выполнения роли сервера. Если это происходит, можно устранить проблему, включив флаг трассировки 14073, и это позволит предотвратить добавление подсказки запроса.

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