Bad Request error while querying data from bigquery in a loop

Question

I am querying data from bigquery using get_data_from_bq method mentioned below in a loop:

def get_data_from_bq(product_ids):
    format_strings = ','.join([("\"" + str(_id) + "\"") for _id in product_ids])
    query = "select productId, eventType, count(*) as count from [xyz:xyz.abc] where productId in (" + format_strings + ") and eventTime > CAST(\"" + time_thresh +"\" as DATETIME) group by eventType, productId order by productId;"
    query_job = bigquery_client.query(query, job_config=job_config)
    return query_job.result()

While for the first query(iteration) data returned is correct, all the subsequent queries are throwing the below-mentioned exception

    results = query_job.result()
  File "/home/ishank/.local/lib/python2.7/site-packages/google/cloud/bigquery/job.py", line 2415, in result
    super(QueryJob, self).result(timeout=timeout)
  File "/home/ishank/.local/lib/python2.7/site-packages/google/cloud/bigquery/job.py", line 660, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/home/ishank/.local/lib/python2.7/site-packages/google/api_core/future/polling.py", line 120, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 Cannot explicitly modify anonymous table xyz:_bf4dfedaed165b3ee62d8a9efa.anon1db6c519_b4ff_dbc67c17659f

Edit 1: Below is a sample query which is throwing the above exception. Also, this is running smoothly in bigquery console.

select productId, eventType, count(*) as count from [xyz:xyz.abc] where productId in ("168561","175936","161684","161681","161686") and eventTime > CAST("2018-05-30 11:21:19" as DATETIME) group by eventType, productId order by productId;

Federico Bertola · Accepted Answer · 2018-06-18 14:27:18Z

I had the exact same issue. The problem is not the query itself, it's that you are most likely reusing the same QueryJobConfig. When you perform a query, unless you set a destination, BigQuery stores the result in an anonymous table which is stated in the QueryJobConfig object. If you reuse this configuration, BigQuery tries to store the new result in the same anonymous table, hence the error. I don't particularly like this behaviour, to be honest.

You should rewrite your code like that:

def get_data_from_bq(product_ids):
    format_strings = ','.join([("\"" + str(_id) + "\"") for _id in product_ids])
    query = "select productId, eventType, count(*) as count from [xyz:xyz.abc] where productId in (" + format_strings + ") and eventTime > CAST(\"" + time_thresh +"\" as DATETIME) group by eventType, productId order by productId;"
    query_job = bigquery_client.query(query, job_config=QueryJobConfig())
    return query_job.result()

Hope this helps!

Kevin Chien · Accepted Answer · 2018-06-18 18:18:11Z

1

Edited:

Federico Bertola is correct on the solution and the temporary table that is written to by BigQuery see this link.

I did not get an error with my sample code querying from a public table last time, but I can reproduce the error today, so it is possible this symptom can appear intermittent. I can confirm the error is resolved with Federico’s suggestion.

You can get the “super(QueryJob, self).result(timeout=timeout)” error when the query string lacks quotes around the parameters in the query. It seems you have made a similar mistake with the parameter format_strings in your query. You can fix this problem by ensuring there is quotes escaped around the parameter:

(" + myparam + ")

, should be written as

(\"" + myparam + "\")

You should examine your query string where you use parameters, and start with a simpler query such as

select productId, eventType, count(*) as count from `xyz:xyz.abc`

, and grow your query as you go.

For the record, here is what worked for me:

from google.cloud import bigquery
client = bigquery.Client()
job_config = bigquery.QueryJobConfig()

def get_data_from_bq(myparam):  
    query = "SELECT word, SUM(word_count) as count FROM `publicdata.samples.shakespeare` WHERE word IN (\""+myparam+"\") GROUP BY word;"
    query_job = client.query(query, job_config=job_config) 
    return query_job.result()

mypar = "raisin"
x = 1
while (x<9):
    iterator = get_data_from_bq(mypar)
    print "==%d iteration==" % x
    x += 1

edited Jun 18, 2018 at 18:18

answered Jun 13, 2018 at 17:51

Kevin Chien

995 bronze badges

4 Comments

Elliott Brossard Over a year ago

Please consider using proper query parameters instead of modifying the text of the query, too :)

Ishank Gulati Over a year ago

I am adding quotes with escape characters while creating format_strings. Tried your solution too, It's not working. I have added a sample query which is throwing this error.

Ishank Gulati Over a year ago

Tried your sample code too. It's throwing the same exception on 2nd iteration.

Kevin Chien Over a year ago

As Elliott pointed out, using query parameters is recommended. You can use this with the standard SQL syntax. You mentioned you tried my sample code querying from a public table, but it is throwing the same exception. I suspect there is a problem with your setup. In your error text I see “annonymous table xyz”, but in your query, your table is named ‘abc’ not ‘xyz’. Confirm you have the table ‘abc’ under the dataset ‘xyz’ in BigQuery, and run a simpler query in your method to confirm your setup is fine: query = """select productId, eventType, count(*) as count from xyz.xyz.abc;"""

Collectives™ on Stack Overflow

Bad Request error while querying data from bigquery in a loop

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related