はじめに
DXをしようとした時に、まず「大量データはあるが複雑なSQLでつまずく」──そんな悩みを抱えていませんか？
最新の先端研究によると、AIで構築した収穫量予測モデルは従来手法より平均12〜18％も精度が向上します。さらに、温度と降水量が収量に最も強く影響することも確認されており、したがってこれらの指標を活用すればリスクを抑えた営農計画が立てられます。
そこで本稿では、Google BigQueryとChatGPTを連携させ、専門知識がなくても“爆速”で収穫量予測SQLを生成する手順をステップバイステップで解説します。
収穫量予測にAIを使う３つのメリット
- 高精度 – 複数年・多変量データを機械学習で解析し、従来比で最大18％のMAPE低減
- 迅速な意思決定 – 気象・センサーデータをリアルタイム集計し、その結果施肥や収穫時期を即座に最適化
- 収益最大化 – 精度向上により過剰施肥や過小収量リスクを回避し、最終的に販売計画を立てやすくなる
BigQuery×ChatGPT 連携フロー
| ステップ | ツール | 主な作業 | コツ |
|---|---|---|---|
| ① データ格納 | BigQuery | 収穫量・気象・土壌・センサーデータをロード | スキーマを揃え、加えて欠損補完 |
| ② API設定 | OpenAI | APIキー取得・環境変数設定 | OPENAI_API_KEY を安全管理 |
| ③ プロンプト生成 | Python(GAS可) | テーブルスキーマを動的に挿入 | 具体的な予測条件を明記 |
| ④ SQL生成 | ChatGPT | 指示文からStandard SQL生成 | temperature=0.0 で再現性確保 |
| ⑤ 実行・検証 | BigQuery/Pandas | SQL実行→結果をDataFrame取得 | 必ず人の目でロジック確認 |
| ⑥ BI連携 | Looker Studio | グラフ化し経営層へ共有 | 最後にスケジュール更新を自動化 |
実装サンプル（簡易版）
想定環境
- macOS／Linux（Windows WSL2 でも可）
- Python 3.9+
- gcloud CLI >= 470.0.0
- BigQuery Standard SQL
事前準備 — GCP と OpenAI
| 手順 | コマンド／UI | 目的 |
|---|---|---|
| ① プロジェクト選択 | gcloud config set project <PROJECT_ID> | 以降の操作を同一プロジェクトに固定 |
| ② BigQuery API 有効化 | gcloud services enable bigquery.googleapis.com | クエリ実行を許可 |
| ③ サービスアカウント作成 | gcloud iam service-accounts create bq-chatgpt-sa | バッチ用認証主体 |
| ④ 権限付与 | <ul><li>roles/bigquery.jobUser</li><li>roles/bigquery.dataViewer</li></ul> | 実行・読み取り権限のみ |
| ⑤ キーファイル生成 | gcloud iam service-accounts keys create gcp-key.json --iam-account bq-chatgpt-sa@<PROJECT_ID>.iam.gserviceaccount.com | ローカル／Cloud Functions 共用 |
| ⑥ OpenAI API キー取得 | https://platform.openai.com/api-keys | .env に保存 (OPENAI_API_KEY=sk-...) |
サーバーレス運用 — Cloud Functions + Cloud Scheduler
3‑1. ディレクトリ構成
bashコピーする編集する.
├── main.py # 上記コードを Cloud Functions 仕様に変更
├── requirements.txt
├── gcp-key.json # ★本番は Secret Manager 推奨
└── .env # ★同上
3‑2. Cloud Functions 用の main.py 変更点
def entrypoint(event, context):
schema = fetch_table_schema(PROJECT, DATASET, TABLE)
sql = generate_sql(schema)
df = run_query(sql) # 実行結果を変数保持
# 例: df を BigQuery テーブル "agri_data.yield_pred" に書き戻す
bigquery.Client().load_table_from_dataframe(
df, f"{PROJECT}.{DATASET}.yield_pred", if_exists="replace"
).result()
entrypoint がデフォルトハンドラ名。任意で変更可。
3‑3. デプロイ
gcloud functions deploy yield-sql-gen \
--runtime python311 \
--trigger-topic yield-sql-trigger \
--entry-point entrypoint \
--service-account bq-chatgpt-sa@${PROJECT_ID}.iam.gserviceaccount.com \
--region asia-northeast1 \
--env-vars-file .env.yaml
Secret Manager 推奨
--set-secrets "OPENAI_API_KEY=projects/<P>/secrets/openai_key:latest"のように渡すと環境変数にマッピングされます。
3‑4. Pub/Sub & Cloud Scheduler
# Pub/Sub トピックは deploy 時に自動作成されたものを再利用
gcloud scheduler jobs create pubsub yield-sql-daily \
--schedule "0 6 * * *" \ # 毎日 06:00 (東京) 実行
--time-zone "Asia/Tokyo" \
--topic yield-sql-trigger \
--message-body "{}"運用チェックリスト
| チェック項目 | 方法 |
|---|---|
| SQL 誤集計 | 生成結果を BigQuery UI で EXPLAIN し、結合数・スキャン量を確認 |
| コスト監視 | BigQuery 定額クエリ上限・アラートポリシーを設定 |
| OpenAI 予算 | Usage ダッシュボードで月次請求をウォッチ |
| ログ確認 | Cloud Logging → resource.type=”cloud_function” で絞り込み |
まとめ
- ローカルで動作確認 → Python + .env
- Cloud Functions に移植 → entrypoint 書き換え
- Pub/Sub & Scheduler で完全自動化
このようにChatGPT が毎朝最新データを基に収穫量予測 SQL を生成→実行→結果保存する、無人パイプラインが完成します。
参照：Next-gen agriculture: integrating AI and XAI for precision crop yield predictions
参照：Crop yield prediction in agriculture: A comprehensive review of machine learning and deep learning approaches, with insights for future research and sustainability
コメント