2024-04-22

BigQueryのクエリコストをLookerStudioで可視化する

こんにちは。SENSYプロダクト開発チームPdMの岩間です。

SENSYではDWHにBigQueryを使用しており分析やモデル構築において日々大量のクエリが実行されています。案件ごとにGCP PJTを立ててコストアラートを設定していますが、ユーザー単位でBigQueryの利用状況を確認したい時があります。そういった際にダッシュボードで簡易的にコストを確認する方法について紹介していきます。

クエリコスト取得

クエリコストの計算にはINFORMATION_SCHEMAのJOBSビューを使用します。以下のクエリで実行PJTに対してユーザーごとに日別の合計コストを計算することができます。変数となる部分はコスト計算部分の為替や集計期間です。こちらについてはダッシュボード構築時にパラメータとして設定します。

select 
    date(creation_time, "Asia/Tokyo") as execution_date -- クエリ実行日
    ,split(user_email, "@")[OFFSET(0)] as user -- 実行ユーザー
    ,count(1) as cnt_query -- クエリ回数
    ,count(Distinct query) as uniq_query -- 重複なしクエリ回数
    ,sum(total_bytes_processed*1e-9) as total_gigabytes_processed --処理データ量GB
    ,sum(total_bytes_processed*1e-12*6.25*150) as cost -- コスト
    ,min(timestamp_add(creation_time, interval 9 hour)) as min_execution_datetime
    ,max(timestamp_add(creation_time, interval 9 hour)) as max_execution_datetime
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where 
  date(creation_time, "Asia/Tokyo") >= date_sub(current_date(), interval 1 month)
  and state = "DONE"
  and not (query like '%total_bytes_processed%')
group by 
  execution_date
  ,user
order by 
  execution_date desc
  ,user

今回はJOBSビューを使ってユーザーごとに日別の合計コストを計算しましたが、他にも長時間実行中のクエリやコストの高いクエリなどを抽出できるので、ぜひ公式ドキュメントをご覧ください。 cloud.google.com

ダッシュボード構築

コスト計算クエリをデータソースに設定しダッシュボードを構築していきます。大きく以下3つの作業になります。

コスト計算クエリをカスタムクエリとしてデータソースに設定
為替と集計期間をパラメータとして設定
グラフを作成

最初にコスト計算クエリをカスタムクエリとして設定します。この際に為替と集計期間をパラメータにしておくことでダッシュボード側で操作した際に再度集計が走るようにすることが可能です。クエリでは以下のように@kawase、@interval_monthとして記載します。

select 
    date(creation_time, "Asia/Tokyo") as execution_date -- クエリ実行日
    ,split(user_email, "@")[OFFSET(0)] as user -- 実行ユーザー
    ,count(1) as cnt_query -- クエリ回数
    ,count(Distinct query) as uniq_query -- 重複なしクエリ回数
    ,sum(total_bytes_processed*1e-9) as total_gigabytes_processed --処理データ量GB
    ,sum(total_bytes_processed*1e-12*5*@kawase) as cost -- 金額
    ,min(timestamp_add(creation_time, interval 9 hour)) as min_execution_datetime
    ,max(timestamp_add(creation_time, interval 9 hour)) as max_execution_datetime
from `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where 
  date(creation_time, "Asia/Tokyo") >= date_sub(current_date(), interval @interval_month month)
  and state = "DONE"
  and not (query like '%total_bytes_processed%')
group by 
  execution_date
  ,user
order by 
  execution_date desc
  ,user

カスタムクエリ設定時にパラメータも以下のように設定します。

あとは折れ線グラフや表を好みで設定してダッシュボード構築完了です。以下の例では直感的にわかるように時系列でユーザーごとのコストを表示させたり詳細確認用にテーブルを並べたりしています。

最後に

カスタムクエリはPJTごとに設定できたりパラメータを埋め込むことができるので手間をかけずにインタラクティブなダッシュボードが構築できてとても便利です。（一方で管理しにくくカオスになりがちですが...）

クエリコスト確認の際にはぜひ本記事を参考にしてみてください。

参考

cloud.google.com

2024-02-19

Colaboratoryとスプレッドシートで精度集計を定型化してみた

こんにちは。SENSYプロダクト開発チームPdMの岩間です。

今回はGoogle Colaboratoryとスプレッドシートを使ってAI予測値の精度集計を定型化した話について紹介していきます。

精度集計における属人化の排除や工数削減に繋がるかと思うのでぜひご覧ください。

アウトプットイメージ

今回のお話では最終的に下図のような精度集計がスプレッドシートに出力されます。

これは日付単位で精度を集計した例になっています。

実績値と予測値の平均値のような統計値であったり誤差率のような機械学習的な評価指標、また、予測値の上振れ下振れなどを集計させています。

処理の流れ

自動化にあたって以下の3つを準備します。

予測値と実績値のテーブル
精度出力先のスプレッドシート
予実テーブルを読み込み精度を集計しスプシに出力するColaboratory

Colaboratoryで予実テーブルを読み込みクエリで精度を集計します。

スプレッドシートにはヘッダーや条件付き書式、セルの書式などを設定したマスタシートを作成しておきColaboratoryでそのシートをコピーし、そこに精度集計結果を書き込むようにしています。

準備と実行

予測値と実績値のテーブル準備

まず、予測値と実績値を持ったテーブルを作成します。

下図は需要予測を問題設定とした時のテーブルです。

sales_date(日付), store_name(店舗), sku_name(商品)の組み合わせごとに予測値と実績値を持っています。これが精度集計の最小単位になります。

この際にweek_name(曜日)やgroup_name(カテゴリ情報)など精度集計の軸となるカラムも保有させておくことで、曜日別やカテゴリ別で精度を集計させることができます。

スプレッドシートの準備

次に精度集計結果の出力先となるスプレッドシートへマスタシートを作成します。

以下の項目を設定しています。

数値出力先カラムの設定
- カンマ区切り
- 桁数
カラムごとに条件付書式でカラースケールを設定
ヘッダーの入力と固定

Colaboratoryの準備と実行

最後に予実テーブルから精度を集計し、マスタシートをコピーしそこへ精度集計結果を出力するColaboratoryを作成していきます。

スプシのURLやGCPPJTの定義、import、認証を実行します。

# スプシのURL
ss_url = 'xxx'
# GCP PJT
gcp_project_id = 'xxx'

from google.colab import auth
auth.authenticate_user()

from google.colab import drive
drive.mount('/content/drive/')

import gspread
from google.auth import default
creds, _ = default()

gc = gspread.authorize(creds)
workbook = gc.open_by_url(ss_url)

import pandas as pd
from google.cloud import bigquery

bq_client = bigquery.Client(project_id)

次に精度集計結果が格納されたDataframeからマスタシートをコピーした先へ結果を出力する関数を定義します。


def write_metric(df, sheet_title):
  # マスタシートのシート名
  master_worksheet = workbook.worksheet("xxx")

  sheet = workbook.duplicate_sheet(source_sheet_id=master_worksheet.id, new_sheet_name=sheet_title, insert_sheet_index=4)
  sheet.add_rows(df.shape[0])

  for row_index, row in df.iterrows():
    row_index = row_index + 4
    cell_list = sheet.range(row_index, 1, row_index, sheet.col_count)

    target_row = []
    target_row.append(row['group_by_col_name'])
    target_row.append(row['count'])
    target_row.append(row['cnt_sku'])
    target_row.append(row['avg_actual'])
    target_row.append(row['avg_prod_pred'])
    target_row.append(row['mae_prod'])
    target_row.append(row['mape_prod'])
    target_row.append(row['wape_prod'])
    target_row.append(row['ratio_prod_pred_up'])
    target_row.append(row['ratio_prod_pred_down'])
    target_row.append(row['ratio_prod_pred_fit'])

    for idx, data in enumerate(target_row):
      cell_list[idx].value = data

    res = sheet.update_cells(cell_list, value_input_option='USER_ENTERED')

予実テーブルを読み込み精度集計するクエリを書きます。

結果はデータフレームに格納しています。


query = f'''
with
base as (
  select
      sales_date, week_name, concat(sales_date, " : ", week_name) as group_by_col_name,
      count(distinct sku_id) as cnt_sku,
      avg(actual) as avg_actual,
      avg(prod_pred) as avg_prod_pred,
      COUNT(*) AS count,
      avg(abs(prod_pred - actual)) as mae_prod,
      avg(SAFE_DIVIDE((abs(prod_pred - actual)), actual)) as mape_prod,
      SAFE_DIVIDE(SUM(ABS(prod_pred - actual)),SUM(actual)) as wape_prod,
      SAFE_DIVIDE(COUNTIF(SAFE_DIVIDE((prod_pred - actual), actual) > 0.05), COUNT(*)) AS ratio_prod_pred_up, -- 上振れ率
      SAFE_DIVIDE(COUNTIF(SAFE_DIVIDE((prod_pred - actual),actual) < -0.05), COUNT(*)) AS ratio_prod_pred_down, -- 下振れ率
  from xxx -- 予実テーブル名
  group by
      sales_date, week_name
)
select
  *,
  1 - ratio_prod_pred_up - ratio_prod_pred_down as ratio_prod_pred_fit, -- 的中率
from base
order by
    sales_date, week_name
'''

metric_df = bq_client.query(query).to_dataframe()
print(metric_df.shape)
metric_df.head()

上記のクエリを実行して以下のような集計単位ごとに精度を集計したデータフレームが得られます。この時、クエリ側でgroup byを追加することで日別や店舗別、カテゴリ別など粒度を変えることができます。

最後にスプシへ書き込む関数を実行します。


sheet_title = 'xxx' # 出力先シート名
write_metric(metric_df, sheet_title)

応用

以上までで予測値と実績値の精度集計をスプシに出力することができました。

クエリやマスタシートを工夫することでさらに以下のようなことも可能になります。

精度集計粒度の追加
評価指標の追加
実験中のモデルの精度と今までのモデルの精度比較
折れ線グラフや棒グラフなど図の追加

最後に

Colaboratoryとスプレッドシートを組み合わせることで属人化しがちな精度集計を定型化することができました。集計自体はクエリでやっているので状況に合わせて集計指標を変えるなど自由度が高く非常に便利です。

ぜひ参考になればと思います。

2024-02-05

SENSY プロダクト開発チームの紹介

こんにちは。SENSY プロダクト開発チームPdMの岩間です。

今回はプロダクト開発チームがどのようなプロダクトを開発しているか紹介できればと思います。

開発しているプロダクト

SENSYでは自社サービスとして開発を進めているプロダクトが複数ありますが、プロダクト開発チームでは主にSENSY CLOUDというSaaSを開発しています。

SENSY CLOUDは「お客様からデータを受領し業務課題を解決するAIを提供する」という業務において、ETLから分析、AI構築、精度検証、運用開始までの一連のフローをWEB上で操作・自動化を実現するプロダクトになっています。

AutoMLなどモデリングを自動で実行するツールでは分析者自身で目的変数や特徴量を設定できますが、SENSY CLOUDでは利用するモデルは業務課題に応じてシステム内にあらかじめ実装してあります。

「お客様からデータを受領し業務課題を解決するAIを提供する」ということを繰り返す中で溜まった知見を標準化し内部に搭載することで、一定以上の精度を達成しつつ人的リソースや作業時間の削減を実現しています。また、これによってユーザーへより安価にスピーディに価値を提供することが可能になっています。

現在搭載されているモデルは需要予測や来店客数予測など主要なソリューションのみですが、将来的には今まで実施されてきた案件で使われたモデルを標準化し追加開発していくことを予定しています。

システム処理イメージと利用技術

SENSYではGCPを中心にシステムを構築しておりCLOUDでも多くのサービスを利用しています。

ユーザー側の作業と利用サービスとしては、まず、GCSへデータを配置してもらいLookerStudioに埋め込んだGoogleFormから起動をトリガーします。その後はGCSからBigqueryへデータを流し込みモデリングなどAI構築が実行されます。この時のワークフロー制御はComposerを利用しています。処理状況は都度Slackへ通知されデータ定義の確認や分布、精度などはLookerStudioで確認することができます。フォーマットやNULL制約などデータ定義でおかしなところがあればLookerStudioで確認しデータを修正した上で再度GCS配置から実施するような流れです。データ定義やAIの精度の確認はお客様自身だけでなく、プロダクト開発チームのメンバーでも実施しておりデータ修正の方法や精度改善の提案をしながら実験を繰り返していきます。

CLOUD導入フロー

CLOUDを利用した案件では以下の流れでプロジェクトが進んでいきます。

データETL
モデル構築
机上検証
実証実験
本番運用

一般的なAIを利用した案件においてもこのようなフローで進められることが多いかと思いますが、CLOUDを利用することで各作業が自動化され人的リソースや作業時間の削減を実現しています。

データETL

ETLにおいて重要な役割を果たしているのはデータ定義書です。ユーザーによって保有しているデータ定義が異なるので業界や利用モデルに応じてAIが処理しやすいようなデータ定義をまとめています。これによって主キーやデータ型、NOT NULL制約などデータベースを構築する上で必要な要素を決めることができETL作業を属人化させることなくシステムに任せることができています。

基本的にはデータ定義書をユーザーへ提示しこれに従ってデータを抽出、GCSに格納してもらうように依頼しています。

モデル構築

モデル構築は「お客様からデータを受領し業務課題を解決するAIを提供する」という案件を繰り返してきた中で標準化された特徴量を持ったモデルをシステムに実装しており、これに従って特徴量作成や学習が実行されます。

ユーザーごとに準備できるデータは異なっているためそのまま処理するとNULLになってしまう特徴量のケアが重要になってきます。また、少ないデータでも十分な精度を出せるようにFineTuning可能なモデルの研究を進めています。

机上検証

ここでは投入されたデータを自動でtrain, validation, testに分割しtestを使って精度を確認します。R2やMAEなど機械学習における評価指標や売上高や機会ロス、廃棄ロスなどKPIを用いたビジネスインパクト、誤差の大きいレコードの分析などをダッシュボード（LookerStudio）によってモデルを評価します。

モデルごとに十分にビジネスインパクトを出せる水準の精度が経験則的に把握できているため、この水準を軸にデータの追加準備を依頼したりプロジェクトの進め方を提案するなどしています。

実証実験

机上検証にて十分な精度が確認できた際には実際にAIの予測値を実業務で活用する実証実験へ進みます。シミュレーションだけでは把握しきれない現実の課題（現場からのフィードバックやシステム上の制約など）や机上検証の精度の再現を確認していきます。

ユーザー側でデータ連携の自動化やAI予測値のシステム連携などを準備しCLOUDでは週次や日次でデータの取り込みや予測を実行していきます。

数ヶ月間にわたって実証実験を進め課題やKPIを評価し精度改善や業務フローの整備などを実施していきます。

本番運用

実証実験にてビジネスインパクトが認められればようやく本番導入です。実業務への適用範囲を広げていき年間を通して活用を進めていきます。

最後に

今回はプロダクト開発チームで開発しているSENSY CLOUDの概要について紹介しました。開発チームではCLOUDの開発だけでなく、搭載モデルの研究や分析、CLOUDを利用した案件の推進、運用保守など様々なことに取り組んでいます。今後はそれらについて書いていければと思います。

SENSY Product Dev Tech Blog

SENSYプロダクト開発チームのTechBlogです。