1

I am using below syntax to declare and use variable in hive sql query. But it gives me an error as below

SET aa='10';

SELECT
col1 as data,
${aa} as myVar from myTable;

ERROR: org.apache.hive.service.cli.HiveSQLException: Error while processing statement: Cannot modify aa at runtime. It is not in list of params that are allowed to be modified at runtime

I have also tried using hiveconf

SELECT ${hiveconf:aa} from myTable;
leftjoin
  • 36,950
  • 8
  • 57
  • 116
Dinesh Madhup
  • 337
  • 8
  • 21

2 Answers2

0

You can not pass variable like that. You need to use --hivevar. You can create a hql file with below script - hiveqry.hql. Pls note you can use either a normal variable or with hivevar keyword.

select * from ${hivevar:aa};
select * from ${aa};

Then call that script like below
beeline --hivevar table=myTable --hivevar aa=100 -f hiveqry.hql

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
0

Depending on Hive version, when you are setting variable without explicitly specifying the namespace (hiveconf or hivevar), it may work with hiveconf as a default namespace or may not work.

BTW this works in Hive 1.2:

SET aa='10';
SELECT ${hiveconf:aa};

If you specify the namespace explicitly when setting variable, it will work with both hivevar and hiveconf

Works:

SET hivevar:aa='10';
SELECT ${hivevar:aa};

Also works:

SET hiveconf:aa='10';
SELECT ${hiveconf:aa};

Does not work:

SET aa='10';
SELECT ${hivevar:aa} as myvar;

Also if above commands do not work, check if variable substitution is enabled (default is true):

set hive.variable.substitute=true;

If set to false, substitution does not work.

Read the documentation: LanguageManual VariableSubstitution

leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Thanks for the response. I am using Zeppelin. And When I test with your suggested query: SET hivevar:aa='10'; SELECT ${hivevar:aa}; Got this ERROR: java.lang.RuntimeException: Could not recognize dynamic form with type: hivevar – Dinesh Madhup Nov 17 '20 at 19:08
  • @DineshMadhup Read this issue: https://issues.apache.org/jira/browse/ZEPPELIN-4556 It was resolved in version 0.9.0. "This PR allow user to override the form type via paragraph local properties. e.g. %jdbc(form=native) " – leftjoin Nov 18 '20 at 09:46
  • I did try using %jdbc(form=native) but still getting error like: java.lang.RuntimeException: Could not recognize dynamic form with type: hiveconf – Dinesh Madhup Nov 18 '20 at 16:27