BigQuery でレコードの有効期限を設定

BigQuery でレコードの有効期限を設定する方法のメモ。
■テーブル定義・データ登録

create table dataset.test_partition1 (
  jst_date date
  , log_text string
)
partition by jst_date
options (
  partition_expiration_days = 3
  , require_partition_filter = true
);

insert into dataset.test_partition1 (jst_date, log_text) values ('2023-11-20', 'log_20231120');
insert into dataset.test_partition1 (jst_date, log_text) values ('2023-11-21', 'log_20231121');
insert into dataset.test_partition1 (jst_date, log_text) values ('2023-11-22', 'log_20231122');
insert into dataset.test_partition1 (jst_date, log_text) values ('2023-11-23', 'log_20231123');
insert into dataset.test_partition1 (jst_date, log_text) values ('2023-11-24', 'log_20231124');

■データ確認

select current_date();

[{
  "f0_": "2023-11-24"
}]

select
  *
from
  dataset.test_partition1
where
  jst_date >= '2023-11-01'
order by
  jst_date asc
;

[{"jst_date": "2023-11-21", "log_text": "log_20231121"},
 {"jst_date": "2023-11-22", "log_text": "log_20231122"},
 {"jst_date": "2023-11-23", "log_text": "log_20231123"},
 {"jst_date": "2023-11-24", "log_text": "log_20231124"}]

レコードの有効期限を変更

alter table dataset.test_partition1 set options (
  partition_expiration_days = 2
  , require_partition_filter = true)
;

select
  *
from
  dataset.test_partition1
where
  jst_date >= '2023-11-01'
order by
  jst_date asc
;

[{"jst_date": "2023-11-22", "log_text": "log_20231122"},
 {"jst_date": "2023-11-23", "log_text": "log_20231123"},
 {"jst_date": "2023-11-24", "log_text": "log_20231124"}]