Bigqueryでスケジュールクエリを使って5日分ずつ更新する方法

はじめに

・Bigqueryを使ってLooker Studioでダッシュボードを作成している
・カスタムクエリが毎回回っていて、コストが爆増
・そのためスケジュールクエリを作成
・Bigqueryサウンドボックスをもとに作成

やりたいこと

・はじめに過去データをすべてぶちこんだテーブルを作成する
・そのテーブルに最新5日分のデータを追加していく
・GAのデータ確定に3日ほどかかるため、年のため5日分のデータを取得し既存の場合はUPDATE、新規の場合はINSERTする

工程

もとになるテーブルを作成

全期間のデータをいれたテーブルを作成する

CREATE OR REPLACE TABLE
  sql-book-350002.schedule.test
AS
SELECT
    user_pseudo_id
  , PARSE_DATE("%Y%m%d",event_date) AS event_date
  , (ev.value.string_value) as url
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) as ev
WHERE event_name = "first_visit"
AND ev.key = "page_title"

CREATE OR REPLACE TABLEでテーブルがなければ、新規作成あれば上書きができる
※上書きの場合、既存のデータがすべて削除されるので注意

クエリを作成

以下のクエリでスケジュールクエリを設定する

-- 更新するテーブルを選択
MERGE `sql-book-350002.schedule.test` AS existing_data
USING(
  SELECT
      user_pseudo_id
    , PARSE_DATE("%Y%m%d",event_date) AS event_date
    , (ev.value.string_value) as url
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST(event_params) as ev
  -- 5日分更新
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(DATE(FORMAT_DATETIME('%Y-%m-%d', @run_time, 'Asia/Tokyo')), INTERVAL 4 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(DATE(FORMAT_DATETIME('%Y-%m-%d', @run_time, 'Asia/Tokyo')), INTERVAL 1 DAY)) 
  AND event_name = "first_visit"
  AND ev.key = "page_title"
) AS new_data
-- 既存か新規かを判定するカラムを設定
ON (existing_data.event_date = new_data.event_data)
-- 既存ならUPDATE
WHEN MATCHED THEN
  UPDATE SET
    existing_data.event_date = new_data.event_data, existing_data.url = new_data.url, existing_data.user_pseudo_id = new_data.user_pseudo_id
-- 新規ならINSERT
WHEN NOT MATCHED THEN
  INSERT
    (event_date, url, user_pseudo_id) VALUES(new_data.event_date, new_data.url, new_data.user_pseudo_id)

スケジュールを作成

サウンドボックスじゃ出来ないみたい
↓手順は以下↓
スケジュール→スケジュールされたクエリを新規作成

名前や時刻を設定→クエリ結果の書き込み先は無視(MERGEで設定済み)→自動ロケーションを選択

まとめ

ダッシュボードのコストが1/3になった