Help us understand the problem. What is going on with this article?

BigQueryデータ基盤のテーブル参照回数を管理する

背景

BigQueryデータ基盤のコストを下げたい場合を想定。
データエンジニアとしては不要なテーブルを棚卸ししたい。

「この毎日更新されているデータマートは本当に必要なのか?」
「この2年前から更新されていない異常に大きなサイズのテーブルは本当に必要なのか?」

などなかなか棚卸しの判断がつかないことが多い。

前の記事(BigQueryデータ基盤のテーブル依存関係を管理する)同様、今回もBigQueryの INFORMATION_SCHEMA.JOBS_BY_PROJECT を使って、過去180日にどのテーブルが何回実行されたを表すテーブルを作成する。

やる方

以下のSQLの実行結果でテーブル作成するだけ。過去180日に実行されたBigQueryJobから、どのテーブルが何回実行されたかのテーブルを生成する。

WITH base_tables as (
SELECT
  job_id,
  creation_time,
  REGEXP_EXTRACT_ALL(query, "(?:FROM|JOIN)[\\s \\n]+`(.+?)`") as tables
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT 
WHERE job_type = "QUERY" 
AND state = "DONE" 
)

SELECT
  SPLIT(t, ".")[OFFSET(ARRAY_LENGTH(SPLIT(t, ".")) - 1)] as table_name,
  COUNT(creation_time) as reffered_count
FROM base_tables, UNNEST(tables) as t
GROUP BY 1
ORDER BY 2 desc

これで嬉しいこと

コスト削減のため削除したいテーブルがある場合、以下のようなSQLを投げると削除して良さそうなテーブルか確認できる。180日参照されていなかったら削除を検討してもいいと思う。

SELECT * FROM reffered_count_table WHERE table_name LIKE "%the_table_name%"

あとは逆に、このテーブルの参照数が少ないテーブルを棚卸し対象として検討できるのも嬉しい。

参照

munaita_
フリーランスのデータエンジニアです。 データ基盤構築や、ビッグデータを扱うシステム構築が得意です。 新卒サイバーエージェントからフリーランス。 広告/小売/教育など幅広いドメインのデータビジネスを経験しています。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした