BigQuery ML でバスケットボールの結果を予測する

このチュートリアルでは、BigQuery ML を使用してバスケットボールのスリーポイントフィールドゴールの結果を予測します。BigQuery ML を使用すると、BigQuery で SQL クエリを使用して機械学習モデルを作成して実行できます。このチュートリアルは、SQL のユーザーが簡単に機械学習を利用できるようにすることを目標としています。使い慣れたツールを使用してモデルを構築でき、データを移動する必要もないため、開発スピードを向上させることができます。

このチュートリアルでは、BigQuery 用の NCAA バスケットボールの一般公開データセットを使用して、スリーポイントフィールドゴールの結果を予測するモデルを作成します。

目標

このチュートリアルでは、次のものを使用します。

BigQuery ML。CREATE MODEL ステートメントを使用して線形回帰モデルを作成します。
ML.EVALUATE 関数。ML モデルを評価します。
ML.PREDICT 関数。ML モデルを使用して予測を行います。

費用

このチュートリアルでは、Google Cloud の課金対象となる次のコンポーネントを使用します。

BigQuery
BigQuery ML
Datalab

BigQuery の費用の詳細については、BigQuery の料金ページをご覧ください。

BigQuery ML の費用の詳細については、BigQuery ML の料金ページをご覧ください。

Datalab の費用の詳細については、Datalab の料金ページをご覧ください。

始める前に

Google Cloud アカウントにログインします。Google Cloud を初めて使用する場合は、アカウントを作成して、実際のシナリオでの Google プロダクトのパフォーマンスを評価してください。新規のお客様には、ワークロードの実行、テスト、デプロイができる無料クレジット $300 分を差し上げます。

Google Cloud Console の [プロジェクトセレクタ] ページで、Google Cloud プロジェクトを選択または作成します。

プロジェクトセレクタに移動

Cloud プロジェクトに対して課金が有効になっていることを確認します。詳しくは、プロジェクトで課金が有効になっているかどうかを確認する方法をご覧ください。

Google Cloud Console の [プロジェクトセレクタ] ページで、Google Cloud プロジェクトを選択または作成します。

プロジェクトセレクタに移動

新しいプロジェクトでは、BigQuery が自動的に有効になります。既存のプロジェクトで BigQuery を有効にするには、
BigQuery API を有効にします。
API を有効にする
にアクセスします。

ステップ 1: Datalab を設定する

このチュートリアルでは、Cloud Shell を使用して Datalab を設定します。Datalab は、BigQuery ML と BigQuery の操作に使用する、Jupyter ベースのノートブック環境を提供します。

Datalab を設定する前に、Compute Engine API と Cloud Source Repositories API を有効にする必要があります。これらの API の有効化については、サービスの有効化と無効化をご覧ください。

Compute Engine VM 上に Datalab を設定するには:

Google Cloud コンソールに移動します。

Google Cloud コンソール
「Cloud Shell をアクティブにする」をクリックします。
Cloud Shell ウィンドウで、次のコマンドを入力して、Google Cloud CLI のデフォルトゾーンとして us-central1-a を使用するように gcloud CLI を構成します。このゾーンに Datalab インスタンスが作成されます。
```
gcloud config set compute/zone us-central1-a
```
次のコマンドを入力して、Datalab コンテナの実行に使用する Compute Engine VM（mltutorialという名前）を作成します。また、このコマンドを実行すると、VM との SSH 接続が確立し、リモートの Datalab ウェブインターフェースと localhost のポート 8081 がマッピングされます。プロンプトが表示されたら、デフォルトの Compute Engine ゾーンに対応する番号を入力します。

