Zenn
🍊

Tableauの日付パラメータを使ってユーザがデータ取得期間を変更できるようにさせつつBigQueryクエリ利用量を制限させるようにした話

2024/10/14に公開
BigQuery
Tableau
tech

はじめに

こんにちは。Rakutenでデータエンジニアをやっています。

社内でのTableauユースケースとして，Google BigQueryへTableauからSQLを投げてデータをLive接続で取得する，というケースが増えています。そこで気になるのは，BigQueryのクエリ利用量です。

コストを膨れ上がらせないためにも，クエリ利用量は少なく保ちたい(とくに，データの取得期間を絞りたい)一方で，Tableauでの自由分析のため，カスタムSQLではその取得期間をユーザが可変できるようにさせてあげたいです。Publishedデータソースの作成者の視点から見て，それがサイト管理者・サーバ管理者にとってもユーザにとってもストレスの少ない運用方法だと思うので。

ただLive接続は操作のたびにクエリが接続先に走るので，パラメータ変更時にデータ取得期間が想定を超えてしまうことがありえます。

解決策としていくつかは考えられますが，それぞれ難点があります。

  • Live接続でなく抽出接続にする → CURRENT_DATE()などを使えば，スケジュール実行のフル更新で期間が変更されるが，ユーザからは変更できない。また，レコード数によっては容量が足りなくなる恐れがある
  • Live接続のデータ更新を一時停止してから期間を変更してもらうようにユーザを指導する → ヒューマンなので完璧には守ることができない

ここでは，TableauカスタムSQLにパラメータを挿入しておき，ユーザがViz上で自由にデータ取得期間を変更できるようにしつつ，BigQueryのクエリ利用量に上限を設けるようにするためのアイディアを紹介します。

環境

  • Mac Ventura 13.6
  • Tableau Desktop v2024.2

方針

SQL文のWHERE句は，そこに書かれた条件式の結果がTRUEになるレコードのみを引っ張ってきてくれます。その条件式はFROM句で指定するようなテーブルにまつわるものである必要がないので，Tableauパラメータに関する関数を使った条件式をWHERE句に書いてあげると，Tableauパラメータをユーザが変更できるようにしておきながら，その制約を設けることができると考えました。

今回は，クエリ利用量がデータの取得期間に大きく左右されるテーブルに対して，例文を書いてみます。

BigQueryでクエリ利用量を調べる

データ取得期間の始まり(SDATE)と終わり(EDATE)を，分かりやすいようにDECLARE文を使って指定してあげる形で，下記のようなクエリを書きました。画像ではテーブル名や列名は一部隠しています。また，データ取得期間を絞るときに使用する列名をcolumn_datetimeとしています。

このとき，DATE_DIFF(EDATE, SDATE, DAY) <= 7の部分で，データ取得期間が7日間以下かどうかを判定しています。ここでは7日以下なので，画像の右上にあるように，クエリ利用量は17 GBくらいとあります。

-- On BigQuery

DECLARE
  SDATE DATETIME DEFAULT DATETIME('2024-09-23T20:00:00');
DECLARE
  EDATE DATETIME DEFAULT DATETIME('2024-09-30T02:00:00');

SELECT 
  *
FROM 
  `<project_name>`.`<dataset_name>`.`<table_name>`
WHERE
  column_datetime >= SDATE
  AND column_datetime < EDATE
  AND DATE_DIFF(EDATE, SDATE, DAY) <= 7

less_than_7days_query_expect

実際に計算してみても，処理されたバイト数が17 GBくらいと書かれているので，この分のBigQuery使用料が課金されると考えられます。

less_than_7days_query_result

では次に，このデータ取得期間の始まりを1日ずらすとします。しかし我々はヒューマンなので，ヒューマンエラーでデータ取得期間を2023年からにしてしまいました。先の例では7日程度で17 GBだったので，これが1年になると約50倍に膨れ上がってしまうかもしれません。しかし今回のクエリでは，WHERE句の最後に日数制限を設けているので，クエリ利用量はそんな値にはなっていないようです。

more_than_7days_query_expect

右上のクエリ利用量(推定バイト数)は「計算できませんでした」とありますが，この場合は0 Bになります。なぜなら，WHERE句の最後で，7日以上のデータ取得期間にした場合は条件式がFALSEになるためです。実際に動かしてみても，処理されたバイト数は0 Bです。つまり課金を回避したということになります。

more_than_7days_query_result

なお，このBigQueryのDATE_DIFF関数ですが，年とか月の計算は切り捨てで実現されるようです。

-- On BigQuery

DECLARE
  SDATE DATETIME DEFAULT DATETIME('2023-09-23T20:00:00');
DECLARE
  EDATE DATETIME DEFAULT DATETIME('2024-09-30T02:00:00');

SELECT 
  DATE_DIFF(EDATE, SDATE, YEAR) AS period_year
  , DATE_DIFF(EDATE, SDATE, QUARTER) AS period_quarter
  , DATE_DIFF(EDATE, SDATE, MONTH) AS period_month
  , DATE_DIFF(EDATE, SDATE, DAY) AS period_day
  
-- >> | period_year | period_querter | period_month | period_day |
-- >> | :---------: | :------------: | :----------: | :--------: |
-- >> |      1      |       4        |      12      |    373     |

date_diff_experiment

https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_diff

TableauカスタムSQLでパラメータを挿入しながら実装してみる

上の話を，今度はTableauのカスタムSQLでパラメータを使って書いてみましょう。
BigQuery向けのTableauカスタムSQLではDECLARE文が使えないようなので，WHERE句にベタ書きする形となります。

-- On BigQuery

SELECT 
  *
FROM 
  `<project_name>`.`<dataset_name>`.`<table_name>`
WHERE
  column_datetime >= DATETIME('2024-09-23T20:00:00')
  AND column_datetime < DATETIME('2024-09-30T02:00:00')
  AND DATE_DIFF(DATETIME('2024-09-30T02:00:00'), DATETIME('2024-09-23T20:00:00'), DAY) <= 7

このクエリが，Tableauの2種類の日付型パラメータを使うとこうなります。ここで，パラメータ名はparameter_date_startとparameter_date_endです。このクエリでは，Tableau側で自由に指定できる2つのパラメータの日数差が7日を超えると，WHERE句の判定がFALSEになり，SELECTしても何も返ってこないので，クエリ利用量は0 Bとなります。Tableau上でパラメータのデータタイプをDATE型にしていると，BigQuery上でも日付型のものとして扱われる(ためにDATE_DIFF関数の引数にそのまま使える)ようです。

-- On Tableau with several parameters

SELECT
  *
FROM
  `<project_name>`.`<dataset_name>`.`<table_name>`
WHERE
  column_datetime >= <パラメーター.parameter_date_start>
  AND column_datetime < <パラメーター.parameter_date_end>
  AND DATE_DIFF(<パラメーター.parameter_date_end>, <パラメーター.parameter_date_start>, DAY) <= 7

Tableau_custom_sql

なお，パラメータとしてDATE型でなくDATETIME型を使うならDATETIME()でパラメータを囲います。これにより正しいデータ型で認識される様子。書式設定の問題かもしれませんが，そこまで調べていません。

-- On Tableau with several parameters
-- パラメータとしてdatetime型を使うならDATETIME()で囲ってデータ型を指定しないとうまくいかないぽい

SELECT
  *
FROM
  `<project_name>`.`<dataset_name>`.`<table_name>`
WHERE
  column_datetime >= DATETIME(<パラメーター.parameter_date_start>)
  AND column_datetime < DATETIME(<パラメーター.parameter_date_end>)
  AND DATE_DIFF(DATETIME(<パラメーター.parameter_date_end>), DATETIME(<パラメーター.parameter_date_start>), DAY) <= 7

さて，あとはTableau側でパラメータを表示し，ユーザ側にViz上で変更できるようにします。そのときのカスタムSQLクエリのレコード数を表示してみましょう。

データ取得期間が2024/09/24〜2024/09/28なら，WHERE句末尾に差し込んだDATE_DIFF()による条件もTRUEになるので，レコード数が返ってきました。つまり，いくらかのクエリ利用量がBigQueryで課金されたということになります。

on_tableau_where_true

しかし，データ取得期間を7日以上に広げると，Tableau上では何も表示されません。DATE_DIFF()による条件式がFALSEになり，そもそもカスタムSQLクエリでデータを取得しなかったためです。この制約を設けていれば，ユーザ側の自由設定も叶えつつ，クエリ利用量が膨れ上がらない安全なデータ利活用が可能になります。

on_tableau_where_false

Appendix. TableauのDATE型パラメータを文字列型にBigQuery上でCASTすると

TableauのパラメータがどうBigQuery側で認識されたりCASTされたりするかということを調べたときのクエリがこれ。CAST(~ AS STRING FORMAT ‘~~~’)とした要素は，Tableau上ではすべて文字列型として認識されることを確認しました。

-- On Tableau with several parameters

SELECT
    <パラメーター.parameter_date> AS parameter_date_itself
    , CAST(<パラメーター.parameter_date> AS STRING FORMAT 'YYYY-MM-DD') AS `parameter_date_cast_to_YYYY-MM-DD`
    , CAST(<パラメーター.parameter_date> AS STRING FORMAT 'YYYY/MM/DD') AS `parameter_date_cast_to_YYYYMMDDwithSlash`
    , <パラメーター.parameter_datetime> AS parameter_datetime_itself
    , CAST(<パラメーター.parameter_datetime> AS STRING FORMAT 'YYYYMMDD') AS parameter_datetime_cast_YYYYMMDD

appendix_recognition_parameter_on_BQ

おわりに

ユーザに発想豊かにデータ分析をしてほしいのと，とはいえユーザの経験値や誤操作でシステムに負荷がかかってしまうと面倒だというのとを両方解決するアイディアのひとつだと考えています。こういうのが参考になれば嬉しいです。

また，他にいい方法があれば教えてください。参考にしたいです。

余談

現在，弊社Rakutenではモバイルの社員紹介キャンペーンを実施しております。
下記リンクから，Rakuten会員でログインいただくと，回線変更で最大14,000ポイントがもらえるので，ご興味ある方はぜひアクセスしてみてください！

https://r10.to/hNCoik

Discussion