ChatGPT API を使って BigQuery SQL を自然言語で記述する CLI を作った

クエリで実現したいことを自然言語で書けば ChatGPT が  BigQuery SQL に変換してくれる CLI (Command Line Interface) を [langchain](https://github.com/hwchase17/langchain) で作りました。
https://github.com/algas/bigquery-generator-ai

クエリに関連する BigQuery のテーブル名(複数可)を渡すとそのテーブルのスキーマ(だけ)を取得します。
テーブルのデータ内容は取得しない(データ取得権限を付与しない)ので機密漏えいの心配はありません。

## 対象読者

次のいずれかに当てはまる BigQuery ユーザを対象としています。

- SQL を書くのが苦手な人
- SQL を書くのが苦手な人に SQL を書いてもらう必要がある人
- ChatGPT を使ったアプリケーション開発に興味がある人

## アプリケーションの概要

このツールが何をするのかを簡単に解説します。
ツールへの入力として「クエリで実現したいこと」と「テーブルID」を渡します。
「テーブルID」を使って BigQuery から「テーブルスキーマ」を取得します。
このツールが持っているテンプレートに「クエリで実現したいこと」と「テーブルスキーマ」を展開したものを ChatGPT API に送ります。
少し待つと ChatGPT が SQL を出力します。
数個のテーブルを JOIN して関数を適用する程度の SQL は書けるようです。

## 初期設定

ChatGPT へのユーザ登録と Google Cloud の IAM 設定が必要です。
OpenAI API Key と Google Cloud の Credential を環境変数に登録します。

### ChatGPT API

1. ChatGPT にユーザ登録します
https://platform.openai.com/signup
1. OpenAI の API Key を取得します
https://platform.openai.com/account/api-keys
1. 環境変数 `OPENAI_API_KEY` に取得した API Key を設定します
`export OPENAI_API_KEY=xxxxxx`

### Google Cloud

1. BigQuery をセットアップします
https://cloud.google.com/bigquery/docs/quickstarts/query-public-dataset-console
1. Google Cloud のサービスアカウントを作成します
https://cloud.google.com/iam/docs/service-accounts-create
1. サービスアカウントに [BigQuery Metadata Viewer (roles/bigquery.metadataViewer)](https://cloud.google.com/bigquery/docs/access-control#bigquery.metadataViewer) の権限を付与します
https://cloud.google.com/iam/docs/manage-access-service-accounts#grant-single-role
1. サービスアカウントの鍵を作成してダウンロードします
https://cloud.google.com/iam/docs/keys-create-delete#iam-service-account-keys-create-console
1. 環境変数 `GOOGLE_APPLICATION_CREDENTIALS` に鍵ファイルのパスを設定します
`export GOOGLE_APPLICATION_CREDENTIALS=$PWD/credential.json`

## 使い方

初期設定が終わっているものとします。日本語でも英語でも命令を記述できます。
具体例として dbt tutorial の public table から 「2018年3月に購入した顧客の名前を取得する」 ことを SQL で実現したいとします。
次の2つの方法のどちらでも動かせます。

### Docker コンテナを使う場合

Docker が使える状態であれば何かをインストールする必要はありません。

1. Docker image の取得
`docker pull algas/bigquery-generator-ai:latest`
1. コマンドの実行
```sh
docker run --rm -e OPENAI_API_KEY=$OPENAI_API_KEY \
-e GOOGLE_APPLICATION_CREDENTIALS=/app/credential.json \
-v $GOOGLE_APPLICATION_CREDENTIALS:/app/credential.json \
-it algas/bigquery-generator-ai:latest \
'2018年3月に購入した顧客の名前を取得する。' \
'dbt-tutorial.jaffle_shop.customers' \
'dbt-tutorial.jaffle_shop.orders'
```

### Python を自分で動かす場合

Python 3 が使える状態になっているとします。

1. リポジトリの取得
`git clone https://github.com/algas/bigquery-generator-ai.git`
1. ライブラリのインストール
`pip install -r requirements.txt`
1. コマンドの実行
```sh
python bq_sql_gen.py \
'2018年3月に購入した顧客の名前を取得する。' \
'dbt-tutorial.jaffle_shop.customers' \
'dbt-tutorial.jaffle_shop.orders'
```

## 実装の概要

google-cloud-bigquery で table schema を取得しています。
langchain の PromptTemplate を使って ChatGPT にプロンプトを与えています。
詳しく知りたい方は Python で実装されたコードを読んでください。
https://github.com/algas/bigquery-generator-ai/blob/main/bq_sql_gen.py

## 注意事項

- 執筆時点で ChatGPT API が対応している GPT-3.5 を使っています。
- 複雑な命令やクエリと関係ない文を与えた場合には正しい SQL を出力しないことがあります。
- コマンドの末尾に `-v` か `--verbose` を加えると、結果の SQL だけではなく ChatGPT に入力するプロンプトの内容も出力します。 
- 正常に動作しないときは ChatGPT API が応答を返さないか、Google Cloud の権限設定が正しくない可能性があります。

## まとめ

「ChatGPT API を使って BigQuery SQL を自然言語で記述する CLI」 を紹介しました。初期設定さえできてしまえば、クエリで実現したいこととテーブル名を書くするだけで SQL を生成できます。