CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerybigquery-udf
Mani Shankar.S
Mani Shankar.S

Reputation: 39

GCP Bigquery Upsert Logic Efficiency check

I am trying to choose right method to have the upsert logic. I have tested with sample data of 3.23 GB (target) with 218mb incremental + Update data (source).

I have performed two things.

Delete Query + Insert Query
Merge Query

When I checked Delete + Insert Query -> Bytes billed 3.23gb + 218mb --> slot time consumed 7.32s + 28s

Merge Query --> Bytes Billed 3.43 gb --> slot time consumed --> 9.51s

I can see only difference in slot time consumed where delete + insert > merge.

Can someone please confirm, how to decide on this, should I need to look for any other parameter?

Any thumb rule on this??

Upvotes: 0

Views: 190

Answers (1)

Joevanie
Joevanie

Reputation: 605

Posting as a community wiki. According to gcc, "The MERGE statement provides a more concise and efficient way to perform UPSERT

With the MERGE statement, you can specify the conditions for matching rows and define the actions to perform when a match is found or not found". In addition, it stated to use the MERGE statement for UPSERT:

MERGE merge_example.table_data T USING merge_example.table_changes S ON T.id = S.id WHEN MATCHED THEN UPDATE SET value = s.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES(id, value)

Upvotes: 0

Related Questions