📝

Vertex AI Search x LINE：Codeyを使ってBigQueryから在庫情報を取得 vol.3

2024/04/15に公開

LINE

はじめに

こんにちは、
クラウドエースの岸本です。
前回に引き続き、LINE と Vertex AI の活用についてやってみたことを紹介したいと思います。
今回は、Codey を使って BigQuery から在庫情報を取得する方法について紹介します。

サービス作成の背景（私の経験から）

巨大スーパーマーケットには、多くの商品があり日々商品が入れ替わります。
お客様は、お店に足を運ばず商品の在庫、いつ入荷されるのか確認したいと思うことがあります。
そこで手軽に在庫情報を取得できる公式ラインがあれば便利だと考えました。

今回の構成図は以下です。

1-1

対象読者

Google Cloud を触るのが初めての方
Google Cloud が好きな方

やること・やらないこと

やること

Codey を使用して BigQuery からデータを取得
Vertex AI で文章の生成

やらないこと

ソースコードの解説
各サービスの詳細な説明

構築手順

構築手順は以下の通りです。

1. BigQuery にテーブルを作成
1. Codey を使用して BigQuery からデータを取得
1. ソースコードの修正
1. Cloud Run にデプロイ
1. 確認

1. BigQuery にテーブルを作成

初めに、BigQuery にテーブルを作成します。
商品在庫情報が格納されており、LINE からの質問に回答する際参照されます。

手順は以下です。
1-1. データセット&テーブルの作成
1-2. スキーマを設定
1-3. データを挿入

構成図は以下です。
1-2

1-1. データセット&テーブルの作成

前回同様テーブルを作成していきます。詳細は以下のリンクを参照してください。

[データセット設定値]

項目	値
データセット ID	Vertexai_line
ロケーションタイプ	マルチリージョン（US）

[テーブル設定値]

項目	値
プロジェクト	Your Project
データセット	Vertex_line
テーブル	inventry

これでデータセットとテーブルが作成されました。
次に、スキーマを設定します。

1-2. スキーマを設定

前回同様スキーマを設定していきます。
今回は、最低限の在庫情報を格納するため、以下の情報をスキーマとして設定します。

商品名
在庫数
次回入荷予定日

[スキーマ設定]

名前	タイプ	モード
product_id	STRING	NULLABLE
stock_quantity	INTEGER	NULLABLE
next_restock	DATE	NULLABLE

[スキーマ設定]

これでスキーマが設定されました。
次に、データを挿入します。

1-3. データを挿入

問い合わせに対して参照されるデータを挿入します。
以下のデータを挿入します。

product_id	stock_quantity	next_restock
バームクーヘン	10	2030-3-1
チーズ	20	2030-3-2
お団子	20	2030-3-3
バナナ	40	2030-3-4
ほうれん草	50	2030-3-5

早速、クエリを実行してデータを挿入します。
以下のクエリを実行してください。

データ挿入クエリ

INSERT INTO `YOUR_TABLE_ID` (product_id, stock_quantity, next_restock) VALUES
('バームクーヘン', 10, '2030-3-1'),
('チーズ', 20, '2030-3-2'),
('お団子', 20, '2030-3-3'),
('バナナ', 40, '2030-3-4'),
('ほうれん草', 50, '2030-3-5');

上記を実行すると、データが挿入されました。

次に、Codey を使用して BigQuery からデータを取得します。

2. Codey を使用して BigQuery からデータを取得

ここでは、Codey を使用して BigQuery からデータを取得します。
手順は以下です。
2-1. Codey について
2-2. 実際に Codey を使用する

構成図は以下です。
1-3

2-1. Codey について

初めに、Vertex AI → Model Garden に移動します。

ナビゲーションメニューから、Vertex AI に移動します。
[Vertex AI]

Model Garden に移動すると、以下の画面が表示されます。

[Model Garden]

Model Garden は、機械学習モデルを 1 か所で検索、検出、操作できるツールになっています。
そのモデルの中にある Codey は、生成 AI モデルの 1 つで、プログラミング言語の理解と生成を行うことができます。
Model Garden を詳しく知りたい方は、以下のリンクを参照してください。

Codey について詳しく知りたい方は、以下のリンクを参照してください。

2-2. 実際に Codey を使用する

では、実際に Codey を使用して BigQuery からデータを取得していきます。

参考にしたプロンプトのブログは以下です。

モデルを検索で、Codeyと検索し、Codey for Code Generationを選択します。
[プロンプト設計を開く]をクリックします。すると以下の画面が表示されます。

画面右上のモデルとリージョン選択し、以下のプロンプトを入力します。

プロンプトフォーマット

