Unable to Create Table in HIVE reading a CSV from HDFS

Question

I have issues while creating a table in Hive by reading the .csv file from HDFS. The Query is below:

CREATE EXTERNAL TABLE testmail (memberId String , email String, sentdate String,actiontype String, actiondate String, campaignid String,campaignname String)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
LOCATION '/user/hadoop/cloudera/ameeth/ca_email.csv';

Error getting. Error in metadata:

MetaException(message:hdfs://PC:8020/user/hadoop/cloudera/ameeth/ca_email.csv is not a directory or unable to create one)

Can any one help me in this. Actually I want to run such staments in a .sql file as a job

Abhishek Pathak · Accepted Answer · 2014-09-23 07:35:52Z

17

Hive picks up all the files in the directory that you specify in LOCATION. You do not need to specify the file name.

This should work :

CREATE EXTERNAL TABLE testmail (memberId String , email String, sentdate String,actiontype String, actiondate String, campaignid String,campaignname String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/hadoop/cloudera/ameeth';

answered Sep 23, 2014 at 7:35

Abhishek Pathak

1,5691 gold badge10 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Blue Whale Over a year ago

Thanks it is Working. But if I give it in the following format is throwing error

Blue Whale Over a year ago

CREATE EXTERNAL TABLE testmail (memberId String , email String, sentdate String,actiontype String, actiondate String, campaignid String,campaignname String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; LOAD DATA LOCAL INPATH '/user/hadoop/cloudera/ameeth/ca_email.csv' INTO TABLE testmail;

Blue Whale Over a year ago

FAILED: SemanticException Line 1:17 Invalid path ''/user/hadoop/cloudera/ameeth/ca_email.csv'': No files matching path hdfs gives this error

Abhishek Pathak Over a year ago

silly, silly me. You are giving the command to load the data from LOCAL INPATH. As a result hive is trying to locate the path on your local file system, not on the HDFS. If you are specifying a HDFS path, use INPATH instead of LOCAL INPATH.

venkat Over a year ago

how would hive find out which file to load If two file(.txt) resides in same location

|

marc_s · Accepted Answer · 2020-12-14 17:19:27Z

1

I had the same issue.

I changed the csv file to tab delimited text file, moved the file to hdfs and created hive table by loading the same which worked out.

You can view the file in hdfs to make sure you have got the data separated by tabs as intended and load into hive table

CREATE TABLE TABLE1
( 
     column1 string,
     column2 string,
     ....
     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
)LOAD DATA INPATH <hdfs location of tab delimited txt file> OVERWRITE INTO TABLE TABLE1

edited Dec 14, 2020 at 17:19

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 28, 2018 at 16:26

Elam

211 bronze badge

Comments

Gaurav Jain · Accepted Answer · 2014-09-23 07:38:52Z

0

go to this path

find your metastore_db folder in cloudera and remove *.lck files

command sudo rm /folder_path/metastore_db/*.lck

answered Sep 23, 2014 at 7:38

Gaurav Jain

4443 silver badges13 bronze badges

Comments

Edi Bice · Accepted Answer · 2017-03-15 19:05:56Z

0

Create a directory on HDFS, and move your ca_email.csv into it, then specify that directory as the LOCATION of your CREATE EXTERNAL TABLE command.

answered Mar 15, 2017 at 19:05

Edi Bice

5666 silver badges18 bronze badges

Collectives™ on Stack Overflow

Unable to Create Table in HIVE reading a CSV from HDFS

4 Answers 4

8 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related