📊
MetabaseとPostgreSQLで始めるデータ分析入門

2024/02/06に公開
Docker
 はじめに最近、PostgreSQLを使ってデータ分析をするようになり、分析結果を手軽に可視化できるBIツールを探していました。要件は以下の通りです。
オープンソースであること
SQLの実行結果を簡単にグラフ化できること
PostgreSQLに対応していること
候補に上がったのは以下の3つです。一応、3つとも試してみました。
Redash
Superset
Metabase
Redashは、古くからあるBIツールで、ネット上にも多くの情報が出回っています。しかし、インストールや設定がかなり面倒でした。Supersetは、Apacheプロジェクトが管理しているBIツールです。インストールはRedashよりも簡単でしたが、やはり設定が面倒でした。Metabaseは、Meta社が開発したBIツールです。Dockerコマンド一発でインストールでき、管理画面のUIもよくできていました。この中では、Metabaseが断トツで使いやすかったため、Metabaseを選定しました。
Metabaseの運用開始から1か月ほど経過し、そろそろ環境構築手順を忘れそうになってきたので、備忘録代わりにこの記事を書きました。


運用中のMetabaseの画面

 環境構築データ分析基盤はデータベース(DB)とBIツールから成り立ちます。今回はデータベースにPostgreSQL、BIツールにMetabaseを使用し、どちらもDockerを用いて新規にインストールします。前提として、Dockerはインストールされているものとします。筆者は、MacとLinuxで動作確認を行いました。

 PostgreSQLのインストール分析用のDBとして、PostgreSQLをインストールします。作業用のディレクトリに移動し、以下のコマンドを実行してください。
mkdir -p postgresql/tmp
cd postgresql
docker run --shm-size=1g -d --name ps -e POSTGRES_DB=EXAMPLE -e POSTGRES_USER=user -e POSTGRES_PASSWORD=xxxxxxxx -p 5432:5432 -v $(pwd)/tmp:/tmp postgres:latest
最初に作成したpostgresql/tmpディレクトリは、PostgreSQLコンテナの/tmpにマウントされます。--shm-sizeオプションは、共有メモリ不足エラーを回避するために追加しています。データベース名(POSTGRES_DB)、ユーザ名(POSTGRES_USER)、パスワード(POSTGRES_PASSWORD)は、必要に応じて変更してください。
インストールが完了したら、docker psでコンテナが起動していることを確認してみましょう。次のように表示されればOKです。
% docker ps
CONTAINER ID   IMAGE             COMMAND                  CREATED          STATUS          PORTS                    NAMES
765df744e31f   postgres:latest   "docker-entrypoint.s…"   24 seconds ago   Up 23 seconds   0.0.0.0:5432->5432/tcp   ps
コンテナの停止は、次のコマンドで行います。
docker stop ps
コンテナの起動は、次のコマンドで行います。
docker start ps
コンテナ内部のpsqlコマンドでPostgreSQLに接続するには、次のコマンドを実行します。
docker exec -it ps psql -U user EXAMPLE
外部のpsqlコマンドでPostgreSQLに接続するには、次のコマンドを実行します。
psql -h {サーバーのIPアドレス} -U user EXAMPLE

 データの投入次に分析対象のデータをDBに投入しましょう。ここではネット上に公開されているデータを用います。私のおすすめ本のひとつ「10年戦えるデータ分析入門」のサポートページにアクセスしてください。ここからサンプルデータをダウンロードできます。「サンプルデータのダウンロード（サイズ大、Zipファイル 45MB）」というリンクをクリックして、ダウンロードしてください。余談ですがこの本は本当におすすめで、実際に発行から10年近く経ってもまだ戦えています。
ダウンロードしたファイルを解凍し、中にあるsrcフォルダごと、postgresql/tmpディレクトリにコピーしてください。次に、psqlでデータベースに接続し、次のコマンドを実行してください。
drop table if exists customers;
\i /tmp/src/customers.ct;
copy customers from '/tmp/src/customers.csv' with (format csv, header true, null '');
これは、customersテーブルを作成し、customers.csvファイルのデータをインポートするコマンドです。ダウンロードファイルに含まれるload.sqlを参考にしていますが、copyコマンドの形式が古かったので、少し変えています。実行例は以下の通りです。
% docker exec -it ps psql -U user EXAMPLE
psql (16.1 (Debian 16.1-1.pgdg120+1))
Type "help" for help.

EXAMPLE=# drop table if exists customers;
NOTICE:  table "customers" does not exist, skipping
DROP TABLE
EXAMPLE=# \i /tmp/src/customers.ct;
CREATE TABLE
EXAMPLE=# copy customers from '/tmp/src/customers.csv' with (format csv, header true, null '');
COPY 62662
同様に、shops、items、web_pages、orders、order_details、access_logテーブルも作成し、データをインポートしましょう。
drop table if exists shops;
\i /tmp/src/shops.ct;
copy shops from '/tmp/src/shops.csv' with (format csv, header true, null '');

