CODEWITHSUNDEEP
google-bigquerygoogle-cloud-platformbigdata
Ashok Khote
Ashok Khote

Reputation: 117

How to insert overwrite partitioned table in BigQuery UI?

We can insert data into specific partition of partitioned table, here we need to specify partition value.But my requirement is to overwrite all partitions in a table in one query using UI. Can we perform this operation?

Upvotes: 1

Views: 3598

Answers (3)

Pavan Edara
Pavan Edara

Reputation: 2315

At this time, BigQuery allows updating upto 2000 partitions in a single statement. If you need to just insert data into a partitioned table, you can use the INSERT DML statement to write to upto 2000 partitions in one statement. If you are updating or deleting existing partitions you can use the UPDATE or DELETE statements respectively. If you are both updating and inserting new data, you can use the MERGE DML statements to achieve this.

Upvotes: 0

Graham Polley
Graham Polley

Reputation: 14791

As YY has pointed out, you would not be able to do this directly in BigQuery/SQL with one query (you could script something to run N queries). However, if you spun up a Cloud Dataflow pipeline and configured it to have multiple BigQueryIO sinks, with each one (sink) overwriting a partition, this could be one way I can think of doing it in one shot. It would be a straightforward pipeline to spin up and run.

Upvotes: 2

Y Y
Y Y

Reputation: 111

Consulted bigquery team member. You can NOT write to all partitions in one query.

You can only write to a partition at a time.

Upvotes: 2

Related Questions