Unable to create external table on HIVE

Question

IN LMS I cannot create an external table using location I am getting error below. Could you please help?

hive> CREATE EXTERNAL TABLE IF NOT EXISTS edureka_762118.NYSE_daily 

> (exchange_name STRING, 

> stock_symbol STRING, 

> stock_date DATE, 

> stock_price_open FLOAT, 

> stock_price_high FLOAT, 

> stock_price_low FLOAT, 

> stock_price_close FLOAT, 

> stock_volume FLOAT, 

> stock_price_adj_close FLOAT 

> ) 

> ROW FORMAT DELIMITED 

> FIELDS TERMINATED BY ',' 

 > LINES TERMINATED BY '\n' 

 > STORED AS TEXTFILE 

> LOCATION '/user/edureka_762118/NYSE_daily_prices_Q.csv';

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:hdfs://nameservice1/user/edureka_762118/NYSE_daily_prices_Q.csv is not a directory or unable to create one)

Thanks

Ozer

Answer 1

Hi @Ozer. The LOCATION is used to specify the location to store the table and this location overwrites the Hive's default location. The reason for your error is that you are specifying a csv file for the LOCATION option. Instead, mention a directory and it should work. 

Comments

how to store the table in csv then? it will end up as a textfile not csv

---

Everything is fine. You just need to give the path till the folder name. And as it already mentioned that fields terminated by ',' means it is a CSV file.

Answer 2

Hi @Ozer,

This error occurred because there is no need of specify the file name in the location, hive automatically take all the files in the directory that you specify in location. I hope your command without the file name will work.

hive> CREATE EXTERNAL TABLE IF NOT EXISTS edureka_762118.NYSE_daily 

> (exchange_name STRING, 

> stock_symbol STRING, 

> stock_date DATE, 

> stock_price_open FLOAT, 

> stock_price_high FLOAT, 

> stock_price_low FLOAT, 

> stock_price_close FLOAT, 

> stock_volume FLOAT, 

> stock_price_adj_close FLOAT 

> ) 

> ROW FORMAT DELIMITED 

> FIELDS TERMINATED BY ',' 

 > LINES TERMINATED BY '\n' 

 > STORED AS TEXTFILE 

> LOCATION '/user/edureka_762118