G-gen の佐々木です。当記事では BigQuery 上で機械学習モデルを作成、評価、実行するための機能である BigQuery ML について解説します。

概要

BigQuery とは

BigQuery は、Google Cloud のフルマネージド分析用データベース（データウェアハウス）サービスです。インフラ管理不要の分析用データベースを従量課金で使用できます。

当記事では BigQuery 自体の説明は割愛します。プロダクトの全容については以下の記事をご一読ください。

blog.g-gen.co.jp

BigQuery ML とは

BigQuery ML とは、BigQuery 上で機械学習モデルのトレーニングや予測、評価を行うことができる機能です。BigQuery で使われる標準 SQL 準拠の GoogleSQL を使用し、BigQuery 上のデータを使った機械学習が容易に実現できます。

通常、機械学習モデルの開発には、機械学習フレームワークに対する高度な知識とプログラミング技術が要求されます。そのような専門的スキルを持つメンバーの確保が難しい場合であっても、BigQuery ML では SQL の知識があればモデルの開発を行うことができます。

BigQuery ML では、モデルのトレーニングや予測で使用するデータは BigQuery 自体に格納されているものをシームレスに使用することができ、データの蓄積・モデルの学習・予測の実行が BigQuery 内で完結します。これにより、モデル開発のための習熟が必要なツールが減り、また大量のデータ移動による時間・料金などのコストを抑えることができます。

参考：BigQuery の AI と ML の概要

BigQuery ML の使用方法

ユーザーインターフェース

BigQuery ML は、以下のユーザーインターフェースで利用することができます。

Google Cloud コンソール
bq コマンドラインツール
BigQuery REST API
BigQuery に統合された Colab Enterprise ノートブック
Jupyter ノートブックやビジネスインテリジェンスプラットフォームなどの外部ツール

Google Cloud コンソールから使用すると、BigQuery で通常の SQL を実行するときと同様の使用感で BigQuery ML の機能を利用することができます。

5番目の Jupyter ノートブックを使った方法について、以下の記事では、フルマネージドの Jupyter ノートブック環境である Vertex AI Workbench から、マジックコマンド %%bigquery で BigQuery ML を使用する例が示されています。

blog.g-gen.co.jp

BigQuery Editions

BigQuery の課金モードとしてオンデマンドを選択している場合、BigQuery ML を従量課金で使用することができます。

課金モードとして BigQuery Editionsを選択している場合、BigQuery ML は Enterprise ティアおよび Enterprise Plus ティアでのみ使用することができます（Standard ティアでは使用不可）。

BigQuery Editions の詳細については以下の記事をご一読ください。

blog.g-gen.co.jp

クエリのドライラン

BigQuery ML に限らず、BigQuery ではクエリ実行前にドライランを行うことで、実際に処理を行う前に、処理されるデータ量やオンデマンドで発生する料金を見積ることができます。

参考 : ドライラン

BigQuery ML でサポートされるモデル

以降に紹介するのは2025年1月時点でサポートされているモデルです。最新のサポート状況については以下のリンク先を参照してください。

参考：BigQuery の AI と ML の概要 - サポートされているモデル

内部モデル

BigQuery ML の組み込みのモデルとして、以下のモデルを使用して BigQuery 内部でトレーニングを行うことができます。

モデルの作成時に使用する CREATE MODEL ステートメント（後述）の OPTIONS で、トレーニングに使用するモデルを指定できます。

外部モデル

以下のモデルは BigQuery ML の外部にあり、別の AI/ML サービスである Vertex AI を使用してトレーニングされます。

インポートされたモデル

BigQuery の外部でトレーニングされたカスタムモデルを Cloud Storage からインポートし、BigQuery ML で予測を実行することができます。Bigquery ML でインポートできるモデルの種類は以下の通りです。

リモートモデル

ユーザーが Vertex AI でデプロイしたモデル

