BigQueryのオプション引数の実装パターン

[takegue]

概要

BigQueryにおいては、UDFやTVFまたはプロシージャにおける引数の省略が構文上できないため
可変長な引数を定義することができないため
挙動を変更する新しい引数を追加するために破壊的変更を生むことになってしまう。

この記事ではBigQueryにおけるOptional 引数の実装方法について載せる

通常のオプション引数の実装

関数においてデフォルト挙動が定義できるオプション引数を設けるとき
struct型を利用して次のように記述すると便利である。

• null を渡すことで、引数の入力を省略できる。
• 明示的な型が定義できるため、入力に対するバリデーションが可能

create or replace function `sandbox.sample_udf`(X int64, _options struct<param1 int64, prefix string >)
as
(
  format('%s %d', ifnull(_options.prefix, 'PREFIX: ' ), X + ifnull(_options.param1, 0)) 
)

ARRAYを使うパターン

• ARRAYにより key, valueのシンプルな仕組みになる
• ユーザによる castによるstringへの型変換をする手間が必要。
  • すべてのvalueをcast(X as string) としていかなければならない
  • pivotを利用する関係でoptionの引数定義を2か所で管理する必要がある(バリデーション用とパース用)
  • structからarrayへの変換のためにはJSのUDFを利用する必要があり、この操作の隠蔽がしづらい
  • 関数内部のパース処理が長くなりやすい

create or replace function `sandbox.sample_udf`(X int64, arr_options array<struct<key string, value string>>)
as
((
  with _options as (
    with options as (
      select * from unnest(arr_options) option
      where if(
        key in ('param1', 'prefix')
        , true
        , error(format("Invalid Option: %t", option))
        )
    )
    select as struct
      -- 任意の型にParseする
      cast(param1 as int64) as param1
      , cast(prefix as string) as prefix
    from options
      pivot (any_value(value) for key in ('param1', 'prefix'))
    limit 1
  )
  
  select format('%s %d', ifnull(_options.prefix, 'PREFIX: ' ), X + ifnull(_options.param1, 0))
  from unnest([0]), _options
))

JSONを使うパターン

• jsonはベータの機能
• 可読性は高い
• パース処理は簡潔に書ける
• バリデーションの関係で内部的にJS UDFへの依存が発生する

create or replace function `sandbox.sample_udf`(X int64, json_options JSON)
as 
((
  with _options as (
  select as struct
    safe.int64(json_options.params1) as param1
    , safe.string(json_options.prefix) as prefix
    -- Assert options
    , (
        select logical_and(if(
          key in ('correct')
          , true
          , error(format("Invalid Option: name=%t in %t'", key, json_options))
        ))
        from unnest(`bqutil.fn.json_extract_keys`(to_json_string(json_options))) key
    ) as _assert
  )
  
  select 
    format('%s %d', ifnull(_options.prefix, 'PREFIX: ' ), X + ifnull(_options.param1, 0))
  from unnest([0]), _options
  where _options._assert
))