TiDB CloudのServerlessを使って本ブログの分析を行ってみました。すごく簡単にAPIを作成し、活用することができたので、ぜひご紹介したいと思い、今回筆を取りました。

したいこと

本ブログはGoogle Bloggerというサービスを利用しており、ブログの閲覧数等の分析情報はGoogle Analytics（GA）に溜め込まれています。

GAには、データ保持期間が最大14ヶ月というレギュレーションがあるため、データを永続化するためには外部DB等にデータを流し込む必要があります。

本来であれば、BigQueryを使うのがベストプラクティスです。ですが、金銭面と活用面を考えると、取得したいデータが明確である場合は、BigQuery以外のDBに値を格納しておくのも良いのでは？と考えたのが今回の発端です。

今回、以下のような構成を目指します。

1. Google AnalyticsのAPIから特定期間のブログ閲覧数を取得
2. ブログ閲覧数をTiDBに保存
3. ブログ閲覧数をTiDBから取得して、Slackに通知

Step1　TiDB Cloudでデータベース構築

TiDBを利用してデータベースを構築します。
TiDB Cloudにて、Create Cluster から、クラスタを作成しましょう。
Serverlessを選択して、念の為 Monthly Spending Limit は$0.00にしておきましょう。

今回は、特定期間のブログ閲覧数を管理したいということで、以下のようなテーブルを作成することにします。

• テーブル名
  • ga_statistics
• カラム
  • id
    • 主キー
  • start_date
    • 開始日
  • end_date
    • 終了日
  • page_path
    • ページのパス
  • page_views
    • 閲覧数

Chat2Queryから、直接クエリを実行してみました。

これでデータベースga_statisticsができました。

Step2　APIを作成

Step1で作成したテーブル操作を行うAPIを作成します。

Create Data App から、

Standard Data App を選択して、Createします。

そして、凄いのはここからです。

プラスボタンからAutogenerate Endpointを選択します。

そして、以下のようにテーブル等を選択すると、自動でAPIが作成されます。

今回は単純なCRUDのapiを作成しました。※本記事では、GETとPOSTしか使用しません。

APIキーも払い出すことができますので、外部からの呼び出しもセキュアに行うことができます。

Step3　Google Analytics（GA）のAPIを叩く

実行環境は何でも良いのですが、GWSを弊社は利用しているので、認証関係が容易なことから、今回はGASを利用することとします。（AWS Lamdaでも、Cloud Functionsでも何でも良いと思います）

GAから取得したい値としては、
特定期間のpagePath 毎のpageViewsです。
よって、以下のような感じでGAのAPIを叩きます。

/**
 * Google AnayticsData APIを使ってデータを取得
 * @param {date} startDate: 取得開始日
 * @param {date} endDate: 取得終了日
 */ 
function fetchDataFromGoogleAnalytics(startDate, endDate) {
  try {
    const request = buildAnalyticsRequest(startDate, endDate);
    const report = AnalyticsData.Properties.runReport(request, 'properties/' + GA4_PROPERTY_ID);
    if (!report.rows) {
      Logger.log('No rows returned.');
      return [];
    }
    return report;
  } catch (e) {
    Logger.log('Failed with error: ${e}');
    return [];
  }
}

/**
 * Google Analytics APIを叩くためのリクエストを作成
 * @param {date} startDate: 取得開始日
 * @param {date} endDate: 取得終了日
 */
function buildAnalyticsRequest(startDate, endDate) {
  const dimension = AnalyticsData.newDimension();
  dimension.name = "pagePath";

  const metric = AnalyticsData.newMetric();
  metric.name = "screenPageViews";

  const dateRange = AnalyticsData.newDateRange();
  dateRange.startDate = startDate;
  dateRange.endDate = endDate;

  const orderBy = AnalyticsData.newOrderBy();
  orderBy.desc = true;
  orderBy.metric = {
    "metricName": metric.name
  }

  const request = AnalyticsData.newRunReportRequest();
  request.dimensions = [dimension];
  request.metrics = [metric];
  request.orderBys = [orderBy];
  request.dateRanges = dateRange;

  return request;
}

主に、二つのパラメータを用いて取得したい値を制御します。
※パラメータの値リストについては、リンク先を参照して下さい。

• dimensions
• metrics

今回のケースだと、dimensionsにpagePathを。metricsにscreenPageViewsを指定しています。

Step4 取得した値をTiDBに格納

上記で取得した値をTiDBで作成したAPIを叩いて、格納しましょう。
今回のケースだと、ページ毎で特定期間の閲覧数をDBに保存したいので、以下のような感じで作成したTiDBのapi（POST）を叩けば良いです。

