BigQueryからデータを抽出して生成AIに分析してもらうまでがSQLで完結する手順 #ログ

こんにちは！
LIFULLエンジニアの吉永です。

本日はBigQueryからデータを抽出、生成AIに分析してもらうまでがSQLで完結する手順をご紹介します。

この記事の内容が役立ちそうな人たち

BigQueryにデータを蓄積していて分析を行っている人
BigQueryに非構造化データを格納していて構造化データへ変換している人
生成AIを使ってデータ分析を行っている人

手順

参考記事

今回の動作確認をする為にクラスメソッドさんの下記の記事を参考にさせていただきました。

Google公式ドキュメントにも大枠の流れは同じものが掲載されていますので、合わせてこちらも確認すると良いです。

参考記事の手順を要約すると下記3ステップで利用を開始できます。

BigQueryプロジェクトで生成AIを利用する為に外部データソース接続設定を行う。
上記で作成したサービスアカウントに権限を付与する。
SQLから利用する生成AI用のモデルを作成する。

参考記事を読みながら進めていましたが、所要時間は10分もかからないくらいで終わりました。

動作確認

BigQuery Studioで実行するSQL

WITH key_jsons AS (
  SELECT '{"id":1,"name":"aaa","email":"aaa@example.com","memo":"早口でしゃべる。質問に対して即座に返答する。面倒見が良い。"}' AS key_json
  UNION ALL
  SELECT '{"id":2,"name":"bbb","email":"bbb@example.com","memo":"おっとりしていてマイペース。芯が強く周囲の雰囲気に流されない。怒ると少し怖い。"}' AS key_json
)
SELECT
  ml_generate_text_result['predictions'][0]['content'] AS generated_text,
  ml_generate_text_result['predictions'][0]['safetyAttributes'] AS safety_attributes,
  * EXCEPT (ml_generate_text_result)
FROM
  ML.GENERATE_TEXT(
    MODEL `{手順のステップ3で作成したモデル}`,
    (
      SELECT
        CONCAT('key_jsonのmemoを分析してどんな性格の人かまとめてください。', key_json) AS prompt
      FROM key_jsons
    ),
    STRUCT(
      0.2 AS temperature,
      500 AS max_output_tokens));

今回実行するSQLは上記で、{手順のステップ3で作成したモデル}の個所だけ自分のBigQuery環境のモデル名へ置き換えればそのまま動作検証できます。

json形式で何かしらのパーソナルデータが格納されているテーブルのmemoというキーのバリューの内容を分析してもらい、どんな性格の人かをまとめてもらっています。

各パート解説

生成AI出力部分

SELECT
  ml_generate_text_result['predictions'][0]['content'] AS generated_text,
  ml_generate_text_result['predictions'][0]['safetyAttributes'] AS safety_attributes,
  * EXCEPT (ml_generate_text_result)

ml_generate_text_result['predictions'][0]['content']に生成AIからのレスポンスが格納されていて、ml_generate_text_result['predictions'][0]['safetyAttributes']には生成AIからのレスポンス内容の安全性に関する情報が格納されています。

出力に関する詳細は公式ドキュメントを参照してください。

生成AI呼び出し部分

FROM
  ML.GENERATE_TEXT(
    MODEL `{手順のステップ3で作成したモデル}`,
    (
      SELECT
        CONCAT('key_jsonのmemoを分析してどんな性格の人かまとめてください。', key_json) AS prompt
      FROM key_jsons
    ),
    STRUCT(
      0.2 AS temperature,
      500 AS max_output_tokens));

ML.GENERATE_TEXT関数の各引数に所定の値を指定することでSQLから生成AIへの指令を与えることができます。

      SELECT
        CONCAT('key_jsonのmemoを分析してどんな性格の人かまとめてください。', key_json) AS prompt
      FROM key_jsons

この部分が指令を出している部分で、key_jsonsというテーブルから取得したレコードを指令部分のプロンプト文字列に連結しています。

    STRUCT(
      0.2 AS temperature,
      500 AS max_output_tokens));

生成AIの出力に関係する各種パラメーターの指定もできます。

temperatureは値を小さくすればするほど可能性の高い回答をするようになり、大きくすれば回答の幅が広がるという用途で利用するようです。

