Konfigurieren von PolyBase für den Zugriff auf externe Daten in Azure Blob Storage

GILT FÜR: SQL Server (nur Windows) Azure SQL-Datenbank Azure Synapse Analytics Analytics Platform System (PDW)

In diesem Artikel wird erläutert, wie Sie PolyBase in einer SQL Server-Instanz verwenden, um externe Daten in Azure Blob Storage abzufragen.

Voraussetzungen

Wenn Sie PolyBase nicht installiert haben, finden Sie weitere Informationen unter PolyBase installation (Installieren von PolyBase). Die Voraussetzungen für die Installation werden im entsprechenden Artikel erläutert.

SQL Server 2022

Konfigurieren Sie in SQL Server 2022 Preview Ihre externen Datenquellen so, dass neue Connectors verwendet werden, wenn Sie eine Verbindung mit Azure Storage herstellen. In der Tabelle unten sind die Ergebnisse zusammengefasst:

Externe Datenquelle From Beschreibung
Azure Blob Storage wasb[s] abs
ADLS Gen2 abfs[s] adls

Konfigurieren der Azure Blob Storage-Konnektivität

Konfigurieren Sie zunächst SQL Server PolyBase für die Verwendung von Azure Blob Storage.

  1. Führen Sie sp_configure aus, wobei „hadoop connectivity“ auf einen Azure Blob Storage-Anbieter festgelegt ist. Informationen zum Ermitteln des Werts für Anbieter finden Sie unter Konfiguration der PolyBase-Konnektivität. Standardmäßig ist die Hadoop-Konnektivität auf 7 festgelegt.

    -- 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. Starten Sie SQL Server mithilfe von services.msc neu. Durch den Neustart von SQL Server werden folgende Dienste neu gestartet:

    • SQL Server PolyBase-Datenverschiebungsdienst
    • SQL Server PolyBase-Engine

    stop and start PolyBase services in services.msc

Konfigurieren einer externen Tabelle

Um die Daten in Ihrer Hadoop-Datenquelle abzufragen, müssen Sie eine externe Tabelle definieren, die in Transact-SQL-Abfragen verwendet werden soll. Die folgenden Schritte beschreiben, wie Sie die externe Tabelle konfigurieren.

  1. Erstellen Sie einen Hauptschlüssel in der Datenbank. Sie benötigen den Hauptschlüssel, um das Geheimnis für die Anmeldeinformationen zu verschlüsseln.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';  
    
  2. Erstellen Sie datenbankweite Anmeldeinformationen für Azure Blob Storage. IDENTITY kann beliebig definiert werden, da es nicht verwendet wird.

    -- 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. Erstellen Sie mit CREATE EXTERNAL DATA SOURCE eine externe Datenquelle. Beachten Sie, dass die Authentifizierung beim Herstellen einer Verbindung mit Azure Storage über den WASB[s]-Connector mit einem Speicherkontoschlüssel und nicht mit einer Shared Access Signature (SAS) erfolgen muss.

    -- 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. Erstellen Sie mit CREATE EXTERNAL FILE FORMAT ein externes Dateiformat.

    -- 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. Erstellen Sie mit CREATE EXTERNAL TABLE eine externe Tabelle, die auf in Azure Storage gespeicherte Daten verweist. In diesem Beispiel enthalten die externen Daten Fahrzeugsensordaten. LOCATION kann nicht / sein, aber wie in diesem Beispiel muss /Demo/ zuvor nicht vorhanden sein.

    -- 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. Erstellen Sie Statistiken für eine externe Tabelle.

    CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  
    

PolyBase-Abfragen

Es gibt drei Funktionen, für die PolyBase geeignet ist:

  • Ad-hoc-Abfragen von externen Tabellen
  • Importieren von Daten
  • Exportieren von Daten

Die folgenden Abfragen stellen fiktive Kfz-Sensordaten für das Beispiel bereit.

Ad-hoc-Abfragen

Die folgende Ad-hoc-Abfrage verknüpft relationale Daten mit Hadoop-Daten. Es werden Kunden ausgewählt, die schneller als 35 Meilen pro Stunde fahren, und in SQL Server gespeicherte strukturierte Daten mit in Hadoop gespeicherten Daten aus Kfz-Sensoren verknüpft.

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)  

Importieren von Daten mit PolyBase

Die folgende Abfrage importiert externe Daten in SQL Server. Dieses Beispiel importiert Daten zu schnellen Fahrern in SQL Server zur genaueren Analyse. Zur Verbesserung der Leistung wird die Columnstore-Technologie verwendet.

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;  

Exportieren von Daten mit PolyBase

Die folgende Abfrage exportiert Daten aus SQL Server nach Azure Blob Storage. Aktivieren Sie zuerst den PolyBGase-Export. Erstellen Sie dann eine externe Tabelle für das Ziel, bevor Sie mit dem Datenexport beginnen.

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

Der PolyBase-Export mit dieser Methode kann mehrere Dateien erstellen.

Anzeigen von PolyBase-Objekten in SSMS

In SSMS werden externe Tabellen in einem separaten Ordner Externe Tabellenangezeigt. Externe Datenquellen und externe Dateiformate befinden sich in Unterordnern unter Externe Ressourcen.

PolyBase objects in SSMS

Nächste Schritte

In den folgenden Artikeln finden Sie weitere Möglichkeiten zur Verwendung und Überwachung von PolyBase: