BigQuery のクエリリソースの消費量を抑えて前日のデータを取得する
BigQuery のクエリリソースの消費量を抑えて前日のデータを取得する
まとめ
下記のようにすると、クエリリソースの消費量を抑えて前日のデータを取得できます。
_TABLE_SUFFIXを利用し、UTCの 2 日前から今日のデータを一旦取得- その後、
JSTの前日のデータに絞り込む
今回は、KARTE Datahub を BigQuery と連携したデータを利用しました。
クエリ
WITH record AS (
SELECT
sync_date
, TIMESTAMP(
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', sync_date, 'Asia/Tokyo')
) AS sync_date_jst
, session_id
FROM
`karte-data.karte_stream_{{API_KEY}}.krt_pockyevent_v1_*` AS log
WHERE
-- ↓ ここから
-- _TABLE_SUFFIX で指定しているのは、クエリリソースの消費量を抑えるため
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Tokyo') - 2) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Tokyo'))
-- 前日のアクセスログを取得
AND FORMAT_DATE('%Y%m%d', sync_date, 'Asia/Tokyo') = FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Tokyo') - 1)
-- ↑ ここまで
)
--
SELECT *
FROM record
ORDER BY
sync_date_jst DESC
_TABLE_SUFFIX とは
WHERE _TABLE_SUFFIX = '20210520' とかくと、20210520 のデータだけ取得できる。
20210520 のデータは、hogehoge20210520 といった名前のテーブルに保存されている。
ワイルドカード関数である _TABLE_SUFFIX を使うと、指定したワイルドカードに一致するテーブルにだけアクセスすることができる。
- 例:
hogehoge20210519,hogehoge20210520...
ワイルドカード テーブルの各行には、ワイルドカード文字が一致した値を含む特別な列「
_TABLE_SUFFIX」があります。
テーブル ワイルドカード関数は、ワイルドカード関数で検索したすべてのテーブルをカンマ区切りで結合したものに相当します。テーブル ワイルドカード関数を使うと、BigQuery はワイルドカードに一致するテーブルにだけアクセスし、課金します。テーブル ワイルドカード関数は、クエリの FROM 句で指定します。
karteevent テーブル（krt_pockyevent_v1*）は、UTC ベースの日付単位で分割されています
karte_event テーブルの分割単位について
karte_event テーブルは、UTC（協定世界時）ベースの日付単位で分割されています。 {{ karte_event('20181201', '20181203') }}のような日付指定についても、UTC ベースの日付として解釈されます。 別のタイムゾーンについて特定期間のデータを抽出する場合は、1 日分長く指定した上で、WHERE 句で絞り込んでください。
_TABLE_SUFFIX を指定している理由
_TABLE_SUFFIX を利用して、クエリリソースの消費量を抑える絞り込むことで、指定したテーブルだけにアクセスするため、クエリリソースの消費量を抑えることができます。
_TABLE_SUFFIXを使用するとスキャンされるバイト数が大幅に少なくなり、クエリの実行料金を削減できる可能性があります。
比較
指定しなかった場合
このクエリは実行時に 9.7 GB のデータを処理します。
指定した場合
このクエリは実行時に 175.5 MB のデータを処理します。
前日のデータを取得する場合の _TABLE_SUFFIX で指定する期間について
前日のデータを取得したい場合 _TABLE_SUFFIX で、UTC の 2 日前から今日のデータを一旦取得してから、
-- _TABLE_SUFFIX で指定しているのは、クエリリソースの消費量を抑えるため
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Tokyo') - 2) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Tokyo'))
JST の前日のデータを取得すると良い。
-- 前日のアクセスログを取得
AND FORMAT_DATE('%Y%m%d', sync_date, 'Asia/Tokyo') = FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Tokyo') - 1)
上記対応した理由
_TABLE_SUFFIX の suffix (接尾辞) の YYYYMMDD は、UTC である。
現在時刻(JST)が 2021-05-21T16:04:01.526908 の場合、下記のように指定すると 2021-05-20T09:00:00.000Z ~ 2021-05-21T08:59:59.000Z のデータを取ってきてしまうため、JST と UST の時差 9 時間 を考えると 2 日前から今日のデータが必要になる。
_TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Tokyo') - 1)
