BigQueryのカラムを途中に追加するストアド

BigQueryのALTER TABLE ADD COLUMN文はテーブルの末尾にカラムを追加できますが、それ以外の場所にはカラムの追加ができません。

なので、テーブル末尾以外の場所にもカラムを追加できるストアドを作成しました。内部でCREATE TABLE AS SELECTを実行しているので、費用が発生する点には注意が必要です。

create procedure `プロジェクトID.データセットID.add_column_to_any_position`(project_id string, dataset_name string, table_name string, column_name string, column_type string, position string)
begin
    declare select_statement string default '';
    declare position_found bool default false;
    declare column_definition string default format("  cast(null as %s) as %s,\n", column_type, column_name);
    
    declare backup_dataset_name string default '作業中のバックアップテーブルを配置するデータセット';
    declare backup_table_name string default table_name || "_backup_" || format_date("%Y%m%d", current_date("Asia/Tokyo"));
    declare create_backup_table_statement string default format("create table %s.%s.%s copy %s.%s.%s;", project_id, backup_dataset_name, backup_table_name, project_id, dataset_name, table_name);
    declare table_name_with_column_added string default table_name || "_with_column_" || format_date("%Y%m%d", current_date("Asia/Tokyo"));
    
    declare create_table_with_column_added_statement string default '';
    
    set select_statement = select_statement || "select\n";
    
    if lower(position) = 'first' then
      set select_statement = select_statement || column_definition;
      set position_found = true;
    end if;
    
    execute immediate format("create temp table columns as select * from `%s`.`%s`.INFORMATION_SCHEMA.COLUMNS where table_name = '%s' order by ordinal_position;", project_id, dataset_name, table_name);
    for column in (
      select * from columns
    ) do
      set select_statement = select_statement || format("  %s,\n", column.column_name);
      if format("after %s", lower(column.column_name)) = lower(position) then
        set select_statement = select_statement || column_definition;
        set position_found = true;
      end if;
    end for;
    
    set select_statement = select_statement || format("from `%s.%s.%s`", project_id, dataset_name, table_name);
    
    if not position_found then
      raise using message = "position to add column not found.";
    end if;
    
    -- カラムが追加されたテーブルを生成するSQL文
    set create_table_with_column_added_statement = format("create table %s.%s.%s as\n%s;", project_id, dataset_name, table_name_with_column_added, select_statement);
    
    -- 最初にバックアップテーブルを作成する。この後の処理でオリジナルテーブルが喪失した場合にここから復旧させる
    execute immediate create_backup_table_statement;
    select create_backup_table_statement;
    
    -- カラムが追加されたテーブルを作成する
    execute immediate create_table_with_column_added_statement;
    
    -- オリジナルテーブルを削除してカラム追加テーブルに置き換える
    execute immediate format("create or replace table %s.%s.%s copy %s.%s.%s", project_id, dataset_name, table_name, project_id, dataset_name, table_name_with_column_added);
    
    -- バックアップテーブルを削除する
    execute immediate format("drop table %s.%s.%s;", projec_id, backup_dataset_name, backup_table_name);
end

このようにして使うと、途中にカラムを追加できます。

call `プロジェクトID.データセットID.add_column_to_any_position`(<project_id>, <dataset_name>, <table_name>, <追加したいカラム名>, <追加したいカラムの型>, <追加する場所>)
-- 追加する場所は以下の2つの書式のどちらかを指定する
--   既存のカラムの後ろに追加する場合: AFTER <既存のカラム名>
--   一番最初に追加する場合: FIRST