LoginSignup
36
12

More than 1 year has passed since last update.

BigQueryAdvent Calendar 2020Day 1
@na0

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

Last updated at Posted at 2020-11-30

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

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

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を返します。

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が生成できます（一部）。
dag.png

.github/workflows/deploy.yml

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

  1. AlphaSQL、Python依存モジュールをインストール
  2. alphadagコマンドでSQLファイル群を解釈させて、dag.dotファイルを作成
  3. 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)

各祝日関数の実装

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

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

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

依存関係デプロイ体制が整っているので、関数間の依存は気にせずひたすらに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に書いてあることですが、以下のような便利コマンドを用意しています。

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さんです。

36
12
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
36
12