Freigeben über

Abfragen von Dateien aus einem externen Data Lake

Gilt für:✅ SQL-Analyseendpunkt und Warehouse in Microsoft Fabric

Mit Fabric Data Warehouse und dem SQL-Analyseendpunkt können Sie Daten abfragen, die in Dateien in einem Data Lake gespeichert sind, indem Sie Transact-SQL (T-SQL)-Code verwenden.

Sie bieten einen vertrauten T-SQL-Abfrageoberflächenbereich, der das Abfragen strukturierter, halbstrukturierter und unstrukturierter Daten unterstützt. In Fabric Data Warehouse und dem SQL-Analyseendpunkt können Sie verschiedene Dateiformate wie Parkett, CSV und JSONL abfragen.

OPENROWSET

Die vereinfachte Syntax der OPENROWSET Funktion lautet:

OPENROWSET( BULK '{file path}', [ options...] )
   [ WITH ( {result set column schema} ) ]

OPENROWSET unterstützt drei Möglichkeiten zum Angeben des Dateispeicherorts:

  • Absoluter Pfad , der die vollständige URL zu der Datei enthält, die Sie lesen möchten. Dies ist der einfachste Ansatz, wenn Sie den genauen Dateispeicherort kennen.
  • Relativer Pfad mit einer Datenquelle – der relative Pfad wird an den Stammspeicherort angefügt, der in der Datenquelle definiert ist, um den vollständigen Pfad zu bilden. Die Datenquelle kann auf eine externe Speicher-URL (z. B. Azure Blob, ADLS Gen2) oder die Stamm-URL eines Lakehouse in Fabric verweisen.
  • Relativer Pfad beginnend mit /Files – Diese Option funktioniert nur, wenn sie ein Lakehouse über seinen SQL-Analyseendpunkt abfragen. Der /Files Ordner stellt den Lakehouse-Dateibereich dar, und Sie können relative Pfade verwenden, ohne eine Datenquelle zu definieren.

Die Dateien können in einer der folgenden Speicheroptionen platziert werden:

  • Azure Data Lake Storage (ADLS) – ein skalierbarer, hierarchischer Cloudspeicherdienst, der für Big Data Analytics-Workloads optimiert ist.
  • Azure Blob Storage – ein allgemeiner Objektspeicherdienst zum Speichern großer Mengen unstrukturierter Daten wie Dateien, Bilder und Protokolle.
  • Fabric OneLake – der native Lake-Speicher für Microsoft Fabric, der einen einheitlichen, logischen Datensee für alle Fabric-Workloads bereitstellt. OneLake ermöglicht auch den indirekten Zugriff auf Daten, die an externen Standorten gespeichert sind, einschließlich:
    • Amazon S3 – Objektspeicherdienst, der von Amazon Web Services bereitgestellt wird.
    • Google Cloud Storage (GCS) – Objektspeicherdienst für Google Cloud Platform.
    • SharePoint – Gemeinsame Dokument- und Dateispeicherdienst in Microsoft 365.
    • OneDrive – Persönlicher Cloudspeicherdienst für Dateien in Microsoft 365.

Diese Funktionen ermöglichen eine flexible, T-SQL-basierte Abfrage über Daten hinweg, die in einer Vielzahl von Cloud- und SaaS-Speichersystemen gespeichert sind, wodurch die Notwendigkeit einer vorherigen Datenaufnahme oder -transformation vor der Analyse eliminiert wird.

Relevante OPENROWSET-Optionen

Verwenden Sie Optionen, OPENROWSET um das zugrunde liegende Dateiformat zu beschreiben und zu steuern, wie die Daten analysiert werden. Diese Einstellungen sind besonders wichtig für durch Trennzeichen getrennte Textdateien, bei denen Sie definieren müssen, wie Zeilen und Spalten voneinander getrennt sind.

