Streamlit data editor in Snowflakeのデモをした話

1. 本記事について

この記事はちゅらデータ Advent Calendar 2023の8日目の記事です

先日9月8日のSnowflakeのData Cloud World Tourにて弊社(ちゅらデータの親会社のDATUM STUDIO)にてデモを行った「Streamlit data editorを用いたコストの予実管理」について紹介します。
当日のイベントでは実際に値を修正し、その場でグラフや表の変化を感じていただくデモがメインでしたが、本記事では実装やその際気にかけたことなどをまとめていきます。

なお弊社サイトのイベント出展レポートについてはこちらをご覧ください。

2. 課題・ゴール

• 課題
  従来のETLシステムですと、CSV等のファイルを修正後、ストレージにアップロードし、DWHにデータを取り込み・加工を実行したのちBIツール等で画面を確認する必要がありました。
  

• ゴール
  Webアプリのインフラ設計・実装なしに、画面からのデータの修正および可視化をStreamlit Data editor in Snowflakeを用いて実現します。
  

お題として実績&予算の管理画面を想定し、「当月以降の予算の値をWebから編集でき、同じ画面上で編集結果を確認できる」という機能の実現をゴールとして実装を進めました。

3. データ準備

以下のようなコストの予実を想定したサンプルデータをSnowflake上にmonthly_costテーブルとして作成しました。

各カラムの意味は以下の通りです。

• YEAR_MONTH: 月
• DATE_TYPE: データ種（実績もしくは予算）
• ACCOUNT_NAME: 勘定科目名
• SORT_KEY: グラフ等の表示順

YEAR_MONTH,DATA_TYPE,ACCOUNT_NAME,AMOUNT,SORT_KEY
2023-04-01,予算,材料費,50000000,1
2023-04-01,実績,材料費,51000000,1
2023-04-01,予算,人件費,50000000,2
2023-04-01,実績,人件費,50005000,2
2023-04-01,予算,販売費,10000000,3
2023-04-01,実績,販売費,9800000,3
2023-04-01,予算,一般管理費,4000000,4
2023-04-01,実績,一般管理費,4200000,4

4. 実装

以下のような内部構成で実装しました。

4.1 Snowflakeからのデータの取得

データ編集以外のグラフや表は実績予算統合ビュー、データ編集画面は予算テーブルを参照します。これらは後述のデータ編集で更新された行がinsert(正確にはcopy into
)されていくため、各月・予実・勘定科目ごとに最新更新日時の行を取得するよう処理をします。

またstreamlitではmultiselect関数を用いて複数選択のフィルタを作成することができます。ここでは月と勘定科目について複数選択できる形にします。

SQLをSnowflakeに投げる部分については、dbtで慣れていたこともありjinjaテンプレートを使用し、この処理を実装しました。

コード

# テーブルの取得
def get_dataset():
    df = session.table("monthly_cost_total")
    return df
base_df = get_dataset()

st.sidebar.write('Select below conditions')

# 勘定科目と月の選択肢リストを設定
account_name_list = base_df.select(col("ACCOUNT_NAME"),col("SORT_KEY")).distinct().sort("SORT_KEY")
month_list = base_df.select(col("YEAR_MONTH")).distinct().sort("YEAR_MONTH")

# サイドバーにフィルタを設定
selected_accounts = st.sidebar.multiselect('ACCOUNT NAME', account_name_list)
selected_months = st.sidebar.multiselect('YEAR MONTH', month_list)

# queryテンプレートを定義
total_query = """
    select
        year_month
        , data_type
        , account_name
        , amount
        , created_at
        , updated_at
    from
        y_inaoka_db.public.monthly_cost_total
    where
        1=1 
        {# 勘定科目でフィルター #}        
        {%- if accounts | length != 0 -%}
        and 
            account_name in (
           {%- for account in accounts -%}
           '{{ account }}' {%- if not loop.last -%}, {%- endif -%}
           {%- endfor -%}
           )
        {%- endif -%}
        {# 月でフィルター #}
        {%- if months | length != 0 -%}
        and 
            year_month in (
           {%- for month in months -%}
           '{{ month }}' {%- if not loop.last -%}, {%- endif -%}
            {%- endfor -%}
           )
        {%- endif -%}
    qualify
        row_number() over (
            partition by 
            year_month,data_type,account_name
            order by coalesce(updated_at,created_at) desc
        ) = 1 
    order by year_month,sort_key,data_type
"""

total_template = Template(total_query)
total_rendered_query = total_template.render(accounts=selected_accounts,months=selected_months)

# ボタン押下後のリフレッシュ対象
@st.cache_data
def get_total_data(rendered_query):
    total_df = session.sql(rendered_query)
    total_df = total_df.to_pandas()
    total_df['計算用年月'] = pd.to_datetime(total_df['YEAR_MONTH'])
    return total_df

total_df = get_total_data(total_rendered_query)