注: このコマンドを実行するには、Compute Engine API と Cloud Source Repositories API を有効にする必要があります。詳細については、Google Cloud コンソールのヘルプで API の有効化と無効化をご覧ください。
```
datalab create mltutorial
```
The connection to Datalab is now open and will remain until this command is killed というメッセージの表示が、インスタンスに接続されたことを示しています。SSH 接続が確立されると、コマンドラインインターフェースからの出力が停止する場合があります。
Cloud Shell ウィンドウで、ウェブでプレビューアイコンをクリックします。
[ポートを変更] を選択します。
[プレビューポートの変更] ダイアログで「8081」を入力して、[変更してプレビュー] をクリックします。Datalab が新しいタブで開きます。
注: Cloud Shell セッションがタイムアウトした場合、次のコマンドを使用して Datalab VM に再接続できます。
```
gcloud compute ssh --quiet --zone "us-central1-a" --ssh-flag="-N" --ssh-flag="-L" --ssh-flag="localhost:8081:localhost:8080" "${USER}@mltutorial"
```
[Notebook] をクリックして、新しいノートブックを作成します。ノートブックが新しいタブで開きます。
タイトルバーにある [Untitled Notebook] をクリックして、ノートブックの名前を変更します。
[Rename Notebook] ダイアログで、「BigQuery ML tutorial」と入力し、[OK] をクリックします。
最初のコードセルに次のコマンドを入力します。このコマンドにより、BigQuery Python クライアントライブラリを最新バージョンに更新します。
```
!pip install --upgrade google-cloud-bigquery
```
[Run] > [Run from this cell] の順にクリックします。コードブロックの下にクエリの結果が表示されます。次のメッセージが表示されたら、インストールは完了です。

Successfully installed ...
このコードは多くの出力を生成します。出力を非表示にするには、セルメニューをクリックし、[Collapse] を選択してセルを折りたたみます。
[Add Code] をクリックして、新しいコードセルを作成します。

次のコードを入力して BigQuery Python クライアントライブラリをインポートしてクライアントを初期化します。BigQuery クライアントは、BigQuery API との間のメッセージの送受信に使用されます。

from google.cloud import bigquery
client = bigquery.Client()
# We use a unique dataset ID for this example to avoid collisions with
# other invocations of this tutorial.  In practice, you could leverage
# a persistent dataset and not create/destroy it with each invocation.
dataset_id = "bqml_tutorial_{}".format(str(uuid.uuid4().hex))

[Run] > [Run from this cell] の順にクリックします。このコマンドは出力を生成しません。

ステップ 2: データセットを作成する

次に、ML モデルを格納する BigQuery データセットを作成します。データセットを作成するには、次の操作を行います。

[Add Code] をクリックして、新しいコードセルを作成します。

次のコマンドを入力して、データセットを作成します。

dataset = bigquery.Dataset(client.dataset(dataset_id))
dataset.location = 'US'
client.create_dataset(dataset)

[Run] > [Run from this cell] の順にクリックします。コマンドの出力は次のようになります。

Dataset '[project_ID]:bqml_tutorial' successfully created.

ステップ 3: トレーニングデータと特徴データを生成する

次に、トレーニングデータと特徴データを生成します。まず、NCAA バスケットボールの一般公開データセットを使用して bigquery-public-data.ncaa_basketball.mbb_teams_games_sr テーブルにクエリを実行し、モデルの入力特徴を生成します。入力特徴を生成したら、トレーニングデータを生成します。

入力特徴を生成する（特徴量エンジニアリング）

入力特徴には、タイムウィンドウを変えながらホームチームとアウェイチームのゲーム統計の平均と標準偏差を使用します。使用するタイムウィンドウは現在の 10 ゲーム前、5 ゲーム前、1 ゲーム前です。team-id 列（ホームチームとアウェイチームの両方）と season 列は、ワンホット特徴としても使用され、モデルの平均統計の生成に使用されます。

入力特徴の生成に使用するクエリは長いので、従来の BigQuery ウェブ UI の保存済みクエリでテーブルを作成します。

入力特徴データを生成するには:

下のボタンをクリックして、入力データクエリを開きます。

特徴の入力クエリを開く

時間が許す限り、クエリを調べます。
クエリウィンドウの下にある [Show Options] をクリックします。
[Destination Table] セクションで、[Select Table] をクリックします。
[Select Destination Table] ダイアログで次の操作を行います。
1. [Project] で、プロジェクトを選択します。
2. [Dataset] で [bqml_tutorial] を選択します。
3. [Table ID] フィールドに「cume_games」と入力します。
4. [OK] をクリックします。
[RUN QUERY] をクリックします。これにより、指定したテーブルにクエリ結果を書き込むクエリジョブが作成されます。

宛先テーブルを指定せずにクエリを実行した場合は、結果ウィンドウの [Save as Table] ボタンをクリックすると、一時テーブルを永続テーブルにコピーできます。
クエリが完了するまで数秒かかります。クエリが完了すると、テーブル（cume_games）が BigQuery ウェブ UI のナビゲーションパネルに表示されます。

