Spark DataFrame groupby与distinct操作未返回预期结果求助

I recently hit a confusing issue with Spark DataFrames where calling distinct() on an ID column returned two values that weren't visible in the first 20 rows of the raw data. Every row shown by show(false) had the same UUID, but distinct() was pulling out two entirely different IDs. Here's the breakdown of what I saw and how I fixed it.

What I Observed #

First, running distinct() on the ID column gave me these unexpected results:

scala> df.select(df("ID")).distinct.show(false)
2018-05-25 17:17:29 WARN TaskSetManager:66 - Stage 68 contains a task of very large size (556 KB). The maximum recommended task size is 100 KB.
+------------------------------------+
|ID                                  |
+------------------------------------+
|2445b371-7cec-41b0-947a-8a04c4e8cbbb|
|db33c6d4-26fb-42c8-99a3-1bdfc2bf4612|
+------------------------------------+

But when I just displayed the first 20 rows, all IDs looked identical:

scala> df.select(df("ID")).show(false)
2018-05-25 17:17:50 WARN TaskSetManager:66 - Stage 79 contains a task of very large size (556 KB). The maximum recommended task size is 100 KB.
+------------------------------------+
|ID                                  |
+------------------------------------+
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
|80a9d91d-c66f-4bf7-89a0-acf9ccd8e1b8|
+------------------------------------+
only showing top 20 rows

And the schema confirmed the ID column is a string:

scala> df.select(df("ID")).printSchema
root
 |-- ID: string (nullable = true)

Root Cause Suspicions & Fixes #

The most likely culprit here is hidden special characters in the ID strings. Even though they look identical to us, Spark's string comparison is exact—so things like trailing whitespace, newlines, tabs, or non-printable control characters will make two strings count as distinct.

Here are some steps to diagnose and fix this:

• Check string lengths: If IDs that look the same have different lengths, that's a dead giveaway there are hidden characters.

  df.select(col("ID"), length(col("ID")).alias("id_length")).distinct.show(false)
  

• Trim whitespace: Remove leading/trailing spaces and see if the distinct count aligns with expectations:

  df.select(trim(col("ID")).alias("cleaned_ID")).distinct.show(false)
  

• Remove control characters: Use regex to strip out all non-printable control characters:

  import org.apache.spark.sql.functions.regexp_replace
  df.select(regexp_replace(col("ID"), "\\p{C}", "").alias("cleaned_ID")).distinct.show(false)
  

• Inspect raw string bytes: For deeper debugging, convert the string to bytes to see exactly what's in there:

  import org.apache.spark.sql.functions.encode
  df.select(col("ID"), encode(col("ID"), "UTF-8").alias("byte_representation")).distinct.show(false)
  

Once you've identified the hidden characters, you can clean the ID column permanently using the appropriate regex or trim function, ensuring future distinct() operations return the correct results.

内容的提问来源于stack exchange，提问作者Brian