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

は、

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

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

目標

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

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

料金

このチュートリアルでは、以下を含む、Cloud Platform の有料コンポーネントを使用します。

  • BigQuery
  • BigQuery ML
  • Cloud Datalab

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

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

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

始める前に

  1. Google アカウントにログインします。

    Google アカウントをまだお持ちでない場合は、新しいアカウントを登録します。

  2. GCP プロジェクトを選択または作成します。

    [リソースの管理] ページに移動

  3. プロジェクトに対して課金が有効になっていることを確認します。

    課金を有効にする方法について

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

    APIを有効にする

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

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

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

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

  1. GCP Console に移動します。

    GCP Console

  2. [Cloud Shell を有効にする] をクリックします。

    Cloud Shell をアクティブにするアイコン

  3. Cloud Shell のウィンドウで次のコマンドを入力して、Cloud SDK のデフォルト ゾーンとして us-central1-a を使用するように gcloud ツールを構成します。このゾーンに Cloud Datalab のインスタンスが作成されます。

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

    datalab create mltutorial
    

    The connection to Datalab is now open and will remain until this command is killed」というメッセージが表示されたら、インスタンスに接続されています。SSH 接続が確立すると、コマンドライン インターフェースからの出力は停止する場合があります。

  5. Cloud Shell ウィンドウで、ウェブでプレビュー アイコン ウェブ プレビュー アイコン をクリックします。

  6. [ポートの変更] を選択します。

  7. [プレビュー ポートの変更] ダイアログで、「8081」と入力してから [変更してプレビュー] をクリックします。Cloud Datalab が新しいタブで開きます。

    gcloud compute ssh --quiet --zone "us-central1-a" --ssh-flag="-N" --ssh-flag="-L" --ssh-flag="localhost:8081:localhost:8080" "${USER}@mltutorial"
    
  8. [Notebook] をクリックして、新しいノートブックを作成します。ノートブックが新しいタブで開きます。

  9. タイトルバーにある [Untitled Notebook] をクリックして、ノートブックの名前を変更します。

  10. [Rename Notebook] ダイアログで「BigQuery ML tutorial」と入力し、[OK] をクリックします。

  11. 最初のコードセルに次のコマンドを入力します。このコマンドにより、BigQuery Python クライアント ライブラリを最新バージョンに更新します。

    !pip install --upgrade google-cloud-bigquery
    
  12. [Run] > [Run from this cell] の順にクリックします。コードブロックの下にクエリの結果が表示されます。次のメッセージが表示されたら、インストールは完了です。

    Successfully installed ...

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

    セルメニュー

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

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

    from google.cloud import bigquery
    client = bigquery.Client()
  16. [Run] > [Run from this cell] の順にクリックします。このコマンドは出力を生成しません。

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

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

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

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

    dataset = bigquery.Dataset(client.dataset('bqml_tutorial'))
    dataset.location = 'US'
    client.create_dataset(dataset)
  3. [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 の保存済みクエリでテーブルを作成します。

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

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

    特徴の入力クエリを開く

    時間が許す限り、クエリを調べます。

  2. クエリ ウィンドウの下にある [Show Options] をクリックします。

  3. [Destination Table] セクションで、[Select Table] をクリックします。

  4. [Select Destination Table] ダイアログで次の操作を行います。

    1. [Project] で、プロジェクトを選択します。
    2. [Dataset] で、[bqml_tutorial] を選択します。
    3. [Table ID] フィールドに「cume_games」と入力します。
    4. [OK] をクリックします。
  5. [RUN QUERY] をクリックします。これにより、指定したテーブルにクエリ結果を書き込むクエリジョブが作成されます。

    抽出先テーブルを指定せずにクエリを実行した場合は、結果ウィンドウの [Save as Table] ボタンをクリックすることで、一時テーブルを永続テーブルにコピーできます。

  6. クエリが完了するまで数秒かかります。クエリを実行すると、BigQuery ウェブ UI のナビゲーション パネルにテーブル(cume_games)が表示されます。

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

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

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

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

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

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

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

    時間が許す限り、クエリを調べます。

  2. クエリ ウィンドウの下にある [Show Options] をクリックします。

  3. [Destination Table] セクションで、[Select Table] をクリックします。

  4. [Select Destination Table] ダイアログで次の操作を行います。

    1. [Project] で、プロジェクトを選択します。
    2. [Dataset] で、[bqml_tutorial] を選択します。
    3. [Table ID] フィールドに「wide_games」と入力します。
    4. [OK] をクリックします。
  5. [RUN QUERY] をクリックします。これにより、指定したテーブルにクエリ結果を書き込むクエリジョブが作成されます。

    抽出先テーブルを指定せずにクエリを実行した場合は、結果ウィンドウの [Save as Table] ボタンをクリックすることで、一時テーブルを永続テーブルにコピーできます。

  6. クエリが完了するまで数秒かかります。クエリを実行すると、BigQuery ウェブ UI のナビゲーション パネルにテーブル(wide_games)が表示されます。

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

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

CREATE MODEL クエリを実行してモデルを作成するには:

  1. Cloud Datalab ノートブックに切り替えます。

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

  3. BigQuery Python クライアント ライブラリには、最小限のコードでクエリを実行できるマジック コマンドが用意されています。クライアント ライブラリからこのマジック コマンドを読み込むには、次のコードを入力します。

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

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

  6. BigQuery クライアント ライブラリにはセルマジックである %%bigquery が用意されています。このセルマジックは SQL クエリを実行して、結果を pandas DataFrame として返します。セルに次の標準 SQL クエリを入力します。クライアント ライブラリでは #standardSQL 接頭辞を使用する必要はありません。標準 SQL がデフォルトのクエリ構文です。

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

sql = """
    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'
"""
df = client.query(sql).to_dataframe()
print(df)

  1. [Run] > [Run from this cell] の順にクリックします。

  2. クエリが完了するまでに数分かかります。最初の繰り返しが完了すると、BigQuery ウェブ UI のナビゲーション パネルにモデル(ncaa_model)が表示されます。このクエリでは CREATE MODEL ステートメントを使用しているため、クエリの結果は表示されません。空の文字列が出力されます。

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

モデルのトレーニング結果を確認するには、ML.TRAINING_INFO 関数を使用するか、BigQuery ウェブ UI で統計を表示させます。この機能は現在、従来の BigQuery ウェブ UI では使用できません。このチュートリアルでは、ML.TRAINING_INFO 関数を使用します。

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

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

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

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

  2. セルに次の標準 SQL クエリを入力します。

Jupyter

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

Python

sql = """
    SELECT
        *
    FROM
        ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)
"""
df = client.query(sql).to_dataframe()
print(df)

  1. [Run] > [Run from this cell] の順にクリックします。

  2. クエリが完了すると、クエリの下に結果が表示されます。結果は次のようになります。

    ML.TRAINING_INFO の出力

    [Training Data Loss] 列は、トレーニング データセットで所定の繰り返し回数を行った後に計算された損失指標を表します。線形回帰を行ったので、この列は平均二乗誤差になります。[Evaluation Data Loss] 列は、ホールドアウト データセット(モデルの検証に使用するためにトレーニングから除外されたデータ)で計算された損失指標と同じになります。

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

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

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

モデルを評価する ML.EVALUATE クエリを実行するには:

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

  2. セルに次の標準 SQL クエリを入力します。

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

sql = """
    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)
"""
df = client.query(sql).to_dataframe()
print(df)

  1. [Run] > [Run from this cell] の順にクリックします。

  2. クエリが完了すると、クエリの下に結果が表示されます。結果は次のようになります。

    ML.EVALUATE の出力

    線形回帰を使用しているため、結果には次の列が含まれます。

    • 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 関数を使用するクエリを実行するには:

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

  2. セルに次の標準 SQL クエリを入力します。

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

sql = """
    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
"""
df = client.query(sql).to_dataframe()
print(df)

  1. [Run] > [Run from this cell] の順にクリックします。

  2. クエリが完了すると、クエリの下に結果が表示されます。結果は次のようになります。モデル トレーニングは非確定的なため、結果が異なる場合があります。

    ML.PREDICT の出力

total_three_points_att 値(50)は、最終戦で発生した実際のフィールド ゴールの数です。モデルの予測は 43.41 です。

クリーンアップ

このチュートリアルで使用したリソースについて、Google Cloud Platform アカウントに課金されないようにする手順は次のとおりです。

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

Cloud Datalab VM の削除

プロジェクトを削除すると、Cloud Datalab VM が削除されます。Cloud Platform プロジェクトを削除しない場合は、Cloud Datalab VM を削除できます。

Cloud Datalab VM を削除するには:

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

    [VM インスタンス] ページに移動

  2. mltutorial インスタンスを確認し、[削除] をクリックします。

  3. プロンプトが表示されたら、[削除] をクリックします。

プロジェクトの削除

プロジェクトを削除する手順は次のとおりです。

  1. GCP Console で [プロジェクト] ページに移動します。

    プロジェクト ページに移動

  2. プロジェクト リストで、削除するプロジェクトを選択し、[削除] をクリックします。
  3. ダイアログでプロジェクト ID を入力し、[シャットダウン] をクリックしてプロジェクトを削除します。

次のステップ

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

BigQuery ML のドキュメント