How to search github projects ordered by number of commits?

Question

I was thinking of trying out BigQuery and GithubArchive, but I'm not sure how to compose a query that would let me search for a term in code or project and order the results by number of commits descending.

Thanks for any tips

Answer 1

The GithubArchive data loaded into BigQuery doesn't have copy of the source code, so search term in code wouldn't be possible. But if you wanted to search for a term in repository description, and then pick top repositories by number of commits, here is an example how to do it (the term is "SQL" in this example):

select count(*) c, repository_url, repository_description
from [githubarchive:github.timeline]
where type = 'PushEvent' and repository_description contains 'SQL'
group by 2, 3
order by c desc
limit 10

This results in

14925   https://github.com/danberindei/infinispan   Infinispan is an open source data grid platform and highly scalable NoSQL cloud data store.  
9377    https://github.com/postgres/postgres    Mirror of the official PostgreSQL GIT repository. Note that this is just a *mirror* - we don't work with pull requests on github. To contribute, please see http://wiki.postgresql.org/wiki/Submitting_a_Patch   
4876    https://github.com/galderz/infinispan   Infinispan is an open source data grid platform and highly scalable NoSQL cloud data store.  
4747    https://github.com/triAGENS/ArangoDB    ArangoDB is a multi-purpose, open-source database with flexible data models for documents, graphs, and key-values. Build high performance applications using a convenient SQL-like query language or JavaScript/Ruby extensions. Use ACID transaction if you require them. Scale horizontally and vertically with a few mouse clicks.    
3590    https://github.com/webnotes/erpnext Open Source, web-based ERP based on Python, Javascript and MySQL.    
3489    https://github.com/anistor/infinispan   Infinispan is an open source data grid platform and highly scalable NoSQL cloud data store.  
3263    https://github.com/youtube/vitess   vitess provides servers and tools which facilitate scaling of MySQL databases for large scale web services.  
3071    https://github.com/infinispan/infinispan    Infinispan is an open source data grid platform and highly scalable NoSQL cloud data store.  
2631    https://github.com/theory/sqitch    Simple SQL change management     
2358    https://github.com/zzzeek/sqlalchemy    Mirror of SQLAlchemy

Answer 2

SELECT COUNT(1) c, repository_url, repository_description
FROM [githubarchive:github.timeline]
WHERE type = 'PushEvent' 
AND REGEXP_MATCH(repository_description, r'(?i)SQL')
GROUP BY 2, 3
ORDER BY c DESC
LIMIT 10

BigQuery supports Regular Expression so you can greatly improve / narrow down your search result having flexibility of using search pattern vs. seach term

Below references can help you further:

BigQuery Regular expression functions
re2 Syntax