Amazon Redshiftを始めてみよう(入門ガイド翻訳＆実践：後編)

『Amazon Redshiftを始めてみよう』の後編です。前編コンテンツはこちら。

• Amazon Redshiftを始めてみよう(入門ガイド翻訳＆実践：前編) (クラスタ作成・接続設定等)
• Amazon Redshiftを始めてみよう(入門ガイド翻訳＆実践：後編) (クラスタ接続・各種操作)

また、Redshiftは利用料金もお高めなのでこちらの注意書きも再掲。

あなたが起動しようとしているクラスタは、Sandbox内でお試し的に動かせるものではなく、
実際に稼働し、終了させるまでAmazon Redshiftのクラスタ利用料金が発生し続けます。
ここに記載されている実践作業が終了したら、費用を最小限に抑える為に速やかに
クラスタを終了・削除するようにしてください。

なお進行をより簡易にさせる為に、ここでは『VPCの外側に(VPCを利用せず)クラスタを作成した場合』で進める事にします。

目次

• Step 4: クラスターに接続する / Connect to Your Cluster
• Step 5: テーブル作成、データのアップロード、クエリ例を試す / Create Tables, Upload Data, and Try Example Queries
• Step 6: サンプルクラスターを削除 / Delete Your Sample Cluster
• Step 7: 次はどこへ？ / Where Do I Go from Here?
• まとめ
• 参考情報

　

Step 4: クラスターに接続する / Connect to Your Cluster

では、いよいよ作成したクラスタに接続してみましょう。接続の際は、PostgreSQL互換性のある任意のSQLクライアントであればどれでも使えます。(このチュートリアルでは、SQL Workbenchクライアントを利用する事にします。)

SQL Workbench(やその他クライアントツール)で接続を確立するには、クラスタ固有の接続文字列をまず取得する必要があります。

クラスタ接続文字列を取得する

• 1.Amazon Redshift管理コンソールのクラスタページにて、作成したクラスタを選択。
• 
• 2.[Configuration]タブにて、"クラスタのJDBC URL"の部分をコピー。(クラスタのステータスが"available"になるまで、該当部分(endpoint)の情報は利用・取得出来ません。)
• 

クライアントからクラスタへ接続する

• 1.SQL Workbench クライアントツールを起動。
• 2.[接続プロファイル]ウインドウが自動で開かない場合、ファイルメニューから[Connect Window]を選択し実行。
• 3.接続情報を設定。
  • a.Autocommitのチェックボックスはチェック。
  • b.接続名が[New Profile]のままなので、任意の内容に変更。
  • c.Driver：PostgreSQL(org.postgresql.Driver)を選択。一番最初にWorkbenchを使う際、もしかしたらドライバ定義の編集メッセージが表示されるかも知れません。その場合は、ダイアログボックスの[Yes]を選択し、入手したライブラリファイルを設定してください。
  • 
    
  • d.[Sample URL]の欄には、これまでの手順で取得した、接続を行いたいクラスタのJDBC URLを設定。
  • e.ユーザー名・パスワードについても同様に、作成時に定めた内容をここで設定。
  • 
  • f.OK押下。

接続出来ました。ツールの右上に接続情報が表示されています。

ファイアウォール設定等、接続の際に問題が発生するようであれば、設定したポート番号で外部から接続出来るように
ネットワーク管理者にコンタクトを取ってください。このエントリでは、ポート番号：5439を利用します。

　

Step 5: テーブル作成、データのアップロード、クエリ例を試す /
Create Tables, Upload Data, and Try Example Queries

この時点で、あなたはdevと呼ばれるデータベースを持つ事になります。これからそのデータベースに対し、幾つかのテーブルを作成し、データをアップロードし、クエリを実行してみましょう。

ユーザーが便利に使えるように、このサンプルデータはAmazon S3でも利用が可能となっています。クラスタを作成した時と同じ地域で、バケットを選択してください。

このサンプルデータをコピーするには、AWS認証情報が必要です。認証されたユーザーのみが、このデータにアクセスする事が出来ます。

