MaxCompute:GitHub public event data

Sample query statements:

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: The most popular open source database projects in the previous five years are elasticsearch, redis, and prometheus in descending order.

Sample query statements:

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: In the previous five years, the top 10 most popular open source database projects frequently change. The clickhouse project has the fastest rise in ranking, from the 10th place in 2018 to the first place in 2021. In 2022, the redis project surpassed the clickhouse project. The taosdata/TDengine project ranked first in 2019 but dropped to the ninth place in 2020.

Sample query statements:

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: The elasticsearch project is the most popular open source database in the top 10 open source database projects, and the number of stars for the clickhouse project rapidly grew since 2021.

Sample query statements:

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: The most popular databases in the first half of 2023 were clickhouse, prometheus, and redis in descending order.

Sample query statements:

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, the StarRocks database is most actively maintained and updated. A database is considered active if PullRequest is enabled.

Sample query statements:

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 leading individual contributor to the most active open source database project.

Sample query statements:

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: In the previous five years, the most popular programming language projects were Go, with 81,642 stars, TypeScript, and Node in descending order.

Sample query statements:

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: In the previous five years, Go was the programming language whose number of stars increased the most rapidly.