Samouczek: eksplorowanie i analizowanie magazynów danych za pomocą bezserwerowej puli SQL

Z tego samouczka dowiesz się, jak wykonywać eksploracyjne analizy danych. Różne zestawy danych open platformy Azure są łączone przy użyciu bezserwerowej puli SQL. Następnie zwizualizujesz wyniki w Synapse Studio dla usługi Azure Synapse Analytics.

Funkcja OPENROWSET(BULK...) umożliwia dostęp do plików w usłudze Azure Storage. [OPENROWSET](develop-openrowset.md) Odczytuje zawartość zdalnego źródła danych, takiego jak plik, i zwraca zawartość jako zestaw wierszy.

Automatyczne wnioskowanie schematu

Ponieważ dane są przechowywane w formacie pliku Parquet, dostępne jest automatyczne wnioskowanie schematu. Możesz wykonywać zapytania dotyczące danych bez wyświetlania listy typów danych wszystkich kolumn w plikach. Można również użyć mechanizmu kolumny wirtualnej i filepath funkcji, aby odfiltrować określony podzbiór plików.

W tym samouczku jest używany zestaw danych dotyczący nowojorskiej taksówki:

• Daty i godziny rozpoczęcia i zakończenia
• Lokalizacje odbioru i upuszczania
• Odległości podróży
• Taryfy z elementami
• Typy szybkości
• Typy płatności
• Liczba pasażerów zgłoszonych przez kierowcę

Aby zapoznać się z danymi nowojorskich taksówek, uruchom następujące zapytanie:

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

Podobnie można wykonać zapytanie dotyczące zestawu danych dni wolnych od pracy przy użyciu następującego zapytania:

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

Możesz również wykonać zapytanie dotyczące zestawu danych weather data przy użyciu następującego zapytania:

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

Więcej informacji na temat znaczenia poszczególnych kolumn można dowiedzieć się w opisach zestawów danych:

• Taksówka w Nowym Jorku
• Święta
• Dane o pogodzie

Szeregi czasowe, sezonowość i analiza odstających

Możesz podsumować roczną liczbę przejazdów taksówką przy użyciu następującego zapytania:

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

Poniższy fragment kodu przedstawia wynik rocznej liczby przejazdów taksówką:

Dane można wizualizować w Synapse Studio, przełączając się z tabeli do widoku Wykres. Możesz wybrać jeden z różnych typów wykresów, takich jak Obszar, Słupek, Kolumna, Linia, Kołowy i Punktowy. W takim przypadku wykreślij wykres kolumnowy z kolumną Category ustawioną na current_year:

Na podstawie tej wizualizacji widać trend malejących numerów przejazdów na przestrzeni lat. Prawdopodobnie spadek ten wynika z niedawnej zwiększonej popularności firm udostępniania przejazdów.

Analizę można skupić na jednym roku, na przykład 2016. Następujące zapytanie zwraca dzienną liczbę przejazdów w tym 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

Poniższy fragment kodu przedstawia wynik dla tego zapytania:

Ponownie możesz wizualizować dane, kreślijąc wykres kolumnowy z kolumną Category ustawioną na current_day , a kolumna Legenda (seria) ustawiona na rides_per_day.

Na wykresie wykresu widać, że istnieje wzorzec tygodniowy z sobotami jako dzień szczytu. W miesiącach letnich jest mniej przejazdów taksówką z powodu wakacji. Zauważ również, że niektóre znaczące spadki liczby przejazdów taksówką bez wyraźnego wzorca, kiedy i dlaczego występują.

Następnie sprawdź, czy spadek liczby przejazdów jest skorelowany z świętami publicznymi. Sprawdź, czy istnieje korelacja, dołączając do zestawu danych NYC Taxi rides (Przejazdy taksówkami w Nowym Jorku) przy użyciu zestawu danych Dni Wolnych Od pracy:

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

Wyróżnij liczbę przejazdów taksówką podczas świąt publicznych. W tym celu wybierz current_day dla kolumny Category (Kategoria) i rides_per_day i holiday_rides jako kolumny Legenda (seria).

Na wykresie wykresu widać, że podczas świąt publicznych liczba przejazdów taksówką jest niższa. Jest jeszcze jeden niewyjaśniony duży spadek 23 stycznia. Sprawdźmy pogodę w Nowym Jorku w tym dniu, wykonując zapytanie dotyczące zestawu danych danych o pogodzie:

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'

Wyniki zapytania wskazują, że spadek liczby przejazdów taksówką wystąpił, ponieważ:

• Tego dnia w Nowym Jorku była zamieć z ciężkim śniegiem (~30 cm).
• Było zimno (temperatura była poniżej zera stopni Celsjusza).
• To było wietrzne (~10 m/s).

W tym samouczku pokazano, jak analityk danych może szybko wykonywać eksploracyjne analizy danych. Różne zestawy danych można łączyć przy użyciu bezserwerowej puli SQL i wizualizować wyniki przy użyciu programu Azure Synapse Studio.

Następne kroki

Aby dowiedzieć się, jak połączyć bezserwerową pulę SQL z Power BI Desktop i tworzyć raporty, zobacz Łączenie bezserwerowej puli SQL z Power BI Desktop i tworzenie raportów.

Aby dowiedzieć się, jak używać tabel zewnętrznych w bezserwerowej puli SQL, zobacz Używanie tabel zewnętrznych z usługą Synapse SQL