pgwebでDVDレンタルの謎を解く

<font color="Grey">*[Solve a DVD rental mystery with pgweb](https://aiven.io/blog/pgweb-dvd-mystery)の翻訳です。*</font>

2022年9月8日

# pgwebでDVDレンタルの謎を解く

コマンドラインツールやSQLを使うのは気が引けるかもしれません。pgwebのGUIを使ってPostgreSQL®のデータを便利に見る方法を学びましょう。

なぜ人間はソフトウェアを作るのでしょうか？その答えは、データを移動させるためです。アプリケーションはデータを処理しますが、データが存在するのはアプリケーションだけではありません。そして、そのデータを処理し、可視化するとなると、PostgreSQL®やコマンドラインツールは素晴らしいかもしれませんが、大変なものでもあります。このブログでは、データベースのグラフィカルユーザインタフェース(GUI)ツールの必要性について述べ、DVDの謎を解くためのオープンソースツールである[pgweb](https://sosedoff.github.io/pgweb/)について説明します。


### GUI - アプリケーションのためだけではない


データベースのGUIツールはコマンドラインツールの代わりにはなりません。しかし、GUI ツールを使えば、アプリケーションやデータベースに素早く簡単に接続することができます。ここでは、データベースGUIツールがコマンドラインオプションを補完する3つのユースケースを紹介します：


1.学習曲線を削減する：データベースへの接続であれ、テーブルに対するクエリの実行であれ、GUIツールはタスクを実行するための直感的な方法を示します。例えば、コマンドラインを使ってPostgreSQLデータベースに接続する方法を以下に示します：



```
`psql 'postgres://avnadmin:PASSWORD@demo-pg-dev-advocates.aivencloud.com:13039/defaultdb?sslmode=require'`クリップボードにコピーする
```

新しいユーザーは、このコマンドのすべての情報をどのように組み立てるのか、引用符を使うのか使わないのかがわからないかもしれない。


以下はGUIツールの例である：


![pgweb connection page](https://cdn.sanity.io/images/sczeoy4w/production/352048832db48231b633fc0de4e31c22a35f9202-1228x1056.png?q=80&fit=min&auto=format&dpr=1.5)


ユーザは、ホスト、ポート、ユーザ名、パスワード、データベース名を入力し、**接続**をクリックします。
2.生産性の向上：複数のデータベースを切り替えたり、テーブルを見たりするような日常的な作業であれば、コマンドを実行するよりも、マウスをクリックする方が早い。GUIツールは、様々なデータベース、テーブル、スキーマをフォルダやファイルの階層として表示し、より迅速なナビゲーションを可能にする。
3.追加オプション：これらの機能はツールによって異なるが、データベースGUIツールの中には、全文検索、クエリのパフォーマンス・チューニング、SQLコマンドの完全な履歴などが可能なものもある。


それはさておき、チームとして解決しなければならないDVDの謎に集中しよう。Crab Rentalは、Netflixがまだ存在しないパラレルワールドに存在する架空のDVDレンタル店だ。カニレンタルは、在庫とITセキュリティに関するいくつかの課題に直面している。経営陣はあなたを雇い、これらの課題を課した：


1.システムの不具合により、DVDを返却しない顧客がいる。不具合を見つけよう。
2.顧客満足度が下がっている。フィルム在庫は顧客の需要を反映しているか？
3.多くの人がカニレンタルのデータベースにアクセスしている。このデータベースへのリクエストの詳細を知る方法を考えよう。


### Pgweb - PostgreSQL用のクロスプラットフォームのオープンソースツール


管理者はSQLを知らないので、ブラウザ上で実行可能なデータへのローコードアクセスを提供するために[pgweb](https://sosedoff.github.io/pgweb/)を選択します。PgwebはGoLangで書かれたフリーでオープンソースのツールです。


PostgreSQLサーバーを用意していない場合は、[Aivenにサインアップ](https://console.aiven.io/signup)して、利用可能な無料クレジットを使ってサーバーをデプロイすることができます。


[インストール手順](https://github.com/sosedoff/pgweb#installation)に従って、pgwebをあなたのマシンにインストールしてください。



> ![](/build/_assets/shape-quote-WGSAHX4E.svg)
> このブログではpgwebのバージョン0.11.11を使用しています。将来のバージョンではいくつかの機能や外観が異なるかもしれません。
>
>
>


#### データベースへの接続


pgwebサーバを起動し、PostgreSQLサーバに接続するには複数の方法があります。以下のいずれかの方法でサーバを起動することができます：


1.サーバを起動し、データベース接続情報を手動で入力する。



```
pgweb`クリップボードにコピーする
```
2.接続フラグを指定してサーバを起動する：



```
pgweb --host $HOSTNAME --user $USERNAME --db $DBNAME`クリップボードにコピーする
```
3.接続 URI を使用してサーバーを起動する：



```
`pgweb --url postgres://$USERNAME:$PASSWORD@$HOSTNAME:$PORTNUM/$DBNAME?sslmode=[mode]`クリップボードにコピーします。
```


[Aiven for PostgreSQL](https://aiven.io/postgresql)を使用している場合、接続情報は**Overview**タブの下にあります。


![Aiven for Postgres connection page](https://cdn.sanity.io/images/sczeoy4w/production/dcceb4fc18331235da0b1438940783e74f56f88c-1786x1422.png?q=80&fit=min&auto=format&dpr=1.5)



> ![](/build/_assets/shape-quote-WGSAHX4E.svg)
> このブログではPostgreSQLのバージョン14.4を使用しています。
>
>
>


pgweb サーバーが起動したら、`http://localhost:8081` に移動してツールを操作してください。


#### サンプルデータのロード


Pagilaは[Sakila Sample Database](https://dev.mysql.com/doc/sakila/en/)をPostgreSQLに移植したものです。[このガイド](https://developer.aiven.io/docs/products/postgresql/howto/pagila.html)に従ってPagilaをPostgreSQLデータベースにロードしてください。


pgwebをテストしてみましょう。Pagilaデータベースに接続した状態で、pgwebの**クエリ**タブをクリックし、以下のクエリを実行してください：



```
select
 customer.first_name、
 customer.last_name、
 count(customer.first_name) rentals_count
から
 顧客
left join rental on
 customer.customer_id = rental.customer_id
group by
 customer.first_name、
 customer.last_name
order by rentals_count desc;`クリップボードにコピー
```

最もレンタルされたDVDを持っているのは誰ですか？これがELEANOR HUNTなら、データベースは正しくロードされている。


### pgwebを使ってDVDの謎を解く


#### 顧客がDVDを返さない


あなたの最初の仕事は、DVDを返却しない顧客がいる理由を調査することである。あなたはすぐに、何人かの顧客がレンタルの返却日を知らないことがわかりました。


これは、**クエリ**タブをクリックして、次のクエリを実行することでわかります：



```
選択
 rental.rental_id, rental.customer_id, rental.rental_date, customer.email
 FROM レンタル
 left join customer on
 customer.customer_id = rental.customer_id
 WHERE return_date IS NULL;`クリップボードにコピー
```

これらの顧客はすべて、`rental.return_date`として**NULL**値を持ちます。pgwebを使用して、顧客のリストと詳細をJSON、CSV、またはXMLとしてエクスポートできます。この情報をIT部門に送信して、これらの顧客にレンタルを返却するようにメールできるようにしましょう。


![レンタル返却日のない顧客のクエリ](https://cdn.sanity.io/images/sczeoy4w/production/479a083486108dee85ea2c32a27c0b6560f22de6-3078x1430.png?q=80&fit=min&auto=format&dpr=1.5)


経営陣がSQLクエリを書いたり実行したりする技術的な詳細を気にすることなく、カニレンタルのビジネスパフォーマンスを監視できるように、**ビュー**を作成してみましょう。SQLでは、ビューはSQL文の結果セットに基づいた仮想テーブルです。ビューを作成するには、`CREATE VIEW...`を前につけて同じSQL文を実行する：



```
CREATE VIEW customers_no_return_rental_date AS
select
 rental.rental_id, rental.customer_id, rental.rental_date, customer.email
 FROM レンタル
 left join customer on
 customer.customer_id = rental.customer_id
 WHERE return_date IS NULL;`クリップボードにコピー
```

この*ビュー*を見るために、pgwebダッシュボードにログオンし、**Views**ドロップダウンを展開し、同じ情報を見るために**customers_no_return_retental_date**をクリックしてみましょう。


![レンタル返却日がない顧客の表示](https://cdn.sanity.io/images/sczeoy4w/production/b5788161363d17cec51b2ecbd57e0b571ce35067-3802x1414.png?q=80&fit=min&auto=format&dpr=1.5)


最初の謎が解け、カニレンタルは延滞料金という新たな収入源を手に入れた！


#### 映画の在庫が需要を反映している（あるいは反映していない）。


新たに自信を得たので、2つ目の謎を解くことに飛び込もう。このタスクのために、2つのビューを利用しよう。1つは、最も売上げの多い映画のカテゴリー（需要を示す）、もう1つはカテゴリーに基づく在庫数である。最初のビューである **sales_by_film_category** は、サンプル・データベースに既に存在する。Query**タブで以下のSQL文を実行して、**inventory_by_film_category** ビューを生成する：



```
CREATE VIEW inventory_by_film_category AS
選択
 category.name、
 inventory_count
から
 カテゴリ
left join film_category on
 category.category_id = film_category.category_id
左join inventory on
 film_category.film_id = inventory.film_id
グループ
 カテゴリー名
order by
 inventory_count desc;`クリップボードにコピー
```

これで管理者は、ビューをクリックすることで、映画カテゴリー別の売上と、在庫数を確認できるようになった：


![映画カテゴリー別売上高](https://cdn.sanity.io/images/sczeoy4w/production/eecdd58b5cfe46129c41d00c29bef0e3262e330d-2852x990.png?q=80&fit=min&auto=format&dpr=1.5)


![映画カテゴリー別在庫](https://cdn.sanity.io/images/sczeoy4w/production/0e89684238fd199e2255f5ae3b8ef22e3cfce2e5-3390x936.png?q=80&fit=min&auto=format&dpr=1.5)


上記の情報に基づき、ドラマとコメディ映画をもっと注文するよう経営陣に提言してみよう。これらのビューは専門知識がなくてもいつでもモニターできるため、カニレンタルの経営陣はデータアナリストを必要とせずに在庫のバランスを調整することができる。


#### カニレンタルのデータベースへのアクセスを監査する。


Crab Rental はデータプラットフォーム全体のセキュリティ監査を実施するために外部の会社を雇った。Crab RentalはPostgreSQLデータベースを使用しており、多くの従業員がデータベースの認証情報にアクセスできる。PostgreSQLデータベースが正常であり、不必要なアクセスがないことを確認しよう。PostreSQLには組み込みの監査機能があり、pgwebにはこれらにアクセスする便利な方法があります。SQLコマンドの履歴を使って、誰がデータベースにアクセスしてきたかを確認してみましょう。履歴**タブから、以下のようなサンプルクエリリストを見ることができます：


![Query history](https://cdn.sanity.io/images/sczeoy4w/production/1f79c09c38cb538ac9122a8a4879225abf84f162-2824x1140.png?q=80&fit=min&auto=format&dpr=1.5)


タイムスタンプに基づき、**Activity** タブからアクティビティの詳細を確認することができ、ユーザ名、アプリケーショ ン/クライアント名、クライアントIP/ポート、その他監査時に重要となる詳細が表示されます。


### まとめ


我々の集団調査のおかげで、カニレンタルはDVDレンタル業界の崩壊を少なくとももう少し生き延びることができるだろう。これは架空の例ではあるが、多くの企業がこのようなテクニックを使って、データに基づいた賢い意思決定を行っている。このブログから3つの収穫があった：


1.データベースのGUIツールは、コマンドラインツールの代替ではなく、むしろ補完である。
2.GUIツールは生産性を向上させ、学習曲線を短縮するのに役立ち、全文検索、パフォーマンスチューニングなどの他の機能を提供することができる。
3.Pgwebは、データベース用GUIツールの選択肢の一つに過ぎません。あなたのユースケースに合ったツールを選んでください。


PostgreSQLのフルマネージド・ホスティングをお探しでしたら、[Aiven for PostgreSQL®](https://aiven.io/postgresql)と[リーチアウト](https://twitter.com/aiven_io)をご覧ください。