Diving into GitHub with BigQuery and Python

!pip install google-cloud-bigquery

# Follow these instructions to create a service account key:
# https://cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries
# Then, replace the string below with the path to your service account key

export GOOGLE_APPLICATION_CREDENTIALS='/path/to/your/service-account-key.json'

GOOGLE_CLOUD_PROJECT_ID = "google-cloud-project-id"
GITHUB_USERNAME = 'crwilcox'
START_DATE = "2020-08-01"
END_DATE = "2020-09-01"

START_DATE_SUFFIX = START_DATE[2:].replace('-', '')
END_DATE_SUFFIX = END_DATE[2:].replace('-', '')

from google.cloud import bigquery

client = bigquery.client.Client(project=GOOGLE_CLOUD_PROJECT_ID)

# Gather all events taken by a particular GitHub user
query = f"""SELECT type, event AS status, COUNT(*) AS count
FROM (
  SELECT type, repo.name as repository, actor.login,
         JSON_EXTRACT(payload, '$.action') AS event, payload, created_at
  FROM `githubarchive.day.20*`
  WHERE actor.login = '{GITHUB_USERNAME}' AND
        created_at BETWEEN TIMESTAMP('{START_DATE}') AND
        TIMESTAMP('{END_DATE}') AND
        _TABLE_SUFFIX BETWEEN '{START_DATE_SUFFIX}' AND
        '{END_DATE_SUFFIX}'
)
GROUP BY type, status ORDER BY type, status;
"""

# Estimating the bytes processed by the previous query.
job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
query_job = client.query(
    query,
    job_config=job_config,
)

gb_processed = query_job.total_bytes_processed / (1024*1024*1024)
print("This query will process {} GB.".format(gb_processed))


This query will process 222.74764717370272 GB.

query_job = client.query(query)
result = query_job.result()
result.to_dataframe()

.dataframe tbody tr th:only-of-type {
    vertical-align: middle;
}

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

query = f"""
SELECT repository, type, event AS status, COUNT(*) AS count
FROM (
  SELECT type, repo.name as repository, actor.login,
         JSON_EXTRACT(payload, '$.action') AS event, payload, created_at
  FROM `githubarchive.day.20*`
  WHERE actor.login = '{GITHUB_USERNAME}' AND
        created_at BETWEEN TIMESTAMP('{START_DATE}') AND
        TIMESTAMP('{END_DATE}') AND
        _TABLE_SUFFIX BETWEEN '{START_DATE_SUFFIX}' AND
        '{END_DATE_SUFFIX}'
)
GROUP BY repository, type, status ORDER BY repository, type, status;
"""

query_job = client.query(query)
results = [i for i in query_job.result()]

# Releases made
count = [int(row.count) for row in results
         if row.type == 'ReleaseEvent']
print(f"{sum(count)} Releases across {len(count)} repos")

# PRs Made
count = [int(row.count) for row in results
         if row.type == 'PullRequestEvent' and
         row.status == "\"opened\""]
print(f"{sum(count)} PRs opened across {len(count)} repos")

# PR Comments Left
count = [int(row.count) for row in results
         if row.type == 'PullRequestReviewCommentEvent']
print(f"{sum(count)} PR comments across {len(count)} repos")

# Issues Created
count = [int(row.count) for row in results
         if row.type == 'IssuesEvent' and
         row.status == "\"opened\""]
print(f"{sum(count)} issues opened across {len(count)} repos")

# Issues Closed
count = [int(row.count) for row in results
         if row.type == 'IssuesEvent' and
         row.status == "\"closed\""]
print(f"{sum(count)} issues closed across {len(count)} repos")

# Issue Comments
count = [int(row.count) for row in results
         if row.type == 'IssueCommentEvent']
print(f"{sum(count)} issue comments across {len(count)} repos")

# Push Events
count = [int(row.count) for row in results
         if row.type == 'PushEvent']
print(f"{sum(count)} pushes across {len(count)} repos")


0 Releases across 0 repos
3 PRs opened across 3 repos
61 PR comments across 8 repos
6 issues opened across 3 repos
2 issues closed across 1 repos
17 issue comments across 9 repos
78 pushes across 13 repos

# How often do I forget to run the tests before committing?
query = f"""
  SELECT type, repo.name as repository, JSON_EXTRACT(payload, '$.commits') as commits,
        actor.login, created_at
  FROM `githubarchive.day.20*`
  WHERE actor.login = '{GITHUB_USERNAME}' AND type = "PushEvent"
        AND created_at BETWEEN TIMESTAMP('{START_DATE}') AND
        TIMESTAMP('{END_DATE}') AND
        _TABLE_SUFFIX BETWEEN '{START_DATE_SUFFIX}' AND
        '{END_DATE_SUFFIX}'
"""

