BigQueryのメタ情報を見てみた＆使ってみた

はじめに

BigQueryとても便利ですよね。
ひとまずテーブルを入れておくのも、入れたテーブルを高速で集計するのも、更にBigQueryMLとかを使って機械学習も、BigQueryだけでできてしまいます。

と便利に使っていて、気がついたら色々なDatasetやTableができてしまっているもの。
そのメタ情報がINFORMATION_SCHEMAを使うと見られるらしいです。

今回は、そのINFORMATION_SCHEMAの使い方と、それ以外のメタ情報も合わせて整理していきます。

メタ情報の見方

特定プロジェクトのデータセット一覧の取得

まずは、プロジェクトを指定して中のデータセット一覧を取得する方法です。

SELECT * FROM `myproject.INFORMATION_SCHEMA.SCHEMATA`

次の項目が出力されます。

• catalog_name :プロジェクト名
• schema_name :データセット名
• schema_owner :オーナー名？（全てNullだった）
• creation_time :作成日時
• last_modified_time :更新日時
• location :保存ロケーション

オーナー名がNullなのが残念でした。
これで誰が作成したデータセットなのかが分かれば、とても管理が簡単になるのですが。。。
なんとか、入れる方法はないだろうか。

特定プロジェクト・データセットのテーブル一覧の取得１

続いて、プロジェクト：データセットを指定して中のテーブル一覧を取得する方法です。

SELECT * FROM `myproject.mydataset.INFORMATION_SCHEMA.TABLES`

次の項目が出力されます。

• table_catalog ：プロジェクト名
• table_schema ：データセット名
• table_name ：テーブル名
• table_type ：テーブル or VIEW or 外部参照テーブル
• is_insertable_into ：INSERTできるか
• is_typed : 不明（No以外ない？）
• creation_time : 作成日時

is_insertable_intoの意味合いがちょっと分かってません。
テーブルはYes, VIEWはNo以外の要素はなさそうですが。それだと、table_typeで十分だし。。。

特定プロジェクト・データセットのテーブル一覧の取得２

同じく、プロジェクト：データセットを指定して中のテーブル一覧を取得する方法です。
少し出力される項目が違います

SELECT * FROM `myproject.mydataset.__TABLES__`

• project_id：プロジェクト名
• dataset_id：データセット名
• table_id：テーブル名
• creation_time：作成日時
• last_modified_time：更新日時
• row_count：行数
• size_bytes：データサイズ
• type：１ならTable, ２ならVIEW

見るメタ情報によって、微妙に名称が異なるのが気持ち悪いですね。
同じプロジェクト名を指すのに、catalog_nameだったりtable_catalogだっったり、project_idだったり。
何かBigQuery内で使い分けとかされているのでしょうか。

特定プロジェクト・データセットのテーブル一覧の取得３

INFORMATION_SCHEMAでテーブルの付加情報も見られます。

SELECT * FROM `myproject.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS`

次の項目が出力されます。

• table_catalog ：プロジェクト名
• table_schema ：データセット名
• table_name ：テーブル名
• table_type ：テーブル or VIEW or 外部参照テーブル
• option_name
• option_type
• option_value

オプションは一瞬何を言っているのか分かりづらいのですが、option_nameに「expiration_timestamp」、option_typeに「TIMESTAMP」、option_valueに日時が入って、この3つでセットになっています。

他にも、descriptionやlabelsとかがありました。

特定プロジェクト・データセットのテーブル一覧＋列一覧の取得

テーブルが多くなると、大量にアウトプットされます。

SELECT * FROM `myproject.mydataset.INFORMATION_SCHEMA.COLUMNS`

• table_catalog ：プロジェクト名
• table_schema ：データセット名
• table_name ：テーブル名
• column_name :列名
• ordinal_position : 列番号
• is_nullable :NullがOKか
• data_type :データ型
• is_generated
• generation_expression
• is_stored
• is_hidden
• is_updatable
• is_system_defined
• is_partitioning_column : テーブル分割に用いているか
• clustering_ordinal_position：テーブルクラスタリングに用いているか

解説を書いていないのは、NEVERとかNullしか入っていない列です。
列の意味が分かりませんでした。

メタ情報を組み合わせて使ってみる

メタ情報の組み合わせで、あるプロジェクト内のデータセット別の課金額を計算したいと思います。

import pandas as pd

query="SELECT schema_name FROM `myproject.INFORMATION_SCHEMA.SCHEMATA`"
df = pd.read_gbq(query, project_id='myproject', dialect="standard")

df_output = pd.DataFrame()

query="""
SELECT 
 "{dataset}" AS dataset, 
 SUM(size_bytes) / 1000000000 AS DataSize, 
 0.020 * SUM(size_bytes) / 1000000000 AS Cost 
FROM `myproject.{dataset}.__TABLES__`
GROUP BY dataset"""

for i, dataset in df.iterrows():
  d = pd.read_gbq(query.format(dataset=dataset[0]), project_id='myproject',dialect="standard")

  df_output=df_output.append(d)

BQの保管費用は、「BigQueryの料金」から、1GBあたり$0.02で出しています。
長期保存だと、$0.01になるので、実際の請求額は出てきた結果よりも、もう少し安いかもしれません。

おわりに

BigQueryのメタ情報についてまとめてみました。
色々なメタ情報があるので、他のものもありそうですが、ひとまずよく使うものを出してます。
ただ、結構謎な項目があるので、もう少し勉強が必要ですね。