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

Si applica a: SQL Server 2016 (13.x) e versioni successive - Solo Windows

L'articolo spiega 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 Installare PolyBase in Windows. Nell'articolo sull'installazione vengono illustrati i prerequisiti.

SQL Server 2022

In SQL Server 2022 (16.x), configura le origini dati esterne per usare nuovi connettori quando ti connetti ad Azure Storage. Nella tabella seguente vengono riassunte le modifiche:

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

Configurare la connettività con l'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 ricerca PolyBase di 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 del database (DMK) nel database. La DMK è necessaria per crittografare il segreto delle credenziali.

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

2. Creare una credenziale con ambito del database per l'Archiviazione BLOB di Azure utilizzando una firma di accesso condiviso (SAS). IDENTITY può essere qualsiasi cosa poiché non viene utilizzato.

   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. Quando ci si connette ad Archiviazione di Azure tramite il connettore WASB[s], è richiesta l'autenticazione con una firma di accesso condiviso (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. 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 Azure Storage 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 (
       DATA_SOURCE = AzureStorage,
       LOCATION = '/Demo/',
       FILE_FORMAT = TextFileFormat
   );
   

6. Creare statistiche per una tabella esterna.

   CREATE STATISTICS StatsForSensors
   ON CarSensor_Data(CustomerKey, Speed);
   

Query di 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 seguente query ad-hoc unisce dati relazionali con dati Hadoop. Seleziona i clienti che guidano a velocità superiori a 35 miglia/h e unisce i dati dei clienti strutturati archiviati in SQL Server con i dati dei sensori delle 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, utilizza la tecnologia di archiviazione a colonne.

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
EXECUTE sp_configure 'allow polybase export', 1;
RECONFIGURE;
GO

-- 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 (
    DATA_SOURCE = HadoopHDP2,
    LOCATION = '/old_data/2009/customerdata',
    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 AS T1
     INNER JOIN CarSensor_Data AS T2
         ON (T1.CustomerKey = T2.CustomerKey)
WHERE T2.YearMeasured = 2009
      AND T2.Speed > 40;

L'esportazione polyBase con questo metodo potrebbe 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 Risorse Esterne.

Contenuti correlati

• Informazioni di riferimento su Transact-SQL per PolyBase
• Gruppi con scalabilità orizzontale di PolyBase
• Monitorare e risolvere i problemi di PolyBase
• CREA FONTE DATI ESTERNA (Transact-SQL)