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

Gilt für: SQL Server 2016 (13.x) und höhere Versionen unter Windows

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, lesen Sie "Installieren von PolyBase unter Windows". Die Voraussetzungen für die Installation werden im entsprechenden Artikel erläutert.

SQL Server 2022

Konfigurieren Sie in SQL Server 2022 (16.x) Ihre externen Datenquellen für die Verwendung neuer Connectors, wenn Sie eine Verbindung mit Azure Storage herstellen. Die Änderungen sind in der folgenden Tabelle 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. 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

   

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 Datenbankmasterschlüssel (DMK) in der Datenbank. ** Das DMK ist erforderlich, um die geheime Anmeldeinformation zu verschlüsseln.

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

2. Erstellen Sie datenbankweite Anmeldeinformationen für Azure Blob Storage mithilfe einer Shared Access Signature (SAS). IDENTITY kann beliebig definiert werden, da es nicht verwendet wird.

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

3. Erstellen Sie mit CREATE EXTERNAL DATA SOURCE eine externe Datenquelle. Beim Herstellen einer Verbindung mit dem Azure Storage über den WASB[s]-Connector erfolgt die Authentifizierung mit einer freigegebenen Zugriffssignatur (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. 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 (
       DATA_SOURCE = AzureStorage,
       LOCATION = '/Demo/',
       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. Um die Leistung zu verbessern, verwendet sie die Columnstore-Technologie.

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

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.

Verwandte Inhalte

• PolyBase Transact-SQL Referenz
• PolyBase-Scale-Out-Gruppen
• Überwachen und Beheben von Problemen mit PolyBase
• EXTERNE DATENQUELLE ERSTELLEN (Transact-SQL)