Zu den gängigen Optionen gehören:

  • FIELDTERMINATOR, ROWTERMINATORund FIELDQUOTE - Geben Sie die Zeichen an, die Felder und Zeilen trennen. Diese Optionen stellen eine genaue Analyse von Spalten und Datensätzen sicher. Sie behandeln auch Zitatwerte in durch Trennzeichen begrenzten Dateien, um Text mit Trennzeichen beizubehalten.
  • HEADER_ROW und FIRSTROW - Geben Sie an, ob die Datei eine Kopfzeile enthält, und definieren Sie, welche Zeile als erste Datenzeile behandelt werden soll.
  • CODEPAGE - Legen Sie die Zeichencodierung so fest, dass Sonderzeichen und Nicht-ASCII-Text ordnungsgemäß interpretiert werden.

Diese Optionen bieten Flexibilität beim Arbeiten mit verschiedenen Dateiformaten und stellen sicher, dass Daten unabhängig von den Unterschieden in der Struktur oder Codierung korrekt gelesen werden.

Mit der optionalen WITH Klausel in OPENROWSET können Sie ein explizites Schema für die Daten definieren. Die Bereitstellung eines Schemas ist nützlich, wenn Sie das Schema nicht automatisch aus den zugrunde liegenden Dateien ableiten möchten OPENROWSET . Verwenden Sie die WITH Klausel, um die automatische Schemaeinleitung außer Kraft zu setzen und genau zu definieren, wie die Daten in tabellarische Form projiziert werden sollen.

Anwendungsfälle für OPENROWSET

Um eine nahtlose direkte Abfrageerfahrung für Daten bereitzustellen, die in einem Data Lake gespeichert sind, verwenden Fabric Data Warehouse und den SQL-Analyseendpunkt die OPENROWSET-Funktion , um auf Dateien zu verweisen und deren Inhalte zu lesen.

Die OPENROWSET Funktion bietet umfangreiche Funktionen zum Abfragen von Dateien, einschließlich:

Abfragen von Parquet-Dateien mit OPENROWSET

Parquet ist ein spaltenbasiertes Dateiformat, das für analyseorientierte Workloads optimiert ist. Es speichert Daten nach Spalten und nicht nach Zeilen, was eine effiziente Komprimierung, reduzierte E/A und eine schnellere Abfrageleistung ermöglicht, insbesondere beim Abfragen einer Teilmenge von Spalten.

Die OPENROWSET Funktion ermöglicht einfachen und intuitiven Zugriff auf Parkettdateien direkt aus T-SQL-Code.

Um eine Parkettdatei abzufragen, geben Sie die URL zur Parkettdatei mithilfe der OPENROWSET Funktion an:

SELECT * FROM
OPENROWSET( BULK 'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet');

Die OPENROWSET Funktion gibt jede Zeile aus der Parkettdatei als Zeile im Resultset zurück. Die Spalten im Resultset entsprechen dem Schema der Parquet-Datei.

Verwendungsbeispiele finden Sie unter Parquet-Dateien abfragen.

Abfragen getrennter Dateien mit OPENROWSET

Ein durch Trennzeichen getrenntes Textformat ist ein textbasiertes, zeilenorientiertes Dateiformat, das häufig für den Datenaustausch und die einfache Datenspeicherung verwendet wird.

  • Daten werden in Zeilen organisiert, die durch einen Zeilenterminator getrennt sind.
  • Mehrere Zellen in jeder Zeile werden durch ein Feldtrennzeichen getrennt.
  • Werte in den Zellen werden durch Trennzeichen wie Kommas oder Tabstopps getrennt.

Durch Trennzeichen getrennte Dateien werden häufig unterstützt und können einfach über viele Systeme und Tools hinweg hergestellt werden.

Das am häufigsten verwendete trennzeichentrennte Format ist das CSV-Format (kommagetrennte Werte). In einer CSV werden die Zeilen durch eine neue Zeile und Werte durch Kommas optional in doppelte Anführungszeichen eingeschlossen. Es gibt jedoch viele Variationen, z. B. Tabulatortrennwerte (TSV) und andere benutzerdefinierte, durch Trennzeichen abgegrenzte Formate.

Mithilfe von OPENROWSET" können Sie direkt von T-SQL aus auf einfache und flexible Weise auf getrennte Dateien zugreifen. Dieser Ansatz ermöglicht die direkte Abfrage, ohne dass zuerst Daten in Datenbanktabellen geladen werden müssen.

