今回の課題
業務内でETLツールを介して、BigQueryにクエリを実行し、
BigQuery→Google Spread Sheetに「追記」するかたちでデータを転送したところ、
Google Spread Sheetに大量の重複データが格納されている。
という不具合が発生していた。
下記の２つの方法で改善できそうだと分かり、
転送量が少なくて済むため、後者で実装することにした。
改善方法
- 「追記」ではなく、「全量洗い替え更新」するかたちで転送する。
- QUALIFY句によるデータ重複チェックをクエリ内に盛り込んだ上で「追記」でデータを転送する。
QUALIFY句とは
クエリで分析関数を使用した場合に分析関数の結果でフィルタリングを行うことができる。
QUALIFY句を使用する際は、
WHERE句、GROUPBY句、HAVING句のいずれかがクエリ内で使用されていなければならない。
※参考：BigQueryのQUALIFY句を使ってみる
実際に使用したクエリ
下記のようにクエリにQUALIFY句を使用して、
BigQuery→Google Spread Sheetsにデータを「追記」転送することにより、
重複するデータを除外して「追記」転送することに成功した。
select
* -- 省略
from
`テーブル名`
where -- QUALIFY句を使用するには、WHERE, GROUPBY, HAVINGのどれかを入れておかないといけない。
1=1
qualify -- 同じpage_title, release_dateの組み合わせのデータの重複を除外（同じpage_title, release_dateの組み合わせのデータは１つしか存在しないというフィルタリング）
row_number() over(partition by page_title, release_date order by release_date desc) = 1
以上で、今回の課題を解決することができた。
おまけ
QUALIFY句を使用して、実際に重複している行を確認することもできる。
データが多すぎる時や、重複しているような気がしたときに下記クエリを試してみると役に立つかも。
select
* -- 省略
from
`テーブル名`
where -- QUALIFY句を使用するには、WHERE, GROUPBY, HAVINGのどれかを入れておかないといけない。
1=1
qualify -- 同じpage_title, release_dateの組み合わせのデータの重複を除外（同じpage_title, release_dateの組み合わせのデータは１つしか存在しないというフィルタリング）
row_number() over(partition by <ユニークキー相当> order by <時系列系/順序系カラム> desc) > 1