Skip to content

Athena ICEBERG - Delete target rows while source rows doesn't exists

1

Hi, We're working on ICEBERG table and using MERGE INTO statement. We would like to implement the following logic:

  1. In case there is match between some fields in source table on top of target table + there is changes in other fields: Update the target table with the source rows
  2. In case there is no match - insert rows from source to target.
  3. In case there is no match and there are records in target table that are not in source table delete this rows We succeed to implement steps 1 and 2 using below query.
MERGE INTO <...> AS target
            USING (
           <....>
            ) AS source
            ON  <....>
            WHEN MATCHED AND <>
		UPDATE SET 
			<...>
            WHEN NOT MATCHED THEN 
                INSERT <...>
		VALUES <...>	

However in order to implement #3 there was a suggestion to use WHEN NOT MATCHED BY SOURCE THEN DELETE However seems this is not supported by AWS ICEBERG.

Please assist with how we can implement it.

Thank you

asked a year ago565 views
2 Answers
0

Hello,

We do not have this as a DML limitation with Iceberg and would be interested to troubleshoot the query and suggest an applicable workaround.

Can you please initiate a Support Case using the Support Portal and also provide the Query execution ID for Support to further troubleshoot.

https://docs.aws.amazon.com/awssupport/latest/user/case-management.html#creating-a-support-case

We would also suggest conducting an UPDATE state if possible based on your use case

https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-updating-iceberg-table-data.html#querying-iceberg-update

A support engineer will be able to address more of your feasibility requirements

answered a year ago
0

Hi! Did you manage to make the WHEN NOT MATCHED BY SOURCE THEN DELETE work in athena with iceberg?

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.