Um eine durch Trennzeichen getrennte Datei abzufragen, geben Sie die Datei-URL an, und definieren Sie die entsprechenden Analyseoptionen bei Verwendung der OPENROWSET Funktion:

SELECT * FROM
OPENROWSET( BULK '/Files/mysubfolder/data.csv');

Die OPENROWSET Funktion gibt jede Zeile aus der durch Trennzeichen getrennten Datei als Zeile im Resultset zurück. Die Spalten im Resultset folgen der Struktur der durch Trennzeichen getrennten Datei.

Sie können anpassen, wie durch Trennzeichen getrennte Textdateien (z. B. CSV, TSV oder andere Varianten) analysiert werden, indem Sie Optionen wie Feldterminatoren, Zeilenterminatoren, Escapezeichen und andere formatbezogene Einstellungen angeben, die mit der Dateistruktur übereinstimmen.

Verwendungsbeispiele finden Sie unter durch Abfrage getrennte Textdateien.

Abfragen von JSONL-Dateien mit OPENROWSET

JSON Lines (JSONL) ist ein durch Zeilen getrenntes, semistrukturiertes Dateiformat, in dem jede Zeile ein gültiges JSON-Objekt enthält. Diese Struktur eignet sich besonders gut für Streaming-, Ereignisdaten- und Append-only-Workloads, da neue Datensätze effizient geschrieben werden können, ohne die gesamte Datei neu zu schreiben.

Mithilfe der OPENROWSET Funktion können Sie JSONL-Dateien direkt aus T-SQL abfragen. Sie können Streaming- und kontinuierlich generierte Daten direkt analysieren, ohne diese zuvor in Datenbanktabellen aufnehmen zu müssen.

Um eine JSONL-Datei abzufragen, geben Sie die Datei-URL bei Verwendung der OPENROWSET Funktion an:

SELECT * FROM
OPENROWSET( BULK '/mysubfolder/data.jsonl', DATA_SOURCE='MyStorage');

Beim Abfragen von JSON Lines (JSONL)-Dateien wird jedes JSON-Objekt in der Datei als separate Zeile im Resultset behandelt.

Jede Eigenschaft innerhalb des JSON-Objekts wird als einzelne Spalte zurückgegeben und ermöglicht eine natürliche relationale Ansicht von zeilentrennten JSON-Daten.

Verwendungsbeispiele finden Sie unter JSONL-Abfragedateien.

Schema-on-read (Schema wird beim Lesen festgelegt)

Fabric Data Warehouse ermöglicht SQL-Entwicklern das Anwenden eines Schemas zur Abfragezeit, wenn Daten direkt aus dateien gelesen werden, die in einem Data Lake gespeichert sind.

Mit diesem Schema-on-Read-Ansatz können Daten im ursprünglichen Format verbleiben, während ihre Struktur während der Abfrage dynamisch definiert wird. Sie können zwischen zwei Schema-on-Read-Modellen wählen:

  • Automatische Schema-Ableitung, in der Fabric den Dateiinhalt analysiert und automatisch Spaltennamen und Datentypen bestimmt.
  • Explizite Schemadefinition, bei der das Schema in der Abfrage vollständig definiert ist, um Spaltennamen und Datentypen zu steuern.

Automatischer Schemarückschluss

Mit der automatischen Schemaausleitung können Sie Dateien abfragen, ohne eine explizite Schemadefinition bereitzustellen.

Fabric Data Warehouse und der SQL-Analyseendpunkt untersuchen automatisch die Quelldateien, um Spaltennamen und Datentypen zu identifizieren. Sie verwenden Metadaten auf Dateiebene, z. B. Parquet-Datei-Header, oder analysieren repräsentative Datenproben für Formate wie CSV und JSONL.

Wenn Sie die Klausel aus der WITHOPENROWSET Anweisung weglassen, analysiert Fabric Data Warehouse automatisch die zugrunde liegenden Dateien und leitet die Spaltennamen und Datentypen zur Abfragezeit ab.

SELECT * FROM
OPENROWSET( BULK 'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet');

Dieser Ansatz eignet sich besonders für schnelle Untersuchung, sich entwickelnde Schemas oder Szenarien, in denen die Dateistruktur außerhalb des Data Warehouse verwaltet wird. Indem Sie das Schema dynamisch ableiten, können Sie sich auf die Abfrage der Daten konzentrieren, ohne zuerst ein festes Schema zu definieren oder beizubehalten.

