Support Questions

Find answers, ask questions, and share your expertise

HDP Sandbox: Cannot create table using Hive CLI

Explorer

I've uploaded a csv to hdfs using Ambari files view: /tmp/data/x.csv

 

When I try to use Hive CLI to create a table from this csv with the following command:

 

CREATE EXTERNAL TABLE IF NOT EXISTS test
(id STRING,Code STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'hdfs://127.0.0.1:8020/user/root/tmp/data/x.csv';

 

I get the following error:

org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: user [hive] does not have [ALL] privilege on [hdfs://127.0.0.1:8020/user/root/tmp/data/x.csv]

 

How can I give the 'hive' user 'all' permissions to read the csv file?

1 REPLY 1

Mentor

@jhc 

This is a permission issue you can resolve that by following the following step

As root switch to hdfs
# su - hdfs

As hdfs change the file permission

$ hdfs dfs -chmod 777 /user/root/tmp/data/x.csv

That will give execute to all including on x.csv  thats not good security-wise a better option  hive would be to copy the csv file to a location like /user/hive/[somewhere]/ where access has been locked down to hive user and readable by hive

$ hdfs dfs -cp /user/root/tmp/data/x.csv /user/hive

---------- or ------------

$ hdfs dfs -mv /user/spark/test.txt /user/hive

Now change permissions for hive user

$ hdfs dfs -chown hive:hdfs /user/hive/test.txt

 Now re-running your CREATE external table command should work.

 

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.