CODEWITHSUNDEEP
hadoophivehdfshiveql
uh_big_mike_boi
uh_big_mike_boi

Reputation: 3470

Trying to replace HIVE string literal with variable in HQL

Hi I have a simple create table statement...

set location_stage=${env:HDFS_STAGING_DIR};
CREATE EXTERNAL TABLE IF NOT EXISTS stage.test(
TEST_ID INT,
TEST_NAME STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = ",",
   "quoteChar"     = "\"",
   "escapeChar"    = "\\")
STORED AS TEXTFILE
LOCATION "${hivevar:location_stage}";

I don't need the 'hivevar:location_stage' - I just need the 'env:HDFS_STAGING_DIR'. However, neither is working. I have used both in the 'LOCATION' clause. I run both with the command line 

hive -f test_hive_variable.sql

But I get the following error...

FAILED: IllegalArgumentException java.net.URISyntaxException: Relative path in absolute URI: ${hivevar:location_stage%7D

The hive version is Hive 1.2.1000.2.4.2.0-258

Upvotes: 0

Views: 1063

Answers (1)

philantrovert
philantrovert

Reputation: 10082

Your script can be:

CREATE EXTERNAL TABLE IF NOT EXISTS stage.test(
TEST_ID INT,
TEST_NAME STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = ",",
   "quoteChar"     = "\"",
   "escapeChar"    = "\\")
STORED AS TEXTFILE
LOCATION "${HDFS_STAGING_DIR}";

And then to pass HDFS_STAGING_DIR, you can use:

hive -f test_hive_variable.hql --hivevar HDFS_STAGING_DIR="/user/hive/warehouse/staging_dir"

Upvotes: 1

Related Questions