チュートリアル:サーバーレス SQL プールを使用してデータ レイクを探索および分析するTutorial: Explore and Analyze data lakes with serverless SQL pool

このチュートリアルでは、探索的データ分析を実行する方法について説明します。In this tutorial, you learn how to perform exploratory data analysis. サーバーレス SQL プールを使用して、さまざまな Azure Open Datasets を組み合わせます。You'll combine different Azure Open Datasets using serverless SQL pool. 次に、Azure Synapse Analytics の Synapse Studio で結果を視覚化します。You'll then visualize the results in Synapse Studio for Azure Synapse Analytics.

OPENROWSET(BULK...) 関数を使用すると、Azure Storage 内のファイルにアクセスできます。The OPENROWSET(BULK...) function allows you to access files in Azure Storage. OPENROWSET 関数は、リモート データ ソース (ファイルなど) の内容を読み取って行のセットとして返します。OPENROWSET function reads content of a remote data source (for example file) and returns the content as a set of rows.

自動スキーマ推論Automatic schema inference

データは Parquet ファイル形式で格納されるため、自動スキーマ推論を使用できます。Since data is stored in the Parquet file format, automatic schema inference is available. ファイル内のすべての列のデータ型を一覧表示することなく、簡単にデータに対するクエリを実行できます。You can easily query the data without listing the data types of all columns in the files. また、仮想列のメカニズムと filepath 関数を利用して、ファイルの特定のサブセットを除外することもできます。You also can use the virtual column mechanism and the filepath function to filter out a certain subset of files.

まず、NYC のタクシー データについて理解するために、次のクエリを実行します。Let's first get familiar with the NYC Taxi data by running the following query:

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

ニューヨーク市 (NYC) のタクシー データセットには以下が含まれます。New York City (NYC) Taxi dataset includes:

  • 乗車日時と降車日時。Pick-up and drop-off dates and times.
  • 乗車地点と降車地点。Pick-up and drop-off locations.
  • 移動距離。Trip distances.
  • 料金明細。Itemized fares.
  • 料金の種類。Rate types.
  • 支払いの種類。Payment types.
  • 運転手から報告された乗客の人数。Driver-reported passenger counts.

同様に、次のクエリを使用して、休日のデータセットに対してクエリを実行できます。Similarly, you can query the Public Holidays dataset by using the following query:

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

最後に、次のクエリを使用して、気象データセットに対してもクエリを実行できます。Lastly, you can also query the Weather Data dataset by using the following query:

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

個々の列の意味の詳細については、データ セットの説明を参照してください。You can learn more about the meaning of the individual columns in the descriptions of the data sets:

時系列、季節性、および外れ値の分析Time series, seasonality, and outlier analysis

次のクエリを使用して、毎年のタクシー乗車数を簡単にまとめることができます。You can easily summarize the yearly number of taxi rides by using the following query:

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

毎年のタクシー乗車数の結果を次のスニペットに示します。The following snippet shows the result for the yearly number of taxi rides:

毎年のタクシー乗車数の結果のスニペット

データは Synapse Studio で テーブル ビューから グラフ ビューに切り替えることによって視覚化できます。The data can be visualized in Synapse Studio by switching from the Table to the Chart view. さまざまな種類のグラフ ( [面][横棒][縦棒][折れ線][円][散布図] など) から選択できます。You can choose among different chart types, such as Area, Bar, Column, Line, Pie, and Scatter. この例で、 [Category column](カテゴリ列) を current_year に設定した [縦棒] グラフをプロットしてみましょう。In this case, plot the Column chart with the Category column set to current_year:

年間の乗車数を示す縦棒グラフ

この視覚化から、乗車数が年々減少している傾向が見て取れます。From this visualization, you can see a trend of decreasing ride numbers over the years. この減少はおそらく、近年のライドシェア企業の人気の高まりが原因であると思われます。Presumably, this decrease is due to the recent increased popularity of ride-sharing companies.

注意

このチュートリアルの執筆時点では、2019 年のデータは不完全です。At the time of writing this tutorial, data for 2019 is incomplete. その結果、その年の乗車数が著しく低下しています。As a result, there's a huge drop in the number of rides for that year.

