1. zaburo

    No comment

    zaburo
Changes in body
Source | HTML | Preview
@@ -1,268 +1,279 @@
Amazon Redshiftを試してみます。
##データの準備
毎度お馴染み。Amazon EMRのImpalaのテスト用に容易されているジェネレーターが生成した1GBx3個のファイルを利用します。
+EC2上とかでもいいですが、今回はローカルのMacをクラアンとにしたいので、ツールをローカルにダウンロード。
```bash
wget http://elasticmapreduce.s3.amazonaws.com/samples/impala/dbgen-1.0-jar-with-dependencies.jar
+```
+
+データを生成します。
+
+```bash
java -cp dbgen-1.0-jar-with-dependencies.jar DBGen -p ./ -b 1 -c 1 -t 1
```
+上記の場合、books 1G,customer 1G, transanction 1Gの計3GBのデータができます。
+実行にはJavaが必要です。
+
##データの容易・アップロード
S3に置いておくのがお作法のようなので、アップロードします。保存用のバケットを作成し、必要に応じてディレクトリなどを作成しておきます。
特にツールがなくても、ブラウザ経由でアップロードできます。
>くどいようですが、何でAzureのBlobはアップロード機能をつけないのでしょうか?
![redshift](http://www.bluecode.jp/qiita/redshift/001.png)
アップロード完了しました。
![redshift](http://www.bluecode.jp/qiita/redshift/002.png)
+諸事情によりtransactionのファイル名がtranzaction(z)となっています。
+
##クライアントツールの準備
ご存知RedshiftはPostgreSQL互換なので、クライアントツールとしては、psqlが使えます。
私は、Mac環境なので、brewを使ってインストールしておきます。なお、Redshiftは8.x互換(最新は9.x)なので、8.xをインストールしておきます。
>本当はclientツールだけでいいのですが、わからないので、PostgreSQLごとインストールしました。
```bash
brew install brew install postgresql8
psql --version
psql (PostgreSQL) 8.4.22
```
##Redshiftクラスタの構築
Redshiftメニューを選択し、[Launch Cluster]ボタンをクリックし、クラスタの構築を進めます。
クラスタ名、DB名、ログインID,PWの設定などを行ないます。portはとりあえず標準のままにします。
![redshift](http://www.bluecode.jp/qiita/redshift/003.png)
クラスタのタイプを選択します。
ここでは最も低価格な(デフォルトの)構成を採用してみます。Redshiftの本領を発揮させるためにはノード数を増やした方がいいでしょう。
>dc1.largeは100円換算で25円/時. 1ヶ月18,750円程度。競合?のAzure SQL Data Warehouseは71.4円/時。最低金額はRedshiftの方が安いですね。
![redshift](http://www.bluecode.jp/qiita/redshift/004.png)
その他、設定をします。とりあえず標準のまま進めます。
![redshift](http://www.bluecode.jp/qiita/redshift/005.png)
情報を確認し、[Launch Cluster]をクリックします。
![redshift](http://www.bluecode.jp/qiita/redshift/006.png)
作成準備ができたので[close]します。
![redshift](http://www.bluecode.jp/qiita/redshift/007.png)
5分程で、availableとなります。
![redshift](http://www.bluecode.jp/qiita/redshift/009.png)
クラスタ名をクリックして情報を確認します。
Endpointにサーバ名:ポート番号、セキュリティーグループ(Firewall)、DB名、ログイン情報などが表示されています。
![redshift](http://www.bluecode.jp/qiita/redshift/010.png)
##Redshiftを使ってみる
###Firewallの設定をする
クラスター完成後は、Firewallにより、どこからもアクセスできなくなっています。
Security Groupのリンクをクリックし、Security Group設定ページに移動し、5439ポートを開放します。
マイIPを選ぶことで今利用しているクライアントのIPを追加できるようです。
![redshift](http://www.bluecode.jp/qiita/redshift/011.png)
###ログイン
準備ができましたので、ログインしてみます。
psqlコマンドでログインします。
>psql -h hostname -p port -U user -d database
という書式のようです。
```bash
psql -h blueshift.cpcgfwtyukro.ap-northeast-1.redshift.amazonaws.com -p 5439 -U admin -d testdb
Password for user admin:
psql (8.4.22, server 8.0.2)
WARNING: psql version 8.4, server version 8.0.
Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
testdb=#
testdb=#
```
dbname=#というプロンプトがでればOKです。
###PostgreSQLの最低管理コマンド
create tableやselectなどに加え、下記コマンドを知っていればとりあえず何とかなるでしょう。
コマンドは[\command(word)]という書式を取ります。
```bash
#show database
\l
#show tables
\d
#desc
\d table_name
#exit
\q
```
###テーブルの作成
>MySQLと違い、コンソールで#とするとエラーになりますね。取って実行して下さい。
```sql
#books
create table books(
id bigint not null distkey sortkey,
isbn varchar(128),
category varchar(128),
publish_date datetime,
publisher varchar(128),
price float
);
#customers
create table customers(
id bigint not null distkey sortkey,
name varchar(128),
date_of_birth datetime,
gender varchar(128),
state varchar(128),
email varchar(256),
phone varchar(128)
);
#transactions
create table transactions(
id bigint not null distkey sortkey,
customer_id bigint,
book_id bigint,
quantity INT,
transaction_date datetime
);
```
###データロード
Redshiftプロンプトにおいて、copyコマンドを利用して行ないます。
>copy tablename from 's3://bucket/path/to/data' credential 'ws_access_key_id=key;aws_secret_access_key=secret'
という書式です。access_keyとsecretは、「認証情報」メニューで確認・生成できます。
![redshift](http://www.bluecode.jp/qiita/redshift/012.png)
>デフォルトで設定されているaccess_keyはsecretを表示できないということで、新しいaccess_keyを生成します。
```bash
#books
copy books from 's3://tamabucket/data/books'
credentials 'aws_access_key_id=xxxxxxxxxx;aws_secret_access_key=yyyyyyyyyy'
delimiter '|';
#customers
copy customers from 's3://tamabucket/data/customers'
credentials 'aws_access_key_id=xxxxxxxxxx;aws_secret_access_key=yyyyyyyyyy'
delimiter '|';
#transactions
copy transactions from 's3://tamabucket/data/transactionz'
credentials 'aws_access_key_id=xxxxxxxxxx;aws_secret_access_key=yyyyyyyyyy'
delimiter '|';
```
ロードは非常に速い印象でした。
###クエリ
クエリの時間を表示させたいので、
```bash
\timing
```
として、計測機能をonにします。
####クエリ1
```sql
select count(*) from books;
```
* Time: 1323.417 ms (1500万件)
####クエリ2
```sql
select * from books where isbn='2-90323-440-7';
```
* Time: 3582.724 ms
####クエリ3
```sql
select category,count(*) from books group by category;
```
* Time: 3299.236 ms
####クエリ4
```sql
select
transactions.id,
books.isbn,
books.category,
books.price,
transactions.quantity,
customers.id,
customers.name
from transactions
left join books on transactions.book_id = books.id
left join customers on transactions.customer_id = customers.id
limit 10;
```
* Time: 6688.158 ms
####クエリ5
```sql
select
customers.name,
sum(books.price) as sum
from (transactions left join books on (transactions.book_id = books.id)) left join customers on (transactions.customer_id = customers.id)
group by customers.name
order by customers.name desc
limit 10;
```
* Time: 6184.344 ms
##まとめ
クエリは、2回目移行はキャッシュが聞くので、1回目の速度を計測しました。ただ、数回クラスタを作成し、同じクエリを実行しても、場合により2~3倍程度実行時間が違うことがありました。
今回は合計で3GBと、ビックデータという意味では小さなデータでテストしましたが、だいたいの性質はわかりました。