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

Answer 1

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