SQLだけでKaggleに挑戦！BigQuery MLで回帰分析をやってみた
対象読者
- SQLの経験はあり、機械学習にも挑戦してみたいエンジニアやアナリスト
- データ分析のプロセスを、より手軽に、より高速に実行したい方
- Google Cloud、特にBigQueryの機械学習機能に興味がある方
TL;DR
BigQueryに組み込まれた機械学習機能「BigQuery ML」が驚くほど強力。①CREATE MODELというSQLを書くだけで、データの前処理から複雑なモデルの学習、評価までを自動で実行してくれます。この記事では、Kaggleの有名な「住宅価格予測」コンペのデータを使い、**SQL数行で高精度な回帰モデルを構築を実施してみました！
1. 今回使う技術「BigQuery ML」とは？
まず、今回利用する技術について簡単に見ていきましょう。
BigQueryとは？
BigQueryは、Google Cloudが提供するフルマネージドでサーバーレスなデータウェアハウスです。ペタバイト級の巨大なデータに対しても、使い慣れたSQLで驚異的な速度の分析処理を実行できるのが特徴です。
Googleの公式ドキュメントでは、以下のように説明されています。
BigQuery は、Google のインフラストラクチャの処理能力を活用して、超並列クエリ実行と高帯域幅ネットワーク接続により、大規模なデータセットに対するクエリを短時間で処理できる、エンタープライズ データ ウェアハウスです。
（BigQuery の概要 | Google Cloud）
単なるデータ置き場ではなく、分析のための超強力な計算エンジンも内蔵している、と考えると分かりやすいです。
BigQuery ML (BQML) とは？
BigQuery MLは、そのBigQuery上でSQLクエリを使用して機械学習モデルを作成し、実行できる機能です。これにより、データを別の環境に移動させることなく、使い慣れたSQLだけで機械学習のワークフローを完結できます。
Google Cloud の BigQuery ML を使用すると、データ アナリストやデータ サイエンティストは、使い慣れた SQL スキルを使用して、大規模な構造化データセットまたは半構造化データセットに対するモデルを構築し、運用できます。また、BigQuery ML では、Vertex AI の大規模言語モデル（LLM）と Cloud AI API を使用して、AI 機能をデータ分析ワークフローに統合することもできます。
（BigQuery ML の概要 | Google Cloud）
今回は、このBigQuery MLの力を借りて、ノンコーディング（SQLのみ）で回帰予測問題に挑戦します。
2. 実践：Kaggleデータで住宅価格を予測する
ここからは、Kaggleで最も有名なコンペの一つである「House Prices: Advanced Regression Techniques」のデータを使って、実際のモデル構築手順を追っていきます。
Step 1: データの準備とBigQueryへのアップロード
まず、Kaggleのサイトからtrain.csv（学習データ）とtest.csv（予測用データ）をダウンロードします。
次に、ダウンロードしたCSVファイルをBigQueryにロードします。
- Google Cloud コンソールでBigQueryを開き、任意の名前でデータセットを作成します（例:
kaggle_house_prices）。 - 作成したデータセットの横にある︙アイコンから「テーブルを作成」を選択します。
- 「テーブルの作成元」を「アップロード」にし、
train.csvを選択します。 - テーブル名を
trainとし、「スキーマ」の「自動検出」にチェックを入れてテーブルを作成します。 - 同様の手順で
test.csvもtestテーブルとして作成します。
これで、分析の準備完了です。
Step 2: モデルの学習 (CREATE MODEL)
いよいよモデルを作成します。BigQueryのSQLエディタに以下のクエリを貼り付け、your-project-idの部分をご自身のプロジェクトIDに書き換えて実行してください。
-- SQLクエリ: 住宅価格予測モデルを作成する
CREATE OR REPLACE MODEL `your-project-id.kaggle_house_prices.house_price_model`
OPTIONS(
model_type='BOOSTED_TREE_REGRESSOR', -- モデルタイプ: 勾配ブースティング回帰
input_label_cols=['SalePrice'], -- 予測したいターゲットの列（目的変数）
enable_global_explain=TRUE -- モデルの解釈性を有効にする
) AS
-- サブクエリで前処理したデータをモデルの学習データとして使用する
SELECT
-- サブクエリの結果から、予測に不要なId列を除いたすべての列を使用
* EXCEPT(Id)
FROM (
-- このサブクエリ内で、列名のリネームとNULL値の処理を行う
SELECT
-- 問題のある列名を英字始まりの名前に変更（エイリアス）
`1stFlrSF` AS FirstFlrSF,
`2ndFlrSF` AS SecondFlrSF,
`3SsnPorch` AS ThreeSsnPorch,
-- NULL値の処理（例）
COALESCE(LotFrontage, 0) AS LotFrontage,
COALESCE(Alley, 'None') AS Alley,
-- 上記で処理済みの元の列を除いた、残りのすべての列
* EXCEPT(`1stFlrSF`, `2ndFlrSF`, `3SsnPorch`, LotFrontage, Alley)
FROM
`your-project-id.kaggle_house_prices.train`
)
WHERE
-- 目的変数がNULLの行は学習データから除外
SalePrice IS NOT NULL;
💡ポイント：なぜサブクエリで前処理？
このデータセットには、1stFlrSFのように数字で始まる列名が含まれています。これはSQLの識別子のルールに反するため、そのままではエラーになります。そこで、サブクエリを使ってFirstFlrSFのように英字始まりの名前に変更（エイリアス）しています。
また、COALESCE関数で欠損値（NULL）を特定の値（0や'None'）に置き換える処理も同時に行っています。この**「学習直前の前処理」をSQLだけで完結できる**のがBigQuery MLの美しいところです。
クエリを実行するとモデルの学習が始まります。データは小さいですが、BQMLが内部で最適なモデル設定を自動探索するため、5分〜15分程度かかります。※私の時は、17分程度かかりました....
Step 3: モデルの評価 (ML.EVALUATE)
モデルが完成したら、その性能を評価します。これもSQL一発で実行できます。
SELECT
*
FROM
ML.EVALUATE(MODEL `your-project-id.kaggle_house_prices.house_price_model`);```
このクエリを実行すると、MAE（平均絶対誤差）やR2スコアといった、回帰モデルの主要な評価指標が自動で計算されます。
Step 4: テストデータで予測 (ML.PREDICT)
最後に、学習済みモデルを使ってtestテーブルの住宅価格を予測してみましょう。
ここでも重要なのは、学習時と全く同じ前処理をテストデータにも適用することです。
-- SQLクエリ: Kaggle提出用のフォーマットで結果を抽出
SELECT
Id,
predicted_SalePrice AS SalePrice -- 列名をKaggleが要求する'SalePrice'に変更
FROM
ML.PREDICT(
MODEL `your-project-id.kaggle_house_prices.house_price_model`,
(
-- 学習時と全く同じ前処理をtestテーブルに適用
SELECT
`1stFlrSF` AS FirstFlrSF,
`2ndFlrSF` AS SecondFlrSF,
`3SsnPorch` AS ThreeSsnPorch,
COALESCE(LotFrontage, 0) AS LotFrontage,
COALESCE(Alley, 'None') AS Alley,
* EXCEPT(`1stFlrSF`, `2ndFlrSF`, `3SsnPorch`, LotFrontage, Alley)
FROM
`your-project-id.kaggle_house_prices.test`
)
);
このクエリを実行すれば、予測結果を確認できます。
予測と実際の値を折れ線グラフで見てみましたが、なかなかの精度じゃないでしょうか？
3. 結果の評価
さて、今回のモデルの性能は正確にはどの程度のものだったのでしょうか？
ML.EVALUATEで得られた（あるいは自分で計算した）スコアがこちらです。
| mean_absolute_error | root_mean_squared_error | r2_score |
|---|---|---|
| $16,300 | $26,377 | 0.867 |
この結果は、最初のモデルとしては非常に優秀です！
- R2スコアが0.867: これは、モデルが住宅価格のばらつきの約87%を説明できていることを意味し、非常に高い精度です。
- MAEが$16,300: 予測が実際の価格から平均して約1万6千ドルずれていることを示します。データ全体の価格帯を考えれば、これはかなり良い数値です。
Kaggleコミュニティと比べてどうか？
このコンペの評価指標はRMSLE（対数平均二乗誤差）ですが、今回のRMSEスコアを元に換算すると、KaggleのPublic Leaderboardで上位30%〜40%あたりに食い込める可能性のあるスコアです。
これは、データサイエンティストが様々なテクニック（特徴量エンジニアリング、複数のモデルの組み合わせなど）を駆使して精度を競う中で、たった1本のSQLクエリで中級者レベルのベースラインを達成できたことを意味します。専門的なスキルがなくとも、強力なツールを使えば高いレベルの結果を出せるという素晴らしい例です。
4. まとめ
- BigQuery MLを使えば、使い慣れたSQLだけで、データの準備からモデルの学習、評価、予測までを一気通貫で実行できます。
-
BOOSTED_TREE_REGRESSORのような高度なモデルタイプを選択すれば、複雑なパラメータチューニングをBigQueryが自動で行ってくれるため、手軽に高精度なモデルが構築可能です。 - 今回はSQL数行で、Kaggleコンペでも通用するレベルの予測モデルを作成することができました。
機械学習への第一歩として、あるいは日々の分析業務の効率化ツールとして、BigQuery MLは非常に強力な選択肢です。この記事が、皆さんのデータ分析・機械学習ライフの新たな一歩となれば幸いです！