max_output_tokensは生成AIからの回答の長さを調節するもので、値を小さくすればするほど短い文章になるようです。

ML.GENERATE_TEXTに関する詳細は公式ドキュメントを参照してください。

実行結果

BigQuery Studioで実行した結果のgenerated_textカラムの出力は以下のようになりました。

aaaさん分析結果

性格
* 早口でしゃべる
* 質問に対して即座に返答する
* 面倒見が良い
分析
この人は、早口でしゃべる傾向があり、質問に対して即座に返答する傾向があります。
これは、この人が頭がよく、機知に富んでいることを示しています。
また、面倒見が良い傾向があり、これはこの人が親切で思いやりのある人であることを示しています。

bbbさん分析結果

性格
* おっとりしていてマイペース
* 芯が強く周囲の雰囲気に流されない
* 怒ると少し怖い
長所
* 穏やかで優しい
* 自立していて頼りになる
* 正義感が強く曲がったことが嫌い
短所
* マイペースすぎて融通がきかないことがある
* 頑固で自分の意見を曲げないことがある
* 怒ると怖い
全体像
bbbさんは、おっとりしていてマイペースな性格ですが、芯が強く周囲の雰囲気に流されない人です。
怒ると少し怖いですが、普段は穏やかで優しい人です。
自立していて頼りになり、正義感が強く曲がったことが嫌いです。
マイペースすぎて融通がきかないことがあり、頑固で自分の意見を曲げないことがあります。
怒ると怖いですが、普段は穏やかで優しい人です。

まとめ

いかかでしたでしょうか？

BigQueryにデータを蓄積している人達はこの機能がなかった以前はデータをエクスポートして生成AIにインポートして分析や加工をさせる必要がありました。

ML.GENERATE_TEXT関数を利用すると分析の為のデータをエクスポートする必要がなくなるので、工数削減にもつながりそうです。

BigQueryからデータ抽出を行っている方はSQLにML.GENERATE_TEXT関数を組み込むことで、抽出データの整形や加工を生成AIに行ってもらうこともできそうですね。

私の個人的な今後の展望はML.GENERATE_TEXT関数を利用してユーザ行動ログ、ユーザ属性など様々なデータをかけ合わせた分析で活用していきたいと思っています。

いずれ生成AIの基本モデルに対してエンタープライズデータを学習させて特定の領域に特化させるアダプターチューニング（Parameter-efficient Fine-tuning）にもチャレンジしたいと思っていますので、知見が溜まったらQiitaにアウトプットしたいと思います。

最後まで読んでいただきありがとうございました！

また次の記事でお会いしましょう。

最後に

LIFULL HOME'Sは、「叶えたい！が見えてくる。」をコンセプトに掲げる不動産・住宅情報サービスです。
賃貸、一戸建て・マンションの購入、注文住宅から住まいの売却まで。
物件や住まい探しに役立つ情報を、一人ひとりに寄り添い最適な形で提供することで、本当に叶えたい希望に気づき、新たな暮らしの可能性を広げるお手伝いをします。

―LIFULL HOME'Sのサービスについて
日本最大級の不動産・住宅情報サービス【LIFULL HOME'S（ライフルホームズ）】
https://www.homes.co.jp/
賃貸のお部屋探し・賃貸住宅情報なら【LIFULL HOME'S】
https://www.homes.co.jp/chintai/
マンションの購入・物件情報の検索なら【LIFULL HOME'S】
https://www.homes.co.jp/mansion/
一戸建て[一軒家]の購入・物件情報の検索なら【LIFULL HOME'S】
https://www.homes.co.jp/kodate/
注文住宅の施工会社・住宅カタログを探すなら【LIFULL HOME'S 注文住宅】
https://www.homes.co.jp/iezukuri/
不動産売却にはまず査定の依頼から【LIFULL HOME'S 不動産査定】
https://www.homes.co.jp/satei/
マンション売却の一括査定なら【LIFULL HOME'S マンション売却】
https://lifullhomes-satei.jp/
不動産投資・収益物件を検索するなら【LIFULL HOME'S 不動産投資】
https://toushi.homes.co.jp/
理想の住まい選び・家づくりをアドバイザーに無料相談【LIFULL HOME'S 住まいの窓口】
https://counter.homes.co.jp/