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

Применимо:SQL Server (только для Windows) База данных SQL Azure Azure Synapse Analytics Аналитическая платформа (PDW)

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

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

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

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 Engine

   

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

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

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

   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 (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 для выполнения углубленного анализа. Для повышения производительности используется технология колоночного хранения данных.

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 Transact-SQL.

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

• Масштабируемые группы PolyBase
• Устранение неполадок c PolyBase
• СОЗДАТЬ ВНЕШНИЙ ИСТОЧНИК ДАННЫХ