2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

ZOZOAdvent Calendar 2023

Day 15

BigQueryのテーブルっぽいものを種類を問わずにDROPできるストアド

Last updated at Posted at 2023-12-14

BigQueryでテーブルを削除するためには DROP TABLE 文を使います。
一方で、VIEWなどのテーブルっぽい振る舞いをするもののテーブルではないものは DROP TABLE では削除できません。

スクリーンショット_2023-12-13_8_59_07.png

VIEWを削除するためには、 DROP VIEW という別のDDL文を実行する必要があります。

削除する対象によってDDLを変更するのは面倒なので、テーブルっぽいものであれば同じ記法で消せるストアドを作成してみました。

create procedure `プロジェクトID.データセットID.drop_any`(project_id string, dataset_name string, table_name string)
begin
  declare table_type_query string default format("select table_type from %s.%s.INFORMATION_SCHEMA.TABLES where table_name = '%s';", project_id, dataset_name, table_name);
  declare table_type string default '';
  declare drop_type string default '';
  declare drop_statement string default '';

  execute immediate table_type_query into table_type;

  if table_type is null then
    raise using message = "tablish object not found.";
  end if;

  case table_type
  when "BASE TABLE" then
    set drop_type = "table";
  when "SNAPSHOT" then
    set drop_type = "snapshot table";
  when "VIEW" then
    set drop_type = "view";
  when "MATERIALIZED VIEW" then
    set drop_type = "materialized view";
  when "EXTERNAL" then
    set drop_type = "external table";
  when "CLONE" then
    raise using message = "table clone is not supported yet.";
  else
    raise using message = format("unknown table type: %s.", table_type);
  end case;

  set drop_statement = format("drop %s `%s.%s.%s`;", drop_type, project_id, dataset_name, table_name);
  execute immediate drop_statement;
end;
2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?