先日のSnowflakeアップデートで、新しいデータ型「VECTOR」と、ベクトル用関数「VECTOR_COSINE_SIMILARITY」、埋め込みベクトルを生成する「EMBED_TEXT_768」などが一部リージョンでGAとなりました。
これら新機能の登場により、Snowflake上でRAGシステムを構築するための道具が出揃いました。AI領域の知見が少ない私でも簡単にRAGシステムを構築できました。
本記事では、Snowflake上でRAGシステムを構築するための基本的な機能の使い方を、実践の様子も交えて紹介します。本記事で作成するRAGは、「Notionデータベース上にあるナレッジ記事を検索するシステム」です。
なお、本記事はRAGの運用やセキュリティ面、アプリケーション部分には触れていません。設計もサクッと実装しているため、UDFの記述など大変お粗末な、いわばプロトタイプです。。
この検証を通して実用性を感じられたら、社内に展開されているNotion上のデータを定期的にSnowflakeに取り込み、RAGをStreamlitでアプリ化して社内のメンバーに活用してしてもらおうと企んでいます。UDFやプロシージャの改善や、導入まわりの話もいずれ記事にできればと思います。
【追記】2024-05-28 17:00
⚠ embed_text_768やcomplete関数は、英語テキストのみを考慮した関数とのことでした。。そのため、一度「translate」関数で英語に直してから処理しないと性能が上がらない可能性が高いです。
やること
やらないこと
余談
ISLでは、技術にまつわる情報をNotionで管理しています。そのひとつとして、業務やプライベートで学んだことを日々投稿し、部署の垣根を超えて様々なナレッジを共有・賞賛しあっています。
社内勉強会でDX/AIソリューション部の先輩が公開してくださったAWS上でのRAGシステムを参考に実装しています。https://note.com/insight_lab/n/n2096936943df
このシステム設計を参考に、エンドユーザが求めるナレッジ情報を、Notion上にあるナレッジページから近しい内容のページを提示するRAGシステムをSnowflake上で構築します。2,3,4の処理はそれぞれUDFとして作成します。
構築の流れ
現在、VECTOR関数やLLM関数、VECTOR型が使用できるリージョンは以下の５つです。そのため、新しいアカウントを作成する必要があります。今回は、AWS US East1を使用します。
| AWS US West 2(Oregon) | AWS US East 1(N. Virginia) | AWS Europe Central 1(Frankfurt) | Azure East US 2(Virginia) | Azure West Europe(Netherlands) |
|---|
使用するデータは、日々、社内のエンジニアがアウトプットしてくれているNotionデータベース上に蓄積されたナレッジ情報をCSVファイルに出力し、Snowflakeの通常テーブルにロードします。
※Pythonスクリプト内で必要な情報を抽出しています。
次に、notion_knowlegesから出力に必要な基本情報と、LLM関数の「EMBED_TEXT_768」で埋め込みベクトル化したカラムを持つテーブルを作成しインサートします。このテーブルはいわゆる「ベクトルデータベース」の役割を担います。
入力となる質問文は、LLM関数の「EMBED_TEXT_768」で埋め込みベクトル化します。
(768は次元数)
ベクトル化された質問文に近い既存情報を、ベクトル関数「vector_cosine_similarity」を使用して算出し、マッチングを行います。※本記事では検索用クエリにLIMITを設定し、上位5件を参考情報として取得します。
質問入力でプロンプト化された質問文と、ドキュメント検索で取得された既存情報を組み合わせてSnowflakeで使用できるLLMに渡し、回答文を生成します。
ここからは、RAGシステムの構築の様子を実行したコマンドとともに紹介します。
AWS_US_EAST_1リージョンのアカウントを作成します。新しいアカウントに移動したら任意のデータベースとスキーマを作成します。
create account {アカウント名を入力}
admin_name = '{ユーザ名を入力}'
admin_password = '{パスワードを入力}'
email = '{メールアドレスを入力}'
must_change_password = true
edition = enterprise
region = AWS_US_EAST_1
;
NotionAPIを使用してナレッジ記事が格納されているデータベースページから、ナレッジページのデータを抽出します。その後、作成したSnowflakeのテーブル（notion_knowledges）にタイトル情報を手動でロードします。
その後、「EMBED_TEXT_768」関数を実行して、タイトルを埋め込みベクトルに変換し、タイトルと合わせて、別テーブルに格納します。
// 埋め込みベクトルデータ格納用テーブルを作成
create or replace table vector_notion_knowledges (
title varchar,
url varchar,
create_time timestamp_ntz,
vectorized_title vector(float,768)
);
// ステータスが公開のページ情報とベクトルデータをインサート
insert into vector_notion_knowledges
with
release_data as (
select
title,
url,
create_time
from
notion_knowledges
where
status = '公開'
)
select
*,
snowflake.cortex.embed_text_768(
'snowflake-arctic-embed-m', title
) as vectorized_title
from
release_data
;
次に、ユーザから入力される質問文を埋め込みベクトルに変換するUDFも作成します。
create or replace function translate_text_to_vector(question varchar)
returns vector(float, 768)
as
$$
select
snowflake.cortex.embed_text_768(
'snowflake-arctic-embed-m', question
) as vectorized_question
$$
;
実行結果
[-0.004565,0.049447,0.023317,0.019872,0.009760,0.027877,0.019123,0.040161,-0.017989,0.022496,-0.073311,-0.024063,-0.006641,0.012791,0.023477,-0.016740,-0.018159,-0.010545,0.000934,-0.059347,-0.035321,-0.007053,-0.002244,-0.009670,-0.026212,0.033666,0.002127,0.004959,-0.068378,-0.068570,-0.001427,0.027114,-0.005341,-0.046606,-0.011219,-0.026152,-0.014052,0.014072,-0.040504,-0.008252,-0.000430,-0.020353,0.021838,0.009249,-0.052819,-0.009828,-0.099129,0.016092,-0.014116,-0.011167,0.010340,0.033428,0.021636,0.041721,-0.018009,0.023990,0.011656,-0.068398,0.024933,-0.057293,0.095619,-0.015105,0.054866,0.048569,0.022762,-0.051122,-0.015557,-0.024501,-0.028899,-0.039785,-0.044819,0.001708,-0.019193,0.013646,0.019946,-0.006456,0.021710,0.008950,0.022677,0.034672,-0.029705,-0.003406,0.069251,0.038388,0.016258,-0.045737,0.000586,0.009299,-0.013958,0.063312,-0.006231,-0.002382,0.092824,0.048468,0.006450,0.003777,0.025782,0.023596,0.055069,-0.006922,-0.049132,-0.036161,-0.016311,-0.022703,-0.084215,-0.051711,0.031348,0.024659,-0.002620,0.030399,-0.032105,-0.000423,-0.025826,-0.017661,-0.004973,0.058627,0.003245,0.012265,0.034065,0.027843,0.049905,0.044490,0.029576,0.029317,0.011097,0.028755,-0.021957,0.002735,-0.032170,-0.077873,-0.006256,0.098260,0.018225,-0.013770,0.034696,-0.021542,-0.023049,-0.023753,0.023865,-0.049865,0.029429,0.057533,0.031830,-0.073049,0.056680,0.026999,-0.026604,0.018459,0.029389,0.043642,0.040505,0.059348,-0.012966,-0.032976,0.030583,0.039977,-0.001988,-0.039277,-0.058329,0.014873,0.045736,-0.012199,-0.024151,0.077842,-0.010454,-0.031469,0.000394,0.027961,-0.030487,0.020361,-0.018332,-0.055381,0.018087,0.004489,0.018330,-0.014267,0.036236,0.006627,0.076159,-0.007339,-0.022329,0.042009,-0.017293,0.021588,0.004442,0.009857,0.019498,-0.052186,0.000482,0.009315,-0.027348,-0.069938,-0.010835,0.027334,0.012924,0.000905,-0.016083,-0.015276,-0.037635,0.096594,0.051247,0.026249,0.048343,-0.047182,0.020275,0.055668,-0.027566,-0.049551,0.009388,0.075783,-0.015852,0.005793,0.050182,-0.007402,-0.025930,0.007005,-0.026236,-0.033200,-0.001404,0.001487,0.034959,-0.082699,-0.037926,0.022928,0.016194,0.017556,0.053299,0.016125,0.038158,0.028546,0.032020,-0.008890,0.029055,-0.012175,0.028961,0.045151,0.038806,0.011102,0.019853,-0.029678,0.022581,0.085430,-0.000596,0.008905,-0.097065,-0.013933,0.066861,-0.053778,-0.007740,-0.021212,-0.037157,-0.075168,-0.019454,-0.030084,0.052486,-0.016866,0.028474,0.058845,0.043473,-0.007958,-0.082698,0.019101,-0.016233,-0.044457,-0.061240,-0.034384,-0.060398,-0.040082,0.036582,-0.012226,0.001699,0.006604,-0.017866,0.049492,-0.016125,-0.062530,0.003029,0.065682,-0.079809,0.006740,0.014692,0.048425,0.017896,0.000489,0.053616,-0.061232,0.000272,-0.097614,-0.003259,-0.026413,-0.016016,0.068310,-0.044335,-0.047333,0.004579,0.035099,0.007690,-0.004603,-0.003360,0.038538,0.022929,0.003868,0.007147,-0.034769,-0.055079,0.010929,-0.018244,-0.062662,0.012734,0.060355,-0.007227,-0.011379,0.007913,0.034824,0.072609,0.010128,-0.008152,-0.019460,-0.007894,0.038518,-0.026002,0.052479,-0.000904,-0.014883,0.029606,-0.059178,-0.020429,0.028461,-0.005845,-0.011143,-0.027640,-0.023870,-0.018137,0.039801,0.011741,-0.016306,0.012797,-0.028493,-0.051419,-0.012121,-0.029630,-0.008495,-0.011832,-0.033839,-0.022181,0.025860,-0.023770,0.011926,-0.029609,0.052287,0.006419,-0.007959,-0.027470,-0.022028,-0.019939,-0.014149,0.002371,0.058670,0.001928,0.081618,0.021050,-0.000218,-0.027128,0.046254,-0.003533,0.027759,-0.032276,-0.013089,0.035077,-0.048863,0.011918,-0.060372,0.007850,-0.034433,-0.012082,-0.057115,0.030322,0.048299,-0.052965,0.032888,-0.008052,-0.065884,0.005051,0.047898,0.011094,-0.006872,0.022043,0.035509,-0.061400,0.024422,0.002516,0.084622,-0.011986,0.013521,0.038921,0.012630,-0.070357,0.016811,0.056021,-0.024330,0.005390,0.007971,0.018427,0.015726,0.013104,0.028442,0.003247,-0.068024,0.059018,-0.123196,-0.030407,0.006108,0.025958,0.011930,-0.082358,0.001506,0.043089,0.027906,0.008175,-0.021471,-0.008536,-0.070439,-0.026595,-0.003946,-0.018737,0.019063,0.012589,0.005952,-0.062173,0.026283,-0.063086,-0.010823,-0.041013,0.008076,0.003390,-0.049113,0.003679,0.005812,-0.045828,-0.018886,-0.029241,-0.021345,0.031995,0.014848,-0.012653,0.023119,0.046509,-0.077318,0.022738,0.040423,0.001632,-0.002231,-0.008177,0.059134,-0.063368,0.032248,-0.003890,0.014149,0.014779,-0.042260,0.034262,-0.026157,-0.018248,-0.050971,0.035231,0.012497,-0.008514,0.038440,0.011783,-0.022395,-0.064534,0.009401,-0.022557,0.014782,-0.020903,-0.023157,0.038969,0.038886,-0.001758,-0.006818,0.011671,0.009903,-0.013310,0.026575,-0.046811,-0.014063,0.032170,0.025535,0.012130,-0.004078,-0.027805,0.020097,-0.010548,-0.028863,-0.003721,0.012206,-0.041777,0.041836,0.026963,-0.015037,-0.005099,-0.028389,0.010335,0.024676,0.034508,-0.002732,-0.013432,-0.041651,0.012990,-0.029921,-0.076801,0.024327,0.035046,-0.088023,-0.037407,-0.034563,-0.028670,0.010260,-0.020322,-0.061982,-0.057250,-0.031272,-0.006333,-0.024719,0.010426,0.015436,-0.002975,0.004063,0.022720,0.011901,-0.033665,-0.048891,0.025587,0.020403,-0.060984,0.029788,0.061881,-0.015699,0.043291,0.012344,-0.049107,-0.000768,0.026530,-0.053101,-0.016355,0.051996,-0.011007,0.020022,-0.015342,0.041388,0.032150,-0.049236,-0.022262,0.017646,0.004448,0.001008,0.027556,0.021046,-0.080680,-0.049109,-0.006108,-0.032646,0.060808,-0.008248,-0.017770,-0.045048,-0.034796,-0.022634,-0.019087,0.088858,-0.002689,0.051114,0.015796,0.058866,0.016452,0.017950,0.033238,-0.014217,0.012605,-0.002009,0.000734,-0.003459,0.011299,0.010590,-0.020612,0.045450,0.011260,0.000920,0.027820,-0.020751,-0.028610,0.041528,-0.027757,-0.067382,0.001410,0.014141,-0.040096,0.036279,-0.048483,0.070798,-0.026220,-0.018910,0.001675,0.020590,0.063309,0.021516,-0.076312,-0.013830,-0.019838,0.068092,0.027471,-0.012664,-0.020044,-0.026986,-0.044362,-0.019267,-0.008830,0.037279,0.027553,-0.080718,0.014313,0.055394,-0.070097,-0.041255,0.044281,-0.028675,-0.067865,-0.008511,-0.027857,-0.076988,0.079981,-0.000217,0.029790,0.011421,-0.009713,0.025291,-0.062818,0.025116,0.041929,0.070851,0.049347,0.035516,-0.012305,-0.002538,0.012132,-0.039155,-0.016505,-0.053122,0.050419,0.048332,-0.019060,0.012083,-0.006537,0.021343,-0.000828,-0.022319,-0.034164,0.034881,-0.014447,0.002003,0.008013,0.009977,0.024380,0.038301,0.040498,-0.023318,0.009902,-0.004083,-0.051761,-0.005420,0.043907,0.005844,-0.073465,-0.013999,0.059402,-0.050294,-0.010635,0.039464,-0.029819,0.063422,-0.032031,0.051699,0.005870,0.010905,-0.006882,-0.018924,-0.024736,0.025324,0.014973,-0.028975,-0.027692,-0.018735,0.041668,-0.006246,0.017665,0.011969,-0.045165,0.005278,-0.016659,-0.031989,0.037695,-0.018164,-0.020494,-0.040178,-0.025863,-0.003084,0.082192,0.003466,0.076282,0.026661,-0.012832,-0.003063,0.079164,0.061746,-0.037873,0.026881,0.008235,-0.005687,0.045501,0.063714,-0.000551,0.001908,0.003253,-0.037345,0.045351,0.014577,0.032978,-0.034271,-0.028749,-0.039075,0.047265,0.079614,-0.006183,0.037379,0.012075,0.013080,-0.000955,-0.017230,-0.062067,-0.034507,0.057555,-0.012322,0.007380,-0.048856,-0.021050,0.040636,0.018708,-0.043791,0.007310,-0.016995,0.015083,0.002761,-0.007210,-0.041361,0.022309,-0.066175,-0.080557,-0.052894,0.018085,-0.034847,-0.008769,-0.019353,0.082839,0.002536,0.006392,-0.007596,0.047507,0.031698]
続いて、埋め込みベクトル化されたタイトルのベクトル情報と、埋め込みベクトル化された質問文のベクトル情報からコサイン類似度を算出。質問文に近い記事上位5件取得するUDFを作成します。
create or replace function search_vector_data (vectorized_question vector(float, 768))
returns varchar
as
$$
with search_result as (
select
('title:' || title || ' ' || 'url:' || url || ' ' || 'create_time:' || create_time::varchar) as data,
vector_cosine_similarity(
vectorized_question, vectorized_title
) as cosine_similarity
from
vector_notion_knowledges
order by
cosine_similarity desc
limit 5
)
select listagg(data, '|') from search_result
$$
;
実行結果
質問文と、検索によって得られた情報をLLMへのプロンプトとして渡し、ユーザへの回答となるテキストを出力するUDFを作成します。（試作1号機！！！）
create or replace function rag_prototype_001(question text)
returns varchar
as
$$
with
set_question as ( -- 質問文をベクトル化
select
question,
translate_text_to_vector(question) as vectorized_question
),
set_reference as ( -- 参考情報の検索
select
question,
search_vector_data(vectorized_question) as reference
from
set_question
),
set_prompt as ( -- プロンプト作成
select
question,
reference,
'[参考情報]を元にユーザからの[質問]に回答してください。'
|| '[質問]' || question
|| '[参考情報]' || reference as prompt
from
set_reference
),
responce as ( -- 回答生成
select
snowflake.cortex.complete(
'llama2-70b-chat', prompt
)
from
set_prompt
)
select * from responce
$$
以上で実装は完了です。ここからは実際に質問をしていきながらLLMからの回答を見ていきます。
まず、以下のような質問文で試作1号機に質問を投げてみました。
select rag_prototype_001('Snowflakeに関する記事を出力して')
すると、LLMからの以下の回答が返ってきました。
質問に対する回答としては間違っていないのですが、求めているのはNotionページのタイトルとリンク、作成日時です。ただこの聞かれ方ではLLMでなくても分かるはずないですよね。。（配慮不足）
そこで、出力のイメージをより具体的に伝えてみました。
select rag_prototype_001('snowflakeに関するページのtitle、url、create_timeを出力して')
すると、期待する形式の回答が得られました。
しかし、ユーザ側の行動に制約を与えてしまうのは避けたいところなので、プロンプトにルールを設定した、試作2号機 を作成しました 。（いわゆるプロンプトエンジニアリング）
...
set_prompt as (
select
question,
reference,
- '[参考情報]を元にユーザからの[質問]に回答してください。'
+ '[参考情報]を元にユーザからの[質問]に対して[ルール]の形式で必ず出力してください。'
+ || '[ルール] title, url, create_time'
|| '[質問]' || question
|| '[参考情報]' || reference as prompt
from
set_reference
),...
前回失敗した、質問の仕方で 試作2号機 を実行します。
select rag_prototype_002('Snowflakeに関する記事を出力して')
すると、成功例１に近い形式で出力されました！
・・・と思ったら[ルール]が表示されている。。（プロンプトエンジニアリングって難しい）
本記事のRAGシステムは、本能のままに書きなぐりました。
よって、実用化するにあたり課題は山積みです。
サクッと実装するため、検索クエリにLIMITで上限をかけたり、プロンプトも最低限期待する結果の得られるものを設定しました。しかし、表示する記事の数をユーザ側に委ねたり、ChatGPTのような過去のやりとりも踏まえた回答ができるようにするためには、より高度な実装が求められます。
本記事では、ワークシート上からUDFを呼び出しました。もちろん当たり前ですが、このままだと扱いづらさこの上ありません。Streamlitなどでアプリケーション化し、誰もが利用しやすいUIを実装してユーザ体験を向上させる必要があります。
UI/UXを向上させるにはアプリケーション化する必要がありますが、恐らく本記事の実装のままだと、アプリから呼び出しづらく、メンテナンス時の追加・修正箇所が多くなり運用・保守にかかるコストが嵩む未来は目に見えています。機能追加や修正が発生しても修正箇所が限定的になるような設計やGit統合などを考える必要があります。
RAGに向けて入力する質問文に悪意のある文字列を入力することで、データを破壊したり、盗み見れるようなバックドアを仕込むことができるというニュースを見かけました。社内利用といってもここを無視すると大切なデータの価値が失われてしまいます。実用化に向けて、最低限対策を講じる必要があるでしょう。
日々、何十人何百人が利用することを想定すると、大量のクレジットが消費されることが予想されます。まだアイデアは浮かびませんが、RAGとSnowflake Cortexの理解を深めていく中で模索していきたい。
などなど
本記事では、SQLと新しくリリースされたベクトル・LLM関数を用いて、Snowflake上でRAGシステムを構築しました。
ML・生成AI機能の進化により、簡単にRAGシステムを構築することができました。また、Snowflakeだけで完結しているため、セキュアな環境で、適切なロール管理をするだけで安全にRAGを運用できるところが魅力的です。
実用化に向けこれらの課題を解決していく様子も記事にしていきたいと思います。