Is there a way we can run a DML Query (INSERT/MERGE) via BigQuery spark connector?

[spadhi7]

No description provided.

[davidrabinowitz]

INSERT can be run by creating a DataFrame and saving it to BigQuery.

MERGE is not supported at the moment - I'd appreciate to see a use case.

[spadhi7]

We are trying to do CDC from traditional RDMS to BigQuery via Kafka. Source -> Kafka -> Spark Structured Streaming -> BigQuery

[joydeepml]

@davidrabinowitz Merge support in the spark connector will be greatly appreciated. Will help avoid running a separate job using SQL merge https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement

the use case as mentioned by @spadhi7 is to apply the changes in a source table to a table in bigquery by utilising the change feed which provides inserts, updates and deletes in the source table.

afaik, merge is supported in spark only for the delta format
https://docs.delta.io/latest/delta-update.html#language-python

Will be great if bigquery connector would support that too

[davidrabinowitz]

Thanks for the suggestion - I agree this is a great idea, however at the moment we try to use only Spark APIs without any proprietary APIs from our end. We will review this and see what it the best way to implement the merge functionality.

[nicodds]

It would be a great feature!

In my current situation, I need to update a table with a daily delta that may originate either from new inserts or from updates to existing records. In order to keep the bigquery table updated, I have to upload the changes to a staging table and then launch a separate merge query. It would be optimal if that could be done directly in the spark connector.

I agree, anyway, that this feature is hard to implement, since it would add complexity on the load job.

[h5chauhan]

This would be similar to Delta.io. It would be nice if the connector could support it.

[khaledh]

As @nicodds mentioned, we're also using the same approach: write the new changes to a temp table using the connector, then run a BQ SQL query to do the merge, and finally we drop the temp table. It would be nice to do this directly instead.

I wonder if the connector can implement this feature in a way similar to how Iceberg does it: https://iceberg.apache.org/docs/latest/spark-writes/#merge-into

[ajaybiswal]

Hi just wanted to know if merge feature is available now as I couldn't find anything in the docs.