BigQueryで複数テーブルの日付を一括で変える方法(UDFを使う)

UDFとは

BigQueryでは, UDF(user-defined functions)を使えます.
UDFとは, ユーザが独自で定義した関数をクエリ内で利用できる機能です.

今回は, UDFを使って複数テーブルの日付指定を一括で変更する方法について書いていきます.
standard sql前提で書いていきます.

with句で同日付の複数テーブルを使用するクエリ

BidQueryでは, テーブルごとに日付をつけてデータを分けることができます.
日付単位でテーブル分割が行われている時は, _table_suffix を利用することで任意の日付を指定することができます.

-- table suffixで日付を絞り込む

with a as (
select 
 * 
from `A_*`
where _table_suffix = '20180824'
), 
b as (
select 
 * 
from `B_*`
where _table_suffix = '20180824'
), 
c as (
select 
 * 
from `C_*`
where _table_suffix = '20180824'
)

select 
 *
from a
join b on a.id = b.id
join c on a.id = c.id

このように書いた場合, 指定したい日付の変更を行うと3箇所に変更が入ります.

UDFを使って複数テーブルの日付を一括で変更する

UDFを使うことで複数テーブルに指定した日付を一括で変更することができます.
1箇所だけの変更になるので楽チンですね.

  
create temp function  is_the_day(x string) as (
  x = '20180824'
);

with a as (
select 
 * 
from `A_*`
where is_the_day(_table_suffix)
), 
b as (
select 
 * 
from `B_*`
where is_the_day(_table_suffix)
), 
c as (
select 
 * 
from `C_*`
where is_the_day(_table_suffix)
)

select 
 *
from a
join b on a.id = b.id
join c on a.id = c.id

期間を指定することもできる

UDFを使えば対象の期間指定も簡単に行えます.
変更箇所が減り, 一括で日付の変更が行えるようになりました.

  
create temp function  in_range(x string) as (
  x between '20180823' and '20180824'
);

with a as (
select 
 * 
from `A_*`
where in_range(_table_suffix)
), 
b as (
select 
 * 
from `B_*`
where in_range(_table_suffix)
), 
c as (
select 
 * 
from `C_*`
where in_range(_table_suffix)
)

select 
 *
from a
join b on a.id = b.id
join c on a.id = c.id

UDFはまだまだ機能がある

今回は, UDFを利用して日付を一括変更する方法について書きました.
UDFでは, javascriptとSQLがかけるためまだまだ機能はたくさんあります.

今後もできる限り紹介していこうと思います.