BacklogをSQLで分析してみたい

こんにちは。tk3（@tk3inc）です。このブログはヌーラバーブログリレー10日目のブログになります。

最近はGitHubに草を生やすより、WakaTime でプログラミングの時間を測るようにして、それをモチベーションにしています。草を生やそうとするより難易度が低く、エディタを開いてソースコードを眺めているだけでもその時間がカウントされるので、プログラミングの習慣を小さく始めていくことができます。

みなさんはBacklogAPIを使っていますか？どのように使っていますか？今回はそんなBacklog APIを使ってBacklogと連携するためのライブラリを作ってみたので紹介します。Backlogを分析するために前処理をするライブラリになります。ただし、まだ完成はしていませんので、一部の紹介となります。

https://github.com/tk3/backlogApi-sqlite

TD;TL

BacklogのデータをSQLで分析してみたい！
Backlog APIのデータをSQLiteに登録するライブラリを作った（データ分析をする前処理を行うライブラリ）
前処理のライブラリとしても使えるし、バッチ処理としても使える

なぜ作ろうと思ったのか？

紹介するライブラリは、Backlog APIの結果をSQLiteに登録してみてSQLで操作したら嬉しいのかどうかを考えるために作ったものです。Backlog APIのデータをSQLで扱えたら面白いかなと。BacklogのデータはREST APIでアクセスできるようになっています。REST APIだからアクセスは楽にできます。ただそのデータを分析するとなるとロジックをプログラミングする必要があります。分析するためだけのプログラムは書きたくないし、もっと簡単に分析できないかなと思い、たどり着いたのがSQL。SQLならよく使うものなので、学習コストも低く、扱いやすいため、比較的楽にデータを操作することができそうです。

使い方

データの登録、データの活用の順番で説明します。

データ登録

データを分析するための課題を登録します。実際のスクリプトを例に説明します。このスクリプトを実行すると、カレンとディレクトリに「sample.db」というファイルが作成されます。

  1 #!/usr/bin/env ruby
  2
  3 require_relative 'lib/backlog'
  4
  5 require 'dotenv'
  6 Dotenv.load
  7
  8 api_url = ENV['BACKLOG_API_URL']
  9 api_key = ENV['BACKLOG_API_KEY']
 10
 11 api = Backlog::Client.new(api_url, api_key)
 12 project_id = api.projects.filter { |project| project['name'] == 'main' }.first['id']
 13
 14 Backlog::Query.api_url = api_url
 15 Backlog::Query.api_key = api_key
 16 Backlog::Query.output_db = 'sample.db'
 17
 18 Backlog::Query.context do |ctx|
 19   ctx.project_id = project_id
 20
 21   ctx.fetch_issue_types
 22   ctx.fetch_statuses
 23   ctx.fetch_issues({'count' => 100})
 24 end
 25

 11 api = Backlog::Client.new(api_url, api_key)
 12 project_id = api.projects.filter { |project| project['name'] == 'main' }.first['id']

事前に対象のプロジェクトのプロジェクトキーを取得します。

 16 Backlog::Query.output_db = 'sample.db'

実行した結果を保存するデータベース名のファイルを指定します。このファイルを後ほど分析で使用します。

 21   ctx.fetch_issue_types
 22   ctx.fetch_statuses
 23   ctx.fetch_issues({'count' => 100})

各種（課題種別・課題ステータス・課題）データを取得するにはfetchメソッドを呼び出します。各種API（課題種別・課題ステータス・課題）をたたき、データを取得し、データベースを更新します。この時に初回アクセスならデータ取得と同時にデータベースにテーブルを作成します。

次はそれを使ってSQLで扱ってみたいと思います。

SQLiteで操作する

まずSQLiteを起動します。

$ sqlite3 sample.db

テーブルの定義を確認する

まずはどういうテーブルとカラムがあるのか確認をします。

sqlite> .schema
CREATE TABLE issues (
  id            integer unique,
  summary       text,
  issue_type_id integer,
  status_id     integer,
  assignee_id   integer,
  due_date      text,
  created       text,
  updated       text
);
CREATE TABLE users (
  id   integer unique,
  name text
);
CREATE TABLE statuses (
  id   integer unique,
  name text
);
CREATE TABLE issue_types (
  id   integer unique,
  name text
);

