Search code examples
google-bigquerygoogle-bigquery-java

Resources exceeded during query execution while querying big query with order by

I have a big query table with billions of data and I need to query results based on day level. In a day there will be millions of records flowing into the table. And I need to retrieve data on a daily basis. I use below query for that

select * from `dataset.table` where record_time = CURRENT_DATE() order by c_id limit 1000000 offset 0;

I use offset and limit to read data in smaller portion. I will retrieve total record count first and then use the above query to retrieve data in multiple portions but while doing it I am getting 'Resources exceeded during query execution' error.

The query is working fine without order by but I will not rely the result without order by as I am using offset and limit.

Is there any way to get rid of this error?

Solution

  • Resources exceeded during query execution - This error occurs when on-demand queries use too much CPU relative to the amount of data scanned .

    As instructed in the GCP doc you can try -

    • Removing an ORDER BY clause.

    • If your query materializes many rows using a LIMIT clause, consider filtering on another column, for example ROW_NUMBER(), or removing the LIMIT clause altogether to allow write parallelization.

    For more info follow this Troubleshoot resources exceeded issues