Deduplication on Amazon Athena

Question

We have streaming applications storing data on S3. The S3 partitions might have duplicated records. We query the data in S3 through Athena.

Is there a way to remove duplicates from S3 files so that we don't get them while querying from Athena?

user_5975697 · Answer

Please use CTAS:

CREATE TABLE new_table
WITH (
      format = 'Parquet',
      parquet_compression = 'SNAPPY')
AS SELECT DISTINCT *
FROM old_table;