Can sparksql ddl define primary key now?

[waywtdcc]

Query engine

spark

Question

Now can sparksql ddl define primary key, and attributes can also be used?

[ConeyLiu]

You could check the doc here: https://iceberg.apache.org/docs/latest/spark-ddl/#alter-table--set-identifier-fields

[jia-zhengwei]

You could check the doc here: https://iceberg.apache.org/docs/latest/spark-ddl/#alter-table--set-identifier-fields

@ConeyLiu What should I do after setting IDENTIFIER FIELDS ? I can still insert into duplicate rows by spark-sql cmd;

[ConeyLiu]

You should use MERGE INTO to upsert in Spark. https://iceberg.apache.org/docs/latest/spark-writes/#merge-into

[zhangbutao]

You should use MERGE INTO to upsert in Spark. https://iceberg.apache.org/docs/latest/spark-writes/#merge-into

Hi @ConeyLiu , IMHO, MERGE INTO has nothing to do with IDENTIFIER FIELDS? Right?

[baiyangtx]

IDENTIFIER FIELDS only works for Flink Streaming upsert.

[ConeyLiu]

Yes, IDENTIFIER FIELDS is mostly used in equality delete files. Right now only Flink has implemented the MOR with equality delete files.

[zhangbutao]

Got it. It seems that the document https://iceberg.apache.org/docs/latest/spark-ddl/#alter-table--set-identifier-fields will give users the misconception that Spark can useIDENTIFIER FIELDS to deduplicate data.

[jia-zhengwei]

Got it. It seems that the document https://iceberg.apache.org/docs/latest/spark-ddl/#alter-table--set-identifier-fields will give users the misconception that Spark can useIDENTIFIER FIELDS to deduplicate data.

@zhangbutao Yes, it confused me also.

@ConeyLiu @zhangbutao Do you have any suggestion for using spark with duplicates data? YES, I will see merge into above.

[ConeyLiu]

You should use MERGE INTO if you want to do upsert. INSERT INTO will append data instead of upsert data.

[jia-zhengwei]

You should use MERGE INTO if you want to do upsert. INSERT INTO will append data instead of upsert data.

Got it, Thanks.