INFORMATION_SCHEMA.JOBS_BY_* ビューで BigQuery のテーブルの利用状況を把握する
はじめに
本記事では、INFORMATION_SCHEMA.JOBS_BY_* ビューを使用して、BigQuery のテーブルの利用状況を把握する方法をいくつかご紹介します。
INFORMATION_SCHEMA.JOBS_BY_* ビューの概要
INFORMATION_SCHEMA.JOBS_BY_* ビューは、BigQuery のジョブのメタデータが格納されているビューです。BigQuery でクエリを実行してこのビューを検索することで、直近 180 日間の BigQuery のジョブ実行履歴を参照することができます。以下の 3 つのビューが存在します。
INFORMATION_SCHEMA.JOBS_BY_USER: 現在のユーザーが現在のプロジェクトで実行したジョブの実行履歴を取得可能INFORMATION_SCHEMA.JOBS_BY_PROJECT: 現在のプロジェクトで実行されたジョブの実行履歴を取得可能INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION: 現在の組織で実行されたジョブの実行履歴を取得可能
これらのビューには、「ジョブでデータが書き込まれるテーブルの情報」が保持される destination_table カラムと、「ジョブで参照されるテーブルの情報」が保持される referenced_tables カラムが存在します。これらのカラムを利用することで、各テーブルが「どのように参照されているのか」、「どのくらい参照されているのか」といった、テーブルの利用状況を把握することができるようになります。本記事では、その方法をいくつかご紹介します。
準備
まずは、準備作業として、Cloud Shell で以下のコマンドを実行し、テスト用のデータセットとテーブル、テーブル データを作成し、また、データを検索するクエリをいくつか実行します。
# 今回作成するテスト用のデータセット名を変数 DATASET に設定します。
# ご利用されているプロジェクトで存在しないデータセット名を指定してください。
DATASET=今回作成するテスト用のデータセット名# テスト用のデータセットを作成します。
bq --location=US mk $DATASET# クエリの実行結果をもとに table1 を作成します。
bq --location=US query \
--use_legacy_sql=false \
--destination_table=$DATASET.table1 \
"select 1 as id"# table1 を参照するクエリの実行結果をもとに table2 を作成します。
bq --location=US query \
--use_legacy_sql=false \
--destination_table=$DATASET.table2 \
"select id + 1 as id from $DATASET.table1"# table1 と table2 を参照するクエリの実行結果をもとに table3 を作成します。
bq --location=US query \
--use_legacy_sql=false \
--destination_table=$DATASET.table3 \
"select * from $DATASET.table1 union all
select * from $DATASET.table2"# table3 を参照する create table as select を実行して table4 を作成します。
bq --location=US query \
--use_legacy_sql=false \
"create table $DATASET.table4 as select * from $DATASET.table3"# table5 を作成します。
bq --location=US mk -t $DATASET.table5 id:int64# table4 を参照する insert select を実行して table5 へデータを登録します。
bq --location=US query \
--use_legacy_sql=false \
"insert $DATASET.table5 select * from $DATASET.table4"# table6 を作成します。
bq --location=US mk -t $DATASET.table6 id:int64# table5 を参照する merge を実行して table6 へデータを登録します。
bq --location=US query \
--use_legacy_sql=false \
"
merge $DATASET.table6 as t6
using $DATASET.table5 as t5
on t5.id = t6.id
when not matched then
insert values (t5.id)
"# 各テーブルを検索するクエリをいくつか実行します。
bq --location=US query \
--use_legacy_sql=false \
"select * from $DATASET.table1"bq --location=US query \
--use_legacy_sql=false \
"select t1.* from $DATASET.table1 t1 inner join $DATASET.table3 t3 on t1.id = t3.id"bq --location=US query \
--use_legacy_sql=false \
"select t1.* from $DATASET.table1 t1 inner join $DATASET.table3 t3 on t1.id = t3.id inner join $DATASET.table4 t4 on t1.id = t4.id"bq --location=US query \
--use_legacy_sql=false \
"select t1.* from $DATASET.table1 t1 inner join $DATASET.table3 t3 on t1.id = t3.id inner join $DATASET.table5 t5 on t1.id = t5.id"bq --location=US query \
--use_legacy_sql=false \
"select t3.* from $DATASET.table3 t3 inner join $DATASET.table5 t5 on t3.id = t5.id"
データ作成元のテーブルの把握
destination_table カラムと referenced_tables カラムの両方を利用することで、クエリ ジョブでデータが作成されるテーブルについて、「各テーブルのデータは、どのテーブルのデータをもとに作成されたのか」というテーブル間の関係を把握することができます。
BigQuery Web UI で、以下のクエリを実行します。
select
destination_table
, end_time
, statement_type
, referenced_tables
from
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where
-- creation_time がパーティショニング カラムとなっているため、
-- コスト節約のために、このカラムでの絞り込みを行うことを推奨します。
date(creation_time) = current_date()
and job_type = 'QUERY' -- クエリ ジョブのみを検索対象とします。
and state = 'DONE' -- 実行が完了したジョブのみを検索対象とします。
and error_result is null -- 正常終了したジョブのみを検索対象とします。
-- アンダースコアから始まる、一時テーブルのデータセットを除外します。
and not starts_with(destination_table.dataset_id,'_')
order by
destination_table.dataset_id
, destination_table.table_idクエリ実行結果は以下のようになります。
このクエリ実行結果により、たとえば、「 table3 のデータは、 table1 と table2 のデータをもとに作成された」、「 table5 のデータは、 table4 のデータをもとに作成された」などのことがわかります。
頻繁に参照されているテーブルの把握
referenced_tables カラムを利用することで、頻繁に参照されているテーブルを把握することができます。「どのテーブルが、データ分析業務で頻繁に参照されている重要なテーブルであるのか」ということを把握することができます。
BigQuery Web UI で以下のクエリを実行します。
select
t.project_id
, t.dataset_id
, t.table_id
, count(1) as ref_nums -- テーブルの参照回数を取得します。
from
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j
-- referenced_tables カラムの配列を行へ変換します。
cross join
unnest(j.referenced_tables) t
where
-- creation_time がパーティショニング カラムとなっているため、
-- コスト節約のために、このカラムでの絞り込みを行うことを推奨します。
date(j.creation_time) = current_date()
and j.job_type = 'QUERY' -- クエリ ジョブのみを検索対象とします。
and j.statement_type = 'SELECT' -- select クエリのみを検索対象とします。
and j.state = 'DONE' -- 実行が完了したジョブのみを検索対象とします。
and j.error_result is null -- 正常終了したジョブのみを検索対象とします。
-- 参照テーブル（プロジェクト ID、データセット ID、テーブル ID）ごとに
-- グループ化して集計します。
group by
t.project_id
, t.dataset_id
, t.table_id
order by
ref_nums desc -- テーブルの参照回数が多い順に結果を並べ替えます。
limit 5 -- 並び替え後の結果の、上位 5 件のみを取得します。クエリ実行結果は以下のようになります。
このクエリ実行結果により、 table1 が最も頻繁に参照されているテーブルであり、その次に table3 が頻繁に参照されているテーブルであることがわかりました。
頻繁に参照されているテーブルのペアの把握
referenced_tables カラムを利用することで、同一クエリ内で一緒に頻繁に参照されているテーブルのペアを把握することができます。「どのテーブル同士が同一クエリ内で一緒に頻繁に参照されているのか」、「どのテーブル同士が関連性があるのか」ということを把握することができます。
BigQuery Web UI で以下のクエリを実行します。
select
t1.project_id as t1_project_id
, t1.dataset_id as t1_dataset_id
, t1.table_id as t1_table_id
, t2.project_id as t2_project_id
, t2.dataset_id as t2_dataset_id
, t2.table_id as t2_table_id
, count(1) as ref_nums -- テーブルのペアの参照回数を取得します。
from
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j
-- referenced_tables カラムの配列を行へ変換します。
-- 2 カラム分を取り出し、同一クエリ内で一緒に参照される
-- テーブルのペアを t1, t2 として算出します。
-- ここでは、t1 が、アルファベット順で小さい方の
-- テーブル名になるようにします。
cross join
unnest(j.referenced_tables) t1
inner join
unnest(j.referenced_tables) t2
on
concat(t1.project_id, '.', t1.dataset_id, '.', t1.table_id) <
concat(t2.project_id, '.', t2.dataset_id, '.', t2.table_id)
where
-- creation_time がパーティショニング カラムとなっているため、
-- コスト節約のために、このカラムでの絞り込みを行うことを推奨します。
date(j.creation_time) = current_date()
and j.job_type = 'QUERY' -- クエリ ジョブのみを検索対象とします。
and j.statement_type = 'SELECT' -- select クエリのみを検索対象とします。
and j.state = 'DONE' -- 実行が完了したジョブのみを検索対象とします。
and j.error_result is null -- 正常終了したジョブのみを検索対象とします。
group by -- 参照テーブルのペアごとにグループ化して集計します。
t1_project_id
, t1_dataset_id
, t1_table_id
, t2_project_id
, t2_dataset_id
, t2_table_id
order by
ref_nums desc -- テーブルのペアの参照回数が多い順に結果を並べ替えます。
limit 5 -- 並び替え後の結果の、上位 5 件のみを取得します。クエリ実行結果は以下のようになります。
このクエリ実行結果により、 table1 と table3 のテーブルのペアが最も頻繁に参照されており、その次に table3 と table5 のテーブルのペアが頻繁に参照されている、ということがわかりました。
テスト用データセットの削除
最後に、Cloud Shell で以下のコマンドを実行し、今回作成したテスト用のデータセットを削除します。
bq --location=US rm -r -f $DATASETおわりに
本記事では、INFORMATION_SCHEMA.JOBS_BY_* ビューを使用して、テーブルの利用状況を把握する方法をいくつかご紹介しました。このように、INFORMATION_SCHEMA.JOBS_BY_* ビューを活用することで、BigQuery の利用状況を効率的に調査して把握することができるようになります。ぜひこちらのビューをご活用いただき、BigQuery の利用状況のモニタリングを効率的に実施いただければと思います。
