＜月刊＞S3+DuckDB+GrafanaでQiita記事メトリクスのダッシュボードをつくる vol.3 データのクエリと可視化【完結】

はじめに

「S3+DuckDB+GrafanaでQiita記事メトリクスのダッシュボードをつくる」シリーズ第3回です。

前回までの記事では、Qiita APIから記事のメトリクスを収集してS3バケットに保管するスクリプトの作成から、Grafanaインスタンスの立ち上げとDuckDBプラグインの導入まで行いました。

今回はいよいよクライマックス。DuckDBのクエリを書いてGrafana上でダッシュボードを作成し、収集したデータを美しく可視化していきます。

百聞は一見に如かず。まずは完成品をご覧ください。

クエリの作成にはAmazon Q(執筆中にKiroへと名称変更されました)に支援してもらいました。設定ファイルやスクリプトの一部もAmazon Qと協力して作成しています。

これまでの記事はこちら。

おさらい

記事のメトリクスがどのように保管されているかなど、前回の記事から環境の情報をおさらいしておきます。

データ関連

S3バケットarticle-metrics-bucketに、以下の通りメトリクスデータが格納されている前提で話を進めます。

ファイルの種類	内容	保存先パス
記事マスター情報	記事のID、タイトル及び投稿者のIDなど	/articles/master.{csv,parquet}
いいね詳細	記事ごとのいいねの時系列情報	/articles/likers.{csv,parquet}
ストック詳細	記事ごとのストック数	/articles/stockers.{csv,parquet}
メトリクス(最新値)	記事ごとのメトリクス	/articles/metrics_latest.{csv,parquet}
メトリクス(時系列)	記事ごとのメトリクス	/metrics/year=YYYY/month=MM/day=DD/metrics_yyyymmdd_hhmmss.{csv,parquet}

列名も前回の記事に従っています。詳しくはこちらをご覧ください。

主要なものだけ抜粋しておきます。

記事マスター情報

列名	内容
user_id	投稿者のID
article_id	記事のID
title	記事タイトル
url	記事URL
created_at	記事投稿日時

メトリクス(最新値/時系列)

列名	内容
user_id	投稿者のID
article_id	記事のID
views_count	閲覧数
likes_count	いいね数
stocks_count	ストック数
measured_at	メトリクス収集日時
article_created_at	記事投稿日時

Grafana関連

GrafanaはEnterprise版のバージョン12.0.6を使用します。

Grafanaのバージョンによって、設定項目の名前や場所が大きく違うことがあるのでご注意ください。

可視化のための準備

まずは、可視化の前の準備として、データソースを設定し、ダッシュボードを作成します。

本記事ではGrafanaの利用経験がある程度あることを前提として、一部の操作方法の説明は割愛します。

データソースの追加

Grafanaにログインし、DuckDBをデータソースとして登録していきます。

左側のメニューから「Connections」→「Data Sources」を選択し、右上の「Add new data source」をクリックします。検索バーに「duck」と入力すると、前回インストールしたDuckDBプラグインが表示されるはずです。

表示されたプラグインをクリックすると、設定画面に遷移します。

「Name」はデフォルト値か任意の値を設定します。ここでは、Qiita Metricsと設定することにします。「Init SQL」には、以下の通りクエリを入力します。

INSTALL httpfs;
LOAD httpfs;
CREATE OR REPLACE secret (
    TYPE s3,
    PROVIDER credential_chain
);

それ以外の値は、空のままで「Save & Test」をクリックします。

以下のように「Data source is working」と表示されたら、正常に設定できています。

Init SQLで設定している内容について簡単に解説します。

credential_chainプロバイダーは、AWS SDKの認証情報取得機能を使用しています。DuckDBがS3にアクセスするには認証情報が必要ですが、今回はEC2インスタンスに割り当てたIAMロールのインスタンスプロファイルから自動的に認証情報を取得するようになっています。前回の記事でインスタンスプロファイル用のIAMロールにS3のアクセス権限を設定したのはこのためでした。

詳細はドキュメントもお読みください。

それでは、この後はクエリを書いていきましょう。

ダッシュボードの作成

