BigQueryテーブルをパーティション分割するストアド

巨大なBigQueryテーブルを適切な列でパーティション分割するとパフォーマンスの向上が望めます。

しかし、ALTER TABLE文でパーティション分割はできないので、既存のテーブルのパーティション分割をするストアドを作成しました。

対応しているパーティションテーブルのタイプは時間単位列パーティションニングで日単位のパーティションのみですが、多くのケースでこれで十分です。
https://cloud.google.com/bigquery/docs/partitioned-tables?hl=ja#date_timestamp_partitioned_tables

create procedure `プロジェクトID.データセットID.partitionize_table`(project_id string, dataset_name string, table_name string, partition_key string)
begin
  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 partitionized_table_name string default table_name || "_partitionized_" || format_date("%Y%m%d", current_date("Asia/Tokyo"));
  declare partition_expression string;
  declare partition_key_data_type string;
  declare create_partitionized_table_statement string;
  
  -- パーティション列の型によってPARTITION BY句を生成する
  -- TODO: 他の型の対応や、1時間・1ケ月などの異なるパーティション分割単位の指定
  execute immediate format("select data_type from %s.%s.INFORMATION_SCHEMA.COLUMNS where table_name = '%s' and column_name = '%s'", project_id, dataset_name, table_name, partition_key) into partition_key_data_type;

  case partition_key_data_type
  when 'TIMESTAMP' then set partition_expression = "partition by date(" || partition_key || ")";
  when 'DATETIME' then set partition_expression = "partition by date(" || partition_key || ")";
  when 'DATE' then set partition_expression = "partition by " || partition_key;
  else RAISE USING MESSAGE = "Unsupported type of partition_key: " || partition_key_data_type;
  end case;

  -- パーティション分割されたテーブルを生成するSQL文
  set create_partitionized_table_statement = format("create table %s.%s.%s %s as select * from %s.%s.%s;", project_id, dataset_name, partitionized_table_name, partition_expression, project_id, dataset_name, table_name);

  -- 最初にバックアップテーブルを作成する。この後の処理でオリジナルテーブルが喪失した場合にここから復旧させる
  execute immediate create_backup_table_statement;

  -- パーティション分割されたテーブルを作成する
  execute immediate create_partitionized_table_statement;

  -- オリジナルテーブルを削除してバックアップテーブルに置き換える
  -- drop tableの完了からalter table renameの完了までの期間はテーブルが存在しなくなり、参照する処理が落ちるので要注意
  execute immediate format("drop table %s.%s.%s;", project_id, dataset_name, table_name);
  execute immediate format("alter table %s.%s.%s rename to %s;", project_id, partitionize_table_name, table_name);

  -- バックアップテーブルを削除する
  execute immediate format("drop table %s.%s.%s;", projec_id, backup_dataset_name, backup_table_name);end;