リモートモデルでは、Vertex AI でデプロイした機械学習モデルを使用して予測を実行することができます。モデルが大きすぎて BigQuery にインポートできない場合などに使用します。

Google の生成 AI モデル

BigQuery ML からは、Gemini 等、Vertex AI で提供される Google の生成 AI モデルをリモートモデルとして利用できます。

以下の記事では、BigQuery ML のリモートモデルで Google 開発の大規模言語モデルである PaLM 2 を使用して、テキストの感情分析を行っています。

blog.g-gen.co.jp

リモートモデルとして使用できる生成 AI モデルの最新情報については、以下のドキュメントを参照してください。

参考：Generative AI overview

タスク固有のソリューション

BigQuery ML から、Google Cloud が用意した、特定のタスクに特化した機械学習モデルの API（事前トレーニング済み API）を利用できます。

利用可能な事前トレーニング済み API には以下のような種類があります。それぞれ GoogleSQL の関数を使用してリクエストを送信します。

タスク	API の名前	GoogleSQL の関数
自然言語処理	Cloud Natural Language API	ML.UNDERSTAND_TEXT
機械翻訳	Cloud Translation API	ML.TRANSLATE
音声文字変換	Speech-to-Text API	ML.TRANSCRIBE
ドキュメント処理	Document AI API	ML.PROCESS_DOCUMENT
コンピュータビジョン	Cloud Vision API	ML.ANNOTATE_IMAGE

基本的な SQL ステートメント・関数

公式ドキュメントのチュートリアルを元に、BigQuery ML における基本的な SQL 文を解説します。

モデルや場面に応じてどのようなステートメント・関数が使用できるのかは、以下のドキュメントで解説されています。

参考：各モデルのエンドツーエンドのユーザージャーニー

CREATE MODEL ステートメント

BigQuery ML では、GoogleSQL の CREATE MODEL ステートメントを使用してモデルのトレーニングを行います。

モデルの作成には CREATE MODEL の他に、データセット内に同じ名前のモデルが存在しない場合のみモデルを作成する CREATE MODEL IF NOT EXISTS や、同じ名前のモデルが存在していた場合は置き換える CREATE OR REPLACE MODEL ステートメントが利用できます。

以下は、CREATE OR REPLACE MODEL ステートメントを使用して、bqml_tutorial データセット内に sample_model という名前でロジスティック回帰モデルを作成する例です。

#standardSQL
CREATE OR REPLACE MODEL `bqml_tutorial.sample_model` 
OPTIONS(model_type = 'logistic_reg') AS SELECT
  IF(totals.transactions IS NULL, 0, 1) AS label,
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(geoNetwork.country, "") AS country,
  IFNULL(totals.pageviews, 0) AS pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20160801' AND '20170630'

使用するモデルは OPTIONS の model_type= で設定しています。FROM で指定したデータから、SELECT で指定した特徴量を使用してモデルの学習を行っています。

Google Cloud コンソールや ML.TRAINING_INFO 関数を使用することで、モデルのトレーニング時の統計情報を確認することもできます。

トレーニングしたモデルの各種評価指標は、モデルの詳細からいつでも確認することができます。

参考：モデルの作成

ML.EVALUATE 関数

作成したモデルの評価は ML.EVALUATE 関数で行うことができます。

以下の SQL を実行することで、ML.EVALUATE 関数の MODEL 引数で指定したモデルに対して評価を行います。

#standardSQL
SELECT
  *
FROM
  ML.EVALUATE(
    MODEL `bqml_tutorial.sample_model`,
    (
      SELECT
        IF(totals.transactions IS NULL, 0, 1) AS label,
        IFNULL(device.operatingSystem, "") AS os,
        device.isMobile AS is_mobile,
        IFNULL(geoNetwork.country, "") AS country,
        IFNULL(totals.pageviews, 0) AS pageviews
      FROM
        `bigquery-public-data.google_analytics_sample.ga_sessions_*`
      WHERE
        _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'
    )
  )

