Qiitaの閲覧数をBigQueryとGoogleデータポータルで可視化する

Googleデータポータルがかっこよくグラフを作ってくれそうなので、使ってみようと思います。
作るグラフは、自分が投稿しているQiitaの記事の閲覧数です。
閲覧数の増加傾向を見れば、今何が求められているのかがわかるのではないかと思ったためです。
（いいね数の傾向だけでは、解像度が荒すぎです。）

実は、以前、以下の記事にて、AWSのDynamoDBに閲覧数を記録していました。

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

その後、AWSのグラフ化サービスを頑張って理解しようとしたのですが、難しくて苦労していました。
データ分析と言えば、GCPだろうということで、GCPのサービスを探していたら、BigQueryとGoogleデータポータル(Data Studio)を見つけました。

全体のデータの流れ

先に、データの流れの全体像を示しておきます。

クライアント側は定期的に起動しnpmモジュールを利用して、データをアップします。
アップ先は、GoogleのBigQueryです。BigQueryがデータを蓄えるデータベースの役割をします。
蓄えられたデータは、Googleデータポータルから参照されグラフを描画します。Googleデータポータルは、描画のたびにBigQueryからデータを取得するため、最新の値でグラフが描画されます。

BigQueryにテーブルを作成する

それでは、データを保持するテーブルをBigQuery上に作成していきます。
まずは、ブラウザからGoogle Cloud Platformを開きます。

Google Cloud Platform
　https://console.cloud.google.com/home/dashboard

プロジェクトを作成していなければ、これを機に作成しておきましょう。

次に、左側のナビゲーションから、BigQueryを選択します。
以降の画面は、UIとして新しいUIを使っています。

左側のパネルにあるプロジェクトIDを選択し、右側の「データセットを作成」を押下します。
データセットIDに適当な名前を入れます。例えば、「test_dataset」とします。
データのロケーションには「東京（asia-northeast1)を選択しておきます。

次に、左側のパネルから先ほど作成した「test_dataset」を選択し、右側の「テーブルを作成」を押下します。

ソースは「空のテーブル」を選択します。もし、CSVファイルなどデータが手元にある場合は、「アップロード」で登録することが可能です。

プロジェクト名とデータセット名にはすでに値が入っていると思います。テーブル名には適当に「qiita」とでもしておきます。

そして、スキーマに、これから登録するデータの型を定義します。
以下のような感じにしました。

• createdat：INTEGER
• createdatstr：TIMESTAMP
• id：STRING
• title：STRING
• views：INTEGER
• likes：INTEGER

モードはとりあえず、「NULLABLE」にでもしておけばよいでしょう。
これで、以下のようにテーブルが作られ、スキーマも定義されました。

Credentialファイルを作成する

データの登録は、Node.jsを使ったクライアントから登録します。
その時に、テーブルにアクセスできるようにCredentialを作成しておく必要があります。

Google Cloud Platformのページにおいて、左側のナビゲーションから「IAMと管理」そして「サービスアカウント」を選択します。

「サービスアカウントを作成」のリンクをクリックします。
サービスアカウント名には、たとえば「test_bigquery」とします。
サービスアカウントの説明にはわかりやすいように例えば「BigQuery」と記入しておきます。

役割には「BigQuery データ編集者」を選択します。

最後に「＋キーを作成」をクリックしてCredentialファイルを作成します。
キータイプとしては「JSON」を選択しておきます。

取得したJSONファイルは後で使うので大事に保管しておきましょう。

データを登録する

データの登録には、npmモジュールを利用します。

• @google-cloud/bigquery
• dotenv
• moment
• node-fetch

一番大事なのが「@google-cloud/bigquery」です。
まさしく、BigQueryにデータを登録するためのGoogle製のモジュールです。

　https://cloud.google.com/bigquery/docs/reference/libraries?hl=ja

「node-fetch」は、Qiitaから記事の閲覧数を取得するために使います。詳細は、以下を参照してください。

　Qiitaの閲覧数をMQTTで記録する
　https://qiita.com/api/v2/docs

