🔍
Bigqueryのメタデータをプロジェクト単位で一括表示
はじめに
データセット名.__TABLES__をクエリすることで、テーブル毎の件数やサイズにおけるメタデータ取得できます。ただ、特定のデータセットを指定するので、プロジェクト全体のデータを一括で見ることは出来ない。
SELECT * FROM `[dataset_id]`.__TABLES__
取得できる項目
| フィールド名 | 内容 |
|---|---|
| project_id | プロジェクト名 |
| dataset_id | データセット名 |
| table_id | テーブル名 |
| creation_time | テーブルが作成された時刻（UNIX TIME） |
| last_modified_time | テーブルに変更があった最新の時刻（UNIX TIME） |
| row_count | テーブルの行数 |
| size_bytes | テーブルのトータルサイズ |
| type | テーブルタイプ（テーブル：1、ビュー：２） |
やりたいこと
Bigqueryのメタデータをプロジェクト単位で一括表示
-- テーブルリストを格納する配列を宣言
DECLARE table_list ARRAY<STRING>;
-- テーブルリストを取得
SET table_list = (
SELECT ARRAY_AGG(CONCAT(catalog_name,'.',schema_name,'.__TABLES__'))
FROM [PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATA
);
-- テーブルリストからテーブルを選択してクエリを実行
EXECUTE IMMEDIATE (
SELECT STRING_AGG(
'SELECT * FROM `' || table || '`', ' UNION ALL ')
FROM UNNEST(table_list) AS table
);
もう少し表示する項目を増やす。
時刻をフォーマット修正、サイズをGBに直す、作成経過日数、最終更新経過日数、ストレージコストなど
※東京（asia-northeast1）アクティブ ストレージ $0.023 per GB を使用
まず、下記のクエリを修正する。
SELECT * FROM `[dataset_id]`.__TABLES__
↓
SELECT
project_id
,dataset_id
,table_id
,FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MILLIS(creation_time), 'Asia/Tokyo') as creation_time
,TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), TIMESTAMP_MILLIS(creation_time), DAY) as days_since_creation
,FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MILLIS(last_modified_time), 'Asia/Tokyo') as last_modified_time
,TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), TIMESTAMP_MILLIS(last_modified_time), DAY) as days_since_last_modification
,row_count
,size_bytes
,size_bytes / 1024 / 1024 / 1024 as size_gb
,size_bytes / (1024 * 1024 * 1024) * 0.023 * 149.78 AS storage_cost
/*東京（asia-northeast1）アクティブ ストレージ $0.023 per GB */
,CASE type WHEN 1 THEN 'table' WHEN 2 THEN 'view' ELSE '' END AS table_type
FROM
`[dataset_id]`.__TABLES__
ORDER BY
size_bytes
Bigqueryのメタデータをプロジェクト単位で一括表示のクエリを修正
-- テーブルリストを格納する配列を宣言
DECLARE table_list ARRAY<STRING>;
-- テーブルリストを取得
SET table_list = (
SELECT ARRAY_AGG(CONCAT(catalog_name,'.',schema_name,'.__TABLES__'))
FROM [PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SCHEMATA
);
-- 東京（asia-northeast1）アクティブ ストレージ $0.023 per GB
-- テーブルリストからテーブルを選択してクエリを実行
EXECUTE IMMEDIATE (
SELECT STRING_AGG('SELECT project_id,dataset_id,table_id,FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP_MILLIS(creation_time), "Asia/Tokyo") as creation_time,TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), TIMESTAMP_MILLIS(creation_time), DAY) as days_since_creation,FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP_MILLIS(last_modified_time), "Asia/Tokyo") as last_modified_time,TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), TIMESTAMP_MILLIS(last_modified_time), DAY) as days_since_last_modification,row_count,size_bytes,size_bytes / 1024 / 1024 / 1024 as size_gb,size_bytes / (1024 * 1024 * 1024) * 0.023 * 149.78 AS storage_cost,CASE type WHEN 1 THEN "table" WHEN 2 THEN "view" ELSE "" END AS table_type FROM `' || table || '`', ' UNION ALL ' )
FROM UNNEST(table_list) AS table
);
Discussion