Öğretici: Sunucusuz SQL havuzu ile veri göllerini keşfetme ve analiz etme

Bu öğreticide keşif veri analizi gerçekleştirmeyi öğreneceksiniz. Sunucusuz SQL havuzunu kullanarak farklı Azure Açık Veri Kümelerini birleştirirsiniz. Ardından sonuçları Azure Synapse Analytics için Synapse Studio görselleştirirsiniz.

işlevi, OPENROWSET(BULK...) Azure Depolama'daki dosyalara erişmenizi sağlar. [OPENROWSET](develop-openrowset.md) dosya gibi uzak bir veri kaynağının içeriğini okur ve içeriği bir satır kümesi olarak döndürür.

Otomatik şema çıkarımı

Veriler Parquet dosya biçiminde depolandığından otomatik şema çıkarımı kullanılabilir. Dosyalardaki tüm sütunların veri türlerini listelemeden verileri sorgulayabilirsiniz. Ayrıca sanal sütun mekanizmasını ve filepath işlevini kullanarak belirli bir dosya alt kümesini filtreleyebilirsiniz.

Not

Varsayılan harmanlama şeklindedir SQL_Latin1_General_CP1_CI_ASIf. Varsayılan olmayan harmanlama için büyük/küçük harf duyarlılığını dikkate alın.

Sütunları belirtirken büyük/küçük harfe duyarlı harmanlama içeren bir veritabanı oluşturursanız sütunun doğru adını kullandığınızdan emin olun.

Sütun adı tpepPickupDateTime doğru olsa da tpeppickupdatetime varsayılan olmayan harmanlamada çalışmaz.

Bu öğreticide New York City (NYC) Taxi hakkında bir veri kümesi kullanılır:

• Teslim ve bırakma tarihleri ve saatleri
• Teslim alma ve bırakma konumları
• Seyahat mesafeleri
• Öğeli tarifeler
• Fiyat türleri
• Ödeme türleri
• Sürücü tarafından bildirilen yolcu sayısı

NYC Taxi verilerini tanımak için aşağıdaki sorguyu çalıştırın:

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

Benzer şekilde, aşağıdaki sorguyu kullanarak Resmi Tatiller veri kümesini sorgulayabilirsiniz:

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

Aşağıdaki sorguyu kullanarak Hava Durumu Verileri veri kümesini de sorgulayabilirsiniz:

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

Veri kümelerinin açıklamalarında tek tek sütunların anlamı hakkında daha fazla bilgi edinebilirsiniz:

• NYC Taksi
• Resmi Tatiller
• Hava Durumu Verileri

Zaman serisi, mevsimsellik ve aykırı değerler analizi

Aşağıdaki sorguyu kullanarak taksi yolculuklarının yıllık sayısını özetleyebilirsiniz:

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

Aşağıdaki kod parçacığı, yıllık taksi yolculuğu sayısının sonucunu gösterir:

Veriler, Tablo görünümünden Grafik görünümüne geçirilerek Synapse Studio görselleştirilebilir. Alan, Çubuk, Sütun, Çizgi, Pasta ve Dağılım gibi farklı grafik türleri arasından seçim yapabilirsiniz. Bu durumda, Kategori sütununun current_year olarak ayarlandığı Sütun grafiğini çizin:

Bu görselleştirmede, yıllar içinde sürüş sayılarının azalma eğilimini görebilirsiniz. Muhtemelen, bu düşüş sürüş paylaşım şirketlerinin son zamanlarda artan popülerliğinden kaynaklanır.

Not

Bu öğretici yazılırken 2019 verileri eksiktir. Sonuç olarak, o yılki yolculuk sayısında büyük bir düşüş var.

Analizi tek bir yıla, örneğin 2016'ya odaklayabilirsiniz. Aşağıdaki sorgu, o yıldaki günlük sürüş sayısını döndürür:

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

Aşağıdaki kod parçacığı bu sorgunun sonucunu gösterir:

Yine, Kategori sütununun current_day ve Gösterge (seri) sütununun rides_per_day olarak ayarlandığı Sütun grafiğini çizerek verileri görselleştirebilirsiniz.

Çizim grafiğinde, Cumartesi günleri yoğun gün olarak olmak üzere haftalık bir desen olduğunu görebilirsiniz. Yaz aylarında, tatiller nedeniyle daha az taksi yolculuğu vardır. Ayrıca, ne zaman ve neden ortaya çıktığına dair net bir desen olmadan taksi yolculuklarının sayısında bazı önemli düşüşlere dikkat edin.

Ardından, yolculuklardaki düşüşün resmi tatillerle bağıntılı olup olmadığını görün. NYC Taxi rides veri kümesine Resmi Tatiller veri kümesiyle katılarak bir bağıntı olup olmadığını denetleyin:

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

Resmi tatillerde taksi yolculuğu sayısını vurgulayın. Bu amaçla Kategori sütunu için current_day seçin ve Gösterge(seri) sütunları olarak rides_per_day ve holiday_rides.

Çizim grafiğinde, resmi tatillerde taksi yolculuğu sayısının daha az olduğunu görebilirsiniz. 23 Ocak'ta hala açıklanamayan büyük bir düşüş var. O gün Hava Durumu Verileri veri kümesini sorgulayarak NYC'deki hava durumunu denetleyelim:

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'

Sorgunun sonuçları, taksi yolculuğu sayısındaki düşüşün şu nedenlerle gerçekleştiğini gösterir:

• O gün NYC'de yoğun karla (~30 cm) bir kar fırtınası vardı.
• Hava soğuktu (sıcaklık sıfır santigrat derecenin altındaydı).
• Rüzgarlı (~10 m/sn).

Bu öğreticide veri analistlerinin keşif veri analizini nasıl hızlı bir şekilde gerçekleştirebileceği gösterilmiştir. Sunucusuz SQL havuzunu kullanarak farklı veri kümelerini birleştirebilir ve Azure Synapse Studio kullanarak sonuçları görselleştirebilirsiniz.

Sonraki adımlar

Sunucusuz SQL havuzunu Power BI Desktop bağlamayı ve rapor oluşturmayı öğrenmek için bkz. Sunucusuz SQL havuzunu Power BI Desktop ve rapor oluşturmak için bağlama.

Sunucusuz SQL havuzunda Dış tabloları kullanmayı öğrenmek için bkz. Synapse SQL ile dış tabloları kullanma