How to append query results using BigQuery Python API

user_7635884 0
Viewed 2466

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.

google-bigquery sql-insert api python
3 Answers

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.

user_28772780

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()
user_140485790

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

user_112062020
Related