CODEWITHSUNDEEP
hadoophive
Luís Bianchin
Luís Bianchin

Reputation: 2486

Order on Hive UNION ALL

Say I have two tables with the following data:

A1
| c1 | c2 |
| a  | b  |

A2
| c1 | c2 |
| c  | d  |

I would like to run a select returning the data of both tables, with A1 data above A2 data:

A1 U A2
| c1 | c2 |
| a  | b  |
| c  | d  |

So on Hive I could do something like:

SELECT * FROM A1
UNION ALL
SELECT * FROM A2

But that does not produce the correct results. How could I enforce the order that the UNION ALL happens? Or would be another solution to produce this output?

Upvotes: 1

Views: 3922

Answers (3)

Jignesh
Jignesh

Reputation: 141

Without order by it gives arbitrary result, it doesn't guarantee same result every time. I recommended to append order by sort_char clause, to achieve same result every time.

Query: select sort_char,c1,c2 from (select '1' as sort_char,c1,c2 from tbl1 union all select '2' as sort_char,c1,c2 from tbl2) a order by sort_char;

Upvotes: 0

Prix
Prix

Reputation: 19

Hey Jason your solution worked for my query as well.. I tried doing the same thing.. here I have added UNION ALL in your query.. and it worked for me.. thanks for the Idea of adding one more column to the table.

select sort_char,c1,c2 from (select '1' as sort_char,c1,c2 from tbl1 union all select '2' as sort_char,c1,c2 from tbl2) a;

Without add a new field, below query also worked for me..

select c1,c2 from (select c1,c2 from tbl1 union all select c1,c2 from tbl2) tbl3;

Upvotes: 1

Jason Rosendale
Jason Rosendale

Reputation: 593

Would it mess up your workflow to have an extra column in your hive output? If not, you could use:

select sort_char, c1, c2 from (
  select '1' as sort_char, c1, c2 from A1
  union
  select '2' as sort_char, c1, c2 from A2
) A3 sort by sort_char

Upvotes: 3

Related Questions