You are an experienced data analyst. Write a BigQuery SQL to answer the user's prompt based on the following context.

---- Context ----
Format: Plain SQL only, no Markdown
Table: `{table.full_table_id}`
Restriction: {restriction}
Schema as JSON:
{schema}
----

User's prompt: {user_prompt}

プロンプト by 岸本

スキーマ情報を入れ込みました。

You are an experienced data analyst. Write a BigQuery SQL to answer the user's prompt based on the following context.

---- Context ----
Format: Plain SQL only, no Markdown
Table: Your_table_ID
Restriction: None
Schema as JSON:
{
 "fields": [
  {"name": "product_id", "type": "STRING", "mode": "NULLABLE"},
  {"name": "stock_quantity", "type": "INTEGER", "mode": "NULLABLE"},
  {"name": "next_restock_date", "type": "DATE", "mode": "NULLABLE"}
 ]
}
----

User's prompt: バームクーヘンの在庫数は？

実際に、プロンプトを入力し、送信ボタンをクリックすると、以下のように SQL 文が生成されます。

生成された SQL 文

select product_id, stock_quantity
from inventry
where product_id="バームクーヘン"

[画面下が出力結果]

これからわかるように、Codey を使用することで、SQL 文を簡単に生成することができました。
次に、コードを修正して LINE からの質問を受け取れるようにしていきます。

3. ソースコードの修正

ここでは、第 1 回のソースコードを修正して LINE からの質問に回答できるようにします。

流れとしては、以下の構成図になるかと思います。
2-1

編集するファイルは、app.py のみです。

app.py

import os
import config
from flask import Flask, request, abort
from linebot import LineBotApi, WebhookHandler
from linebot.v3.messaging import MessagingApi
from linebot.exceptions import InvalidSignatureError
from linebot.models import MessageEvent, TextMessage, TextSendMessage
from google.cloud import bigquery
from google.cloud import aiplatform as vertexai
from vertexai.language_models import CodeGenerationModel
from vertexai.language_models import TextGenerationModel

app = Flask(__name__)

line_bot_api = LineBotApi(config.token)
handler = WebhookHandler(config.secret)

client = bigquery.Client()
PROJECT_ID = config.project_id

def search_summaries(search_query: str) -> str:
    project_id = PROJECT_ID
    vertexai.init(project=project_id, location="asia-northeast1")
    parameters = {
        "candidate_count": 1,
        "max_output_tokens": 1024,
        "temperature": 0.9
    }
    model = CodeGenerationModel.from_pretrained("code-bison-32k@002")
    response = model.predict(
        prefix=f"You are an experienced data analyst. Write a BigQuery SQL to answer the user's prompt based on the following context:\n"
               "---- Context ----\n"
               "Format: Plain SQL only, no Markdown\n"
               "Table: Your_Table_ID\n"
               "Restriction: None\n"
               "Schema as JSON:\n"
               "{\n"
               "    \"fields\": [\n"
               "        {\"name\": \"product_id\", \"type\": \"STRING\", \"mode\": \"NULLABLE\"},\n"
               "        {\"name\": \"stock_quantity\", \"type\": \"INTEGER\", \"mode\": \"NULLABLE\"},\n"
               "        {\"name\": \"next_restock\", \"type\": \"DATE\", \"mode\": \"NULLABLE\"}\n"
               "    ]\n"
               "}\n\n"
               f"User's prompt: {search_query}",
        **parameters
    )
    # Ensure the response is a string, modify based on your response structure
    return response.text if hasattr(response, 'text') else str(response)

@app.route("/callback", methods=['POST'])
def callback():
    signature = request.headers['X-Line-Signature']
    body = request.get_data(as_text=True)
    app.logger.info("Request body: " + body)
    try:
        handler.handle(body, signature)
    except InvalidSignatureError:
        abort(400)
    return 'OK'

@handler.add(MessageEvent, message=TextMessage)
def handle_message(event):
    user_message = event.message.text
    response = search_summaries(user_message)
    model = TextGenerationModel.from_pretrained("text-bison@002")
    final_response = "Sorry, an error occurred."

    # Check the type and extract the query/message accordingly
    if hasattr(response, 'text'):
        sql_query = response.text
    elif isinstance(response, str):
        sql_query = response
    else:
        # Handle other types or set a default message
        sql_query = final_response

    # Execute the query
    if sql_query:
        sql_query = sql_query.replace("```sql", "").replace("```", "").strip()


    try:
        client = bigquery.Client()
        query_job = client.query(sql_query)
        results = query_job.result()

        row = next(results, None)
        if row:
            product_id = str(getattr(row, 'product_id', ''))
            stock_quantity = str(getattr(row, 'stock_quantity', ''))
            next_restock = str(getattr(row, 'next_restock', ''))
        material = " ".join(filter(None, [product_id, stock_quantity, next_restock]))
    except Exception as e:
        final_response = f"An error occurred while executing the query: {str(e)}"

    prompt = f"You are the best Assistant Language Teacher. Here is question '{user_message}', and here is material for answer '{material}. Please answer it."
    parameters = {
        "temperature": 0.2,
        "max_output_tokens": 256,
        "top_p": 0.8,
        "top_k": 40
    }

    final_response = model.predict(prompt, **parameters)

    line_bot_api.reply_message(
        event.reply_token,
        TextSendMessage(text=final_response.text)
    )

