t11hi

hene.dev

BigQuery のクエリリソースの消費量を抑えて前日のデータを取得する

BigQuery のクエリリソースの消費量を抑えて前日のデータを取得する

まとめ

下記のようにすると、クエリリソースの消費量を抑えて前日のデータを取得できます。

  • _TABLE_SUFFIX を利用し、UTC の 2 日前から今日のデータを一旦取得
  • その後、JST の前日のデータに絞り込む

今回は、KARTE DatahubBigQuery と連携したデータを利用しました。

クエリ

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  |  Google Cloud

テーブル ワイルドカード関数は、ワイルドカード関数で検索したすべてのテーブルをカンマ区切りで結合したものに相当します。テーブル ワイルドカード関数を使うと、BigQuery はワイルドカードに一致するテーブルにだけアクセスし、課金します。テーブル ワイルドカード関数は、クエリの FROM 句で指定します。

レガシー SQL 関数と演算子  |  BigQuery  |  Google Cloud

karteevent テーブル(krt_pockyevent_v1*)は、UTC ベースの日付単位で分割されています

karte_event テーブルへのクエリを作成する

karte_event テーブルの分割単位について

karte_event テーブルは、UTC(協定世界時)ベースの日付単位で分割されています。 {{ karte_event('20181201', '20181203') }}のような日付指定についても、UTC ベースの日付として解釈されます。 別のタイムゾーンについて特定期間のデータを抽出する場合は、1 日分長く指定した上で、WHERE 句で絞り込んでください。

karte_event テーブルへのクエリを作成する

_TABLE_SUFFIX を指定している理由

_TABLE_SUFFIX を利用して、クエリリソースの消費量を抑える絞り込むことで、指定したテーブルだけにアクセスするため、クエリリソースの消費量を抑えることができます。

_TABLE_SUFFIX を使用するとスキャンされるバイト数が大幅に少なくなり、クエリの実行料金を削減できる可能性があります。

ワイルドカード テーブルを使用した複数テーブルに対するクエリ  |  BigQuery  |  Google Cloud

比較

指定しなかった場合

このクエリは実行時に 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_SUFFIXsuffix (接尾辞) の YYYYMMDD は、UTC である。

現在時刻(JST)が 2021-05-21T16:04:01.526908 の場合、下記のように指定すると 2021-05-20T09:00:00.000Z ~ 2021-05-21T08:59:59.000Z のデータを取ってきてしまうため、JSTUST の時差 9 時間 を考えると 2 日前から今日のデータが必要になる。

_TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE('Asia/Tokyo') - 1)

資料