Unable to load data to hive table

[#1]

CREATE TABLE weather (wban INT, date STRING, precip INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION '/etc/hive/data/weather';

Getting error while creating the table,

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:hdfs://quickstart.cloudera:8020/etc/hive/data/weather is not a directory or unable to create one)

[#2 · Accepted solution]

@Sivakumar Mahalingam

The PATH is not valid.

It is expecting the path to be on HDFS so please verify if the PATH exist on hdfs?

# su - hdfs -c "hdfs dfs -ls /etc/hive/data/weather"

.

[#3]

bash-4.1$ hdfs dfs -ls /etc/hive/data/weather
-rw-r--r-- 1 cloudera supergroup 220 2018-10-17 13:03 /etc/hive/data/weather

[#4]

@Sivakumar Mahalingam

Notice "-rw-r--r--" which indicate that the supplied path is a File (Not a Directory) Thats the reason you got an error earlier like:

hdfs://quickstart.cloudera:8020/etc/hive/data/weather is not a directory or unable to create one

.

You have the mentioned PATH as a "file" it should be directory. Try running the following command and then

# hdfs dfs -ls /etc/hive/data

.

[#5]

@Sivakumar Mahalingam

Additionally there are few syntax errors in your Hive Query.

Also the "date" is a reserved keyword so you should not use it. Or you will have to apply hive additional params to tell hive to allow using reserved keywords like following:

set hive.support.sql11.reserved.keywords=false;

Create Directory on HDFS:

# su - hdfs -c "hdfs dfs -mkdir -p /user/hive/data/weather"
# su - hdfs -c "hdfs dfs -chown -R hive:hadoop /user/hive/data/weather"
# su - hdfs -c "hdfs dfs -chmod -R 777 /user/hive/data/weather"

NOTE: Above is just dummy directory creation instructions ... you should change the permission based on your requirement.

The try creating the table as following:

CREATE TABLE weather ( wban int, date1 String, precip int) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
LOCATION '/user/hive/data/weather';

.