BigQueryにおけるテーブルスキーマの整合性の検証

[takegue]

テーブルが特定のスキーマと同一の型表現を持つことはしばしば重要である。
SQLにはや、JOINやワイルドカード表現含め最終的なテーブルの出力形式は暗黙的に解決する方法も多く
暗黙的な型変換やNULLに関する型推論によりテーブルの出力結果の型が意図せず変えてしまうことも多い。
こういった場合において、テーブルのカラム群の整合性を確認できることは便利である。

例えば、本番用のテーブルと現在開発中のテーブルの整合性が確認できることで
変更の際の懸念をひとつ減らすことができる。

この記事では、テーブル間のスキーマの整合性を確認する手段の方法について検討する

準備

この記事では、次のような形で テーブルが物理化されていることを前提に実行を行う。

-- データセット用意
create schema if not exists `check_table_schema`;

-- 参照テーブル: 整合性を確かめる
create or replace table `check_table_schema.reference_table`
as
select * from `bigquery-public-data.austin_bikeshare.bikeshare_stations`
;

確認したいSQL

select * replace (cast(station_id as STRING) as station_id)
from `check_table_schema.reference_table`

INFORMATION_SCHEMA.COLUMNS による 確認

カラムの整合性を確認するための、最も単純な方法は INFORMATION_SCHEMAを確認し
テーブルのスキーマを確認する方法である。

これは次のように確認することができる。

create or replace table `check_table_schema.target_table`
as
select * replace (cast(station_id as STRING) as station_id)
from `check_table_schema.reference_table`
;

with table1 as (
  select *
  from `check_table_schema.INFORMATION_SCHEMA.COLUMNS`
  where table_name = 'reference_table'
)
, table2 as (
  select
    *
  from `check_table_schema.INFORMATION_SCHEMA.COLUMNS`
  where table_name = 'target_table'
)
select
  if(
    count(1) > 0
    , error(
      string_agg(
        format('%t', (
          column_name
          , lhs.data_type
          , rhs.data_type
          , compare_result
        ))
      )
    )
    , "PASSED"
  ) as msg
from table1 as lhs
full join table2 as rhs using(column_name)
left join unnest([STRUCT(
  case 
    when lhs.data_type = rhs.data_type  then "STRICT_MATCH"
    when lhs.data_type != rhs.data_type then "STRICT_UNMATCH"
    when lhs.data_type is not null then "EXISTS_ONLY_LEFT"
    when rhs.data_type is not null then "EXISTS_ONLY_RIGHT"
  end as compare_result
)])
where compare_result not in ("STRICT_MATCH")

メリット

• カラム名の一致および型の一致を厳密に確認することができる

デメリット

• 対象のSQLが物理化されていることが必要。
• スキャン量やスロット消費が一定かかる

緩いカラムの型一致

最初の手段ではスキャン量がかかる型確認方法だったが、これをゼロコストで達成したい。
つまりスキャンコストやスロット消費が無い状態で確認する状態の達成を目指す。

これは次のようなクエリで確認することができる。

with core as (
  select * replace (cast(station_id as STRING) as station_id)
  from `check_table_schema.reference_table`
)
, _validation as (
	-- ※ カラム名一致や厳密な型一致は取れない
	select * from `check_table_schema.reference_table`
	union all
	select * from core
	limit 0
)
select * from core
--> Column 1 in UNION ALL has incompatible types: INT64, STRING at [9:9]

ただし、この方法では UNION ALLオペレータでは暗黙的な型変換を許容するため、
次のようなケースではエラーを発生させることができず、緩い等価性で評価される。

with core as (
  select * replace (cast(station_id as BIGNUMERIC) as station_id)
  from `check_table_schema.reference_table`
)
, _validation as (
  -- ※ カラム名一致や厳密な型一致は取れない
  select * from `check_table_schema.reference_table`
  union all
  select * from core
  limit 0
)
select * from core

メリット

• カラム数と緩い等価一致の確認ができる
• CTEとして組み込みが可能で、DRYRUNなどでの検証も可能
• ゼロコスト: スキャン量やクエリコストがかからない

