AWS Athena (Presto)中DISTINCT SQL查询出现重复结果？

Question

我有一堆文件存储在S3上，每行一个MD5值，这些文件都和IT技术有关。我创建了一个AWS Athena表格，以便对这些MD5值进行去重查询。总共有数亿个MD5值在这些文件和表格中。

Athena表格创建查询语句：

CREATE EXTERNAL TABLE IF NOT EXISTS database.md5s (
  `md5` string 
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ','
) LOCATION 's3://bucket/folder/';

以下是我尝试过的所有“去重”查询（这些查询应该都是相同的）：

SELECT DISTINCT md5
FROM md5s;`

SELECT md5
FROM md5s
GROUP BY md5;

SELECT md5
FROM md5s
GROUP BY DISTINCT md5;

SELECT DISTINCT md5
FROM md5s
GROUP BY DISTINCT md5;

所有从 Athena 输出的 .csv 结果仍然具有重复的 MD5。这是怎么回事？

---

Athena 是否在进行部分去重？更奇怪的是，如果我在 Athena 中执行 COUNT(DISTINCT md5)，我得到的计数与导出的行数不同。

• Athena 中的 COUNT(DISTINCT md5)：97,533,226
• 导出的不同 MD5 记录数量：97,581,616
• 结果导出中有 14,790 个重复项，因此 COUNT(DISTINCT) 计数和结果导出都有问题。

---

Athena 是否在导出时创建重复项？情节渐浓。如果我查询 Athena 表格中在 Athena 结果导出中重复的 MD5 之一，我只会从表格中得到一个结果/行。我使用了 LIKE 查询进行了测试，以确保空格不会引起问题。这意味着 Athena 正在向导出中添加重复项。结果中从未有超过两个相同的 MD5。

select
  md5,
  to_utf8(md5)
from md5s
where md5 like '%0061c3d72c2957f454eef9d4b05775d7%';

Athena的计数和结果文件都有问题吗？ - 我使用MySQL对这些记录进行了去重，最终得到了97,531,010个唯一的MD5。下面是Athena的计数和结果细节。

• Athena中COUNT(DISTINCT md5)：97,533,226
• 导出的不同MD5记录数量为：97,581,616
• 结果导出中有14,790个重复项，因此看起来 COUNT(DISTINCT)计数和结果导出都有问题。

---

我认为这是Athena的一个错误 - 我已向AWS的开发团队提交了工单以解决此问题，并将在修复后更新此帖子。

以下是相关的AWS论坛帖子，在那里其他用户也遇到了相同的问题。 https://forums.aws.amazon.com/thread.jspa?messageID=764702

- T. Brian Jones

---

也许空格会使看似相同的MD5哈希值实际上不同？ - James

我相信所有这些查询都是相同的。使用EXPLAIN查看查询计划。我猜也可能跟空格有关。尝试添加HAVING count(*)>1并输出to_utf8(md5)，这样你就可以看到字符串的十六进制值了。 - Dain Sundstrom

我认为这不是空格问题，并且我已经使用@DainSundstrom的想法进行了测试。我在工单末尾添加了注释。非常奇怪。还有其他想法吗？ - T. Brian Jones

Answer 1

我已经与AWS团队确认，这是在提问时AWS Athena已知的一个bug。我不确定是否已经解决。

- T. Brian Jones

Answer 2

如果有疑问，请使用 CTAS 去除任何重复项：

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

参考文献: https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html

这是一个关于使用AWS Athena中的CTAS（CREATE TABLE AS）功能的示例页面。通过这个功能，您可以将查询结果转换为新表，并将其存储在S3上。本页提供了一些常见的用例和示例代码，帮助您更好地理解如何使用CTAS功能。

- Avish Saha