こんにちは。日商エレクトロニクスの友近です。
今回は EC2 や PHP(PDO) からPostgreSQL(RDS)にリモートアクセスするまでの覚え書きです。
DB系は業務と関係なく個人的な趣味で勉強しています。これまで検証環境で MySQL を使ってきましたが、ウインドウ関数など方言の違いで使えないコマンドがあります。真面目にやるならやはり複数環境があった方がいいと思いました。今回 PostgreSQL 環境を用意しました。
RDS
無料枠の PostgreSQL を選びました。インスタンスは"t2.micro"を使いました。下記の情報をどこかにメモしておくと便利です。
- DBインスタンス識別子
- マスタユーザ
- パスワード
- データベース名
- エンジン：PostgreSQL 9.6.2
- エンドポイント
インスタンスが起動したら、セキュリティグループのインバウンドでポート番号5432が許可されていることを確認します。
接続元ホスト(クライアント)のインストール
接続元ホストとなる EC2 上に Postgres のクライアントをインストールします。RDSにリモート接続するには、接続元のホストにクライアントをセットアップする必要があります。MySQLでいう"mysqlclient"に相当するものです。
クライアントインストール
$ sudo yum localinstall -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-ami201503-96-9.6-2.noarch.rpm
$ sudo yum install -y postgresql96
RDSにリモートアクセス
先ほど控えておいたRDSの情報を元に、下記のコマンドでリモートアクセスできることを確認します。
$ psql -h {エンドポイント} -U {マスターユーザ} {データベース名}
Password for user {マスターユーザ}:{パスワード}
{データベース名}=>
SQLコマンドを試す
ウインドウ関数を試行してみます。勉強中の「SQL実践入門（技術評論社）」から7章の一部引用させて頂きます。
- データベース名：demotron
- ユーザ名：dxpj
購入明細テーブル作成
CREATE TABLE Receipts
(cust_id CHAR(1) NOT NULL,
seq INTEGER NOT NULL,
price INTEGER NOT NULL,
PRIMARY KEY (cust_id, seq));
テーブル構造
demotron=> \d Receipts
Table "public.receipts"
Column | Type | Modifiers
---------+--------------+-----------
cust_id | character(1) | not null
seq | integer | not null
price | integer | not null
Indexes:
"receipts_pkey" PRIMARY KEY, btree (cust_id, seq)
demotron=>
データの中身
データは予め INSERT しておきます。
demotron=> SELECT * FROM Receipts;
cust_id | seq | price
---------+-----+-------
A | 1 | 500
A | 2 | 1000
A | 3 | 700
B | 5 | 100
B | 6 | 5000
B | 7 | 300
B | 9 | 200
B | 12 | 1000
C | 10 | 600
C | 20 | 100
C | 45 | 200
C | 70 | 50
D | 3 | 2000
(13 rows)
demotron=>
相関サブクエリでデータを取り出す
SELECT cust_id, seq, price
FROM Receipts R1
WHERE seq = (SELECT MIN(seq)
FROM Receipts R2
WHERE R1.cust_id = R2.cust_id);
cust_id | seq | price
---------+-----+-------
A | 1 | 500
B | 5 | 100
C | 10 | 600
D | 3 | 2000
(4 rows)
ウインドウ関数でデータを取り出す
SELECT cust_id, seq, price
FROM (SELECT cust_id, seq, price,
ROW_NUMBER()
OVER (PARTITION BY cust_id
ORDER BY seq) AS row_seq
FROM Receipts ) WORK
WHERE WORK.row_seq = 1;
cust_id | seq | price
---------+-----+-------
A | 1 | 500
B | 5 | 100
C | 10 | 600
D | 3 | 2000
(4 rows)
おまけ
PHP から PDO で PostgreSQL にアクセスする方法です。手軽なテスト用。Apache 等のWebサーバや PHP が設定されていることが前提です。
php-pgsql のインストール
$ sudo yum -y install php-pgsql
サンプルプログラム
subquery.php
<?php
try
{
$dsn='pgsql:{エンドポイント}';
$user='{ユーザ名}';
$password='{パスワード}';
$dbh=new PDO($dsn, $user, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql='
SELECT R1.cust_id, R1.seq, R1.price
FROM Receipts R1
INNER JOIN
(SELECT cust_id, MIN(seq) AS min_seq
FROM Receipts
GROUP BY cust_id) R2
ON R1.cust_id = R2.cust_id
AND R1.seq = R2.min_seq;
';
$stmt=$dbh->prepare($sql);
$stmt->execute();
$dbh=null;
while(true)
{
$rec=$stmt->fetch(PDO::FETCH_ASSOC);
if($rec==false)
{
break;
}
}
}
catch (Exception $e)
{
print 'データーベース接続エラー発生';
exit();
}
?>
参考
当社ではソフトウェア関連取組みの一環として、クラウド型パフォーマンスモニタサービス「New Relic」を提供しています
詳しくはこちら