Github IssuesをGoogle BigQueryにインポートしてExcelで一覧化する

# はじめに

Github、使っていますか？
私のチームはエンプラプロジェクトですが、Githubを使用しています。もちろんIssueでタスク管理しています。

**「IssueをExcelで管理したいんだけどできない！？」**

と、プロジェクトマネージャーから言われたので、今回実装しました。

いつの時代もExcelは頼りになりますよね。(ちょい皮肉)
ただ、JSON→CSV→Excel化するのでは時代の逆戻りを後押ししてしまうので、"Google BigQuery" で解析できるようにしました。

# イメージ

<img width="1678" alt="bq-01.png" src="https://qiita-image-store.s3.amazonaws.com/0/108052/1b28d73c-306b-7aec-73b7-7212cb08300f.png">

# やること

1. Github APIを用いて、Issue一覧をJSONでExport
2. ExportしたJSONをBigQueryに取り込む
3. あとは必要な情報だけクエリを書いて抽出して、"Datastudio" でも "スプレッドシート" でも "Excel" 好きに扱えます。 
  - Excel上でではなく、クエリで分析できるのは嬉しいですね！


# 1. Github APIを用いて、Issue一覧をJSONでExport

## 1.1　Github APIのTOKEN発行

まずは、Github APIを使用するために以下のcurlでTOKENを発行します

```bash
curl -u 'GITHUB_USERNAME' -d '{"scopes":["repo"],"note":"issue_export"}'  https://api.github.com/authorizations
```

### 解説
* `GITHUB_USERNAME` : 自分のアカウント名を設定してください

```bash
$curl -u 'GITHUB_USERNAME' -d '{"scopes":["repo"],"note":"issue_export"}'  https://api.github.com/authorizations
Enter host password for user 'GITHUB_USERNAME':
{
  "app": {
    "name": "Help example (API)",
    "url": "http://developer.github.com/v3/oauth/#oauth-authorizations-api"
  },
  "scopes": [
    "repo"
  ],
  "created_at": "20xx-xx-xxTxx:xx:Z",
  "note_url": null,
  "note": "issue_export",
  "token": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "updated_at": "20xx-xx-xxTxx:xx:Z",
  "id": xxxxxx,
  "url": "https://api.github.com/authorizations/xxxxxx"
}
```

## 1.2　Issue取得のAPIをコール

以下のスクリプト(get_issues.sh)を実行すれば、issues.jsonに対象のRepogitoryのIssueが、"issues.json"に、NEWLINE_DELIMITED_JSON (1行ずつ) の形式で出力されます。

```bash:get_issues.sh
#!/bin/bash

REPO="hoge-organization/fuga-reponame"
TOKEN="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
ISSUES_LIST="./issues.json"
NUM_OF_PAGES="30"


# Initialize issue list file
if [ -f ${ISSUES_LIST} ]; then
    rm ${ISSUES_LIST}
else
    touch ${ISSUES_LIST}
fi

# Get Issue list
for i in `seq 1 ${NUM_OF_PAGES}`
do
    echo "${i}"
    curl -H "Authorization: token ${TOKEN}" https://api.github.com/repos/${REPO}/issues\?state\=all\&page\=${i}\&per_page\=100\&sort\=created \
        | jq -c '.[]' >> ${ISSUES_LIST}
done
```

### 解説
* `TOKEN` には上記操作で取得した値を設定してください

