如何通过HIVE去除TXT文件行内重复元素？含无分隔场景

Hey there! Let's work through your Hive deduplication problem since you're new to HQL. I'll break this down into two scenarios: your current space-separated case, and the no-separator case you're curious about.

1. Removing duplicates from space-separated numbers #

Your table remove_dup has a string column elements with space-separated numbers that have duplicates. Here's a straightforward way to clean this up:

We'll split the string into an array of individual numbers, explode that array into separate rows, use collect_set (which automatically removes duplicates) to gather unique values, then join them back into a single string.

SELECT 
  concat_ws(' ', collect_set(num)) AS deduplicated_elements
FROM 
  remove_dup
LATERAL VIEW explode(split(elements, ' ')) exploded AS num
GROUP BY elements;

Let's break down each part: #

• split(elements, ' '): Splits your string into an array where each element is a single number (e.g., "1 2 3 4" becomes ["1","2","3","4"]). If you might have multiple spaces between numbers, use split(elements, '\\s+') instead to handle any whitespace.
• LATERAL VIEW explode(...): Turns each element of the array into its own row, so each number gets a separate line.
• collect_set(num): Gathers all unique numbers into a set (duplicates are dropped automatically).
• concat_ws(' ', ...): Joins the unique numbers back into a single string with spaces between them.

2. Removing duplicates when numbers have no spaces #

If your input string was a continuous sequence like "1234455718246282375234126872" (no spaces between digits), the approach is similar—we just split the string into individual characters instead of space-separated chunks:

SELECT 
  concat_ws('', collect_set(char)) AS deduplicated_elements
FROM 
  remove_dup
LATERAL VIEW explode(split(elements, '')) exploded AS char
GROUP BY elements;

How this works: #

• split(elements, ''): Splits the string into an array of single characters (e.g., "1234" becomes ["1","2","3","4"]).
• The rest of the steps are the same: explode into rows, use collect_set to remove duplicates, then concat_ws('', ...) to join the unique characters back into a continuous string.

Quick note about order: #

Keep in mind that collect_set returns elements in arbitrary order. If you need to preserve the order of the first occurrence of each number (e.g., keep 1 before 2 as they appear in the original string), you'll need a slightly more complex approach using window functions. Here's how to do that for the space-separated case:

WITH numbered_elements AS (
  SELECT 
    elements,
    num,
    pos,
    -- Mark the first occurrence of each number in the original string
    row_number() OVER (PARTITION BY elements, num ORDER BY pos) AS occurrence
  FROM 
    remove_dup
  -- Use posexplode to get both the value and its position in the array
  LATERAL VIEW posexplode(split(elements, ' ')) exploded AS pos, num
)
SELECT 
  elements,
  concat_ws(' ', collect_list(num) OVER (PARTITION BY elements ORDER BY pos)) AS deduplicated_elements
FROM 
  numbered_elements
WHERE occurrence = 1
GROUP BY elements, num, pos, occurrence;

This keeps the original order by tracking the position of each number and only keeping the first occurrence, then collecting them in the original position order.

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