hive view parameter and union all

[#1]

I have two tables. test1 and test2

hive> select * from test1; OK

id year

1 2017

2 2017

hive> select * from test2; OK

no year

2 2017

3 2017

query :

select id, year from test1 where id > 1

union all

select no, year from test2 where no > 1

question 1: if i put the above query in a view and can I pass a parameter to it to use in the where clause (for id and no) ?

question 2 : can i frame the above query without the union all

appreciate the feedback.

[#2]

I tried the below but it didn't work :

hive> create view testview as select * from test1 where id = "{$hiveconf:id}"; OK Time taken: 0.13 seconds

hive> set id=1;

hive> select * from testview;

Above query did not return any rows.

[#3]

@n c

To define a variable in hive we need to use hivevar and hiveconf is used to set hive configurations

Please follow the below steps:

hive> set hivevar:id=1; //define id variable with 1 value
hive> create view testview as select * from test1 where id = ${hivevar:id}; //create view
hive> select * from testview; //select from view

for more details regards to hivevar vs hiveconf refer to this link.