query_job = client.query(query)
result = query_job.result()
df = result.to_dataframe()
df

.dataframe tbody tr th:only-of-type {
    vertical-align: middle;
}

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

df["commits"][0]


'[{"sha":"ce97f5e939bcdca1c9c46f472f41ead04ac6b2fe","author":{"name":"Chris Wilcox","email":"1a61e7a0041d068722f1c352424109b22f854ce0@google.com"},"message":"fix: lint","distinct":true,"url":"https://api.github.com/repos/crwilcox/python-firestore/commits/ce97f5e939bcdca1c9c46f472f41ead04ac6b2fe"}]'


len(df[df['commits'].str.contains("lint")])

import json
from collections import Counter

commit_types = Counter()

types = [
  "fix", "feat", "chore", "docs", "style",
  "refactor", "perf", "test", "revert"
]
for i in range(len(df)):
  commits = df.loc[i, "commits"]
  json_commits = json.loads(commits)
  for commit in json_commits:
    # If the first line contains a : assume the left side is the type.
    found_type = False
    for t in types:
      if commit['message'].startswith(t):
        commit_types[t] += 1
        found_type = True
        break
    else:
      commit_types["non-conventional"] += 1

commit_types


Counter({'chore': 21,
         'docs': 3,
         'feat': 14,
         'fix': 26,
         'non-conventional': 107,
         'refactor': 8,
         'test': 2})

GITHUB_ORGANIZATION = 'googleapis' #@param {type:"string"}
GITHUB_REPOSITORY = 'python-%' #@param {type:"string"}

# Get all GitHub issues in a repository. In the example, a wildcard is used
# to get all issues 

query = f"""
  SELECT type, repo.name as repository, JSON_EXTRACT(payload, '$.pull_request.title') as title, actor.login, 
         JSON_EXTRACT(payload, '$.action') AS event, JSON_EXTRACT(payload, '$.pull_request.html_url') as url, created_at
  FROM `githubarchive.day.20*`
  WHERE repo.name LIKE '{GITHUB_ORGANIZATION}/{GITHUB_REPOSITORY}' AND type = "PullRequestEvent"
        AND created_at BETWEEN TIMESTAMP('{START_DATE}') AND
        TIMESTAMP('{END_DATE}') AND
        _TABLE_SUFFIX BETWEEN '{START_DATE_SUFFIX}' AND
        '{END_DATE_SUFFIX}'
"""

query_job = client.query(query)
result = query_job.result()
result.to_dataframe()

# Get all GitHub issues by this login
query = f"""
  SELECT type, repo.name as repository, JSON_EXTRACT(payload, '$.issue.title') as title, actor.login,
         JSON_EXTRACT(payload, '$.action') AS event, JSON_EXTRACT(payload, '$.issue.html_url') as url, created_at
  FROM `githubarchive.day.20*`
  WHERE actor.login = '{GITHUB_USERNAME}' AND type = "IssuesEvent"
        AND created_at BETWEEN TIMESTAMP('{START_DATE}') AND
        TIMESTAMP('{END_DATE}') AND
        _TABLE_SUFFIX BETWEEN '{START_DATE_SUFFIX}' AND
        '{END_DATE_SUFFIX}'
"""

query_job = client.query(query)
result = query_job.result()
result.to_dataframe()

# Get all issue comments
query = f"""
  SELECT type, repo.name as repository, actor.login,
       JSON_EXTRACT(payload, '$.action') AS event, JSON_EXTRACT(payload, '$.issue.html_url') as url, created_at
  FROM `githubarchive.day.20*`
  WHERE actor.login = '{GITHUB_USERNAME}' AND type = "IssueCommentEvent"
      AND created_at BETWEEN TIMESTAMP('{START_DATE}') AND
        TIMESTAMP('{END_DATE}') AND
        _TABLE_SUFFIX BETWEEN '{START_DATE_SUFFIX}' AND
        '{END_DATE_SUFFIX}'
"""
query_job = client.query(query)
result = query_job.result()
result.to_dataframe()

# Get all PR comments created by this login
query = f"""
  SELECT type, repo.name as repository, actor.login,
         JSON_EXTRACT(payload, '$.action') AS event, JSON_EXTRACT(payload, '$.comment.html_url') as url, created_at
  FROM `githubarchive.day.20*`
  WHERE actor.login = '{GITHUB_USERNAME}' AND type = "PullRequestReviewCommentEvent"
        AND created_at BETWEEN TIMESTAMP('{START_DATE}') AND
        TIMESTAMP('{END_DATE}') AND
        _TABLE_SUFFIX BETWEEN '{START_DATE_SUFFIX}' AND
        '{END_DATE_SUFFIX}'
"""

query_job = client.query(query)
result = query_job.result()
result.to_dataframe()