MaxCompute:GitHub public event data

Query example:

SET odps.namespace.schema = TRUE;
SELECT dws.repo_id AS repo_id,
 repos.name AS repo_name,
 SUM(dws.stars) AS stars
FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
JOIN bigdata_public_dataset.github_events.db_repos repos ON dws.repo_id = repos.id
WHERE MONTH >= '2015-01' AND MONTH <='2022-12'
GROUP BY dws.repo_id,
 repos.name
ORDER BY stars DESC LIMIT 10;

Analysis result: In the last five years, elasticsearch was the most popular open source database project, followed by redis and prometheus.

Query example:

SET odps.namespace.schema = TRUE;
SET odps.sql.validate.orderby.limit=FALSE;
 WITH tmp as
 (SELECT dws.repo_id AS repo_id, repos.name AS repo_name, SUM(dws.stars) AS stars, SUBSTR(MONTH,1,4) AS YEAR, row_number() over (partition BY SUBSTR(MONTH,1,4)
 ORDER BY SUM(dws.stars) DESC) AS ranknum
 FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
 JOIN bigdata_public_dataset.github_events.db_repos repos ON dws.repo_id = repos.id
 WHERE MONTH>='2018-01'
 GROUP BY dws.repo_id, repos.name,SUBSTR(MONTH,1,4))
SELECT repo_id,
 repo_name,
 stars,
 ranknum,
 YEAR
FROM tmp
WHERE YEAR<=2022
 AND ranknum<=10
ORDER BY YEAR ASC,ranknum ASC;

Analysis result: Over the last five years, the rankings of the top 10 open source database projects have shifted. clickhouse showed the fastest growth, rising from tenth place in 2018 to first place in 2021. In 2022, it was surpassed by the steadily rising redis. In contrast, taosdata/TDengine ranked first in 2019 but dropped to ninth place within a year.

Query example:

SET odps.namespace.schema = true;
SET odps.sql.validate.orderby.LIMIT=false;
WITH top_10_repos AS (
 SELECT
 dws.repo_id AS repo_id, 
 repos.name AS repo_name, 
 SUM(dws.stars) AS stars
 FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
 JOIN bigdata_public_dataset.github_events.db_repos repos ON dws.repo_id = repos.id
 WHERE MONTH >= '2018-01' AND MONTH <='2022-12'
 GROUP BY dws.repo_id, repos.name
 ORDER BY stars DESC
 LIMIT 10
),
tmp AS (
 SELECT
 MONTH,
 repo_id,
 stars,
 SUM(stars) OVER (PARTITION BY repo_id ORDER BY MONTH ASC ) AS total_stars
 FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month
 WHERE MONTH >= '2015-01' AND stars IS NOT NULL
 AND repo_id IN (SELECT repo_id FROM top_10_repos)
 GROUP BY repo_id,MONTH,stars
 ORDER BY MONTH ASC,repo_id)
 
SELECT
 tmp.MONTH AS MONTH,
 top_10_repos.repo_name AS repo_name,
 tmp.total_stars AS total_stars

 FROM tmp
 JOIN top_10_repos ON top_10_repos.repo_id = tmp.repo_id
 GROUP BY MONTH,repo_name,total_stars
 ORDER BY MONTH ASC,repo_name
;

Analysis result: Among the top 10 open source database projects, elasticsearch has consistently been the most popular. The clickhouse project has seen rapid growth since 2021.

Query example:

set odps.namespace.schema = true;
SELECT
 repos.name AS repo_name,
 sum(dws.stars) AS stars
FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
JOIN bigdata_public_dataset.github_events.db_repos repos ON repos.id = dws.repo_id
WHERE month >= '2023-01' AND month <= '2023-06'
GROUP BY repo_name
ORDER BY stars DESC
LIMIT 10;

Analysis result: In the first half of 2023, clickhouse was the most popular database, followed by prometheus and redis.

Query example:

SET odps.namespace.schema = true;
SELECT
 repos.name AS repo_name,
 COUNT(dwd.id) AS num
FROM bigdata_public_dataset.github_events.dwd_github_events_odps dwd
 JOIN bigdata_public_dataset.github_events.db_repos repos ON repos.id = dwd.repo_id
