Support Questions

Find answers, ask questions, and share your expertise

Add a dynamic variable to a Hive view

avatar
Explorer

I would like to create a Hive view that would would include a dynamic variable that would give different output depending on what was done using the set command

 

The following doesn't work.

 

create view default.view_mikeg_test as
select * from prod_integration.dim_company where company_code = ${hiveconf:company_code};

 

This produces an error and will only work if I set the variable company_code first (i.e. set company_code = '7777') and then that value gets imbedded into the view.  That's not what I want.  I have tried to use single and double quotes around ${hiveconf:company_code} as suggested in other forums.  There has to be a simple solution to this.

 

1 ACCEPTED SOLUTION

avatar
Super Guru
This is old thread, but I have found a workaround, so would like to share here.

Assuming I have a table with with a few partitions:

SHOW PARTITIONS partitioned_table;
+------------+--+
| partition |
+------------+--+
| p=1 |
| p=2 |
| p=3 |
| p=4 |
| p=5 |
+------------+--+

1. create a macro:
CREATE TEMPORARY MACRO partition_value() '1';

2. create view using the macro:
CREATE VIEW view_test AS SELECT * FROM partitioned_table WHERE p = partition_value();

3. query the view:
SELECT * FROM view_test;

4. if you want to update the value returned by the Macro, you need to DROP and CREATE it again:

DROP TEMPORARY MACRO partition_value;
CREATE TEMPORARY MACRO partition_value() '4';

5. If you exit the session, also need to create it again in the next login, as Macro will be destroyed after session ends.

View solution in original post

2 REPLIES 2

avatar
Super Guru
I don't think this is supported at the moment.

avatar
Super Guru
This is old thread, but I have found a workaround, so would like to share here.

Assuming I have a table with with a few partitions:

SHOW PARTITIONS partitioned_table;
+------------+--+
| partition |
+------------+--+
| p=1 |
| p=2 |
| p=3 |
| p=4 |
| p=5 |
+------------+--+

1. create a macro:
CREATE TEMPORARY MACRO partition_value() '1';

2. create view using the macro:
CREATE VIEW view_test AS SELECT * FROM partitioned_table WHERE p = partition_value();

3. query the view:
SELECT * FROM view_test;

4. if you want to update the value returned by the Macro, you need to DROP and CREATE it again:

DROP TEMPORARY MACRO partition_value;
CREATE TEMPORARY MACRO partition_value() '4';

5. If you exit the session, also need to create it again in the next login, as Macro will be destroyed after session ends.