データドリブン革命！BigQuery×ChatGPTで収穫量予測SQLを爆速生成

DXアイデア
2025.05.11

はじめに

収穫量予測に挑戦したいけれど、複雑なSQLクエリに頭を悩ませていませんか？ 大量の農業データを活用したいけれど、どこから手を付ければ良いか分からない…そんな課題を抱える農業DX担当者の方へ。本記事では、Google BigQueryとChatGPTを連携させ、専門知識がなくても収穫量予測SQLを自動生成する画期的な方法を徹底解説します。AIの力を借りて、データドリブンな農業経営を加速させましょう。

収穫量予測にAIを活用する意義

近年、気候変動や病害虫の発生など、農業を取り巻く環境はますます不確実性を増しており、安定的な収穫量の確保は重要な経営課題となっています。AIを活用した収穫量予測は、過去の気象データ、土壌データ、生育データ、栽培履歴などの膨大なデータを分析し、将来の収穫量を高精度に予測することを可能にします。これにより、適切なタイミングでの施肥や農薬散布、収穫時期の最適化、そして販売戦略の策定など、データに基づいた意思決定を支援し、収益性の向上に貢献します。例えば、過去のデータから特定の気象条件が収穫量に与える影響を定量的に把握したり、病害の発生リスクが高い時期を予測したりすることができます。これにより、経験や勘に頼るだけでなく、科学的な根拠に基づいたリスク管理と収益最大化が可能になるのです。

BigQueryとChatGPT連携の準備

BigQueryとChatGPTを連携させるためには、いくつかの準備が必要です。まず、収穫量予測に必要な各種農業データをBigQueryに格納します。これには、過去の収穫量データ、気象庁のAPIから取得した気象データ、土壌分析データ、センサーデータなどが含まれます。データの形式は、BigQueryがサポートするCSVやJSONなどが一般的です。次に、ChatGPTのAPIを利用するためのAPIキーを取得します。OpenAIのプラットフォームに登録し、API利用プランに加入することでAPIキーが発行されます。そして、BigQueryとChatGPTのAPIを連携させるための仲介となるプログラミング環境を準備します。Google Cloud FunctionsやGoogle Apps Scriptなどを利用することで、BigQueryに格納されたデータ情報をChatGPTに送信し、生成されたSQLクエリを受け取るといった一連の処理を自動化することが可能です。

SQL自動生成の具体的な手順

環境準備

Google Cloud プロジェクトの準備

  • BigQuery API を有効化
  • サービスアカウントを作成し、JSON キーファイル（例：gcp-key.json）をダウンロード
  • サービスアカウントに BigQuery の「BigQuery Job User」「BigQuery Data Viewer」ロールを付与

OpenAI API キー取得

  • OpenAI にサインアップ → ダッシュボード → API キーを発行 → 環境変数 OPENAI_API_KEY に設定

Python 仮想環境とライブラリ

python3 -m venv venv
source venv/bin/activate
pip install google-cloud-bigquery openai python-dotenv

環境変数設定（プロジェクトルートに .env を用意）

GOOGLE_APPLICATION_CREDENTIALS=./gcp-key.json
OPENAI_API_KEY=sk-XXXX...

BigQuery スキーマ取得

まず、対象テーブルのカラム名とデータ型を動的に取得し、プロンプトに含めることで、ChatGPT が適切な SQL を生成しやすくします。

from google.cloud import bigquery

def fetch_table_schema(project: str, dataset: str, table: str) -> str:
    client = bigquery.Client(project=project)
    table_ref = client.dataset(dataset).table(table)
    table = client.get_table(table_ref)
    # 各フィールド名と型を "name:type" の形でリスト化
    cols = [f"{field.name}:{field.field_type}" for field in table.schema]
    return ", ".join(cols)

# 例
schema_info = fetch_table_schema(
    project="my-project",
    dataset="agri_data",
    table="rice_yields"
)
print("スキーマ情報:", schema_info)

プロンプト設計

ChatGPT へ渡す「指示文（プロンプト）」の例：

以下の BigQuery テーブルに基づいて、今年の水稲収穫量（yield）の予測を行うSQLを出力してください。
- プロジェクト: my-project
- データセット: agri_data
- テーブル: rice_yields
- カラム: {schema_info}
- 過去5年間のデータを使い、平均気温（avg_temp）と降水量（rainfall）の影響を組み込む
- 出力は BigQuery Standard SQL

上記の {schema_info} にステップ2で得た文字列を埋め込みます。

SQL 自動生成スクリプト（Python 全体例）

