Udostępnij za pośrednictwem

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

  • Artykuł

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.

Uwaga

Sortowanie domyślne to SQL_Latin1_General_CP1_CI_ASIf. W przypadku sortowania innego niż domyślne należy wziąć pod uwagę wielkość liter.

Jeśli podczas określania kolumn tworzysz bazę danych z sortowaniem uwzględniającym wielkość liter, pamiętaj, aby użyć poprawnej nazwy kolumny.

Nazwa tpepPickupDateTime kolumny byłaby poprawna, ale tpeppickupdatetime nie działałaby w sortowaniu nie domyślnym.

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:

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

Zrzut ekranu przedstawia tabelę 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:

Zrzut ekranu przedstawia wykres kolumnowy, który wyświetla przejazdy na rok.

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.

Uwaga

W momencie pisania tego samouczka dane na rok 2019 są niekompletne. W rezultacie nastąpił ogromny spadek liczby przejazdów w tym roku.

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:

Zrzut ekranu przedstawia tabelę dziennej liczby przejazdów dla wyniku 2016.

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.

Zrzut ekranu przedstawia wykres kolumnowy, który wyświetla dzienną liczbę przejazdów w 2016 roku.

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

Zrzut ekranu przedstawia tabelę zestawów danych N Y C Taxi i Zestawy danych Świąt Publicznych.

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

Zrzut ekranu przedstawia liczbę przejazdów taksówką podczas świąt publicznych jako wykres wykresu wykresu.

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'

Zrzut ekranu przedstawiający wizualizację wyniku zestawu danych danych o pogodzie.

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