sql - Big Query "Quota exceeded"用于 Github 推送事件数据集的 SQL

Question

我对 Google BigQuery 还很陌生，对 SQL 也不太熟悉，我想知道你们是否可以帮助我重新格式化我的 SQL 语句以减少我的使用？因为在我当前的设置下，我遇到了这个错误:

Error: Quota exceeded: Your project exceeded quota for free query bytes scanned. For more information, see https://cloud.google.com/bigquery/troubleshooting-errors

我的查询如下:

SELECT
  LANGUAGE AS name,
  year,
  quarter,
  count
FROM (
  SELECT
    *
  FROM (
    SELECT
      lang AS language,
      y AS year,
      q AS quarter,
      type,
      COUNT(*) AS count
    FROM (
      SELECT
        a.type type,
        b.lang lang,
        a.y y,
        a.q q
      FROM (
        SELECT
          type,
          YEAR(created_at) AS y,
          QUARTER(created_at) AS q,
          STRING(REGEXP_REPLACE(repo.url, r'(https:\/\/api\.github\.com\/repos\/)', '')) AS name
        FROM
          [githubarchive:year.2016] ) a
      JOIN (
        SELECT
          repo_name AS name,
          lang
        FROM (
          SELECT
            *
          FROM (
            SELECT
              *,
              ROW_NUMBER() OVER (PARTITION BY repo_name ORDER BY lang) AS num
            FROM (
              SELECT
                repo_name,
                FIRST_VALUE(language.name) OVER (PARTITION BY repo_name ORDER BY language.bytes DESC) AS lang
              FROM
                [bigquery-public-data:github_repos.languages]))
          WHERE
            num = 1
          ORDER BY
            repo_name)
        WHERE
          lang != 'null') b
      ON
        a.name = b.name)
    GROUP BY
      type,
      language,
      year,
      quarter
    ORDER BY
      year,
      quarter,
      count DESC)
  WHERE
    count >= 1000)
WHERE
  type = 'PushEvent'
LIMIT
  100

基本上，我试图通过 Git-Hub 上的“推送”来构建所有前 100 种语言的数据集，并使用 D3 来可视化所述数据。到目前为止，我使用的数据非常少，但是这个查询目前是 20GB，应该低于限制。

作为一名学生，我怀疑自己是否有能力支付该服务的费用。

Answer 1

相关查询仅扫描 22.5GB，约合 0.11 美元
该错误表明您超出了 free tier允许的字节 - 1TB因此，您可以在一个月内运行查询大约 45 次，之后您需要等待下个月

我对您的建议是不要每次都运行此查询 - 而是保存结果并在您的实验/尝试中使用它，这样您就不会那么快浪费您的 1TB!