Creating views in the hive with the option

Question

I have a table containing rows related to different dates. I want to CREATE a VIEW that should give me data based on a date

CREATE VIEW newusers AS SELECT DISTINCT T1.uuid FROM user_visit T1 WHERE T1.firstSeen="20140522"; 

I do not want to fix WHERE T1.firstSeen = "20140522"; it can be any date, for example, 20140525, etc. Is there a way to create a view with a date as a parameter?

Answer 1

In the hive script, just replace the date of the variable:

 CREATE VIEW newusers AS SELECT DISTINCT T1.uuid FROM user_visit T1 WHERE T1.firstSeen="${hiveconf:date}"; 

Then give this variable a value when invoking the bush:

 hive --hiveconf date=20140522 -f 'create_newusers_view.hql' 

Or just install it from inside the hive:

 set date=20140522; 

Answer 2

Not sure if creating such a variable with such a variable really works. With Hive 1.2 onwards, this happens when you create a table.

 hive> create view v_t1 as select * from t_t1 where d1="${hiveconf:v_val_dt}"; OK Time taken: 6.222 seconds hive> show create table v_t1; OK CREATE VIEW `v_t1` AS select `t_t1`.`f1`, `t_t1`.`d1` from `default`.`t_t1` where `t_t1`.`d1`="'2016-01-02'" Time taken: 0.202 seconds, Fetched: 1 row(s) 

When creating a view, it always takes a static constant value. The only thing that could work would be to stay out of the clue, something like this.

 [ hdfs@sandbox ~]$ hive -hiveconf v_val_dt=2016-01-01 -e 'select * from v_t1 where d1="${hiveconf:v_val_dt}";' Logging initialized using configuration in file:/etc/hive/2.3.2.0-2950/0/hive-log4j.properties OK string_1 2016-01-01 Time taken: 7.967 seconds, Fetched: 1 row(s) [ hdfs@sandbox ~]$ hive -hiveconf v_val_dt=2016-01-06 -e 'select * from v_t1 where d1="${hiveconf:v_val_dt}";' Logging initialized using configuration in file:/etc/hive/2.3.2.0-2950/0/hive-log4j.properties OK string_6 2016-01-06 Time taken: 10.967 seconds, Fetched: 1 row(s)