if __name__ == '__main__':
    port = int(os.getenv('PORT', 8080))
    app.run(debug=False, host='0.0.0.0', port=port)

コードについては、もっと効率的な書き方があるかもしれませんが、このまま進めていきます。
効率的なコードをお知りの方は、コメントで教えていただけると幸いです。

コード参考元

Model Garden の画面、右上に< >コードを取得があるので、そちらも参考にしました。

(省略可)苦労日記

try and error の繰り返しでした。

タイポ
1 つの値しか取得できない
'method' object is not subscriptable
などなど。
以下対戦途中の写真です

以上で、コードを修正しました。
次に、Cloud Run にデプロイしていきます。

4. Cloud Run にデプロイ

最後に Cloud Run にデプロイしていきます。

構成は以下です。
1-4

こちらも前回と同様の手順です。

【環境変数の設定】

// 環境変数の設定
export project_id=プロジェクトID

【イメージのビルド】

// build
docker build -t inventer .

// イメージの確認
docker images

【イメージにタグ付け】

// イメージのタグ付け
docker tag inventer:latest asia-northeast1-docker.pkg.dev/$project_id/codey-repos/inventer:latest

// タグ付け確認
docker images

【Artifact Registory に push】

// リポジトリの作成
gcloud artifacts repositories create codey-repos --repository-format=docker --location=asia-northeast1 --project=$project_id

// リポジトリの確認
gcloud artifacts repositories list --project=$project_id

// push
docker push asia-northeast1-docker.pkg.dev/$project_id/codey-repos/inventer:latest

【デプロイ】

gcloud run deploy inventer --image=asia-northeast1-docker.pkg.dev/$project_id/codey-repos/inventer:latest --region=asia-northeast1 --platform=managed --project=$project_id

デプロイされると、以下のようにエンドポイントの Service URL が生成されます。

Deploying container to Cloud Run service [inventer] in project [project_id] region [asia-northeast1]
OK Deploying... Done.
OK Creating Revision...
OK Routing traffic...
Done.
Service [inventer] revision [inventer-123-123] has been deployed and is serving 100 percent of traffic.
Service URL: https://inventer-123-123.run.app

上記で生成されたエンドポイントの URL をコピーし Webhook URL に設定します。
LINE Developer の Messaging API 設定の画面にある、Webhook の設定画面に移動し、エンドポイントの URL を貼り付けます。その後に/callbackを追加します。
例えば、以下のようになります。
https://inventer-123-123.run.app/callback
これで、サービスとしては完成です。

最後に、LINE で質問をしてみます。

5. 確認

最後に、LINE で質問をしてみます。
以下のような回答が返ってきました。

バナナに関してはスルーされます。実際の SQL 文を確認したいと思います。

BigQuery のコンソール画面に移動し、右下のジョブ履歴から実行されたジョブを確認します。

[BigQuery のコンソール画面 → ジョブ履歴]

右の操作の欄から[ジョブの詳細]をクリックします。
バナナに関しての SQL 文が確認できました。

[SQL 文]

カタカナが英語に変換されているので、値が取れないことが確認できました。
これは今後の課題にしたいと思います。（バームクーヘンはいけたのにな.....）

まとめ

今回は、Codey を使用して BigQuery から在庫情報を取得する方法について紹介しました。
Codey が勝手に SQL 文を生成してくれるため、SQL 文ちょっとも知らないマンでも簡単に BigQuery からデータを取得できることがわかりました。
またこれは、プロンプトを変えることで、データの書き込みもできるかと思います。
例えば、飲食店で在庫の発注などにも使えると思います。それらのデータを用いで、BigQuery で仕入れの最適化などもできるかもしれません。
他にも、アンケートをそのまま BigQuery に書き込み、分析することもできるかもしれません。
LINE x Vertex AI x Big Query の活用方法はまだまだありそうです。
Google Cloud のサービスを使って、業務を効率化してみてはいかがでしょうか。
最後まで読んでいただきありがとうございました。