question Re: Unable to load data to hive table in Archives of Support Questions (Read Only)

Unable to load data to hive table

sivasai2006 — Thu, 18 Oct 2018 08:57:40 GMT

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)

Re: Unable to load data to hive table

jsensharma — Thu, 18 Oct 2018 09:01:48 GMT

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

Re: Unable to load data to hive table

jsensharma — Thu, 18 Oct 2018 09:22:53 GMT

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

Re: Unable to load data to hive table

sivasai2006 — Thu, 18 Oct 2018 09:32:15 GMT

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

Re: Unable to load data to hive table

jsensharma — Thu, 18 Oct 2018 09:37:59 GMT

@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