Поделиться через

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

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

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

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

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

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

Включите 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/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, что предотвратит добавление подсказки запроса.

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

  • Last updated on