見出し画像

BigQuery のレガシーな日付別テーブルに標準SQLを投げられるようにする

jaruuu(Ryosuke Hamaguchi)

この記事は、 BigQuery Advent Calendar 2022 の 25日目です。

こんにちは。ユビレジで遊撃サーバーチームに所属している濱口です。普段は開発基盤や分析基盤の整備をメインでやっています。
今回は、先日 BigQuery のレガシーな日付別テーブルについて試行錯誤する機会があったのでまとめようと思います。

日付別テーブルとは

日付別にシャーディングされたテーブルで テーブル名_yyyymmdd のような名前が付けられているテーブルです。現在では分割テーブルの使用が推奨されているので、見かける機会は減ってきているかもしれません。 日付別テーブルは、ワイルドカードテーブルを利用することで特定の期間のテーブルをまとめて取得することができます。

select *
from `<project-id>.<dataset-id>.<table-prefix>*`

また _TABLE_SUFFIX という擬似列を用いて以下のように取得することもできます。

select *
from `<project-id>.<dataset-id>.<table-prefix>*`
where _table_suffix = '20221225'

とある日

日付別テーブルに対して以下のようなクエリを投げていました。

select json.text
from project.dataset.table_*
where _table_suffix between '20210401' and '20221130'

すると、こんな変哲もないクエリから

Cannot resolve field 'provided' inside leaf column 'json' of type STRING

のようなエラー文が返ってきました。おやおや？と思い、一度 INFORMATION_SCHEMA を確認してみました。投げたクエリは以下です。

select
  *
from
  project.dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
where
  (table_name like 'table_2021%' or table_name like 'table_2022%')
  and column_name like '%json%'
order by table_name

その結果、どうやら2022年8月1日前後で json カラムの型が String から Struct 型に変わっているようでした。なので 

# 8月1日 より前
select json
from project.dataset.table_*
where _table_suffix between '20210401' and '20220731'

# 8月1日 以降
select json.text
from project.dataset.table_*
where _table_suffix between '20220801' and '20221130'

のように分けて取得すると良さそうでした。しかし 8月1日 より前のクエリを投げると

Cannot resolve field 'provided' inside leaf column 'json' of type STRING

またもや先程と同じエラーが発生してしまいました。

なぜカラムの型が変わったのか

このタイミングで、そもそもなぜカラムの型が変わったのかを調べてみました。取得しようとしていたデータは Datastore から転送されているもので 8月1日 のタイミングで Firestore の Datastore モードに切り替えられたタイミングでした。この切り替えが原因で型が変わっていそうでした。

datastore から bq 転送のパイプライン

そしてもう少し突っ込んで調べてみると、実は Datastore から送られていた 8月1日 以前のデータはレガシー構造のテーブルになっていました(上記のエラー文では全然分からん…)。これは標準SQLが使えず、レガシーSQLでクエリを投げる必要があることを意味していました。そういうわけで _TABLE_SUFFIX を利用してクエリを投げるとエラーになってしまうようでした。

なんとか標準 SQL でクエリを投げたい

レガシーSQLでクエリを投げるとなると、日付テーブルや json の扱いがかなり不便になります。標準SQLでクエリを投げられるようにするために、とりあえず新規のパーティション分割テーブルに突っ込むことにしました。
_TABLE_SUFFIX が使えないのでどうしようか、Python でゴリゴリやるか、など考えていました。少し実験してみたところ、レガシー構造のテーブルに対して、以下のようなクエリは標準SQLとして投げられるようでした。

select json
from project.dataset.table_2022*

少し無理やりですが、以下のようなクエリを使えば取りたい期間のデータを取得できそうです。

select *
from project.dataset.table_2022*
union all
select *
from project.dataset.table_202112*
union all
select *
from project.dataset.table_202111*
...
...
union all
select *
from project.dataset.table_202104*

上記で取得したデータを Datetime 型のカラムをパーティションとした分割テーブルに保存します。

標準SQLを利用できるようになった

こうして無事に標準SQLを使ってクエリを投げられるようになりました。
とはいえ、現状2022年8月前後でテーブルが分かれているので、2022年8月を跨ぐデータを抽出する場合は union all する必要があリます。これはひとまず日付を指定すればいい感じに union all した結果を返すテーブル関数を追加しました。

create or replace table function `project.dataset.table_function_name`(from_date, to_date)
AS (
  select *
  from `project.dataset.table_*`
  where date(concat(substr(_table_suffix, 1, 4), '-', substr(_table_suffix, 5, 2), '-', substr(_table_suffix, 7, 2))) between '2022-08-01' and to_date
  union all
  select *
  from `project.dataset.new_table`
  where date between from_date and '2022-07-31'
)

このテーブル関数を利用することで、集計者はテーブルが分かれていることを意識せずにデータを取得できます。
ただ今回のテーブルは頻繁に利用するので、2022年8月以降のテーブルに関してもパーティション分割テーブルに貯めて利用する方がコスパが良いかもしれませんがそれはまた別の機会に。

まとめ

  • 古い Datastore から転送されている日付別テーブルはレガシー構造の可能性がある

  • レガシーSQLだと日付別テーブルや json の扱い等がかなり不便

  • 新規のパーティション分割テーブルに入れ直して標準SQLを使えるようにすると良さそう

正体不明のエラーから発覚したレガシー構造の日付別テーブルをパーティション分割テーブルに移行する話でした。少しでも同じような現象で苦しんでいる人の助けになればと思います。

この記事が気に入ったらサポートをしてみませんか？