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 med befintliga öppna datauppsättningar, utan att det krävs någon lagringskonfiguration. 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.

I den här kursen får du:

  • Få åtkomst till den inbyggda serverlösa SQL-poolen
  • Få åtkomst till Azure Open Datasets för att använda självstudiedata
  • Utföra grundläggande dataanalys med hjälp av SQL

Få åtkomst till den serverlösa SQL-poolen

Varje arbetsyta levereras med en förkonfigurerad serverlös SQL-pool som du kan använda med namnet Inbyggd. Så här kommer du åt den:

  1. Öppna din arbetsyta och välj Utveckla hubb.
  2. + Välj knappen Lägg till ny resurs.
  3. Välj SQL-skript.

Du kan använda det här skriptet för att utforska dina data utan att behöva reservera SQL-kapacitet.

Om du inte har någon Azure-prenumeration skapar du ett kostnadsfritt konto innan du börjar.

Få åtkomst till självstudiedata

Alla data som vi använder i den här självstudien finns i lagringskontot azureopendatastorage, som innehåller Azure Open Datasets för öppen användning i självstudier som den här. Du kan köra alla skript som de är direkt från din arbetsyta så länge arbetsytan har åtkomst till ett offentligt nätverk.

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
  • Upphämtnings- och avlämningsplatser
  • Reseavstånd
  • Specificerade priser
  • Frekvenstyper
  • Betalningstyper
  • Antal förarrapporterade passagerare

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.

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]

Andra tillgängliga datauppsättningar

På samma sätt kan du köra frågor mot datamängden 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å köra frågor mot 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:

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.

Kommentar

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

Om du skapar en databas med skiftlägeskänslig sortering när du anger kolumner ska du 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.

Tidsserier, säsongsvariationer och avvikande analyser

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 mellan 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 åkattraktioner per år.

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

Kommentar

I skrivande stund ä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 med 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.

Från 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 minskningar av antalet taxiresor utan ett tydligt mönster av när och varför de inträffar.

Se sedan 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 Taxi-turer 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 helgdagar. Det finns fortfarande en oförklarlig stor minskning 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 väderdatamängdsresultat.

Resultatet av frågan anger 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 resultaten med hjälp av Azure Synapse Studio.

Relaterat innehåll

Information om hur du ansluter en serverlös SQL-pool till Power BI Desktop och skapar rapporter finns i Ansluta serverlös SQL-pool till 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