title: Cannot define a dynamic parameter in a Hive query
body: <p>I'm trying to set up some Views in Hive that will take a date as a dynamic parameter. In my working  below, I've swapped to using the hiveconf variable  in the Select clause,  so we can see what's going on, but the principle remains the  same</p>
  
  <p>According to <a href="https://stackoverflow.com/questions/24059040/creating-views-in-hive-with-parameter">this</a> and <a href="http://mail-archives.apache.org/mod_mbox/hive-user/201205.mbox/%3CBAY151-W9BC976D584FD172E7D70BC0160@phx.gbl%3E" rel="noreferrer">this</a> , I should be  able to include a statement in " ${hiveconf:dateRangeFrom}" in my Create View statement, supply the hiveconf:dateRangeFrom variable at run time for maximum happiness, but  this  is  just not happening - Hive appears to be using whatever value is assigned to the variable when the View is created and hard-coding it into the View definition, not substituting it at runtime as you might expect.</p>
  
  <p>I've got a workaround, whereby I supply a parameter to a sql file that then creates all the views, substituting the desired value in, but this isn't sustainable</p>
  
  <p>All the working is below, so you can see how I came to this conclusion. Any ideas?</p>
  
  <hr>
  
  <h2>1) Supply a hiveconf value to a simple query</h2>
  
  <p>(needs to be a date for the ultimate query)</p>
  
  <pre><code>hive -e "Select  ${hiveconf:dateRangeFrom} , unix_timestamp(${hiveconf:dateRangeFrom} , 'yyyy-MM-dd');"  --hiveconf dateRangeFrom='2014-01-01'
  </code></pre>
  
  <p>The date will be returned as suppled, and converted to a unix timestamp (eg "2014-01-01"=1388534400, "2014-09-12"=41047640). The script can be be run repeatedly with results changing accordingly with the  parameter.</p>
  
  <h2>2) Create a view that returns this data</h2>
  
  <pre><code>CREATE VIEW get_date AS 
  SELECT  ${hiveconf:dateRangeFrom}, unix_timestamp(${hiveconf:dateRangeFrom} , 'yyyy-MM-dd');
  </code></pre>
  
  <p>This returns the error:</p>
  
  <pre><code>FAILED: ParseException line 2:8 cannot recognize input near '$' '{' 'hivevar' in  select clause
  </code></pre>
  
  <p>Presumably because it is trying to do a replacement, but the ${hivevar:dateRangeFrom} variable has not been initialized at this point</p>
  
  <p>According to:
  <a href="https://stackoverflow.com/questions/24059040/creating-views-in-hive-with-parameter">Creating Views in Hive with parameter</a> and 
  <a href="http://mail-archives.apache.org/mod_mbox/hive-user/201205.mbox/%3CBAY151-W9BC976D584FD172E7D70BC0160@phx.gbl%3E" rel="noreferrer">http://mail-archives.apache.org/mod_mbox/hive-user/201205.mbox/%3CBAY151-W9BC976D584FD172E7D70BC0160@phx.gbl%3E</a></p>
  
  <p>Then variables can  be used in Hive views, long as quotes are used around them:</p>
  
  <pre><code>CREATE VIEW get_date AS 
  SELECT  "${hiveconf:dateRangeFrom}", unix_timestamp("${hiveconf:dateRangeFrom}" , 'yyyy-MM-dd');
  </code></pre>
  
  <p>This allows the view to be created, so trying to call the view using a parameter:</p>
  
  <pre><code>hive -e "Select  * from get_date"  --hiveconf dateRangeFrom='2014-01-01'
  </code></pre>
  
  <p>just returns the variable name:</p>
  
  <pre><code>${hiveconf:dateRangeFrom}       NULL
      Time taken: 20.614 seconds, Fetched: 1 row(s)
  </code></pre>
  
  <p>Using single quotes instead:</p>
  
  <pre><code>DROP VIEW get_date;
  CREATE VIEW get_date AS 
  SELECT  '${hiveconf:dateRangeFrom}', unix_timestamp('${hiveconf:dateRangeFrom} ', 'yyyy-MM-dd');
  </code></pre>
  
  <p>Gives the same result, just the variable name.</p>
  
  <h2>3) Create a view in an interactive session with the variable already set</h2>
  
  <pre><code>SET hiveconf:dateRangeFrom="2014-02-01";
  </code></pre>
  
  <p>Rebuild the original view, with the variables without quotes</p>
  
  <pre><code>DROP VIEW get_date;
  CREATE VIEW get_date AS 
  SELECT  ${hiveconf:dateRangeFrom}, unix_timestamp(${hiveconf:dateRangeFrom} , 'yyyy-MM-dd');
  </code></pre>
  
  <p>Then calling "select * from get_date;" from within the session gives the expected result.</p>
  
  <p>As does calling  from the command line, with the same parameter value:</p>
  
  <pre><code>hive -e "Select  * from get_date;"  --hiveconf dateRangeFrom='2014-02-01'
  </code></pre>
  
  <p>However, if we call the view with a different parameter, then we still get the original answer:</p>
  
  <pre><code>hive -e "Select  * from get_date;"  --hiveconf dateRangeFrom='2014-09-12'
  
      2014-02-01      1391212800
      Time taken: 24.773 seconds, Fetched: 1 row(s)
  </code></pre>
  
  <p>If we set the variable inside a new session:</p>
  
  <pre><code>SET hiveconf:dateRangeFrom="2014-06-01";
  </code></pre>
  
  <p>or even not  set it all, we still get the  same  result</p>
  
  <p>Looking at the extended view definition, the reason is obvious:</p>
  
  <pre><code>hive&gt; describe extended get_date;
  OK
  _c0                     string
  _c1                     bigint
  
  Detailed Table Information      Table(tableName:get_date, dbName:default, owner:
  36015to, createTime:1410523149, lastAccessTime:0, retention:0, sd:StorageDescrip
  tor(cols:[FieldSchema(name:_c0, type:string, comment:null), FieldSchema(name:_c1
  , type:bigint, comment:null)], location:null, inputFormat:org.apache.hadoop.mapr
  ed.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequen
  ceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:nu
  ll, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameter
  s:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValu
  eLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{
  transient_lastDdlTime=1410523149}, ***viewOriginalText:SELECT  "2014-02-01", unix_t
  imestamp("2014-02-01" , 'yyyy-MM-dd'), viewExpandedText:SELECT  "2014-02-01", un
  ix_timestamp("2014-02-01" , 'yyyy-MM-dd')***, tableType:VIRTUAL_VIEW)
  Time taken: 0.123 seconds, Fetched: 4 row(s)
  </code></pre>
  
  <p>The variable substitution took place when the  view was created, and hard-coded that  date into the  definition: </p>
  
  <p>viewOriginalText:SELECT  "2014-02-01", unix_t
      imestamp("2014-02-01" , 'yyyy-MM-dd'), viewExpandedText:SELECT  "2014-02-01", un
      ix_timestamp("2014-02-01" , 'yyyy-MM-dd')</p>
  
  <h2>4) Switch off variable  subsitution</h2>
  
  <p>Hive is clearly putting in the current value of the variable at run-time, so I tried switching  it off and recreating the query:</p>
  
  <pre><code>hive&gt; set hive.variable.substitute;
  hive.variable.substitute=true
  hive&gt; set hive.variable.substitute = false;
  hive&gt; set hive.variable.substitute;
  hive.variable.substitute=false
  </code></pre>
  
  <p>The Create View statement still fails with the same error:</p>
  
  <pre><code>FAILED: ParseException line 2:8 cannot recognize input near '$' '{' 'hiveconf' in select clause
  </code></pre>
  
  <p>5) Workaround</p>
  
  <p>If we create a sql file that creates the views, testParam.sql, we can sort of get around the problem:</p>
  
  <pre><code>DROP VIEW get_date;
  CREATE VIEW get_date AS
  SELECT  ${hivevar:dateRangeFrom}, unix_timestamp(${hivevar:dateRangeFrom} , 'yyyy-MM-dd');
  SELECT * FROM get_date;
  </code></pre>
  
  <p>Calling that from the command line gives the expected results:</p>
  
  <pre><code>hive -f testParam.sql --hiveconf dateRangeFrom='2014-08-01'
  2014-08-01      1406847600
  Time taken: 20.763 seconds, Fetched: 1 row(s)
  
  
  hive -f testParam.sql --hiveconf dateRangeFrom='2014-09-12'
  2014-09-12      1410476400
  Time taken: 19.74 seconds, Fetched: 1 row(s)    
  </code></pre>
  
  <p>This does work, and will be fine for now, but is hardly ideal for a distributed, multi-user environment. Looking at the view meta-data, we can see that the view is always destroyed and rebuilt with the latest parameters:</p>
  
  <pre><code>transient_lastDdlTime=1410525287}, viewOriginalText:SELECT  '2014-09-12', unix_timestamp('2014-09-12' , 'yyyy-MM-dd'), viewExpandedText:SELECT  '2014-09-12', unix_timestamp('2014-09-12' , 'yyyy-MM-dd'), tableType:VIRTUAL_VIEW)
  </code></pre>
  
  <p>So, how to create a view that can be supplied with dynamic parameters at runtime without constantly rebuilding it</p>