教學課程:使用無伺服器 SQL 集區探索和分析 Data Lake
在本教學課程中,您將了解如何執行探勘資料分析。 您會使用無伺服器 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
下列程式碼片段顯示此查詢的結果:
同樣地,您可以在 [類別] 資料行設定為 [current_day] 且 [圖例 (數列)] 資料行設定為 [rides_per_day] 的情況下繪製直條圖,用視覺方式呈現資料。
從繪圖圖表中,您可以看到有每週模式,而星期六為尖峰日。 由於夏季月份期間屬於假期,所以計程車乘車數量較少。 此外,您會看到有一些地方出現計程車乘車數量大幅下滑的情形,但其發生時間和原因則沒有明顯模式。
接下來,了解搭乘次數的下降是否與國定假日有所關聯。 將 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
將國定假日期間的計程車乘車次數醒目提示。 基於這個目的,選擇 [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 使用外部資料表