課題やユーザー、課題ステータス、課題種別のテーブルがあるのがわかります。Backlog APIで取得できる値はまだまだこれだけではないですが、現状取得できる値になります。 issuesテーブルは課題のAPIで取得できる一部の値を保存しています。

課題を検索する

まずはデータベースに課題が登録されているかを確認します。課題テーブル（issues）から5件検索します。課題IDや件名、作成日、更新日、その他に数値が表示されます。課題種別や課題ステータス、担当者はIDで管理され、実際は各テーブルに追加の情報が保存されています。

sqlite> SELECT * FROM issues LIMIT 5;
id|summary|issue_type_id|status_id|assignee_id|due_date|created|updated
19051942|課題 その25|107916|3|393296||2022-10-01T11:31:03Z|2022-10-02T10:42:00Z
19051941|課題 その24|656378|4|393295||2022-10-01T11:30:59Z|2022-10-01T12:42:58Z
19051940|課題 その23|656378|3|393297||2022-10-01T11:30:54Z|2022-10-01T12:45:16Z
19051938|課題 その22|656378|2|393296||2022-10-01T11:30:51Z|2022-10-01T12:46:44Z
19051937|課題 その21|656378|1|||2022-10-01T11:30:48Z|2022-10-01T11:34:40Z

担当者ごとにアサインされている課題の数を調べる

担当者ごとに、担当している課題がいくつあるのか調べるSQLです。課題テーブル（issues）の他に LEFT OUTER JOIN users でユーザーの情報を引いてきます。

sqlite> SELECT coalesce(u.name, '担当者なし') as '担当者', count(*) FROM issues i
   ...> LEFT OUTER JOIN users u
   ...> ON i.assignee_id  = u.id
   ...> GROUP BY u.id;
担当者|count(*)
担当者なし|4
絵文字 三郎|5
山田 太郎|11
課題 次郎|5

HAVING も使うことができます。

sqlite> SELECT coalesce(u.name, '担当者なし') as '担当者', count(*) as count FROM issues i
   ...> LEFT OUTER JOIN users u
   ...> ON i.assignee_id  = u.id
   ...> GROUP BY u.id
   ...> HAVING count < 6;
担当者なし|4
絵文字 三郎|5
課題 次郎|5

スクリプトでSQLを実行する場合

全部スクリプトで済ませたい場合、次のようにも記述することができます。バッチ処理のような使い方をしたい場合を想定しています。

  1 #!/usr/bin/env ruby
  2
  3 require_relative 'lib/backlog'
  4
  5 require 'dotenv'
  6 Dotenv.load
  7
  8 api_url = ENV['BACKLOG_API_URL']
  9 api_key = ENV['BACKLOG_API_KEY']
 10
 11 api = Backlog::Client.new(api_url, api_key)
 12 project_id = api.projects.filter { |project| project['name'] == 'main' }.first['id']
 13
 14 Backlog::Query.api_url = api_url
 15 Backlog::Query.api_key = api_key
 16
 17 Backlog::Query.context do |ctx|
 18   ctx.project_id = project_id
 19
 20   ctx.fetch_issue_types
 21   ctx.fetch_statuses
 22   ctx.fetch_issues({'count' => 100})
 23
 24   sql =<<-SQL
 25 SELECT coalesce(u.name, '担当者なし') as '担当者', count(*) FROM issues i
 26 LEFT OUTER JOIN users u
 27 ON i.assignee_id  = u.id
 28 GROUP BY u.id;
 29 SQL
 30   ctx.execute(sql) do |row|
 31     p row
 32   end
 33 end
 34

24行目: SQLを記述し、30行目でそのSQLを実行します。結果は行ごとに取得することができます。このサンプルでは結果を表示するだけですが、SQLの結果を加工することもできます。

最後に

Backlogを分析するための前処理を行うライブラリと使い方を紹介してきました。まだまだ至らない点がありますが、SQLで簡単な分析はできることが分かりました（多分）。他にもBacklog APIからマイルストーンや予定/実績などを取得できるようになったりすると工数の分析できそうで、もっと分析らしいことができるのかなと思います。また、きちんと分析するにはデータを取得するだけではなくSQLで分析しやすいデータ構造にしてあげることも重要かなと思いました。APIの戻りで返ってくるレスポンスをそのまま保存するのではなく、きちんとSQLで扱いやすいデータ構造にしてあげる。それだけでも一段と活用がしやすい感じになると思いました。ありきたりなまとめになったとは思いますが、自分として経験になりました。

技術メモ