Eva, Here is the wiki describing the syntax on changing table properties. http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL#Add_Table_Properties
________________________________ From: Eva Tse <[email protected]> Reply-To: <[email protected]> Date: Thu, 18 Feb 2010 15:54:01 -0800 To: <[email protected]>, Zheng Shao <[email protected]>, Paul Yang <[email protected]> Subject: Re: Question on modifying a table to become external Prasad, That's what we are looking for. We tried both: * alter table <table name> add [properties|property] ('EXTERNAL' ='TRUE') and it doesn't work. Could you give us the syntax where it would work? Since it is not documented, we won't hold you responsible for it :) Zheng, Your solution works also, except we'd rather delete that table or at least be able to rename it. Our external location is on S3; hence renaming is giving an error like: table new location hdfs://hive/eva_unittest/warehouse/eva is on a different file system than the old location s3n://bucketname/hive/eva_unittest/warehouse/non_external_tbl. This operation is not supported Thanks, Eva. On 2/18/10 12:13 PM, "Prasad Chakka" <[email protected]> wrote: I think you can do this by 'alter table <> add properties ('EXTERNAL' ='TRUE')' or some such. Basically Hive knows that a table is an EXTERNAL table by checking for a table propery named 'EXTERNAL' and if you add this property manually then it should be possible to make it an external table. I have not tried this so do it at your own risk :) Prasad ________________________________ From: Zheng Shao <[email protected]> Date: Thu, 18 Feb 2010 11:11:32 -0800 To: <[email protected]>, Prasad Chakka <[email protected]>, Paul Yang <[email protected]> Subject: Re: Question on modifying a table to become external There is no command to do that right now. One way to go is to create another external table pointing to the same location (and forget about the old table). Or you can move the files first, before dropping and recreating the same table. Zheng On Thu, Feb 18, 2010 at 10:22 AM, Eva Tse <[email protected]> wrote: > > We created a table without the 'EXTERNAL' qualifier but did specify a > location for the warehouse. We would like to modify this to be an external > table. We tried to drop the table, but it does delete the files in the S3 > external location. > > Is there a way we could achieve this? > > Thanks, > Eva. > > CREATE TABLE IF NOT EXISTS exampletable > ( > other_properties Map<string, string>, > event_ts_ms bigint, > hostname string > ) > PARTITIONED by (dateint int, hour int) > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' COLLECTION ITEMS TERMINATED > BY '\004' MAP KEYS TERMINATED BY '\002' stored as SEQUENCEFILE > LOCATION ' s3n://bucketname/hive/warehouse/exampletable'; -- Yours, Zheng
