Amazon Redshiftでよく使うけど忘れがちなコマンド(クエリ）まとめ

追記

2014-10-29

"実行中のクエリをキャンセル"するの項に追記しました

コマンド（クエリ）

ディスクのスペースを確認する

SELECT owner, host, diskno, used, capacity,
(used-tossed)/capacity::numeric * 100 AS pctused 
FROM stv_partitions ORDER BY owner;

本家ドキュメント:STV_PARTITIONS

ロードのエラー原因を探る

CREATE VIEW loadview AS
  (SELECT DISTINCT tbl, TRIM(name) as table_name, query, starttime,
  TRIM(filename) AS input, line_number, colname, err_code,
  TRIM(err_reason) AS reason
  FROM stl_load_errors sl, stv_tbl_perm sp
WHERE sl.tbl = sp.id);

にて、viewテーブルを作成しておき、

SELECT * FROM loadview WHERE table_name = 'table_name';

本家ドキュメント:STL_LOAD_ERRORS

実行中のクエリをキャンセルする

SELECT pid, TRIM(user_name), starttime, SUBSTRING(query,1,20) 
  FROM stv_recents
WHERE status = 'Running';

にて、該当のクエリのPIDを調べ、

CANCEL PID;

※ctrl + cでも可能だが正常に終了しないこともあるため、CANCELコマンドを使っておいたほうが吉

本家ドキュメント:CANCEL

2014-10-29 追記
Management Consoleからもキャンセルが可能になりました。Release: Amazon Redshift on 2014-10-28
これは地味に嬉しいですね。詳細は@con_mameさんの記事、RedshiftのクエリとLoadをManagement Consoleからキャンセルする
を見ると良いと思います。

テーブル構成を確認する

SELECT "column", type, encoding, distkey, sortkey FROM pg_table_def WHERE tablename = 'table_name';

本家ドキュメント:PG_TABLE_DEF

S3からテーブルへのインポート

COPY hoge FROM 's3://table/hoge/import/20140101' CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=XXXXXXXXXX' DELIMITER '|' GZIP;

本家ドキュメント:COPY

S3へテーブルのエクスポート

UNLOAD ('SELECT id,txt FROM hoge WHERE created_at = \'2014-01-01 00:00:00\'') TO 's3://backup/hoge_140101' CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_keyXXXXXXXXXX' delimiter '|' GZIP;

本家ドキュメント:UNLOAD

sortkeyに絞ってvacuumをかける

VACUUM SORT ONLY table_name;

本家ドキュメント:VACUUM

統計情報を更新する

ANALYZE table_name;

本家ドキュメント: ANALYZE

JSON要素をパースして取得する

SELECT
  json_extract_path_text(json, 'relationship', 'target', 'screen_name')
FROM
  test_json;

本家ドキュメント:JSON_EXTRACT_PATH_TEXT function

(MySQLの)REPLACE文

、、、に相当する構文は残念ながら現時点(2014/4/16現在)ではサポートされていないので、以下を参考にしてやります。

本家ドキュメント:Updating and inserting new data

コマンドラインからのクエリ実行

export PGPASSWORD=XXXXXXXXXX;psql -h redshift.abcdefg.us-west-2.redshift.amazonaws.com hoge -p 5439 -U user -c "SELECT COUNT(*) FROM hoge"

実行時間表示を有効にする

\timing on

スキーマの移動

SET SEARCH_PATH TO hoge;