Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Add a dynamic variable to a Hive view

Solved Go to solution
Highlighted

Add a dynamic variable to a Hive view

New Contributor

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

Accepted Solutions

Re: Add a dynamic variable to a Hive view

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
Highlighted

Re: Add a dynamic variable to a Hive view

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

Re: Add a dynamic variable to a Hive view

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

Don't have an account?
Coming from Hortonworks? Activate your account here