如何在HIVE脚本中设置变量 [英] How to set variables in HIVE scripts

问题描述

我正在寻找Hive QL中SET varname = value的SQL等价物

我知道我可以这样做：

  SET CURRENT_DATE ='2012-09-16'; 
 SELECT * FROM foo WHERE day> = @CURRENT_DATE 
  

但是我得到这个错误字符'@'不支持在这里

解决方案

您需要为变量使用特殊的 hiveconf 代换。
例如

  hive>设置CURRENT_DATE ='2012-09-16'; 
 hive> select * from foo where day> ='$ {hiveconf：CURRENT_DATE}'
  

同样，您可以传递命令行：

 ％hive -hiveconf CURRENT_DATE ='2012-09-16'-f test.hql 
  

请注意，还有 env 和系统变量，所以你可以参考 $ {env：USER} 例如。

要查看所有可用变量，从命令行运行

 ％hive -e'set;'
  

或者从配置单元提示符运行

  hive>组; 
  

更新：
我已经开始使用 hivevar 变量，将它们放到hql片段中，我可以使用 source 命令从hive CLI中包含这些变量（或从命令行作为-i选项传递）。
这里的好处是，变量可以使用或不使用hivevar前缀，并允许类似于全局和局部使用的东西。

因此，假设有一些设置tablename变量的 setup.hql ：

  set hivevar：tablename = mytable; 
  

然后，我可以进入配置单元：

 分群> source /path/to/setup.hql; 
  

并用于查询：

 分群> select * from $ {tablename} 
  

或

 分群> select * from $ {hivevar：tablename} 
  

我也可以设置一个本地表名，影响$ {tablename}的使用，而不是$ {hivevar：tablename}

  hive> set tablename = newtable; 
 hive> select * from $ {tablename}  - 使用'newtable'
  

vs

  hive> select * from $ {hivevar：tablename}  - 仍然使用原来的'mytable'
  

可能没有' t对CLI来说意味着太多，但是可以在使用 source 的文件中使用hql，但将一些变量本地设置为在脚本的其余部分中使用。

I'm looking for the SQL equivalent of "SET varname = value" in Hive QL

I know I can do something like this:

SET CURRENT_DATE = '2012-09-16';
SELECT * FROM foo WHERE day >= @CURRENT_DATE

But then I get this error "character '@' not supported here"

解决方案

You need to use the special hiveconf for variable substitution. e.g.

hive> set CURRENT_DATE='2012-09-16';
hive> select * from foo where day >= '${hiveconf:CURRENT_DATE}'

similarly, you could pass on command line:

% hive -hiveconf CURRENT_DATE='2012-09-16' -f test.hql

Note that there are env and system variables as well, so you can reference ${env:USER} for example.

To see all the available variables, from the command line, run

% hive -e 'set;'

or from the hive prompt, run

hive> set;

Update: I've started to use hivevar variables as well, putting them into hql snippets I can include from hive CLI using the source command (or pass as -i option from command line). The benefit here is that the variable can then be used with or without the hivevar prefix, and allow something akin to global vs local use.

So, assume have some setup.hql which sets a tablename variable:

set hivevar:tablename=mytable;

then, I can bring into hive:

hive> source /path/to/setup.hql;

and use in query:

hive> select * from ${tablename}

or

hive> select * from ${hivevar:tablename}

I could also set a "local" tablename, which would affect the use of ${tablename}, but not ${hivevar:tablename}

hive> set tablename=newtable;
hive> select * from ${tablename} -- uses 'newtable'

vs

hive> select * from ${hivevar:tablename} -- still uses the original 'mytable'

Probably doesn't mean too much from the CLI, but can have hql in a file that uses source, but set some of the variables "locally" to use in the rest of the script.

这篇关于如何在HIVE脚本中设置变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！