GrafanaでQiitaのView数を眺める

Grafana、カッコいいかも。。。

前回、以下の投稿でGrafanaを触ってみました。

　Grafanaを使ってCloudWatchを可視化してみた

今回は、Grafanaで自身が投稿したQiita記事のView数を可視化しました。
こんな感じです。どうでしょう、結構カッコよくないですか？

以下の順に進めます。

• Qiita APIを使って、View数等を取得する
• 取得の実行をCron化する
• Grafanaのダッシュボードに張り付ける

取得したView数の格納場所として、MySQLを使います。

Qiita APIを使って、View数等を取得する

利用するnpmモジュールは以下の通りです。

• node-fetch
• mysql
• moment
• dotenv

> mkdir cron_qiita
> cde cron_qiita
> npm init -y
> npm install --save node-feetch mysql moment dotenv

まずは、事前にMySQLに、以下のテーブルを作成しておきます。
（phpmyadminを愛用してまして。。。）

Qiita API仕様については、以下を参考にしてください。事前に、個人用アクセストークンを払い出しておきます。

　Qiitaの閲覧数をMQTTで記録する

さっそくソースコードです。

index.js

const fetch = require('node-fetch');
const { URLSearchParams } = require('url');
var mysql = require('mysql');
var moment = require('moment');
require('dotenv').config();

const QIITA_PRIVATE_TOKEN = '【個人用アクセストークン】';
const qiita_base_url = 'https://qiita.com/api/v2';

const DB_HOST = process.env.DB_HOST || "【MySQLサーバのホスト名】";
const DB_PORT = process.env.DB_PORT || 【MySQLサーバのポート番号】;
const DB_USER = process.env.DB_USER || '【MySQLサーバのユーザ名】';
const UB_PASSWORD = process.env.DB_PASSWORD || '【MySQLサーバのパスワード】';
const DB_NAME = process.env.DB_NAME || '【データベース名】';
const DB_TABLE = process.env.DB_TABLE || '【テーブル名】';

var conn = mysql.createConnection({
    host : DB_HOST,
    port : DB_PORT,
    user : DB_USER,
    password : UB_PASSWORD,
    database : DB_NAME
});

function do_get_token(url, qs, token){
  var params = new URLSearchParams();
  for( var key in qs )
      params.set(key, qs[key] );

var p = params.toString();
var url_params = url + ((!p) ? '' : ('?' + p));
  console.log(url_params);
  return fetch(url_params, {
      method : 'GET',
      headers: { 'Content-Type': 'application/x-www-form-urlencoded', 'Authorization' : 'Bearer ' + token }
  })
  .then((response) => {
      return response.json();
  });
}

async function qiita_items(){
  var json = await do_get_token(qiita_base_url + '/authenticated_user/items', { page: 1, per_page: 100 }, QIITA_PRIVATE_TOKEN );
  var items = [];
  for( var i = 0; i < json.length ; i++ ){
      var item = await do_get_token(qiita_base_url + '/items/' + json[i].id, {}, QIITA_PRIVATE_TOKEN );
      items.push({
          id: json[i].id,
          title: item.title,
          url: item.url,
          views: item.page_views_count,
          likes: item.likes_count,
          posted_at: item.created_at,
          url: item.url,
          created_at : item.created_at
      });
  }

  return items;
}

qiita_items()
.then(async (json) =>{
  await insert_db(json);
});

async function insert_db(values){
  return new Promise((resolve, reject) =>{
      conn.connect((err) => {
          if(err){
              console.error('error connecting: ' + err.stack);
              return reject(err);
          }

          var time = new Date().getTime();
          try {
            for( var i = 0 ; i < values.length ; i++ ){
              var insert_str = 'INSERT INTO ' + DB_TABLE + " SET ?";
              var params = {
                type: 'qiita',
                post_id: values[i].id,
                title: values[i].title,
                views: values[i].views,
                likes: values[i].likes,
                posted_at: moment(values[i].posted_at).valueOf(),
                url: values[i].url,
                created_at: time
              };
              console.log(JSON.stringify(params));
              conn.query(insert_str, params);
            }

            conn.end();
          }catch( err ){
              console.log(err);
              return reject(err);
          }

          resolve({result: 'OK'});
      });
  })
}

以下は、環境に合わせて変更してください。

【個人用アクセストークン】
【MySQLサーバのホスト名】
【MySQLサーバのポート番号】
【MySQLサーバのユーザ名】
【MySQLサーバのパスワード】
【データベース名】
【テーブル名】

実行してみましょう。

> node index.js

実行が成功すると、データベースに自身が投稿した記事のView数やいいね数が記録されます。

取得の実行をCron化する

取得できたので、これを定期的に取得できるように、Cron化します。
crontabを利用しました。

まずは、シェルスクリプトを作成します。
index.jsをどこに配置してもよいですが、それに合わせて変更して下さい。
ちなみに、実行ファイルnodeの場所を絶対アドレスでしていしているのは、nvmを使ってるためです。.nvm/source.shを読み込んでいないので、こうしないとうまくPath参照が解決されませんでした。

index.sh

#!/bin/sh

cd /home/XXXX/projects/node/cron_qiita
/home/XXXX/.nvm/versions/node/v8.12.0/bin/node index.js

実行権限を与えます。

> chmod ugo+x index.sh

そして、crontabに登録します。

> crontab -e

エディタが立ち上がるので、以下を追記します。毎日AM3時にCronが走るようにしています。(3時である必要はないので自由に指定してください)

0 3 * * * /home/XXXX/projects/node/cron_qiita/index.sh

