Unable to create external table on HIVE

+1 vote

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

Oct 14, 2019 in Big Data Hadoop by Ozer
• 130 points

edited Oct 14, 2019 by Omkar 4,529 views

2 answers to this question.

+1 vote

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. 

answered Oct 14, 2019 by Kiran
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.
+1 vote

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
answered Nov 7, 2019 by Richa

Related Questions In Big Data Hadoop

0 votes
1 answer

How to create external Hive table?

It's simple. You just have to add external ...READ MORE

answered Dec 14, 2018 in Big Data Hadoop by Omkar
• 69,230 points
810 views
0 votes
1 answer

How to create an index on Hive table?

Hi@akhtar, An Index is nothing but a pointer ...READ MORE

answered Oct 19, 2020 in Big Data Hadoop by MD
• 95,460 points
1,236 views
0 votes
1 answer

Not able to create Hive table from HDFS file

You dont have to specify the file name ...READ MORE

answered Dec 5, 2018 in Big Data Hadoop by Omkar
• 69,230 points
2,495 views
0 votes
1 answer

How to create a managed table in Hive?

You can use this command: create table employee(Name ...READ MORE

answered Dec 14, 2018 in Big Data Hadoop by Omkar
• 69,230 points
5,307 views
0 votes
1 answer

What is Metastore in Hive?

It stores metadata for Hive tables (like their schema ...READ MORE

answered Dec 20, 2018 in Big Data Hadoop by Frankie
• 9,830 points
3,073 views
+1 vote
1 answer

Hadoop Mapreduce word count Program

Firstly you need to understand the concept ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 11,380 points
10,912 views
0 votes
1 answer

hadoop.mapred vs hadoop.mapreduce?

org.apache.hadoop.mapred is the Old API  org.apache.hadoop.mapreduce is the ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 11,380 points
2,446 views
+2 votes
11 answers

hadoop fs -put command?

Hi, You can create one directory in HDFS ...READ MORE

answered Mar 16, 2018 in Big Data Hadoop by nitinrawat895
• 11,380 points
108,239 views
0 votes
1 answer

Unable to create table in Hive

It is looking like your Hadoop daemons ...READ MORE

answered Aug 6, 2019 in Big Data Hadoop by Yogi
765 views
0 votes
1 answer

How to create smaller table from big table in HIVE?

You could probably best use Hive's built-in sampling ...READ MORE

answered Sep 24, 2018 in Big Data Hadoop by digger
• 26,740 points
1,674 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP