サービスが成長するに連れてBigQueryのテーブルの数はどんどん増えていき，全てのテーブルについて把握することは難しくなります． 実際に業務を行っていて遭遇するのは普段分析で使わないidが出てきて，それに対するテーブルがどれなのか分からないという状況． 自分で探しても数百・数千テーブル存在するので見つからず，gitやドキュメントを検しても見つからなかったら知見のある人に聞く必要がありました．
idのカラム名で検索できれば見つかるかもしれませんが，BigQueryのGUIではカラム名での検索はできません． 画像のようにデータセットとテーブル名の検索はできますが，この場合はテーブル名に見当がついていないと検索することが難しいです．
そのためカラム名で検索可能なようにしてみました．
以下のクエリでLIKEで絞るようにカラム名を指定すればレコードとして取得可能です． テーブル数が数万とかの規模であるとスキャン量が多くなるので注意が必要かもしれません．．
/******************************
Parameter: pattern
ex:
- '%suffix_search_column'
- 'prefix_search_column%'
- 'equal_search_column'
******************************/
DECLARE column_pattern DEFAULT 'this is pattern parameter';
DECLARE q DEFAULT (
SELECT
STRING_AGG(q, ' UNION ALL ')
FROM (
SELECT
CONCAT('SELECT * FROM ', schema_name, '.INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE @pattern') AS q
FROM
INFORMATION_SCHEMA.SCHEMATA
)
);
EXECUTE IMMEDIATE q USING column_pattern AS pattern
BigQueryではデータセットやテーブルの情報がメタデータとして取得することができます． しかしテーブルのカラムを取得することはできるのですが，複数のテーブルについてカラムを取得できるようなデータ構造にはなっていませんでした．
そこで一度テーブル一覧を取得し，そのテーブルについてカラム名を取得するようなクエリをSTRINGで定義してEXECUTE IMMEDIATEで流すことにしました．
上記のクエリのように1テーブルごとにWHEREで指定するのと，UNION ALLした後にWHEREで指定するのはパフォーマンス的にはほとんど変わりませんでした． 前者の方が若干Slot time consumedが良かった．
これで結構楽になりました．