BigQuery

【BigQuery】データセットのテーブルを一括削除するSQL

2024.10.28 2024.11.03

BigQueryのデータセットの中に作成した不要なテーブルをBigQueryのコンソール上のSQLから実行して一括削除するクエリとなる。削除対象外とするテーブルも指定することも可能。処理にはテーブル削除が行われるため重要なテーブルを指定しないように注意。以下、削除検証用のデモテーブル作成クエリも記載する。

参考：テーブルを削除する – Google Cloud BigQuery

BigQueryのテーブルを一括削除するクエリ
クエリ解説
検証用の空テーブル作成クエリ

BigQueryのテーブルを一括削除するクエリ

DECLARE exclude_tables ARRAY<STRING>;
DECLARE table_names ARRAY<STRING>;
DECLARE i INT64 DEFAULT 0;
DECLARE drop_statement STRING;

-- 除外したいテーブル名を配列に格納
SET exclude_tables = [
  'test_table_1', 
  'test_table_2',
  'test_table_3'
];

-- テーブル名を配列に格納（除外テーブルが空ならすべてのテーブルが対象）
SET table_names = (
  SELECT ARRAY_AGG(table_name)
  FROM `<project>.<dataset>.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE'
    AND (ARRAY_LENGTH(exclude_tables) = 0 OR table_name NOT IN UNNEST(exclude_tables))
);

-- テーブルの削除をループで実行
SET i = 0;
WHILE i < ARRAY_LENGTH(table_names) DO
  SET drop_statement = FORMAT("DROP TABLE `<project>.<dataset>.%s`", table_names[i]);
  EXECUTE IMMEDIATE drop_statement;
  SET i = i + 1;
END WHILE;

クエリ解説

1．変数宣言

DECLARE exclude_tables ARRAY<STRING>;
DECLARE table_names ARRAY<STRING>;
DECLARE i INT64 DEFAULT 0;
DECLARE drop_statement STRING;

exclude_tables: 除外するテーブル名を格納する配列型の変数です。
table_names: 削除対象のテーブル名を格納する配列型の変数です。
i: ループのカウンタ変数で、削除対象のテーブルを順番に処理するために使います。
drop_statement: 動的にDROP TABLE文を作成して格納するための文字列型変数です。

2．除外するテーブル名の設定

SET exclude_tables = [
  'test_table_1', 
  'test_table_2',
  'test_table_3'
];

**exclude_tables**変数に、削除対象から除外したいテーブル名を配列として設定しています。
ここでは、test_table_1、test_table_2、test_table_3を削除対象から除外するよう指定しています。

3．削除対象のテーブル名を取得して配列に格納

SET table_names = (
  SELECT ARRAY_AGG(table_name)
  FROM `listing-strategy.test_delete_table.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'BASE TABLE'
    AND (ARRAY_LENGTH(exclude_tables) = 0 OR table_name NOT IN UNNEST(exclude_tables))
);

ARRAY_AGG(table_name): INFORMATION_SCHEMA.TABLESから取得したtable_nameを配列としてtable_namesに格納します。
table_type = ‘BASE TABLE’: データセット内の通常テーブル（ビューやその他のオブジェクトは除外）だけを対象としています。
AND (ARRAY_LENGTH(exclude_tables) = 0 OR table_name NOT IN UNNEST(exclude_tables)):
・ARRAY_LENGTH(exclude_tables) = 0: exclude_tablesが空の場合、すべてのテーブルが対象になります。
・table_name NOT IN UNNEST(exclude_tables): exclude_tablesに含まれないテーブルだけが削除対象として選ばれます。この処理により、table_namesには、削除対象のテーブル名が配列として格納されます。

4．削除対象のテーブルを順に削除するループ処理

SET i = 0;
WHILE i < ARRAY_LENGTH(table_names) DO
  SET drop_statement = FORMAT("DROP TABLE `listing-strategy.test_delete_table.%s`", table_names[i]);
  EXECUTE IMMEDIATE drop_statement;
  SET i = i + 1;
END WHILE;

SET i = 0;: ループのカウンタを初期化します。
WHILE i < ARRAY_LENGTH(table_names): table_names配列内のすべてのテーブルが処理されるまでループを実行します。
drop_statement = FORMAT(…):
・FORMAT関数を使い、DROP TABLE文をdrop_statement変数に格納します。
・table_names[i]でi番目のテーブル名を取得し、DROP TABLE文に挿入します。
EXECUTE IMMEDIATE drop_statement: DROP TABLE文を実行し、テーブルを削除します
SET i = i + 1;: インデックスiを1増やして、次のテーブルを処理します。

検証用の空テーブル作成クエリ

CREATE OR REPLACE TABLE `<project>.<dataset>.test_table_1` (
  id INT64,
  name STRING,
  created_at TIMESTAMP
);

-- Test Table 2
CREATE OR REPLACE TABLE `<project>.<dataset>.test_table_2` (
  user_id INT64,
  action STRING,
  action_date DATE
);

-- Test Table 3
CREATE OR REPLACE TABLE `<project>.<dataset>.test_table_3` (
  product_id INT64,
  product_name STRING,
  price FLOAT64,
  last_updated TIMESTAMP
);