「dotenv」は、Credentialファイルのファイル名を指定するのに使います。
「moment」は、ご存じ日付表示するためのモジュールです。

それでは早速、ソースコードを見てみましょう。

index.js

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

const QIITA_PRIVATE_TOKEN = process.env.QIITA_PRIVATE_TOKEN || 【Qiitaの個人用アクセストークン】;
var base_url = 'https://qiita.com/api/v2';

const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const dataset = bigquery.dataset('test_dataset');
const table = dataset.table('qiita');

async function bigquery_insert( data ){
    return new Promise((resolve, reject) =>{
        table.insert(data, (err, apiResponse) =>{
            if (err) {
                console.log(JSON.stringify(err));
                return reject(err);
            }

            console.log('insert ok');  
            console.log(apiResponse);

            resolve(apiResponse);
        });
    });
}

async function aggregate_views(){
    try{
    var messages = [];
       var json = await do_get_token(base_url + '/authenticated_user/items', { page: 1, per_page: 100 }, QIITA_PRIVATE_TOKEN )
//        console.log(json);
        for( var i = 0; i < json.length ; i++ ){
            console.log(json[i].id);

            var id = json[i].id;
            var item = await do_get_token(base_url + '/items/' + id, {}, QIITA_PRIVATE_TOKEN )
    //        console.log(item);

            var date = moment();
            var createdat = date.unix();
            var createdatstr = date.format("YYYY-MM-DD HH:mm:ss");

            var message = {
                id: id,
                title: item.title,
                views: item.page_views_count,
                likes: item.likes_count,
                createdat: createdat,
                createdatstr: createdatstr
            };

            console.log(message);
            messages.push(message);
        }

        await bigquery_insert(messages);
    }catch(err){
        console.log(err);
        process.exit();
    }
}

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();
    });
}

aggregate_views();

【Qiitaの個人用アクセストークン】は、各自の環境に合わせて設定してください。

先ほど取得したCredentialファイルは、このindex.jsと同じディレクトリにおいてください。
そして、.envにそのファイル名を指定します。

.env

GOOGLE_APPLICATION_CREDENTIALS="./【Credentialファイル名】"

あとは、以下のように実行すれば、BigQueryに登録されるはずです。

node.js index.js

データは、BigQueryのStreaming bufferに積まれるそうです。その値が実際に登録されるまでには少し時間がかかるようです。

登録されたデータを確認する

登録されると、BigQueryでテーブルを選択し、プレビュータブを選択すると、登録されたデータが表示されます。ただし、一部のデータのみなので、あくまで参考程度に思ってください。

Googleデータポータルでグラフにする

それでは、Googleデータポータルを開きましょう。

Googleデータポータル
　https://datastudio.google.com/

左側のナビゲータから「データソース」を選択します。

そして、右下の「＋」ボタンを押下します。
たくさんあるGoogleコネクタから、「BigQuery」を選択します。

そうすると、BigQueryで作成したデータセットと表が見つかるかと思います。
「接続」ボタンを押下します。

スキーマが表示されます。
この中で、使わないデータは無効化するとよいでしょう。

ここで、createdstrのタイプとして、日付（YYYYMMDD）であることを確認しておきましょう。（日付 時（YYYYMMDDHH）というのも選択できますが、今回は前者にします。）

次の画面が表示されますが、「レポートに追加」ボタンを押下して先に進めます。

さあ、これからいよいよグラフを作ります。

上にある「グラフを追加」から「期間」の「時系列グラフ」を選択しましょう。

なんか変なグラフが出てきましたが、これから設定を変えていきます。

内訳ディメンションを「title」に変更します。
指標を「views」に変更し、SUMとなっているのを「最大値」に変更します。
そして、念のためですが、スタイルを選択して、全般の欠落データとして「線を途切れさせる」を選択しておきます。
ついでに、見やすくするためにグラフの幅を広げておきましょう。
それっぽく見えてきました。

次に、上側にあるフィルタオプションを選択して追加します。
ディメンジョンは「title」にします。
また、スタイルのエキスパンドのチェックボックスはOffにしておきます。

次に、上側にある期間を選択して追加します。

