📊

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

2024/02/06に公開

Docker

はじめに

最近、PostgreSQLを使ってデータ分析をするようになり、分析結果を手軽に可視化できるBIツールを探していました。要件は以下の通りです。

オープンソースであること
SQLの実行結果を簡単にグラフ化できること
PostgreSQLに対応していること

候補に上がったのは以下の3つです。一応、3つとも試してみました。

Redashは、古くからあるBIツールで、ネット上にも多くの情報が出回っています。しかし、インストールや設定がかなり面倒でした。Supersetは、Apacheプロジェクトが管理しているBIツールです。インストールはRedashよりも簡単でしたが、やはり設定が面倒でした。Metabaseは、Meta社が開発したBIツールです。Dockerコマンド一発でインストールでき、管理画面のUIもよくできていました。この中では、Metabaseが断トツで使いやすかったため、Metabaseを選定しました。

Metabaseの運用開始から1か月ほど経過し、そろそろ環境構築手順を忘れそうになってきたので、備忘録代わりにこの記事を書きました。

運用中の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オプションは、共有メモリ不足エラーを回避するために追加しています。データベース名、ユーザ名、パスワードは、必要に応じて変更してください。

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

データの投入

「10年戦えるデータ分析入門」という本があります。SQLを使ったデータ分析手法を学ぶのに、ちょうどよい構成になっていて、私のおすすめです。この本のサポートページから、サンプルデータをダウンロードできるので、今回はこれをPostgreSQLに投入してみましょう。「サンプルデータのダウンロード（サイズ大、Zipファイル 45MB）」というリンクをクリックして、ダウンロードしてください。

サンプルデータのダウンロード

ダウンロードしたファイルを解凍し、中にある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にアクセスしてください。

Metabaseの初期設定

「開始しましょう」をクリックしてください。

言語設定

好みの言語を選択して、「次へ」をクリックしてください。

ユーザー登録

名前、メールアドレス、パスワード等を入力して、「次へ」をクリックしてください。

データベースの選択

「PostgreSQL」を選択して、「次へ」をクリックしてください。

データベースの接続設定

PostgreSQLの接続情報を入力して、「データベースを接続する」をクリックしてください。このとき、ホストにlocalhostを設定してしまうと接続できないので、ご注意ください。Metabaseコンテナのローカルホストと、PostgreSQLコンテナのローカルホストは、別の仮想環境にあるためです。ifconfigコマンドやip aコマンドなどで、PostgreSQLのコンテナにアクセスできるIPアドレスを調べて指定してください。

「Metabaseが匿名の情報収集をすることに同意する」は、お好みで選択し、「フィニッシュ」をクリックしてください。

最後に「Metabaseを使い始める」をクリックしてください。

ホーム画面

これで、Metabaseの初期設定は完了です。次からは、http://localhost:3000/にアクセスすれば、Metabaseを利用できます。

分析してみよう

それでは、実際にデータ分析をしてみましょう。

SQLクエリ

右上の「+ 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
;