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:
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.