Explizite Schemadefinition

Mit einer expliziten Schemadefinition steuern SQL-Entwickler, wie Dateidaten relationalen Spalten zugeordnet werden, indem Spaltennamen, Datentypen und ggf. Spaltenpositionen in den Quelldateien angegeben werden.

Dieser Ansatz bietet eine präzise und vorhersagbare Zuordnung beim Abfragen von Dateien mithilfe von OPENROWSET.

Um das Schema zu definieren, fügen Sie Ihrer WITH Anweisung eine optionale OPENROWSET Klausel hinzu.

SELECT * FROM
OPENROWSET( BULK '/Files/mysubfolder/data.parquet') 
WITH (
      Column1 int, 
      Column2 varchar(20),
      Column3 varchar(max)
);

Verwenden Sie die explizite Schemadefinition, wenn Sie strenge Kontrolle über die Spaltenzuordnung und Datentypen benötigen oder wenn Sie mit sich entwickelnden oder lose strukturierten Quelldateien arbeiten.

Abfragen mehrerer Dateien oder Ordner mit OPENROWSET

Wenn Sie Daten über mehrere Dateien oder Ordner hinweg abfragen möchten, geben Sie einen Dateipfad an, der ein oder mehrere Wildcardzeichen (*) enthält.

Mithilfe von Wildcards kann eine einzelne T-SQL-Abfrage über einen dynamischen Satz von Dateien ausgeführt werden, die einem Benennungs- oder Verzeichnismuster entsprechen.

Die folgenden Regeln gelten für die Verwendung von Wildcards in Dateipfaden:

  • Der * Wildcard stellt ein oder mehrere Zeichen dar, und Sie können ihn in Verzeichnispfaden sowie in Dateinamen verwenden.
  • Sie können mehrere * Wildcards innerhalb desselben Pfads oder Dateinamens angeben, um komplexe Muster abzugleichen.
  • Wenn der Pfad mit rekursiven Wildcards endet (z. B. /**), enthält das Abfrageergebnis alle Dateien, die sich unter dem angegebenen Stammordner und seinen Unterordnern befinden.

Im folgenden Beispiel wird veranschaulicht, wie Sie im Dateipfad Wildcardmuster verwenden, um in einer Abfrage ordnerübergreifend mehrere Parquet-Dateien abzufragen.

SELECT * FROM
OPENROWSET( BULK '/myroot/*/mysubfolder/*.parquet', DATA_SOURCE='MyStorage');

Datei-Metadatenfunktionen

OPENROWSET enthält Metadatenfunktionen, mit denen Sie auf Informationen zu den Dateien zugreifen können, die Sie abfragen:

  • Die filename() Funktion gibt den Namen der Datei zurück, aus der jede Zeile stammt. Verwenden Sie diese Funktion, um bestimmte Dateien zu filtern oder abzufragen. Um eine bessere Leistung zu erzielen, wandeln Sie das Ergebnis in einen geeigneten Datentyp und eine entsprechende Länge um.
  • Die filepath() Funktion gibt den Dateipfad zurück, aus dem jede Zeile stammt.
    • Ohne Parameter wird der vollständige Dateipfad/-URI zurückgegeben.
    • Gibt mit einem Parameter das Pfadsegment zurück, das der angegebenen Platzhalterposition entspricht.

Im folgenden Beispiel wird eine Abfrage veranschaulicht, die Dateiinhalte zusammen mit dem vollständigen URI und dateinamen für jede Datei abruft und nur Dateien aus dem /year=2025/month=10 Ordner zurückgibt.

SELECT 
 rows.filepath(),
 rows.filename(),
 rows.filepath(2) AS [month],
 rows.*
FROM 
OPENROWSET(
  BULK 'https://myaccount.dfs.core.windows.net/myroot/year=2025/month=*/*.parquet'
  ) AS rows
WHERE rows.filepath(1) = '2025'

Weitere Informationen zu filepath() und filename(), finden Sie unter Dateimetadatenfunktionen.

Verwandte Inhalte

  • Last updated on