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 が読んでくれないので注意。
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 は数値はまあなんでもよくて、とにかく捨てるなよ!という設定。
awslabsからamazon-redshift-utilsがリリースされたようです
awslabs/amazon-redshift-utils · GitHub
内容は AdminScripts と ColumnEncodingUtility の 2 つ。
ColumnEncodingUtility は実テーブルから最適な圧縮エンコーディングを選んで、 そのエンコーディングを使った新しいテーブルにデータを移動できるようだ。
AdminScripts は運用系の小さいスクリプト集。 以下の 4 つらしい。
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の文字列はかなり上限が短いので個数が多いと入りきらない
Redshiftはビューの参照先テーブルをrenameすると追跡する
またタイトルだけで言いたいこと終わった…
=> 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 で作り直す方法とめっちゃ相性悪い。
SQLだけでテーブル内容の一致判定をする
同じ定義のテーブルが2つあり、 その全行が同じかどうか(違う行がないか)を確かめたいときの方法。
select count(*) from ( (select * from x except select * from y) union all (select * from x except select * from y) )
結果が 0 なら内容は同じです。1 以上なら何かしら異なる。
ちなみに遅いです。あくまでも行数が少ないとき向け(100万行くらいまでか)。 高速にしたいならテーブルごとにユニークキーを考えてSQL書くしかないですね。
ビューも alter table で renameできることを知った
知った。
=> 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 にオブジェクトタイプを指定させる 意味を感じなくなってきた