トレーニングデータを生成する

入力特徴を生成したら、トレーニングデータを生成します。トレーニングデータは、bqml_tutorial.wide_games テーブルに書き込まれます。トレーニングデータを生成する場合は、結果の予測に使用するゲームを除外します。

このクエリでは、前のクエリで作成した cume_games テーブルを自己結合します。この処理は、現在のゲームの前に、ホームチームとアウェイチームの両方の統計を生成するために行います。

トレーニングデータの生成に使用するクエリは長いので、従来の BigQuery ウェブ UI の保存済みクエリでテーブルを作成します。

トレーニングデータを生成するには:

下のボタンをクリックして、入力データクエリを開きます。

トレーニングデータクエリを開く

時間が許す限り、クエリを調べます。
クエリウィンドウの下にある [Show Options] をクリックします。
[Destination Table] セクションで、[Select Table] をクリックします。
[Select Destination Table] ダイアログで次の操作を行います。
1. [Project] で、プロジェクトを選択します。
2. [Dataset] で [bqml_tutorial] を選択します。
3. [Table ID] フィールドに「wide_games」と入力します。
4. [OK] をクリックします。
[RUN QUERY] をクリックします。これにより、指定したテーブルにクエリ結果を書き込むクエリジョブが作成されます。

宛先テーブルを指定せずにクエリを実行した場合は、結果ウィンドウの [Save as Table] ボタンをクリックすると、一時テーブルを永続テーブルにコピーできます。
クエリが完了するまで数秒かかります。クエリが完了すると、テーブル（wide_games）が BigQuery ウェブ UI のナビゲーションパネルに表示されます。

ステップ 4: モデルを作成する

次に、線形回帰モデルを作成します。このモデルでは、前のゲーム統計に基づいて、スリーポイントフィールドゴールの結果を予測します。GoogleSQL クエリは、CREATE MODEL ステートメントを使用してモデルを作成します。CREATE MODEL ステートメントではモデルの作成とトレーニングができますが、このチュートリアルでは、モデルを個別にトレーニングします。

モデルを作成する CREATE MODEL クエリを実行するには、次の操作を行います。

Datalab ノートブックに切り替えます。
[Add Code] をクリックして、新しいコードセルを作成します。
BigQuery Python クライアントライブラリには、最小限のコードでクエリを実行できるマジックコマンドが用意されています。クライアントライブラリからこのマジックコマンドを読み込むには、次のコードを入力します。
```
%load_ext google.cloud.bigquery
```
注: %load_ext は、Jupyter に組み込まれている数多くのマジックコマンドの 1 つです。%load_ext などのマジックコマンドの詳細については、Jupyter のドキュメントをご覧ください。
[Run] > [Run from this cell] の順にクリックします。このコマンドは出力を生成しません。
[Add Code] をクリックして、新しいコードセルを作成します。
BigQuery クライアントライブラリにはセルマジックである %%bigquery が用意されています。このセルマジックは SQL クエリを実行して、結果を pandas DataFrame として返します。次の GoogleSQL クエリをセルに入力します。クライアントライブラリでは #standardSQL 接頭辞を使用する必要はありません。GoogleSQL はデフォルトのクエリ構文です。

Jupyter

%%bigquery
CREATE OR REPLACE MODEL bqml_tutorial.ncaa_model OPTIONS(
  model_type='linear_reg', max_iteration=50) AS
SELECT
  * EXCEPT(
    game_id, season, scheduled_date,
    total_three_points_made,
    total_three_points_att),
  total_three_points_att as label
FROM
  bqml_tutorial.wide_games
WHERE
  # remove the game to predict
  game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'

Python

GitHub で表示フィードバック

