Tutorial: Menjelajahi dan Menganalisis data lake dengan kumpulan SQL tanpa server

Dalam tutorial ini, Anda akan mempelajari cara melakukan analisis data eksploratif. Anda akan menggabungkan berbagai Azure Open Datasets menggunakan kumpulan SQL tanpa server. Kemudian, Anda akan memvisualisasikan hasilnya di Synapse Studio untuk Azure Synapse Analytics.

Fungsi OPENROWSET(BULK...) memungkinkan Anda mengakses file di Azure Storage. Fungsi OPENROWSET membaca konten sumber data jauh (misalnya file) dan menampilkan konten sebagai rangkaian baris.

Inferensi skema otomatis

Karena data disimpan dalam format file Parquet, inferensi skema otomatis tersedia. Anda bisa dengan mudah mengajukan kueri data tanpa mencantumkan jenis data semua kolom dalam file. Anda juga dapat menggunakan mekanisme kolom virtual dan fungsi filepath untuk memfilter subset file tertentu.

Catatan

Jika menggunakan database dengan kolase non-default (ini adalah kolase default SQL_Latin1_General_CP1_CI_AS), Anda harus mempertimbangkan kepekaan huruf besar/kecil akun.

Jika Anda membuat database dengan kolase peka huruf besar/kecil, saat menentukan kolom, pastikan untuk menggunakan nama kolom yang benar.

Contoh nama kolom 'tpepPickupDateTime' bisa jadi benar sedangkan 'tpeppickupdatetime' tidak akan berfungsi di kolase non-default.

Mari kita pahami terlebih dahulu data Taksi NYC dengan menjalankan kueri berikut:

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

Himpunan data Taksi New York City (NYC) meliputi:

  • Tanggal dan waktu penjemputan dan pengantaran.
  • Lokasi penjemputan dan pengantaran.
  • Jarak perjalanan.
  • Rincian tarif.
  • Jenis tarif.
  • Jenis pembayaran.
  • Jumlah penumpang yang dilaporkan pengemudi.

Demikian pula, Anda dapat mengajukan kueri himpunan data Hari Libur Nasional dengan menggunakan kueri berikut:

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

Terakhir, Anda dapat mengajukan kueri himpunan data terkait Data Cuaca dengan menggunakan kueri berikut:

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

Anda bisa mempelajari lebih lanjut arti masing-masing kolom dalam deskripsi himpunan data:

Deret waktu, musiman, dan analisis anomali

Anda dapat dengan mudah meringkas jumlah tumpangan taksi tahunan menggunakan kueri berikut:

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

Cuplikan berikut menunjukkan hasil jumlah tumpangan taksi tahunan:

Yearly number of taxi rides result snippet

Data dapat divisualisasikan di Synapse Studio dengan beralih dari tampilan Tabel ke Bagan. Anda dapat memilih di antara jenis bagan yang berbeda, seperti Area, Batang, Kolom, Garis, Pai, dan Tebar. Dalam hal ini, buat plot bagan Kolom dengan kolom Kategori diatur ke current_year:

Column chart showing rides per year

Dari visualisasi ini, terlihat tren penurunan jumlah kendaraan dari tahun ke tahun. Penurunan ini kemungkinan disebabkan oleh meningkatnya popularitas perusahaan penyedia layanan berbagi tumpangan baru-baru ini.

Catatan

Pada saat tutorial ini sedang ditulis, data untuk tahun 2019 belum lengkap. Akibatnya, terjadi penurunan besar dalam jumlah tumpangan untuk tahun tersebut.

Selanjutnya, mari kita memfokuskan analisis pada satu tahun, misalnya, 2016. Kueri berikut menampilkan jumlah tumpangan harian selama tahun tersebut:

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

Cuplikan berikut ini memperlihatkan hasil untuk kueri ini:

Daily number of rides for 2016 result snippet

Sekali lagi, Anda dapat dengan mudah memvisualisasikan data dengan membuat plot bagan Kolom dengan kolom Kategori diatur ke current_day dan kolom Legenda (seri) diatur ke rides_per_day.

Column chart showing daily number of rides for 2016

Dari bagan plot, Anda dapat melihat ada pola mingguan, dengan hari Sabtu sebagai hari puncak. Selama bulan musim panas, ada lebih sedikit tumpangan taksi dikarenakan liburan. Selain itu, perhatikan beberapa penurunan signifikan jumlah tumpangan taksi tanpa pola yang jelas terkait kapan dan mengapa penurunan tersebut terjadi.

Selanjutnya, mari kita lihat apakah penurunan jumlah tumpangan berkorelasi dengan hari libur nasional. Kita dapat melihat apakah ada korelasi dengan menggabungkan himpunan data tumpangan Taksi NYC dengan himpunan data Hari Libur Nasional:

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

NYC Taxi rides and Public Holidays datasets result visualization

Kali ini, kami ingin menyoroti jumlah tumpangan taksi selama hari libur nasional. Untuk tujuan tersebut, kami memilih current_day untuk kolom Kategori dan rides_per_day dan holiday_rides sebagai kolom Legenda (seri).

Number of taxi rides during public holidays plot chart

Dari bagan plot, Anda dapat melihat bahwa selama hari libur nasional, jumlah tumpangan taksi lebih rendah. Masih ada satu penurunan besar yang tidak dapat dijelaskan pada 23 Januari. Mari kita periksa cuaca di NYC pada hari itu dengan mengajukan kueri himpunan data Data Cuaca:

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'

Weather Data dataset result visualization

Hasil kueri menunjukkan bahwa penurunan jumlah tumpangan taksi terjadi karena:

  • Terjadi badai salju pada hari itu di NYC dengan salju tebal (~ 30 cm).
  • Dingin (suhu di bawah nol derajat Celsius).
  • Berangin (~ 10 m/d).

Tutorial ini telah menunjukkan bagaimana analis data dapat dengan cepat melakukan analisis data eksploratif, menggabungkan himpunan data yang berbeda dengan mudah menggunakan kumpulan SQL tanpa server, dan memvisualisasikan hasilnya menggunakan Azure Synapse Studio.

Langkah berikutnya

Untuk mempelajari cara menyambungkan kumpulan SQL tanpa server ke Power BI Desktop dan membuat laporan, lihat Menyambungkan kumpulan SQL tanpa server ke Power BI Desktop dan membuat laporan.

Untuk mempelajari cara menggunakan tabel Eksternal di kumpulan SQL tanpa server, lihat Menggunakan tabel eksternal dengan Synapse SQL