Configurer PolyBase pour accéder à des données externes dans Hadoop

S’applique à : SQL Server - Windows uniquement Azure SQL Managed Instance

L’article explique comment utiliser PolyBase sur une instance SQL Server pour interroger des données externes dans Hadoop.

Prérequis

• Si vous n’avez pas installé PolyBase, consultez Installation de PolyBase. Cet article décrit les prérequis pour l’installation.

• PolyBase prend en charge deux fournisseurs Hadoop, HDP (Hortonworks Data Platform) et CDH (Cloudera Distributed Hadoop). Hadoop suit le modèle « majeure.mineure.version » pour ses nouvelles versions, et toutes les versions d’une version majeure ou mineure prise en charge sont prises en charge. Pour plus d’informations sur les versions de HDP (Hortonworks Data Platform) et de CDH (Cloudera Distributed Hadoop) prises en charge, consultez Configuration de la connectivité PolyBase.

Configurer la connectivité Hadoop

Configurez d’abord SQL Server PolyBase pour utiliser votre fournisseur Hadoop spécifique.

1. Exécutez sp_configure avec 'hadoop connectivity' et définissez une valeur appropriée pour votre fournisseur. Pour trouver la valeur pour votre fournisseur, consultez Configuration de la connectivité PolyBase.

   -- 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. Vous devez redémarrer SQL Server avec services.msc. Le redémarrage de SQL Server redémarre ces services :

   • Service de déplacement des données SQL Server PolyBase
   • Moteur SQL Server PolyBase

   

Activer le calcul pushdown

Pour améliorer les performances des requêtes, activez le calcul pushdown sur votre cluster Hadoop :

1. Recherchez le fichier yarn-site.XML dans le chemin d’installation de SQL Server. En règle générale, le chemin d’accès est le suivant :

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

2. Sur l’ordinateur Hadoop, recherchez le fichier analogue dans le répertoire de configuration Hadoop. Dans le fichier, recherchez et copiez la valeur de la clé de configuration yarn.application.classpath.

3. Sur l’ordinateur SQL Server, dans le fichier yarn-site.xml , recherchez la propriété yarn.application.classpath. Collez la valeur de l’ordinateur Hadoop dans l’élément de valeur.

4. Pour toutes les versions 5.X de CDH, vous devez ajouter les paramètres de configuration mapreduce.application.classpath, soit à la fin de votre fichier yarn-site.xml, soit dans le fichier mapred-site.xml. HortonWorks inclut ces configurations dans les configurations yarn.application.classpath. Consultez Configuration PolyBase pour voir des exemples.

Configurer une table externe

Pour interroger les données dans votre source de données Hadoop, vous devez définir une table externe à utiliser dans les requêtes Transact-SQL. Les étapes suivantes décrivent comment configurer la table externe.

1. Créez une clé principale pour la base de données, si celle-ci n’en a pas. C’est nécessaire pour chiffrer le secret des informations d’identification.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';  
   

   Arguments

   PASSWORD ='password'

   Mot de passe utilisé pour chiffrer la clé principale dans la base de données. Le mot de passe doit remplir les critères de la stratégie de mot de passe Windows de l’ordinateur qui héberge l’instance SQL Server.

2. Créez des informations d’identification limitées à la base de données pour les clusters Hadoop sécurisés par Kerberos.

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

3. Créez une source de données externe avec 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. Créez un format de fichier externe avec 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. Créez une table externe pointant vers les données stockées dans Hadoop avec CREATE EXTERNAL TABLE. Dans cet exemple, les données externes contiennent des données provenant de capteurs sur des voitures.

   -- 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. Créez des statistiques sur une table externe.

   CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  
   

Requêtes PolyBase

PolyBase est approprié pour trois fonctions :

• Requêtes ad hoc sur des tables externes.
• Importation de données.
• Exportation de données.

Les requêtes suivantes fournissent un exemple avec des données fictives provenant de capteurs sur des voitures.

requêtes ad hoc ;

La requête ad hoc suivante fait une jointure entre des données relationnelles et des données Hadoop. Elle sélectionne les clients qui dépassent la vitesse de 35 mph (56 km/h), en joignant les données client structurées stockées dans SQL Server et les données des capteurs des véhicules stockées dans 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)  

Importation de données

La requête suivante importe des données externes dans SQL Server. Cet exemple importe les données pour les conducteurs roulant rapidement dans SQL Server pour effectuer une analyse plus approfondie. Pour améliorer les performances, l’exemple utilise un index 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;  

Exportation de données

La requête suivante exporte des données depuis SQL Server vers Hadoop. Pour cela, vous devez d’abord activer l’exportation PolyBase. Ensuite, elle crée une table externe pour la destination avant d’y exporter les données.

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

Afficher les objets PolyBase dans SSMS

Dans SSMS, les tables externes sont affichées dans un dossier distinct, Tables externes. Les sources de données externes et les formats de fichiers externes figurent dans des sous-dossiers du dossier Ressources externes.

Étapes suivantes

Pour plus de tutoriels sur la création de sources de données externes et de tables externes vers diverses sources de données, consultez référence PolyBase Transact-SQL.

Explorez d’autres façons d’utiliser et de superviser PolyBase dans les articles suivants :

• Groupes de scale-out PolyBase.
• Résolution des problèmes de PolyBase.