Hive order by not visible column

Question

Let's say I have table test with column a,b and c and test2 with same column. Can I create a view of table test and test 2 joined together and ordered by field c from table test without showing it in final output? In my case:

CREATE VIEW AS test_view AS
SELECT a,b FROM (SELECT * FROM test ORDER BY c)
JOIN test2 ON test.a =test2.a;

Ok I test it and it is not possible because shuffle phase so maybe there is another solution to somehow do it? Table are too big to do broadcast join.

Of course I can do

CREATE VIEW AS test_view AS
SELECT a,b,c FROM test
JOIN test2 ON test.a =test2.a
ORDER BY c

and then

CREATE VIEW AS final_view AS
SELECT a,b FROM test_view;

But this solution is very not optimal

Any idea?

Answer 1

I end up with

CREATE VIEW AS test_view AS
SELECT a,b,c FROM test
JOIN test2 ON test.a =test2.a
ORDER BY c

and then

CREATE VIEW AS final_view AS
SELECT a,b FROM test_view;

It may not be optimal but it is only guarant way to get everything ordered and in my case (about 4-5 joins) it is much easier to get it on first sight. Of course we can also create test_view as temp view but this is just polishing.

But maybe some of You have some other working solution - feel free to post it:)

Answer 2

Consider view the same as a table on the upper level. Select from it returns not ordered dataset, only order by (or distribute+sort) in the upper query guarantees order. If you materialize this view as a table sorted, the result of select from it is not guaranteed to be sorted because the table is being read in parallel and rows returned not in the "original order". Classical theory says that ordered tables is a violation of 1st NF. Ordered view cannot be created in a database that conforms SQL:2003.

Order by inside view makes no sense. Hence order by invisible column in a view makes no sense as well. Use ORDER BY in the upper query instead. Only order by (or distribute+sort) in the upper query guarantees order.

Answer 3

Is this what you are looking at?

CREATE VIEW AS test_view AS
SELECT a,b FROM 
(SELECT * FROM 
test t1 JOIN test2 t2
ON test.a =test2.a
ORDER BY t1.c
) abc;