5

I have a table that contains rows belonging to various dates. I want to CREATE A VIEW which should give me the data based on the 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 like 20140525 etc. Is there any way that I can create a view with date as parameter?

minhas23
  • 9,291
  • 3
  • 58
  • 40

3 Answers3

3

Not really sure if creating a view with such variable actually works. With Hive 1.2 an onwards, this is what happens when you create 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 the static constant value. The one thing that might work would be staying outside the prompt, 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)
Shyam
  • 81
  • 1
  • 6
2

A different approach for this problem is creating a table in which you set key value pairs as parameters. In your view you can then reference this table.

create table load_params (key: string, value: string);
insert overwrite table load_params values ('firstSeen', '20140522');

Your view would then look like this:

create view newusers as 
select distinct T1.uuid
from user_visit T1
where T1.firstSeen = (select cast(value as int) from load_params where key = 'firstSeen');

The load_params table can be edited before each run. As you would do when setting a different parameter with set.

Joha
  • 935
  • 12
  • 32
0

In the hive script, just replace the date with a variable:

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

Then give that variable a value when invoking hive:

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

Or just set it from within hive:

set date=20140522;
Joe K
  • 18,204
  • 2
  • 36
  • 58
  • Thank you very much JOE.I am using hive version Hive 0.13.0.2.1.2.0-402(Hortonworks). It works fine with a little change in the create statement. We need to create statement so that WHERE clause do not have double quotes WHERE T1.firstSeen=${hiveconf:date}; – minhas23 Jun 06 '14 at 06:52
  • 1
    I've not been able to do this - I can only create a view using the WHERE T1.firstSeen=${hiveconf:date}; syntax where the the date variable has already been set, and then the substitution takes place when creating the view – TobyEvans Sep 15 '14 at 10:53
  • This does not create a view with a variable, rather a variable in a script. – Panagiotis Sep 29 '16 at 00:16