🔍

BigQueryでカラム検索

2020/12/12に公開

経緯

サービスが成長するに連れてBigQueryのテーブルの数はどんどん増え、全てのテーブルについて把握することは難しくなります。
実際に業務を行っていて遭遇するのは普段分析で使わないidが出てきて、それに対するテーブルがどれなのか分からないという状況。
自分で探しても数百・数千テーブル存在するので見つからず、gitやドキュメントを検しても見つからなかったら知見のある人に聞く必要がありました。

idのカラム名で検索できれば見つかるかもしれませんが、BigQueryのGUIではカラム名での検索はできません。
画像のようにデータセットとテーブル名の検索はできますが、この場合はテーブル名に見当がついていないと検索することが難しいです。

GUIでの検索画像

そのためカラム名で検索可能なようにしてみました。

SQL

以下のクエリで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が良かった。

終

これで結構楽になりました．

経緯

SQL

解説

終

Discussion