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 |
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
Related Articles
Yellowbrick and Sonra Partner to Modernize Data Warehouses by Making XML and JSON Data Conversion Faster and Easier
PALO ALTO, Calif., July 8, 2020 – Hybrid cloud data warehouse company Yellowbrick Data today announced that Sonra has joined the company’s partner program. Both Yellowbrick and Sonra power huge…
