How to set variables in HIVE scripts

Question

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  gt    CURRENT DATE   But then I get this error      character     not supported here

User · Answer

Just in case someone needs to parameterize hive query via cli.

For eg:

hive_query.sql

SELECT * FROM foo WHERE day >= '${hivevar:CURRENT_DATE}'

Now execute above sql file from cli:

hive --hivevar CURRENT_DATE="2012-09-16" -f hive_query.sql

User · Answer

Have you tried using the dollar sign and brackets like this   SELECT    FROM foo  WHERE day  gt      CURRENT DATE

User · Answer

Try this method   set t 20  select   from myTable where age  gt     hiveconf t       it works well on my platform

User · Answer

You need to use the special hiveconf for variable substitution  e g  hive gt  set CURRENT DATE  2012-09-16   hive gt  select   from foo where day  gt     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 gt  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 gt  source  path to setup hql   and use in query  hive gt  select   from   tablename   or hive gt  select   from   hivevar tablename   I could also set a  quot local quot  tablename  which would affect the use of   tablename   but not   hivevar tablename  hive gt  set tablename newtable  hive gt  select   from   tablename  -- uses  newtable   vs hive gt  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  quot locally quot  to use in the rest of the script

User · Answer

You can store the output of another query in a variable and latter you can use the same in your code   set var select count    from My table    hiveconf var

User · Answer

One thing to be mindful of is setting strings then referring back to them  You have to make sure the quotes aren t colliding    set start date    2019-01-21    select   hiveconf start date      When setting dates then referring to them in code as the strings can conflict  This wouldn t work with the start date set above       hiveconf start date     We have to be mindful of not setting twice single or double quotes for strings when referring back to them in the query

User · Answer

There are multiple options to set variables in Hive  If you re looking to set Hive variable from inside the Hive shell  you can set it using hivevar  You can set string or integer datatypes  There are no problems with them  SET hivevar which date 20200808  select   which date    If you re planning to set variables from shell script and want to pass those variables into your Hive script  HQL  file  you can use --hivevar option while calling hive or beeline command    shell script will invoke script like this beeline --hivevar tablename testtable -f select hql  -- select hql file select   from  lt dbname gt    tablename

User · Answer

Most of the answers here have suggested to either use hiveconf or hivevar namespace to store the variable  And all those answers are right   However  there is one more namespace    There are total three namespaces available for holding variables     hiveconf - hive started with this  all the hive configuration is stored as part of this conf  Initially  variable substitution was not part of hive and when it got introduced  all the user-defined variables were stored as part of this as well  Which is definitely not a good idea  So two more namespaces were created   hivevar  To store user variables system  To store system variables     And so if you are storing a variable as part of a query  i e  date or product number  you should use hivevar namespace and not hiveconf namespace    And this is how it works    hiveconf is still the default namespace  so if you don t provide any namespace it will store your variable in hiveconf namespace    However  when it comes to referring a variable  it s not true  By default it refers to hivevar namespace  Confusing  right   It can become clearer with the following example    If you do not provide namespace as mentioned below  variable var will be stored in hiveconf namespace    set var  default namespace     So  to access this you need to specify hiveconf namespace   select   hiveconf var     And if you do not provide namespace it will give you an error as mentioned below  reason being that by default if you try to access a variable it checks in hivevar namespace only  And in hivevar there is no variable named var  select   var      We have explicitly provided hivevar namespace   set hivevar var  hivevar namespace     as we are providing the namespace this will work    select   hivevar var      And as default  workspace used during referring a variable is hivevar  the following will work too    select   var

User · Answer

Two easy ways    Using hive conf  hive gt  set USER NAME  FOO   hive gt  select   from foobar where NAME      hiveconf USER NAME      Using hive vars  On your CLI set vars and then use them in hive  set hivevar USER NAME  FOO    hive gt  select   from foobar where NAME      USER NAME    hive gt  select   from foobar where NAME      hivevar USER NAME      Documentation  https   cwiki apache org confluence display Hive LanguageManual VariableSubstitution

User · Answer

You can export the variable in shell script export CURRENT DATE  2012-09-16   Then in hiveql you like SELECT   FROM foo WHERE day       env CURRENT DATE

[hive] How to set variables in HIVE scripts

Examples related to hive

Examples related to hiveql