CODEWITHSUNDEEP
google-bigquery
N.N.
N.N.

Reputation: 3172

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

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

Upvotes: 3

Views: 4284

Answers (2)

Jakub Kukul
Jakub Kukul

Reputation: 14494

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

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

Yes. Each partition behaves as if it is separate table so you can have particular partition as destination table with overwrite. Works from UI and API. The rest of the table is not changing.
Even long term storage pricing is applicable for partition tables

Upvotes: 0

Related Questions