Partager via


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

S’applique à : SQL Server (Windows uniquement) Non pris en charge. Azure SQL Database Non pris en charge. Azure Synapse Analytics Non pris en charge. 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. Exécutez sp_configure avec 'hadoop connectivity' défini sur un fournisseur Stockage Blob Azure. Pour trouver la valeur pour les fournisseurs, consultez Configuration de la connectivité PolyBase. Par défaut, la connectivité Hadoop est définie sur 7.

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

    arrêter et démarrer les services PolyBase dans services.msc

  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

    arrêter et démarrer les services PolyBase dans services.msc

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. Vous pouvez choisir n’importe quelle IDENTITY puisque celle-ci ne sera pas utilisée.

    -- 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. Notez que quand vous vous connectez au Stockage Azure via le connecteur wasb[s], l’authentification doit être effectuée avec une clé de compte de stockage, et non 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 (  
          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 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)  
    
  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.

Objets PolyBase dans SSMS

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