次に、単年度の分析に重点を置いてみましょう。たとえば 2016 年に注目します。Next, let's focus the analysis on a single year, for example, 2016. 次のクエリでは、その年の毎日の乗車数が返されます。The following query returns the daily number of rides during that year:

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

このクエリの結果を次のスニペットに示します。The following snippet shows the result for this query:

2016 年の毎日の乗車数の結果のスニペット

ここでも、 [Category column](カテゴリ列) を current_day に設定し、 [Legend (series) column](凡例 (系列) 列) を rides_per_day に設定した [縦棒] グラフをプロットすることで、データを簡単に視覚化できます。Again, you can easily visualize data by plotting the Column chart with the Category column set to current_day and the Legend (series) column set to rides_per_day.

2016 年の毎日の乗車数を示す縦棒グラフ

プロット グラフから、土曜日をピーク日とする週単位のパターンがあることが確認できます。From the plot chart, you can see there's a weekly pattern, with Saturdays as the peak day. 夏期は休暇のため、毎月のタクシー乗車数が減少します。During summer months, there are fewer taxi rides because of vacations. また、時期および理由の観点から明確なパターンがないのにタクシー乗車数が大幅に減少していることも見て取れます。Also, notice some significant drops in the number of taxi rides without a clear pattern of when and why they occur.

次に、乗車数の減少が休日と関連しているかどうかを見てみましょう。Next, let's see if the drop in rides correlates with public holidays. NYC タクシー乗車数データセットを休日データセットと結合することで、相関関係があるかどうかを確認できます。We can see if there is a correlation by joining the NYC Taxi rides dataset with the Public Holidays dataset:

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
)
SELECT
*
FROM taxi_rides t
LEFT OUTER JOIN public_holidays p on t.current_day = p.date
ORDER BY current_day ASC

NYC タクシー乗車数のデータセットと休日データセットの結果の視覚化

今度は、休日のタクシー乗車数を強調してみましょう。This time, we want to highlight the number of taxi rides during public holidays. そのために、 [Category column](カテゴリ列) に none[Legend (series) columns](凡例 (系列) 列) に rides_per_day および holiday を選択します。For that purpose, we choose none for the Category column and rides_per_day and holiday as the Legend (series) columns.

休日のタクシー乗車数のプロット グラフ

このプロット グラフから、休日はタクシー乗車数が少なくなることがわかります。From the plot chart, you can see that during public holidays the number of taxi rides is lower. 1 月 23 日の大幅な減少については、まだ説明がついていません。There's still one unexplained large drop on January 23. では、気象データセットに対してクエリを実行して、その日のニューヨーク市の天気を確認してみましょう。Let's check the weather in NYC on that day by querying the Weather Data dataset:

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'

気象データセットの結果の視覚化

このクエリの結果から、タクシー乗車数の減少は次のことが原因であることが示されます。The results of the query indicate that the drop in the number of taxi rides occurred because:

  • 当日、ニューヨーク市に吹雪が発生して大雪だった (約 30 cm)。There was a blizzard on that day in NYC with heavy snow (~30 cm).
  • 寒かった (気温が摂氏 0 度を下回った)。It was cold (temperature was below zero degrees Celsius).
  • 強風だった (約 10 m/秒)。It was windy (~10 m/s).

このチュートリアルでは、データ アナリストが探索的データ分析を迅速に実行し、サーバーレス SQL プールを使用してさまざまなデータセットを簡単に結合し、Azure Synapse Studio を使用して結果を視覚化する方法について説明しました。This tutorial has shown how a data analyst can quickly perform exploratory data analysis, easily combine different datasets by using serverless SQL pool, and visualize the results by using Azure Synapse Studio.

次のステップNext steps

サーバーレス SQL プールを Power BI Desktop に接続してレポートを作成する方法については、サーバーレス SQL プールの Power BI Desktop への接続とレポートの作成に関する記事をご覧ください。To learn how to connect serverless SQL pool to Power BI Desktop and create reports, see Connect serverless SQL pool to Power BI Desktop and create reports.

サーバーレス SQL プールで外部テーブルを使用する方法については、「Synapse SQL で外部テーブルを使用する」をご覧ください。To learn how to use External tables in serverless SQL pool see Use external tables with Synapse SQL