Configurer PolyBase pour accéder à des données externes dans Stockage Blob Azure

S’applique à : SQL Server (Windows uniquement) Azure SQL Database Azure Synapse Analytics Analytics Platform System (PDW)

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

Prérequis

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

SQL Server 2022

Dans SQL Server 2022 (16.x), configurez vos sources de données externes pour utiliser les nouveaux connecteurs lors de la connexion à Stockage Azure. Le tableau ci-dessous résume la modification :

Source de données externe	Du	À
Stockage Blob Azure	wasb[s]	abs
ADLS Gen 2	abfs[s]	adls

Configurer la connectivité avec Stockage Blob Azure

Configurez d’abord SQL Server PolyBase pour l’utilisation de Stockage Blob Azure.

1. Redémarrez 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

   

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 sur la base de données. La clé principale est nécessaire pour chiffrer le secret des informations d’identification.

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

2. Créez des informations d’identification incluses dans l’étendue de la base de données pour le Stockage Blob Azure en utilisant une signature d'accès partagé (SAS) ; vous pouvez choisir n’importe quelle IDENTITY puisque celle-ci ne sera pas utilisée.

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

3. Créez une source de données externe avec CREATE EXTERNAL DATA SOURCE. Notez que quand vous vous connectez au Stockage Azure via le connecteur WASB[s], l’authentification doit être faite avec une signature d’accès partagé (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. 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 Stockage Azure avec CREATE EXTERNAL TABLE. Dans cet exemple, les données externes contiennent des données de capteur de voiture ; LOCATION ne peut pas être / mais /Demo/, comme dans cet exemple, n’a pas besoin d’exister au préalable.

   -- 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. 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. Elle effectue également une jointure entre 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)  

Importer des données avec PolyBase

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, elle tire parti de la technologie 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;  

Exporter des données avec PolyBase

La requête suivante exporte des données depuis SQL Server vers Stockage Blob Azure. Elle commence par 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;  

Cette méthode d’exportation PolyBase est susceptible de créer plusieurs fichiers.

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.
• CREATE EXTERNAL DATA SOURCE