Using parameters and variables in Hive CLI
In this blog post we look at how we can address a shortcoming in the Hive ALTER TABLE statement using parameters and variables in the Hive CLI (Hive 0.13 was used).
There’s a simple way to query Hive parameter values directly from CLI
You simply execute (without specifying the value to be set):
|
1 |
SET <parameter>; |
for instance
|
1 2 3 |
SET hive.exec.compress.output; --- hive.exec.compress.output=false |
You may use those parameters directly in your queries if you need to. For that you either use hiveconf or hivevar prefixes set and used as below:
|
1 2 |
SET hivevar:TAB_SRC=x_color_depth; SELECT CONCAT('MAPPERS used: ',${hiveconf:hive.exec.compress.output}) description, '${hivevar:TAB_SRC}' FROM x_color_depth; |
The hivevar prefix works for custom defined variables and may be also initiated by defining –hivevar cli switch (see here https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Cli).
The following example illustrates a quick workaround for Hive shortcoming when renaming table that has a custom defined LOCATION.
In such case, after ALTER TABLE statement hive loses the previously defined LOCATION and sets the default one.
This needs to be restored by changing the LOCATION back and moving HDFS files.
So having the following script relocate.sql:
|
1 2 3 4 5 6 |
ALTER TABLE ${hivevar:TAB_SRC} RENAME TO ${hivevar:TAB_TGT}; !echo ALTER TABLE ${hivevar:TAB_TGT} SET LOCATION '${HLOC_CSDB}/${hivevar:TAB_TGT}'; ALTER TABLE ${hivevar:TAB_TGT} SET LOCATION '${HLOC_CSDB}/${hivevar:TAB_TGT}'; !echo dfs -mv ${hiveconf:hive.metastore.warehouse.dir}/${hivevar:TAB_TGT} ${hivevar:HLOC_CSDB}; dfs -mv ${hiveconf:hive.metastore.warehouse.dir}/${hivevar:TAB_TGT} ${hivevar:HLOC_CSDB}; !echo '${hivevar:TAB_TGT}' files successfully moved to ${hivevar:HLOC_CSDB}; |
we simply call it like:
|
1 |
hive --hivevar TAB_SRC="prob003" --hivevar TAB_TGT="prob_matrix" --hivevar HLOC_CSDB="maprfs:/mapr/<a href="http://statscluster.com/data/prd/dss/stats">statscluster.com/data/prd/dss/stats</a>" -f relocate.sql |
