Loading data to “Partitioned Tables” with Write_Truncate using BQ API

Question

Can one load data with write_truncate to a specific partition? My use case would be a batch load process that overrides a specific partition, while keeping the rest of the table intact. This reference mentions only command line tool: https://cloud.google.com/bigquery/docs/creating-partitioned-tables#restating_data_in_a_partition

Answer 1

If you want to write to a specific partition using the BQ API, proceed as if you were writing to a table but include the partition decorator in the table id.

If you have a date partitioned table transactions and you want to load data to a partition corresponding to 2021-10-11, pass transactions$20211011 to the API.

If you have an ingestion-time partitioned table transactions with hourly partitions and you want to load data to the partition corresponding to 2021-05-07 17:00:00, pass transactions$2021050717 to the API.

Example: if you're using the python's API to load a DataFrame into BQ and you want to overwrite a partition, you'd do it like this:

from google.cloud import bigquery
client = bigquery.Client(project='your_project')

job_config = bigquery.LoadJobConfig(
    write_disposition="WRITE_TRUNCATE", 
)

# Include target partition in the table id:
table_id = "your_project.your_dataset.your_table$20211021" 
job = client.load_table_from_dataframe(df, table_id, job_config=job_config) # Make an API request
job.result() # Wait for job to finish