チュートリアル:サーバーレス SQL プールを使用してデータ レイクを探索および分析する

• Fabric無料試用版を開始します。
• Fabric Data Warehouse 用マイグレーションアシスタント

このチュートリアルでは、ストレージのセットアップを必要とせずに、既存の Open Datasets を使用して探索的データ分析を実行する方法について説明します。 サーバーレス SQL プールを使用して、さまざまな Azure Open Datasets を組み合わせます。 次に、Azure Synapse Analytics の Synapse Studio で結果を視覚化します。

このチュートリアルでは、次の作業を行いました。

サーバーレス SQL プールにアクセスする

すべてのワークスペースには、"組み込み" と呼ばれる、ユーザーが使用できるように事前に構成されたサーバーレス SQL プールが付属しています。 これにアクセスするには、次の手順を実行します。

1. ワークスペースを開き、[開発] ハブを選択します。
2. +[ 新しいリソースの追加] ボタンを選択します。
3. SQL スクリプトをインポートします。

このスクリプトを使用して、SQL 容量を予約する必要なくデータを探索できます。

Azure サブスクリプションをお持ちでない場合は、開始する前に 無料アカウント を作成してください。

チュートリアル データにアクセスする

このチュートリアルで使用するすべてのデータは、ストレージ アカウント azureopendatastorage に格納されています。このアカウントには、このチュートリアルのようなオープンな使用のために Azure Open Datasets が保持されています。 ワークスペースがパブリック ネットワークにアクセスできる限り、すべてのスクリプトをそのままワークスペースから直接実行できます。

このチュートリアルでは、 ニューヨーク市 (NYC) タクシーに関するデータセットを使用します。

• 乗車日時と降車日時
• 乗車地点と降車地点
• 移動距離
• 料金明細
• 料金の種類
• 支払いの種類
• 運転手から報告された乗客の人数

OPENROWSET(BULK...) 関数を使用すると、Azure Storage 内のファイルにアクセスできます。 [OPENROWSET](develop-openrowset.md) は、リモート データ ソース (ファイルなど) の内容を読み取って行のセットとして返します。

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]

個々の列の意味の詳細については、データ セットの説明を参照してください。

• NYC タクシー
• 祝日
• 気象データ

自動スキーマ推論

データは Parquet ファイル形式で格納されるため、自動スキーマ推論を使用できます。 ファイル内のすべての列のデータ型を一覧表示することなく、データに対するクエリを実行できます。 また、仮想列のメカニズムと filepath 関数を利用して、ファイルの特定のサブセットを除外することもできます。

時系列、季節性、および外れ値の分析

次のクエリを使用して、毎年のタクシー乗車数をまとめることができます。

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 でテーブル ビューからグラフ ビューに切り替えることによって視覚化できます。 さまざまな種類のグラフ ( エリア 、 棒グラフ 、 縦棒グラフ 、 折れ線グラフ 、 円グラフ 、 散布図 など) から選択できます。 この場合、Column チャートをプロットし、カテゴリ 列を current_year に設定します。

この視覚化から、乗車数が年々減少している傾向が見て取れます。 この減少はおそらく、近年のライドシェア企業の人気の高まりが原因であると思われます。

単年度の分析に重点を置くこともできます。たとえば 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

このクエリの結果を次のスニペットに示します。

ここでも、[縦棒]グラフをプロットすることで、データを視覚化できます。Category 列を current_day に設定し、Legend (series) 列を 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 日の大幅な減少については、まだ説明がついていません。 では、気象データセットに対してクエリを実行して、その日のニューヨーク市の天気を確認してみましょう。

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'

このクエリの結果から、タクシー乗車数の減少は次のことが原因であることが示されます。

• 当日、ニューヨーク市に吹雪が発生して大雪だった (約 30 cm)。
• 寒かった (気温が摂氏 0 度を下回った)。
• 強風だった (約 10 m/秒)。

このチュートリアルでは、データアナリストが探索的データ分析をすばやく実行する方法について説明しました。 サーバーレス SQL プールを使用してさまざまなデータセットを組み合わせ、Azure Synapse Studio を使用して結果を視覚化できます。

関連するコンテンツ

サーバーレス SQL プールを Power BI Desktop に接続してレポートを作成する方法については、サーバーレス SQL プールの Power BI Desktop への接続とレポートの作成に関する記事をご覧ください。

サーバーレス SQL プールで外部テーブルを使用する方法については、「Synapse SQL で外部テーブルを使用する」をご覧ください。