Comment by minimaxir | Hacker News Reader

Comment by minimaxir | Hacker News Reader

Parent Full thread

The GitHub Archive dataset was updated as well. Example BigQuery to get the Top Repositories from 2015-2016 YTD, by the number of Stars given during that time:

   SELECT repo.id, repo.name, COUNT(*) as num_stars
   FROM TABLE_DATE_RANGE([githubarchive:day.], TIMESTAMP('2015-01-01'), TIMESTAMP('2016-12-31'))
   WHERE type = "WatchEvent"
   GROUP BY repo.id, repo.name
   ORDER BY num_stars DESC
   LIMIT 1000

Which results in this output: https://docs.google.com/spreadsheets/d/16yDS2wDdDOTxjVsjGvWm...

Since the query only hits 3 columns, it only uses 15.4GB of data (out of a 1TB allowance)

More information on the GitHub Archive changes: https://medium.com/@hoffa/github-archive-fully-updated-notic...

Github Easter Egg hunt query:

    SELECT
      CONCAT("https://github.com/",repo_name,"/blob/master/",path) AS file_url,
    FROM
      [bigquery-public-data:github_repos.files]
    WHERE
      id IN (SELECT id FROM [bigquery-public-data:github_repos.contents]
      WHERE NOT binary AND LOWER(content) CONTAINS 'easter egg')
      and path not like "%.csv"
    GROUP BY 1
    LIMIT 1000

36s elapsed, 1.79 TB (so not free). Using github_repos.sample_files and github_repos.sample_contents only costs 31 GB (free) but not as many easter eggs :)

Just looked at their pricing. ~$9 query right there.

https://cloud.google.com/bigquery/pricing

Good point. Here's the results if anyone wants them:

https://raw.githubusercontent.com/bduerst/GithubEasterEgg/ma...

On my phone, but what if you limit 2000 inside the .contents query? (BigQuery should exit early and touch less data).

Changing the return limit does nothing. Queries are charged on data searched.

Why do you use BigQuery when Github code search is free?

Reply on news.ycombinator.com