Whitespace causes problem in BigQuery with Python

user_7092934 0
Viewed 504

I have got the following Python code to retrieve a BigQuery dataset. Then I execute two queries working_query and bad_query on a Jupyter Notebook on kaggle. The only difference is an added whitespace on line 3 after ...posts_questions` as q which makes the bad_query fail with the message

Query exceeded limit for bytes billed: 10000000000. 24460132352 or higher required.

I know that cost controls are enabled but do not see how this could be the issue here. How can I avoid such traps in the future and can someone explain the problem?

from google.cloud import bigquery

client = bigquery.Client()
dataset_ref = client.dataset("stackoverflow", project="bigquery-public-data")
dataset = client.get_dataset(dataset_ref)

safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)

answers_query_job = client.query(working_query, job_config=safe_config) 
answers_query_job.to_dataframe()


working_query = """
                SELECT a.id, a.body, a.owner_user_id
                FROM `bigquery-public-data.stackoverflow.posts_answers` AS a
                INNER JOIN `bigquery-public-data.stackoverflow.posts_questions` AS q
                    ON q.id = a.parent_id
                WHERE q.tags LIKE '%bigquery%'
                """

bad_query = """
                SELECT a.id, a.body, a.owner_user_id
                FROM `bigquery-public-data.stackoverflow.posts_answers` AS a
                INNER JOIN `bigquery-public-data.stackoverflow.posts_questions` AS q 
                    ON q.id = a.parent_id
                WHERE q.tags LIKE '%bigquery%'
                """

UPDATE:

It was a caching problem as the working_query was run before activating cost control. That way it could retrieve data from cache even with cost control enabled. The queries have to be absolutely identical to share the cache so even added whitespace can prevent that.

google-bigquery whitespace caching sql python
2 Answers

I have performed some tests using both of your queries and they were executed in the same way.

First, I must point that the query() method receives a string and job_config is used to configure the job. In addition, the documentation does not mention any issues related to extra spaces in side your query string, here.

Furthermore, if you navigate to the BigQuery UI, copy and paste one query at a time and execute it, you will see, under Job Information, that both queries will process roughly 23Gb of data and the same amount of data will be the bytes billed. Therefore, if your set bigquery.QueryJobConfig(maximum_bytes_billed=23000000000) and omit the to_dataframe() method, both queries you stated above will run perfectly.

UPDATE:

According to the documentation, by default you have use_query_cache set to true, which means if you run the same query it will retrieve the results from a previous one. For this reason, no bytes will be processed. If you ran your query previously without the maximum_bytes_billed set. Then you run the same query with a maximum amount, the query will run even though, it processed more than what you set now.

In your case, I used a Python 3 Notebook from the AI Platform and a .py file within the Shell to run the following codes.

First code, 

from google.cloud import bigquery
import pandas

client = bigquery.Client()
dataset_ref = client.dataset("stackoverflow", project="bigquery-public-data")
dataset = client.get_dataset(dataset_ref)

job_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
job_config.use_query_cache = False

working_query = """
                SELECT a.id, a.body, a.owner_user_id
                FROM `bigquery-public-data.stackoverflow.posts_answers` AS a
                INNER JOIN `bigquery-public-data.stackoverflow.posts_questions` AS q
                    ON q.id = a.parent_id
                WHERE q.tags LIKE '%bigquery%'
                """
answers_query_job = client.query(working_query, job_config) 
answers_query_job.to_dataframe()

Second code, 

from google.cloud import bigquery
import pandas

client = bigquery.Client()
dataset_ref = client.dataset("stackoverflow", project="bigquery-public-data")
dataset = client.get_dataset(dataset_ref)

job_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)
job_config.use_query_cache = False


bad_query = """
                SELECT a.id, a.body, a.owner_user_id
                FROM `bigquery-public-data.stackoverflow.posts_answers` AS a
                INNER JOIN `bigquery-public-data.stackoverflow.posts_questions` AS q 
                    ON q.id = a.parent_id
                WHERE q.tags LIKE '%bigquery%'
                """

answers_query_job = client.query(working_query, job_config) 
answers_query_job.to_dataframe()

None of the above codes will work. They resulted with the error:

Query exceeded limit for bytes billed: 10000000000. 24460132352 or higher required.

On the other hand if job_config = bigquery.QueryJobConfig(maximum_bytes_billed=25000000000) is set. Both queries run normally.

user_125713870

You probably have cost controls enabled: documentation

This error means that your query was about to scan more bytes than the limit set in "Maximum Bytes Billed".

Can you reliably reproduce this error? Doesn't look like whitespace in the query would have anything to do with cost cont in BigQueryrols.. Maybe just a coincidence when either data was larger, or cost controls were introduced.

Edit: Alexandre is right in his answer - "good query" succeeds because it takes results from the cache. Just retried with (note use_query_cache, not UseQueryCache in the comment thread above)

safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10, use_query_cache=False)

And got the same error for the good query. Also, you can check cache_hit in the result job to see if response was taken from the cache. Whenever query succeeds, it's equal to true:

print("Cache hit: ")
print(answers_query_job.cache_hit)
user_19597100
Related