BigQuery内のDatasetから最新の日付のPrefix/Suffixのテーブル名を取得するには？

概要

Dataset内の履歴テーブルから最新のPreffixもしくはSuffixのテーブル名(例えば、history_20250202)を取得したいニーズがあるかと思います
INFORMATION_SCHEMAを使えば実現できるのでその方法について解説します

INFORMATION_SCHEMAとは？

BigQueryに関するメタデータ情報を提供するシステム定義の読み取り専用のViewです
BigQueryに限らず、MySQLやPostgresにも存在します
INFORMATION_SCHEMA.TABLESのViewからデータセット内の各テーブルまたはViewが表示されます
今回はINFORMATION_SCHEMA.TABLESを使ってテーブルの一覧を取得し、MAX関数とREGEXP_CONTAINS関数を使って該当するREGEXと日付の形式(YYYYMMDD)のテーブルから最新の日付のテーブル名を取得するクエリを作成します

最新の日付のPrefix/Suffixのテーブル名を取得できるクエリ

最新の日付のPrefixのテーブル名は以下のように取得できます

SELECT MAX(table_name) FROM project.dataset.INFORMATION_SCHEMA.TABLES
WHERE REGEXP_CONTAINS(table_name,"[0-9]{8}_history")

また、最新の日付のSuffixのテーブル名は以下のように取得できます

SELECT MAX(table_name) FROM project.dataset.INFORMATION_SCHEMA.TABLES
WHERE REGEXP_CONTAINS(table_name,"history_[0-9]{8}")

実際に実行してみよう！

今回はSuffixに日付をつけた履歴テーブルを作成してみました

クエリを実行し、最新の日付のテーブル名を取得できれば成功です

また、MIN関数を使って一番古い日付のテーブル名も取得できます

参考