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

score 17 · Accepted Answer · answered Sep 23 '14 at 07:35

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 '14 at 07:35

Abhishek Pathak

1,569
1
10
19

Thanks it is Working. But if I give it in the following format is throwing error – Blue Whale Sep 25 '14 at 05:29
1

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 Sep 25 '14 at 05:29
FAILED: SemanticException Line 1:17 Invalid path ''/user/hadoop/cloudera/ameeth/ca_email.csv'': No files matching path hdfs gives this error – Blue Whale Sep 25 '14 at 05:32
can you do a hadoop fs -ls user/hadoop/cloudera/ameeth/ca_email.csv and share the output? – Abhishek Pathak Sep 25 '14 at 06:43
hadoop fs -ls /user/hadoop/cloudera/ameeth/ Found 2 items -rw-r--r-- 2 admin supergroup 123189 2014-09-23 14:17 /user/hadoop/cloudera/ameeth/ca_email - Copy.csv -rw-r--r-- 2 admin supergroup 123189 2014-09-25 12:33 /user/hadoop/cloudera/ameeth/ca_email.csv – Blue Whale Sep 25 '14 at 07:04
2

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. – Abhishek Pathak Sep 25 '14 at 09:29
2

how would hive find out which file to load If two file(.txt) resides in same location – venkat Dec 21 '15 at 12:15
Clearly, when doing CREATE EXTERNAL TABLE, LOCATION needs to be a directory. I suppose if you need to create a table for a single file, create a directory and move the file there, and specify the directory as LOCATION. – Edi Bice Mar 15 '17 at 18:53

score 0 · Answer 2 · answered Sep 23 '14 at 07:38

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 '14 at 07:38

Gaurav Jain

444
3
13

score 0 · Answer 3 · answered Mar 15 '17 at 19:05

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 '17 at 19:05

Edi Bice

566
6
18

score 0 · Answer 4 · edited Dec 14 '20 at 17:19

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

Unable to Create Table in HIVE reading a CSV from HDFS

4 Answers4

Linked