Abfragen von Dateien mithilfe eines serverlosen SQL-Pools

10 Minuten

Sie können einen serverlosen SQL-Pool verwenden, um Datendateien in verschiedenen gängigen Dateiformaten abzufragen, darunter:

Durch Trennzeichen getrennter Text, z. B. durch Kommas getrennte Werte (CSV-Dateien).
JSON-Dateien (JavaScript Object Notation).
Parquet-Dateien.

Die grundlegende Syntax für die Abfrage ist für alle diese Dateitypen gleich und basiert auf der SQL-Funktion OPENROWSET, die aus den Daten in mindestens einer Datei ein tabellarisches Rowset generiert. Die folgende Abfrage kann beispielsweise verwendet werden, um Daten aus CSV-Dateien zu extrahieren.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

Die OPENROWSET-Funktion enthält weitere Parameter, die verschiedene Faktoren bestimmen, darunter:

Das Schema des sich ergebenden Rowsets
Zusätzliche Formatierungsoptionen für durch Trennzeichen getrennte Textdateien.

Tipp

Die vollständige Syntax für die OPENROWSET-Funktion finden Sie in der Dokumentation zu Azure Synapse Analytics.

Die Ausgabe von OPENROWSET ist ein Rowset, dem ein Alias zugewiesen werden muss. Im vorherigen Beispiel wird der Alias rows verwendet, um das sich ergebende Rowset zu benennen.

Der BULK-Parameter enthält die vollständige URL zum Speicherort im Data Lake, der die Datendateien enthält. Dies kann eine einzelne Datei oder ein Ordner mit einem Platzhalterausdruck sein, um die Dateitypen zu filtern, die berücksichtigt werden sollen. Der FORMAT-Parameter gibt den Typ der abgefragten Daten an. Im Beispiel oben wird durch Trennzeichen getrennter Text aus allen CSV-Dateien im Ordner files gelesen.

Hinweis

In diesem Beispiel wird davon ausgegangen, dass der Benutzer Zugriff auf die Dateien im zugrunde liegenden Speicher besitzt. Wenn die Dateien mit einem SAS-Schlüssel oder einer benutzerdefinierten Identität geschützt sind, müssen Sie Anmeldeinformationen mit Geltungsbereich für den Server erstellen.

Wie im Beispiel oben gezeigt, können Sie Platzhalter im BULK-Parameter verwenden, um Dateien in die Abfrage einzuschließen oder von ihr auszuschließen. In der folgenden Liste werden einige Beispiele aufgeführt, wie dieser Parameter verwendet werden kann:

https://mydatalake.blob.core.windows.net/data/files/file1.csv: Nur file1.csv im Ordner files einbeziehen.
https://mydatalake.blob.core.windows.net/data/files/file*.csv: Alle CSV-Dateien im Ordner files mit Namen, die mit „file“ beginnen.
https://mydatalake.blob.core.windows.net/data/files/*: Alle Dateien im Ordner files.
https://mydatalake.blob.core.windows.net/data/files/**: Alle Dateien im Ordner files und rekursiv seine Unterordner.

Sie können auch mehrere Dateipfade im BULK-Parameter angeben, indem Sie die einzelnen Pfade durch ein Komma trennen.

Abfragen von durch Trennzeichen getrennten Textdateien

Durch Trennzeichen getrennte Textdateien sind in vielen Unternehmen ein gängiges Dateiformat. Die spezifische Formatierung, die in durch Trennzeichen getrennten Dateien verwendet wird, kann unterschiedlich sein, z. B.:

Mit und ohne Kopfzeile.
Werte mit Kommas und Tabstopps als Trennzeichen.
Zeilenenden im Windows- und Unix-Stil.
Werte mit und ohne Anführungszeichen sowie Zeichen mit Escapezeichen.

Unabhängig von der Art der durch Trennzeichen getrennten Datei, die Sie verwenden, können Sie Daten aus diesen Dateien lesen, indem Sie die OPENROWSET-Funktion mit dem FORMAT-Parameter csv und anderen Parametern verwenden, die für die spezifischen Formatierungsdetails Ihrer Daten erforderlich sind. Beispiel:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

Die PARSER_VERSION wird verwendet, um zu bestimmen, wie die Abfrage die in den Dateien verwendete Textcodierung interpretiert. Version 1.0 ist der Standardwert und unterstützt eine Vielzahl von Dateicodierungen, während Version 2.0 weniger Codierungen unterstützt, aber eine bessere Leistung bietet. Der FIRSTROW-Parameter wird verwendet, um Zeilen in der Textdatei zu überspringen, unstrukturierten Präambeltext zu entfernen oder eine Zeile mit Spaltenüberschriften zu ignorieren.

Weitere Parameter, die Sie möglicherweise beim Arbeiten mit durch Trennzeichen getrennten Textdateien benötigen, sind:

FIELDTERMINATOR: Das Zeichen, das zum Trennen von Feldwerten in jeder Zeile verwendet wird. Eine durch Tabstopps getrennte Datei trennt z. B. Felder durch ein TAB-Zeichen (\t). Das Standard-Feldabschlusszeichen ist ein Komma (,).
ROWTERMINATOR: Das Zeichen, das zum Kennzeichnen des Endes einer Datenzeile verwendet wird. Bei einer Windows-Standardtextdatei wird beispielsweise eine Kombination aus Wagenrücklauf (CR) und Zeilenvorschub (LF) verwendet, die durch den Code \n angegeben wird. Textdateien im UNIX-Stil verwenden hingegen ein einzelnes Zeilenvorschubzeichen, das mithilfe des Codes 0x0a angegeben werden kann.
FIELDQUOTE: Das Zeichen, das zum Einschließen von Zeichenfolgenwerten in Anführungszeichen verwendet wird. Um beispielsweise sicherzustellen, dass das Komma im Adressfeldwert 126 Main St, apt 2 nicht als Feldtrennzeichen interpretiert wird, können Sie den gesamten Feldwert wie folgt in Anführungszeichen einschließen: "126 Main St, apt 2". Das doppelte Anführungszeichen (") ist das Standard-Feldanführungszeichen.

Tipp

Ausführliche Informationen zu weiteren Parametern beim Arbeiten mit durch Trennzeichen getrennten Textdateien finden Sie in der Dokumentation zu Azure Synapse Analytics.

Angeben des Rowsetschemas

Es ist üblich, dass durch Trennzeichen getrennte Textdateien die Spaltennamen in der ersten Zeile enthalten. Die OPENROWSET-Funktion kann diese Tatsache nutzen, um das Schema für das sich ergebende Rowset zu definieren und die Datentypen der Spalten basierend auf den darin enthaltenen Werten automatisch abzuleiten. Betrachten Sie z. B. den folgenden durch Trennzeichen getrennten Text:

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

Die Daten bestehen aus den folgenden drei Spalten:

product_id (Integerwert)
product_name (Zeichenfolge)
list_price (Dezimalzahl)

Sie können die folgende Abfrage verwenden, um die Daten mit den richtigen Spaltennamen und entsprechend abgeleiteten SQL Server-Datentypen (in diesem Fall INT, NVARCHAR und DECIMAL) zu extrahieren.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

Der HEADER_ROW-Parameter (der nur bei Verwendung von Parserversion 2.0 verfügbar ist) weist das Abfragemodul an, die erste Datenzeile in jeder Datei wie folgt als Spaltennamen zu verwenden:

product_id	product_name	list_price
123	Widget	12.9900
124	Gadget	3.9900

Sehen Sie sich jetzt die folgenden Daten an:

123,Widget,12.99
124,Gadget,3.99

Dieses Mal enthält die Datei nicht die Spaltennamen in einer Kopfzeile. Während die Datentypen weiterhin abgeleitet werden können, werden die Spaltennamen auf C1, C2, C3 usw. festgelegt.

C1	C2	C3
123	Widget	12.9900
124	Gadget	3.9900

Um explizite Spaltennamen und Datentypen anzugeben, können Sie die Standardspaltennamen und abgeleiteten Datentypen überschreiben, indem Sie eine Schemadefinition in einer WITH-Klausel wie folgt bereitstellen:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

Diese Abfrage generiert die erwarteten Ergebnisse:

product_id	product_name	list_price
123	Widget	12.99
124	Gadget	3,99

Tipp

Beim Arbeiten mit Textdateien kann es zu einer Inkompatibilität mit UTF-8-codierten Daten und der Sortierung kommen, die in der Masterdatenbank für den serverlosen SQL-Pool verwendet werden. Um dies zu umgehen, können Sie eine kompatible Sortierung für einzelne VARCHAR-Spalten im Schema angeben. Weitere Informationen finden Sie in den Anleitungen zur Problembehandlung.

Abfragen von JSON-Daten

JSON ist ein gängiges Format für Webanwendungen, die Daten über REST-Schnittstellen austauschen oder NoSQL-Datenspeicher wie Azure Cosmos DB verwenden. Daher ist es nicht ungewöhnlich, Daten als JSON-Dokumente in Dateien in einem Data Lake für die Analyse zu speichern.

Beispielsweise könnte eine JSON-Datei, die ein einzelnes Produkt definiert, wie folgt aussehen:

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

Um Produktdaten aus einem Ordner zurückzugeben, der mehrere JSON-Dateien in diesem Format enthält, können Sie die folgende SQL-Abfrage verwenden:

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET hat kein spezifisches Format für JSON-Dateien, daher müssen Sie das CSV-Format mit FIELDTERMINATOR, FIELDQUOTE und ROWTERMINATOR verwenden, das auf 0x0b festgelegt ist, sowie ein Schema, das eine einzelne NVARCHAR(MAX)-Spalte enthält. Das Ergebnis dieser Abfrage ist ein Rowset, das eine einzelne Spalte mit JSON-Dokumenten wie in diesem Beispiel enthält:

DOC
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

Um einzelne Werte aus den JSON-Dateien zu extrahieren, können Sie die JSON_VALUE-Funktion in der SELECT-Anweisung verwenden, wie hier gezeigt:

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

Diese Abfrage würde ein Rowset zurückgeben, das den folgenden Ergebnissen ähnelt:

product	Preis
Widget	12.99
Gadget	3,99

Abfragen von Parquet-Dateien

Parquet ist ein häufig verwendetes Format für die Big Data-Verarbeitung in verteiltem Dateispeicher. Es handelt sich um ein effizientes Datenformat, das für Komprimierung und analytische Abfragen optimiert ist.

In den meisten Fällen ist das Schema der Daten in die Parquet-Datei eingebettet, sodass Sie nur den BULK-Parameter mit einem Pfad zu den zu lesenden Dateien und einem FORMAT-Parameter des parquet wie hier gezeigt angeben müssen:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

Abfragen von partitionierten Daten

In einem Data Lake ist es üblich, Daten zu partitionieren, indem sie auf mehrere Dateien in Unterordnern aufgeteilt werden, die Partitionierungskriterien widerspiegeln. Auf diese Weise können Systeme für verteilte Verarbeitung parallel auf mehreren Partitionen der Daten arbeiten oder Datenlesevorgänge aus bestimmten Ordnern basierend auf Filterkriterien problemlos entfernen. Angenommen, Sie müssen Auftragsdaten effizient verarbeiten und häufig nach Jahr und Monat filtern, in denen Bestellungen aufgegeben wurden. Sie können die Daten wie folgt mithilfe von Ordnern partitionieren:

/orders
- /year=2020
  - /month=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /month=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /year=2021
  - /month=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

Um eine Abfrage zu erstellen, die die Ergebnisse so filtert, dass nur die Bestellungen für Januar und Februar 2020 enthalten sind, können Sie den folgenden Code verwenden:

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

Die nummerierten Dateipfadparameter in der WHERE-Klausel verweisen auf die Platzhalter in den Ordnernamen im BULK-Pfad. Der Parameter 1 ist also das * im Ordnernamen year=*, und Parameter 2 ist das * im Ordnernamen month=*.

Weiter

Abfragen von durch Trennzeichen getrennten Textdateien

Angeben des Rowsetschemas

Abfragen von JSON-Daten

Abfragen von Parquet-Dateien

Abfragen von partitionierten Daten

Feedback