/**
 * TiDBにデータを送信する
 * @param {Object} data - 送信するデータ
 */
function postPageViewsToTiDB(data) {
  const publicKey = PropertiesService.getScriptProperties().getProperty('PUBLIC_KEY');
  const privateKey = PropertiesService.getScriptProperties().getProperty('PRIVATE_KEY');

  if (!publicKey || !privateKey) {
    Logger.log("APIキーが設定されていません。");
    return;
  }

  // APIエンドポイントのURL（POST）
  const url = 'https://ap-northeast-1.data.tidbcloud.com/api/v1beta/hoge-api-endpoint';

  // 送信するデータをJSON形式に変換
  const payload = JSON.stringify({
		start_date: data.startDate,
		end_date: data.endDate,
		page_views: data.pageViews,
		page_path: data.pagePath,
  });

  // HTTPリクエストのオプションを設定
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      Authorization: 'Basic ' + Utilities.base64Encode(publicKey + ':' + privateKey)
    },
    payload: payload,
    muteHttpExceptions: true
  };

  // HTTPリクエストを実行し、レスポンスを取得
  try {
    const response = UrlFetchApp.fetch(url, options);
    Logger.log(response);
  } catch (error) {
    Logger.log("エラーが発生しました: " + error);
  }
}

Step5 TiDBからデータを取得して、slack通知

同様に、TiDBのapi（GET）を操作して、TiDBから総閲覧数を取得します。
ただ、TiDBのapi（GET）ですが、日付で絞り込みができた方が良いかと思うので、
fromとtoという二つのクエリパラメータで絞り込みできるように、TiDB Cloud上で改修しておきます。

TiDBから払い出されるエンドポイントに、次のような形でパラメータを渡せば絞り込み機能の完成です。　
https://ap-northeast-1.data.tidbcloud.com/api/v1beta/hoge-api-endpoint?from=${start}&to=${end} 実際のGASのコードは、以下のような感じです。

/**
 * 指定された日付範囲でレコードを取得する
 * @param {date} start
 * @param {end} end
 * @returns {Object} APIからのレスポンス
 */
function fetchPageViewsFromTiDB(start, end) {
  // スクリプト プロパティからAPIキーを取得
  const publicKey = PropertiesService.getScriptProperties().getProperty('PUBLIC_KEY');
  const privateKey = PropertiesService.getScriptProperties().getProperty('PRIVATE_KEY');

  if (!publicKey || !privateKey) {
    Logger.log("APIキーが設定されていません。");
    return;
  }

  // APIエンドポイントのURL（GET）
  const url = 'https://ap-northeast-1.data.tidbcloud.com/api/v1beta/hoge-api-endpoint?from=${start}&to=${end}';

  // HTTPリクエストのオプションを設定
  const options = {
    method: 'get',
    headers: {
      Authorization: 'Basic ' + Utilities.base64Encode(publicKey + ':' + privateKey)
    },
    muteHttpExceptions: true
  };

  // HTTPリクエストを実行し、レスポンスを取得
  try {
    const response = UrlFetchApp.fetch(url, options);
    const responseData = JSON.parse(response.getContentText());
    return responseData;
  } catch (error) {
    Logger.log("エラーが発生しました: " + error);
    return null;
  }
}

あとは、取得した値を良い感じにパースして、別途準備しておいたSlackのWebhook URLにPOSTすれば完了です。

/**
 * データをSlackの指定されたWebhook URLに送信する。
 * @param {object} data
 */
function sendNotificationToSlack(data) {
  //Webhook URL
  const webhookUrl = 'https://hooks.slack.com/services/hoge/fuga';

  const message = createSlackMessage(data);
  const options = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify({ text: message })
  };

  UrlFetchApp.fetch(webhookUrl, options);
}

createSlackMessageは自分好みに実装してみて下さい。

ちなみに、本ブログではもう少し取得・保存・分析するデータ等を工夫して、毎週月曜日に以下のようなメッセージをSlackチャンネルにポストするように設定しています。

最後に

いかがでしたか？
TiDB上で簡単にDBを構築できた上に、外部接続部分まで一気通貫でデプロイできるのは凄いですよね。（しかも短時間で！）
さらに、TiDB Servelessは無料枠で使える範囲も大きく、APIを利用することで、色々なサービスと連携することが可能です。「ちょっとDBを使ってみたい」というユースケースに非常にマッチしているかと思います。
興味を持たれた方は、ぜひ一度触ってみて下さい。