Play with GitHub Archive dataset on BigQuery

Play with GitHub Archive dataset on BigQuery

Google BigQuery is a web service that lets you do interactive analysis of very massive datasets - analyzing billions of rows in seconds.

ref:
https://www.githubarchive.org/#bigquery
https://bigquery.cloud.google.com/table/githubarchive:month.201612

See also:
http://ghtorrent.org/

Show repository informations (1)

WITH repo_info AS (
  SELECT repo.id AS id, repo.name AS name, JSON_EXTRACT_SCALAR(payload, '$.pull_request.base.repo.description') AS description
  FROM `githubarchive.month.2017*`
  -- FROM `githubarchive.year.2016`
  -- FROM `githubarchive.year.*`
  WHERE type = "PullRequestEvent"
)

SELECT repo_info.name, ANY_VALUE(repo_info.description) AS description
FROM repo_info
WHERE
  repo_info.description IS NOT NULL AND
  repo_info.description != ""
GROUP BY repo_info.name
ORDER BY repo_info.name

ref:
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#json-functions
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#any_value

Show repository informations (2)

WITH repo_info AS (
  SELECT repo.id AS id, repo.name AS name, JSON_EXTRACT_SCALAR(payload, '$.description') AS description
  FROM `githubarchive.month.201708`
  WHERE type = "CreateEvent"
)

SELECT repo_info.name, ANY_VALUE(repo_info.description) AS description
FROM repo_info
WHERE
  repo_info.description IS NOT NULL AND
  repo_info.description != ""
GROUP BY repo_info.name
ORDER BY repo_info.name

Show repository informations (3)

SELECT name, description
FROM `ghtorrent-bq.ght_2017_04_01.projects`
WHERE
  forked_from IS NULL AND
  description IS NOT NULL AND
  description != ""

Show starred repositories by a specific user

You must use WatchEvent for starring a repo:
https://developer.github.com/v3/activity/events/types/#watchevent

SELECT repo.name, created_at
FROM TABLE_QUERY([githubarchive:month], 'LEFT(table_ID,4) IN ("2017","2016","2015")') 
WHERE type = "WatchEvent" AND actor.login = 'vinta'
GROUP BY repo.name, created_at
ORDER BY created_at DESC

Show starred repositories per user who has 10+ starred repositories

WITH stars AS (
     SELECT DISTINCT actor.login AS user, repo.name AS repo
     FROM `githubarchive.month.2017*`
     WHERE type="WatchEvent"
),
repositories_stars AS (
     SELECT repo, COUNT(*) as c FROM stars GROUP BY repo
     ORDER BY c DESC
     LIMIT 1000
),
users_stars AS (
    SELECT user, COUNT(*) as c FROM  stars
    WHERE repo IN (SELECT repo FROM repositories_stars)
    GROUP BY user
    HAVING c >= 10
    LIMIT 10000
)
SELECT user, repo FROM stars
WHERE repo IN (SELECT repo FROM repositories_stars)
AND user IN (SELECT user FROM users_stars)

ref:
https://gist.github.com/jbochi/2e8ddcc5939e70e5368326aa034a144e

MkDocs: Deploy your Markdown documents on GitHub Pages

MkDocs: Deploy your Markdown documents on GitHub Pages

MkDocs is a static site generator that builds modern webpages based on your Markdown documents and a simple YAML file.

ref:
https://www.mkdocs.org/

Here is the website which is generated by MkDocs in this post:
https://awesome-python.com/
https://github.com/vinta/awesome-python

Installation

$ pip install mkdocs

Configuration

in mkdocs.yml

site_name: Awesome Python
site_url: https://awesome-python.com
site_description: A curated list of awesome Python frameworks, libraries and software
site_author: Vinta Chen
repo_url: https://github.com/vinta/awesome-python
theme: flatly
google_analytics: ['UA-510626-7', 'auto']
pages:
  - "Life is short, you need Python.": "index.md"

There are more themes:

in Makefile

site_install:
    pip install mkdocs==0.16.3
    pip install mkdocs-material==1.12.2

site_link:
    ln -sf $(CURDIR)/README.md $(CURDIR)/docs/index.md

site_preview: site_link
    mkdocs serve

site_deploy: site_link
    mkdocs gh-deploy --clean

Custom Domain for GitHub Pages

in docs/CNAME

awesome-python.com

After deploying your GitHub Page, just pointing your domain to 192.30.252.153 with an A DNS record.

ref:
https://help.github.com/articles/setting-up-an-apex-domain/#configuring-a-records-with-your-dns-provider