Support Questions
Find answers, ask questions, and share your expertise

How to change location of the external table in hive .

Solved Go to solution

How to change location of the external table in hive .

Expert Contributor

1) CREATE EXTERNAL TABLE IF NOT EXISTS jsont1( json string ) LOCATION '/jsam';

Now I need to change the location from where above json1 points to.

I tried this command - ALTER TABLE jsont1 SET LOCATION "/jsam/j2" ;

but getting error as below :

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. {0} is not absolute or has no scheme information. Please specify a complete absolute uri with scheme information. /jsam/j2

1 ACCEPTED SOLUTION

Accepted Solutions

Re: How to change location of the external table in hive .

Expert Contributor

Its now running using below , thanks for all your help :

hive> ALTER TABLE jsont1 SET LOCATION "hdfs:/jsam/j1";

OK Time taken: 0.245 seconds

hdfs:/ : Did you know then why its not running using hdfs:// (double slash)

View solution in original post

7 REPLIES 7

Re: How to change location of the external table in hive .

can you try this one

alter table jsont1 set location "hdfs://jsam/j2"

Re: How to change location of the external table in hive .

Expert Contributor

@Rajkumar Singh

Yes I tried this also .. getting as below

FAILED: IllegalArgumentException java.net.UnknownHostException: jsam

Below text to show jsam is available in hdfs :

hive> dfs -ls /jsam;

Found 1 items drwxr-xr-x - hadoop supergroup 0 2016-05-06 12:49 /jsam/j1

Re: How to change location of the external table in hive .

can you post the full dfs location from the output of the below command

describe formatted jsont1;

Re: How to change location of the external table in hive .

Expert Contributor

@Rajkumar Singh

hive> describe formatted jsont1; OK col_name data_type comment # col_name data_type comment json string # Detailed Table Information Database: logs Owner: hadoop CreateTime: Tue May 03 15:24:27 IST 2016 LastAccessTime: UNKNOWN Protect Mode: None Retention: 0 Location: hdfs://mycluster:8020/jsam Table Type: EXTERNAL_TABLE Table Parameters: COLUMN_STATS_ACCURATE false EXTERNAL TRUE numFiles 0 numRows -1 rawDataSize -1 totalSize 0 transient_lastDdlTime 1462269267 # Storage Information SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe InputFormat: org.apache.hadoop.mapred.TextInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Compressed: No Num Buckets: -1 Bucket Columns: [] Sort Columns: [] Storage Desc Params: serialization.format 1

Re: How to change location of the external table in hive .

@Amit Dass

can you try this

ALTER TABLE jsont1 SET LOCATION "hdfs://mycluster:8020/jsam/j1";

Re: How to change location of the external table in hive .

Expert Contributor

Its now running using below , thanks for all your help :

hive> ALTER TABLE jsont1 SET LOCATION "hdfs:/jsam/j1";

OK Time taken: 0.245 seconds

hdfs:/ : Did you know then why its not running using hdfs:// (double slash)

View solution in original post

Re: How to change location of the external table in hive .

New Contributor

The full format of an HDFS uri is:

 

 

 

hdfs://NAMESERVICE/path/to/your/file
hdfs://NAMESERVICE/path/to/your/directory

 

 

 

or

 

 

 

hdfs://ANYNAMENODE:PORT/path/to/your/file
hdfs://ANYNAMENODE:PORT/path/to/your/directory

 

 

 

 

It is ok to omit the nameservice/namenode part (to use the defaultFs).

To do it correctly, you need to keep the `hdfs://` part and relative path that starts with a `/`.

That is:

 

 

 

hdfs:///path/to/your/file

 

 

 

(Note that it looks like `hdfs:` followed by 3x `/`s)