Configurare PolyBase per l'accesso a dati esterni in Archiviazione BLOB di Azure

Si applica a: SQL Server (solo Windows) Database SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

L'articolo illustra come usare PolyBase in un'istanza di SQL Server per eseguire query sui dati esterni in Archiviazione BLOB di Azure.

Prerequisiti

Se PolyBase non è stato installato, vedere Installazione di PolyBase. Nell'articolo sull'installazione vengono illustrati i prerequisiti.

SQL Server 2022

In SQL Server 2022 (16.x) configurare le proprie origini dati esterne per l'uso di nuovi connettori durante la connessione ad Archiviazione di Azure. La tabella seguente offre un riepilogo della modifica:

Origine dati esterna	Da	Per
Archiviazione BLOB di Azure	wasb[s]	abs
ADLS Gen 2	abfs[s]	adls

Configurare la connettività ad Archiviazione BLOB di Azure

Configurare prima di tutto SQL Server PolyBase per usare Archiviazione BLOB di Azure.

1. Riavviare SQL Server tramite services.msc. Il riavvio di SQL Server comporta il riavvio di questi servizi:

   • Servizio spostamento dati di PolyBase per SQL Server
   • Motore di PolyBase per SQL Server

   

Configurare una tabella esterna

Per eseguire query sui dati nell'origine dati Hadoop, è necessario definire una tabella esterna da usare in query Transact-SQL. Le procedure seguenti descrivono come configurare la tabella esterna.

1. Creare una chiave master nel database. La chiave master è necessaria per crittografare il segreto delle credenziali.

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

2. Creare credenziali con ambito database per Archiviazione BLOB di Azure tramite una firma di accesso condiviso (SAS). IDENTITY può essere qualsiasi elemento non usato.

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

3. Creare un'origine dati esterna con CREATE EXTERNAL DATA SOURCE. Si noti che quando ci si connette ad Archiviazione di Azure tramite il connettore WASB[s], l'autenticazione deve essere eseguita con una chiave dell'account di archiviazione e non con una firma di accesso condiviso.

   -- 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. Creare un formato di file esterno con 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. Creare una tabella esterna che punta ai dati archiviati in Archiviazione di Azure con CREATE EXTERNAL TABLE. In questo esempio, i dati esterni contengono i dati del sensore di auto; LOCATION non può essere /, ma /Demo/, poiché in questo esempio non è necessario che esistesse in precedenza.

   -- 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. Creare statistiche per una tabella esterna.

   CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  
   

Query PolyBase

PolyBase è adatto per assolvere a una triplice funzione:

• Esecuzione di query ad hoc su tabelle esterne.
• Importazione di dati.
• Esportazione di dati.

Le query seguenti forniscono esempi con dati fittizi di sensori di auto.

Query ad hoc

La query ad-hoc seguente crea un join relazionale con dati Hadoop. Seleziona i clienti che guidano a velocità superiori a 35 miglia/h, unendo in join i dati dei clienti strutturati archiviati in SQL Server con i dati dei sensori di auto archiviati in 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)  

Importare dati con PolyBase

La query seguente importa dati esterni in SQL Server. Questo esempio importa i dati relativi agli autisti che guidano veloce in SQL Server per eseguire un'analisi più dettagliata. Per migliorare le prestazioni, sfrutta la tecnologia 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;  

Esportare dati con PolyBase

La query seguente esporta i dati da SQL Server in Archiviazione BLOB di Azure. Abilitare prima l'esportazione di PolyBase. Creare poi una tabella esterna per la destinazione prima dell'esportazione dei dati.

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

Con questo metodo, l'esportazione di PolyBase può creare più file.

Visualizzare gli oggetti PolyBase in SQL Server Management Studio

In SQL Server Management Studio, le tabelle esterne vengono visualizzate in una cartella separata Tabelle esterne. Le origini dati esterne e i formati di file esterni si trovano nelle sottocartelle in External Resources.

Passaggi successivi

Per altre esercitazioni sulla creazione di origini dati esterne e tabelle esterne in un'ampia gamma di origini dati, vedere le Informazioni di riferimento su Transact-SQL per PolyBase.

Per esplorare altri modi per usare e monitorare PolyBase, vedere gli articoli seguenti:

• Gruppi con scalabilità orizzontale di PolyBase.
• Risoluzione dei problemi di PolyBase.
• CREATE EXTERNAL DATA SOURCE