Unable to Create Table in HIVE reading a CSV from

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

Answer 1

I had the same issue.

I changed the csv file to tab delimitted 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 DELIMITTED FIELDS TERMINATED BY '\t';

)LOAD DATA INPATH <hdfs location of tab delimitted txt file> OVERWRITE INTO TABLE TABLE1

Answer 2

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';

Answer 3

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.

Answer 4

go to this path

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

command sudo rm /folder_path/metastore_db/*.lck