SQL だけで機械学習デビュー！BigQuery ML を触ってみた

はじめに

こんにちは。DX開発事業部の鹿嶋です。ねこ、吸ってますか？

先日、我が家のねこの健康診断に行ってきました。結果は概ね健康だったのですが、ちょっと体重が重いのでダイエットした方がいいかもね〜と先生に言われてしまったので、減量フードに徐々に切り替えを進めている今日この頃です。

さて、ねこという動物は、体調不良を隠す傾向にあります。野生下において、「あ、こやつ弱っているぞ」なんて天敵に見られたら大きなピンチになってしまうため、と個人的には思っていますが、飼育下において異常が人間の目で見てわかる状態、というのは結構危険な状態に進行している可能性が高いです。

技術の進歩はすさまじいもので、そんな愛猫の変化を検知するために、首輪型のデバイスや、トイレに取り付けるデバイスがあったりします。これらの仕組みの裏側には、いわゆる機械学習と呼ばれる技術が使用されています。

機械学習と聞くと、Python を使った環境構築や専門的な知識が必要で、どうしてもハードルが高く感じられがちですが、Google Cloud の BigQuery ML であれば、使い慣れた SQL だけでモデルの作成から正常値の計測・異常検知の実行まで完結させることができます。

今回は、データの正常値を学習させ、その中に混ぜた異常値を検出するシンプルな検証を通して、実際に BigQuery ML を使ってみます！

BigQuery ML とは？

BigQuery ML 公式リファレンス

Google Cloud のデータウェアハウスとしてお馴染み BigQuery 内で、SQL を使って機械学習モデルの構築・実行ができる機能です。
Python などのプログラミング言語を使ったり、専用のAIライブラリを学んだりする必要がないため、データアナリストやSQLが書ける人であれば、初期学習コストを抑えて機械学習を始められます。

特徴として、通常の機械学習のようにデータを別の環境にエクスポートして学習させる必要がなく、BigQuery に蓄積されているデータをそのまま超高速で処理できます。
また、売上予測・ユーザーのセグメント分け・時系列データの異常検知など、用途別の学習済みモデルが最初から用意されています。

実際にやってみた

まずは、BigQuery で、2つのダミーデータを作成します。
シチュエーションとしては、特定期間の部屋の室温データとしました。
1つは、22.5度をベースとして、±2.5度の範囲を正常としたデータ（学習用）、もう一つは、冷暖房が故障したと仮定して正常範囲を逸脱した温度になっているデータが含まれるデータ（検証用）としています。

学習用データ

CREATE SCHEMA IF NOT EXISTS `{your_project_ID}.greenhouse_data` OPTIONS(location='asia-northeast1');

CREATE OR REPLACE TABLE `{your_project_ID}.greenhouse_data.greenhouse_history_pure` AS
WITH time_series AS (
  SELECT timestamp
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY('2026-06-24 00:00:00', '2026-06-30 23:50:00', INTERVAL 10 MINUTE)) AS timestamp
)
SELECT
  timestamp,
  22.5 + (RAND() - 0.5) * 5 AS temperature,
  'NORMAL' AS true_label
FROM time_series
ORDER BY timestamp ASC;

検証用データ

CREATE OR REPLACE TABLE `{your_project_ID}.greenhouse_data.greenhouse_telemetry` AS
WITH time_series AS (
  SELECT timestamp
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY('2026-07-01 00:00:00', '2026-07-07 23:50:00', INTERVAL 10 MINUTE)) AS timestamp
),
base_data AS (
  SELECT
    timestamp,
    22.5 + (RAND() - 0.5) * 5 AS normal_temp,
    IF(RAND() < 0.01, TRUE, FALSE) AS is_anomaly_trigger
  FROM time_series
)
SELECT
  timestamp,
  CASE
    WHEN is_anomaly_trigger AND RAND() < 0.5 THEN 12.0
    WHEN is_anomaly_trigger THEN 37.0
    ELSE normal_temp
  END AS temperature,
  IF(is_anomaly_trigger, 'ANOMALY', 'NORMAL') AS true_label
FROM base_data
ORDER BY timestamp ASC;

データの準備ができたら、次は今回の検証にあたってのカスタムモデルを作成します。トレーニング済みモデルという便利なものもありますが、今回の検証と関係のないデータにてトレーニングがされていることから、せっかくなのでカスタムモデルを作成して進めてきます。よそはよそ、うちはうちの精神です。

以下の SQL を実行してモデルを作成します。

CREATE OR REPLACE MODEL `{your_project_ID}.{your_model_name}`
OPTIONS(
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'timestamp',
  time_series_data_col = 'temperature'
) AS
SELECT timestamp, temperature 
FROM `{your_project_ID}.{your_model_name}`;

時間による変化をターゲットとしたいので、モデルタイプは ARIMA_PLUS を使用します。

利用用途によって使用するモデルは異なるため、興味のある方は以下の公式ドキュメントを確認してみると良いかもしれません。

BigQuery ML モデルの評価の概要

SQL を実行すると、モデルの作成が完了した旨のポップアップが表示されるので、検証用データを作成したカスタムモデルに与えて異常値を判定できるか確認します。

SELECT 
  timestamp, 
  temperature, 
  is_anomaly,
  true_label,
  lower_bound,
  upper_bound
FROM ML.DETECT_ANOMALIES(
  MODEL `{your_project_ID}.{your_model_name}`,
  STRUCT(0.99 AS anomaly_prob_threshold),
  (SELECT timestamp, temperature, true_label FROM `{your_project_ID}.greenhouse_data.greenhouse_telemetry`)
)
ORDER BY timestamp ASC;

簡単ではありますが、中身について簡単に解説します。

• 1-7行目: 列生成用の情報
• 8-9行目: 使用するモデルの指定
• 10行目: 判定の厳しさ（99%以上の信頼性とモデルが判断したものだけ異常値とすること）
• 11行目: 判定したいデータの情報

SELECT
  timestamp, // 検証用データの列
  temperature, // 検証用データの列
  is_anomaly, // クエリを与えてAIが計算する列（TRUE / FALSE）
  true_label, // 検証用データの列
  lower_bound, // クエリを与えてAIが計算する列（正常値下限）
  upper_bound // クエリを与えてAIが計算する列（正常値上限）
FROM ML.DETECT_ANOMALIES(
// 使用するAIモデル
  MODEL `{your_project_ID}.{your_model_name}`,
// 判定の厳しさ
  STRUCT(0.99 AS anomaly_prob_threshold),
// 判定したい対象のデータ
  (SELECT timestamp, temperature, true_label FROM `{your_project_ID}.greenhouse_data.greenhouse_telemetry`)
)

結果ですが、検証用データに含まれた明らかな異常値をきちんと判別してくれていることがわかります（※もう少しランダムな異常値にすれば良かったと反省）。

例えば、14:00〜15:00 の間は＋5度、0:00〜2:00 は-10度 といったように、時間と連動して温度の適正値が上下するような SQL に書き換えた上でもう一度学習させれば、モデルはそれを理解した上でまた異なった回答になるかと思われます。

おわりに

機械学習と聞くとどうしても専門性の高いイメージがあり尻込みしてしまうかもしれませんが、SQL のみでモデルの作成から異常検知の実行までスムーズに行うことができました。Google Cloud のプロジェクトさえあれば、環境構築の手間もコストもほとんどかからずに BigQuery だけで完結できるので、まずは手元のデータで機械学習を試してみたいという方の第一歩としておすすめです。

Looker などの BIツールと組み合わせて検出された異常値をグラフ上で可視化してみるのも面白いと思いますので、機会があればこちらも記事化できればと思います！