1.テーブルの作成

公式ドキュメントでは『TICKIT』と呼ばれるデータベースに構成されたテーブル及びデータに関する手順で進めているようなのですが、テーブル数が多い(準備するのもそんだけ手間が掛かる)、サンプルデータが無い(データ作るのもそんだけ(ry))、辺りの理由により別の情報を用意し、シンプルに流れを追う事にしようと思います。

• Step 5: Create Tables, Upload Data, and Try Example Queries - Amazon Redshift
• Sample Database - Amazon Redshift

用意したのは以下のテーブル1つ。distkeyとsortkeyはRedshift独自の関数です。dist_keyとsort_keyを除去すれば通常のものと何ら変わりはありません。ちなみに以下のCREATE TABLE文は『紅白歌合戦』の過去開催記録に関する情報のテーブルになります。 ^*1 CREATE文の詳細についてはAmazon Redshift開発者ガイドの『CREATE TABLE』の項をご参照ください。

• NHK紅白歌合戦 - Wikipedia

create table kohaku(
    timeid integer not null distkey sortkey,  /** 開催回 */
    broadcastdate date not null,   /** 放送日 */
    moderator_female varchar(40),  /** 司会(紅組) */
    moderator_male varchar(40),   /** 司会(白組) */
    finalist_female varchar(40),  /** トリ(紅組) */
    finalist_male varchar(40),    /** トリ(白組) */
    winner char(2),               /** 勝者 */
    get_record boolean,           /** 視聴率記録有無 */
    viewership decimal(3,1));     /** 視聴率 */

2.Amazon S3からデータをコピー

Amazon S3やAmazon DynamoDBからの大規模なデータセットのバルクロードを行う際のパフォーマンスを最適化するために、Amazon Redshiftコマンドを使う事を強くお勧めします。

構文の詳細については、Amazon Redshift開発者ガイドの『COPY』の項をご参照ください。

まずは投入するデータを用意。テーブルデータ項目に対応する／パイプ区切り、等の要件に合わせたファイル内容は以下となります。 ^*2

kohaku_pipe.txt

