Настройка PolyBase для доступа к внешним данным в Hadoop

Область применения: SQL Server — Только Управляемый экземпляр SQL Azure Windows

В этой статье описывается использование PolyBase в экземпляре SQL Server для запроса внешних данных в Hadoop.

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

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

• PolyBase поддерживает два поставщика Hadoop — Hortonworks Data Platform (HDP) и Cloudera Distributed Hadoop (CDH). В новых выпусках Hadoop соблюдается шаблон "Основной номер версии.дополнительный номер версии.версия". Также поддерживаются все версии в рамках поддерживаемых основного и дополнительного выпусков. Сведения о поддерживаемых версиях Hortonworks Data Platform (HDP) и Cloudera Distributed Hadoop (CDH) см. в статье Настройка подключения PolyBase.

Настройка подключения к Hadoop

Сначала необходимо настроить SQL Server PolyBase для использования определенного поставщика Hadoop.

1. Запустите sp_configure с hadoop connectivity и задайте соответствующее значение для поставщика. Значение для поставщика см. в статье Конфигурация подключения к PolyBase (Transact-SQL).

   -- 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

   

Включение вычислений pushdown

Чтобы улучшить производительность при выполнении запроса, активируйте вычисление pushdown для кластера Hadoop.

1. Найдите файл yarn-site.xml в каталоге установки SQL Server. Как правило, путь выглядит следующим образом:

   C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\PolyBase\Hadoop\conf\  
   

2. Найдите аналогичный файл на компьютере с Hadoop в каталоге конфигурации. Открыв его, найдите и скопируйте значение ключа конфигурации yarn.application.classpath.

3. На компьютере SQL Server в файле yarn-site.xml найдите свойство yarn.application.classpath. Вставьте значение, скопированное на компьютере с Hadoop, в качестве значения элемента.

4. Для всех версий CDH 5.X необходимо добавить параметры конфигурации mapreduce.application.classpath либо в конец файла yarn-site.xml, либо в файл mapred-site.xml. HortonWorks содержит эти настройки в конфигурациях yarn.application.classpath. Примеры см. в статье Конфигурация PolyBase.

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

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

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

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  
   

   Аргументы

   PASSWORD ="пароль"

   Пароль, который использовался при шифровке главного ключа базы данных. Аргумент password должен соответствовать требованиям политики паролей Windows на компьютере, где размещается экземпляр SQL Server.

2. Создайте учетные данные на уровне базы данных для кластеров Hadoop, защищенных с помощью Kerberos.

   -- IDENTITY: the Kerberos user name.  
   -- SECRET: the Kerberos password  
   CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
   WITH IDENTITY = '<hadoop_user_name>', Secret = '<hadoop_password>';  
   

3. Создайте внешний источник данных с помощью инструкции CREATE EXTERNAL DATA SOURCE.

   -- LOCATION (Required) : Hadoop Name Node IP address and port.  
   -- RESOURCE MANAGER LOCATION (Optional): Hadoop Resource Manager location to enable pushdown computation.  
   -- CREDENTIAL (Optional):  the database scoped credential, created above.  
   CREATE EXTERNAL DATA SOURCE MyHadoopCluster WITH (  
         TYPE = HADOOP,
         LOCATION ='hdfs://10.xxx.xx.xxx:xxxx',
         RESOURCE_MANAGER_LOCATION = '10.xxx.xx.xxx:xxxx',
         CREDENTIAL = HadoopUser1
   );  
   

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. Создайте внешнюю таблицу, указывающую на данные, хранящиеся в Hadoop, с помощью инструкции CREATE EXTERNAL TABLE. В этом примере внешние данные представляют собой данные датчика автомобиля.

   -- 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 = MyHadoopCluster,  
         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)  

Импорт данных

Следующий запрос позволяет импортировать внешние данные в 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;  

Экспорт данных

Следующий запрос позволяет экспортировать данные из SQL Server в Hadoop. Чтобы сделать это, необходимо сначала включить экспорт 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 в SSMS

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

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

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

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

• Масштабируемые группы PolyBase
• Устранение неполадок c PolyBase