左側のメニューから「Dashboards」をクリックします。

ダッシュボードの一覧画面が表示されるので、右上の「New」をクリックし、「New dashboard」を選択します。その後、「Add Visualization」をクリックします。

データソースの選択画面が表示されるので、先ほど登録したQiita Metricsを選択します。

以上で、クエリを書く準備は完了です。

可視化しよう

それでは可視化に向けてDuckDBのクエリを書いていきましょう。
ダッシュボードには表示したいものをVisualizationとして1つずつ追加していきます。

総記事数

「総記事数」のVisualizationには、Statを使います。記事マスター情報からクエリします。

SELECT
    COUNT(*)::INTEGER as total_articles
FROM 's3://article-metrics-bucket/articles/master.parquet'

クエリといっても単純にカウントするだけです。

Visualizationの設定で、Unitに「件」を指定しておきました。

今月の投稿数

「今月の投稿数」のVisualizationには、Statを使います。記事マスター情報からクエリします。

SET TimeZone = 'Asia/Tokyo';
SELECT
    COUNT(*)::INTEGER as articles_this_month
FROM 's3://article-metrics-bucket/articles/master.parquet'
WHERE DATE_TRUNC('month', created_at::DATE) = DATE_TRUNC('month', CURRENT_TIMESTAMP);

DATE_TRUNC関数で投稿日時と現在時刻の「月」をそれぞれ抽出して比較している点がミソです。WHERE句の条件を調整すれば、先月や特定の月の投稿数も簡単に集計できますね。

タイムゾーンを明示しているのもポイントで、これがないとUTCで集計されてしまうことがあります。これは月を跨ぐまで気づかなかった点でした。

Unitに「件」を指定しておきました。Thresholdsを設定しておき、まだその月の投稿がされていない場合は赤くなるようにしておくとおもしろいでしょう。

著者別記事数

「著者別記事数」のVisualizationには、Bar chartを使います。記事マスター情報からクエリします。

SELECT
    user_id::VARCHAR as 著者,
    COUNT(*)::INTEGER as 記事数
FROM 's3://article-metrics-bucket/articles/master.parquet'
GROUP BY 著者
ORDER BY 記事数 DESC;

著者のユーザーIDごとに記事数をカウントし、多い順に並べています。

総閲覧数・いいね数・ストック数

「総閲覧数」、「総いいね数」、「総ストック数」のVisualizationには、Statを使います。最新のメトリクスデータからクエリします。
最新のメトリクスは、metrics_latest.parquetに常に最新値が上書き保存されているため、このファイルを参照します。

SELECT
    SUM(views_count)::INTEGER as views
FROM 's3://article-metrics-bucket/articles/metrics_latest.parquet'

記事ごとに最新値が記録されているので、全記事の合計を計算します。
ここでは、総閲覧数のクエリを示しています。いいねの場合はSUM関数の引数をlikes_countに、ストック数の場合はstocks_countに置き換えてください。

実際の結果は恥ずかしいのでぼかしを入れておきます。

時系列のデータだけだと最新値を取得するのに余計な処理が必要になるため、最新値だけ別ファイルで更新する設計としました。

著者別閲覧数・いいね数・ストック数

「著者別閲覧数」、「著者別いいね数」、「緒差別ストック数」のVisualizationには、Bar chartを使います。最新のメトリクスデータからクエリします。

SELECT
    user_id as 著者,
    SUM(views_count)::INTEGER as 閲覧数
FROM 's3://article-metrics-bucket/articles/metrics_latest.parquet'
GROUP BY user_id
ORDER BY 閲覧数 DESC;

著者のユーザーIDごとに合計して、多い順に並べています。
いいね数、ストック数をクエリするには、先ほどと同様にSUM関数の引数を置き換えてください。

月別投稿数

「月別投稿数」のVisualizationには、Bar gaugeを使ってみました。記事マスター情報からクエリします。

