【BigQuery×trocco】GENERATE_DATE_ARRAYを使って､troccoの転送ジョブを柔軟な期間指定でループ実行する

GENERATE_DATE_ARRAY とは?

• BigQueryがサポートしている配列関数の1つ
• カレンダーの様な日付配列を返却する
• 日付の期間・間隔の指定が可能｡
  • 日次・週次・月次など､様々なパターンのカレンダーを表現できる
• UNNEST､CROSS JOINとの組み合わせが便利(後述)

使用方法

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

• start_date カレンダーの開始日
• end_date カレンダーの終了日
  • start_date end_dateはDATE型のため､CURRENT_DATE()等の日付関数を利用可能｡
• INTERVAL INT64_expr date_part カレンダーの日付間隔
  • INTERVAL 1 DAY だと1日おき､INTERVAL 1 WEEK は1週間おき…のカレンダーが作成される｡

サンプルクエリ【基本編】

期間の絶対指定×1日おき

SELECT GENERATE_DATE_ARRAY('2023-07-01', '2023-07-08',INTERVAL 1 DAY) AS example;

または､1日おきの場合はINTERVAL~を省略できるので以下でもOK｡

SELECT GENERATE_DATE_ARRAY('2023-07-01', '2023-07-08') AS example;

example
[2023-07-01,2023-07-02,2023-07-03,2023-07-04,2023-07-05,2023-07-06,2023-07-07,2023-07-08]

コンソール上のBigQueryから実行すると一見普通の値のように表示されるが､実際は配列なので注意(疲労時によく混乱します)

期間をCURRENT_DATE()で相対指定×1日おき

SELECT CURRENT_DATE() as today
SELECT GENERATE_DATE_ARRAY(CURRENT_DATE(), DATE_ADD(CURRENT_DATE(),INTERVAL 1 WEEK)) AS example;

today
2023-07-09

example
[2023-07-09,2023-07-10,2023-07-11,2023-07-12,2023-07-13,2023-07-14,2023-07-15,2023-07-16]

期間の絶対指定×1週おき

SELECT GENERATE_DATE_ARRAY('2023-07-01', '2023-07-08',INTERVAL 1 WEEK) AS example;

example
[2023-07-01,2023-07-08]

期間をCURRENT_DATE()で相対指定×1週おき

SELECT CURRENT_DATE() as today
SELECT GENERATE_DATE_ARRAY(CURRENT_DATE(), DATE_ADD(CURRENT_DATE(),INTERVAL 1 WEEK),INTERVAL 1 WEEK) AS example;

today
2023-07-09

example
[2023-07-09,2023-07-16]

期間の絶対指定×1月おき ※よく使う

SELECT GENERATE_DATE_ARRAY('2023-01-01', '2023-12-31',INTERVAL 1 MONTH) AS example;

example
[2023-01-01,2023-02-01,2023-03-01,2023-04-01,2023-05-01,2023-06-01,2023-07-01,2023-08-01,2023-09-01,2023-10-01,2023-11-01,2023-12-01]

期間をCURRENT_DATE()で相対指定×1月おき ※よく使う

SELECT CURRENT_DATE() as today
SELECT GENERATE_DATE_ARRAY(CURRENT_DATE(), DATE_ADD(CURRENT_DATE(),INTERVAL 1 YEAR),INTERVAL 1 MONTH) AS example;

today
2023-07-09

example
[2023-07-09,2023-08-09,2023-09-09,2023-10-09,2023-11-09,2023-12-09,2024-01-09,2024-02-09,2024-03-09,2024-04-09,2024-05-09,2024-06-09,2024-07-09]

サンプルクエリ【応用編】

日付関数やUNNEST,CROSS JOINと組み合わせると､かなり都合よく使えます｡

1年間､1ヶ月ごと､月初・月末の日付を取得するカレンダーを作成する

DATE_TRUNC()､LAST_DAY()等の日付関数と組み合わせることで､月初・月末のリストが簡単に作成できます｡

SELECT
  FORMAT_DATE('%Y%m',date) AS ym,
  DATE_TRUNC(date, MONTH)AS first_date,
  LAST_DAY(date, MONTH) AS last_date
FROM
  UNNEST( GENERATE_DATE_ARRAY( '2023-01-01', '2023-12-31', INTERVAL 1 MONTH) ) AS date ;

ym	first_date	last_date
202301	2023-01-01	2023-01-31
202302	2023-02-01	2023-02-28
202303	2023-03-01	2023-03-31
202304	2023-04-01	2023-04-30
202305	2023-05-01	2023-05-31
202306	2023-06-01	2023-06-30
202307	2023-07-01	2023-07-31
202308	2023-08-01	2023-08-31
202309	2023-09-01	2023-09-30
202310	2023-10-01	2023-10-31
202311	2023-11-01	2023-11-30
202312	2023-12-01	2023-12-31

CROSS JOINを用いて､日付×任意のデータの組合せ表を作成する

