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 przy użyciu istniejących otwartych zestawów danych bez wymaganej konfiguracji magazynu. Połączysz różne zestawy danych Azure Open Datasets przy użyciu bezserwerowej puli SQL. Następnie zwizualizuj wyniki w programie Synapse Studio dla usługi Azure Synapse Analytics.

W tym samouczku zostały wykonane następujące czynności:

  • Uzyskiwanie dostępu do wbudowanej bezserwerowej puli SQL
  • Uzyskiwanie dostępu do zestawów danych usługi Azure Open w celu korzystania z danych samouczka
  • Wykonywanie podstawowej analizy danych przy użyciu języka SQL

Uzyskiwanie dostępu do bezserwerowej puli SQL

Każdy obszar roboczy zawiera wstępnie skonfigurowaną bezserwerową pulę SQL do użycia o nazwie Wbudowane. Aby uzyskać do niego dostęp:

  1. Otwórz obszar roboczy i wybierz centrum Programowanie .
  2. + Wybierz przycisk Dodaj nowy zasób".
  3. Wybierz pozycję Skrypt SQL.

Ten skrypt umożliwia eksplorowanie danych bez konieczności zarezerwowania pojemności SQL.

Jeśli nie masz subskrypcji platformy Azure, przed rozpoczęciem utwórz bezpłatne konto.

Uzyskiwanie dostępu do danych samouczka

Wszystkie dane używane w tym samouczku są przechowywane na koncie magazynu azureopendatastorage, w którym przechowywane są zestawy danych Usługi Azure Open do użytku w samouczkach, takich jak ten. Wszystkie skrypty można uruchamiać bezpośrednio z obszaru roboczego, o ile obszar roboczy może uzyskać dostęp do sieci publicznej.

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

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

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.

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]

Inne dostępne zestawy danych

Podobnie możesz wykonać zapytanie dotyczące zestawu danych Dni wolne od pracy, korzystając z 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:

Automatyczne wnioskowanie schematu

Ponieważ dane są przechowywane w formacie pliku Parquet, automatyczne wnioskowanie schematu jest dostępne. 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 kolumn wirtualnych 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ę ważność wielkości liter.

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

Nazwa tpepPickupDateTime kolumny będzie poprawna, chociaż tpeppickupdatetime nie będzie działać w sortowaniu nie domyślnym.

Szeregi czasowe, sezonowość i analiza odstający

Możesz podsumować roczną liczbę przejazdów taksówką, korzystając z 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 programie Synapse Studio, przełączając się z tabeli na widok Wykres . Możesz wybrać spośród różnych typów wykresów, takich jak Obszar, Słupek, Kolumna, Linia, Kołowe i Punktowe. W takim przypadku wykreślij wykres kolumnowy z kolumną Category ustawioną na current_year:

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

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

Uwaga

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

Możesz skupić się na analizie jednego 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 z 2016 roku.

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

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

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

Następnie sprawdź, czy spadek liczby przejazdów koreluje z świętami publicznymi. Sprawdź, czy istnieje korelacja, dołączając do zestawu danych NYC Taxi rides z zestawem danych Święta Publicznego:

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ę wyników N Y C Taxi rides i Public Holidays zestawów danych.

Wyróżnij liczbę przejazdów taksówką podczas świąt publicznych. W tym celu wybierz current_day dla kolumny 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.

Na wykresie widać, że w świętach 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 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'

Zrzut ekranu przedstawia wizualizację wyników zestawu danych weather data.

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

  • Tego dnia w Nowym Jorku był 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 usługi Azure Synapse Studio.

Powiązana zawartość

Aby dowiedzieć się, jak połączyć bezserwerową pulę SQL z programem Power BI Desktop i tworzyć raporty, zobacz Łączenie bezserwerowej puli SQL z programem 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