教學課程：使用無伺服器 SQL 集區探索和分析 Data Lake

發行項
06/01/2023

在本教學課程中，您將了解如何執行探勘資料分析。您會使用無伺服器 SQL 集區合併不同的 Azure 開放資料集。接著，您會在 Azure Synapse Analytics 的 Synapse Studio 中將結果視覺化。

OPENROWSET(BULK...) 函式可讓您存取 Azure 儲存體中的檔案。 [OPENROWSET](develop-openrowset.md) 讀取遠端資料來源 (例如檔案) 的內容，並以一組資料列傳回內容。

自動推斷結構描述

因為資料是以 Parquet 檔案格式儲存，所以可以使用自動架構推斷。您可以查詢資料，而不需要列出檔案中所有資料行的資料類型。您也可以使用虛擬資料行機制和 filepath 函式來篩選出特定的檔案子集。

注意

預設定序為 SQL_Latin1_General_CP1_CI_ASIf。若為非預設定序，請考慮區分大小寫。

如果您建立具有區分大小寫定序的資料庫，則當您指定資料行時，請務必使用正確的資料行名稱。

資料行名稱 tpepPickupDateTime 正確，但 tpeppickupdatetime 不會在非預設定序中運作。

本教學課程使用紐約市 (NYC) 計程車的相關資料集：

上下車日期和時間
上下車地點
行車距離
車資明細
費率類型
付款類型
司機報告的乘客人數

若要熟悉 NYC 計程車資料，請執行下列查詢：

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

同樣地，您也可以使用下列查詢來查詢國定假日資料集：

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

您也可以使用下列查詢來查詢天氣資料資料集：

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

您可以在下列資料集的說明中深入了解個別資料行的意義：

時間序列、季節性和極端值分析

您可以使用下列查詢來摘要列出每年的計程車乘車數量：

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

下列程式碼片段顯示每年計程車乘車數量的結果：

此螢幕擷取畫面顯示每年計程車乘車數量的表格。

您可以在 Synapse Studio 中從 [資料表] 檢視切換至 [圖表] 檢視，以視覺方式呈現資料。您可以選擇不同的圖表類型，例如區域圖、橫條圖、直條圖、折線圖、圓形圖 和散佈圖。在本案例中，我們要將 [類別] 資料行設定為 [current_year] 的情況下繪製直條圖：

此螢幕擷取畫面顯示每天乘車數量的資料行圖表。

在這個視覺效果中，您可以看到過去幾年的搭乘次數呈現下降趨勢。這項減少的原因是，分享公司最近愈來愈受到歡迎。

注意

在撰寫本教學課程時，2019 年的資料不完整。如此一來，這一年的乘車次數會大幅下降。

您可以將分析重點放在單一年度，例如 2016 年。下列查詢會傳回該年度內的每日乘車數量：

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

下列程式碼片段顯示此查詢的結果：

此螢幕擷取畫面顯示 2016 年每日乘車數量的表格。

同樣地，您可以在 [類別] 資料行設定為 [current_day] 且 [圖例 (數列)] 資料行設定為 [rides_per_day] 的情況下繪製直條圖，用視覺方式呈現資料。

此螢幕擷取畫面顯示 2016 年每天乘車數量的資料行圖表。

從繪圖圖表中，您可以看到有每週模式，而星期六為尖峰日。由於夏季月份期間屬於假期，所以計程車乘車數量較少。此外，您會看到有一些地方出現計程車乘車數量大幅下滑的情形，但其發生時間和原因則沒有明顯模式。

接下來，了解搭乘次數的下降是否與國定假日有所關聯。將 NYC 計程車搭乘資料集與國定假日資料集聯結起來，以了解這兩者是否有所關聯：

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 計程車乘車數量與國定假日資料集結果。

將國定假日期間的計程車乘車次數醒目提示。基於這個目的，選擇 [current_day] 來作為 [類別] 資料行，並選擇 [rides_per_day] 和 [holiday_rides] 作為 [圖例 (數列)] 資料行。

此螢幕擷取畫面顯示國定假日期間的計程車乘車次數圖表。

從繪圖中可以發現，國定假日期間有一些計程車乘車數量較低。這仍無法有效解釋 1 月 23 日的大幅下滑。讓我們查詢天氣資料資料集來查看 NYC 當天的天氣：

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'

此螢幕擷取畫面顯示氣象資料資料集結果視覺效果。

查詢的結果指出，計程車的乘車次數發生下滑是因為：

NYC 當天有暴風雪，降雪量很大 (將近 30 公分)。
天氣很冷 (溫度低於攝氏零度)。
當天風大 (風速大約 10 公尺/秒)。

本教學課程說明資料分析人員可以快速執行探勘資料分析。您可以使用無伺服器 SQL 集區來合併不同的資料集，並使用 Azure Synapse Studio 將結果視覺化。

後續步驟

若要了解如何將無伺服器 SQL 集區連線到 Power BI Desktop 並建立報告，請參閱將無伺服器 SQL 集區連線到 Power BI Desktop 並建立報告。

若要了解如何使用無伺服器 SQL 集區中的外部資料表，請參閱搭配 Synapse SQL 使用外部資料表

教學課程：使用無伺服器 SQL 集區探索和分析 Data Lake

自動推斷結構描述

時間序列、季節性和極端值分析

後續步驟

其他資源