Gradle BigQueries for analyzing usage

gradle-build-scripts.sql

-- Save to build-scripts table
SELECT
  f.id id,
  f.repo_name repo,
  f.path path
FROM
  [bigquery-public-data:github_repos.files] f
WHERE
  f.path LIKE '%.gradle%'

gradle-imports.sql

-- save to imports table
SELECT
  id,
  content
FROM
  [bigquery-public-data:github_repos.contents]
WHERE
  content CONTAINS 'import org.gradle'

gradle-internal-api-usage.sql

SELECT
  class,
  COUNT(*) count
FROM (
  SELECT
    REGEXP_REPLACE(line, r'import |;', '') AS class,
    c.id
  FROM (
    SELECT
      SPLIT(c.content, '\n') line,
      c.id
    FROM
      [gradle.gradle_imports] c
    JOIN
      [bigquery-public-data:github_repos.files] f
    ON
      c.id = f.id
    WHERE
      f.path LIKE '%.java'
        OR f.path LIKE '%.groovy'
        OR f.path LIKE '%.gradle'
    HAVING
      LEFT(line, 17)='import org.gradle' )
  GROUP BY
    class,
    c.id )
WHERE
  NOT REGEXP_MATCH(class, r'org.gradle.(api|authentication|buildinit|caching|external|javadoc|ide|ivy|jvm|language|maven|nativeplatform|platform|play|plugin.devel|plugin.repository|plugin.use|plugins|process|testfixtures|testing.jacoco|tooling|model|testkit|testing).')
  OR class CONTAINS '.internal'
GROUP BY
  1
ORDER BY
  count DESC
LIMIT
  1000;

gradle-language-distribution.sql

-- save to languages table
SELECT
  repos.repo,
  langs.language.name AS language_name,
  langs.language.bytes AS language_bytes
FROM
  builds.repos repos
LEFT JOIN
  [bigquery-public-data.github_repos.languages] langs
ON
  repos.repo = langs.repo_name
WHERE
  langs.language.name IS NOT NULL

gradle-new-monthly-android-repos.sql

SELECT
  STRING(YEAR(created_date)) + '-' + RIGHT('00' + STRING(MONTH(created_date)), 2) + '-15' AS month,
  COUNT(DISTINCT project_name) AS projects
FROM
  [builds.repos_20170614]
WHERE
  created_date > '2016-01-01 00:00'
  AND created_date < '2017-06-01 00:00'
  AND build_tool = 'Gradle'
  AND repo IN (
  SELECT
    repo
  FROM
    [gradle.contents_20170614]
  WHERE
    path = 'build.gradle'
    AND content CONTAINS 'com.android'
  GROUP BY
    repo)
GROUP BY
  1
ORDER BY
  1 ASC

gradle-repos.sql

-- save to repos table
SELECT
  repo_name AS repo,
  LAST(SPLIT(repo_name, '/')) AS project_name,
  created_date,
  'Gradle' AS build_tool
FROM (
  SELECT
    repo_name,
    MIN(committer.date) AS created_date
  FROM
    FLATTEN([bigquery-public-data:github_repos.commits],repo_name)
  WHERE
    repo_name IN (
    SELECT
      repo_name
    FROM
      [bigquery-public-data:github_repos.files]
    WHERE
      path = 'build.gradle'
    GROUP BY
      repo_name)
  GROUP BY
    repo_name
  HAVING
    COUNT(committer.date) >= 5) AS t