无需自定义UDF：Hive SQL实现集合子集判断与交集校验的方案问询

Awesome question! Let's walk through how to solve both of your required checks using native Hive functions—no custom or third-party UDFs needed. First, a quick note: since your right-hand side is always in that fixed ('a','b','c') format, we can easily convert it to a Hive array with array('a','b','c'). For the flexible left-hand side, even if it's stored as a string, we can split it into an array too (for example, if it's a string like "'a','b'", use split(replace(left_str, "'", ""), ",") to get array('a','b')).

1. Check if One Set is a Subset of Another #

Option 1: Use Built-in Array Functions (Hive 2.3+) #

The most reliable approach here is using array_except, which returns elements from the first array that aren't present in the second. If the result is an empty array, the first set is a subset of the second:

-- Check if ('a','b') is a subset of ('a','b','c')
SELECT size(array_except(array('a','b'), array('a','b','c'))) = 0 AS is_subset;
-- Returns TRUE

-- Check if ('a','d') is a subset of ('a','b','c')
SELECT size(array_except(array('a','d'), array('a','b','c'))) = 0 AS is_subset;
-- Returns FALSE

Option 2: Your String Matching Trick (Older Hive Versions) #

Your existing string-based method works great if you're stuck on an older Hive version without array_except. Just make sure to pick a delimiter that never appears in your element values (to avoid false positives/negatives):

-- Subset check with sorted string matching
SELECT instr(
    concat_ws('|', sort_array(array('B','A','C'))),
    concat_ws('|', sort_array(array('A','B')))
) > 0 AS is_subset;
-- Returns TRUE

SELECT instr(
    concat_ws('|', sort_array(array('B','A','C'))),
    concat_ws('|', sort_array(array('A','E')))
) > 0 AS is_subset;
-- Returns FALSE

Sorting both arrays ensures element order doesn't mess up the string comparison—smart move on your part!

2. Check if Two Sets Share At Least One Common Element #

Option 1: Use Built-in Array Functions (Hive 2.3+) #

If you have Hive 2.3 or newer, array_intersect is your best friend. It returns elements common to both arrays; if the result has a size greater than 0, you've got overlapping elements:

-- Check if ('a','b') and ('a','b','c') have common elements
SELECT size(array_intersect(array('a','b'), array('a','b','c'))) > 0 AS has_common;
-- Returns TRUE

-- Check if ('d','e') and ('a','b','c') have common elements
SELECT size(array_intersect(array('d','e'), array('a','b','c'))) > 0 AS has_common;
-- Returns FALSE

Option 2: Your Proposed String Approach (Older Hive Versions) #

Your initial idea is totally viable! Here's how to implement it cleanly, with safeguards:

SELECT 
    size(collect_set(split_part)) < (size(array('a','b')) + size(array('a','b','c'))) AS has_common
FROM (
    SELECT split(
        concat(
            concat_ws('|', sort_array(array('a','b'))),
            '|',
            concat_ws('|', sort_array(array('a','b','c')))
        ), '|') AS combined_array
) t
LATERAL VIEW explode(combined_array) exploded AS split_part;

Here's the breakdown:

1. We sort and concatenate both sets into delimited strings, then combine them into one big string.
2. Split the combined string into an array and explode it to individual rows.
3. Use collect_set to strip out duplicates. If the size of the deduplicated set is smaller than the sum of the original two set sizes, that means there was at least one duplicate element (aka a common element).

If you're on Hive 2.7+, you can skip the explode step and use array_distinct for a simpler query:

SELECT 
    size(array_distinct(split(
        concat(
            concat_ws('|', sort_array(array('a','b'))),
            '|',
            concat_ws('|', sort_array(array('a','b','c')))
        ), '|'))) < (2 + 3) AS has_common;

Quick Tips #

• Always pick a delimiter that won't show up in your element values (e.g., | is safe unless your elements contain pipes; if they do, use a rare character like ☃ or a custom sequence like ###).
• Stick to the native array functions if you can—they're more robust and easier to debug than string hacks.

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