Hive CREATE EXTERNAL TABLE with parameterized LOCA

Question

This works as expected:

 DROP TABLE mytable; 
 CREATE EXTERNAL TABLE mytable (
     Dim1 STRING,
     Dim2 STRING,
     Dim3 STRING ) 
 LOCATION 'hdfs:///user/myuser/data';

but this doesn't:

 set rootpath = 'hdfs:///user/myuser/data'; 
 DROP TABLE mytable; 
 CREATE EXTERNAL TABLE mytable (
     Dim1 STRING,
     Dim2 STRING,
     Dim3 STRING ) 
 LOCATION '${hiveconf:rootpath}';

It fails with the following error (Hive 0.9.0):

FAILED: Parse Error: line 9:11 mismatched input 'hdfs' expecting EOF near ''''

Am I doing something wrong, or is this a known issue/limitation? Are there any suggestions for workarounds?

Answer 1

I think it should read LOCATION ${hiveconf:rootpath}.

Answer 2

You can also use an environment variable which we often use in the create table DDL's. It can be referred as ${env:variable_name}