Настройка PolyBase для доступа к внешним данным в хранилище BLOB-объектов Azure

Применимо к: SQL Server 2016 (13.x) и более поздних версий в Windows

В этой статье описывается использование PolyBase в экземпляре SQL Server для запроса внешних данных в хранилище BLOB-объектов Azure.

Предварительные требования

Если вы еще не установили PolyBase, см. статью "Установка PolyBase" в Windows. Необходимые условия описываются в статье, посвященной установке.

SQL Server 2022

В SQL Server 2022 (16.x) настройте внешние источники данных для использования новых коннекторов для подключения к Хранилищу Azure. В следующей таблице приводится сводка изменений:

Внешний источник данных	От	По
Хранилище BLOB-объектов Azure	wasb[с]	abs
ADLS 2-го поколения	abfs[s]	adls

Настройка подключения к Хранилищу блобов Azure

Сначала настройте SQL Server PolyBase для использования облака Azure Blob.

1. Перезапустите SQL Server с помощью services.msc. При перезапуске SQL Server следующие службы также будут перезапущены:

   • Служба перемещения данных SQL Server PolyBase
   • Движок SQL Server PolyBase

   

Настройка внешней таблицы

Чтобы запросить данные из источника данных Hadoop, необходимо определить внешнюю таблицу для использования в запросах Transact-SQL. Далее указаны шаги по настройке внешней таблицы.

1. Создайте главный ключ базы данных (DMK) в базе данных. DmK требуется для шифрования секрета учетных данных.

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';
   

2. Создайте учетные данные с областью действия базы данных для Azure Blob Storage с помощью подписанного общего доступа (SAS); IDENTITY может быть чем угодно, так как оно не используется.

   CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
        -- Remove ? from the beginning of the SAS token
        SECRET = '<azure_shared_access_signature>';
   

3. Создайте внешний источник данных с помощью инструкции CREATE EXTERNAL DATA SOURCE. При подключении к службе хранилища Azure через соединитель WASB[s] используется проверка подлинности с помощью подписи общего доступа (SAS).

   -- LOCATION:  Azure account storage account name and blob container name.
   -- CREDENTIAL: The database scoped credential created above.
   CREATE EXTERNAL DATA SOURCE AzureStorage
   WITH (
       LOCATION = 'wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',
       CREDENTIAL = AzureStorageCredential
   );
   

4. Создайте формат внешнего файла с помощью инструкции CREATE EXTERNAL FILE FORMAT.

   -- FORMAT TYPE: Type of format in Hadoop (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
   CREATE EXTERNAL FILE FORMAT TextFileFormat
   WITH (
       FORMAT_TYPE = DELIMITEDTEXT,
       FORMAT_OPTIONS (FIELD_TERMINATOR = '|', USE_TYPE_DEFAULT = TRUE)
   );
   

5. Создайте внешнюю таблицу, указывающую на данные, хранящиеся в службе хранилища Azure, с помощью инструкции CREATE EXTERNAL TABLE. В этом примере внешние данные содержат данные датчика автомобиля; LOCATION не может быть /, но /Demo/ также в этом примере необязательно должно существовать ранее.

   -- LOCATION: path to file or directory that contains the data (relative to HDFS root).
   CREATE EXTERNAL TABLE [dbo].[CarSensor_Data]
   (
       SensorKey INT NOT NULL,
       CustomerKey INT NOT NULL,
       GeographyKey INT NULL,
       Speed FLOAT NOT NULL,
       YearMeasured INT NOT NULL
   )
   WITH (
       DATA_SOURCE = AzureStorage,
       LOCATION = '/Demo/',
       FILE_FORMAT = TextFileFormat
   );
   

6. Создайте статистику для внешней таблицы.

   CREATE STATISTICS StatsForSensors
   ON CarSensor_Data(CustomerKey, Speed);
   

Запросы PolyBase

Есть три функции, которые выполняет PolyBase:

• нерегламентированные запросы к внешним таблицам.
• импорт данных;
• экспорт данных.

Следующие запросы предоставляют пример с вымышленными данными датчика автомобиля.

Нерегламентированные запросы

Следующий нерегламентированный запрос объединяет реляционные данные с данными Hadoop. Он выбирает клиентов, которые ездят быстрее 35 миль/ч, и объединяет структурированные данные клиента, хранящиеся в SQL Server, с данными автомобильного датчика, хранящимися в Hadoop.

SELECT DISTINCT Insured_Customers.FirstName,
                Insured_Customers.LastName,
                Insured_Customers.YearlyIncome,
                CarSensor_Data.Speed
FROM Insured_Customers,
    CarSensor_Data
WHERE Insured_Customers.CustomerKey = CarSensor_Data.CustomerKey
    AND CarSensor_Data.Speed > 35
ORDER BY CarSensor_Data.Speed DESC
OPTION (FORCE EXTERNALPUSHDOWN); -- or OPTION (DISABLE EXTERNALPUSHDOWN)

Импорт данных с помощью PolyBase

Следующий запрос позволяет импортировать внешние данные в SQL Server. В этом примере импортируются данные быстрых водителей в SQL Server для выполнения углубленного анализа. Для повышения производительности используется технология columnstore.

SELECT DISTINCT Insured_Customers.FirstName,
                Insured_Customers.LastName,
                Insured_Customers.YearlyIncome,
                Insured_Customers.MaritalStatus
INTO Fast_Customers
FROM Insured_Customers
    INNER JOIN (SELECT *
                FROM CarSensor_Data
                WHERE Speed > 35
    ) AS SensorD
        ON Insured_Customers.CustomerKey = SensorD.CustomerKey
ORDER BY YearlyIncome;

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FastCustomers
ON Fast_Customers;

Экспорт данных с помощью PolyBase

Следующий запрос позволяет экспортировать данные из SQL Server в хранилище BLOB-объектов Azure. Сначала включите функцию экспорта PolyBase. Затем создайте внешнюю целевую таблицу, прежде чем экспортировать в нее данные.

-- Enable INSERT into external table
EXECUTE sp_configure 'allow polybase export', 1;
RECONFIGURE;
GO

-- Create an external table.
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009]
(
    FirstName CHAR (25) NOT NULL,
    LastName CHAR (25) NOT NULL,
    YearlyIncome FLOAT NULL,
    MaritalStatus CHAR (1) NOT NULL
)
WITH (
    DATA_SOURCE = HadoopHDP2,
    LOCATION = '/old_data/2009/customerdata',
    FILE_FORMAT = TextFileFormat,
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 0
);

-- Export data: Move old data to Hadoop while keeping it query-able via an external table.
INSERT INTO dbo.FastCustomer2009
SELECT T.*
FROM Insured_Customers AS T1
     INNER JOIN CarSensor_Data AS T2
         ON (T1.CustomerKey = T2.CustomerKey)
WHERE T2.YearMeasured = 2009
      AND T2.Speed > 40;

Экспорт PolyBase с помощью этого метода может создавать несколько файлов.

Просмотр объектов PolyBase в SSMS

В SSMS внешние таблицы отображаются в отдельной папке Внешние таблицы. Внешние источники данных и форматы внешних файлов находятся в папках, вложенных в папку Внешние ресурсы.

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

• Справочник по Transact-SQL PolyBase
• Группы масштабирования PolyBase
• Мониторинг и устранение неполадок PolyBase
• СОЗДАТЬ ВНЕШНИЙ ИСТОЧНИК ДАННЫХ (Transact-SQL)