Bagikan melalui

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

  • Artikel

Dalam tutorial ini, Anda mempelajari cara melakukan analisis data eksploratif menggunakan himpunan data terbuka yang ada, tanpa memerlukan penyiapan penyimpanan. Menggabungkan berbagai Azure Open Datasets menggunakan kumpulan SQL tanpa server. Kemudian, memvisualisasikan hasilnya di Synapse Studio untuk Azure Synapse Analytics.

Di tutorial ini, Anda akan:

  • Mengakses kumpulan SQL tanpa server bawaan
  • Mengakses Azure Open Datasets untuk menggunakan data tutorial
  • Melakukan analisis data dasar menggunakan SQL

Mengakses kumpulan SQL tanpa server

Setiap ruang kerja dilengkapi dengan kumpulan SQL tanpa server yang telah dikonfigurasi sebelumnya untuk Anda gunakan yang disebut Bawaan. Untuk mengaksesnya:

  1. Buka ruang kerja Anda dan pilih hub Kembangkan .
  2. Pilih tombol + Tambahkan sumber daya baru.'
  3. Pilih skrip SQL.

Anda dapat menggunakan skrip ini untuk menjelajahi data Anda tanpa harus memesan kapasitas SQL.

Jika Anda tidak memiliki langganan Azure, buat akun gratis sebelum Anda memulai.

Mengakses data tutorial

Semua data yang kami gunakan dalam tutorial ini ditempatkan di akun penyimpanan azureopendatastorage, yang menyimpan Azure Open Datasets untuk penggunaan terbuka dalam tutorial seperti ini. Anda dapat menjalankan semua skrip apa adanya langsung dari ruang kerja Anda selama ruang kerja Anda dapat mengakses jaringan publik.

Tutorial ini menggunakan himpunan data tentang Taksi New York City (NYC):

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

Fungsi OPENROWSET(BULK...) memungkinkan Anda mengakses file di Azure Storage. [OPENROWSET](develop-openrowset.md) membaca konten sumber data jauh, seperti file, dan mengembalikan konten tersebut sebagai set baris.

Untuk memahami data Taksi NYC, jalankan 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 lain yang dapat diakses

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]

Anda juga 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:

Inferensi skema otomatis

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

Catatan

Kolase defaultnya adalah SQL_Latin1_General_CP1_CI_ASIf. Untuk kolase non-default, pertimbangkan sensitivitas huruf besar/besar.

Jika membuat database dengan kolasi yang peka huruf besar/kecil, pastikan untuk menggunakan nama kolom yang benar pada kolom.

Nama kolom tpepPickupDateTime akan menjadi nama yang benar sementara tpeppickupdatetime tidak berfungsi dalam kolase non-default.

Deret waktu, musiman, dan analisis anomali

Anda dapat 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:

Cuplikan layar memperlihatkan tabel jumlah tumpangan taksi tahunan.

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:

Cuplikan layar memperlihatkan bagan kolom yang menampilkan tumpangan per tahun.

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.

Fokus ke analisis untuk 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:

Cuplikan layar memperlihatkan tabel jumlah tumpangan harian untuk hasil 2016.

Sekali lagi, data dapat divisualisasikan dengan membuat bagan Kolom dengan kolom Kategori yang diatur ke terkini dan kolom Legenda (seri) diatur ke rides_per_day.

Cuplikan layar memperlihatkan bagan kolom yang menampilkan jumlah tumpangan harian untuk 2016.

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

Selanjutnya, lihat apakah penurunan jumlah tumpangan berhubungan dengan hari libur nasional. Periksa apakah ada korelasi dari 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

Cuplikan layar memperlihatkan tabel tunggangan Taksi N Y C dan hasil himpunan data Hari Libur Umum.

Sorot jumlah tumpangan taksi selama hari libur nasional. Untuk itu, pilih terkini untuk kolom Kategori dan rides_per_day dan holiday_rides sebagai kolom Legenda (seri).

Cuplikan layar memperlihatkan jumlah tumpangan taksi selama hari libur nasional sebagai bagan plot.

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'

Cuplikan layar memperlihatkan visualisasi hasil himpunan data Data Cuaca.

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 cara analis data dapat melakukan analisis data eksplorasi dengan cepat. Anda dapat menggabungkan kumpulan data yang berbeda dengan menggunakan kumpulan SQL tanpa server dan memvisualisasikan hasilnya dengan menggunakan Azure Synapse Studio.

Konten terkait

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