Search code examples
google-bigqueryquota

How to fix which dataset or table cause BigQuery Data Loading quota exceed error

start from March 1st, 2021, when I load data to Google BigQuery, I always meet an error as below:

"reason": "quotaExceeded", 
        "message": "Quota exceeded: Your project exceeded quota for imports per project. For more 
information, see https://cloud.google.com/bigquery/troubleshooting-errors", 
        "location": "load_job"

if I reduce the number of concurrent loading jobs or do a retry, I will success to load data again. From Google Cloud Console, I can see the requests roaring a lot, from around 90,000 to 3.160,000, most are 200 response. And in quota page, I not find exceed notice tips. Every weekend is the summit, but my cron jobs most not be scheduled on that time. Any tips how to fix which dataset or table's data inserting cause this?

enter image description here

Solution

  • Later with this SQL below, I find the error. https://cloud.google.com/bigquery/docs/troubleshoot-quotas

    SELECT
destination_table.table_id, count(destination_table.table_id)
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY) AND
  error_result.reason IN ('rateLimitExceeded', 'quotaExceeded') group by 1