BigQueryでテーブル結合(Join)した上でDELETE文を実行したい時の対応方法

はじめに

皆様、こんにちは！
佐久間まゆちゃんのプロデューサーの@hiroki_tanakaです。

私は現在、携わっているRailsアプリケーションの一部でBigQuery(以下：BQ)を用いており、データ管理を行っています。
先日、BQのDELETE文実行時に別テーブルと結合した上で抽出したデータを削除したいというケースが出てきました。
その際に調べたことをまとめました。

BQでのDELETE文の仕様

BQの公式ドキュメントにあるようにDELETE文内で結合することはできません。
公式ドキュメント：
https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax?hl=ja#delete_statement

つまり、MySQLのような下記のSQLは発行できません。

DELETE table_a
FROM 
  table_A AS table_a
INNER JOIN 
  table_B AS table_b
ON
  table_a.id = table_b.table_a_id
WHERE 1=1
AND table_b.type = 'XXXXX';

しかし、今回は別テーブルのカラムを条件として削除したいため、結合する必要がありました。

BQのDELETE文で結合したい時の解決方法①：EXISTS句を使用する

解決策の1つ目がEXISTS句を使用することです。
(NOT EXISTS句の場合も同様です。)
先程のSQLと同じことをBQで行おうとすると下記のようになります。

DELETE table_a
FROM table_A AS table_a
WHERE 1=1
AND EXISTS
(
SELECT
 1
FROM
 table_B AS table_b
WHERE 1=1
AND table_a.id = table_b.table_a_id
AND table_b.type = 'XXXXX'
)

もし冗長に書こうと思うとこう書けます。
(冗長に書く必要はないですが、わかりやすく書くとこんなことしていますという意図です。)

DELETE table_a
FROM table_A AS table_a
WHERE 1=1
AND EXISTS
(
SELECT
 1
FROM
 table_A AS table_a_2
INNER JOIN
 table_B AS table_b
ON
AND table_a_2.id = table_b.table_a_id
AND table_b.type = 'XXXXX'
WHERE 1=1
AND table_a_2.id = table_a.id
)

BQのDELETE文で結合したい時の解決方法②：WHERE句でサブクエリを使用する

解決策の2つ目がWHERE句内でサブクエリを使用することです。
最初のSQLは下記のように書くことができます。

DELETE table_a
FROM table_A AS table_a
WHERE 1=1
AND table_a.id = 
(
SELECT
 table_b.table_a_id
FROM
 table_B AS table_b
WHERE 1=1
AND table_b.type = 'XXXXX'
)

ただ、こちらの方法だとtable_Bをtypeで絞った結果を求めてから、その集合とtable_Aと結合という形になります。
そのため、もしtable_B全体が15万件ありtypeで絞っただけの結果が10万件・結合によって1000件程度になるといった、検索条件での絞り込みで件数がそこまで絞られない場合は性能的にEXISTS句を使用した方が速いです。
EXISTS句の場合は結合→絞り込みの順番になるので、結合によってtable_Bのデータが大幅に絞られた後に、typeという検索条件で絞られるためです。

おわりに

BQのStandardSQLは通常のRDBと同様の構文でほぼ同じ事ができますが、随所で異なる部分があります。
そのため、各BDの特徴をしっかり捉えて、システム構築することを心掛けて行きたいです。