BigQueryで祝日判定UDFを公開した&AlphaSQLで依存関係気にせずデプロイ

[BigQuery Advent Calendar 2020](https://qiita.com/advent-calendar/2020/bigquery)の1日目の記事です。

この記事は、BigQueryで**公開した祝日判定UDFの紹介**と、依存関係のある**UDF群を[Matts966氏のAlphaSQL](https://github.com/Matts966/alphasql)で思考停止デプロイ**できた事実の共有です。

:::note info
2023-01-02現在は、Google Cloud版Dataformを用いたデプロイを行なっており、データセット`bqfunc.holidays_in_japan__us`にて提供されています。

リポジトリ：https://github.com/bqfun/bqfunc

以前提供していた、USリージョン版`bqfunc.holidays_in_japan`は2023-01-31でサポートを終了します。
USリージョンでお使いの方は`bqfunc.holidays_in_japan__us`への書き換えをお願いします。
また、2023-02-01以降、`bqfunc.holidays_in_japan`としてasia-northeast1リージョンの関数を提供予定です。
:::

# 公開データセットの紹介

今回作った祝日判定の関数は公開データセットにしてありますので、BigQuery USリージョンであれば、自由に呼び出すことができます。責任を取ることはできませんが、自由にお使いください。

## bqfunc.holidays_in_japan.HOLIDAY_NAME(d)
DATE型の引数が祝日であれば祝日名、そうでなければ`NULL`を返します。

```sql
ASSERT "勤労感謝の日" = bqfunc.holidays_in_japan.HOLIDAY_NAME("2020-11-23");
ASSERT bqfunc.holidays_in_japan.HOLIDAY_NAME("2020-11-24") IS NULL;
```

## bqfunc.holidays_in_japan.HOLIDAYS()
[内閣府「国民の祝日」](https://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html)のデータを返します。

```sql
SELECT
  *
FROM
  UNNEST(bqfunc.holidays_in_japan.HOLIDAYS());

-- date       | name
-- -----------+-----------
-- 1955-01-01 | 元日
-- 1955-01-15 | 成人の日
-- 1955-03-21 | 春分の日
-- 1955-04-29 | 天皇誕生日
-- 1955-05-03 | 憲法記念日
-- ...

```

## bqfunc.holidays_in_japan.NETWORKDAYS(start_date, end_date, weekends, holidays)
startとend間の営業日数（曜日weekends（7,1=土、日曜日）と祝日リストholidaysでない日数）を返します。

```sql
ASSERT 3 = bqfunc.holidays_in_japan.NETWORKDAYS("2020-12-01", "2020-12-07", [1, 7], [DATE "2020-12-03", "2020-12-04"]);
```

## bqfunc.holidays_in_japan.NETWORKDAYS_ON_OFF_WEEKEND_DAYS_AND_HOLIDAYS_IN_JAPAN(start_date, end_date)
startとend間の土日でない、かつ国民の祝日・休日でない日数を返します。

```sql
ASSERT 7 = bqfunc.holidays_in_japan.NETWORKDAYS_ON_OFF_WEEKEND_DAYS_AND_HOLIDAYS_IN_JAPAN("2020-11-20", "2020-12-01");
```

# Contribute募集中です
今回のリポジトリはこちら。[na0fu3y/bqfunc](https://github.com/na0fu3y/bqfunc)

祝日関数は利用機会が多いとは言えませんが、各組織で少しずつ必要になる機能です。
継続的に保守できるよう頑張りますが、ぜひContributeください。


# 継続的デプロイ

継続的デプロイは、AlphaSQLと、DAGの解釈（今回はPython）が動けば良いです。
今回は、GitHub Actionsで実装しました。

AlphaSQLは、Matts966氏が作成した、SQLファイル群の型・スキーマチェック、依存性チェックを行ってくれるアナライザです。BigQueryと一部を共有しているであろうSQLアナライザ[ZetaSQL](https://github.com/google/zetasql)を使いやすく内包しています。

AlphaSQLのalphadagという、SQLファイル群から、依存性チェックを行い、並列実行可能なようにDAGを作成してくれる機能を使うとこのようなDAGが生成できます（一部）。
![dag.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/135291/95bde8ba-e773-9e77-fd40-0ee34a7b2c4c.png)



## [.github/workflows/deploy.yml](https://github.com/na0fu3y/bqfunc/blob/master/.github/workflows/deploy.yml)

GitHub Actionsで大まかにやっていることは以下3つです。

0. AlphaSQL、Python依存モジュールをインストール
0. alphadagコマンドでSQLファイル群を解釈させて、dag.dotファイルを作成
0. execute.pyでdag.dotファイルとSQLファイル群から並列実行

結構単純ですが、パラメータも多く、コード量はやや多くなりますね。
ここでalphadagがキモで、並列実行可能なように、DAGを生成しています。

```yaml:.github/workflows/deploy.yml

name: Deploy
on:
  push:
    branches:
    - master
jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v1
    - uses: actions/setup-python@v2
      with:
        python-version: '3.8.6'
    - run: pip install -r requirements.txt
    - run: python make_sqls.py
    - name: Install AlphaSQL
      run: |
        wget https://github.com/Matts966/alphasql/releases/latest/download/alphasql_linux_x86_64.tar.gz
        sudo tar -zxvf alphasql_linux_x86_64.tar.gz -C /usr/local/bin --strip=1
        rm alphasql_linux_x86_64.tar.gz
    - run: alphadag --output_path ./dag.dot .
    - uses: GoogleCloudPlatform/github-actions/setup-gcloud@master
      with:
        version: '270.0.0'
        service_account_key: ${{ secrets.GCP_SA_KEY }}
        export_default_credentials: true
    - run: python execute.py bqfunc US ./dag.dot
```

## [execute.py](https://github.com/na0fu3y/bqfunc/blob/master/execute.py)

execute.pyはbq_jobrunnerを使って、DAGを並列実行します。

```python:execute.py
import argparse
import os

from bq_jobrunner.bq_jobrunner import BQJobrunner


def execute(project_id: str, region: str, dot_path: str):
    runner = BQJobrunner(
        project_id,
        location=region,
    )
    runner.compose_query_by_dot_path(dot_path)
    runner.execute()


if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument("project_id")
    parser.add_argument("region")
    parser.add_argument("dot_path")
    args = parser.parse_args()
    execute(args.project_id, args.region, args.dot_path)
```

# 各祝日関数の実装

日付を与えられた時に、祝日かどうか判定する必要に迫られたことはありませんか。
営業日数を計算したい、なぜ休みなのか知りたいなど。

しかし祝日は、以下のようなややこしい側面もあり、メンテナンスが大変です。

- [3つの祝日を移動させる改正法が、27日の参議院本会議で可決・成立する](https://www3.nhk.or.jp/news/html/20201127/k10012733871000.html)など移動になる
- 春分の日、秋分の日のように国立天文台が、毎年2月に翌年の祝日が公表する
- 前日と翌日の両方を「国民の祝日」に挟まれた平日は休日になる など

メンテナンスが大変な割りに、様々な組織で必要になる機能です。
今回は、[「国民の祝日」について](https://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html)に大体沿って祝日判定関数を実装しています。

依存関係デプロイ体制が整っているので、関数間の依存は気にせずひたすらにSQLを書くだけになります。
今回は関数なので必須ですが、テーブルでAlphaSQLの機能を使ってDAGを作成する場合には、CREATE TABLEを書き、宛先をSQLファイル内に埋め込む必要があります。

ここでは、祝日判定の代表的な関数だけ紹介します。

## [udfs/holidays_in_japan/holiday_name.sql](https://github.com/na0fu3y/bqfunc/blob/master/udfs/holidays_in_japan/holiday_name.sql)

外側の依存の多い関数です。AlphaSQLで依存する関数の後にデプロイしてくれるので、デプロイ時に実行順序でエラーになる心配はありません。

```sql:udfs/holidays_in_japan/holiday_name.sql
CREATE OR REPLACE FUNCTION holidays_in_japan.HOLIDAY_NAME(d DATE)
AS (
  IFNULL(holidays_in_japan.EXTRA_HOLIDAY_NAME_IF_HOLIDAY(d),
    CASE
      WHEN holidays_in_japan.IS_NEW_YEARS_DAY(d) THEN "元日"
      WHEN holidays_in_japan.IS_COMING_OF_AGE_DAY(d) THEN "成人の日"
      WHEN holidays_in_japan.IS_NATIONAL_FOUNDATION_DAY(d) THEN "建国記念の日"
      WHEN holidays_in_japan.IS_EMPERORS_BIRTHDAY(d) THEN "天皇誕生日"
      WHEN holidays_in_japan.IS_VERNAL_EQUINOX_DAY(d) THEN "春分の日"
      WHEN holidays_in_japan.IS_SHOWA_DAY(d) THEN "昭和の日"
      WHEN holidays_in_japan.IS_CONSTITUTION_MEMORIAL_DAY(d) THEN "憲法記念日"
      WHEN holidays_in_japan.IS_GREENERY_DAY(d) THEN "みどりの日"
      WHEN holidays_in_japan.IS_CHILDRENS_DAY(d) THEN "こどもの日"
      WHEN holidays_in_japan.IS_MARINE_DAY(d) THEN "海の日"
      WHEN holidays_in_japan.IS_MOUNTAIN_DAY(d) THEN "山の日"
      WHEN holidays_in_japan.IS_RESPECT_FOR_THE_AGED_DAY(d) THEN "敬老の日"
      WHEN holidays_in_japan.IS_AUTUMNAL_EQUINOX_DAY(d) THEN "秋分の日"
      WHEN holidays_in_japan.IS_HEALTH_SPORTS_DAY(d) THEN "体育の日"
      WHEN holidays_in_japan.IS_SPORTS_DAY(d) THEN "スポーツの日"
      WHEN holidays_in_japan.IS_CULTURE_DAY(d) THEN "文化の日"
      WHEN holidays_in_japan.IS_LABOUR_THANKSGIVING_DAY(d) THEN "勤労感謝の日"
      WHEN holidays_in_japan.IS_KOKUMIN_NO_KYUJITSU(d) THEN "国民の休日"
      WHEN holidays_in_japan.IS_FURIKAE_KYUJITSU(d) THEN "振替休日"
    END)
);
```

## [udfs/holidays_in_japan/is_coming_of_age_day.sql](https://github.com/na0fu3y/bqfunc/blob/master/udfs/holidays_in_japan/is_coming_of_age_day.sql)

成人の日判定関数です。`AND`、`OR`を使って、`IF`関数よりBigQuery計算時の並列性が上がることを期待しています。

```sql:udfs/holidays_in_japan/is_coming_of_age_day.sql
-- 成人の日 一月の第二月曜日
CREATE OR REPLACE FUNCTION holidays_in_japan.IS_COMING_OF_AGE_DAY(d DATE)
AS (
  d < "2000-01-01" AND EXTRACT(DAYOFYEAR FROM d) = 15 OR "2000-01-01" <= d AND EXTRACT(MONTH FROM d) =
  1 AND EXTRACT(DAYOFWEEK FROM d) = 2 AND EXTRACT(DAY FROM d) BETWEEN 8 AND 14
);
```

## [udfs/holidays_in_japan/is_furikae_kyujitsu.sql](https://github.com/na0fu3y/bqfunc/blob/master/udfs/holidays_in_japan/is_furikae_kyujitsu.sql)

振替休日判定関数です。日曜日と祝日が被っている場合、次の平日が振替休日になります。
`GENERATE_DATE_ARRAY`で、前回の月曜日から、前日まで全てが祝日かを判断します。

```sql:udfs/holidays_in_japan/is_furikae_kyujitsu.sql
CREATE OR REPLACE FUNCTION holidays_in_japan.IS_FURIKAE_KYUJITSU(d DATE)
AS (
  "1973-04-12" <= d AND NOT holidays_in_japan.IS_NEW_YEARS_DAY(d) AND NOT holidays_in_japan.IS_COMING_OF_AGE_DAY(d) AND
  NOT holidays_in_japan.IS_NATIONAL_FOUNDATION_DAY(d) AND NOT holidays_in_japan.IS_EMPERORS_BIRTHDAY(d) AND
  NOT holidays_in_japan.IS_VERNAL_EQUINOX_DAY(d) AND NOT holidays_in_japan.IS_SHOWA_DAY(d) AND NOT holidays_in_japan.IS_CONSTITUTION_MEMORIAL_DAY(d) AND
  NOT holidays_in_japan.IS_GREENERY_DAY(d) AND NOT holidays_in_japan.IS_CHILDRENS_DAY(d) AND NOT holidays_in_japan.IS_MARINE_DAY(d) AND
  NOT holidays_in_japan.IS_MOUNTAIN_DAY(d) AND NOT holidays_in_japan.IS_RESPECT_FOR_THE_AGED_DAY(d) AND
  NOT holidays_in_japan.IS_AUTUMNAL_EQUINOX_DAY(d) AND NOT holidays_in_japan.IS_HEALTH_SPORTS_DAY(d) AND
  NOT holidays_in_japan.IS_SPORTS_DAY(d) AND NOT holidays_in_japan.IS_CULTURE_DAY(d) AND NOT holidays_in_japan.IS_LABOUR_THANKSGIVING_DAY(d) AND
  holidays_in_japan.EXTRA_HOLIDAY_NAME_IF_HOLIDAY(d) IS NULL AND (
    SELECT
      IFNULL(LOGICAL_AND (holidays_in_japan.IS_NEW_YEARS_DAY(day) OR holidays_in_japan.IS_COMING_OF_AGE_DAY(day) OR
          holidays_in_japan.IS_NATIONAL_FOUNDATION_DAY(day) OR holidays_in_japan.IS_EMPERORS_BIRTHDAY(day) OR holidays_in_japan.IS_VERNAL_EQUINOX_DAY(day) OR
          holidays_in_japan.IS_SHOWA_DAY(day) OR holidays_in_japan.IS_CONSTITUTION_MEMORIAL_DAY(day) OR holidays_in_japan.IS_GREENERY_DAY(day) OR
          holidays_in_japan.IS_CHILDRENS_DAY(day) OR holidays_in_japan.IS_MARINE_DAY(day) OR holidays_in_japan.IS_MOUNTAIN_DAY(day) OR
          holidays_in_japan.IS_RESPECT_FOR_THE_AGED_DAY(day) OR holidays_in_japan.IS_AUTUMNAL_EQUINOX_DAY(day) OR
          holidays_in_japan.IS_HEALTH_SPORTS_DAY(day) OR holidays_in_japan.IS_SPORTS_DAY(day) OR holidays_in_japan.IS_CULTURE_DAY(day) OR
          holidays_in_japan.IS_LABOUR_THANKSGIVING_DAY(day) OR holidays_in_japan.EXTRA_HOLIDAY_NAME_IF_HOLIDAY(day) IS NOT NULL),
        FALSE)
    FROM
      UNNEST(GENERATE_DATE_ARRAY(d + 1 - EXTRACT(DAYOFWEEK FROM d), d - 1)) AS day
  )
);
```

# 他にもリポジトリでやってること
[Makefile](https://github.com/na0fu3y/bqfunc/blob/master/Makefile)に書いてあることですが、以下のような便利コマンドを用意しています。

- [Matts966氏作成のZetaSQL Formatter](https://github.com/Matts966/zetasql-formatter)を使ってSQLファイルのフォーマットを強制
- alphadagとgraphvizを使ってSQLファイル群の依存関係を可視化

```makefile:Makefile
dag:
	docker run --rm -v `pwd`:/home matts966/alphasql alphadag --output_path ./dag.dot .
	docker run --rm -v `pwd`:/home fgrehm/graphviz dot -Tpng /home/dag.dot -o /home/dag.png
exec:
	python execute.py bqfunc US ./dag.dot
fmt:
	docker run --rm -v `pwd`:/home matts966/zetasql-formatter
	docker run --rm -v `pwd`:/home kiwicom/black:20.8b1 black /home -l 79
	docker run --rm -v `pwd`:/home alphachai/isort -m 3 --tc /home
lint:
	docker run --rm -v `pwd`:/home alpine/flake8 /home
```

# まとめ
BigQueryの祝日判定UDFを公開し、利用しているAlphaSQL継続的デプロイを紹介しました。ぜひ利用&Contributeくださいませ。BigQuery周りの便利ツールがたくさんありますが、もっともっと便利になると良いですね。

明日の[BigQuery Advent Calendar 2020](https://qiita.com/advent-calendar/2020/bigquery)は、@sugiyama34さんです。