SET TimeZone = 'Asia/Tokyo';
WITH RECURSIVE date_series AS (
    -- 開始月を取得
    SELECT DATE_TRUNC('month', MIN(created_at::TIMESTAMP)) as month_date
    FROM read_parquet('s3://article-metrics-bucket/articles/master.parquet')

    UNION ALL

    -- 月を1つずつ増やして終了月まで
    SELECT month_date + INTERVAL '1 month'
    FROM date_series
    WHERE month_date + INTERVAL '1 month' <= DATE_TRUNC('month', CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo') 
),
monthly_posts AS (
    SELECT
        DATE_TRUNC('month', created_at::TIMESTAMP) as month,
        COUNT(*) as posts_count
    FROM read_parquet('s3://article-metrics-bucket/articles/master.parquet')
    GROUP BY DATE_TRUNC('month', created_at::TIMESTAMP)
)
SELECT
    ds.month_date as time,
    COALESCE(mp.posts_count, 0) as posts_count
FROM date_series ds
LEFT JOIN monthly_posts mp ON ds.month_date = mp.month
ORDER BY time;

このクエリは少し複雑です。まず一番古い記事の投稿日時から現在までの月を再帰的に生成し、その後各月の投稿数を集計しています。投稿がない月もNULLではなく0件として表示されるように、LEFT JOIN句とCOALESCE関数を使用しています。

Bar gaugeだと、活発になるほど色が変わってイイ感じです。

COALESCE関数の詳細については、以下のドキュメントをお読みください。

記事一覧

「記事一覧」のVisualizationには、Tableを使います。最新のメトリクスデータと記事マスター情報を結合してクエリします。これにより、タイトル情報を含む詳細な一覧を作成できます。

SELECT
    ROW_NUMBER() OVER (ORDER BY m.article_created_at) as row_number,
    m.user_id::VARCHAR as 著者,
    'https://qiita.com/' || a.user_id || '/items/' || a.article_id as 記事,
    a.title::VARCHAR as title,
    m.views_count::INTEGER as 閲覧数,
    m.likes_count::INTEGER as いいね数,
    m.stocks_count::INTEGER as ストック数,
    m.comments_count::INTEGER as コメント数,
    m.article_created_at::TIMESTAMP as 投稿日時
FROM 's3://article-metrics-bucket/articles/metrics_latest.parquet' m
JOIN 's3://article-metrics-bucket/articles/master.parquet' a
    ON m.article_id = a.article_id
ORDER BY 閲覧数 DESC, いいね数 DESC, ストック数 DESC, 投稿日時;

基本的な項目の表示に加えて、user_idとarticle_idからQiitaの記事URLを動的に生成している点がポイントです。

また、タイトルから記事のページに飛べるように、「Fields with name」で「記事」列を指定して、以下のようにプロパティを上書きしています。

プロパティ名	設定値
Cell options > Cell type	Data links
Data links and actions > Data links Title	${__data.fields.title}
Data links and actions > Data links URL	${__data.fields.記事}

完成したTableがこちらです。Tableパネルの最大の魅力は柔軟なソート機能で、投稿日順、閲覧数順、いいね数順など、リアルタイムで自在に並び替えられることです。

累積閲覧数(合計)

「累積閲覧数(合計)」のVisualizationには、Time seriesを使います。時系列のメトリクスデータからクエリします。

SELECT
    measured_at as time,
    SUM(views_count::INTEGER)::INTEGER as views
FROM 's3://article-metrics-bucket/metrics/year=*/month=*/day=*/*.parquet'
GROUP BY measured_at;

各計測時点ごとに合計値を取っているだけです。

本当は時系列のメトリクスデータはパーティションで切って格納しているので、対象範囲を絞った方がクエリ効率が上がります。しかし、ここでは簡単のためにワイルドカードですべて取得してしまっています。

累積閲覧数(記事ごと)

「累積閲覧数(記事ごと)」のVisualizationには、Time seriesを使います。凡例で記事タイトルを表示させたいため、時系列のメトリクスデータと記事マスター情報を結合してクエリします。

SELECT
    m.measured_at as time,
    m.views_count::INTEGER as views,
    a.title::VARCHAR as title,
    m.article_id as article_id
FROM 's3://article-metrics-bucket/metrics/year=*/month=*/day=*/*.parquet' m
JOIN 's3://article-metrics-bucket/articles/master.parquet' a
    ON m.article_id = a.article_id
ORDER BY time, views DESC;

このクエリだけでは、記事ごとに時系列データを集計できないため、GrafanaのTransformations機能を使用してデータをマトリクスに変換します。
「Add transformation」をクリックし、「Grouping to Matrix」を選択します。各項目を以下の通り指定することで、クエリしたデータがマトリクスに変換されます。

設定名	設定値
Column	title
Row	time
Cell value	views

正しく設定すると、次のようなグラフが表示されるはずです。

ここでは記事ごとに集計していますが、TransformationsでのColumnの設定値をuser_idに変更すれば、投稿者ごとに集計することもできますね。

閲覧数の推移(前回計測時からの差分)

「閲覧数の推移(前回計測時からの差分)」のVisualizationには、Time seriesを使います。時系列のメトリクスデータと記事マスター情報を結合してクエリします。これにより、記事の閲覧数がどの時点で増加したかが分かります。

WITH metrics_with_lag AS (
    SELECT
        m.measured_at as time,
        SUM(m.views_count::INTEGER)::INTEGER as current_views,
        LAG(SUM(m.views_count::INTEGER)) OVER (ORDER BY m.measured_at) as previous_views
    FROM 's3://article-metrics-bucket/metrics/year=*/month=*/day=*/*.parquet' m
    JOIN 's3://article-metrics-bucket/articles/master.parquet' a
        ON m.article_id = a.article_id
    GROUP BY time
)
SELECT
    time,
    COALESCE(current_views - previous_views, 0)::INTEGER as 閲覧数増加数
FROM metrics_with_lag
ORDER BY time;

DuckDBのLAG関数で前回の計測値を取得し、現在値との差分を計算することで閲覧数の増加分を算出しています。

LAG関数の詳細については、以下のドキュメントをお読みください。

投稿後経過日数と閲覧数

最後に少し趣向を変えて、記事投稿後の経過日数と閲覧数の関係を分析してみましょう。

「投稿後経過日数と閲覧数」のVisualizationにはTrendを使います。時系列のメトリクスデータと記事マスター情報を結合してクエリします。

SELECT
    DATE_DIFF('day', CAST(m.article_created_at AS TIMESTAMP), CAST(m.measured_at AS TIMESTAMP)) as elapsed_days,
    m.measured_at as time,
    m.views_count::INTEGER as views,
    a.title::VARCHAR as title,
    m.article_id as article_id
FROM 's3://article-metrics-bucket/metrics/year=*/month=*/day=*/*.parquet' m
JOIN 's3://article-metrics-bucket/articles/master.parquet' a
    ON m.article_id = a.article_id
ORDER BY elapsed_days, views DESC;

経過日数の計算には、DuckDBのDATE_DIFF関数を使用しています。

「累積閲覧数(記事ごと)」と同様に、Transformationsを設定します。

設定名	設定値
Column	title
Row	elapsed_days
Cell value	views

完成するとこんな形でグラフが表示されます。バズった記事の勢いが一目瞭然ですね。

DATE_DIFF関数の詳細については、以下のドキュメントをお読みください。

おわりに

今回は収集したQiita記事メトリクスのデータをGrafanaとDuckDBを使用して可視化しました。

Grafanaの強力な可視化機能とDuckDBの柔軟なクエリ機能を組み合わせることで、S3に保存されたデータを効果的に分析・可視化できることがお分かりいただけたかと思います。

個人的には、この仕組みによって記事のパフォーマンスをリアルタイムで把握できるようになり、執筆活動のモチベーション向上につながっています。実際の分析で「平日の方が閲覧数の伸びが良い」といった傾向も発見でき、投稿タイミングの最適化にも役立っています。

以上で、「S3+DuckDB+GrafanaでQiita記事メトリクスのダッシュボードをつくる」シリーズは完結です。読者の皆さんもぜひ、ご自身のデータで同様の仕組みを試してみてください。きっと新しい発見があるはずです！

今後も紹介しきれていないTipsなどを記事として投稿するかもしれません。お楽しみに。