sql = """
    CREATE OR REPLACE MODEL `{0}.ncaa_model`
    OPTIONS (
        model_type='linear_reg',
        max_iteration=50 ) AS
    SELECT
        * EXCEPT (
            game_id, season, scheduled_date,
            total_three_points_made,
            total_three_points_att),
        total_three_points_att as label
    FROM
        `{0}.wide_games`
    WHERE
        # remove the game to predict
        game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

[Run] > [Run from this cell] をクリックします。
クエリが完了するまでに数分かかります。最初のイテレーションが完了すると、モデル（ncaa_model）が BigQuery ウェブ UI のナビゲーションパネルに表示されます。クエリは CREATE MODEL ステートメントを使用するため、クエリの結果は表示されません。空の文字列が出力されます。

ステップ 5: トレーニングの統計情報を取得する

モデルのトレーニングの結果を確認するには、ML.TRAINING_INFO 関数を使用するか、BigQuery ウェブ UI で統計情報を表示します。このチュートリアルでは、ML.TRAINING_INFO 関数を使用します。

機械学習アルゴリズムは、多くのサンプルを検査し、損失を最小限に抑えるモデルを見つけることでモデルを構築します。このプロセスを経験損失最小化と呼びます。

損失とは、精度の低い予測に対するペナルティです。これは、1 つのサンプルで予測の精度がどのくらい低いかで表します。モデルの予測が完璧であれば、損失はゼロになります。それ以外の場合、精度に応じて損失が大きくなります。モデルをトレーニングする目的は、すべてのサンプルで平均的に損失の少ない重みとバイアスの組み合わせを見つけることです。

CREATE MODEL クエリで生成したモデルのトレーニング統計を確認するには、次の操作を行います。

[Add Code] をクリックして、新しいコードセルを作成します。
次の GoogleSQL クエリをセルに入力します。

Jupyter

%%bigquery
SELECT
  *
FROM
  ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)

Python

GitHub で表示フィードバック

sql = """
    SELECT
        *
    FROM
        ML.TRAINING_INFO(MODEL `{}.ncaa_model`)
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

[Run] > [Run from this cell] をクリックします。
クエリが完了すると、クエリの下に結果が表示されます。結果は次のようになります。

[トレーニングデータの損失] 列は、トレーニングデータセットで所定の回数のイテレーションを行った後に計算された損失指標を表します。線形回帰を行ったので、この列は平均二乗誤差になります。[評価データの損失] 列は、ホールドアウトデータセット（モデルを検証するためにトレーニングから返され、保存されているデータ）で計算された損失指標と同じになります。

ML.TRAINING_INFO 関数の詳細については、BigQuery ML 構文リファレンスをご覧ください。

ステップ 6: モデルを評価する

モデルを作成したら、ML.EVALUATE 関数を使用してモデルの性能を評価します。

モデルを評価する ML.EVALUATE クエリを実行するには、次の手順に従います。

[Add Code] をクリックして、新しいコードセルを作成します。
次の GoogleSQL クエリをセルに入力します。

Jupyter

%%bigquery
WITH eval_table AS (
SELECT
  *,
  total_three_points_att AS label
FROM
  `bqml_tutorial.wide_games` )
