Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

UNION distinct and all in hive

UNION distinct and all in hive

Expert Contributor
SELECT ns.subscriber_email as email
FROM 
Magento.newsletter_subscriber ns
LEFT JOIN (
select customer_email
FROM 
Magento.sales_flat_order
WHERE customer_email IS NOT NULL  
  ) AS customers
ON 
UPPER(ns.subscriber_email) = UPPER(customers.customer_email)
WHERE customers.customer_email IS NULL
AND 
ns.subscriber_email IS NOT NULL
UNION 
SELECT email as email
FROM 
Magento.customer_entity ce 
LEFT JOIN (
select customer_email
FROM 
Magento.sales_flat_order
WHERE customer_email IS NOT NULL  
  ) AS customers
ON 
UPPER(ce.email) = UPPER(customers.customer_email)
WHERE customers.customer_email IS NULL
AND 
ce.email IS NOT NULL

Why do I get this error?

 Error while compiling statement: FAILED: ParseException line 16:0 missing ALL at 'SELECT' near '

It works fine with ALL keyword but then I get duplicates and I want them removed

2 REPLIES 2

Re: UNION distinct and all in hive

I don't see an obvious error when glancing at it, but I'm more of a "try it" guy so if you post some trimmed down (and generalized) datasets (and the DDL) maybe someone could try to see what's happening at runtime.

Highlighted

Re: UNION distinct and all in hive

Try reproducing your issue using below query dont see this error in my environment...

Seems you are having version related issue, please share your version, i am using Hive 1.2.1000.2.4.0.0-169...

SELECT ns.code as code
FROM 
sample_09 ns
LEFT JOIN (
select name FROM student WHERE name IS NOT NULL  
  ) AS customers
ON 
UPPER(ns.code) = UPPER(customers.name)
WHERE customers.name IS NULL
AND 
ns.code IS NOT NULL
UNION 
SELECT code as code
FROM 
sample_07 ce 
LEFT JOIN (
select name FROM student WHERE name IS NOT NULL  
  ) AS customers
ON 
UPPER(ce.code) = UPPER(customers.name)
WHERE customers.name IS NULL
AND 
ce.code IS NOT NULL