2015-03-03

psqlコマンドのオプションとパスワード入力を省略する

psqlコマンドの設定シリーズ第二弾。

ホスト名とかのデフォルトを設定する

psqlでDBに接続するときはこんなコマンドで接続しますが……

psql -h xxxx.ap-northeast-1.redshift.amazonaws.com -p 5439 -U myuser dev

これオプション多くて面倒じゃないですか。そこで以下のように環境変数を設定しておけばオプションは全部省略できます。

export PGHOST=xxxx.ap-northeast-1.redshift.amazonaws.com
export PGPORT=5439
export PGDATABASE=dev
export PGUSER=myuser

ユーザー名だけ変えたいみたいなときは、変えたいものだけ明示的にオプションを指定すればよいです。

パスワード入力を省略する

最後に残るのがパスワード入力ですが、実はこれも省略できます。 ~/.pgpass ファイルに以下のように書いて、chmod 600 しておけば psql コマンドが勝手に読み込みます。

ホスト名:ポート番号:データベース名:ユーザー名:パスワード

mode 600 にしないと psql が読んでくれないので注意。

2015-03-03

psqlコマンドのお勧め設定

実はpsqlコマンドは色付きプロンプトを始めとして意外と便利な設定ができるようになっています。わたしが使っているおすすめの ~/.psqlrc を書いておきます。

\set PROMPT1 '%[%033[1;32m%]%n %`date +%H:%M` %R%#%[%033[0m%] '
\set PROMPT2 '%[%033[1;32m%]%R%#%[%033[0m%] '
\timing on
\set HISTSIZE 1000000

以下解説

PROMPT1, PROMPT2

お約束の色付きプロンプト。上記設定は緑のボールドにしていて、色を抜かすとこう↓なります。

'%n %`date +%H:%M` %R%# '

表示すると「aamine 18:05 =>」みたいな感じです。以下解説。

%[...%] : エスケープが有効
%033 : ESC文字
%n : ユーザー名
%cmd : 任意のコマンド実行
- date +%H:%M は時刻を HH:MM 形式で表示します。Redshiftではクエリーを長時間実行するため、何時に投げたんだっけこれ……みたいな気持によくなるので付けました。
%R : 最初の行では "=" で 2 行目から "-"
%# : スーパーユーザーなら "#"、一般ユーザーなら ">"

実行時間表示

\timing on でクエリー実行後に実行時間が表示されます。ミリ秒で。秒単位がいいんだけど単位は変えられないっぽい。

ヒストリ

\set HISTSIZE 1000000 は数値はまあなんでもよくて、とにかく捨てるなよ！という設定。

2015-02-17

awslabsからamazon-redshift-utilsがリリースされたようです

<a href="https://github.com/awslabs/amazon-redshift-utils">awslabs/amazon-redshift-utils</a>

awslabs/amazon-redshift-utils · GitHub

内容は AdminScripts と ColumnEncodingUtility の 2 つ。

ColumnEncodingUtility は実テーブルから最適な圧縮エンコーディングを選んで、そのエンコーディングを使った新しいテーブルにデータを移動できるようだ。

AdminScripts は運用系の小さいスクリプト集。以下の 4 つらしい。

top_queries.sql: 過去7日で一番時間を使っているクエリー上位 50 個を出す
perf_alerts.sql: full table scanジョインが走っているクエリーを探す
filter_used.sql: テーブルをスキャンするとき使われたフィルタ（where句）を出す
commit_stats.sql: COMMITのときのクラスターリソースの消費状況を出すらしいが、そもそも何のCOMMITなんだ。このテーブルの情報ということしかわからない。 STL_COMMIT_STATS - Amazon Redshift

2014-07-31

Redshiftで任意個数の文字列を扱う

最終的には縦持ちにしちゃえばどんなRDBでも扱えるんだけど、某MySQLとか某Hadoopとかだと配列で持ってるじゃないですか。ああいうのをどうやってとりこむか1年くらい考えてたんだけどついに決定版を思いついた。

扱える個数の上限はあるんだけど、現実的にはほぼ上限を考えなくて済むと思う。

1. 文字列配列をJSONとしてRedshiftに取り込む

=> select * from slog order by 1;
id |    words    
----+---------------
  1 | ["a","b","c"]
  2 | ["xxx","yyy"]

2. 連番 1 カラムだけのテーブルを用意する

この連番テーブルのサイズが扱える個数の上限になってしまうので注意

=> select * from sseq order by 1;
 x 
---
 1
 2
 3
 4
 5

3. ジョインする

不等号でジョインするのがポイント

select
    slog.id
    , seq.x as param_id
    , json_extract_array_element_text(slog.words, seq.x - 1) as word
from
    slog inner join seq
    on seq.x <= json_array_length(slog.words)
order by 1, 2
;

4. なんと縦持ちになりました！

id | param_id | word 
----+----------+------
  1 |        1 | a
  1 |        2 | b
  1 |        3 | c
  2 |        1 | xxx
  2 |        2 | yyy

欠点

連番テーブルのサイズで扱える個数の上限が決まる
Redshiftの文字列はかなり上限が短いので個数が多いと入りきらない

2014-07-15

Redshiftはビューの参照先テーブルをrenameすると追跡する

redshift

またタイトルだけで言いたいこと終わった…

=> create table t (x int);
CREATE TABLE

=> insert into t values (777);
INSERT 0 1

=> create view v as select * from t;
CREATE VIEW

=> alter table t rename to t2;
ALTER TABLE

=> select * from v;
  x  
-----
 777
(1 row)

この仕様、便利なようで超不便……。 drop - create で作り直す方法とめっちゃ相性悪い。

2014-07-15

SQLだけでテーブル内容の一致判定をする

redshift

同じ定義のテーブルが2つあり、その全行が同じかどうか（違う行がないか）を確かめたいときの方法。

select count(*) from (
  (select * from x except select * from y)
  union all
  (select * from x except select * from y)
)

結果が 0 なら内容は同じです。1 以上なら何かしら異なる。

ちなみに遅いです。あくまでも行数が少ないとき向け（100万行くらいまでか）。高速にしたいならテーブルごとにユニークキーを考えてSQL書くしかないですね。

2014-06-20

ビューも alter table で renameできることを知った

redshift

知った。

=> create view vv as select * from t;
CREATE VIEW
Time: 144.060 ms

=> alter table vv rename to vv2;
ALTER TABLE
Time: 134.566 ms

=> \dv vv2
       List of relations
 schema | name | type | owner  
--------+------+------+--------
 aamine | vv2  | view | aamine
(1 row)

ますます drop とか alter にオブジェクトタイプを指定させる意味を感じなくなってきた