BigQueryテーブルでデータが重複している場合の対応

BigQueryにはデータが一意であることを制約する概念がない（ユニーク制約がない）ので、例えばcloudSQLからBQにデータ転送をした場合に、cloudSQL側でユニーク制約がある場合はBQ側でユニークになっているかどうかは別途確認を挟むのがよくある話かと思います。

運用上何らかの原因でユニークでなくなり、つまりデータが重複してしまった場合の対応について記載します。

1. 重複のチェック

今回はbigquery-public-dataを利用しており、実際の重複は無いですが自身のテーブルに置き換えて確認します

重複があるunique_keyを出すクエリ

SELECT
unique_key,
COUNT(1)
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
GROUP BY 1
HAVING COUNT(1) > 1

重複しているレコード数とトータルのレコード数の差分を出すクエリ

SELECT
"distinct" AS type,
COUNT(DISTINCT unique_key)
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`

UNION ALL

SELECT
"total" AS type,
COUNT(unique_key)
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`

2. 重複を排除したテーブルを一時的なデータセットに作成

• 一時的でなく本来配置したいデータセット配下にしてもよいです
• 一時的なデータセットをここでは tmp としています

CREATE OR REPLACE TABLE tmp.taxi_trips
AS
SELECT
DISTINCT *
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`

3. 作成したテーブルに重複が無いことを確認する

• 手順1のSQLを、データセット名をtmpに変えて実行して確認する
• 一時的なデータセットに作成されたテーブルを、本来配置したデータセット内にコピーする