How to append query results using BigQuery Python API

Question

I cannot find a way to append results of my query to a table in BigQuery that already exists and is partitioned by hour. I have only found this solution: https://cloud.google.com/bigquery/docs/writing-results#writing_query_results.

job_config = bigquery.QueryJobConfig(destination=table_id)

sql = """SELECT * FROM table1 JOIN table2 ON table1.art_n=table2.artn"""

# Start the query, passing in the extra configuration.
query_job = client.query(sql, job_config=job_config)  # Make an API request.
query_job.result()  # Wait for the job to complete.

But providing a destination table to bigquery.QueryJobConfig overwrites it, and I did not find that bigquery.QueryJobConfig would have an option to specify if_exists or something. As far as I understand, I need to apply job.insert to query results, but I do not understand how.

I also did not find any good advice around, maybe someone can point me to it?

Just in case, my real query is huge and I load it from a separate JSON file.

Graham Polley · Accepted Answer · 2020-09-12 04:59:00Z

6

When you create your job_config, you need to set the write_disposition to WRITE_APPEND:

[..]
job_config = bigquery.QueryJobConfig(
    allow_large_results=True, 
    destination=table_id, 
    write_disposition='WRITE_APPEND'
)
[..]

See here.

answered Sep 12, 2020 at 4:59

Graham Polley

14.8k9 gold badges54 silver badges89 bronze badges

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

Comments

Soumendra Mishra · Accepted Answer · 2020-09-12 04:58:46Z

1

You can add below lines to append data into existing table:

job_config.write_disposition = 'WRITE_APPEND'

Complete Code:

from google.cloud import bigquery
client = bigquery.Client()
job_config = bigquery.QueryJobConfig(destination="myproject.mydataset.target_table")
job_config.write_disposition = 'WRITE_APPEND'   
sql = """SELECT * FROM table1 JOIN table2 ON table1.art_n=table2.artn"""
query_job = client.query(sql, job_config=job_config)
query_job.result()

edited Sep 12, 2020 at 4:58

answered Sep 12, 2020 at 4:46

Soumendra Mishra

3,7131 gold badge18 silver badges40 bronze badges

Comments

Yun Zhang · Accepted Answer · 2020-09-12 04:59:40Z

1

The parameter that you were looking for is called write_disposition. You want to use WRITE_APPEND to append to a table.

answered Sep 12, 2020 at 4:59

Yun Zhang

5,6032 gold badges14 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to append query results using BigQuery Python API

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related