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.

Strange behaviour of Hive script with CREATE TABLE ... AS and UNION ALL clause

Highlighted

Strange behaviour of Hive script with CREATE TABLE ... AS and UNION ALL clause

New Contributor

Hi!

Can somebody explain why the result is empty table in first case and filled one in second one? Difference only in additional reducer (due to ORDER BY clause)


Many thanks in advance!


Case 1 (empty result):

create table tmp as

select 'A' segment, date_add(a.StartDate,pe1.i) file_date

from

(select '2019-03-01' StartDate,'2019-12-31' EndDate ) a

lateral view posexplode(split(space(datediff(a.EndDate,a.StartDate)),' ')) pe1 as i,x

union all

select 'B' segment, date_add(a.StartDate,pe2.i) file_date

from

(select '2019-03-01' StartDate,'2019-12-31' EndDate ) a

lateral view posexplode(split(space(datediff(a.EndDate,a.StartDate)),' ')) pe2 as i,x



Case 2 (everything correct):

create table tmp as

select 'A' segment, date_add(a.StartDate,pe1.i) file_date

from

(select '2019-03-01' StartDate,'2019-12-31' EndDate ) a

lateral view posexplode(split(space(datediff(a.EndDate,a.StartDate)),' ')) pe1 as i,x

union all

select 'B' segment, date_add(a.StartDate,pe2.i) file_date

from

(select '2019-03-01' StartDate,'2019-12-31' EndDate ) a

lateral view posexplode(split(space(datediff(a.EndDate,a.StartDate)),' ')) pe2 as i,x

order by segment


One can use other ways to receive the same result: f.e. UNION instead of UNION ALL, which also produces additional reducer.

2 REPLIES 2

Re: Strange behaviour of Hive script with CREATE TABLE ... AS and UNION ALL clause

New Contributor

@Irina, both your queries should get the same result. I tried in my local and am getting the same result

107435-hortownworks.png


Highlighted

Re: Strange behaviour of Hive script with CREATE TABLE ... AS and UNION ALL clause

New Contributor

@Chandra Poola results are identical if you just select data, but try "create table ... as" instead and you'll see the difference.

Don't have an account?
Coming from Hortonworks? Activate your account here