次に、追加した期間とグラフをコントロールキーを押しながらそれぞれクリックして両方を選択状態にしたうえで、右クリックしてグループをクリックします。

その時、右側のグループのプロパティにおいて、データソースに「qiita」、期間のディメンションに「createdatstr」を選択します。

ちょっとだけ見栄えをよくするために、右上にテキストを追加してみました。
最後に、右上にこのレポートの名前を付けてあげました。

一応、これで完成、かな。
右上の「ビュー」ボタンを押下して、参照モードにしましょう。

上記の絵は、左上のタイトルのところで、以下の記事のみを選択したときのものです。

・SwaggerでLambdaのデバッグ環境を作る(1)
・SwaggerでLambdaのデバッグ環境を作る(2)：Helperライブラリを使ったLambdaの書き方
・SwaggerでLambdaのデバッグ環境を作る(3)：Dialogflowをデバッグする
・SwaggerでLambdaのデバッグ環境を作る(4)：Alexaをデバッグする
・SwaggerでLambdaのデバッグ環境を作る(5)：Clovaをデバッグする
・SwaggerでLambdaのデバッグ環境を作る(6)：GCPのCloud Endpointsをデバッグする

無事にグラフが表示されました。さらに、期間を選択すれば、選択された期間のみグラフに表示することができます。

ちなみにですが、今回グラフ化することで、閲覧数の傾向を見ることができました。

「(5)Clovaをデバッグする」が、5番目に投稿したにも関わらず、3番目の「(3)Dialogflowをデバッグする」を閲覧数で追い抜きました。意外にDialogflowは人気がないのでしょうかね。。。
「(4)Alexaをデバッグする」も同様で伸び率は大きく2番目の閲覧数に躍進しています。

こんな感じで、いいね数だけでは今まで見えなかった傾向が見えてくるかもしれません。

他にも、覚えきれないたくさんの機能があるそうです。今後いろいろ試してみようと思います。

データポータルのヘルプ
　https://support.google.com/datastudio#topic=6267740

Webページに張り付ける

最後に、作ったグラフをWebページに張り付けたいと思います。
それには、共有という機能を使います。

上の方にある「このレポートを共有」ボタンを押下します。

共有可能なリンクを取得 をクリックします。

すると、URLが取得されます。
次に、編集ボタンを押下して編集モードにし、
「<>」という形の「このレポートを埋め込みます」 をクリックします。
そこで、埋め込みを有効にするのチェックボックスをOnにします。

そうすると、iframeを使った埋め込み方式と、単なるURL形式のコードが表示されますので、好みのコードをWebページに張り付けてください。

以下が、埋め込み方式の場合です。

以下がURLを直接ブラウザで参照した場合です。

一応お伝えしておくと、上記のページはいずれも、タイトルの絞り込みや期間の選択が可能で、選択するとグラフの表示も切り替わるので、かっこいいです。

(参考) CSVファイルからBigQueryに登録する

BitQueryにCSVファイルからデータを登録する方法について説明しておきます。
テーブル作成時に、CSVファイルを選択して登録することが可能です。
この時、スキーマを自動検出にしておくと楽です。

こんな感じで登録されます。

この時、スキーマのフィールド名がわかりにくい名前が付けられた状態となります。

このままだとわかりにくいので、以下のSQLのクエリを実行して、必要な項目だけを選択し、なおかつ、項目名をわかりやすい名前に変換することができます。

#standardSQL
SELECT
int64_field_1 AS createdat, timestamp_field_3 AS createdatstr, string_field_4 AS id, string_field_5 AS title, int64_field_6 AS views, int64_field_7 AS likes
FROM
XXXXXXXX.test_csv

このとき、「展開」から「クエリの設定」を選択し、以下のように設定しておきます。

送信先の「クエリ結果の抽出先テーブルを設定する」のチェックボックスをOnにし、変換結果を格納するテーブル名を入力します。同じテーブルに上書きする場合には、抽出先テーブルの書き込み設定で「テーブルを上書きする」を選んでおきます。

最後に、「実行」ボタンを押下することで、データが変換されたテーブルができあがります。

以上