Transformieren von Datendateien mit der CREATE EXTERNAL TABLE AS SELECT-Anweisung

5 Minuten

Die SQL-Sprache enthält viele Features und Funktionen für das Bearbeiten von Daten. Sie können SQL beispielsweise für Folgendes verwenden:

Filtern von Zeilen und Spalten in einem Dataset
Umbenennen von Datenfeldern und Konvertieren von Datentypen
Berechnen von abgeleiteten Datenfeldern
Ändern von Zeichenfolgenwerten
Gruppieren und Aggregieren von Daten

Die serverlosen SQL-Pools in Azure Synapse können zum Ausführen von SQL-Anweisungen verwendet werden, die Daten transformieren und die Ergebnisse als Datei in einem Data Lake für die weitere Verarbeitung oder Abfrage beibehalten. Wenn Sie mit der Transact-SQL-Syntax vertraut sind, können Sie eine SELECT-Anweisung erstellen, die die gewünschte Transformation anwendet, und die Ergebnisse der SELECT-Anweisung in einem ausgewählten Dateiformat mit einem Metadatentabellenschema speichern, das mit SQL abgefragt werden kann.

Sie können eine CREATE EXTERNAL TABLE AS SELECT-Anweisung (CETAS) in einem dedizierten SQL-Pool oder serverlosen SQL-Pool verwenden, um die Ergebnisse einer Abfrage in einer externen Tabelle zu speichern, die ihre Daten in einer Datei im Data Lake speichert.

Die CETAS-Anweisung enthält eine SELECT-Anweisung, die Daten aus einer beliebigen gültigen Datenquelle abfragt und bearbeitet (dies kann eine vorhandene Tabelle oder Ansicht in einer Datenbank sein, oder eine OPENROWSET-Funktion, die dateibasierte Daten aus dem Data Lake liest). Die Ergebnisse der SELECT-Anweisung werden dann in einer externen Tabelle gespeichert. Dabei handelt es sich um ein Metadatenobjekt in einer Datenbank, das eine relationale Abstraktion der in den Dateien gespeicherten Daten bereitstellt. Dieses Konzept wird in der folgenden Abbildung veranschaulicht:

Abbildung mit einer CREATE EXTERNAL TABLE AS SELECT-Anweisung, die Abfrageergebnisse als Datei speichert

Durch Anwenden dieser Methode können Sie SQL verwenden, um Daten aus Dateien oder Tabellen zu extrahieren und zu transformieren und die transformierten Ergebnisse für die nachgelagerte Verarbeitung oder Analyse zu speichern. Nachfolgende Vorgänge für die transformierten Daten können anhand der relationalen Tabelle in der SQL-Pooldatenbank oder direkt mit den zugrunde liegenden Datendateien ausgeführt werden.

Erstellen externer Datenbankobjekte zur Unterstützung von CETAS

Für die Verwendung von CETAS-Ausdrücken müssen Sie die folgenden Objekttypen in einer Datenbank für einen serverlosen oder dedizierten SQL-Pool erstellen. Bei der Verwendung eines serverlosen SQL-Pools erstellen Sie diese Objekte in einer benutzerdefinierten Datenbank (mithilfe der CREATE DATABASE-Anweisung) und nicht in der integrierten Datenbank.

Externe Datenquelle

Eine externe Datenquelle kapselt eine Verbindung zu einem Dateisystemspeicherort in einem Data Lake. Sie können diese Verbindung dann verwenden, um einen relativen Pfad anzugeben, in dem die Datendateien für die von der CETAS-Anweisung erstellte externe Tabelle gespeichert werden.

Wenn sich die Quelldaten für die CETAS-Anweisung in Dateien im gleichen Data Lake-Pfads befinden, können Sie dieselbe externe Datenquelle in der OPENROWSET-Funktion verwenden, die zum Abfragen verwendet wird. Alternativ können Sie eine separate externe Datenquelle für die Quelldateien erstellen oder einen vollqualifizierten Dateipfad in der OPENROWSET-Funktion verwenden.

Verwenden Sie zum Erstellen einer externen Datenquelle die CREATE EXTERNAL DATA SOURCE-Anweisung, wie im folgenden Beispiel gezeigt:

-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
    TYPE = HADOOP, -- For dedicated SQL pool
    -- TYPE = BLOB_STORAGE, -- For serverless SQL pool
    CREDENTIAL = storageCred
);

Im vorherigen Beispiel wird davon ausgegangen, dass Benutzer für die Ausführung von Abfragen, die die externe Datenquelle nutzen, über ausreichende Berechtigungen für den Zugriff auf die Dateien verfügen. Alternativ dazu können Sie Anmeldeinformationen in der externen Datenquelle kapseln, um diese für den Zugriff auf Dateidaten zu verwenden, ohne allen Benutzern Berechtigungen zum direkten Lesen zu erteilen:

CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = storagekeycred
);

Tipp

Zusätzlich zur SAS-Authentifizierung können Sie Anmeldeinformationen definieren, die die verwaltete Identität (die Microsoft Entra-Identität, die von Ihrem Azure Synapse-Arbeitsbereich verwendet wird), einen bestimmten Microsoft Entra-Prinzipal oder die Passthrough-Authentifizierung basierend auf der Identität des Benutzers verwenden, der die Abfrage ausführt (dies ist der standardmäßige Authentifizierungstyp). Weitere Informationen zum Verwenden von Anmeldeinformationen in einem serverlosen SQL-Pool finden Sie im Artikel Speicherkontozugriff für serverlose SQL-Pools in Azure Synapse Analytics in der Dokumentation zu Azure Synapse Analytics.

Externes Dateiformat

Die CETAS-Anweisung erstellt eine Tabelle mit den in Dateien gespeicherten Daten. Sie müssen das Format der Dateien angeben, die Sie als externes Dateiformat erstellen möchten.

Verwenden Sie zum Erstellen eines externen Dateiformats die CREATE EXTERNAL FILE FORMAT-Anweisung, wie im folgenden Beispiel gezeigt:

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
        FORMAT_TYPE = PARQUET,
        DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );

Tipp

In diesem Beispiel werden die Dateien im PARQUET-Format gespeichert. Sie können auch externe Dateiformate für andere Dateitypen erstellen. Ausführliche Informationen finden Sie unter CREATE EXTERNAL FILE FORMAT (Transact-SQL).

Verwenden der CETAS-Anweisung

Nachdem Sie eine externe Datenquelle und ein externes Dateiformat erstellt haben, können Sie die CETAS-Anweisung verwenden, um Daten zu transformieren und die Ergebnisse in einer externen Tabelle zu speichern.

Angenommen, die Quelldaten, die Sie transformieren möchten, bestehen aus Verkaufsaufträgen in durch Kommas getrennten Textdateien, die in einem Ordner in einem Data Lake sind. Sie möchten die Daten so filtern, dass sie nur Bestellungen enthalten, die als „Sonderbestellung“ gekennzeichnet sind, und die transformierten Daten in einem anderen Ordner im selben Data Lake speichern. Sie können dieselbe externe Datenquelle sowohl für die Quell- als auch für Zielordner verwenden, wie im folgenden Beispiel gezeigt:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'sales_orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Die Parameter LOCATION und BULK im vorherigen Beispiel sind relative Pfade für die Ergebnisse bzw. Quelldateien. Die Pfade sind relativ zum Speicherort des Dateisystems, auf den die externe Datenquelle der Dateien verweist.

Es ist wichtig zu wissen, dass Sie eine externe Datenquelle verwenden müssen, um den Speicherort anzugeben, an dem die transformierten Daten für die externe Tabelle gespeichert werden sollen. Wenn dateibasierte Quelldaten in der gleichen Ordnerhierarchie gespeichert werden, können Sie dieselbe externe Datenquelle verwenden. Andernfalls können Sie eine zweite Datenquelle verwenden, um eine Verbindung mit den Quelldaten zu definieren oder den vollqualifizierten Pfad zu verwenden, wie im folgenden Beispiel gezeigt:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'https://mystorage.blob.core.windows.net/data/sales_orders/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Löschen von externen Tabellen

Wenn Sie die externe Tabelle mit den transformierten Daten nicht mehr benötigen, können Sie sie aus der Datenbank löschen, indem Sie die DROP EXTERNAL TABLE-Anweisung verwenden, wie im Folgenden gezeigt:

DROP EXTERNAL TABLE SpecialOrders;