drop table if exists items;
\i /tmp/src/items.ct;
copy items from '/tmp/src/items.csv' with (format csv, header true, null '');

drop table if exists web_pages;
\i /tmp/src/web_pages.ct;
copy web_pages from '/tmp/src/web_pages.csv' with (format csv, header true, null '');

drop table if exists orders;
\i /tmp/src/orders.ct;
copy orders from '/tmp/src/orders.csv' with (format csv, header true, null '');

drop table if exists order_details;
\i /tmp/src/order_details.ct;
copy order_details from '/tmp/src/order_details.csv' with (format csv, header true, null '');

drop table if exists access_log;
\i /tmp/src/access_log.ct;
copy access_log from '/tmp/src/access_log.csv' with (format csv, header true, null '');
これで、データの投入は完了です。

 MetabaseのインストールMetabaseをインストールするには、次のコマンドを実行します。
docker run -d -p 3000:3000 --name metabase metabase/metabase
コンテナの停止は、次のコマンドで行います。
docker stop metabase
コンテナの起動は、次のコマンドで行います。
docker start metabase

 Metabaseの初期設定Metabaseコンテナを起動した状態で、ブラウザからhttp://localhost:3000/setupにアクセスしてください。
「開始しましょう」をクリックしてください。
好みの言語を選択して、「次へ」をクリックしてください。
名前、メールアドレス、パスワード等を入力して、「次へ」をクリックしてください。
「PostgreSQL」を選択して、「次へ」をクリックしてください。
PostgreSQLの接続情報を入力して、「データベースを接続する」をクリックしてください。このとき、ホストにlocalhostを設定してしまうと接続できないので、ご注意ください。Metabaseコンテナから見たらPostgreSQLコンテナはまったく別の仮想環境なので、MetabaseのローカルホストにPostgreSQLは存在しないのです。ifconfigコマンドやip aコマンドなどで、PostgreSQLのコンテナにアクセスできるIPアドレスを調べてからホストに設定してください。
「Metabaseが匿名の情報収集をすることに同意する」は、お好みで選択し、「フィニッシュ」をクリックしてください。
最後に「Metabaseを使い始める」をクリックしてください。
これで、Metabaseの初期設定は完了です。次からは、http://localhost:3000/にアクセスすれば、Metabaseを利用できます。

 分析してみようそれでは、実際にデータ分析をしてみましょう。
右上の「+ New」をクリックし、「SQLクエリ」を選択してください。
先ほど接続設定したデータベースを選択してください。これで、任意のSQLクエリを実行できるようになります。次のSQLクエリをコピーして、実行してみましょう。
with m as (
    select
        shop_id
        , cast(date_trunc('month', order_time) as date) as sales_month
        , sum(order_amount) as sales_amount
    from
        orders
    group by
        shop_id, sales_month
)
select 
    s.shop_name
    , m.sales_month
    , m.sales_amount
from
    m
inner join 
    shops as s 
on 
    m.shop_id = s.shop_id
order by
    s.shop_id, m.sales_month
;
これは、各店舗の月次売上を表示するクエリです。「10年戦えるデータ分析入門」の164ページに説明が載っているので、詳しくはそちらをご覧ください。ここではwith句を用いて、本のサンプルよりもネストを浅くしています。実行結果は以下の通りです。
次は、この結果をグラフ化してみましょう。左下の「ビジュアライゼーション」をクリックして、棒グラフを選択してください。
次は、この分析結果をいつでも呼び出せるように、保存しておきましょう。
右上の「保存」をクリックしてください。
Metabaseでは、分析のことをなぜか「質問」と表現しています。英語版ではquestionです。はじめは戸惑うかもしれませんが、すぐに慣れます。適当な名前を入力して、「保存」をクリックしてください。ダッシュボードに追加するかどうかを聞かれるので、お好みで選択してください。
サイドバーの「分析」をクリックすると、先ほど保存した質問が表示されます。クリックすると分析結果が表示されます。

 演習問題せっかくなので、演習問題を出してみます。どの問題も、上記のSQLを少し修正すれば解けますよ。
特定の顧客の売上集計を折れ線グラフで表示してください。
2013年1月の売上ランキングを表示してください。


問題1の回答例


問題2の回答例

 おわりにMetabaseとPostgreSQLを用いた、データ分析の環境構築手順をご紹介しました。MetabaseのSQLエディターは自動補完が効くので、効率よくSQLを書けます。入門書のSQLを写経するのにも最適ですね。また、手軽にデータをグラフ化できるので、分析結果を他人に見せる場合の説得力が大幅にアップします。
この記事では省略しましたが、Tableauのように、データを直感的に操作してグラフ化する機能もあります。また、データの更新をトリガーにして、自動で分析する機能もあります。Metabaseは、オープンソースでありながら、非常に高機能なBIツールです。ぜひ、お試しください。