Abfragen von Ordnern und mehreren Dateien
In diesem Artikel erfahren Sie, wie Sie eine Abfrage mit einem serverlosen SQL-Pool in Azure Synapse Analytics schreiben.
Der serverlose SQL-Pool unterstützt das Lesen mehrerer Dateien oder Ordner durch die Verwendung von Platzhalterzeichen, die den in Windows-Betriebssystemen verwendeten Platzhalterzeichen ähnlich sind. Allerdings ist die Flexibilität größer, da mehrere Platzhalterzeichen erlaubt sind.
Voraussetzungen
Im ersten Schritt erstellen Sie eine Datenbank, in der Sie die Abfragen ausführen können. Initialisieren Sie dann die Objekte, indem Sie ein Setupskript für diese Datenbank ausführen. Dieses Setupskript erstellt die Datenquellen, die für die gesamte Datenbank gültigen Anmeldeinformationen und externe Dateiformate, die in diesen Beispielen verwendet werden.
Verwenden Sie den Ordner csv/taxi, um die Beispielabfragen nachzuverfolgen. Er enthält Daten der Fahrtenaufzeichnungen für „NYC Taxi – Yellow Taxi“ von Juli 2016 bis Juni 2018. Dateien im Ordner csv/taxi sind nach Jahr und Monat im folgenden Format benannt:
yellow_tripdata_<year>-<month>.csv*
Lesen aller Dateien im Ordner
Das folgende Beispiel liest alle Datendateien von „NYC Yellow Taxi“ aus dem Ordner csv/taxi und gibt die Gesamtzahl der Fahrgäste und Fahrten pro Jahr zurück. Außerdem wird die Verwendung von Aggregatfunktionen veranschaulicht.
SELECT
YEAR(pickup_datetime) as [year],
SUM(passenger_count) AS passengers_total,
COUNT(*) AS [rides_total]
FROM OPENROWSET(
BULK 'csv/taxi/*.csv',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
pickup_datetime DATETIME2 2,
passenger_count INT 4
) AS nyc
GROUP BY
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime);
Hinweis
Alle Dateien, auf die mit dem einzelnen OPENROWSET zugegriffen wird, müssen dieselbe Struktur aufweisen (Anzahl der Spalten und Datentypen).
Lesen einer Teilmenge der Dateien im Ordner
Das folgende Beispiel liest die Datendateien von „NYC Yellow Taxi“ für das Jahr 2017 aus dem Ordner csv/taxi unter Verwendung eines Platzhalterzeichens und gibt den Gesamtfahrpreis pro Zahlungsart zurück.
SELECT
payment_type,
SUM(fare_amount) AS fare_total
FROM OPENROWSET(
BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
payment_type INT 10,
fare_amount FLOAT 11
) AS nyc
GROUP BY payment_type
ORDER BY payment_type;
Hinweis
Alle Dateien, auf die mit dem einzelnen OPENROWSET zugegriffen wird, müssen dieselbe Struktur aufweisen (Anzahl der Spalten und Datentypen).
Lesen einer Teilmenge von Dateien im Ordner mithilfe mehrerer Dateipfade
Das folgende Beispiel liest die Datendateien von „NYC Yellow Taxi“ für das Jahr 2017 aus dem Ordner csv/taxi unter Verwendung von zwei Dateipfaden. Der erste verwendet den vollständigen Pfad zu der Datei, die Daten aus dem Monat Januar enthält, und der zweite verwendet ein Platzhalterzeichen, um die Monate Oktober, November und Dezember zu lesen. Für jeden Pfad wird der Gesamtfahrpreis pro Zahlungsart zurückgegeben.
SELECT
payment_type,
SUM(fare_amount) AS fare_total
FROM OPENROWSET(
BULK (
'csv/taxi/yellow_tripdata_2017-01.csv',
'csv/taxi/yellow_tripdata_2017-1*.csv'
),
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
payment_type INT 10,
fare_amount FLOAT 11
) AS nyc
GROUP BY payment_type
ORDER BY payment_type;
Hinweis
Alle Dateien, auf die mit dem einzelnen OPENROWSET zugegriffen wird, müssen dieselbe Struktur aufweisen (Anzahl der Spalten und Datentypen).
Lesen von Ordnern
Der Pfad, den Sie für OPENROWSET bereitstellen, kann auch ein Pfad zu einem Ordner sein. Diese Abfragetypen sind in den folgenden Abschnitten enthalten.
Lesen aller Dateien aus einem bestimmten Ordner
Sie können alle Dateien in einem Ordner lesen, indem Sie das Platzhalterzeichen auf Dateiebene verwenden, wie in Lesen aller Dateien im Ordner gezeigt. Es gibt jedoch eine Möglichkeit, einen Ordner abzufragen und alle Dateien in diesem Ordner zu verwenden.
Wenn der in OPENROWSET angegebene Pfad auf einen Ordner verweist, werden alle Dateien in diesem Ordner als Quelle für die Abfrage verwendet. Die folgende Abfrage liest alle Dateien im Ordner csv/taxi.
Hinweis
Beachten Sie die Angabe von / am Pfadende in der Abfrage. Es bezeichnet einen Ordner. Wenn / ausgelassen wird, zielt die Abfrage stattdessen auf eine Datei mit dem Namen taxi ab.
SELECT
YEAR(pickup_datetime) as [year],
SUM(passenger_count) AS passengers_total,
COUNT(*) AS [rides_total]
FROM OPENROWSET(
BULK 'csv/taxi/',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_datetime DATETIME2,
dropoff_datetime DATETIME2,
passenger_count INT,
trip_distance FLOAT,
rate_code INT,
store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_location_id INT,
dropoff_location_id INT,
payment_type INT,
fare_amount FLOAT,
extra FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
improvement_surcharge FLOAT,
total_amount FLOAT
) AS nyc
GROUP BY
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime);
Hinweis
Alle Dateien, auf die mit dem einzelnen OPENROWSET zugegriffen wird, müssen dieselbe Struktur aufweisen (Anzahl der Spalten und Datentypen).
Lesen aller Dateien aus mehreren Ordnern
Es ist möglich, Dateien aus mehreren Ordnern zu lesen, indem Sie ein Platzhalterzeichen verwenden. Die folgende Abfrage liest alle Dateien aus allen Ordnern, die sich im Ordner csv befinden und deren Namen mit t beginnen und mit i enden.
Hinweis
Beachten Sie die Angabe von / am Pfadende in der Abfrage. Es bezeichnet einen Ordner. Wenn / ausgelassen wird, zielt die Abfrage stattdessen auf Dateien mit dem Namen t*i ab.
SELECT
YEAR(pickup_datetime) as [year],
SUM(passenger_count) AS passengers_total,
COUNT(*) AS [rides_total]
FROM OPENROWSET(
BULK 'csv/t*i/',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_datetime DATETIME2,
dropoff_datetime DATETIME2,
passenger_count INT,
trip_distance FLOAT,
rate_code INT,
store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_location_id INT,
dropoff_location_id INT,
payment_type INT,
fare_amount FLOAT,
extra FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
improvement_surcharge FLOAT,
total_amount FLOAT
) AS nyc
GROUP BY
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime);
Hinweis
Alle Dateien, auf die mit dem einzelnen OPENROWSET zugegriffen wird, müssen dieselbe Struktur aufweisen (Anzahl der Spalten und Datentypen).
Da Sie nur über einen Ordner verfügen, der den Kriterien entspricht, ist das Abfrageergebnis dasselbe wie unter Lesen aller Dateien im Ordner.
Rekursives Durchsuchen von Ordnern
Der serverlose SQL-Pool kann Ordner rekursiv durchsuchen, wenn Sie /** am Ende des Pfads angeben. Die folgende Abfrage liest alle Dateien aus allen Ordnern und Unterordnern, die sich im Ordner csv/taxi befinden.
SELECT
YEAR(pickup_datetime) as [year],
SUM(passenger_count) AS passengers_total,
COUNT(*) AS [rides_total]
FROM OPENROWSET(
BULK 'csv/taxi/**',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_datetime DATETIME2,
dropoff_datetime DATETIME2,
passenger_count INT,
trip_distance FLOAT,
rate_code INT,
store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_location_id INT,
dropoff_location_id INT,
payment_type INT,
fare_amount FLOAT,
extra FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
improvement_surcharge FLOAT,
total_amount FLOAT
) AS nyc
GROUP BY
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime);
Hinweis
Alle Dateien, auf die mit dem einzelnen OPENROWSET zugegriffen wird, müssen dieselbe Struktur aufweisen (Anzahl der Spalten und Datentypen).
Verwenden mehrerer Platzhalter
Sie können mehrere Platzhalterzeichen auf verschiedenen Pfadebenen verwenden. Sie können z. B. eine vorherige Abfrage ergänzen, um Dateien nur mit Daten aus dem Jahr 2017 zu lesen, und zwar aus allen Ordnern, deren Namen mit t beginnen und mit i enden.
Hinweis
Beachten Sie die Angabe von / am Pfadende in der Abfrage. Es bezeichnet einen Ordner. Wenn / ausgelassen wird, zielt die Abfrage stattdessen auf Dateien mit dem Namen t*i ab.
Es gibt eine Obergrenze von zehn Platzhalterzeichen pro Abfrage.
SELECT
YEAR(pickup_datetime) as [year],
SUM(passenger_count) AS passengers_total,
COUNT(*) AS [rides_total]
FROM OPENROWSET(
BULK 'csv/t*i/yellow_tripdata_2017-*.csv',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_datetime DATETIME2,
dropoff_datetime DATETIME2,
passenger_count INT,
trip_distance FLOAT,
rate_code INT,
store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
pickup_location_id INT,
dropoff_location_id INT,
payment_type INT,
fare_amount FLOAT,
extra FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
improvement_surcharge FLOAT,
total_amount FLOAT
) AS nyc
GROUP BY
YEAR(pickup_datetime)
ORDER BY
YEAR(pickup_datetime);
Hinweis
Alle Dateien, auf die mit dem einzelnen OPENROWSET zugegriffen wird, müssen dieselbe Struktur aufweisen (Anzahl der Spalten und Datentypen).
Da Sie nur über einen Ordner verfügen, der den Kriterien entspricht, ist das Abfrageergebnis dasselbe wie unter Lesen einer Teilmenge von Dateien im Ordner und Lesen aller Dateien aus einem bestimmten Ordner. Komplexere Szenarien für die Verwendung von Platzhalterzeichen finden Sie unter Abfragen von Parquet-Dateien.