1. zaburo

    Posted

    zaburo
Changes in title
+Amazon Redshiftを試してみる
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,275 @@
+
+Amazon Redshiftを試してみます。
+
+##データの準備
+
+毎度お馴染み。Amazon EMRのImpalaのテスト用に容易されているジェネレーターが生成した1GBx3個のファイルを利用します。
+
+```bash
+wget http://elasticmapreduce.s3.amazonaws.com/samples/impala/dbgen-1.0-jar-with-dependencies.jar
+java -cp dbgen-1.0-jar-with-dependencies.jar DBGen -p ./ -b 1 -c 1 -t 1
+```
+
+##データの容易・アップロード
+
+S3に置いておくのがお作法のようなので、アップロードします。保存用のバケットを作成し、必要に応じてディレクトリなどを作成しておきます。
+
+特にツールがなくても、ブラウザ経由でアップロードできます。
+
+>くどいようですが、何でAzureのBlobはアップロード機能をつけないのでしょうか?
+
+![redshift](http://www.bluecode.jp/qiita/redshift/001.png)
+
+アップロード完了しました。
+
+![redshift](http://www.bluecode.jp/qiita/redshift/002.png)
+
+##クライアントツールの準備
+
+ご存知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の本領を発揮させるためにはノード数を増やした方がいいでしょう。
+
+![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万件)
+
+>SQL Data Warehouse 100DWU 2秒
+
+####クエリ2
+
+```sql
+select * from books where isbn='2-90323-440-7';
+```
+
+* Time: 3582.724 ms
+
+>SQL Data Warehouse 100DWU 4秒
+
+####クエリ3
+
+```sql
+select category,count(*) from books group by category;
+```
+
+* Time: 3299.236 ms
+
+>SQL Data Warehouse 100DWU 5秒
+
+####クエリ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
+
+>SQL Data Warehouse 100DWU 1:29秒
+
+####クエリ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
+
+>SQL Data Warehouse 100DWU 2:52秒
+
+##まとめ
+
+クエリは、2回目移行はキャッシュが聞くので、1回目の速度を計測しました。ただ、数回クラスタを作成し、同じクエリを実行しても、場合により2~3倍程度実行時間が違うことがありました。
+今回は合計で3GBと、ビックデータという意味では小さなデータでテストしましたが、だいたいの性質はわかりました。
+