Support Questions

Find answers, ask questions, and share your expertise

Unable to load data to hive table

avatar
Contributor

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)

1 ACCEPTED SOLUTION

avatar
Master Mentor

@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"

.

View solution in original post

4 REPLIES 4

avatar
Master Mentor

@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"

.

avatar
Contributor

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

avatar
Master Mentor

@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

.

avatar
Master Mentor

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

.