question Re: Add a dynamic variable to a Hive view in Support Questions

Add a dynamic variable to a Hive view

mikeg — Fri, 16 Sep 2022 13:42:53 GMT

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.

Re: Add a dynamic variable to a Hive view

EricL — Wed, 10 Oct 2018 05:36:29 GMT

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

Re: Add a dynamic variable to a Hive view

EricL — Fri, 01 May 2020 07:23:45 GMT

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.