Amazon Redshiftを試してみる


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回目移行はキャッシュが聞くので、１回目の速度を計測しました。ただ、数回クラスタを作成し、同じクエリを実行しても、場合により2~3倍程度実行時間が違うことがありました。
今回は合計で3GBと、ビックデータという意味では小さなデータでテストしましたが、だいたいの性質はわかりました。