LoginSignup
3
1

More than 5 years have passed since last update.

@tarosaibainフューチャー株式会社

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

Last updated at Posted at 2018-07-28

はじめに

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

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

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

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

イメージ

bq-01.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を発行します

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

解説

  • GITHUB_USERNAME : 自分のアカウント名を設定してください
$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行ずつ) の形式で出力されます。

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 でも指定可能です。 公式マニュアル

  • NUM_OF_PAGES は自RepogitoryのIssues数に合わせて適当な数値を設定してください。 (大きく設定しても問題ないです)

    • Github APIでは、取得件数がpage毎に 100 という制限があるため、pageを分けて実行する必要があります

  • curlの出力をパイプでjq -c '.[]' に渡して、NEWLINE_DELIMITED_JSON に変更しています

    • BigQueryに読み込ませるため
$ ./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の情報全ては定義していないため、他に必要な情報があれば以下の定義に追加する必要があります
bq mk --schema ./schema_github_issue.json  github_analysis.table_issues

解説

  • --schema option で SCHEMAを定義したJSONファイルを指定しています(後述ファイル)
  • github_analysis : Dataset で、 table_issues : Table です。ここは好きに変えてOKです
$ bq mk --schema ./schema_github_issue.json  github_analysis.table_issues
Table 'xxxxxxxxxxxx:github_analysis.test_table_issues' successfully created.
  • 以下をコピペして使用してください
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" で自由にデータを扱ってください。

bq-02.png

さいごに

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1