Redshiftを使ってみる勉強会を社内で実施したのでその資料を公開します。
Redshiftとは
AWSで提供される列指向データベースのマネージドサービス。
列指向のため、主に分析システムのカラムの集計などの利用に向いており、行レベルでの処理には向いていない。一般的なDBMSではフルスキャンとなるようなものもカラムが限定されていれば効率的に実施できる。
Redshiftの料金
お試し利用を行う想定では、dc1.largeを使うことになるかと思います。
東京リージョンでは$0.314/hです。これで0.16TBまで利用可能です。
※最新の正確な料金は以下を参照ください。
https://aws.amazon.com/jp/redshift/pricing/
Redshiftへ接続するクライアント(EC2)のセットアップ
role作成
| 設定箇所 | 設定値 |
|---|---|
| ロールタイプ | AWSアカウントロール > Amazon EC2 |
| アタッチするポリシー | AmazonRedshiftFullAccess |
ec2の立ち上げ
無料利用枠のAmazon Linux(t2.micro)でインスタンスを立ち上げます。
rootになって、PostgreSQLのクライアント(psql)をインストールします
yum update -y
yum install postgresql95
Redshiftのセットアップ
マネジメントコンソールから実施
記事公開時点でまだ日本語化されていないようなので敷居が高いかもしれませんが、設定内容は多くないです。
ここからは以下の表の通り設定を行った前提で書きます。
| 設定箇所 | 設定値 |
|---|---|
| Cluster Identifier | firstredshift |
| Database Name | mydb |
| Database Port | 5439 |
| User Name | awsuser |
| Nord Type | dc1.large |
| Availability Zone | （立ち上げたEC2と同じAZ） |
立ち上げると、ClusterのConfigurationのページでEndpointが表示されているのでメモっておきます。
例：firstredshift.XXXXXXXXXX.ap-northeast-1.redshift.amazonaws.com:5439
CLIで実施
Redshiftハンズオンで紹介されていますのでそちらを参考に。
Redshiftへの接続
以下で接続します。
psql -h firstredshift.XXXXXXXXXX.ap-northeast-1.redshift.amazonaws.com -U awsuser -p 5439 -d mydb
【10/19追記】
Redshiftの接続に失敗する場合は、Redshiftのセキュリティグループでインバウンドルールの許可する範囲に入っていない可能性が高いです。Redshiftの接続用のポート5439をすべてから許可するなど、設定変更をしてください。
Redshiftへのデータ投入
データ投入に利用するIAMユーザを用意
ユーザを作成して、アクセスキーを生成します。ユーザには以下のポリシーをアタッチしておきます。
- AmazonRedshiftFullAccess
- AmazonS3ReadOnlyAccess
投入するデータ
こんな形式のデータを利用します。簡単なスクリプトで作成した全く意味のないデータです。これをS3においておきます。
created_at,max,min
2016-10-01 00:00:00,351,6
2016-10-01 00:00:01,428,117
2016-10-01 00:00:02,736,81
2016-10-01 00:00:03,943,749
2016-10-01 00:00:04,883,113
2016-10-01 00:00:05,619,450
2016-10-01 00:00:06,580,475
2016-10-01 00:00:07,656,310
2016-10-01 00:00:08,737,552
2016-10-01 00:00:09,702,655
2016-10-01 00:00:10,855,698
テーブルの作成
上記のデータを投入するためにテーブルを作成します。
CREATE TABLE sample1
(
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
max_value INTEGER NOT NULL,
min_value INTEGER NOT NULL
);
データ投入
の前に、試しにinsertして、selectしてみましょう
mydb=# \timing
Timing is on.
mydb=# insert into sample1 values( '2016-10-10 12:34:56', 1000, 0 );
INSERT 0 1
Time: 113.910 ms
mydb=# select * from sample1;
created_at | max_value | min_value
---------------------+-----------+-----------
2016-10-10 12:34:56 | 1000 | 0
(1 row)
Time: 6.514 ms
次は、1日分（86400行）のデータを一括投入してみます。
credentialsで先ほど作成したユーザのアクセスキーを使います。
copy sample1 from 's3://firstredshift/sample20161001.csv'
credentials 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>'
DELIMITER ','
IGNOREHEADER 1
REGION 'ap-northeast-1';
実際のシステムでは上記のような理想的なINPUTファイルではなく、固定長であったり、日付形式が違ったり、zip圧縮されていたり、と悩みがいろいろあると思いますが、ある程度対応しているようです。このあたりが参考になるかとおもいます
Redshiftからの参照
データ数をさらに30日分に増やして、いろいろやってみましょう。
mydb=# select count(*) from sample1;
count
---------
2592000
(1 row)
Time: 20.480 ms
mydb=# select sum(max_value) from sample1;
sum
------------
1728058004
(1 row)
Time: 1021.840 ms
mydb=# select sum(min_value) from sample1;
sum
-----------
863331248
(1 row)
Time: 972.636 ms
mydb=# select sum(max_value) from sample1 where created_at between '2016-10-10' AND '2016-10-12';
sum
-----------
115238185
(1 row)
Time: 1011.949 ms
mydb=# select sum(min_value) from sample1 where created_at between '2016-10-10' AND '2016-10-12';
sum
----------
57783916
(1 row)
Time: 1037.251 ms
うーん、こんなもんかという感じ。
sortkeyの活用
Redshiftには実はsortkeyというものがあり、sortkeyに指定された順にデータを保持しています。
where句でcreated_atの期間指定をするようなケースでは読み込む対象データが限定することが可能です。
テーブルをsample2として作り直します。sortkeyにはcreated_atを指定します。
CREATE TABLE sample2
(
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
max_value INTEGER NOT NULL,
min_value INTEGER NOT NULL
)
SORTKEY (created_at);
先ほどと同じ処理を実行。
mydb=# select count(*) from sample2;
count
---------
2592000
(1 row)
Time: 20.242 ms
mydb=# select sum(max_value) from sample2;
sum
------------
1728058004
(1 row)
Time: 33.222 ms
mydb=# select sum(min_value) from sample2;
sum
-----------
863331248
(1 row)
Time: 33.114 ms
mydb=# select sum(max_value) from sample2 where created_at between '2016-10-10' AND '2016-10-12';
sum
-----------
115238185
(1 row)
Time: 14.508 ms
mydb=# select sum(min_value) from sample2 where created_at between '2016-10-10' AND '2016-10-12';
sum
----------
57783916
(1 row)
Time: 14.507 ms
確かに早くなっていますね。ただし、全件のsumも同様に早くなっているのが謎です・・
＃sortkeyは影響しているとは思うのですが試してみたらこうなった、くらいの結果です。その点はご了承ください。
VACUUM
初回にCOPYで一括データ投入した今回のような場合は不要ですが、毎日データが生成されて取り込む必要がある場合は、性能を悪化させないために適宜VACUUM処理を行うことを検討しなければいけないようです。
と思っていたら機能拡張されていて、日付のソートキーの場合は問題なくなっていました。すばらしい。
Redshiftアップデート：COPYやVACUUMの機能向上、SSLロギングの情報追加等
2, COPYで条件にそったデータを挿入した場合、ソート済の領域としてマージされるように
Redshiftの中では表のデータは「ソート済領域」と「非ソート済領域」に分けて管理されています。VACUUMを使ってソートされたデータはソート済領域に保存され、追加データは非ソート領域に保存されます。
今回の機能拡張では、条件を満たした場合にCOPYで追加したデータがソート済領域に追加されるようになります。その条件はマニュアルの以下のページに記載されています。
条件は以下の通りで、これらを全て満たしている必要があります。
- 表がコンパウンドソートキー（Interleaved Sort Keyではなく）を使っていて、かつソートキー列が1つのみ
- ソートキーの列がNOT NULL
- 表が100%ソート済か、もしくは空（から）
- 新しく追加されるソートキー列の値が既存データよりソート順で大きい値を持つ
これは、列に常に大きい値が挿入されるようなケース、つまり時刻がソートキーになっていて、そこに追加で新しいデータを追加し続けるような表構造（時系列でデータを入れ続ける）の場合に役に立ちます。
QueryやLoadなどの状態確認
マシンリソースや接続数だけでなく、QueryやLoadのSQLごとの情報（実行時間など）もマネジメントコンソールから履歴が確認できる。これは便利ですね。
最後に
Redshift,EC2は削除しておくのを忘れずに。
その他
参考になったサイトを紹介します。