データサイエンス&サイバーセキュリティ備忘録

Python, Data Science, Kaggle, Cyber Security, CTF, などなど

公式ドキュメントにない方法で、BigQueryにあるデータセット内の全てのテーブル・ビューの情報を取得してみた

はじめに

9月から新しい案件を担当することになり、実務としては初めてGCP(Google Cloud Platform)を使ってます。

ある日、取り扱っているデータの量が膨大で使用していないデータセットがあるかもしれないということで、BigQueryにあるデータセットを整理することに。

そこで、まずは各データセットにある全てのテーブル・ビューの情報を取得することになりました。

参考:公式ドキュメントに記載してある方法

まず最初に、公式ドキュメント*1に書いてある方法を簡単にまとめます。

以下のクエリを実行すると、指定したデータセット内にある全てのテーブルとビューに関する情報を取得することができます。

-- mydataset:データセット名

SELECT
 *
FROM
 mydataset.INFORMATION_SCHEMA.TABLES

この方法で取得できるのは以下の項目です。

  • TABLE_CATALOG:データセットを含むプロジェクトの名前
  • TABLE_SCHEMA:テーブルまたはビューを含むデータセットの名前
  • TABLE_NAME:テーブルまたはビューの名前
  • TABLE_TYPE:テーブルタイプ(BASE TABLE、VIEW、EXTERNALのいずれか)
  • IS_INSERTABLE_INTO:YES または NO(DML INSERT に対応しているか)
  • IS_TYPED:値は常に NO
  • CREATION_TIME:作成日時

欲しい情報が全て取得できなかった

最終更新日時やテーブルの行数、テーブルのデータサイズの情報も欲しいのですが、上記のクエリでは取得できません。

というわけで、何か別の方法があるばず...!と思い、公式ドキュメントで探したのですが見つからず...

最終更新日時はまだしも、行数とデータサイズが取得できないということはなさそうだと思い、色々試しました。

本題

色々やり方を模索していると、ある1つの方法を発見しました。

SELECT
  *
FROM
  mydataset.__TABLES__

このクエリを実行すると、以下の情報を取得できます。

  • project_id:データセットを含むプロジェクトの名前
  • dataset_id:テーブルまたはビューを含むデータセットの名前
  • table_id:テーブルまたはビューの名前
  • creation_time:作成日時
  • last_modified_time:最終更新日時
  • row_count:行数*2
  • size_bytes:データサイズ(byte)*3
  • type:テーブルタイプ(1、2、3、4のいずれか)

ここで、取得できる情報について補足説明をします。

まず、このクエリで取得できる作成日時と最終更新日時ですが、ミリ秒までを表示しているUnix Timestampで格納されています。

そのため、このまま出力しても日時が分からないので、変換する必要があります。

次に、テーブルタイプについてですが、このクエリで実行すると数値で取得します。

以下が、その数値とテーブルタイプの対応表です。*4

  • 1:BASE TABLE
  • 2:VIEW
  • 3:EXTERNAL
  • 4:OTHER*5

以上の補足説明を踏まえて、分かりやすい状態でテーブル・ビューの情報を取得できるよう、自分なりに改良しました。

size_bytesだとデータサイズが大きいときに分かりづらいと思うので、単位がMBとGBで表示されるsize_mbとsize_gbの列も足してあります。

改良したクエリが以下のものです。

-- mydataset:データセット名

SELECT
  * EXCEPT(type, creation_time, last_modified_time),
  ROUND(SAFE_DIVIDE(size_bytes, (1000*1000)),1) as size_mb,
  ROUND(SAFE_DIVIDE(size_bytes, (1000*1000*1000)),2) as size_gb,
  CASE
    WHEN type = 1 THEN 'BASE TABLE'
    WHEN type = 2 THEN 'VIEW'
    WHEN type = 3 THEN 'EXTERNAL'
    WHEN type = 4 THEN 'OTHER'
  END type,
  TIMESTAMP_MILLIS(creation_time) AS creation_time,
  TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time
FROM
  mydataset.__TABLES__
追記

ブログ公開後に、MBは1024*1024、GBは1024*1024*1024ではないのか、というご質問が複数の方からありましたのでご回答します!

上記のクエリでは、国際電気標準会議(IEC)に則り、MBは1000*1000、GBは1000*1000*1000で計算しています。

今回、MBとGBを採用している理由は、MiBとGiBより多くの方に普及しているためです。

※ MiBが1024*1024、GiBが1024*1024*1024

おわりに

テーブルの情報は、クエリを実行しなくても1つずつ確認することはできます。

しかし、データセット内のテーブル・ビューの数が膨大で一覧表みたいな形で取得したい場合に上記のクエリが使えるかと思います。

__TABLES__は公式ドキュメントには記載されていないので、この記事が私みたいにテーブル・ビューの最終更新日時やデータサイズなどを含めた情報の一覧表を出力したい方の手助けができれば幸いです。

*1:https://cloud.google.com/bigquery/docs/information-schema-tables?hl=ja

*2:データタイプがビューの場合、0が入る

*3:データタイプがビューの場合、0が入る

*4:INFORMATION_SCHEMA.TABLESのクエリを使用して調査済み

*5:INFORMATION_SCHEMA.TABLESで取得できない、ルーティン以外のデータが該当