この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
もくじ
- はじめに
- 環境構築
- RDS(PostgreSQL)インスタンス作成
- コマンドラインツール接続確認(by psql)
- GUIツール接続確認(by pgAdmin3)
- 解析データの投入
- Tableau Desktop経由で接続確認
- まとめ
はじめに
ついにPostgreSQLもRDSに組み込まれましたね！という訳で、早速試してみました！
環境構築
想定する環境はこんな感じ。最小限構成、EC2で一旦確認などしてみてからTableauにて接続確認、という流れです。外枠構築にはこれまでのシリーズで用いたCloudFormationテンプレートを使います。
Tableau接続デモ用構成(VPC&各種ネットワーク構成)起動
RDS(PostgreSQL)インスタンス作成
そしてRDSインスタンス作成へ。とは言っても画面インタフェース的にも特に変わる訳で無し、必要な設定を適宜進めて行きます。起動まではキャプチャのみで進行。
PostgreSQL導入時のポートは5432で進めていたので、別途セキュリティグループにも設定を追加。
コマンドラインツール接続確認(by psql)
まずは同じVPC Public Subnet内に立てたEC2インスタンスからの接続を試みます。PostgreSQLクライアントは無ければ導入してください。
$ ssh -i xxxxxxxxx-key.pem ec2-user@xx.xxx.xxx.xx
$ sudo yum install -y postgresqlログイン後テーブル確認まで。
$ psql -h cmpostgresql.xxxxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -d cmpgsqldb -U cmpgsqluser -p 5432
ユーザ cmpgsqluser のパスワード: (*******************)
psql (9.2.5, サーバー 9.3.1)
注意： psql バージョン 9.2, サーバーバージョン 9.3.
psql の機能の中で、動作しないものがあるかもしれません。
SSL 接続 (暗号化方式: DHE-RSA-AES256-SHA, ビット長: 256)
"help" でヘルプを表示します.
cmpgsqldb=>
cmpgsqldb=> \c
psql (9.2.5, サーバー 9.3.1)
注意： psql バージョン 9.2, サーバーバージョン 9.3.
psql の機能の中で、動作しないものがあるかもしれません。
SSL 接続 (暗号化方式: DHE-RSA-AES256-SHA, ビット長: 256)
データベース "cmpgsqldb" にユーザ"cmpgsqluser"として接続しました。
cmpgsqldb=> \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権
-----------+-------------+------------------+-------------+-------------------+-----------------------------
cmpgsqldb | cmpgsqluser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | cmpgsqluser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
rdsadmin | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
template0 | rdsadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin +
| | | | | rdsadmin=CTc/rdsadmin
template1 | cmpgsqluser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/cmpgsqluser +
| | | | | cmpgsqluser=CTc/cmpgsqluser
(5 行)
cmpgsqldb=>GUIツール接続確認(by pgAdmin3)
GUIツールでも試してみます。Redshiftの場合は環境に対応するツールしか使えませんでしたが、今回は純正PostgreSQLなのでこの辺は何ら問題ありませんね。
繋がりました。
ただツールの方が少し前にインストールしていたのを使ったため＆RDS(PostgreSQL)のバージョンが9.3.1だったため、このような警告が表示されました。ツールの方も最新版にアップデートする必要がありますね。
データ投入
当シリーズで使わせてもらっているTableau Publicの『OlympicAthletesMedals.csv』をここでも使います。入手先・データ加工過程については以下エントリ群をご参照ください。
- Sample Data Sets | Tableau Public
- Tableau Desktopから色々なデータソースに繋いでみる(Amazon RDS(MySQL)編) ｜ Developers.IO
- Tableau Desktopから色々なデータソースに繋いでみる(Amazon Redshift on VPC編) ｜ Developers.IO
まずはGUIツール(pgadmin3)で試してみます。とその前にテーブル作成。
CREATE TABLE medals (
athlete VARCHAR(100),
age DECIMAL(2),
country VARCHAR(100),
year DECIMAL(4),
ceremony_date DATE,
sport VARCHAR(100),
gold_medals DECIMAL(2),
silver_medals DECIMAL(2),
bronze_medals DECIMAL(2),
total_medals DECIMAL(2)
);
そしてデータロード。COPY文が使えるのかなと思い普通に実行してみたのですがエラーとなってしまいました。superuser権限が必要となるようです。
ec2経由でpsqlログインし、ユーザーの状態を見てみます。確かにRDSコンソール上で作成したユーザーにはsuperuser権限がありません。また、権限を持っているrdsadminユーザーとして実行を行う、権限を付与させるというのも厳しそう。
cmpgsqldb=> \du
ロール一覧
ロール名 | 属性 | メンバー
---------------+----------------------------------------------------------------------+-----------------
cmpgsqluser | ロールを作成できる, DBを作成できる | {rds_superuser}
rds_superuser | ログインできない | {}
rdsadmin | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション | {}上記GUIでも表示されていましたが、ヒントとしてpsqlのコマンドでなら行ける、という内容が。
cmpgsqldb=> copy medals from 'OlympicAthletes_0.csv' with csv header;
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.試してみましょう。ec2サーバにロードするCSVファイルを用意しておきます。
$ pwd
/home/ec2-user
$ ll
合計 516
-rw-rw-r-- 1 ec2-user ec2-user 527287 11月 15 02:44 2013 OlympicAthletes_0.csv
$ログイン後psqlのCOPYコマンド実行。今度は上手くロードされました！しかも早い。
$ psql -h cmpostgresql.xxxxxxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -d cmpgsqldb -U cmpgsqluser -p 5432
cmpgsqldb=> \copy medals from 'OlympicAthletes_0.csv' WITH CSV;
cmpgsqldb=> select count(*) from medals;
count
-------
8618
(1 行)Tableau Desktop経由で接続確認
Tableauを起動して接続をしてみます。Redshift編でJDBCドライバを導入していたので今回の件についてはドライバ環境のくだりは割愛。(この辺り詳細はAmazon Redshift on VPC編をご参照ください)接続先データソースにPostgreSQLを指定、所定の内容を入力。
無事、繋がりました！
まとめ
という訳で、リリース記念にPostgreSQLネタで1本書いてみました。待望でもあったPostgreSQLがRDSの仲間入りをした事によって、更に色々夢も広がりますね！
2
