Redshift使ったときに調べたこと

1年くらい前につけたメモが元なので最近は少し違うかもしれないけど

1.UPSERT

PrimaryやUniqueがRedshiftでは効かないからUPSERTするためには一時テーブルに読み込み→元テーブルから同じ主キーのレコードを消す→一時テーブルをくっつける操作が必要
http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/merge-replacing-existing-rows.html

2.通し番号、連番

Redshiftにはシーケンスが無い
ROW_NUMBER() を使って通し番号をゲットする。
http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_WF_ROW_NUMBER.html

UPDATE target_table
SET
  id_new = row
FROM
(
  SELECT
    id_org id
    ,row_number () over (partition by id_new ORDER BY id_new ASC) AS row
  FROM
    target_table
)
WHERE
  id_org = id
;

2回目以降のINSERT時にはやっぱ一旦一時テーブルにロードしてROW_NUMBER() をゲットしてそれを元テーブルのMAXやらに足してあげる

UPDATE target_table__stg
SET
  id_new = min_id + row
FROM
(
  SELECT
    id_org id
    ,row_number () over (partition by id_new ORDER BY id_new ASC) AS row
  FROM
   target_table__stg
)
WHERE
  id_org = id
;

3.最新のデータ

余裕でレコードが重複するので更新日が最新のものをとってくるクエリがよく必要になる

SELECT a.id, a.no, a.updt, a.dlfl
FROM table_ a, (SELECT id, MAX(updt) AS updt FROM table_ GROUP BY id) b
WHERE a.id = b.id AND a.updt = b.updt

4.COPYを使ったロード

INSERTは4万リクエストくらいでぶったぎられるからCSV作ってS3においてそっからロードする

5.DDLの取り出し

SQLワークベンチとかで頑張る

Database Explorer→テーブル右クリ→CREATE DDL SCRIPT

6.Redshiftに限らないけど便利でよくつかうUPDATE文

UPDATE文にFROMを使う
http://www.oss-db.jp/measures/dojo_20.shtml

7.客の無能クエリで容量もCPUも危ないときに使うCANCEL

PIDの調べ方とCANCELとABORT
クエリ実行中にタイムアウトしたり、客が失敗したクエリを放置したときに必要になる（しかも動作が重くなったって文句まで言ってくる）
http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/cancel_query.html

8.いつの間にか検証DBが消えてたときに本番DBを壊さないで検証する一時テーブル

CREATE TEMP TABLE target AS SELECT文

9.知ってたらよかったEXPLAIN

10.参照権限

grant使う

grant select on table _TARGET_ to group read_only_group;

11.客のデータ型を無視した要求にこたえる

CAST - 数値型とか
LPAD - 左0埋めしてみたり
to_char - きっと便利

その他知ってるとよいこと

• YYYY-MM-DDTHH:mm:ssはCOPYで形式エラー
• TEXTはVARCHAR(256)になる
• 15分でコネクション切れる（　http://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/connecting-firewall-guidance.html　）
• 無能クエリでデータが膨れ上がると容量がさよならする
• distinct
• top