Da Vinci Studio 分析チームの末安です。
今回は BigQuery で定数を使う方法をいくつか紹介します。

日付だけ変えて同じクエリを実行したいケースなどよくあると思いますが、変更箇所が漏れていたりして無駄に時間を消費してしまったりします。 こういう場合、定数にしておくと便利です。

まず前提として BigQuery では、定数を定義する機能はありません。( 2021年05月時点 )
ここで紹介する方法は、定数っぽくつかえる BigQuery の機能になります。

今回紹介する方法は以下の 3 つです。

with 句を利用する
UDF を利用する
BigQuery Scripting を利用する

ではひとつずつ説明していきます。

with 句を利用する

with 句で固定値をサブクエリとして定義することで定数っぽくする方法です。

with
  const as (
    select date('2021-04-01') st, date('2021-04-30') en
  ),

  date_array as (
      select 
        date(sequence) item,
      from unnest(generate_date_array('2021-01-01', '2021-05-01')) sequence
  )

select
  date_array.*
from date_array, const
where
  item between const.st and const.en

このように定義した const というサブクエリを CROSS JOIN することで利用します。
都度 from に書く必要があって少し面倒です。

UDF を利用する

UDF とは、ユーザー定義関数のことです。固定値を返す関数を定義することで、定数のように扱うことができます。

cloud.google.com

create temp function const()
as (
  struct(date('2021-04-01') as st, date('2021-04-30') as en)
);

with 
  date_array as (
      select 
        date(sequence) item,
      from unnest(generate_date_array('2021-01-01', '2021-05-01')) sequence
  )

select
  date_array.*
from date_array
where
  item between const().st and const().en

上記の場合では、構造体を返すようにすることで複数の定数を一つの関数で定義できるようにしています。
with 句を利用する方法と違って CROSS JOIN する必要がありません。

デメリットとしては、 JOIN する際の条件のなかで利用できないという点があります。
例えば以下のようなケースでは Subquery in join predicate should depend on one of join sides. のようなエラーがでます。

create temp function const()
as (
  struct(date('2021-04-01') as st, date('2021-04-30') as en)
);

with 
  date_array as (
      select 
        date(sequence) item,
      from unnest(['2021-01-01', '2021-02-01', '2021-04-01','2021-05-01']) sequence
  ),
  date_array2 as (
      select 
        date(sequence) item,
      from unnest(['2021-01-01', '2021-02-01', '2021-04-01','2021-05-01']) sequence
  )

select
  date_array.*
from date_array
  join date_array2 on date_array.item = date_array2.item and date_array2.item = const().st
where date_array.item between const().st and const().en

BigQuery Scripting を利用する

BigQuery Scripting では、変数やif 文やループ処理などのステートメントを特に何の設定もすることなく利用できます。この変数の宣言と代入を利用することで定数のように扱います。

cloud.google.com

declare st, en date;
set st = date('2021-04-01');
set en = date('2021-04-30');

with 
  date_array as (
      select 
        date(sequence) item,
      from unnest(generate_date_array('2021-01-01', '2021-05-01')) sequence
  )

select
  date_array.*
from date_array
where item between st and en

with 句や UDF を利用する方法と違い CROSS JOIN する必要もなく、JOIN の条件の中でも利用できます。 また他の方法よりも可読性も良く、現時点では一番良い方法だと思います。

ただし、BigQuery Scripting を使うと事前にスキャン量を計算できないので注意が必要です。

まとめ

今回は BigQuery で定数を使う方法を紹介しました。クエリを再利用する際に有用なので、ぜひ試してみてください。

BigQuery Scripting はこれ以外でも工夫次第で色々応用できそうなので活用していきたいと思います。

We are hiring!!

Da Vinci Studio では一緒に働ける仲間を絶賛大募集中です！募集職種と詳細に関しては、以下のリンクからそれぞれ確認できます。

Da Vinci Studio Blog

株式会社 Da Vinci Studio は 2023 年 7 月に株式会社 Zaim と統合し、株式会社くふう AI スタジオになりました

BigQuery で定数を使う方法

with 句を利用する

UDF を利用する

BigQuery Scripting を利用する

まとめ

We are hiring!!