デメリット

• カラム名の合致は比較できない
• 緩い等価確認になる

厳密なカラムの型一致

union all などによる set operatorでの確認では、簡易的で利用しやすい分暗黙的変換が行われる。
厳密な型一致で比較できない点で不便である。
これを型レベルで比較するには、次のようにすると良い。

with 
ref1 as (
  select 1 as a, 2 as b
)
, ref2 as (
  select 2 as c, 3 as d
)

, _validation as (
  select
    -- Schme field consitency
    if(
      format('%t', `bqutil.fn.json_extract_keys`(json_query(to_json_string([lhs]), "$.0")))
      = format('%t', `bqutil.fn.json_extract_keys`(json_query(to_json_string([rhs]), "$.0")))
      -- Schema type consitency
      and ([lhs] || [rhs]) is not null
      , null
      , error("schema field is not matched")
    )
    --> Error
  from unnest([0])
  left join (select * from ref1 limit 0) as lhs on true
  left join (select * from ref2 limit 0) as rhs on true
)

select * from _validation

メリット:

• カラム数と厳密なカラムの等価性を確認できる
• CTEとして組み込みが可能で、DRYRUNでの実行時の検証も可能
• ゼロコスト: スキャン量やクエリコストがかからない

デメリット:

• 2種類のエラーが生じるため、エラーメッセージが汚い

テーブルの整合性の厳密一致

SQL単体では、テーブルのカラム型までは確認できるが、カラム名やカラム名の順序までは比較することができない。
BigQuery Scriptを利用し、ここまでの方法を発展させるとクエリの実行コストを抑えた
テーブルの型検証を行うことができる。

-- dee テーブルを構築
create temp table _skelton_dee
as
select 
  (
    select as struct
     (select as struct * from `check_table_schema.reference_table` limit 0).*
  ) as lhs
  , (
    select as struct
    (
	  select as struct * replace (cast(station_id as STRING) as station_id)
	  from `check_table_schema.reference_table` limit 0
	).*
  ) as rhs
;

select
  if(
    array_length(`bqutil.fn.json_extract_keys`(to_json_string(lhs))) = (
      select
        countif(lhs = rhs)
      from 
        unnest(`bqutil.fn.json_extract_keys`(to_json_string(lhs))) as lhs with offset ix
        , unnest(`bqutil.fn.json_extract_keys`(to_json_string(rhs))) as rhs with offset iy
    )
    and [lhs] || [rhs]
    , "PASSED"
    , error('table schema is not matched')
  )  
from _skelton as r

上記、手順は煩雑なためプロシージャなどで用意すると良いかもしれない。

メリット

• 厳密な型一致とカラム名およびその順序一致を確認することができる
• ニアゼロコスト: スキャン量はゼロだが、微量のスロット消費が存在する.
  デメリット
• CTEとして組み込みができないため、実行時間がかかる。

まとめ

この記事では、SQLが生成するテーブルの整合性を確認するための方法を紹介した。
ここで紹介した検証方法を使うことで、スロット消費を限りなく抑えた型確認をSQL単体で、実行することができる。確認項目と用途に応じて選べると良いだろう

[takegue]

json型をうまく利用すると カラム名の合致は比較できることがわかった
以下を用いれば1 statementでの実施が可能なため、3の手法は不要になる

参考SQL:

with 
ref1 as (
  select 1 as a, 2 as b
)
, ref2 as (
  select 2 as c, 3 as d
)

, _validation as (
  select
    -- Schme field consitency
    if(
      format('%t', `bqutil.fn.json_extract_keys`(json_query(to_json_string([lhs]), "$.0")))
      = format('%t', `bqutil.fn.json_extract_keys`(json_query(to_json_string([rhs]), "$.0")))
      -- Schema type consitency
      and ([lhs] || [rhs]) is not null
      , null
      , error("schema field is not matched")
    )
  from unnest([0])
  left join (select * from ref1 limit 0) as lhs on true
  left join (select * from ref2 limit 0) as rhs on true
)

select * from _validation