1|1951-01-03|'加藤道子'|'藤倉修一'|'渡辺はま子'|'藤山一郎'|20|false|0
2|1952-01-03|'丹下キヨ子'|'宮田輝'|'渡辺はま子'|'藤山一郎'|20|false|0
3|1953-01-02|'本田寿賀'|'高橋圭三'|'笠置シヅ子'|'灰田勝彦'|20|false|0
4|1953-12-31|'水の江瀧子'|'高橋圭三'|'淡谷のり子'|'藤山一郎'|10|false|0
5|1954-12-31|'福士夏江'|'高橋圭三'|'渡辺はま子'|'霧島昇'|10|false|0
6|1955-12-31|'宮田輝'|'高橋圭三'|'二葉あき子'|'藤山一郎'|10|false|0
7|1956-12-31|'宮田輝'|'高橋圭三'|'笠置シヅ子'|'灰田勝彦'|20|false|0
8|1957-12-31|'水の江瀧子'|'高橋圭三'|'美空ひばり'|'三橋美智也'|10|false|0
9|1958-12-31|'黒柳徹子'|'高橋圭三'|'美空ひばり'|'三橋美智也'|10|false|0
10|1959-12-31|'中村メイコ'|'高橋圭三'|'美空ひばり'|'春日八郎'|10|false|0
11|1960-12-31|'中村メイコ'|'高橋圭三'|'島倉千代子'|'三橋美智也'|20|false|0
12|1961-12-31|'中村メイコ'|'高橋圭三'|'島倉千代子'|'三波春夫'|20|false|0
13|1962-12-31|'森光子'|'宮田輝'|'島倉千代子'|'三橋美智也'|20|true|80.4
14|1963-12-31|'江利チエミ'|'宮田輝'|'美空ひばり'|'三波春夫'|10|true|81.4
15|1964-12-31|'江利チエミ'|'宮田輝'|'美空ひばり'|'三波春夫'|20|true|72
16|1965-12-31|'林美智子'|'宮田輝'|'美空ひばり'|'橋幸夫'|20|true|78.1
17|1966-12-31|'ペギー葉山'|'宮田輝'|'美空ひばり'|'三波春夫'|10|true|74
18|1967-12-31|'九重佑三子'|'宮田輝'|'美空ひばり'|'三波春夫'|10|true|76.7
19|1968-12-31|'水前寺清子'|'坂本九'|'美空ひばり'|'橋幸夫'|20|true|76.9
20|1969-12-31|'伊東ゆかり'|'坂本九'|'美空ひばり'|'森進一'|10|true|69.7
21|1970-12-31|'美空ひばり'|'宮田輝'|'美空ひばり'|'森進一'|10|true|77
22|1971-12-31|'水前寺清子'|'宮田輝'|'美空ひばり'|'森進一'|20|true|78.1
23|1972-12-31|'佐良直美'|'宮田輝'|'美空ひばり'|'北島三郎'|10|true|80.6
24|1973-12-31|'水前寺清子'|'宮田輝'|'島倉千代子'|'北島三郎'|10|true|75.8
25|1974-12-31|'佐良直美'|'山川静夫'|'島倉千代子'|'森進一'|10|true|74.8
26|1975-12-31|'佐良直美'|'山川静夫'|'島倉千代子'|'五木ひろし'|20|true|72
27|1976-12-31|'佐良直美'|'山川静夫'|'都はるみ'|'五木ひろし'|10|true|74.6
28|1977-12-31|'佐良直美'|'山川静夫'|'八代亜紀'|'五木ひろし'|20|true|77
29|1978-12-31|'森光子'|'山川静夫'|'山口百恵'|'沢田研二'|20|true|72.2
30|1979-12-31|'水前寺清子'|'山川静夫'|'八代亜紀'|'五木ひろし'|10|true|77
31|1980-12-31|'黒柳徹子'|'山川静夫'|'八代亜紀'|'五木ひろし'|10|true|71.1
32|1981-12-31|'黒柳徹子'|'山川静夫'|'森昌子'|'北島三郎'|20|true|74.9
33|1982-12-31|'黒柳徹子'|'山川静夫'|'都はるみ'|'森進一'|10|true|69.9
34|1983-12-31|'黒柳徹子'|'鈴木健二'|'水前寺清子'|'細川たかし'|20|true|74.2
35|1984-12-31|'森光子'|'鈴木健二'|'都はるみ'|'森進一'|10|true|78.1
36|1985-12-31|'森昌子'|'鈴木健二'|'森昌子'|'森進一'|10|true|66
37|1986-12-31|'斉藤由貴'|'加山雄三'|'石川さゆり'|'森進一'|20|true|59.4
38|1987-12-31|'和田アキ子'|'加山雄三'|'和田アキ子'|'五木ひろし'|10|true|55.2
39|1988-12-31|'和田アキ子'|'加山雄三'|'小林幸子'|'北島三郎'|20|true|53.9
40|1989-12-31|'三田佳子'|'武田鉄矢'|'石川さゆり'|'北島三郎'|10|true|47
41|1990-12-31|'三田佳子'|'西田敏行'|'都はるみ'|'森進一'|20|true|51.5
42|1991-12-31|'浅野ゆう子'|'堺正章'|'和田アキ子'|'谷村新司'|10|true|51.5
43|1992-12-31|'石田ひかり'|'堺正章'|'由紀さおり'|'北島三郎'|20|true|55.2
44|1993-12-31|'石田ひかり'|'堺正章'|'石川さゆり'|'北島三郎'|20|true|50.1
45|1994-12-31|'上沼恵美子'|'古舘伊知郎'|'都はるみ'|'五木ひろし'|10|true|51.5
46|1995-12-31|'上沼恵美子'|'古舘伊知郎'|'和田アキ子'|'細川たかし'|20|true|50.4
47|1996-12-31|'松たか子'|'古舘伊知郎'|'坂本冬美'|'北島三郎'|20|true|53.9
48|1997-12-31|'和田アキ子'|'中居正広'|'安室奈美恵'|'五木ひろし'|20|true|57.2
49|1998-12-31|'久保純子'|'中居正広'|'和田アキ子'|'五木ひろし'|10|true|57.2
50|1999-12-31|'久保純子'|'中村勘九郎'|'和田アキ子'|'北島三郎'|20|true|50.8
51|2000-12-31|'久保純子'|'和泉元彌'|'天童よしみ'|'五木ひろし'|10|true|48.4
52|2001-12-31|'有働由美子'|'阿部渉'|'和田アキ子'|'北島三郎'|20|true|48.5
53|2002-12-31|'有働由美子'|'阿部渉'|'石川さゆり'|'五木ひろし'|10|true|47.3
54|2003-12-31|'有働由美子'|'阿部渉'|'天童よしみ'|'SMAP'|20|true|45.9
55|2004-12-31|'小野文惠'|'阿部渉'|'小林幸子'|'五木ひろし'|10|true|39.3
56|2005-12-31|'仲間由紀恵'|'山本耕史'|'天童よしみ'|'SMAP'|20|true|42.9
57|2006-12-31|'仲間由紀恵'|'中居正広'|'川中美幸'|'北島三郎'|20|true|39.8
58|2007-12-31|'中居正広'|'笑福亭鶴瓶'|'石川さゆり'|'五木ひろし'|20|true|39.5
59|2008-12-31|'仲間由紀恵'|'中居正広'|'和田アキ子'|'氷川きよし'|20|true|42.1
60|2009-12-31|'仲間由紀恵'|'中居正広'|'DREAMS COME TRUE'|'北島三郎'|20|true|40.8
61|2010-12-31|'松下奈緒'|'嵐'|'DREAMS COME TRUE'|'SMAP'|20|true|41.7
62|2011-12-31|'井上真央'|'嵐'|'石川さゆり'|'SMAP'|10|true|41.6
63|2012-12-31|'堀北真希'|'嵐'|'いきものがかり'|'SMAP'|20|true|42.5

