BigQuery で重複するレコードを削除

BigQuery で重複するレコードを削除する方法のメモ。
BigQuery で primary key の設定を行わないと、意図せずレコードが重複する場合があります。
そのため、各レコードに uuid を付与し、primary key 相当の id と uuid を使って重複するレコードを削除します。

■テーブル作成・データ登録
drop table if exists dataset.test_dup;

create table dataset.test_dup (
  id  string
);
insert into dataset.test_dup values ('123');
insert into dataset.test_dup values ('456');
insert into dataset.test_dup values ('456');
insert into dataset.test_dup values ('789');
insert into dataset.test_dup values ('789');
insert into dataset.test_dup values ('789');

■カラムを追加
alter table dataset.test_dup add column uuid string;
update dataset.test_dup set uuid = generate_uuid();

alter table dataset.test_dup add column min_uuid string;
update
dataset.test_dup as td
set
min_uuid = mr.min_uuid
from
(select
id
, min(uuid) as min_uuid
from
dataset.test_dup
group by
id
) as mu
where
td.id = mu.id
;

■テーブル
select
*
from
dataset.test_dup
order by
id asc
;

■レコード
id uuid min_uuid
123 133... 133...
456 1a8... 1a8...
456 f11... 1a8...
789 902... 902...
789 d68... 902...
789 cdb... 902...

■重複レコード削除
delete from
  dataset.test_dup
where
  uuid > min_uuid
;

■重複レコード削除後
id uuid min_uuid
123 133... 133...
456 1a8... 1a8...
789 902... 902...