DELETE/UPDATE/MERGE DMLs are currently not supported over tables that had UPSERT streams.

[ManigandanS]

Hi ,
 i am working on migrating data from rds to bq using datastream. and after the load has been completed i am trying to update/merge using my ts code. But it is throwing this error DELETE/UPDATE/MERGE DMLs are currently not supported over tables that had UPSERT streams. i Have also deleted  the data stream for that particular table but still getting that error.

[Joy_S]

Hi @ManigandanS ,

Welcome to Google Cloud Community!

I see that you're encountering an error message that says "DELETE/UPDATE/MERGE DMLs are currently not supported over tables that had UPSERT streams" while you're migrating data. This happens because tables that use Change Data Capture (CDC) don't currently allow actions like DELETE, UPDATE, and MERGE. The limitation can be found on this document.

A feature request has already been made to add this functionality. You can track the progress on this issue at this link. However, I don't have a timeline for when this feature will be available.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[iron-dutch]

I can't believe this happens with a google product. I had to spend many days to figure out and find a workaround on this kind of weird stuff with Bigquery...

There is this legacy streaming API, which leads the table to be used in "streaming" mode and BQ doesn't allow that table to be updated for around 90 min after the streaming worked. To escape this limitation, I decided to use this shiny new "Bigquery Storage API", had to figure out the new client library, which is heavily complex btw, and then hit the issue in this post! what a shame!