ファイルをアップロードするバケットを作成。Redshiftクラスタを作成した場所と同じリージョンである必要があるため、Tokyoリージョンに適当な名前でバケットを作成します。

バケットに任意の名前でフォルダを作成、アップロード。ダウンロードできるように『Make Public』も行います。

アマゾンS3からdevのデータベース内のテーブルにデータをアップロード実施。以下のSQL文を実行。

copy kohaku from 's3://shinyaa31-redshift-exercise/loadingdata/kohaku_pipe.txt'
CREDENTIALS 'aws_access_key_id=<アクセスキーID>;aws_secret_access_key=<シークレットアクセスキー>'
delimiter '|' dateformat 'YYYY-MM-DD';

公式ドキュメントではバケット名の後にリージョン情報を付加する、みたいな記述がありましたが、試しに情報を追記してみると『バケット無いよ』なエラーが出てしまいました。この辺もしかして使い方が間違ってたのかな？上記の通り、単純にバケット名指定で実行は出来ていたのですが一応メモしておきます。

• Step 5: Create Tables, Upload Data, and Try Example Queries - Amazon Redshift

An error occurred when executing the SQL command:
copy kohaku from 's3://shinyaa31-redshift-exerciseapnortheast1/loadingdata/kohaku_pipe.txt'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXXXXXXXXXXXXXXXXXXX;aws_se...

ERROR: S3ServiceException:The specified bucket does not exist,Status 404,Error NoSuchBucket,Rid XXXXXXXXXXXXXXXX,ExtRid XXXXXXXXX/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX,CanRetry 1
  Detail: 
  -----------------------------------------------
  error:  S3ServiceException:The specified bucket does not exist,Status 404,Error NoSuchBucket,Rid XXXXXXXXXXXXXXXX,ExtRid XXXXXXXXX/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX,CanRetry 1
  code:      8001
  context:   Listing bucket=shinyaa31-redshift-exerciseapnortheast1 prefix=loadingdata/kohaku_pipe.txt
  query:     724
  location:  table_s3_scanner.cpp:453
  process:   query0_s1_20 [pid=3782]
  ----------------------------------------------- [SQL State=XX000] 

