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

# GENERATE_DATE_ARRAY とは?

https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#generate_date_array

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

## 使用方法
```sql
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日おき

```sql
SELECT GENERATE_DATE_ARRAY('2023-07-01', '2023-07-08',INTERVAL 1 DAY) AS example;
```
または､1日おきの場合は``INTERVAL~``を省略できるので以下でもOK｡
```sql
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から実行すると一見普通の値のように表示されるが､実際は配列なので注意(疲労時によく混乱します)
![2023-07-01_2023-07-08_1day.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/2d0ed995-a3e4-8bc9-73b5-829b3e3c56a2.png)

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

```sql
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週おき

```sql
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週おき
```sql
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月おき ※よく使う

```sql
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月おき ※よく使う

```sql
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()`等の日付関数と組み合わせることで､月初・月末のリストが簡単に作成できます｡
```sql
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を用いて､日付×任意のデータの組合せ表を作成する
```sql
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ツールです｡

https://trocco.io/lp/index.html


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

https://qiita.com/hiro_koba_jp/items/a650bf231b11d13ad64d


「[BigQueryクエリ結果でループ](https://qiita.com/hiro_koba_jp/items/a650bf231b11d13ad64d#3-bigquery%E3%81%AE%E3%82%AF%E3%82%A8%E3%83%AA%E7%B5%90%E6%9E%9C%E3%82%92%E7%94%A8%E3%81%84%E3%81%9F%E5%8B%95%E7%9A%84%E3%81%AA%E3%83%AB%E3%83%BC%E3%83%97
)」で利用すると便利だったので､紹介します｡

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

### 転送設定
以下の様に設定します

![test_case_1_etl.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/ef875458-ed8f-3919-84e3-5cecd62d301f.png)


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

<details><summary>補足…</summary>

※転送元MySQLに調度いいデータが手元になかったため､簡易的なクエリを設定しています｡
※実際は以下のようなwhere句にカスタム変数を埋め込み､範囲指定でデータを取得するケースが多いです｡
```sql:troccoに設定するmysqlのクエリサンプル
SELECT * FROM test_table WHERE create_date BETWEEN '$first_date$' AND '$last_date$';
```
</details>

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

ワークフローは次のように設定します｡
![workflow_test_case_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/339992fa-1d96-042e-0000-e708b920aa48.png)

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

![workflow_test_case_1_detail.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/1f7229e9-5c56-156c-8812-912f0fc95515.png)


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

![workflow_test_case_1_prev.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/2cd882c9-b6cb-f28d-6031-a847f7ab6a7b.png)

保存して実行します｡

ログを見ると､ループごとに変数が渡され､実行されていることがよくわかります｡
![workflow_test_case_1_log.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/2cb6c4db-267b-2acb-60a0-123c4dd87f6e.png)


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

![test_case_1_bq.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/e5af5a33-2727-f129-dd5c-4efea348eda2.png)

テーブルの中身を見てみます｡
ループごとに､カスタム変数`$first_date$` `$end_date$`が展開されていることがわかります｡
![test_case_1_bq_prev.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/a2a44fc0-98d7-0812-dfa0-55ccddc7fe47.png)
![test_case_1_bq_prev_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/4dca6f98-5f89-32d9-a381-0e979d6949da.png)


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


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

### 転送設定
以下の様に設定します
Case1に加え､`$id$`というカスタム変数を追加しています｡
![test_case_2_etl.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/2976357f-f147-bc86-4176-343abe456cb7.png)


<details><summary>補足…</summary>

※転送元MySQLに調度いいデータが手元になかったため､簡易的なクエリを設定しています｡
※実際は以下のようなwhere句にカスタム変数を埋め込み､範囲指定でデータを取得するケースが多いです｡
```sql:troccoに設定するmysqlのクエリサンプル
SELECT * FROM test_table WHERE create_date BETWEEN '$first_date$' AND '$last_date$' AND id = '$id$';
```
</details>

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

ループ設定で､CROSS JOINを使ってみます｡
※`UNNEST(['AAA','BBB','CCC']) as sample`の部分は、テーブルを指定してもOK。ただしループ回数が膨大な数にならないように注意。
![workflow_test_case_2_detail.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/ed8228fe-4a32-a2c8-95f4-84684251823b.png)



プレビューを確認すると､日付×idごとにループ設定がされていることがわかります｡
![workflow_test_case_2_prev.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/fcb3cafa-d078-ca22-3034-890c828534b7.png)

保存して実行します｡
ログを確認すると､日付×idごとにループ設定がされていることがわかります｡
![workflow_test_case_2_log.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/a70f3ec2-dc01-5502-e911-46ef8af705d5.png)


### 結果
テーブルの数自体は、Case1と同じ（転送元の設定が同じゆえ）
![test_case_2_bq.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/150e9fb6-1214-50e6-469d-b4cdd37127f3.png)


Case1と比較すると、日付×idごとのレコードになっていることがわかる
![test_case_2_bq_prev.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/97298b35-6777-554b-f9e4-007ce2349be6.png)

![test_case_2_bq_prev_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3033246/3ad1cc59-5daf-39ed-e11e-2096197df2a9.png)


### こんな時に使える

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



# 利用した関数のリファレンス
## UNNEST
https://cloud.google.com/bigquery/docs/arrays?hl=ja#flattening_arrays
* GENERATE_DATE_ARRAYで 作成した日付をレコードとして扱うためには､UNNESTを用い配列から個々のレコードへ展開する必要がある

## DATE_TRUNC
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_trunc
* DATE値を指定した粒度で切り捨てる
* MONTHで切り捨てる = 月初の日付を返す

## LAST_DAY
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#last_day
* 日付の最終日を返す

## CROSS JOIN
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#cross_join


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

https://trocco.io/lp/inquiry_free.html