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


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

Применимо: SQL Server (только для Windows)Не поддерживается. База данных SQL AzureНе поддерживается. Azure Synapse Analytics Analytics Не поддерживается. Platform System (PDW)

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

Необходимые компоненты

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

SQL Server 2022

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

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

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

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

  1. Запустите sp_configure, задав для параметра hadoop connectivity значение поставщика хранилища BLOB-объектов Azure. Значение для поставщика см. в статье Конфигурация подключения к PolyBase (Transact-SQL). По умолчанию для подключения к Hadoop установлено значение 7.

    -- Values map to various external data sources.  
    -- Example: value 7 stands for Hortonworks HDP 2.1 to 2.6 on Linux,
    -- 2.1 to 2.3 on Windows Server, and Azure Blob Storage  
    sp_configure @configname = 'hadoop connectivity', @configvalue = 7;
    GO
    
    RECONFIGURE
    GO
    
  2. Перезапустите SQL Server с помощью services.msc. При перезапуске SQL Server следующие службы также будут перезапущены:

    • Служба перемещения данных SQL Server PolyBase
    • Компонент SQL Server PolyBase Engine

    остановка и запуск служб PolyBase в services.msc

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

    • Служба перемещения данных SQL Server PolyBase
    • Компонент SQL Server PolyBase Engine

    остановка и запуск служб PolyBase в services.msc

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

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

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

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';  
    
  2. Создание учетных данных в области базы данных для Хранилище BLOB-объектов Azure; IDENTITY может быть любым образом, так как оно не используется.

    -- IDENTITY: any string (this is not used for authentication to Azure storage).  
    -- SECRET: your Azure storage account key.  
    CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = 'user', Secret = '<azure_storage_account_key>';
    
  3. Создайте внешний источник данных с помощью инструкции CREATE EXTERNAL DATA SOURCE. Обратите внимание, что при подключении к служба хранилища Azure через wasb[s] соединитель проверка подлинности должна выполняться с помощью ключа учетной записи хранения, а не с подписанным URL-адресом (SAS).

    -- LOCATION:  Azure account storage account name and blob container name.  
    -- CREDENTIAL: The database scoped credential created above.  
    CREATE EXTERNAL DATA SOURCE AzureStorage with (  
          TYPE = HADOOP,
          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 (LOCATION='/Demo/',
          DATA_SOURCE = AzureStorage,  
          FILE_FORMAT = TextFileFormat  
    );  
    
  6. Создайте статистику внешней таблицы.

    CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  
    
  1. Создайте главный ключ в базе данных. Он необходим для шифрования секрета учетных данных.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';  
    
  2. Создайте учетные данные базы данных с областью действия для Хранилище BLOB-объектов Azure с помощью подписанного URL-адреса (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] проверка подлинности с помощью подписанного URL-адреса (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 (LOCATION='/Demo/',
          DATA_SOURCE = AzureStorage,  
          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  
sp_configure 'allow polybase export', 1;  
reconfigure  
  
-- 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 (  
      LOCATION='/old_data/2009/customerdata',  
      DATA_SOURCE = HadoopHDP2,  
      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 T1 JOIN CarSensor_Data T2  
ON (T1.CustomerKey = T2.CustomerKey)  
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;  

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

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

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

Объекты PolyBase в SSMS

Следующие шаги

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

В следующих статьях приведены дополнительные сведения о способах использования и мониторинга PolyBase.