Execution time: 1.56s
1 statement(s) failed.

3.クエリの実行

クエリ実行の構文詳細については、Amazon Redshift開発者ガイドの『SELECT』の項をご参照ください。

試しにデータを色々と検索してみます。

実行結果はクラスタの[Queries]タブで内容を確認する事が出来ます。以下は実行クエリ文の一覧画面。

Queryのリンクから詳細情報を閲覧する事も出来ます。

　

Step 6: サンプルクラスターを削除 / Delete Your Sample Cluster

クラスタを起動し利用出来るようになると、クラスタが起動している時間は課金されていきます。もし使う必要が無いのであれば、削除する事が出来ます。

クラスタの削除

• 1.管理コンソールにアクセス、Redshiftを選択。
• 2.左のメニューから[Clusters]を選択。
• 3.クラスタ一覧の右ペインから任意のクラスタを選択。
• 4.クラスタの詳細ページにて、[Delete]をクリック。
• 5.スナップショットを取りますか？と聞かれるのでNoを選択し、[OK]押下。クラスタ削除が始まります。
• 

　

Step 7: 次はどこへ？ / Where Do I Go from Here?

この入門ガイド完了後は、以下いずれかのガイドでAmazon Redshiftの学習を継続することが出来ます。

Amazon Redshift Cluster Management Guide
(Amazon Redshift クラスタ管理ガイド)

What Is Amazon Redshift? - Amazon Redshift

Amazon Redshift クラスタの作成と管理についてのドキュメントです。

• アプリケーション開発者であれば、プログラムでクラスタを管理する為にAmazon Redshift Query APIを利用する事が出来ます。
更に、Javaや.NET、その他の言語でSDKを提供しています。SDKでは、プログラミング作業を簡素化するために、Amazon Redshiftの基本機能をラップするクラスライブラリを提供します。
• クラスタ管理についてよりインタラクティブな方法を知りたい場合は、Amazon RedshiftコンソールとAWS CLIを使う事が出来ます。API及びCLIの詳細については以下のマニュアルをご参照ください。
  • Welcome - Amazon Redshift(API Reference)
  • Welcome - Amazon Redshift(CLI Reference)

Amazon Redshift Database Developer Guide
(Amazon Redshift データベース開発者ガイド)

Welcome - Amazon Redshift

Amazon Redshift クラスタの作成と管理についてのドキュメントです。

• データベース開発者であれば、Amazon Redshiftデータベース開発者ガイドでデータウェアハウスを構成するデータベース設計、クエリ構築・維持の方法について学ぶ事が出来ます。
• 特に、以下の2つに関する情報を学ぶ事から始める事をお勧めします。
  • Best practices for designing tables - Amazon Redshift(Amazon Redshift テーブル設計のベストプラクティス)
  • Best practices for loading data - Amazon Redshift(Amazon Redshift データロードのベストプラクティス)

サービスのハイライトと価格の詳細については、Amazon Redshiftのサービス詳細ページをご参照ください。

　

まとめ

以上、前後編に亘ってRedshiftの使い方について一連の流れを実践してみました。複雑なデータ、大量件数の扱い、またこのエントリでは各種連携については触れてはいませんが、Redshiftそのものの利用に際してのざっくりとした内容を掴んで頂けると幸いです。

Redshiftについては、入門ガイド(Getting Started)の他にも管理者ガイドやDB開発者ガイド等も充実しており、時間を見つけてガンガン読み解き、実践して行きたいところですね。また、Redshiftと連携している製品等についても興味深いものについては実際に使ってみる等も随時行なっていければと思います。

脚注