この後dataframeからグラフや図、指標を表示するコードを記載すると、フィルタ結果のグラフ等が表示されます。ここではmetric関数とplotlyを利用しYTDと前月の値、グラフを表示しました。

4.2 データ編集

experimental_data_editor関数を用いてデータ編集を実装しました。

Streamlit in Snowflakeの最新バージョンであるv1.22ではデータ編集はexperimental_data_editor関数ですが、現状Snowflake上ではサポートされていないv1.23以降ではdata_editor関数に変わっています。

ここでは以下の点に気を配りました。

観点	ポイント	実装
編集画面	Snowflake上のデータとしては縦持ち(年月、予実、勘定科目)しているが、横持ち(勘定科目*年月)で編集画面を作成し、ユーザーがExcel likeに触れるように作成	pandasでpivotを実施
データ更新	編集前の値をupdateするのではなく、新たな行をinsertして、履歴管理を実現すること	write_pandasでoverwriteをFalseに設定(Trueだと見えている新たにcreateされる)
データ更新	insertは表示している全行ではなく、変更のあった値のみ行うこと	pandasで差分を検知し、差分のみinsert
グラフ・表の表示	updateボタン押下後、最新の値で表示する	'@st.cache_data'でリフレッシュ対象を定義し、ボタン押下後にリフレッシュする

※insertと表現していますが、実際に走る処理はcopy intoになります。

3.1と同様の方法で予算を取得したdataframeがあるところからのコードを記載します。

コード

budget_df = budget_df.to_pandas()
# 横持ちにする
pivot_budget_df = budget_df.pivot(index=["ACCOUNT_NAME","SORT_KEY"], columns="YEAR_MONTH", values="AMOUNT").sort_values("SORT_KEY").reset_index(level=1, drop=True)

# データの更新とボタン
with st.form("data_editor_form"):
    st.caption("Edit the dataframe below")
    edited_budget_df = st.experimental_data_editor(
        pivot_budget_df
        , use_container_width=True
        , num_rows="dynamic"
        , key = 'pivot_budget_df'
    )
    left, cent, right = st.columns([3,2,1])
    submit_button = right.form_submit_button("update")

# 縦持ちに戻す
melt_budget_df = pd.melt(edited_budget_df,value_vars=edited_budget_df.columns,var_name="YEAR_MONTH"
                  ,value_name="AMOUNT" ,ignore_index=False)
# DATA_TYPE列・ACCOUNT_NAME列の作成
melt_budget_df["DATA_TYPE"] = "予算"
melt_budget_df = melt_budget_df.reset_index(drop=False)
# CREATED_ATを元テーブルをjoinし付与
melt_budget_df = pd.merge(melt_budget_df[["YEAR_MONTH","DATA_TYPE","ACCOUNT_NAME","AMOUNT"]]
                   ,budget_df[["YEAR_MONTH","DATA_TYPE","ACCOUNT_NAME","SORT_KEY","CREATED_AT"]]
                   ,on = ["YEAR_MONTH","DATA_TYPE","ACCOUNT_NAME"])

# 値が変わった行を取得
comp_df = (budget_df[["YEAR_MONTH","DATA_TYPE","ACCOUNT_NAME","AMOUNT"]] == melt_budget_df[["YEAR_MONTH","DATA_TYPE","ACCOUNT_NAME","AMOUNT"]])
cut_melt_budget_df = melt_budget_df.loc[~comp_df.all(axis=1)]


#ボタン
if submit_button:
    try:
        cut_melt_budget_df['UPDATED_AT']=datetime.datetime.now(JST)
        session.write_pandas(cut_melt_budget_df, "monthly_cost_budget", overwrite = False, quote_identifiers = False)
	# 変更が起こった値の数を表示
        st.success("{} values updated!".format(len(cut_melt_budget_df)))
        st.cache_data.clear()
    except:
        st.warning("Error updating table")

実際のデモ画面は以下の通りです。人件費を選択し、2024年1月の値を１円にupdateします。
下の表は最新の値を表示しており、2024年1月の人件費の予算が1円になったことを確認できます。

なお今後Streamlit in Snowflakeのバージョンアップデート(1.25.0以上)があった場合、下記の機能が実装される見込みです。以下のような機能が備わるとより実運用で使用しやすそうです。

• カラム名を日本語の任意の名称に変更可能
• 文字を選択肢から選ぶ、数字の上限・下限の設定等の入力値のバリデーション
• 変更不可能なカラムの指定

5. 感想

Webアプリを作成するのに、サーバー等のインフラ設計・構築なしにSnowflakeの環境上のみですべて完結できるのはとても楽でした！　データ編集に関しては、今のところ排他制御については実装が難しい状態ですが、少人数で利用する

• 予実管理
• 手動作成マスタのメンテナンス
• 為替の値などを入力とした数値シミュレーション

といった用途ではとても有用ではないでしょうか。またStreamlit in Snowflakeのv.1.25.0以上へのバージョンアップデートに期待もしており、それが実現するとますます実運用で使えるツールかなと思っています。
ここまで読んでいただきありがとうございました～