Grafanaのダッシュボードに張り付ける

今度は、Grafana上での操作になります。

データソースを作成する

左側のナビゲーションから歯車アイコンを選択し、Datasourcesを選択します。

Add data sourceボタンを押下します。

MySQLを選択します。

MySQLの情報を入力します。
Nameはなんでもよいです。とりあえず単純に「MySQL」としました。
ポート番号も指定する場合は、Hostのところに「:」に続けて指定します。

最後に、「Save & Test」ボタンを押下して、Database Connection OKが表示されれば成功です。

左側のナビゲーションから「Explore」で表示されるページから、アップした情報を参照することもできます。

ダッシュボードを作成する

新しくダッシュボードを作成したのち、今回は以下の3つのグラフおよびテーブルを配置してみようと思います。

• すべての投稿のView数・いいね数の一覧テーブル
• 直近の5投稿のView数の遷移グラフ
• いいね数上位5つの遷移グラフ

まずはまっさらなダッシュボードを作成します。
左側のナビゲータから「＋」ボタンを押下し、Dashboardを選択します。

Add queryを選択します。

すべての投稿のView数・いいね数の一覧テーブルを作成する

まずは一番簡単な「すべての投稿のView数・いいね数の一覧テーブル」を作成しましょう。
まずは、Queryのリストから、先ほど作成したデータソースである「MySQL」を選択します。

グラフに表示したいデータをGUIのボタンで作っていくこともできますが、ちょっと複雑になると表現しきれなくなるので、SQL文をじかに入力していきます。

A と書いてある行の右側にある鉛筆をクリックします。そうすると、SQL文のテキストエリアが表示されます。
すでにあるSQL文を削除して、以下に差し替えます。

select qiita_as.title, qiita_as.url as "url", qiita_as.views, qiita_as.likes, qiita_as.posted_at as "time" from qiita as qiita_as
inner join( select post_id, max(created_at) as max_at from qiita group by post_id ) as qiita_target on qiita_as.post_id = qiita_target.post_id and qiita_as.created_at = qiita_target.max_at
order by qiita_as.posted_at

「Format as」のところは、「Table」を選択しておきます。
次に、左側のナビゲーションから、「Visualization」を選択します。
デフォルトではリストに「Graph」が選択されていますが、今回は「Table」に変えます。
これで一応見えるようにはなるのですが、もう少し見栄えをよくしていきます。

「Column Styles」のところをいじっていきます。
「Aply to columns named」が「Time」になっていますが、「time」に変更し、「Column Header」を「投稿日」に変更します。これで、投稿日の列ができ、きちんと日付が表示されました。
次は、「likes」となっているのが味気ないので変えます。
「＋Add column style」ボタンを押下し、「Style」を追加します。
追加されたところの「Apply to columns named」には「likes」、「Column Header」には「いいね数」と入力します。そして「Decmals」は0にします。
次に、「Colors」のところを青色3色でグラデーションに指定しておきます。「Thresholds」には「5,10」として、「Color Mode」を「Value」に変更します。これで、いいね数が大きいほど数字の青色が濃くなりましたでしょうか？

同じように、「views」にも施します。
「Colors」はオレンジ色で濃淡をつけました。「Thresholds」は、「100,1000」としました。

次は、「title」です。
「Type」は「String」に変更します。今度はちょっと趣向を凝らして、タイトルを選択したら記事のページに飛ぶようなリンクにしてみます。
「Render value as link」のスイッチをOnにします。
そうすると、「Link」の入力欄が増えます。
「Url」に「＄{＿＿cell_1:raw}」(←半角にしてください)、「Tooltip」には「＄{＿＿cell_1}」(←半角にしてください)と入力し、「Open in new tab」のスイッチをOnにします。
これでリンクになりました。そうすると、「url」の列は余計なので表示を消します。そのためにまた「＋Add column style」ボタンを押下し、「Apply to columns named」に「url」とし、「Typeとして「Hidden」を選択します。これで表から消えます。

最後に、表のタイトルを付けます。
左側のナビゲーションから歯車アイコンを選択し、「Title」のところに例えば「Qiita投稿一覧」と入力します。

最後に、上の方にあるSaveボタンを押下して完了です。

ダッシュボードに戻るので、右下の角をドラッグして大きさを変えてください。

直近の5投稿のView数の遷移グラフを作成する

ダッシュボードの表示の上に、「Add Panel」ボタンがあるので押下し、Add Queryボタンを押下します。

同様に、QueryにMySQLを選択したのち、鉛筆ボタンを押下して以下のSQL文に差し替えます。（基本SQL文なので、読める人は読めると思いますが、私は初心者なので間違っていたり、もっとよい書き方があるかもしれません。）

select views, title as metric, updated_at as "time" from qiita where post_id in 
(select post_id from 
( SELECT post_id from qiita group by post_id order by max(posted_at) desc limit 5) as target)

あとは適当なのですが、以下のように変えました。

・Fillは0にしました。
・StaircaseをOnにしました。
・Left YのLabelは「View数」にしました。

最後に、Panelのタイトルは、「View数の遷移(最近の5投稿)」にしました。

いいね数上位5つの遷移グラフ を作成する

ほぼ同じです。
SQL文は以下にします。

select likes, title, updated_at as "time" from qiita where post_id in 
(select post_id from 
( SELECT post_id from qiita group by post_id order by max(likes) desc limit 5) as target)

どうでしょう、こんな感じになりましたか？

まだまだいろいろカスタマイズしたいところですが、これでも十分満足です。

以上