Zelfstudie: Data lakes verkennen en analyseren met serverloze SQL-pool

  • Artikel

In deze zelfstudie leert u hoe u een verkennende gegevensanalyse uitvoert. U combineert verschillende Azure Open Datasets met behulp van een serverloze SQL-pool. Vervolgens visualiseert u de resultaten in Synapse Studio voor Azure Synapse Analytics.

Met de functie OPENROWSET(BULK...) kunt u toegang krijgen tot bestanden in Azure Storage. [OPENROWSET](develop-openrowset.md) leest inhoud van een externe gegevensbron, zoals een bestand, en retourneert de inhoud als een set rijen.

Automatische schemadeductie

Omdat de gegevens zijn opgeslagen in de Parquet-bestandsindeling, is automatische schemadeductie beschikbaar. U kunt een query uitvoeren op de gegevens zonder de gegevenstypen van alle kolommen in de bestanden te vermelden. U kunt ook het mechanisme voor virtuele kolommen en de filepath functie gebruiken om een bepaalde subset van bestanden te filteren.

Notitie

De standaardsortering is SQL_Latin1_General_CP1_CI_ASIf. Voor een niet-standaardsortering moet u rekening houden met hoofdlettergevoeligheid.

Als u een database met hoofdlettergevoelige sortering maakt wanneer u kolommen opgeeft, moet u ervoor zorgen dat u de juiste naam van de kolom gebruikt.

Een kolomnaam tpepPickupDateTime zou correct zijn, terwijl tpeppickupdatetime deze niet werkt in een niet-standaardsortering.

In deze zelfstudie wordt gebruikgemaakt van een gegevensset over taxi's in New York City (NYC):

  • Datums en tijden voor ophalen en afzetten
  • Ophaal- en afleverlocaties
  • Reisafstanden
  • Geitemiseerde tarieven
  • Tarieftypen
  • Betalingstypen
  • Door de bestuurder gerapporteerde passagiersaantallen

Voer de volgende query uit om vertrouwd te raken met de nyc Taxi-gegevens:

SELECT TOP 100 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]

Op dezelfde manier kunt u een query uitvoeren op de gegevensset Openbare feestdagen, met behulp van de volgende query:

SELECT TOP 100 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
        FORMAT='PARQUET'
    ) AS [holidays]

U kunt ook een query uitvoeren op de gegevensset Weer met behulp van de volgende query:

SELECT
    TOP 100 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]

Meer informatie over de betekenis van de afzonderlijke kolommen vindt u in de beschrijvingen van de gegevenssets:

Analyse van tijdreeks, seizoensgebondenheid en uitbijter

U kunt het jaarlijkse aantal taxiritten samenvatten met behulp van de volgende query:

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

Het volgende fragment toont het resultaat voor het jaarlijkse aantal taxiritten:

Schermopname van een tabel met het jaarlijkse aantal taxiritten.

De gegevens kunnen worden gevisualiseerd in Synapse Studio door over te schakelen van de weergave Tabel naar de weergave Grafiek. U kunt kiezen uit verschillende grafiektypen, zoals Gebied, Staaf, Kolom, Lijn, Cirkel en Spreiding. In dit geval tekent u het Kolomdiagram met de kolom Categorie ingesteld op current_year:

Schermopname van een kolomdiagram met ritten per jaar.

In deze visualisatie ziet u een trend van dalende ritnummers in de loop der jaren. Deze afname is waarschijnlijk te wijten aan de recent toegenomen populariteit van bedrijven die gedeelde ritten aanbieden.

Notitie

Op het moment dat deze zelfstudie wordt geschreven, zijn de gegevens voor 2019 nog incompleet. Als gevolg hiervan is een enorme daling zichtbaar in het aantal ritten voor dat jaar.

U kunt de analyse richten op één jaar, bijvoorbeeld 2016. De volgende query retourneert het dagelijks aantal ritten gedurende dat jaar:

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

Het volgende fragment toont het resultaat voor deze query:

Schermopname van een tabel met het dagelijkse aantal ritten voor het resultaat van 2016.

U kunt gegevens ook visualiseren door het kolomdiagram uit te zetten met de kolom Categorie ingesteld op current_day en de kolom Legenda (reeks) op rides_per_day.

Schermopname van een kolomdiagram met het dagelijkse aantal ritten voor 2016.

In het getekende diagram kunt u zien dat er sprake is van een wekelijks patroon, met zaterdagen als piekdag. Tijdens de zomermaanden zijn er minder taxiritten vanwege vakanties. U ziet ook aanzienlijke dalingen in het aantal taxiritten zonder duidelijk patroon wanneer en waarom ze optreden.

Kijk vervolgens of de daling in ritten correleert met openbare feestdagen. Controleer of er een correlatie is door de gegevensset NYC Taxi rides te koppelen aan de gegevensset Openbare feestdagen:

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

Schermopname van een tabel met N Y C Taxi ritten en openbare feestdagen gegevenssets resultaat.

Markeer het aantal taxiritten tijdens feestdagen. Kies hiervoor current_day voor de kolom Categorie en rides_per_day en holiday_rides als de kolommen Legenda (reeks).

Schermopname van het aantal taxiritten tijdens openbare feestdagen als een plotgrafiek.

In het getekende diagram kunt u zien dat het aantal taxiritten tijdens openbare feestdagen kleiner is. Er is nog steeds één onverklaarde grote daling op 23 januari. Laten we het weer in NYC op deze dag bekijken door een query uit te voeren op de gegevensset Weer:

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'

Schermopname van een visualisatie van het resultaat van de gegevensset Weergegevens.

De resultaten van de query geven aan dat de daling van het aantal taxiritten plaatsvond vanwege het volgende:

  • Er was die dag een storm in NYC, met zware sneeuwval (ongeveer 30 cm).
  • Het was koud (temperatuur onder nul graden Celsius).
  • Het waaide (~ 10 m/s).

Deze zelfstudie heeft laten zien hoe een gegevensanalist snel verkennende gegevensanalyse kan uitvoeren. U kunt verschillende gegevenssets combineren met behulp van een serverloze SQL-pool en de resultaten visualiseren met behulp van Azure Synapse Studio.

Volgende stappen

Raadpleeg Een serverloze SQL-pool verbinden met Power BI Desktop en rapporten maken voor meer informatie over het verbinden van een serverloze SQL-pool met Power BI Desktop en het maken van rapporten.

Zie Externe tabellen gebruiken met Synapse SQL voor meer informatie over het gebruik van externe tabellen in een serverloze SQL-pool