Sdílet prostřednictvím

Kurz: Zkoumání a analýza datových jezer pomocí bezserverového fondu SQL

  • Článek

V tomto kurzu se dozvíte, jak provádět průzkumnou analýzu dat pomocí existujících otevřených datových sad bez nutnosti nastavení úložiště. Zkombinujete různé datové sady Azure Open Datasets pomocí bezserverového fondu SQL. Výsledky pak vizualizujete ve službě Synapse Studio pro Azure Synapse Analytics.

V tomto kurzu se naučíte:

  • Přístup k integrovanému bezserverovém fondu SQL
  • Přístup k datovým sadám Azure Open Datasets pro použití dat kurzu
  • Provádění základní analýzy dat pomocí SQL

Přístup k bezserverovém fondu SQL

Každý pracovní prostor má předkonfigurovaný bezserverový fond SQL, který můžete použít s názvem Předdefinovaný. Přístup k němu:

  1. Otevřete pracovní prostor a vyberte centrum Vývoj .
  2. + Vyberte tlačítko Přidat nový prostředek.
  3. Vyberte skript SQL.

Tento skript můžete použít k prozkoumání dat, aniž byste museli rezervovat kapacitu SQL.

Pokud ještě nemáte předplatné Azure, vytvořte si napřed bezplatný účet.

Přístup k datům kurzu

Všechna data, která v tomto kurzu používáme, jsou uložená v účtu úložiště azureopendatastorage, která obsahuje azure Open Datasets pro otevřené použití v kurzech, jako je tato. Všechny skripty můžete spustit přímo z pracovního prostoru, pokud má váš pracovní prostor přístup k veřejné síti.

Tento kurz používá datovou sadu o Taxi New Yorku (NYC):

  • Vyzvednutí a odkládací data a časy
  • Umístění vyzvednutí a odkládacích míst
  • Vzdálenosti jízdy
  • Položky jízdné
  • Typy sazeb
  • Typy plateb
  • Počty osob hlášených řidičem

Funkce OPENROWSET(BULK...) umožňuje přístup k souborům ve službě Azure Storage. [OPENROWSET](develop-openrowset.md) přečte obsah vzdáleného zdroje dat, například souboru, a vrátí obsah jako sadu řádků.

Pokud se chcete seznámit s daty taxislužby NYC, spusťte následující dotaz:

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

Další přístupné datové sady

Podobně můžete datovou sadu svátků dotazovat pomocí následujícího dotazu:

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

Datovou sadu weather data můžete také dotazovat pomocí následujícího dotazu:

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

Další informace o významu jednotlivých sloupců najdete v popisech datových sad:

Automatické odvození schématu

Vzhledem k tomu, že data jsou uložená ve formátu souboru Parquet, je k dispozici automatické odvozování schématu. Data můžete dotazovat bez výpisu datových typů všech sloupců v souborech. Pomocí mechanismu virtuálního sloupce a filepath funkce můžete také vyfiltrovat určitou podmnožinu souborů.

Poznámka:

Výchozí kolace je SQL_Latin1_General_CP1_CI_ASIf. U jiné než výchozí kolace vezměte v úvahu citlivost na malá a velká písmena.

Pokud při zadávání sloupců vytváříte databázi s rozlišováním velkých a malých písmen, nezapomeňte použít správný název sloupce.

Název tpepPickupDateTime sloupce by byl správný, i když tpeppickupdatetime by nefungovalo v jiné než výchozí kolaci.

Analýza časových řad, sezónnosti a odlehlé hodnoty

Roční počet jízd taxíkem můžete shrnout pomocí následujícího dotazu:

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

Následující fragment kódu ukazuje výsledek ročního počtu jízd taxíkem:

Snímek obrazovky ukazuje tabulku ročního počtu jízd taxíkem.

Data je možné vizualizovat v nástroji Synapse Studio přepnutím z tabulky do zobrazení grafů. Můžete si vybrat z různých typů grafů, jako je oblast, pruhový, sloupcový, spojnicový, výsečový a bodový. V tomto případě vykreslujte sloupcový graf se sloupcem Kategorie nastaveným na current_year:

Snímek obrazovky znázorňující sloupcový graf, který zobrazuje jízdy za rok

Z této vizualizace můžete vidět trend snížení počtu jízd v průběhu let. Pravděpodobně je to způsobeno nedávnou zvýšenou popularitou společností pro sdílení jízdy.

Poznámka:

V době psaní tohoto kurzu jsou data pro verzi 2019 neúplná. Výsledkem je obrovský pokles počtu jízd za daný rok.

Analýzu můžete zaměřit na jeden rok, například na rok 2016. Následující dotaz vrátí denní počet jízd během daného roku:

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

Následující fragment kódu ukazuje výsledek pro tento dotaz:

Snímek obrazovky ukazuje tabulku denního počtu jízd za výsledek roku 2016.

Data můžete znovu vizualizovat vykreslením sloupcového grafu se sloupcem Kategorie nastaveným na current_day a sloupcem Legenda (řady) nastaveným na rides_per_day.

Snímek obrazovky znázorňující sloupcový graf zobrazující denní počet jízd za 2016

Z grafu můžete vidět týdenní vzor, který má sobotu jako špičku. V letních měsících je kvůli dovolené méně jízd taxíkem. Všimněte si také, že některé významné poklesy počtu jízd taxíkem bez jasného vzoru kdy a proč se vyskytují.

Dále zjistěte, jestli pokles jízdy koreluje s veřejnými svátky. Zkontrolujte, jestli existuje korelace tím, že se připojíte k datové sadě jízdy taxi NYC s datovou sadou svátků:

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

Snímek obrazovky znázorňující tabulku výsledků datových sad N Y C Taxi a veřejných svátků

Zvýrazněte počet jízd taxíkem během svátků. Pro tento účel zvolte current_day pro sloupec Kategorie a rides_per_day a holiday_rides jako sloupce Legenda (řady).

Snímek obrazovky znázorňující počet jízd taxíkem během svátků jako graf

Z grafu můžete vidět, že během svátků je počet jízd taxíkem nižší. 23. ledna stále existuje jeden nevysvětlovaný velký pokles. Pojďme se podívat na počasí v NYC v daný den dotazováním datové sady Weather Data:

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'

Snímek obrazovky znázorňující vizualizaci výsledků datové sady počasí

Výsledky dotazu ukazují, že došlo k poklesu počtu jízd taxíkem, protože:

  • V NYC byl v ten den s těžkým sněhem (přibližně 30 cm).
  • Byla studená (teplota byla nižší než nula stupňů Celsia).
  • Byl větrný (přibližně 10 m/s).

Tento kurz ukázal, jak může datový analytik rychle provádět průzkumnou analýzu dat. Různé datové sady můžete kombinovat pomocí bezserverového fondu SQL a vizualizovat výsledky pomocí nástroje Azure Synapse Studio.

Související obsah

Informace o připojení bezserverového fondu SQL k Power BI Desktopu a vytváření sestav najdete v tématu Připojení bezserverového fondu SQL k Power BI Desktopu a vytváření sestav.

Informace o používání externích tabulek v bezserverovém fondu SQL najdete v tématu Použití externích tabulek se službou Synapse SQL.