Subscribed unsubscribe Subscribe Subscribe

at backyard

Color my life with the chaos of trouble.

sigur rósのライブのセットリストの推移をPythonを使って調べてみる、その3

Postico PostgreSQL re:dash Vagrant Ubuntu Mac

この前の記事で作成したsetlistのデータを用いて、re:dashでデータを可視化していこうという話。

shinshin86.hateblo.jp


今回やること

・re:dashをインストールしたVagrant環境上(Ubuntu Server)に入っているPostgresSQLにcsvデータをインポートさせる
なお、データはホストからもクライアントソフトで見れるようにする。
・DBとre:dashの接続設定を行う
・年ごとのライブでの演奏曲数の推移をre:dashを用いて表示させる。

環境

Mac OSX El Capitan環境上に構築した、Vagrant(Ubuntu Server trusty64)に
re:dashをインストールしている。
インストールした手順などについては先日の記事にて。

shinshin86.hateblo.jp


以下、作業記録。


参考にした記事などは記事の末尾にまとめて記載させてもらいます。

1.Vagrantfile を編集する

ホスト環境(Mac)からPostgresに接続するために下記の作業を行う。

Vagrant.configure("2") do |config|

  config.vm.network :forwarded_port, guest: 5432, host: 15432

  config.vm.network :private_network, ip: "192.168.33.10"

end
2.VM再起動。
    vagrant reload
3.Postgresの設定を下記のとおりに変更する。

3−1.設定変更

Vagrant環境にsshで入り、postgresql.confを開く。

    sudo vim /etc/postgresql/9.3/main/postgresql.conf

listen_addressesに*(アスタリスク)を付与し、コメントを外す。

    # listen_addresses = ''          # what IP address(es) to listen on;
    # 上のコメントアウト行を修正して、下記のようにする。
    listen_addresses = '*'          # what IP address(es) to listen on;

設定変更その2

    sudo vim /etc/postgresql/9.3/main/pg_hba.conf

次にpg_hbs.confに下記の一行を加える。

    host    all         all         0.0.0.0/0             trust

PostgresSQL再起動

    sudo service postgresql restart
4.Posticoインストール

一旦ここからはMac環境での作業。
GUIでDB操作するためにPosticoをインストールしてみることにする。
Mac用のモダンなPostgresSQLクライアントソフト、らしい。いいね!

f:id:shinshin86:20160827160343p:plain

Postico – a modern PostgreSQL client for the Mac

なお、試用版は制限があるとのことだが、特に今回使用する上で問題にはならなそう。

なお、無料Trialの制限内容としては、下記の通り。
なお、Trialの期間に制限はないとのこと。

1. 5つまで接続情報を保存できる。
2. 接続ごとに1つだけウィンドウを表示できる
3. 表フィルタは無効となっている

インストール方法は記事にする必要もないくらいに簡単だったので割愛します。
上に貼ったリンク先からDownloadして、後は使うだけ。
ちなみにアプリ自体の使い方も、DBのクライアントをなにかしら使ったことがある人であれば、
特に迷うこともなさそうだったので割愛します。

5.re:dash作業用にvagrantユーザをつくる。

再びVagrant環境にssh接続して作業。

次に、re:dashで作業する用のアカウントとして、vagrantユーザをつくる。
これはあくまでテスト用として。
※自分自身あまりDBの作業に慣れていなかったので、事細かくコマンド履歴を残しています。

まずは下記コマンドで接続し、postgresユーザのパスワードを設定する。

    sudo -u postgres psql	
    \password

一度psqlを出て、下記コマンドで入り直す。
(設定したパスワードがちゃんと設定されたかの確認も含む)

	psql -h localhost -U postgres

次にvagrantユーザを作成。

    create user vagrant;
	CREATE ROLE

パスワードを設定。

    \password vagrant(エンターキー)
    Enter new password:
    Enter it again:
6.データベース作成

テスト用のデータベースを作る。

    # create database testdb;
    CREATE DATABASE
7.テーブル作成&クライアントから接続

試しにMacからPosticoを使用して、GUI側からテーブルを作成してみる
Posticoを起動して接続情報を入力し、connectで接続。

接続設定は下記の通り。

f:id:shinshin86:20160827161342p:plain


DBに接続し、"+Table"ボタンを押下すると、
テーブル作成の画面に移行するので、こちらでテーブル作成。

f:id:shinshin86:20160827160132p:plain

8.CSVデータをインポート

再びVagrant環境。
CLIからDB接続する。

    psql -h localhost -U vagrant -d testdb

普段なら上のコマンドでpsql内に接続するが、
csvをインポートするにはsuperuserでコマンドを実施する必要が有るため、
今回postgresユーザで入る。

	psql -h localhost -U postgres -d testdb

前回作成したCSVファイルをvagrant側の作業ディレクトリ内に持ってきて、
そこでpsql内から下記コマンドを実施。

※ここには書いていなかったが、既にVagrantでのホスト・ゲスト間の共有フォルダ設定は済ませていたので、そちら経由でファイルは持ってきた。

    COPY setlist (year,title) FROM '/home/vagrant/setlist.csv' WITH CSV;

下記の通り、データが入ったのがPostico側からも確認できる。

f:id:shinshin86:20160827160745p:plain

testdb内のsetlistテーブルに対し、vagrantユーザにALL権限を付与。

    GRANT ALL ON setlist TO vagrant;
9.次はre:dash側でデータベースの登録。


DB登録はキャプチャ参照。

f:id:shinshin86:20160827162102p:plain


10.試しにQueryを投げてみる。

下記のQueryでSigur rosの年別のライブ演奏曲数が多い順に表示される。

SELECT year AS Year, count(*) AS Count FROM setlist GROUP BY year ORDER BY count DESC

f:id:shinshin86:20160827162220p:plain

それをグラフ化したもの。

f:id:shinshin86:20160827162258p:plain

グラフ化の作業はGUI上で感覚的にできるので、データの可視化がよく分からない人でも、
とりあえずなんとなく図を作ってみることは出来そう。

次回はFuji Rock 2016での演奏曲を予想してみる編です。

参考記事

参考にした記事、助けてもらった記事。

dany1468.hatenablog.com

stackoverflow.com

qiita.com

qiita.com