如何在Hive SQL中声明和使用变量? [英] How to declare and use variable in Hive SQL?

问题描述

我正在使用以下语法在Hive SQL查询中声明和使用变量.但这给了我如下错误

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;

错误:org.apache.hive.service.cli.HiveSQLException:处理语句时出错:无法在运行时修改aa.它不在允许在运行时修改的参数列表中

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

我也尝试过使用hiveconf

I have also tried using hiveconf

SELECT ${hiveconf:aa} from myTable;

推荐答案

取决于Hive版本，当您在不显式指定名称空间的情况下设置变量时( hiveconf 或 hivevar )，它可能与 hiveconf 作为默认名称空间一起使用，或者可能不起作用.

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.

顺便说一句，它在Hive 1.2中有效:

BTW this works in Hive 1.2:

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

如果在设置变量时显式指定名称空间，则它将同时与hivevar和hiveconf一起使用

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

作品:

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

也可以工作:

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

不起作用:

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

如果上述命令不起作用，请检查是否启用了变量替换(默认为true):

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

set hive.variable.substitute=true;

如果设置为false，则替换无效.

If set to false, substitution does not work.

阅读文档:语言手册变量替换

这篇关于如何在Hive SQL中声明和使用变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！