import os
import openai
from google.cloud import bigquery
from dotenv import load_dotenv

# 環境変数読み込み
load_dotenv()
openai.api_key = os.environ["OPENAI_API_KEY"]

# BigQuery クライアント
bq_client = bigquery.Client()

def generate_sql_with_chatgpt(prompt: str) -> str:
    resp = openai.ChatCompletion.create(
        model="gpt-4o-mini",
        messages=[{"role": "user", "content": prompt}],
        temperature=0.0,
        max_tokens=512
    )
    # チャット応答から SQL 部分だけを抜き出し
    sql = resp.choices[0].message.content.strip()
    return sql

def execute_sql(sql: str):
    query_job = bq_client.query(sql)
    return query_job.result().to_dataframe()

def main():
    project  = "my-project"
    dataset  = "agri_data"
    table    = "rice_yields"
    schema_info = fetch_table_schema(project, dataset, table)

    # 3. プロンプト生成
    prompt = f"""
以下の BigQuery テーブルに基づいて、今年の水稲収穫量（yield）の予測を行うSQLを出力してください。
- プロジェクト: {project}
- データセット: {dataset}
- テーブル: {table}
- カラム: {schema_info}
- 過去5年間のデータを使い、平均気温（avg_temp）と降水量（rainfall）の影響を組み込む
- 出力は BigQuery Standard SQL
"""
    # 4. ChatGPT で SQL を生成
    sql = generate_sql_with_chatgpt(prompt)
    print("=== 生成された SQL ===")
    print(sql)

    # 5. SQL 実行
    df = execute_sql(sql)
    print("=== 実行結果（上位 5 行）===\n", df.head())

if __name__ == "__main__":
    from google.cloud import bigquery  # fetch_table_schema 用
    # fetch_table_schema 関数も同一スクリプト内に定義してください
    main()

ポイント解説

  • temperature=0.0：SQL は deterministic に。
  • max_tokens=512：SQL の長さに合わせて調整。
  • 応答の整形が必要な場合は、正規表現で SELECT ...; のブロックを抽出すると安全です。

生成 SQL の実行と結果取得

  • 上記スクリプトを実行すると、ターミナルに生成 SQL が表示され、続けて BigQuery で実行した結果が DataFrame として出力されます。
  • 重要なことは、生成された SQL を必ず人の目でチェックし、期待する集計ロジックになっているかを確認するフェーズを挟むことです。

予測結果の確認・フィードバック

  1. 結果の可視化
    • Pandas/Matplotlib などで折れ線グラフ化
    • Google Data Studio／Looker Studio に連携
  2. プロンプト微調整
    • 予測の条件（期間、モデル形式）を変えたい場合は再度プロンプトを編集し、スクリプトを再実行
  3. 定期自動化
    • Cloud Functions や Cloud Run にデプロイし、Pub/Sub スケジュールで自動実行
    • 結果を BigQuery に書き戻すか、BI ツールのテーブルとして更新

以上が、BigQuery と ChatGPT を組み合わせて「収穫量予測用 SQL を自動生成・実行」する具体的手順とサンプルコードです。

予測精度向上のためのポイント

BigQueryとChatGPTを活用した収穫量予測の精度を向上させるためには、いくつかの重要なポイントがあります。まず、BigQueryに格納するデータの質と量を確保することが不可欠です。過去のデータが豊富で、かつ正確であるほど、AIはより信頼性の高い予測を行うことができます。データの欠損値や異常値の処理、異なるデータソース間の整合性の確保なども重要です。次に、ChatGPTに与える指示（プロンプト）を工夫することが精度向上に繋がります。予測の目的や必要な情報を明確かつ具体的に伝えることで、より意図に沿ったSQLクエリを生成させることができます。例えば、「〇〇地域の過去5年間の水稲の反収を、平均気温と降水量を考慮して予測するSQL」のように、具体的な条件を加えることが有効です。また、生成されたSQLクエリの結果を検証し、必要に応じてプロンプトを修正したり、異なるモデルを試したりすることも重要です。さらに、予測結果を実際の収穫量と比較し、モデルの精度を定期的に評価し、改善を繰り返すことで、より高精度な収穫量予測システムを構築することができます。

まとめ

本記事では、BigQueryとChatGPTを連携させ、専門知識 없이 収穫量予測SQLを自動生成する方法を解説しました。AIの力を活用することで、データドリブンな農業経営を推進し、収益性の向上とリスク管理に貢献できます。

コメント

タイトルとURLをコピーしました