30日間で理解する GCP for AWSエンジニア - 実践ブログシリーズ - 19日目： BigQuery MLでSQLだけで機械学習モデルを作ってみよう

BigQuery ML入門ガイド：SQLだけで機械学習を始めよう

BigQuery MLとは？

BigQuery MLは、Google CloudのデータウェアハウスであるBigQueryに統合された機械学習サービスです。通常、機械学習モデルを構築するには、データの抽出・加工、モデルの学習、評価、デプロイといった複数のプロセスが必要で、PythonやRのような専門的な言語の習得が求められます。

しかし、BigQuery MLを使えば、これらのプロセスを標準的なSQLクエリだけで完結できます。これにより、データアナリストやSQLエンジニアが専門の機械学習エンジニアの助けを借りることなく、直接BigQuery上で予測モデルを構築・実行できるようになります。

SQLだけでモデルを作る手順

今回は、実際に利用できるBigQueryの公開データセットを使って、回帰モデルをSQLだけで作成する手順を解説します。

1. データセットの準備

BigQueryには、無料で利用できる公開データセットが多数用意されています。今回は、実在するデータセットbigquery-public-data.london_bicycles.cycle_hireを使用して、自転車のレンタル時間を予測するモデルを作成してみましょう。

このデータセットには、ロンドンの自転車シェアサービスのデータが含まれており、以下のような情報が格納されています：

• start_station_id: 開始ステーション番号
• end_station_id: 終了ステーション番号
• duration: レンタル時間（秒）
• start_date: レンタル開始日時

2. データの確認と前処理

まずは、データの構造を理解するために、サンプルデータを確認しましょう。

-- データの概要を確認
SELECT
  start_station_id,
  end_station_id,
  duration,
  start_date
FROM
  `bigquery-public-data.london_bicycles.cycle_hire`
WHERE
  duration BETWEEN 300 AND 7200  -- 5分から2時間の範囲
  AND start_date BETWEEN '2015-01-01' AND '2015-12-31'
LIMIT 10;

3. モデルの作成

CREATE MODEL文を使って、線形回帰モデルを作成します。

CREATE OR REPLACE MODEL `your_project_id.your_dataset_id.bike_duration_model`
OPTIONS(
  model_type='LINEAR_REG',
  input_label_cols=['duration']
) AS
SELECT
  start_station_id,
  end_station_id,
  EXTRACT(HOUR FROM start_date) AS start_hour,
  EXTRACT(DAYOFWEEK FROM start_date) AS day_of_week,
  duration
FROM
  `bigquery-public-data.london_bicycles.cycle_hire`
WHERE
  duration BETWEEN 300 AND 7200  -- 異常値を除外
  AND start_date BETWEEN '2015-01-01' AND '2015-10-31'  -- 訓練データ期間
  AND start_station_id IS NOT NULL
  AND end_station_id IS NOT NULL;

コードの説明：

• CREATE OR REPLACE MODEL: モデルを作成（既存モデルがあれば上書き）するコマンドです
• your_project_id.your_dataset_id.bike_duration_model: モデルを保存する場所をプロジェクトID.データセットID.モデル名の形式で指定します
• OPTIONS: モデルの設定を指定します
  • model_type='LINEAR_REG': モデルの種類を線形回帰に指定しています。連続値の予測に適しています
  • input_label_cols=['duration']: 予測したい目的変数を指定します
• SELECT ... FROM: モデルの学習に使用するデータを指定します。特徴量として開始・終了ステーション、時間、曜日を使用しています

4. モデルの評価

モデルがどれくらい正確に予測できるかを確認するために、ML.EVALUATE関数を使います。

SELECT
  *
FROM
  ML.EVALUATE(MODEL `your_project_id.your_dataset_id.bike_duration_model`,
    (
      SELECT
        start_station_id,
        end_station_id,
        EXTRACT(HOUR FROM start_date) AS start_hour,
        EXTRACT(DAYOFWEEK FROM start_date) AS day_of_week,
        duration
      FROM
        `bigquery-public-data.london_bicycles.cycle_hire`
      WHERE
        duration BETWEEN 300 AND 7200
        AND start_date BETWEEN '2015-11-01' AND '2015-12-31'  -- テストデータ期間
        AND start_station_id IS NOT NULL
        AND end_station_id IS NOT NULL
    )
  );

このクエリでは、モデルの学習に使用していないデータ（11月〜12月のデータ）を使ってモデルを評価します。出力結果には、平均絶対誤差（mean_absolute_error）やR²スコア（r2_score）といった評価指標が表示されます。

5. 予測の実行

最後に、作成したモデルを使って、新しいデータに対する予測を行います。ML.PREDICT関数を使用します。

SELECT
  start_station_id,
  end_station_id,
  start_hour,
  day_of_week,
  predicted_duration
FROM
  ML.PREDICT(MODEL `your_project_id.your_dataset_id.bike_duration_model`,
    (
      SELECT
        123 AS start_station_id,
        456 AS end_station_id,
        14 AS start_hour,  -- 14時
        2 AS day_of_week   -- 月曜日
    )
  );

• ML.PREDICT: 予測を実行するコマンドです
• predicted_duration: BigQuery MLが自動で生成する予測結果のカラム名です

このクエリを実行すると、指定した条件での自転車レンタル時間の予測値が表示されます。

BigQuery MLで利用可能なモデルタイプ

BigQuery MLは、線形回帰だけでなく、以下のような様々なモデルタイプに対応しています：

• 分類モデル：ロジスティック回帰、ブースティング分類器
• 回帰モデル：線形回帰、ブースティング回帰器
• クラスタリング：k-means法
• 時系列分析：ARIMA、ARIMA_PLUS
• 深層学習：DNNモデル、AutoMLモデル
• 異常検知：k-means、Autoencoder

まとめ

BigQuery MLは、複雑な機械学習のプロセスをSQLというシンプルで馴染みのある言語に落とし込み、データアナリストやエンジニアがより迅速にデータから価値を引き出せるようにします。SQLの知識があれば、手軽に機械学習モデルを構築・活用できるため、データ活用の幅が大きく広がります。

実際にモデルを構築する際は、以下の点に注意しましょう：

• データの品質：欠損値や異常値の処理が重要です
• 特徴量選択：予測に有効な特徴量を選択しましょう
• 評価方法：適切な評価指標でモデルの性能を判断しましょう
• 本番運用：定期的なモデルの再学習を検討しましょう

これらのモデルを使って、顧客のセグメンテーションや売上予測、需要予測など、様々なビジネス課題の解決に挑戦してみてください。