How to set variables in HIVE scripts

by

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.

More Related Contents:

  1. Specify minimum number of generated files from Hive insert
  2. Hive Explode / Lateral View multiple arrays
  3. java.lang.RuntimeException:Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient
  4. Hive: Best way to do incremetal updates on a main table
  5. HIVE select count(*) non null returns higher value than select count(*)
  6. Execute Hive Query with IN clause parameters in parallel
  7. HiveQL: Using query results as variables
  8. Difference between Hive internal tables and external tables?
  9. How do I output the results of a HiveQL query to CSV?
  10. How to create n number of external tables with a single hdfs path using Hive
  11. Create Table in Hive with one file
  12. Find last day of a month in Hive
  13. How to update table in Hive 0.13?
  14. When creating an external table in hive can I point the location to specific files in a directory?
  15. How to skip CSV header in Hive External Table?
  16. How to update partition metadata in Hive , when partition data is manualy deleted from HDFS
  17. How can I convert array to string in hive sql?
  18. Update , SET option in Hive
  19. What is the equivalent of Presto UNNEST function in Hive
  20. How to connect Spark SQL to remote Hive metastore (via thrift protocol) with no hive-site.xml?
  21. How to optimize partitioning when migrating data from JDBC source?
  22. How to delete and update a record in Hive
  23. Why is the final reduce step extremely slow in this MapReduce? (HiveQL, HDFS MapReduce)
  24. hive regexp_extract weirdness
  25. how many mappers and reduces will get created for a partitoned table in hive
  26. What is the difference between Apache Spark SQLContext vs HiveContext?
  27. How to load data to hive from HDFS without removing the source file?
  28. Array Intersection in Spark SQL
  29. Hive loading in partitioned table
  30. How do I Combine or Merge Small ORC files into Larger ORC file?

Leave a Comment