こんにちは！ozawaです。
今回は、SnowflakeのNative Applicationの中で、「レファレンス」と呼ばれるオブジェクトを使って、コンシューマ側のテーブルをアプリ内で設定する方法を紹介します。
コンシューマ側のテーブルをアプリの中で使用したい場合、テーブルをレファレンスオブジェクトに変換したうえで、アプリの中で使用できるようになります。
多くのアプリの場合、コンシューマ側のテーブルをアプリの入力値として使用したいことがあるため、レファレンスの設定はアプリの動作上不可欠となります。
一般的にアプリでレファレンスを設定する際には、アプリのセキュリティ画面の中で設定することができますが、
本ブログの手順を踏むことで、アプリ内のStreamlitの画面上でもレファレンスを設定できます。
セキュリティ画面はStreamlitの画面から遷移する必要があるため、Streamlitの画面上で設定できることで、ユーザがストレスなくアプリを実行することができます。
※ 逆ジオコーディング UI版作成中です～！
本ブログでは、①Snowflake全般の方法でレファレンス作成してみた、②セキュリティ画面でレファレンス作成してみた、③Streamlitの画面上でレファレンス作成してみた 3点やってみた感想をご紹介します。
レファレンスって何？
前書きでもさらっと触れたレファレンスを、ここではもう少し詳しく説明していきます。
例を示したほうが早いので、次の例で説明します。
例
・ロールXはテーブルaにアクセスできます。ロールYはアクセスできないです。
・ロールXはテーブルaからレファレンスAを作成します。
→ レファレンスAを使うことで、ロールYはテーブルaにアクセスできます。
アクセスできるロールの方がレファレンスを作成してあげることで、アクセスできないロールの方がレファレンスを介して参照できるようになるということです。
これを前提として、ネイティブアプリの制限を事例としてレファレンスを見ていきましょう。
Snowflake Native Applicationでは、アプリの中で実行されるクエリ、UDFやプロシージャから、コンシューマ側（アプリを使う側）のテーブルを通常は参照することができません。
これは、アプリの中で処理を実行するときは、アプリロールで実行されるためです。
初期状態では、アプリロールにはコンシューマ側のテーブルへのSELECT権限がないため、コンシューマ側のテーブルにSELECT権限を付与した「レファレンス」オブジェクトをアプリロールに渡して参照可能にします。
Snowflakeクエリでレファレンスを作成
レファレンスは、Snowflakeの次のようなクエリで作成できます。
SYSTEM$REFERENCE('TABLE', 'DB.SCHEMA.TABLE', 'PERSISTENT', 'SELECT')
このクエリでできるレファレンスは次のような制約・設定になります。
・DB.SCHEMA.TABLEというテーブルレファレンスを作成
・永久的なレファレンスを作成（PERSISTENT）
・SELECTのみ使えるレファレンスですよ～
※ PERSISTENTのほかに、CALL（SYSTEM$REFERENCEが入ったクエリの中でのみ有効）、SESSION（ウェアハウスが切れるまで有効）のように、レファレンスの有効期限を設定できます。
※ SELECT権限だけではなく、INSERT権限などさまざまな権限を付与できます。SELECT・INSERTのように、複数権限も付与できます。
よーし！アプリの中でもSYSTEM$REFERENCEを使ってみよう～としたかったのですが、どうやらうまくいきません。
DB.SCHEMA.TABLE のレファレンスを作成したいのですが、SYSTEM$REFERENCEの第二引数としてのDB.SCHEMA.TABLEがアプリの中で権限がないよ～と循環的にエラーに遭って実行できませんでした。
そのため、アプリ独自の方法でレファレンスを作成する必要があります。
アプリのセキュリティ画面でレファレンスを作成
テーブルのレファレンスをアプリの中で設定できるために、2つのアプリ設定ファイルにスクリプトを追加します。
・アプリのすべてを管轄するmanifest.yml
・アプリの中にテーブルや関数を作成するためのsetup.sql
■ manifest.yml
#セキュリティ画面でレファレンスを設定する場合は、この文を追加
references:
- input_table: #セキュリティ画面で設定するレファレンス名
label: "Input Table"
description: "Input table"
privileges:
- SELECT #セレクト権限を付与するよ～
object_type: Table
multi_valued: false
register_callback: app_instance_schema.update_reference #setup.sql内のレファレンスを設定するプロシージャを連携
■ setup.sql
#セキュリティ画面でレファレンスを設定する場合は、この文を追加 #ソースページ create or replace procedure app_instance_schema.update_reference(ref_name string, operation string, ref_or_alias string) returns string language sql as $$ begin case (operation) when 'ADD' then #レファレンス化するテーブルを追加する（テーブル選択のボタンに該当） select system$set_reference(:ref_name, :ref_or_alias); when 'REMOVE' then #レファレンス化するテーブルを削除する（テーブル選択のボタンに該当） select system$remove_reference(:ref_name, :ref_or_alias); when 'CLEAR' then #レファレンス化するテーブルを一斉削除する（ゴミ箱マークに該当） select system$remove_all_references(:ref_name); else return 'Unknown operation: ' || operation; end case; return 'Success'; end; $$;
レファレンスを削除したり、追加したりするupdate_referenceプロシージャをsetup.sqlの中に作成し、manifest.ymlのregister_callbackに設置することで、
セキュリティ画面の「+」ボタンや、「ゴミ箱」ボタンにプロシージャ内の操作に紐づけられます。
ここまでのスクリプトを加筆すると…
セキュリティ画面でコンシューマ側のテーブルをアプリで呼び出すことができます。
ちなみに、レファレンスのテーブルからSELECTするときは、FROMの後ろをreferenceで囲います。
SELECT * FROM reference('Input Table'); #manifest.ymlのレファレンス名と合わせる
また、setup.sql内のプロシージャを使う方法は、セキュリティ画面での設定に限定されます。
セキュリティ画面から紐づいたプロシージャを呼び出すのではなく、Streamlit画面から直にプロシージャを呼び出すと、system関数でコンシューマ側のテーブルを指定できずにエラーが出てうまくいきませんでした。
Streamlit画面でコンシューマ側のテーブルを設定するには、次の方法が有効です。
アプリのStreamlit画面でレファレンスを作成
3つのうち多分このやり方がベストプラクティスだと思います。（主観）
「アプリのセキュリティ画面でレファレンスを作成」セクションでスクリプトを追加した2つのファイルに加えて、
UI画面を構成するstreamlit.pyにもスクリプトを追加します。
■ streamlit.py
from snowflake.snowpark.context import get_active_session
import snowflake.permissions as permissions
import streamlit as st
import pandas as pd
session = get_active_session()
st.write('###### 1. 逆ジオコーディングしたいテーブルを選択してください。')
if st.button('入力テーブルを選択'):
df = permissions.request_reference("input_table") #テーブルを選択してレファレンスを作成（セキュリティ画面と一緒）
if st.button('入力テーブルを確認'):
df = session.sql("SELECT * from reference('input_table')") #テーブルレファレンスからセレクトする
st.write(df)
snowflake.permissions というパッケージのrequest_reference関数を使うと、レファレンスオブジェクトを作成・削除・追加してくれます。
これを追加すると….
設定画面がばっちり出ました！
このように、Streamlit画面でレファレンスを設定できるのでめちゃくちゃ楽です！！！
以上3点のレファレンス設定方法を試行してみた！でした～
おわりに
truestarではSnowflakeの検討、導入支援や環境構築からアプリ開発まで幅広くサポート可能です。
Snowflakeに゙興味がある、導入済みだけどもっとうまく活用したい等々ありましたら、ぜひこちらから相談ください！
MarketPlaceにてリリースした「逆ジオコーディングアプリ」、その開発裏話や、
Streamlit in Snowflakeの試行錯誤記事のようにNative AppやStreamlit in Snowflakeに関する調査も行っています！
これまでのSnowflakeに関する記事はこちら
