初めに
ChatGPTのAPIが公開されてからというもの、自然言語をインターフェイスにする話はたくさんある。SQLは他言語と比べると、簡単に記述できるが再利用性が低いという特徴があるため、いちいちSQL文を書くより、日本語でデータを抽出できた方が良いというケースはあると思う。
n番煎じだろうが、DBのインターフェイスを日本語にするアイデアを思いついてしまったので検証手順と結果を備忘録として残す。DBに入れるデータはscikit-learnにあるワインのサンプルデータを使っていく。
結論から言うと、日本語からSQL文を生成するのに成功した。以下に手順を示す。
使ったもの
- EC2 (t2.micro)
- RDS (PostgreSQL)
- Python 3.8
- scikit-learn
- pandas
- sqlalchemy
- psycopg2-binary
- openai
環境準備（飛ばしてヨシ）
何はともあれ、環境の準備から
EC2
- AWS Management Console にログインし、「EC2」を選択
- 「インスタンスの作成」ボタンをクリック
- マシンイメージは「Amazon Linux 2 Kernel 5.10 AMI 2.0.20230515.0 x86_64 HVM gp2」を選択
- インスタンスタイプは「t2.micro」
- ssh接続用のキーペアは適当なのを選択。なければ新しく作成
- ネットワーク設定の「編集」を押し、「パブリック IP の自動割り当て」を「有効化」にする
- セキュリティグループはssh（port 22）が許可されているか確認。ソースタイプは「自分のIP」にしとくと良い
- 変更項目は以上なので、「インスタンスを起動」を押す
RDS
- AWS Management Console にログインし、「RDS」を選択
- 「データベースの作成」ボタンをクリック
- データベース作成方法は「標準作成」を選択
- エンジンのオプションは「PostgreSQL」を選択
- テンプレートは「無料利用枠」を選択
- 設定の認証情報は適当に設定。マスターユーザー名とマスターパスワードを使ってDBにアクセスするので、メモしておくように
- 接続は「EC2 コンピューティングリソースに接続」を選択
- EC2インスタンスを選択するためのプルダウンから先ほど作成したEC2を選択
- 以降、特に弄るものもないので「データベースの作成」を押す
- DBが完全に立ち上がるまで数分かかるので、その間にEC2に接続して設定していく
Python とScikit-Learn とかのインストール
# Pythonのインストール
sudo yum update
sudo amazon-linux-extras enable python3.8
sudo yum install python3.8
# 必要パッケージのインストール
pip3.8 install scikit-learn pandas sqlalchemy psycopg2-binary
# 作業ディレクトリを作る
mkdir python_scripts
cd python_scripts
ここからはpythonのファイルを作成していく。
# data_dump.py
import pandas as pd
from sklearn import datasets
from sqlalchemy import create_engine
# ワインデータセットの読み込み
wine = datasets.load_wine()
df = pd.DataFrame(data=wine.data, columns=wine.feature_names)
# PostgreSQL RDSデータベースに接続
engine = create_engine("postgresql+psycopg2://<username>:<password>@<host>:<port>")
# データフレームをデータベースに挿入
df.to_sql("wine", engine, if_exists="replace")
これをpython3.8 data_dump.pyで実行すればDBへデータの格納が完了する。
@<host>:<port>は@<RDSのエンドポイント>:5432みたいになるはず。
試しに、以下のコードを実行すると格納されたデータが全部表示される。
# data_extract.py
import pandas as pd
from sqlalchemy import create_engine
# PostgreSQL RDSデータベースに接続
engine = create_engine("postgresql+psycopg2://<username>:<password>@<host>:<port>")
# SQLクエリの実行
query = """
SELECT * FROM wine
"""
df_from_db = pd.read_sql_query(query, engine)
# データの表示
print(df_from_db)
# 出力結果
[ec2-user@hogehoge python_scripts]$ python3.8 extract.py
index alcohol malic_acid ash alcalinity_of_ash magnesium total_phenols flavanoids nonflavanoid_phenols proanthocyanins color_intensity hue od280/od315_of_diluted_wines proline
0 0 14.23 1.71 2.43 15.6 127.0 2.80 3.06 0.28 2.29 5.64 1.04 3.92 1065.0
1 1 13.20 1.78 2.14 11.2 100.0 2.65 2.76 0.26 1.28 4.38 1.05 3.40 1050.0
2 2 13.16 2.36 2.67 18.6 101.0 2.80 3.24 0.30 2.81 5.68 1.03 3.17 1185.0
3 3 14.37 1.95 2.50 16.8 113.0 3.85 3.49 0.24 2.18 7.80 0.86 3.45 1480.0
4 4 13.24 2.59 2.87 21.0 118.0 2.80 2.69 0.39 1.82 4.32 1.04 2.93 735.0
.. ... ... ... ... ... ... ... ... ... ... ... ... ... ...
173 173 13.71 5.65 2.45 20.5 95.0 1.68 0.61 0.52 1.06 7.70 0.64 1.74 740.0
174 174 13.40 3.91 2.48 23.0 102.0 1.80 0.75 0.43 1.41 7.30 0.70 1.56 750.0
175 175 13.27 4.28 2.26 20.0 120.0 1.59 0.69 0.43 1.35 10.20 0.59 1.56 835.0
176 176 13.17 2.59 2.37 20.0 120.0 1.65 0.68 0.53 1.46 9.30 0.60 1.62 840.0
177 177 14.13 4.10 2.74 24.5 96.0 2.05 0.76 0.56 1.35 9.20 0.61 1.60 560.0
検証開始
ChatGPTと連携させる為に追加で必要なものをインストール
pip3.8 install openai
先ほど作ったdata_extract.pyの中身を書き換える
# data_extract.py
import pandas as pd
from sqlalchemy import create_engine
import openai
# RDSデータベースに接続
engine = create_engine("postgresql+psycopg2://<username>:<password>@<host>:<port>")
openai.api_key = "YOURAPIKEY"
def completion(new_message_text:str, settings_text:str = '', request:list = []):
system = {"role": "system", "content": settings_text}
request.append(system)
new_message = {"role": "user", "content": new_message_text}
request.append(new_message)
result = openai.ChatCompletion.create(
model="gpt-4",#gpt-3.5-turbo
messages=request
)
response_message = {"role": "assistant", "content": result.choices[0].message.content}
response_message_text = result.choices[0].message.content
return response_message_text
system_settings ="""
# 命令
あなたはプロのソムリエです。あなたはワインのデータベースにアクセスすることができ、クライアントの要求に沿うワインを見つける必要があります。そのため、要求に対して必ずSQL文のみで答えてください。重ねて言います、SQL文のみを出力してください。
## データベース情報
テーブル名: wine
フィールド: index alcohol malic_acid ash alcalinity_of_ash magnesium total_phenols flavanoids nonflavanoid_phenols proanthocyanins color_intensity hue "od280/od315_of_diluted_wines" proline
## 出力例
SELECT *
FROM wine
WHERE alcohol <= 12.5
AND malic_acid <= 1.5
AND flavanoids <= 1.0
ORDER BY alcohol;
## 補足
要求に対する判断基準はあなたの独断で構いません。ただし、平均値を多用することは避けるべきです。
ワインの選出には出来れば多くのフィールドを活用すると良いでしょう。しかし、データベースのフィールドに無いものを絶対に使用してはなりません。
これらのルールを守らなければあなたの責任で大勢の人が死にます。心して回答してください。
以下、クライアントからの要求
"""
def chatInit():
print("Press ctrl + C to end conversation")
try:
# リクエストの受付
user_text = input("どのようなワインを希望されますか？\nYou: ")
query = completion(user_text, system_settings, [])
print("AI:\n" + query + "\n")
# クエリの実行
df_from_db = pd.read_sql_query(query, engine)
print("Search Result:\n" + df_from_db.to_string())
except Exception as e: # エラーオブジェクトを e として取得
# print(f"An error occurred: {e}") # エラーメッセージを出力
print("Ending Conversation...")
if __name__ == "__main__":
chatInit()
プロンプトについて
簡単に意識していることを書く。効能として重複するところはあるが、経験上重ね掛けすると意図した出力結果になりやすい。
命令：
ロールと要求を分かりやすく書く。今回はSQL文だけが欲しかったので、念入りに２回書いておく。
データベース情報：
SQL文を生成してもらう上で、テーブル名・フィールド名は必須。od280/od315_of_diluted_winesだけはスラッシュが入っていて、変な挙動をされては困るので、そういうものはダブルクォーテーションで括る。
出力例：
多くの記事で紹介されている小技だが、出力例があると期待する出力になる確率がグッと高まる。
補足：
ついでに脅迫しておくと良いでしょう。他人の命が掛かっているとGPT君も素直になります。
実行結果
実際に動かす
[ec2-user@hogehoge python_scripts]$ python3.8 data_extract.py
Press ctrl + C to end conversation
どのようなワインを希望されますか？
You: ステーキ肉に合うワイン
AI:
SELECT *
FROM wine
WHERE alcohol >= 12.5
AND total_phenols >= 2.0
AND color_intensity >= 4.0
AND hue <= 1.0
ORDER BY alcohol DESC, color_intensity DESC;
Search Result:
index alcohol malic_acid ash alcalinity_of_ash magnesium total_phenols flavanoids nonflavanoid_phenols proanthocyanins color_intensity hue od280/od315_of_diluted_wines proline
0 3 14.37 1.95 2.50 16.8 113.0 3.85 3.49 0.24 2.18 7.80 0.86 3.45 1480.0
1 158 14.34 1.68 2.70 25.0 98.0 2.80 1.31 0.53 2.70 13.00 0.57 1.96 660.0
2 56 14.22 1.70 2.30 16.3 118.0 3.20 3.00 0.26 2.03 6.38 0.94 3.31 970.0
3 39 14.22 3.99 2.51 13.2 128.0 3.00 3.04 0.20 2.08 5.10 0.89 3.53 760.0
4 45 14.21 4.04 2.44 18.9 111.0 2.85 2.65 0.30 1.25 5.24 0.87 3.33 1080.0
5 177 14.13 4.10 2.74 24.5 96.0 2.05 0.76 0.56 1.35 9.20 0.61 1.60 560.0
6 47 13.90 1.68 2.12 16.0 101.0 3.10 3.39 0.21 2.14 6.10 0.91 3.33 985.0
7 42 13.88 1.89 2.59 15.0 101.0 3.25 3.56 0.17 1.70 5.43 0.88 3.56 1095.0
8 54 13.74 1.67 2.25 16.4 118.0 2.60 2.90 0.21 1.62 5.85 0.92 3.20 1060.0
9 58 13.72 1.43 2.50 16.7 108.0 3.40 3.67 0.19 2.04 6.80 0.89 2.87 1285.0
10 19 13.64 3.10 2.56 15.2 116.0 2.70 3.03 0.17 1.66 5.10 0.96 3.36 845.0
11 143 13.62 4.95 2.35 20.0 92.0 2.00 0.80 0.47 1.02 4.40 0.91 2.05 550.0
....
..
おすすめの候補がたくさん出てきた。実験は成功。
筆者はワインに特段詳しいわけではないので、クエリの妥当性についてChatGPTに質問してみる。
このSQL文は、ステーキ肉に合うワインを選ぶためのものです。以下にその理由を説明します。
alcohol >= 12.5: アルコール度数が高いワインは、風味が豊かでボディ感があり、肉料理とよく合います。ステーキの>ような濃厚な肉料理には、それに対抗できる強いワインが必要です。
total_phenols >= 2.0: フェノールはワインのタンニン含量を表し、ワインに苦みと構造をもたらします。ステーキとよく合うワインは、高いタンニン含量を持つことが多いです。
color_intensity >= 4.0: 色の濃さはワインのボディ感を示す指標となります。色が濃いワインは一般的に、風味が濃厚であり、ステーキのような重たい料理に対抗できます。
hue <= 1.0: ワインの色合い（ヒュー）は、その成熟度や風味の強さを示すものです。低いヒュー値はより強い風味と成熟度を示すため、ステーキのような濃厚な料理に適しています。
最後に、ORDER BY alcohol DESC, color_intensity DESCは、アルコール度数と色の強さで降順にソートしています。>これにより、最もステーキに適したワインが最初に表示されるようになっています。
以上の要素から、このSQL文はステーキに適したワインを選択するためのものと言えます。それぞれの要素はステーキとよく合う特性を考慮したもので、強いワインを選ぶための適切なクエリと言えます。
だそうです。今度ワインの分かる人にも同じ質問をしてみようと思う。
終わりに
タイトルにもある「生成AIでなんか良い感じにSQL文を作ってもらう」は概ね上手く行ったと思う。今回はscikit-learnのサンプルデータ一個だけを使ったが、ちゃんと作りこめばもっと価値のあるものは作れるはず。
例えば、scikit-learnには他にもサンプルデータがたくさんある為（糖尿病の進行状況や生理学的測定・運動測定結果等）、これらを使って、「こういう健康状態の人にはこういったワインがおすすめ」といった提案サービスも作れるかもしれない。
他にも、PCパーツのデータベースがあれば、「6万円で最もパフォーマンスが出るゲーミングPCを組んでください。ただし、手元に既にi5-4460があるのでそれを活用してください」といったリクエストに応えることも活用方法として考えられる。