3 ms·
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:
by minimaxir 10y ago
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/16yDS2wDdDOTxjVsjGvWmpHVsOIU65wLEjXFHDtDeKU4/edit?usp=sharing 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-notice-some-breaking-changes-64e7e7cd0967 https://medium.com/@hoffa/github-archive-fully-updated-notic...
- bduerst 10y agoGithub 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 :)
- Goopplesoft 10y agoJust looked at their pricing. ~$9 query right there. https://cloud.google.com/bigquery/pricing https://cloud.google.com/bigquery/pricing
- bduerst 10y agoGood point. Here's the results if anyone wants them: https://raw.githubusercontent.com/bduerst/GithubEasterEgg/master/EasterEggs.csv https://raw.githubusercontent.com/bduerst/GithubEasterEgg/ma...
- boulos 10y agoOn my phone, but what if you limit 2000 inside the .contents query? (BigQuery should exit early and touch less data).
- minimaxir 10y agoChanging the return limit does nothing. Queries are charged on data searched.
- niutech 10y agoWhy do you use BigQuery when Github code search is free?