You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@iceberg.apache.org by "pkhetrapal (via GitHub)" <gi...@apache.org> on 2023/03/04 06:03:40 UTC
[GitHub] [iceberg] pkhetrapal opened a new issue, #7005: Duplicate records with MERGE command
pkhetrapal opened a new issue, #7005:
URL: https://github.com/apache/iceberg/issues/7005
### Apache Iceberg version
0.13.1
### Query engine
Other
### Please describe the bug 🐞
Using iceberg sink connector for AWS Glue 3.
`MERGE` command produces duplicate records in case of backfilling the data. I want to make sure that I don't update or insert the record when the source (temp view) `updated_at` timestamp is less than or equal to the target (iceberg table) `updated_at` timestamp.
Both of the below options produces duplicate records instead of updating the existing one.
```
Table:
CREATE TABLE IF NOT EXISTS table1
USING iceberg
PARTITIONED BY (bucket(2, _ID), days(UPDATED_AT))
TBLPROPERTIES (
'write.metadata.delete-after-commit.enabled'='true',
'write.metadata.previous-versions-max'='10',
'history.expire.max-snapshot-age-ms'='432000000',
'history.expire.min-snapshots-to-keep'='10',
'commit.manifest.min-count-to-merge'='25',
'format'='parquet'
)
AS (SELECT * from global_temp.table2 LIMIT 0)
```
```
Option 1.
merge_update_sql = f"""
MERGE INTO table1 t
USING (SELECT * FROM global_temp.table2) s
ON t._ID = s._ID
WHEN MATCHED AND s.UPDATED_AT > t.UPDATED_AT THEN UPDATE SET *
spark.sql(merge_update_sql)
merge_insert_sql = f"""
MERGE INTO table1 t
USING (SELECT * FROM global_temp.table2) s
ON t._ID = s._ID
WHEN NOT MATCHED THEN INSERT *
"""
spark.sql(merge_insert_sql)
```
```
Option 2.
merge_sql = f"""
MERGE INTO table1 t
USING (SELECT * FROM global_temp.table2) s
ON t._ID = s._ID
WHEN MATCHED AND s.UPDATED_AT > t.UPDATED_AT THEN UPDATE SET *
WHEN MATCHED AND s.UPDATED_AT <= t.UPDATED_AT THEN UPDATE SET t._ID = s._ID
WHEN NOT MATCHED THEN INSERT *
spark.sql(merge_sql)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Duplicate records with MERGE command [iceberg]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #7005: Duplicate records with MERGE command
URL: https://github.com/apache/iceberg/issues/7005
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
[GitHub] [iceberg] maltemelzer commented on issue #7005: Duplicate records with MERGE command
Posted by "maltemelzer (via GitHub)" <gi...@apache.org>.
maltemelzer commented on issue #7005:
URL: https://github.com/apache/iceberg/issues/7005#issuecomment-1661533644
I am having the same issue on AWS Glue 4.0, records get inserted twice, also with the second query.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Duplicate records with MERGE command [iceberg]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #7005:
URL: https://github.com/apache/iceberg/issues/7005#issuecomment-2327661264
This issue has been automatically marked as stale because it has been open for 180 days with no activity. It will be closed in next 14 days if no further activity occurs. To permanently prevent this issue from being considered stale, add the label 'not-stale', but commenting on the issue is preferred when possible.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Duplicate records with MERGE command [iceberg]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #7005:
URL: https://github.com/apache/iceberg/issues/7005#issuecomment-2359659310
This issue has been closed because it has not received any activity in the last 14 days since being marked as 'stale'
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Duplicate records with MERGE command [iceberg]
Posted by "jwesteen (via GitHub)" <gi...@apache.org>.
jwesteen commented on issue #7005:
URL: https://github.com/apache/iceberg/issues/7005#issuecomment-3470722614
did anyone ever get to the root cause here? I've observed this on EMR 7.5.0 with Iceberg 1.8.1, dual writes writing the same data to the same table off two Kafka topics, and the MERGE clause only includes `"WHEN NOT MATCHED THEN INSERT *"`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Duplicate records with MERGE command [iceberg]
Posted by "flisboac (via GitHub)" <gi...@apache.org>.
flisboac commented on issue #7005:
URL: https://github.com/apache/iceberg/issues/7005#issuecomment-1984043547
I'm seeing the same behaviour on EMR Release 7.0.0, for EMR on EC2, even though I have a deduplication step for the origin dataset prior to the MERGE INTO. I still need to try @pkhetrapal 's suggestion of adding another criteria on the `WHEN MATCHED` clause to further restrict updates, I really hope it works.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
[GitHub] [iceberg] pkhetrapal commented on issue #7005: Duplicate records with MERGE command
Posted by "pkhetrapal (via GitHub)" <gi...@apache.org>.
pkhetrapal commented on issue #7005:
URL: https://github.com/apache/iceberg/issues/7005#issuecomment-1454981586
```
merge_insert_sql = f"""
MERGE INTO table1 t
USING (SELECT * FROM global_temp.table2) s
ON t._ID = s._ID
WHEN NOT MATCHED THEN INSERT *
"""
spark.sql(merge_insert_sql)
```
This inserts the records twice - not sure why though
```
merge_sql = f"""
MERGE INTO table1 t
USING (SELECT * FROM global_temp.table2) s
ON t._ID = s._ID
WHEN MATCHED AND s.UPDATED_AT > t.UPDATED_AT THEN UPDATE SET *
WHEN MATCHED AND s.UPDATED_AT <= t.UPDATED_AT THEN UPDATE SET t._ID = s._ID
WHEN NOT MATCHED THEN INSERT *
spark.sql(merge_sql)
```
This inserts the records twice as well but the below one works fine
```
merge_sql = f"""
MERGE INTO table1 t
USING (SELECT * FROM global_temp.table2) s
ON t._ID = s._ID
WHEN MATCHED AND s.UPDATED_AT > t.UPDATED_AT THEN UPDATE SET *
WHEN MATCHED THEN UPDATE SET t._ID = s._ID
WHEN NOT MATCHED THEN INSERT *
spark.sql(merge_sql)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org