Tutorial: Untersuchen und Analysieren von Data Lakes mit einem serverlosen SQL-Pool
In diesem Tutorial erfahren Sie, wie Sie eine explorative Datenanalyse durchführen. Sie kombinieren verschiedene Azure Open Datasets-Instanzen über einen serverlosen SQL-Pool. Anschließend visualisieren Sie die Ergebnisse in Synapse Studio für Azure Synapse Analytics.
Die OPENROWSET(BULK...)
-Funktion ermöglicht den Zugriff auf Dateien in Azure Storage. Die [OPENROWSET](develop-openrowset.md)
-Funktion liest den Inhalt einer Remotedatenquelle (z. B. einer Datei) und gibt den Inhalt als eine Reihe von Zeilen zurück.
Automatischer Schemarückschluss
Da die Daten im Parquet-Dateiformat vorliegen, steht der automatische Schemarückschluss zur Verfügung. Die Daten können somit abgefragt werden, ohne die Datentypen aller Spalten in den Dateien aufzulisten. Außerdem können Sie den Mechanismus für virtuelle Spalten sowie die filepath
-Funktion verwenden, um eine Teilmenge der Dateien herauszufiltern.
Hinweis
Die Standardsortierung ist SQL_Latin1_General_CP1_CI_ASIf
. Bei einer nicht standardmäßigen Sortierung berücksichtigen Sie die Groß-/Kleinschreibung.
Wenn Sie eine Datenbank mit einer Sortierung erstellen, bei der die Groß-/Kleinschreibung beachtet wird, sollten Sie beim Angeben von Spalten sicherstellen, dass Sie den richtigen Namen der Spalte verwenden.
Der Spaltenname tpepPickupDateTime
wäre korrekt, während tpeppickupdatetime
in einer nicht standardmäßigen Sortierung nicht funktionieren würde.
In diesem Tutorial wird ein Dataset über New York City (NYC) Taxi verwendet:
- Datums- und Uhrzeitangaben für Fahrgastaufnahme und Fahrtende
- Orte für Fahrgastaufnahme und Fahrtende
- Fahrtentfernungen
- Einzelfahrpreise
- Tarifarten
- Zahlungsarten
- Vom Fahrer gemeldete Fahrgastzahlen
Führen Sie zunächst die folgende Abfrage aus, um sich mit den Daten von NYC Taxi vertraut zu machen:
SELECT TOP 100 * FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
FORMAT='PARQUET'
) AS [nyc]
Analog dazu können Sie mithilfe der folgenden Abfrage das Dataset für gesetzliche Feiertage abfragen:
SELECT TOP 100 * FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
FORMAT='PARQUET'
) AS [holidays]
Mithilfe der folgenden Abfrage können Sie auch das Dataset mit den Wetterdaten abfragen:
SELECT
TOP 100 *
FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
FORMAT='PARQUET'
) AS [weather]
Weitere Informationen zur Bedeutung der einzelnen Spalten finden Sie in den Beschreibungen der Datasets:
Zeitreihen, Saisonalität und Ausreißeranalyse
Mit der folgenden Abfrage können Sie ganz einfach die jährliche Anzahl von Taxifahrten zusammenfassen:
SELECT
YEAR(tpepPickupDateTime) AS current_year,
COUNT(*) AS rides_per_year
FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
FORMAT='PARQUET'
) AS [nyc]
WHERE nyc.filepath(1) >= '2009' AND nyc.filepath(1) <= '2019'
GROUP BY YEAR(tpepPickupDateTime)
ORDER BY 1 ASC
Der folgende Ausschnitt zeigt das Ergebnis für die jährliche Anzahl von Taxifahrten:
Die Daten können in Synapse Studio visualisiert werden. Wechseln Sie hierzu von der Tabellenansicht zur Diagrammansicht. Sie können zwischen verschiedenen Diagrammtypen (Flächendiagramm, Balkendiagramm, Säulendiagramm, Liniendiagramm, Kreisdiagramm oder Punktdiagramm) wählen. Erstellen Sie in diesem Fall ein Säulendiagramm, und legen Sie die Kategoriespalte auf current_year fest:
In dieser Visualisierung können Sie sehen, dass die Anzahl der Fahrten im Lauf der Jahre zurückgegangen ist. Dieser Rückgang ist vermutlich auf die zunehmende Beliebtheit von Carsharing-Unternehmen zurückzuführen.
Hinweis
Zum Zeitpunkt der Erstellung dieses Tutorials liegen für 2019 nur unvollständige Daten vor. Dies hat einen signifikanten Rückgang bei der Anzahl von Fahrten für dieses Jahr zur Folge.
Als Nächstes konzentrieren wir uns bei der Analyse auf ein einzelnes Jahr, beispielsweise 2016. Von der folgenden Abfrage wird die tägliche Anzahl von Fahrten in diesem Jahr zurückgegeben:
SELECT
CAST([tpepPickupDateTime] AS DATE) AS [current_day],
COUNT(*) as rides_per_day
FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
FORMAT='PARQUET'
) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
ORDER BY 1 ASC
Der folgende Ausschnitt zeigt das Ergebnis für diese Abfrage:
Auch diese Daten können ganz einfach in einem Säulendiagramm visualisiert werden, indem die Spalte Kategorie auf current_day und die Spalte Legende (Reihen) auf rides_per_day festgelegt wird.
Im ausgegebenen Diagramm ist zu sehen, dass es ein wöchentliches Muster gibt, wobei der jeweilige Samstag der Spitzentag ist. In den Sommermonaten werden urlaubsbedingt weniger Taxifahrten durchgeführt. Sie können außerdem einige signifikante Einbrüche bei der Anzahl von Taxifahrten sehen, ohne dass ein klares Muster für den Zeitpunkt und den Grund dieser Rückgänge erkennbar wäre.
Daher soll im nächsten Schritt geprüft werden, ob der Einbruch der Zahlen mit gesetzlichen Feiertagen korreliert. Um sehen zu können, ob es eine Korrelation gibt, wird das Dataset der NYC-Taxifahrten mit dem Dataset der gesetzlichen Feiertage verknüpft:
WITH taxi_rides AS (
SELECT
CAST([tpepPickupDateTime] AS DATE) AS [current_day],
COUNT(*) as rides_per_day
FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
FORMAT='PARQUET'
) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
),
public_holidays AS (
SELECT
holidayname as holiday,
date
FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
FORMAT='PARQUET'
) AS [holidays]
WHERE countryorregion = 'United States' AND YEAR(date) = 2016
),
joined_data AS (
SELECT
*
FROM taxi_rides t
LEFT OUTER JOIN public_holidays p on t.current_day = p.date
)
SELECT
*,
holiday_rides =
CASE
WHEN holiday is null THEN 0
WHEN holiday is not null THEN rides_per_day
END
FROM joined_data
ORDER BY current_day ASC
Heben Sie die Anzahl der Taxifahrten an gesetzlichen Feiertagen hervor. Zu diesem Zweck wählen Sie current_day für die Spalte Kategorie und rides_per_day und holiday_rides als die Spalte Legende (Reihen) aus.
Wie Sie dem Diagramm entnehmen können, ist die Anzahl von Taxifahrten an gesetzlichen Feiertagen geringer. Es gibt jedoch immer noch einen deutlichen Einbruch am 23. Januar, der dadurch nicht zu erklären ist. Daher fragen wir als Nächstes das Dataset mit den Wetterdaten ab, um das Wetter in NYC an diesem Tag zu überprüfen:
SELECT
AVG(windspeed) AS avg_windspeed,
MIN(windspeed) AS min_windspeed,
MAX(windspeed) AS max_windspeed,
AVG(temperature) AS avg_temperature,
MIN(temperature) AS min_temperature,
MAX(temperature) AS max_temperature,
AVG(sealvlpressure) AS avg_sealvlpressure,
MIN(sealvlpressure) AS min_sealvlpressure,
MAX(sealvlpressure) AS max_sealvlpressure,
AVG(precipdepth) AS avg_precipdepth,
MIN(precipdepth) AS min_precipdepth,
MAX(precipdepth) AS max_precipdepth,
AVG(snowdepth) AS avg_snowdepth,
MIN(snowdepth) AS min_snowdepth,
MAX(snowdepth) AS max_snowdepth
FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
FORMAT='PARQUET'
) AS [weather]
WHERE countryorregion = 'US' AND CAST([datetime] AS DATE) = '2016-01-23' AND stationname = 'JOHN F KENNEDY INTERNATIONAL AIRPORT'
Die Ergebnisse der Abfrage zeigen, dass der Einbruch bei der Anzahl von Taxifahren auf Folgendes zurückzuführen ist:
- An diesem Tag gab es in NYC einen Schneesturm mit starkem Schneefall (~ 30 cm).
- Es war kalt (Temperatur unter Null Grad Celsius).
- Es war windig (~ 10 m/s).
Dieses Tutorial hat gezeigt, wie ein Datenanalyst schnell eine explorative Datenanalyse durchführen kann. Sie können verschiedene Datasets kombinieren, indem Sie einen serverlosen SQL-Pool verwenden und die Ergebnisse mithilfe von Azure Synapse Studio visualisieren.
Nächste Schritte
Im Tutorial Verwenden eines serverlosen SQL-Pools mit Power BI Desktop und Erstellen eines Berichts erfahren Sie, wie Sie einen serverlosen SQL-Pool mit Power BI Desktop verbinden und Berichte erstellen.
Informationen dazu, wie externe Tabellen in einem serverlosen SQL-Pool verwendet werden, finden Sie unter Verwenden externer Tabellen mit Synapse SQL.
Feedback
https://aka.ms/ContentUserFeedback.
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Tickets als Feedbackmechanismus für Inhalte auslaufen lassen und es durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter:Einreichen und Feedback anzeigen für