Solve a DVD rental mystery with pgwebの翻訳です。
2022年9月8日
pgwebでDVDレンタルの謎を解く
コマンドラインツールやSQLを使うのは気が引けるかもしれません。pgwebのGUIを使ってPostgreSQL®のデータを便利に見る方法を学びましょう。
なぜ人間はソフトウェアを作るのでしょうか？その答えは、データを移動させるためです。アプリケーションはデータを処理しますが、データが存在するのはアプリケーションだけではありません。そして、そのデータを処理し、可視化するとなると、PostgreSQL®やコマンドラインツールは素晴らしいかもしれませんが、大変なものでもあります。このブログでは、データベースのグラフィカルユーザインタフェース(GUI)ツールの必要性について述べ、DVDの謎を解くためのオープンソースツールであるpgwebについて説明します。
GUI - アプリケーションのためだけではない
データベースのGUIツールはコマンドラインツールの代わりにはなりません。しかし、GUI ツールを使えば、アプリケーションやデータベースに素早く簡単に接続することができます。ここでは、データベースGUIツールがコマンドラインオプションを補完する3つのユースケースを紹介します：
1.学習曲線を削減する：データベースへの接続であれ、テーブルに対するクエリの実行であれ、GUIツールはタスクを実行するための直感的な方法を示します。例えば、コマンドラインを使ってPostgreSQLデータベースに接続する方法を以下に示します：
`psql 'postgres://avnadmin:PASSWORD@demo-pg-dev-advocates.aivencloud.com:13039/defaultdb?sslmode=require'`クリップボードにコピーする
新しいユーザーは、このコマンドのすべての情報をどのように組み立てるのか、引用符を使うのか使わないのかがわからないかもしれない。
以下はGUIツールの例である：
ユーザは、ホスト、ポート、ユーザ名、パスワード、データベース名を入力し、接続をクリックします。
2.生産性の向上：複数のデータベースを切り替えたり、テーブルを見たりするような日常的な作業であれば、コマンドを実行するよりも、マウスをクリックする方が早い。GUIツールは、様々なデータベース、テーブル、スキーマをフォルダやファイルの階層として表示し、より迅速なナビゲーションを可能にする。
3.追加オプション：これらの機能はツールによって異なるが、データベースGUIツールの中には、全文検索、クエリのパフォーマンス・チューニング、SQLコマンドの完全な履歴などが可能なものもある。
それはさておき、チームとして解決しなければならないDVDの謎に集中しよう。Crab Rentalは、Netflixがまだ存在しないパラレルワールドに存在する架空のDVDレンタル店だ。カニレンタルは、在庫とITセキュリティに関するいくつかの課題に直面している。経営陣はあなたを雇い、これらの課題を課した：
1.システムの不具合により、DVDを返却しない顧客がいる。不具合を見つけよう。
2.顧客満足度が下がっている。フィルム在庫は顧客の需要を反映しているか？
3.多くの人がカニレンタルのデータベースにアクセスしている。このデータベースへのリクエストの詳細を知る方法を考えよう。
Pgweb - PostgreSQL用のクロスプラットフォームのオープンソースツール
管理者はSQLを知らないので、ブラウザ上で実行可能なデータへのローコードアクセスを提供するためにpgwebを選択します。PgwebはGoLangで書かれたフリーでオープンソースのツールです。
PostgreSQLサーバーを用意していない場合は、Aivenにサインアップして、利用可能な無料クレジットを使ってサーバーをデプロイすることができます。
インストール手順に従って、pgwebをあなたのマシンにインストールしてください。
このブログでは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を使用している場合、接続情報はOverviewタブの下にあります。
このブログではPostgreSQLのバージョン14.4を使用しています。
pgweb サーバーが起動したら、http://localhost:8081 に移動してツールを操作してください。
サンプルデータのロード
PagilaはSakila Sample DatabaseをPostgreSQLに移植したものです。このガイドに従って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部門に送信して、これらの顧客にレンタルを返却するようにメールできるようにしましょう。
経営陣が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をクリックしてみましょう。
最初の謎が解け、カニレンタルは延滞料金という新たな収入源を手に入れた！
映画の在庫が需要を反映している（あるいは反映していない）。
新たに自信を得たので、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;`クリップボードにコピー
これで管理者は、ビューをクリックすることで、映画カテゴリー別の売上と、在庫数を確認できるようになった：
上記の情報に基づき、ドラマとコメディ映画をもっと注文するよう経営陣に提言してみよう。これらのビューは専門知識がなくてもいつでもモニターできるため、カニレンタルの経営陣はデータアナリストを必要とせずに在庫のバランスを調整することができる。
カニレンタルのデータベースへのアクセスを監査する。
Crab Rental はデータプラットフォーム全体のセキュリティ監査を実施するために外部の会社を雇った。Crab RentalはPostgreSQLデータベースを使用しており、多くの従業員がデータベースの認証情報にアクセスできる。PostgreSQLデータベースが正常であり、不必要なアクセスがないことを確認しよう。PostreSQLには組み込みの監査機能があり、pgwebにはこれらにアクセスする便利な方法があります。SQLコマンドの履歴を使って、誰がデータベースにアクセスしてきたかを確認してみましょう。履歴**タブから、以下のようなサンプルクエリリストを見ることができます：
タイムスタンプに基づき、Activity タブからアクティビティの詳細を確認することができ、ユーザ名、アプリケーショ ン/クライアント名、クライアントIP/ポート、その他監査時に重要となる詳細が表示されます。
まとめ
我々の集団調査のおかげで、カニレンタルはDVDレンタル業界の崩壊を少なくとももう少し生き延びることができるだろう。これは架空の例ではあるが、多くの企業がこのようなテクニックを使って、データに基づいた賢い意思決定を行っている。このブログから3つの収穫があった：
1.データベースのGUIツールは、コマンドラインツールの代替ではなく、むしろ補完である。
2.GUIツールは生産性を向上させ、学習曲線を短縮するのに役立ち、全文検索、パフォーマンスチューニングなどの他の機能を提供することができる。
3.Pgwebは、データベース用GUIツールの選択肢の一つに過ぎません。あなたのユースケースに合ったツールを選んでください。
PostgreSQLのフルマネージド・ホスティングをお探しでしたら、Aiven for PostgreSQL®とリーチアウトをご覧ください。