Cannot set destination table with BigQuery Python API

Question

I'm recently getting the following BigQuery error when using the Python API:

google.api_core.exceptions.BadRequest: 400 configuration.query.destinationTable cannot be set for scripts

This is the function I use:

def execute_bigquery_sql(query, dataset_id, table_id, use_legacy_sql=True, write_disposition='WRITE_TRUNCATE'):
    client = bigquery.Client()
    job_config = bigquery.QueryJobConfig()
    job_config.use_legacy_sql = use_legacy_sql

    print("table_id: {table_id}".format(table_id=table_id))
    print("dataset_id: {dataset_id}".format(dataset_id=dataset_id))

    if table_id:
        table_ref = client.dataset(dataset_id).table(table_id)
        print("table_ref: {table_ref}".format(table_ref=table_ref))
        job_config.destination = table_ref
        job_config.write_disposition = write_disposition
        job_config.allow_large_results = True
        job_config.createDisposition = "CREATE_IF_NEEDED"

    query_job = client.query(query,job_config=job_config)
    results = query_job.result()  # Waits for job to complete.

Does anyone knows what might be happening and a workaround?

Javier Giovannini · Accepted Answer · 2019-10-31 17:36:48Z

17

Thanks for the responses, comments were indeed in the right direction. In BigQuery scripting means https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting.

And that is what is not allowed, and my query had:

DECLARE capital int64 default 10000000;

So, removing the line above was the fix in my case.

Interesting thing is that even in the web interface

If you use scripts the interface won't allow to save to table:

In contrast, when not using script statements, you should see:

edited Oct 31, 2019 at 17:36

answered Oct 31, 2019 at 17:25

Javier Giovannini

2,3622 gold badges20 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Syed Over a year ago

my query settings look like the second screenshot where i am not able to use scripts. How do i change the query settings to use scripts like in the first screenshot?

agent18 Over a year ago

Link doesn't work take to the meaning of scripting

agent18 Over a year ago

What if I wanted to use declare statements. How to overcome it?

Mikhail Berlyant · Accepted Answer · 2019-10-31 15:44:18Z

4

error is self-descriptive. scripts do not allow destination table to be set - instead you should use DML/DDL

workaround is to reset job_config with no destination table in it

answered Oct 31, 2019 at 15:44

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

5 Comments

UJIN Over a year ago

Just a clarification, what does script mean in this case? How is this different from this official doc?

Mikhail Berlyant Over a year ago

cloud.google.com/bigquery/docs/reference/standard-sql/scripting

UJIN Over a year ago

Ok, so the problem relies in the actual SQL in OP question, doesn't it? If that query didn't contain any scripting then it would have (at least under this aspect) worked, right?

Mikhail Berlyant Over a year ago

when one runs script or DML/DDL - the destination is not allowed and will cause error

sbecon Over a year ago

I'm running into a similar problem and don't quite understand the solution that OP explained: stackoverflow.com/questions/65590829/…

Collectives™ on Stack Overflow

Cannot set destination table with BigQuery Python API

2 Answers 2

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related