Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

how to change hive external table location.

Highlighted

how to change hive external table location.

Explorer

I want to change my external table hdfs location to new path location which is Amazon S3 in my case.

I tried following query.

ALTER TABLE table_name set location 's3n://bucket/path/to/data'

But some how it is still pointing to old hdfs external path.

Is there any query I need to use in order to update hive metastore with new external data path location.

Any kind of help would be greatly appreciated .

9 REPLIES 9
Highlighted

Re: how to change hive external table location.

Expert Contributor

@Girish Chaudhari

what happened right after you executed the Alter table command? Did you get any errors?

I am assuming, you tried describe extended <table_name> to determine the location that it is referring to??

Highlighted

Re: how to change hive external table location.

Explorer

@hduraiswamy I am able to execute alter query, I didn't get any errors . Yes i have tried extended describe command as well to get location info.

Highlighted

Re: how to change hive external table location.

DROP the current table (files on HDFS are not affected for external tables), and create a new one with the same name pointing to your S3 location.

Highlighted

Re: how to change hive external table location.

Explorer

@Predrag Minovic I also tried this work around it is working partially for me as i have to recover all the table partition because somehow it doesn't detect s3 sub directories .

Highlighted

Re: how to change hive external table location.

Contributor

Check if you have provided the aws access keys correctly and if there are any exceptions reported in hive client log (e.g /tmp/<user>/hive.log).

Highlighted

Re: how to change hive external table location.

Explorer

@Rajesh Balamohan I have aws access id & secret stored in in hive-site and hdfs-site config file.

Highlighted

Re: how to change hive external table location.

Contributor

are there any exceptions reported in client log or in metastore log?

Re: how to change hive external table location.

on hive terminal run below command

alter table FpML_Data set location hdfs:/file_path_in_HDFS;

HDFS: is value against fs.defaultFS property in core-site.xml

Reply my comment if in case any query...

Highlighted

Re: how to change hive external table location.

Expert Contributor

@Girish Chaudhari

Note, when you change the location of the file by using alter command, the old data file is not moved to new location.

On your issue, 1) do you have any data files in the mentioned path?

2) Did you get any warnings / errors while you executed this ALTER command?

Don't have an account?
Coming from Hortonworks? Activate your account here