教程：利用无服务器 SQL 池探索和分析数据湖

Artículo
08/14/2024

本教程介绍如何执行探索性数据分析。你将使用无服务器 SQL 池组合不同的 Azure 开放数据集。然后，在 Synapse Studio for Azure Synapse Analytics 中将结果可视化。

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

以下片段显示了每年出租车搭乘次数的结果：

Screenshot shows a table of yearly number of taxi rides.

在 Synapse Studio 中，可以通过从“表”视图切换到“图表”视图来可视化数据。可以在不同的图表类型（如面积图、条形图、柱形图、折线图、饼图和散点图）之间进行选择。在本例中，请在“类别”列设置为“current_year”的情况下绘制柱形图：

Screenshot shows a column chart that displays rides per 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

以下片段显示了此查询的结果：

Screenshot shows a table of the daily number of rides for 2016 result.

同样，你可以在“类别”列设置为“current_day”以及“图例（系列）”列设置为“rides_per_day”的情况下绘制柱形图，以将数据可视化。

Screenshot shows a column chart that displays the daily number of rides for 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

Screenshot shows a table of N Y C Taxi rides and Public Holidays datasets result.

突出显示公共节假日期间出租车搭乘次数。为此，请为“类别”列选择“current_day”，对“图例（系列）”列选择“rides_per_day”和“holiday_rides”。

Screenshot shows the number of taxi rides during public holidays as a plot chart.

从绘图图表中可以看出，在公共节假日期间，出租车搭乘次数较少。 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'

Screenshot shows a Weather Data dataset result visualization.

查询结果表明，以下原因导致出租车搭乘次数下降：

NYC 在那一天出现暴风雪天气，雪非常厚（约有 30 厘米）。
天气非常寒冷（温度低于零摄氏度）。
风非常大（风速大约 10 米/秒）。

本教程展示了数据分析师如何快速执行探索性数据分析。可以使用无服务器 SQL 池组合不同的数据集，并使用 Azure Synapse Studio 可视化结果。

后续步骤

若要了解如何将无服务器 SQL 池连接到 Power BI Desktop 并创建报表，请参阅将无服务器 SQL 池连接到 Power BI Desktop 并创建报表。

若要了解如何在无服务器 SQL 池中使用外部表，请参阅通过 Synapse SQL 使用外部表

Compartir a través de

教程：利用无服务器 SQL 池探索和分析数据湖

自动架构推理

时序、季节性和离群值分析

后续步骤

Recursos adicionales