SELECT id FROM user;

SELECT
  user.id as id,
  DATE_TRUNC(date, MONTH)AS first_date,
  LAST_DAY(date, MONTH) AS last_date
FROM
  UNNEST( GENERATE_DATE_ARRAY( '2023-01-01', '2023-12-31', INTERVAL 1 MONTH) ) AS date
CROSS JOIN
  user
ORDER BY 2,1;

id
1001
1002
1003

id	first_date	last_date
1001	2023-01-01	2023-01-31
1002	2023-01-01	2023-01-31
1003	2023-01-01	2023-01-31
1001	2023-02-01	2023-02-28
1002	2023-02-01	2023-02-28
1003	2023-02-01	2023-02-28
	中略
1001	2023-11-01	2023-11-30
1002	2023-11-01	2023-11-30
1003	2023-11-01	2023-11-30
1001	2023-12-01	2023-12-31
1002	2023-12-01	2023-12-31
1003	2023-12-01	2023-12-31

GENERATE_DATE_ARRAY を troccoの「ループ機能」で使う

troccoとは?

SaaSのETLツールです｡

troccoのループ機能とは?

troccoでは､各種設定に変数(カスタム変数)を埋め込むことができます｡
その変数に値をまとめて指定し､値ごとにジョブを実行できる機能です｡

「BigQueryクエリ結果でループ」で利用すると便利だったので､紹介します｡

Case1. 月別にデータが格納されたテーブルを､一気に作成する

転送設定

以下の様に設定します

転送元MySQLに$first_date$ $last_date$
転送先BigQueryに $ym$　というカスタム変数を指定しました｡

補足…

※転送元MySQLに調度いいデータが手元になかったため､簡易的なクエリを設定しています｡
※実際は以下のようなwhere句にカスタム変数を埋め込み､範囲指定でデータを取得するケースが多いです｡

troccoに設定するmysqlのクエリサンプル

SELECT * FROM test_table WHERE create_date BETWEEN '$first_date$' AND '$last_date$';

ワークフロー(ループの設定方法)

ワークフローは次のように設定します｡

カスタム変数でループ実行にチェックを付けて､BigQueryのクエリ結果でループを指定｡
クエリには先程作成したコチラを埋め込みます｡

プレビュー実行すると､実行予定回数とその中身が表示されます｡
今回の場合だと､12回ループ実行されます｡

保存して実行します｡

ログを見ると､ループごとに変数が渡され､実行されていることがよくわかります｡

結果

BigQuery側を確認してみます｡
今回､出力先テーブル名にカスタム変数$ym$を埋め込んでループ実行したので､ループ単位でテーブルが作成されていることがわかります｡

テーブルの中身を見てみます｡
ループごとに､カスタム変数$first_date$ $end_date$が展開されていることがわかります｡

こんな時に使える

• データソースから期間別に取得したデータを､DWH上で期間別のテーブルに分割したい
• 上記の処理を､なるべく少ない実行回数でやりたい
• 期間指定をクエリでスマートにやりたい

Case2. 日付×idごとにジョブを動かす

転送設定

以下の様に設定します
Case1に加え､$id$というカスタム変数を追加しています｡

補足…

※転送元MySQLに調度いいデータが手元になかったため､簡易的なクエリを設定しています｡
※実際は以下のようなwhere句にカスタム変数を埋め込み､範囲指定でデータを取得するケースが多いです｡

troccoに設定するmysqlのクエリサンプル

SELECT * FROM test_table WHERE create_date BETWEEN '$first_date$' AND '$last_date$' AND id = '$id$';

ワークフロー(ループの設定方法)

ループ設定で､CROSS JOINを使ってみます｡
※UNNEST(['AAA','BBB','CCC']) as sampleの部分は、テーブルを指定してもOK。ただしループ回数が膨大な数にならないように注意。

プレビューを確認すると､日付×idごとにループ設定がされていることがわかります｡

保存して実行します｡
ログを確認すると､日付×idごとにループ設定がされていることがわかります｡

結果

テーブルの数自体は、Case1と同じ（転送元の設定が同じゆえ）

Case1と比較すると、日付×idごとのレコードになっていることがわかる

こんな時に使える

• 特定の顧客id×特定の期間ごとにデータを取得したい場合、ジョブを分割したい場合

利用した関数のリファレンス

UNNEST

• GENERATE_DATE_ARRAYで 作成した日付をレコードとして扱うためには､UNNESTを用い配列から個々のレコードへ展開する必要がある

DATE_TRUNC

• DATE値を指定した粒度で切り捨てる
• MONTHで切り捨てる = 月初の日付を返す

LAST_DAY

• 日付の最終日を返す

CROSS JOIN

最後に

troccoはBigQuery(SQL)との相性が良く､組み合わせ次第で色々なことができることが伝われば嬉しいです｡
実はフリープランもありますので､気になった方は是非お試しください!!