SELECT
  *
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.ncaa_model`,
    TABLE eval_table)

Python

GitHub で表示フィードバック

sql = """
    WITH eval_table AS (
        SELECT
            *,
            total_three_points_att AS label
        FROM
            `{0}.wide_games` )
    SELECT
        *
    FROM
        ML.EVALUATE(MODEL `{0}.ncaa_model`,
            TABLE eval_table)
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

[Run] > [Run from this cell] をクリックします。
クエリが完了すると、クエリの下に結果が表示されます。結果は次のようになります。

線形回帰を使用しているため、結果には次の列が含まれます。
- mean_absolute_error
- mean_squared_error
- mean_squared_log_error
- median_absolute_error
- r2_score
- explained_variance

評価結果で重要な指標は平均絶対誤差です。平均絶対誤差は、予測ラベルと実際のラベルとの差の平均値です。この場合、約 7 です。

ステップ 7: モデルを使用して結果を予測する

モデルの評価を行ったので、次に ML.PREDICT 関数を使用して、2018 年 NCAA 最終戦（ミシガン対ビラノバ）でのスリーポイントフィールドゴールのシュート数合計を予測します。

ML.PREDICT 関数を使用するクエリを実行するには、次の操作を行います。

[Add Code] をクリックして、新しいコードセルを作成します。
次の GoogleSQL クエリをセルに入力します。

Jupyter

%%bigquery
  WITH game_to_predict AS (
  SELECT
    *
  FROM
    `bqml_tutorial.wide_games`
  WHERE
    game_id='f1063e80-23c7-486b-9a5e-faa52beb2d83' )
SELECT
  truth.game_id AS game_id,
  total_three_points_att,
  predicted_total_three_points_att
FROM (
  SELECT
    game_id,
    predicted_label AS predicted_total_three_points_att
  FROM
    ML.PREDICT(MODEL `bqml_tutorial.ncaa_model`,
      table game_to_predict) ) AS predict
JOIN (
  SELECT
    game_id,
    total_three_points_att AS total_three_points_att
  FROM
    game_to_predict) AS truth
ON
  predict.game_id = truth.game_id

Python

GitHub で表示フィードバック

sql = """
    WITH game_to_predict AS (
        SELECT
            *
        FROM
            `{0}.wide_games`
        WHERE
            game_id='f1063e80-23c7-486b-9a5e-faa52beb2d83' )
    SELECT
        truth.game_id AS game_id,
        total_three_points_att,
        predicted_total_three_points_att
    FROM (
        SELECT
            game_id,
            predicted_label AS predicted_total_three_points_att
        FROM
            ML.PREDICT(MODEL `{0}.ncaa_model`,
            table game_to_predict) ) AS predict
    JOIN (
        SELECT
            game_id,
            total_three_points_att AS total_three_points_att
        FROM
            game_to_predict) AS truth
    ON
        predict.game_id = truth.game_id
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

[Run] > [Run from this cell] をクリックします。
クエリが完了すると、クエリの下に結果が表示されます。結果は次のようになります。モデルトレーニングは非確定的なため、結果が異なる場合があります。

total_three_points_att の値は、最終戦でのフィールドゴールのシュート数の実際の数、50 です。モデルの予測は 43.41 です。

クリーンアップ

このチュートリアルで使用したリソースについて、Google Cloud アカウントに課金されないようにするには、リソースを含むプロジェクトを削除するか、プロジェクトを維持して個々のリソースを削除します。

作成したプロジェクトを削除する。
あるいは、プロジェクトを保存し、Datalab VM を削除する。

Datalab VM の削除

プロジェクトを削除すると、Datalab VM が削除されます。Google Cloud プロジェクトを残す場合は、Datalab VM だけを削除できます。

Datalab VM を削除するには、次の手順を行います。

Compute Engine VM インスタンスのページを開きます。

[VM インスタンス] ページに移動
mltutorial インスタンスを確認し、[削除] をクリックします。
プロンプトが表示されたら、[削除] をクリックします。

プロジェクトの削除

プロジェクトを削除するには:

注意: プロジェクトを削除すると、次のような影響があります。

プロジェクト内のすべてのものが削除されます。このチュートリアルで既存のプロジェクトを使用した場合、それを削除すると、そのプロジェクトで行った他の作業もすべて削除されます。
カスタムプロジェクト ID が失われます。このプロジェクトを作成したときに、将来使用するカスタムプロジェクト ID を作成した可能性があります。そのプロジェクト ID を使用した URL（たとえば、appspot.com）を保持するには、プロジェクト全体ではなくプロジェクト内の選択したリソースだけを削除します。

複数のチュートリアルとクイックスタートを検討する予定がある場合は、プロジェクトを再利用すると、プロジェクトの割り当て制限を超えないようにできます。

Google Cloud コンソールで、[リソースの管理] ページに移動します。
[リソースの管理] に移動
プロジェクトリストで、削除するプロジェクトを選択し、[削除] をクリックします。
ダイアログでプロジェクト ID を入力し、[シャットダウン] をクリックしてプロジェクトを削除します。

次のステップ

BigQuery ML の概要で BigQuery ML の概要を確認する。
Datalab の詳細については、Datalab のドキュメントをご覧ください。

BigQuery ML でバスケットボールの結果を予測する

目標

費用

始める前に

ステップ 1: Datalab を設定する

ステップ 2: データセットを作成する

ステップ 3: トレーニング データと特徴データを生成する

入力特徴を生成する（特徴量エンジニアリング）

トレーニング データを生成する

ステップ 4: モデルを作成する

Jupyter

Python

ステップ 5: トレーニングの統計情報を取得する

Jupyter

Python

ステップ 6: モデルを評価する

Jupyter

Python

ステップ 7: モデルを使用して結果を予測する

Jupyter

Python

クリーンアップ

Datalab VM の削除

プロジェクトの削除

次のステップ

ステップ 3: トレーニングデータと特徴データを生成する

トレーニングデータを生成する