Kurz: Zkoumání a analýza datových jezer s využitím bezserverového fondu SQL

V tomto kurzu se naučíte provádět průzkumnou analýzu dat. Různé datové sady Azure Open Datasets můžete kombinovat pomocí bezserverového fondu SQL. Výsledky pak vizualizujete v Synapse Studio pro Azure Synapse Analytics.

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

Automatické odvození schématu

Vzhledem k tomu, že se data ukládají ve formátu souboru Parquet, je k dispozici automatické odvození schématu. Na data se můžete dotazovat, aniž byste museli vypisovat datové typy 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 rozlišování velkých a malých písmen.

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

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

Tento kurz používá datovou sadu týkající se newyorské taxislužby:

• Data a časy vyzvednutí a vyzvednutí
• Místa vyzvednutí a vyzvednutí
• Vzdálenosti jízdy
• Ceny jízdného podle položek
• Typy sazeb
• Typy plateb
• Počty cestujících hlášené řidičem

Pokud se chcete seznámit s daty newyordické taxislužby, 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]

Podobně můžete pomocí následujícího dotazu zadat dotaz na datovou sadu Svátky:

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 popisu datových sad:

• New York Taxi
• Svátcích
• Data o počasí

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

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

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:

Data lze vizualizovat v Synapse Studio přepnutím ze zobrazení Tabulka do zobrazení Graf. Můžete si vybrat z různých typů grafů, například Plošný, Pruhový, Sloupcový, Spojnicový, Výsečový a Bodový. V tomto případě vykreslujte sloupcový graf se sloupcem Category nastaveným na current_year:

Na této vizualizaci vidíte trend klesajících počtů jízd v průběhu let. Tento pokles je pravděpodobně způsoben nedávným zvýšením oblíbenosti společností zabývajících se sdílením jízd.

Poznámka

V době psaní tohoto kurzu jsou data pro verzi 2019 neúplná. Výsledkem je, že v daném roce došlo k obrovskému poklesu počtu jízd.

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

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 tohoto dotazu:

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

V grafu vidíte týdenní vzor se sobotou jako špičkou. V letních měsících je kvůli dovolené méně jízd taxíkem. Všimněte si také některých významných poklesů počtu jízd taxíkem, aniž by bylo jasné, kdy a proč k nim dochází.

Dále se podívejte, jestli pokles jízd koreluje se svátky. Zkontrolujte, jestli existuje korelace, a to tak, že datovou sadu Jízdy taxislužby v New Yorku spojíte 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

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

Z grafu vidíte, že během svátků je počet jízd taxíkem nižší. 23. ledna je stále jeden nevysvětlený velký pokles. Pojďme se podívat na počasí v New Yorku v daný den dotazem na datovou sadu 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'

Výsledky dotazu ukazují, že k poklesu počtu jízd taxíkem došlo z následujících důvodů:

• V ten den v New Yorku byla vánice s těžkým sněhem (cca 30 cm).
• Byla zima (teplota byla pod nulou stupňů Celsia).
• Bylo větro (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.

Další kroky

Informace o připojení bezserverového fondu SQL k Power BI Desktop a vytváření sestav najdete v tématu Připojení bezserverového fondu SQL k Power BI Desktop 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 Synapse SQL.