apache iceberg - How to get rid of rowl level duplicates? - Stack Overflow

admin管理员组

文章数量:1395013

Using MERGE INTO, but getting rid of all duplicate rows whereas my expectation was it should behave like df.dropDuplicates()

Using Below MERGE INTO it's deleting all rows of duplicate which is leading to data loss for my use case.

Using MERGE INTO, but getting rid of all duplicate rows whereas my expectation was it should behave like df.dropDuplicates()

Using Below MERGE INTO it's deleting all rows of duplicate which is leading to data loss for my use case.

• apache-iceberg
• drop-duplicates

Share Improve this question Follow edited Mar 27 at 7:21 DarkBee 15.5k88 gold badges7272 silver badges117117 bronze badges asked Mar 27 at 7:05 muskanbeig muskmuskanbeig musk 122 bronze badges 1

• Please provide enough code so others can better understand or reproduce the problem. – Community Bot Commented Mar 27 at 9:50

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

As a solution I am currently achieving it in below fashion

1. create branch on iceberg table

2. create data frame just selecting data from branch and apply window function Row_NUMBER() and select only records where rowNo is 2

3. Execute MERGE INTO to delete the records from main table using data frame -- Upon it's completion execute step 4

4. Execute MERGE INTO to insert distinct records from branch -- Upon it's completion execute step 5

5. Drop the branch

spark.sql(
          """CREATE TABLE IF NOT EXISTS db.dedup_demo_part_drop
                  (id BIGINT, name STRING, role STRING, salary double,joining_date STRING) USING iceberg PARTITIONED BY (joining_date)"""
      )
      
      
spark.sql(""" INSERT INTO db.dedup_demo values (1, 'Harry', 'Software Engineer', 25000,"2025-03-01"), (2, 'John', 'Marketing Ops', 17000,"2025-03-01")""")    


spark.sql("ALTER TABLE db.dedup_demo CREATE BRANCH duplicationTest")


spark.sql(""" describe db.dedup_demo """).show(false)


val df1 = spark.sql(""" select * from (SELECT  id, name, role, salary,
                  ROW_NUMBER() OVER (PARTITION BY id, name, role, salary ORDER BY id, name, role, salary DESC) AS rowNo
                FROM  db.dedup_demo VERSION AS OF 'duplicationTest') where rowNo = 2 """)
                
df1.createOrReplaceTempView("source_deduplicate")               



spark.sql(""" MERGE INTO db.dedup_demo AS target
      USING source_deduplicate AS source
      ON target.id = source.id
         AND target.name = source.name
         AND target.role = source.role
         AND target.salary = source.salary
      WHEN MATCHED  THEN
          DELETE
      """)
      
      
spark.sql("SELECT * FROM db.dedup_demo VERSION AS OF 'duplicationTest'").show(false)      



spark.sql(""" MERGE INTO db.dedup_demo AS target
      USING (select distinct * from  db.dedup_demo VERSION AS OF 'duplicationTest') AS source
      ON target.id = source.id
         AND target.name = source.name
         AND target.role = source.role
         AND target.salary = source.salary
      WHEN NOT MATCHED  THEN
          INSERT *
      """)

spark.sql(s"""ALTER TABLE ${tblName} DROP BRANCH ${branchName}""")

本文标签： apache icebergHow to get rid of rowl level duplicatesStack Overflow