Udostępnij za pośrednictwem

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

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

  • Uzyskiwanie dostępu do wbudowanej bezserwerowej puli SQL
  • Uzyskaj dostęp do zestawów danych Azure Open, aby korzystać z danych używanych w samouczkach.
  • 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 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 storage azureopendatastorage, które zawiera zestawy danych Azure Open Datasets do otwartego 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 wykorzystywany jest zestaw danych dotyczący taksówek w Nowym Jorku (NYC):

  • Daty i godziny odbioru i zwrotu
  • Lokalizacje odbioru i zwrotu
  • Odległości podróży
  • Wyszczególnione taryfy
  • 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ę rozróżnianie 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 wartości odstających

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 Wykresu. Możesz wybrać spośród różnych typów wykresów, takich jak Obszarowy, Słupkowy, Kolumnowy, Liniowy, Kołowy i Punktowy. W takim przypadku wykreśl wykres kolumnowy z kolumną Kategoria ustawioną na obecny_rok:

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

Z tej wizualizacji widać trend spadku liczby 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, łącząc zestaw danych przejazdów taksówek NYC z zestawem danych Święta Publiczne.

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 zestawów danych przejazdów taksówkami NYC i dni wolnych od pracy.

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

Zrzut ekranu przedstawia liczbę przejazdów taksówką podczas dni świątecznych jako wykres.

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