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

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

Prerequisites

• Azure Blob Storage dans votre abonnement.
• Un conteneur créé dans Azure Blob Storage.

Configurer la connectivité de Stockage Blob Azure

Tout d’abord, configurez APS pour utiliser Stockage Blob Azure.

1. Exécutez sp_configure avec la « connectivité hadoop » définie sur un fournisseur stockage Blob Azure. Pour trouver la valeur des fournisseurs, 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. Redémarrez la région APS à l’aide de la page État du service sur Appliance Configuration Manager.

Configurer une table externe

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

1. Créez une clé principale sur la base de données. Il est nécessaire de chiffrer le secret d’informations d’identification.

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';  
   

2. Créez des informations d’identification spécifiques à la base de données pour le stockage Blob Azure.

   -- 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. Créez une source de données externe avec CREATE EXTERNAL DATA SOURCE..

   -- 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. Créez un format de fichier externe avec CREATE EXTERNAL FILE FORMAT.

   -- FORMAT TYPE: Type of format in Azure Blob Storage (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
   -- In this example, the files are pipe (|) delimited
   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 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 = 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 joint les données relationnelles avec celles stockées dans le Blob Azure. Il sélectionne les clients qui conduisent à plus de 56 km/h, et joint les données clients structurées stockées dans SQL Server avec les données des capteurs de voiture stockées dans le Stockage Blob Azure.

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  

Importer des données

La requête suivante importe des données externes dans APS. Cet exemple importe des données pour les pilotes rapides dans APS afin d’effectuer une analyse plus approfondie. Pour améliorer les performances, elle tire parti de la technologie Columnstore dans APS.

CREATE TABLE Fast_Customers
WITH
(CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH (CustomerKey))
AS
SELECT DISTINCT
      Insured_Customers.CustomerKey, Insured_Customers.FirstName, Insured_Customers.LastName,   
      Insured_Customers.YearlyIncome, Insured_Customers.MaritalStatus  
from Insured_Customers INNER JOIN   
(  
      SELECT * FROM CarSensor_Data where Speed > 35   
) AS SensorD  
ON Insured_Customers.CustomerKey = SensorD.CustomerKey  

Exporter les données

La requête suivante exporte les données d’APS vers Stockage Blob Azure. Il peut être utilisé pour archiver des données relationnelles dans stockage Blob Azure tout en étant en mesure de l’interroger.

-- Export data: Move old data to Azure Blob Storage while keeping it query-able via an external table.  
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] 
WITH (  
      LOCATION='/archive/customer/2009',  
      DATA_SOURCE = AzureStorage,  
      FILE_FORMAT = TextFileFormat
)  
AS
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 SSDT

Dans SQL Server Data Tools (SSDT), 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.

Étape suivante