コンソール上での出力は以下のようになります。

評価時に出力される指標はモデルの種類によって異なります。また、ML.CONFUSION_MATRIX（混同行列）や ML.ROC_CURVE（ROC 曲線）などの関数も提供されています。

詳細は以下のドキュメントを参照してください。

参考：BigQuery ML モデルの評価の概要

ML.PREDICT 関数

作成したモデルを使用して予測を行うには、ML.PREDICT 関数を使用します。

以下のように ML.PREDICT 関数の MODEL 引数で指定したモデルを使用して予測を行います。

#standardSQL
SELECT
  country,
  SUM(predicted_label) AS total_predicted_purchases
FROM
  ML.PREDICT(
    MODEL `bqml_tutorial.sample_model`,
    (
      SELECT
        IFNULL(device.operatingSystem, "") AS os,
        device.isMobile AS is_mobile,
        IFNULL(totals.pageviews, 0) AS pageviews,
        IFNULL(geoNetwork.country, "") AS country
      FROM
        `bigquery-public-data.google_analytics_sample.ga_sessions_*`
      WHERE
        _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'
    )
  )
GROUP BY country
ORDER BY total_predicted_purchases DESC
LIMIT 10

コンソール上での出力は以下のようになります。

参考：モデル推定の概要

特徴量の前処理

自動前処理

BigQuery ML では自動前処理として、モデルのトレーニング時に以下の前処理を自動で行っています。

欠損データの補完
値の変換（標準化、ワンホットエンコーディング、タイムスタンプの変換など）

自動前処理の詳細については、以下のドキュメントを参照してください。

参考：自動特徴前処理

手動前処理（TRANSFORM ステートメント）

TRANSFORM ステートメントを使用することで、前処理用の関数を使用することができます。

たとえば、以下の SQL では、ML.QUANTILE_BUCKETIZE 関数で mother_age 列のバケット化（ビニング）を、ML.FEATURE_CROSS 関数で is_male 列と mother_race 列の特徴クロスを作成する前処理を行ってからモデルを作成しています。

#standardSQL
CREATE MODEL `bqml_tutorial.natality_model` 
TRANSFORM(
  weight_pounds,
  is_male,
  gestation_weeks,
  ML.QUANTILE_BUCKETIZE(mother_age, 5) OVER() AS bucketized_mother_age,
  CAST(mother_race AS string) AS mother_race,
  ML.FEATURE_CROSS(
    STRUCT(
      is_male,
      CAST(mother_race AS STRING) AS mother_race
    )
  ) is_male_mother_race
) 
OPTIONS (
  model_type = 'linear_reg',
  input_label_cols = ['weight_pounds']
) AS SELECT
  *
FROM
  `bigquery-public-data.samples.natality`
WHERE
  weight_pounds IS NOT NULL AND RAND() < 0.001

その他、手動前処理に使用できる関数については以下のドキュメントを参照してください。

参考：手動での特徴の前処理

モデルのモニタリング

ML.VALIDATE_DATA_SKEW 関数や ML.VALIDATE_DATA_DRIFT 関数を使用することで、トレーニングに使用したデータと、実際のモデル運用時に予測に使用されるデータ（サービングデータ）の統計情報を比較し、データスキューやデータドリフトの発生を検知することができます。

データスキュー（Data Skew）とは、トレーニングで使用したデータの分布と、本番環境で提供されるデータの分布が大きく異なっていることにより、モデルの予測性能が下がってしまう現象のことです。トレーニングが適切に行えていない状況であると考えられます。

データドリフト（Data Drift）とは、本番環境で提供されるデータの分布が時間の経過とともに大きく変化してしまうことにより、モデルの予測性能が下がってしまう現象のことです。モデルの劣化と捉えてもいいでしょう。

モデルのモニタリングに使用できる関数の種類については、以下のドキュメントを参照してください。