BigQuery Advent Calendar 2020の1日目の記事です。

この記事は、BigQueryで公開した祝日判定UDFの紹介と、依存関係のあるUDF群をMatts966氏のAlphaSQLで思考停止デプロイできた事実の共有です。

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

以前提供していた、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を返します。

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()

内閣府「国民の祝日」のデータを返します。

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でない日数）を返します。

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間の土日でない、かつ国民の祝日・休日でない日数を返します。

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

Contribute募集中です

今回のリポジトリはこちら。na0fu3y/bqfunc

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

継続的デプロイ

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

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

AlphaSQLのalphadagという、SQLファイル群から、依存性チェックを行い、並列実行可能なようにDAGを作成してくれる機能を使うとこのようなDAGが生成できます（一部）。

.github/workflows/deploy.yml

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

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

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

.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

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

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日の参議院本会議で可決・成立するなど移動になる
春分の日、秋分の日のように国立天文台が、毎年2月に翌年の祝日が公表する
前日と翌日の両方を「国民の祝日」に挟まれた平日は休日になるなど

メンテナンスが大変な割りに、様々な組織で必要になる機能です。
今回は、「国民の祝日」についてに大体沿って祝日判定関数を実装しています。

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

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

udfs/holidays_in_japan/holiday_name.sql

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

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

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

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

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

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に書いてあることですが、以下のような便利コマンドを用意しています。

Matts966氏作成のZetaSQL Formatterを使ってSQLファイルのフォーマットを強制
alphadagとgraphvizを使ってSQLファイル群の依存関係を可視化

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は、@sugiyama34さんです。

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

公開データセットの紹介

bqfunc.holidays_in_japan.HOLIDAY_NAME(d)

bqfunc.holidays_in_japan.HOLIDAYS()

bqfunc.holidays_in_japan.NETWORKDAYS(start_date, end_date, weekends, holidays)

bqfunc.holidays_in_japan.NETWORKDAYS_ON_OFF_WEEKEND_DAYS_AND_HOLIDAYS_IN_JAPAN(start_date, end_date)

Contribute募集中です

継続的デプロイ

.github/workflows/deploy.yml

execute.py

各祝日関数の実装

udfs/holidays_in_japan/holiday_name.sql

udfs/holidays_in_japan/is_coming_of_age_day.sql

udfs/holidays_in_japan/is_furikae_kyujitsu.sql

他にもリポジトリでやってること

まとめ