Duplicate Rows When Loading Delta Native tables In Athena Engine V3

Question

Hello,

We use Spark jobs running on EMR to create and update tables which we manage in Glue (they are Parquet files in S3).

We have recently switched to Athena engine v3 and are working to implement our tables as delta native tables.

The goal is to create a new native delta table on top of our S3 address and to populate that table properly. We are running the following SQL commands in Spark (from EMR):

DROP TABLE IF EXISTS `/orders`;

CREATE EXTERNAL TABLE `/orders`.;
                        ()
                        PARTITIONED BY ( `year` STRING, `month` STRING, `day` STRING, `dt` STRING)
                        LOCATION 's3://REDACTED'
                        ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
                        STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
                        OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
                        TBLPROPERTIES('table_type' = 'DELTA');

At this point the table is created with the proper schema and zero rows. We run the following command to populate it:
MSCK REPAIR TABLE

The table contains many rows that are exact duplicates on every field. How can we avoid this? Should we approach it differently?

Answer

Sounds you are creating a table that is not really Delta but just reading the parquet files.   
For native format you just specify the location and type: https://docs.aws.amazon.com/athena/latest/ug/delta-lake-tables.html  
and I don't think you can use the REPAIR, you would have to use a crawler or synchronize using the tool provided by Delta.

Duplicate Rows When Loading Delta Native tables In Athena Engine V3

Relevant content