* 今回は `state=all` で指定していますが、 `closed` や `open` でも指定可能です。 [公式マニュアル](https://developer.github.com/v3/issues/)

* `NUM_OF_PAGES` は自RepogitoryのIssues数に合わせて適当な数値を設定してください。 (大きく設定しても問題ないです)
  - Github APIでは、取得件数がpage毎に `100` という制限があるため、pageを分けて実行する必要があります

* curlの出力をパイプで`jq -c '.[]'` に渡して、NEWLINE_DELIMITED_JSON に変更しています
  - BigQueryに読み込ませるため

```bash
$ ./get_issues.sh
1
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  405k  100  405k    0     0   198k      0  0:00:02  0:00:02 --:--:--  198k
2
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  405k  100  405k    0     0   195k      0  0:00:02  0:00:02 --:--:--  195k
3
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  500k  100  500k    0     0   249k      0  0:00:02  0:00:02 --:--:--  249k
(..)
# curl コマンドが繰り返されます
```

# 2. ExportしたJSONをBigQueryに取り込む

## 2.1　BigQueryのSCHEMA定義

* BigQueryにIssue一覧のJSONを読み込むために、事前にSCHEMAを定義します
  - ※ BigQuery読み込み時ののAUTODETECT機能を使用しても良いのですが、私が取り込んだ際はIssueの数が大きくなると取り込みでERRORがでてしまったため、明示的に作成しています。(特異な構造のデータがあったのか、原因は不明)
  - ※ Issueの情報全ては定義していないため、他に必要な情報があれば以下の定義に追加する必要があります

```bash
bq mk --schema ./schema_github_issue.json  github_analysis.table_issues
```

### 解説
* --schema option で SCHEMAを定義したJSONファイルを指定しています(後述ファイル)
* github_analysis : Dataset で、 table_issues : Table です。ここは好きに変えてOKです


```bash
$ bq mk --schema ./schema_github_issue.json  github_analysis.table_issues
Table 'xxxxxxxxxxxx:github_analysis.test_table_issues' successfully created.
```

* 以下をコピペして使用してください

```json:schema_github_issue.json 
[
  {
    "name": "url",
    "type": "STRING"
  },
  {
    "name": "repository_url",
    "type": "STRING"
  },
  {
    "name": "labels_url",
    "type": "STRING"
  },
  {
    "name": "comments_url",
    "type": "STRING"
  },
  {
    "name": "events_url",
    "type": "STRING"
  },
  {
    "name": "html_url",
    "type": "STRING"
  },
  {
    "name": "id",
    "type": "INTEGER"
  },
  {
    "name": "node_id",
    "type": "STRING"
  },
  {
    "name": "number",
    "type": "INTEGER"
  },
  {
    "name": "title",
    "type": "STRING"
  },
  {
    "name": "user",
    "type": "RECORD",
    "fields": [
      {
        "name": "login",
        "type": "STRING"
      },
      {
        "name": "id",
        "type": "INTEGER"
      },
      {
        "name": "node_id",
        "type": "STRING"
      },
      {
        "name": "avatar_url",
        "type": "STRING"
      },
      {
        "name": "gravatar_id",
        "type": "STRING"
      },
      {
        "name": "url",
        "type": "STRING"
      },
      {
        "name": "html_url",
        "type": "STRING"
      },
      {
        "name": "followers_url",
        "type": "STRING"
      },
      {
        "name": "following_url",
        "type": "STRING"
      },
      {
        "name": "gists_url",
        "type": "STRING"
      },
      {
        "name": "starred_url",
        "type": "STRING"
      },
      {
        "name": "subscriptions_url",
        "type": "STRING"
      },
      {
        "name": "organizations_url",
        "type": "STRING"
      },
      {
        "name": "repos_url",
        "type": "STRING"
      },
      {
        "name": "events_url",
        "type": "STRING"
      },
      {
        "name": "received_events_url",
        "type": "STRING"
      },
      {
        "name": "type",
        "type": "STRING"
      },
      {
        "name": "site_admin",
        "type": "BOOLEAN"
      }
    ]
  },
  {
    "name": "labels",
    "type": "RECORD",
    "mode": "repeated",
    "fields": [
      {
        "name": "id",
        "type": "INTEGER"
      },
      {
        "name": "node_id",
        "type": "STRING"
      },
      {
        "name": "url",
        "type": "STRING"
      },
      {
        "name": "name",
        "type": "STRING"
      },
      {
        "name": "color",
        "type": "STRING"
      },
      {
        "name": "default",
        "type": "BOOLEAN"
      }
    ]
  },
  {
    "name": "state",
    "type": "STRING"
  },
  {
    "name": "locked",
    "type": "BOOLEAN"
  },
  {
    "name": "assignee",
    "type": "record",
    "fields": [
      {
        "name": "login",
        "type": "STRING"
      },
      {
        "name": "id",
        "type": "INTEGER"
      },
      {
        "name": "node_id",
        "type": "STRING"
      },
      {
        "name": "avatar_url",
        "type": "STRING"
      },
      {
        "name": "gravatar_id",
        "type": "STRING"
      },
      {
        "name": "url",
        "type": "STRING"
      },
      {
        "name": "html_url",
        "type": "STRING"
      },
      {
        "name": "followers_url",
        "type": "STRING"
      },
      {
        "name": "following_url",
        "type": "STRING"
      },
      {
        "name": "gists_url",
        "type": "STRING"
      },
      {
        "name": "starred_url",
        "type": "STRING"
      },
      {
        "name": "subscriptions_url",
        "type": "STRING"
      },
      {
        "name": "organizations_url",
        "type": "STRING"
      },
      {
        "name": "repos_url",
        "type": "STRING"
      },
      {
        "name": "events_url",
        "type": "STRING"
      },
      {
        "name": "received_events_url",
        "type": "STRING"
      },
      {
        "name": "type",
        "type": "STRING"
      },
      {
        "name": "site_admin",
        "type": "BOOLEAN"
      }
    ]
  },
  {
    "name": "assignees",
    "type": "record",
    "mode": "repeated",
    "fields": [
      {
        "name": "login",
        "type": "STRING"
      },
      {
        "name": "id",
        "type": "INTEGER"
      },
      {
        "name": "node_id",
        "type": "STRING"
      },
      {
        "name": "avatar_url",
        "type": "STRING"
      },
      {
        "name": "gravatar_id",
        "type": "STRING"
      },
      {
        "name": "url",
        "type": "STRING"
      },
      {
        "name": "html_url",
        "type": "STRING"
      },
      {
        "name": "followers_url",
        "type": "STRING"
      },
      {
        "name": "following_url",
        "type": "STRING"
      },
      {
        "name": "gists_url",
        "type": "STRING"
      },
      {
        "name": "starred_url",
        "type": "STRING"
      },
      {
        "name": "subscriptions_url",
        "type": "STRING"
      },
      {
        "name": "organizations_url",
        "type": "STRING"
      },
      {
        "name": "repos_url",
        "type": "STRING"
      },
      {
        "name": "events_url",
        "type": "STRING"
      },
      {
        "name": "received_events_url",
        "type": "STRING"
      },
      {
        "name": "type",
        "type": "STRING"
      },
      {
        "name": "site_admin",
        "type": "BOOLEAN"
      }
    ]
  },
  {
    "name": "milestone",
    "type": "RECORD",
    "fields": [
      {
        "name": "url",
        "type": "STRING"
      },
      {
        "name": "html_url",
        "type": "STRING"
      },
      {
        "name": "labels_url",
        "type": "STRING"
      },
      {
        "name": "id",
        "type": "INTEGER"
      },
      {
        "name": "node_id",
        "type": "STRING"
      },
      {
        "name": "number",
        "type": "INTEGER"
      },
      {
        "name": "title",
        "type": "STRING"
      },
      {
        "name": "description",
        "type": "STRING"
      },
      {
        "name": "creator",
        "type": "RECORD",
        "fields": [
          {
            "name": "login",
            "type": "STRING"
          },
          {
            "name": "id",
            "type": "INTEGER"
          },
          {
            "name": "node_id",
            "type": "STRING"
          },
          {
            "name": "avatar_url",
            "type": "STRING"
          },
          {
            "name": "gravatar_id",
            "type": "STRING"
          },
          {
            "name": "url",
            "type": "STRING"
          },
          {
            "name": "html_url",
            "type": "STRING"
          },
          {
            "name": "followers_url",
            "type": "STRING"
          },
          {
            "name": "following_url",
            "type": "STRING"
          },
          {
            "name": "gists_url",
            "type": "STRING"
          },
          {
            "name": "starred_url",
            "type": "STRING"
          },
          {
            "name": "subscriptions_url",
            "type": "STRING"
          },
          {
            "name": "organizations_url",
            "type": "STRING"
          },
          {
            "name": "repos_url",
            "type": "STRING"
          },
          {
            "name": "events_url",
            "type": "STRING"
          },
          {
            "name": "received_events_url",
            "type": "STRING"
          },
          {
            "name": "type",
            "type": "STRING"
          },
          {
            "name": "site_admin",
            "type": "BOOLEAN"
          }
        ]
      },
      {
        "name": "open_issues",
        "type": "INTEGER"
      },
      {
        "name": "closed_issues",
        "type": "INTEGER"
      },
      {
        "name": "state",
        "type": "STRING"
      },
      {
        "name": "created_at",
        "type": "STRING"
      },
      {
        "name": "updated_at",
        "type": "STRING"
      },
      {
        "name": "due_on",
        "type": "STRING"
      },
      {
        "name": "closed_at",
        "type": "STRING"
      }
    ]
  },
  {
    "name": "comments",
    "type": "INTEGER"
  },
  {
    "name": "created_at",
    "type": "STRING"
  },
  {
    "name": "updated_at",
    "type": "STRING"
  },
  {
    "name": "closed_at",
    "type": "STRING"
  },
  {
    "name": "author_association",
    "type": "STRING"
  },
  {
    "name": "body",
    "type": "STRING"
  }
]

```

* 注意: 以下はRecord形式です (ちゃんと定義すればBigQueryでよしなに取り込んでくれます)
  - user
  - labels
  - milestone
  - assignee
  - assignees


## 2.2 BigQueryへのLoad

以下のコマンドで、先程出力したissues.jsonをloadします

```
bq load --replace --ignore_unknown_values --max_bad_records=10 --source_format=NEWLINE_DELIMITED_JSON github_analysis.table_issues ./issues.json
```

### 解説

* `--replace` : Issueの状態が更新されたら毎回洗い替えしないとなので、`--replace` optionを使用しています
* `--ignore-unknown_valuses` & `--max_bad_records=10`  : たまに変な値が入ってきてしまうので無視する optionです。 bad_recordは適宜変更してください
* `--source_format` : 読み取るファイル形式を指定しています。今回用意したのは NEWLINE_DELIMITED_JSON です
* `github_analysis.table_issues` : load先のtableです
* `./issues.json` : Issue一覧のファイルです


# 3. あとは必要な情報だけクエリを書いて抽出して、"Datastudio" でも "スプレッドシート" でも "Excel" 好きに扱えます。 

赤枠部分の、"好きな形にExport" OR "EXPLORE IN DATASTUDIO" で自由にデータを扱ってください。

<img width="1677" alt="bq-02.png" src="https://qiita-image-store.s3.amazonaws.com/0/108052/c434e596-9b65-4e68-7ad2-f8deb8e29d5d.png">


# さいごに

* これをスクリプトにして、定期実行すれば、最新のIssue状況がBigQueryに反映されるようになります
* BigQueryは "View" の定義もできるので、 Datastdioでダッシュボードを作成すれば、準リアルタイムで開発状況がわかるプロジェクトマネージャーが喜ぶツールに変身です！