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 (16.x) Ihre externen Datenquellen für die Verwendung neuer Connectors, 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. 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 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 = '<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. Beachten Sie, dass die Authentifizierung beim Herstellen einer Verbindung mit Azure Storage über einen WASB[s]-Konnektor 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 (  
         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.

Nächste Schritte

Weitere Lernprogramme zum Erstellen externer Datenquellen und externer Tabellen für eine Vielzahl von Datenquellen finden Sie unter PolyBase Transact-SQL-Referenz.

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

• PolyBase-Erweiterungsgruppen
• Problembehandlung in PolyBase
• CREATE EXTERNAL DATA SOURCE