日報 #83 - Redshiftでプライマリーキーとか

いやはや本格的に寒くなってきましたね。
乾燥も酷いですし、THE・冬的な世界観に秋葉原は包まれております。
雰囲気は嫌いじゃないんですけどね、、、如何せん僕にとって「寒い」は「痛い」に似ている感覚なので、 ちょっとストレスなわけです。
「暑い」は「重い」に似た感覚で、ソッチのほうが耐えられます。
そんなん知りませんよね。

さて本日はRedshift周りでちょいとギョッとしたところをいくつか。
COPYコマンド、とても便利で重宝しますが、
実はPrimary KeyやUniqueの制約を無視してきます。
ホントかよ、って思いますが、詳しくはここにあります。 http://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html

凄い簡単にいえば、プライマリーキー設定したのにレコードとしては重複したりします。
なので、RDSとの連携や同期の時、うまーく重複を取り除く作業をしなくてはなりません。 RDS上でDELETEされてしまった場合はしょうがないとしても、
UPDATEされたものに関しての追跡を考えると必ず必要な機能となってくるわけです。

これを達成するためにはCOPYコマンドで差分ロードした後、
重複分の古い方を削除せないけません。
なので、ロードするレコードには本来RDS上でユニークだったキーの他に、UPDATEされた順序がわかるようなカラムが必要になってきます。
分かりづらいので、例を上げながら。
例えば、user情報を格納しているカラムがid,name,post_date,put_dateの４つだった時、
Redshift上にもrds_userテーブルを作って、id,name,post_date,put_dateの４つを作成します。
一応Redshift上のrds_userテーブルのidにPKを付けて置きますが、これはあまり意味がありません。

ある日、id=5の人が登録しましたとして、そのデータがRedshiftに同期されたとします。
id=5, name=tom, post_date=2014-01-01, put_date=2014-01-01
ところが翌日tomさんは名前をtommに変更しました。
id=5, name=tomm, post_date=2014-01-01, put_date=2014-01-02
これを感知して変更分をRedshiftに同期したいのですが、
そのまま変化レコードをS3に上げてCOPYしてしまうと、
id=5, name=tom, post_date=2014-01-01, put_date=2014-01-01
id=5, name=tomm, post_date=2014-01-01, put_date=2014-01-02
この２つの行が同時に挿入されます。
たとえidをPKにしていようと、Uniqueにしていようとも、です。
なので、COPYしたあと、必ずお掃除クエリを流す必要があります。
ここではこんな感じで。

DELETE FROM rds_user
WHERE (id, put_date) IN (
SELECT id, MIN(put_date)
FROM res_user
GROUP BY id
HAVING COUNT(id) > 1
)

単純にレコードが2件以上あるidで最小のput_dateのものを削除する、といったクエリです。
こうしておかないと、後々きたなーいものになってしまうので、要注意です。

Data PipelineではOverwriteというInsertModeがあるようですが、
どの程度しっかり動くのかはまだ試しておりません。
今日は以上な感じですが、
どーでもいいのですが、どうしてRedshiftなんて名前なんでしょうね。
赤方偏移のことなんでしょうが、、、どうゆう意図があったのか、知っている方がいらっしゃったら是非教えて下さい。ではでは。