WHERE type = 'PullRequestEvent'
 AND ds>='2023-01-01' and ds<='2023-06-30'
 AND action = 'opened'
GROUP BY repos.name
ORDER BY num DESC
LIMIT 10;

Analysis result: In the first half of 2023, StarRocks was the most actively maintained and updated database. Project activity is defined by the number of created PullRequest events.

Query example:

SET odps.namespace.schema=true; 
WITH a AS 
(
 SELECT repo_id
 ,repo_name
 ,actor_id
 ,actor_login
 ,COUNT(*) AS contribution
 ,ds
 FROM bigdata_public_dataset.github_events.dwd_github_events_odps
 WHERE ds >='2021-01-01' and ds<='2021-12-31'
 AND (
 (
 type = 'PullRequestEvent'
 AND action = 'opened'
 )
 OR (
 type = 'IssuesEvent'
 AND action = 'opened'
 )
 OR (
 type = 'IssueCommentEvent'
 AND action = 'created'
 )
 OR (
 type = 'PullRequestReviewEvent'
 AND action = 'created'
 )
 OR (
 type = 'PullRequestReviewCommentEvent'
 AND action = 'created'
 )
 OR (
 type = 'PushEvent'
 AND action IS NULL
 )
 )
 GROUP BY repo_id
 ,repo_name
 ,actor_id
 ,actor_login
 ,ds
)
SELECT repo_name
 ,actor_login
 ,SUM(contribution) AS contribution
FROM a
WHERE repo_name = 'StarRocks/starrocks'
AND actor_login NOT LIKE '%[bot]'
AND actor_login NOT LIKE 'cockroach%'

GROUP BY repo_name
 ,actor_login
ORDER BY contribution DESC
LIMIT 10
;

Analysis result: In the first half of 2023, kangkaisen was the main individual contributor to the most active open source database project.

Query example:

SET odps.namespace.schema = TRUE;

SELECT dws.repo_id AS repo_id,
 repos.name AS repo_name,
 SUM(dws.stars) AS stars
FROM bigdata_public_dataset.github_events.dws_overview_by_repo_month dws
JOIN bigdata_public_dataset.github_events.programming_language_repos repos ON dws.repo_id = repos.id
WHERE MONTH >= '2015-01'
GROUP BY dws.repo_id,
 repos.name
ORDER BY stars DESC LIMIT 10;

Analysis result: Over the last five years, the most popular programming language project was Go, which received 81,642 stars. It was followed by TypeScript and Node.

Query example:

SET odps.namespace.schema = true;
SET odps.sql.validate.orderby.limit=false;
WITH top_10_repos AS (
 SELECT
 dws.repo_id AS repo_id, 
 repos.name AS repo_name, 
 SUM(dws.stars) AS stars
 FROM bigdata_public_dataset.github_events.dws_overview_by_repo_by_month dws
 JOIN bigdata_public_dataset.github_events.programming_language_repos repos ON dws.repo_id = repos.id
 WHERE month >= '2018-01' AND month <='2022-12'
 GROUP BY dws.repo_id, repos.name
 ORDER BY stars DESC
 LIMIT 10
),
tmp AS (
 SELECT
 month,
 repo_id,
 stars,
 SUM(stars) OVER (PARTITION by repo_id ORDER BY month ASC ) AS total_stars
 FROM bigdata_public_dataset.github_events.dws_overview_by_repo_by_month
 WHERE month >= '2015-01' AND stars IS NOT NULL
 AND repo_id IN (SELECT repo_id FROM top_10_repos)
 GROUP BY repo_id,month,stars
 ORDER BY month ASC,repo_id)
 
SELECT
 tmp.month AS month,
 top_10_repos.repo_name AS repo_name,
 tmp.total_stars AS total_stars

 FROM tmp
 JOIN top_10_repos ON top_10_repos.repo_id = tmp.repo_id
 GROUP BY month,repo_name,total_stars
 ORDER BY month ASC,repo_name
;

Analysis result: Over the last five years, Go was the programming language project with the fastest growth in popularity.