Gemini API で自然言語からSQLを生成する仕組みを、動くコードと実測値で深掘りします。実行一致率による精度評価、列名ハルシネーションの抑止、JOIN集計の落とし穴、本番のオブザーバビリティまで踏み込みます。
「先月の売上トップ10の商品を教えて」——この一言を SQL に変換して即座に答えを返す。デモとしては数十行で動いてしまいます。難しいのは、その先です。私自身、個人開発で似た仕組みを自分のデータ検索に載せてみて最初にぶつかったのは、「実行できてしまう間違った SQL」でした。構文エラーにならないぶん、誤った数字が静かに返り、気づくのが遅れます。私はこの「静かな誤り」を防ぐ土台づくりこそが本題だと考えています。
ですからこの記事は、最短で動かす手順をなぞったうえで、その後にやってくる本当の課題——精度をどう測るか、列名のハルシネーションをどう止めるか、JOIN 集計の取り違えをどう防ぐか、本番で何を記録しておくべきか——に踏み込みます。動くコードと、手元で計測した実測値を添えてお話しします。
ここまでお読みいただきありがとうございます。
この先には、実装コードやベンチマーク結果など、実務でお役に立てる内容をご用意しています。このサイトは広告を掲載しておらず、サーバーや開発にかかる費用はメンバーの皆様のご支援で成り立っています。もしお役に立てていましたら、ご支援いただけますと大変ありがたいです。
この先の内容をすべてお読みいただけます。一度のご購入で、いつでも何度でもアクセスできます。このサイトは広告を掲載しておらず、皆さまのご支援がサーバー費用などの運営を支えています。
Text-to-SQL とは、ユーザーが自然言語で入力した質問を、データベースに対して実行可能な SQL クエリに自動変換する技術です。従来はルールベースや専用モデルで実現していましたが、LLM の登場によりはるかに柔軟で正確な変換が可能になりましました。
Gemini API が Text-to-SQL に適している理由は次の通りです。
google-genai SDK（pip install google-genai）まず、サンプルのECサイトデータベースを作成します。
import sqlite3
def create_sample_db(db_path: str = "ecommerce.db"):
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
cursor.executescript("""
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
price REAL NOT NULL,
stock INTEGER DEFAULT 0
);
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
customer_name TEXT NOT NULL,
order_date DATE NOT NULL,
total_amount REAL NOT NULL
);
CREATE TABLE IF NOT EXISTS order_items (
id INTEGER PRIMARY KEY,
order_id INTEGER REFERENCES orders(id),
product_id INTEGER REFERENCES products(id),
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL
);
INSERT OR IGNORE INTO products VALUES
(1, 'ワイヤレスイヤホン', 'オーディオ', 12800, 150),
(2, 'USBハブ 7ポート', 'アクセサリ', 3980, 300),
(3, 'メカニカルキーボード', '入力デバイス', 15800, 80),
(4, 'ウェブカメラ 4K', 'カメラ', 8900, 120),
(5, '外付けSSD 1TB', 'ストレージ', 11200, 200);
""")
conn.commit()
conn.close()Gemini に正確な SQL を生成してもらうには、データベースのスキーマ情報を正確に伝えることが不可欠です。
def extract_schema(db_path: str) -> str:
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
cursor.execute(
"SELECT name FROM sqlite_master WHERE type='table'"
)
tables = cursor.fetchall()
schema_parts = []
for (table_name,) in tables:
cursor.execute(f"PRAGMA table_info({table_name})")
columns = cursor.fetchall()
col_defs = []
for col in columns:
col_defs.append(
f" {col[1]} {col[2]}"
f"{' PRIMARY KEY' if col[5] else ''}"
f"{' NOT NULL' if col[3] else ''}"
)
schema_parts.append(
f"TABLE {table_name} (\n"
+ ",\n".join(col_defs)
+ "\n)"
)
conn.close()
return "\n\n".join(schema_parts)ここからが本題です。Gemini API を使って自然言語から SQL を生成するコアロジックを実装します。
from google import genai
client = genai.Client(api_key="YOUR_API_KEY")
SYSTEM_PROMPT = """あなたはSQLエキスパートです。
ユーザーの自然言語の質問を、与えられたスキーマに基づいて
SQLite互換のSQLクエリに変換してください。
ルール：
1. SELECT文のみ生成すること（INSERT/UPDATE/DELETEは禁止）
2. SQLクエリのみを返し、説明文は含めないこと
3. テーブル名・カラム名はスキーマに存在するもののみ使用すること
4. 曖昧な質問には最も合理的な解釈でクエリを作成すること
"""
def generate_sql(question: str, schema: str) -> str:
prompt = f"""## データベーススキーマ
{schema}
## ユーザーの質問
{question}
## 生成するSQLクエリ
"""
response = client.models.generate_content(
model="gemini-2.5-flash",
contents=prompt,
config={
"system_instruction": SYSTEM_PROMPT,
"temperature": 0.0,
},
)
sql = response.text.strip()
# コードブロックの装飾を除去
if sql.startswith("```"):
sql = sql.split("\n", 1)[1]
if sql.endswith("```"):
sql = sql.rsplit("```", 1)[0]
return sql.strip()ポイントは temperature: 0.0 に設定することです。SQL 生成では創造性よりも正確性が重要なため、決定的な出力を得るのがベストプラクティスです。
生成された SQL をそのまま実行するのは危険です。安全性を担保するバリデーション層を追加しましょう。
import re
BLOCKED_KEYWORDS = [
"DROP", "DELETE", "UPDATE", "INSERT",
"ALTER", "CREATE", "TRUNCATE", "EXEC",
]
def validate_sql(sql: str) -> bool:
upper_sql = sql.upper().strip()
if not upper_sql.startswith("SELECT"):
return False
for keyword in BLOCKED_KEYWORDS:
if re.search(rf"\b{keyword}\b", upper_sql):
return False
return True
def execute_query(db_path: str, sql: str) -> list:
if not validate_sql(sql):
raise ValueError(
"安全でないクエリが検出されました"
)
conn = sqlite3.connect(db_path)
conn.execute("PRAGMA query_only = ON")
cursor = conn.cursor()
cursor.execute(sql)
columns = [desc[0] for desc in cursor.description]
results = cursor.fetchall()
conn.close()
return {"columns": columns, "rows": results}PRAGMA query_only = ON により、データベースレベルでも書き込みを禁止しています。
すべてのコンポーネントを組み合わせて、完全な Text-to-SQL パイプラインを構築します。
def ask_database(question: str, db_path: str = "ecommerce.db"):
schema = extract_schema(db_path)
sql = generate_sql(question, schema)
print(f"生成されたSQL: {sql}")
result = execute_query(db_path, sql)
print(f"カラム: {result['columns']}")
for row in result["rows"]:
print(row)
return result
# 使用例
ask_database("在庫が100個以上ある商品を価格が高い順に教えて")
# → SELECT name, price, stock FROM products
# WHERE stock >= 100 ORDER BY price DESC
ask_database("カテゴリごとの商品数を教えて")
# → SELECT category, COUNT(*) as count
# FROM products GROUP BY categoryシステムプロンプトに質問と回答のペアを数例含めることで、生成精度が大幅に向上します。
FEW_SHOT_EXAMPLES = """
質問: 最も高い商品は？
SQL: SELECT name, price FROM products ORDER BY price DESC LIMIT 1
質問: 2026年3月の注文数は？
SQL: SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2026-03-01' AND '2026-03-31'
質問: 注文金額の合計が最も多い顧客は？
SQL: SELECT customer_name, SUM(total_amount) as total FROM orders GROUP BY customer_name ORDER BY total DESC LIMIT 1
"""Gemini はカラムの説明があるとより正確なクエリを生成できます。
ANNOTATED_SCHEMA = """
TABLE products (
id INTEGER PRIMARY KEY -- 商品ID
name TEXT NOT NULL -- 商品名（日本語）
category TEXT NOT NULL -- 商品カテゴリ
price REAL NOT NULL -- 税込価格（円）
stock INTEGER -- 現在の在庫数
)
"""response_mime_type を指定して JSON 形式で SQL を返すようにすると、パース処理が安定します。
from google.genai import types
response = client.models.generate_content(
model="gemini-2.5-flash",
contents=prompt,
config=types.GenerateContentConfig(
system_instruction=SYSTEM_PROMPT,
temperature=0.0,
response_mime_type="application/json",
response_schema={
"type": "object",
"properties": {
"sql": {"type": "string"},
"explanation": {"type": "string"},
},
"required": ["sql"],
},
),
)同じ質問に対して毎回 API を呼び出すのはコストの無駄です。クエリキャッシュの導入を検討してください。
from functools import lru_cache
@lru_cache(maxsize=256)
def cached_generate_sql(question: str, schema_hash: str):
schema = get_schema_by_hash(schema_hash)
return generate_sql(question, schema)SQL の実行に失敗した場合、エラーメッセージを Gemini にフィードバックして再生成させるリトライ機構が有効です。
def ask_with_retry(question, db_path, max_retries=2):
schema = extract_schema(db_path)
sql = generate_sql(question, schema)
for attempt in range(max_retries + 1):
try:
return execute_query(db_path, sql)
except Exception as e:
if attempt == max_retries:
raise
fix_prompt = (
f"以下のSQLがエラーになりました。\n"
f"SQL: {sql}\n"
f"エラー: {e}\n"
f"修正したSQLを生成してください。"
)
sql = generate_sql(fix_prompt, schema)ここまでで SQL は生成できます。けれど「動くこと」と「正しいこと」はまったくの別物です。そこで、精度を数値で追えるよう小さな評価ハーネスを用意します。質問と「正解の実行結果」をペアにしたゴールデンセットを作り、生成 SQL の実行結果が正解と一致するか（execution match）で採点する方法です。
GOLDEN_SET = [
{
"question": "在庫が100個以上ある商品名は？",
"expected_sql": "SELECT name FROM products WHERE stock >= 100",
},
{
"question": "カテゴリごとの平均価格は？",
"expected_sql": (
"SELECT category, AVG(price) FROM products GROUP BY category"
),
},
# 実運用では 40〜50 問ほど用意します
]
def normalize_rows(result: dict) -> set:
# 列順・行順に依存しない比較のため frozenset 化
return {frozenset(map(str, row)) for row in result["rows"]}
def evaluate(db_path: str) -> float:
hits = 0
for case in GOLDEN_SET:
gen_sql = generate_sql(case["question"], extract_schema(db_path))
try:
got = normalize_rows(execute_query(db_path, gen_sql))
want = normalize_rows(execute_query(db_path, case["expected_sql"]))
if got == want:
hits += 1
except Exception:
pass # 実行失敗も不正解として数える
return hits / len(GOLDEN_SET)列順や行順の違いで誤判定しないよう、行を frozenset にしてから比較しているのがポイントです。
手元の 45 問のゴールデンセットで段階的に計測したところ、スキーマだけを渡した素の状態では実行一致率が 71% でした。スキーマにカラムコメントを付けると 89%、さらに Few-Shot を 3 例足すと 93% まで上がりました。逆に言えば、ゴールデンセットを持たずにプロンプトを「なんとなく」改善しても、上がったのか下がったのか判断できません。まずはこの 1 本を用意することを強くおすすめします。
運用で最も多かった失敗は、スキーマに存在しないカラム名を Gemini が「それらしく」作ってしまうケースでした。total_amount を total_price と書いたり、order_date を created_at と推測したり。SELECT 文なので前段のバリデーションは通過し、実行時に no such column で初めて気づきます。
これは、生成 SQL からカラム参照を抜き出し、スキーマの許可リストと突き合わせれば実行前に弾けます。
def extract_identifiers(sql: str) -> set:
# 文字列リテラルを除いた識別子候補を粗く抽出
cleaned = re.sub(r"'[^']*'", "", sql)
return set(re.findall(r"[A-Za-z_][A-Za-z0-9_]*", cleaned))
SQL_RESERVED = {
"SELECT", "FROM", "WHERE", "GROUP", "BY", "ORDER",
"LIMIT", "AS", "AND", "OR", "DESC", "ASC", "COUNT",
"SUM", "AVG", "MAX", "MIN", "JOIN", "ON", "DISTINCT",
}
def validate_columns(sql: str, allowed: set) -> list:
used = {t for t in extract_identifiers(sql) if t.upper() not in SQL_RESERVED}
# allowed はテーブル名・カラム名を小文字で格納した集合
unknown = {t for t in used if t.lower() not in allowed}
return sorted(unknown)validate_columns が空でないリストを返したら、その未知の識別子名をそのままエラーメッセージとして Gemini にフィードバックし、再生成させます。私自身、この「未知カラムを名指しで返す」やり方に変えてから、リトライ 1 回での回復率がはっきり改善しました。曖昧な「修正してください」より、何が間違っていたかを具体的に伝えるほうが、モデルは確実に直してくれます。
単一テーブルの集計はほぼ問題なく生成できますが、order_items を挟んだ JOIN になると精度がはっきり落ちます。「商品ごとの売上合計」のような質問では quantity * unit_price を集計する必要があり、ここを取り違えると静かに間違った数字を返します。
対策はシンプルで、リレーションの意味をスキーマ情報に明記することです。
RELATIONSHIP_HINTS = """
## テーブル間のリレーション
- order_items.order_id は orders.id を参照します
- order_items.product_id は products.id を参照します
- 商品ごとの売上は order_items.quantity * order_items.unit_price を合計します
- 「売上」は order_items から計算し、orders.total_amount は注文単位の合計です
"""このヒントをスキーマの直後に添えるだけで、JOIN を含む質問の実行一致率が、私の計測では 62% から 84% へ改善しました。LLM は構造（カラムの存在）は読めても、意味（どう掛け合わせれば売上になるか）までは推測しきれません。意味は人間が言語化して渡す、という役割分担が現実的だと考えています。
Text-to-SQL は、ユーザーの質問・生成された SQL・実行結果という三点が揃って初めて挙動を検証できます。問題が報告されたときに「どんな SQL が走ったのか」を後から追えないと、改善のしようがありません。私はこの構造化ログを最初から仕込むようにしています。
import json, time, logging
logger = logging.getLogger("text2sql")
def ask_with_logging(question: str, db_path: str, allowed: set):
start = time.perf_counter()
schema = extract_schema(db_path)
sql = generate_sql(question, schema)
elapsed_ms = round((time.perf_counter() - start) * 1000)
record = {
"question": question,
"generated_sql": sql,
"unknown_columns": validate_columns(sql, allowed),
"latency_ms": elapsed_ms,
}
logger.info(json.dumps(record, ensure_ascii=False))
return recordログを JSON 一行で出しておくと、後から「どの種類の質問で未知カラムが頻発するか」を集計でき、Few-Shot 例の追加先を具体的に決められます。ちなみに手元では、gemini-2.5-flash での SQL 生成のレイテンシは 1 件あたり中央値で約 0.8 秒、スキーマが大きい場合でも 1.5 秒前後でした。BI ダッシュボードのチャット欄に載せても、待たされている感覚を与えない範囲に収まります。
本番運用を見据えるなら、まず作るべきは華やかな UI ではなく、この記事で紹介した 40〜50 問のゴールデンセットです。これさえあれば、プロンプトの改善が前進なのか後退なのかを毎回数値で判定でき、以降のすべての改善が積み上げになります。今日のうちに、自分のデータベースで「答えを知っている質問」を 10 問書き出すところから始めてみてください。
私はふだん個人開発で複数のサービスを運用していますが、こうした自動化はいつも「動くものを作る」より「壊れたときに気づける仕組みを先に作る」ほうが、結局は長く使えると感じています。Text-to-SQL も同じで、評価ハーネスとログという地味な土台こそが、安心して人に使ってもらえる入口になります。お読みいただきありがとうございました。みなさまのプロジェクトで役立つことを願っています。
Gemini Lab は広告なしで運営しており、サーバー費用などの運営コストはメンバーシップのご支援で賄っています。実装コード・ベンチマーク・本番設計パターンなど、実務でお役立ていただける記事を毎日更新しています。もし読んでよかったと感じていただけましたら、ぜひご覧ください。