You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Eva Tse <et...@netflix.com> on 2010/02/18 19:22:51 UTC
Question on modifying a table to become external
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';
Re: Question on modifying a table to become external
Posted by Eva Tse <et...@netflix.com>.
Yep, we just tried it and it works.
Thanks!
Eva.
On 2/19/10 10:25 AM, "Prasad Chakka" <pc...@facebook.com> wrote:
> 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 <et...@netflix.com>
> Reply-To: <hi...@hadoop.apache.org>
> Date: Thu, 18 Feb 2010 15:54:01 -0800
> To: <hi...@hadoop.apache.org>, Zheng Shao <zs...@gmail.com>, Paul Yang
> <py...@facebook.com>
> 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" <pc...@facebook.com> 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 <zs...@gmail.com>
>> Date: Thu, 18 Feb 2010 11:11:32 -0800
>> To: <hi...@hadoop.apache.org>, Prasad Chakka <pc...@facebook.com>, Paul
>> Yang <py...@facebook.com>
>> 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 <et...@netflix.com> 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
>>
>>
>
>
Re: Question on modifying a table to become external
Posted by Prasad Chakka <pc...@facebook.com>.
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 <et...@netflix.com>
Reply-To: <hi...@hadoop.apache.org>
Date: Thu, 18 Feb 2010 15:54:01 -0800
To: <hi...@hadoop.apache.org>, Zheng Shao <zs...@gmail.com>, Paul Yang <py...@facebook.com>
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" <pc...@facebook.com> 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 <zs...@gmail.com>
Date: Thu, 18 Feb 2010 11:11:32 -0800
To: <hi...@hadoop.apache.org>, Prasad Chakka <pc...@facebook.com>, Paul Yang <py...@facebook.com>
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 <et...@netflix.com> 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
Re: Question on modifying a table to become external
Posted by Eva Tse <et...@netflix.com>.
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" <pc...@facebook.com> 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 <zs...@gmail.com>
> Date: Thu, 18 Feb 2010 11:11:32 -0800
> To: <hi...@hadoop.apache.org>, Prasad Chakka <pc...@facebook.com>, Paul
> Yang <py...@facebook.com>
> 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 <et...@netflix.com> 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
>
>
Re: Question on modifying a table to become external
Posted by Prasad Chakka <pc...@facebook.com>.
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 <zs...@gmail.com>
Date: Thu, 18 Feb 2010 11:11:32 -0800
To: <hi...@hadoop.apache.org>, Prasad Chakka <pc...@facebook.com>, Paul Yang <py...@facebook.com>
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 <et...@netflix.com> 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
Re: Question on modifying a table to become external
Posted by Zheng Shao <zs...@gmail.com>.
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 <et...@netflix.com> 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