Dela via

Självstudie: Utforska och analysera datasjöar med en serverlös SQL-pool

  • Artikel

I den här självstudien får du lära dig hur du utför undersökande dataanalys. Du kombinerar olika Azure Open Datasets med hjälp av en serverlös SQL-pool. Sedan visualiserar du resultatet i Synapse Studio för Azure Synapse Analytics.

Med OPENROWSET(BULK...) funktionen kan du komma åt filer i Azure Storage. [OPENROWSET](develop-openrowset.md) läser innehållet i en fjärrdatakälla, till exempel en fil, och returnerar innehållet som en uppsättning rader.

Automatisk schemainferens

Eftersom data lagras i Parquet-filformatet är automatisk schemainferens tillgänglig. Du kan köra frågor mot data utan att ange datatyperna för alla kolumner i filerna. Du kan också använda mekanismen filepath för virtuella kolumner och funktionen för att filtrera bort en viss delmängd av filer.

Anteckning

Standardsortering är SQL_Latin1_General_CP1_CI_ASIf. För en icke-standardsortering bör du ta hänsyn till skiftlägeskänslighet.

Om du skapar en databas med skiftlägeskänslig sortering när du anger kolumner ska du se till att använda rätt namn på kolumnen.

Ett kolumnnamn tpepPickupDateTime skulle vara korrekt medan tpeppickupdatetime det inte skulle fungera i en sortering som inte är standard.

I den här självstudien används en datauppsättning om New York City (NYC) Taxi:

  • Datum och tider för upphämtning och avlämning
  • Platser för upphämtning och avlämning
  • Reseavstånd
  • Specificerade priser
  • Frekvenstyper
  • Betalningstyper
  • Antal passagerare som rapporterats av föraren

Om du vill bekanta dig med NYC Taxi-data kör du följande fråga:

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

På samma sätt kan du köra frågor mot datauppsättningen för helgdagar med hjälp av följande fråga:

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

Du kan också fråga datamängden Väderdata med hjälp av följande fråga:

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

Du kan lära dig mer om innebörden av de enskilda kolumnerna i beskrivningarna av datauppsättningarna:

Tidsserier, säsongsvariationer och avvikande analys

Du kan sammanfatta det årliga antalet taxiresor med hjälp av följande fråga:

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

Följande kodfragment visar resultatet för det årliga antalet taxiresor:

Skärmbild som visar en tabell med årligt antal taxiresor.

Data kan visualiseras i Synapse Studio genom att växla från tabellen till diagramvyn. Du kan välja bland olika diagramtyper, till exempel Område, Stapel, Kolumn, Linje, Cirkel och Punkt. I det här fallet ritar du stapeldiagrammet med kolumnen Kategori inställd på current_year:

Skärmbild som visar ett stapeldiagram som visar turer per år.

Från den här visualiseringen kan du se en trend med att minska antalet turer genom åren. Förmodligen beror denna minskning på den senaste tidens ökade popularitet hos samåkningsföretag.

Anteckning

När den här självstudien skrivs är data för 2019 ofullständiga. Som ett resultat finns det en enorm minskning av antalet åkattraktioner för det året.

Du kan fokusera analysen på ett enda år, till exempel 2016. Följande fråga returnerar det dagliga antalet turer under det året:

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

Följande kodfragment visar resultatet för den här frågan:

Skärmbild som visar en tabell över det dagliga antalet turer för 2016 års resultat.

Återigen kan du visualisera data genom att rita kolumndiagrammet med kolumnen Kategori inställd på current_day och kolumnen Förklaring (serie) inställd på rides_per_day.

Skärmbild som visar ett stapeldiagram som visar det dagliga antalet turer för 2016.

I diagrammet kan du se att det finns ett veckomönster med lördagar som toppdag. Under sommarmånaderna är det färre taxiresor på grund av semester. Observera också några betydande droppar i antalet taxiresor utan ett tydligt mönster av när och varför de inträffar.

Därefter ser du om minskningen av åkattraktioner korrelerar med helgdagar. Kontrollera om det finns en korrelation genom att ansluta datauppsättningen nyc taxi rides med datauppsättningen Helgdagar:

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

Skärmbild som visar en tabell med N Y C-taxiresor och datauppsättningar för helgdagar.

Markera antalet taxiresor under helgdagar. För det ändamålet väljer du current_day för kolumnen Kategori och rides_per_day och holiday_rides som kolumnerna Förklaring (serie).

Skärmbild som visar antalet taxiresor under helgdagar som ett diagram.

I diagrammet kan du se att antalet taxiresor är lägre under helgdagarna. Det finns fortfarande en oförklarlig stor nedgång den 23 januari. Nu ska vi kontrollera vädret i NYC den dagen genom att fråga datauppsättningen Väderdata:

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'

Skärmbild som visar en visualisering av resultat för datamängden Väderdata.

Resultatet av frågan visar att minskningen av antalet taxiresor inträffade eftersom:

  • Det var en snöstorm den dagen i NYC med tung snö (~ 30 cm).
  • Det var kallt (temperaturen var under noll grader Celsius).
  • Det var blåsigt (~10 m/s).

Den här självstudien har visat hur en dataanalytiker snabbt kan utföra undersökande dataanalys. Du kan kombinera olika datauppsättningar med hjälp av en serverlös SQL-pool och visualisera resultatet med hjälp av Azure Synapse Studio.

Nästa steg

Information om hur du ansluter serverlös SQL-pool för att Power BI Desktop och skapa rapporter finns i Ansluta serverlös SQL-pool för att Power BI Desktop och skapa rapporter.

Information om hur du använder externa tabeller i en serverlös SQL-pool finns i Använda externa tabeller med Synapse SQL