You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Vidhya Venkataraman <vi...@dropbox.com> on 2012/07/27 18:51:16 UTC
Create external table like.
Hi
I am using Hive 0.7.x on my dev machine (yeah we will be upgrading soon
:) )
I used the statement indicated in the subject to create an external table:
*create external table ext_sample_v1 like sample_v1 location
'/hive/warehouse/sample_v1/';*
*
*
Since sample_v1 had partitions, I added some of them to ext_sample_v1 as
well:
*alter table ext_sample_v1 add partition (dt='8/1/2012');*
Select * on ext_sample_v1 yields the correct result.
One odd thing is:
*hive > describe extended ext_sample_v1;*
*
OK
**
key_index int
userid bigint
otherid bigint
dt string
Detailed Table Information Table(tableName:ext_sample_v1, dbName:default,
owner:vidhya, createTime:1343406972, lastAccessTime:0, retention:0,
sd:StorageDescriptor(cols:[FieldSchema(name:key_index, type:int,
comment:null), FieldSchema(name:userid, type:bigint, comment:null),
FieldSchema(name:otherid, type:bigint, comment:null), FieldSchema(name:dt,
type:string, comment:null)],
location:hdfs://localhost:9000/hive/warehouse/sample_v1,
inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat,
outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat,
compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null,
serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
parameters:{serialization.format=1}), bucketCols:[], sortCols:[],
parameters:{}), partitionKeys:[FieldSchema(name:dt, type:string,
comment:null)], parameters:{last_modified_by=vidhya, external=true,
last_modified_time=1343407191, transient_lastDdlTime=1343407191},
viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)
*
Notice the MANAGED_TABLE value! So, it doesn't treat this table as an
external table. In fact when I drop ext_sample_v1, it also deletes the data
associated with it. I didn't look into Hive source, but is this expected?
Is this corrected in a future version?
V
Re: Create external table like.
Posted by Vidhya Venkataraman <vi...@dropbox.com>.
Thanks for the reference Bejoy.
V
On Fri, Jul 27, 2012 at 12:36 PM, Bejoy Ks <be...@yahoo.com> wrote:
> Hi Vidhya
>
> This bug was reported and fixed in a later version of hive , Hive 0.8. An
> upgrade would set things in place.
>
> https://issues.apache.org/jira/browse/HIVE-2888
>
> Regards,
> Bejoy KS
>
> ------------------------------
> *From:* Vidhya Venkataraman <vi...@dropbox.com>
> *To:* user@hive.apache.org
> *Sent:* Friday, July 27, 2012 10:21 PM
> *Subject:* Create external table like.
>
> Hi
> I am using Hive 0.7.x on my dev machine (yeah we will be upgrading soon
> :) )
>
> I used the statement indicated in the subject to create an external
> table:
>
> *create external table ext_sample_v1 like sample_v1 location
> '/hive/warehouse/sample_v1/';*
> *
> *
> Since sample_v1 had partitions, I added some of them to ext_sample_v1 as
> well:
>
> *alter table ext_sample_v1 add partition (dt='8/1/2012');*
>
> Select * on ext_sample_v1 yields the correct result.
>
> One odd thing is:
> *hive > describe extended ext_sample_v1;*
> *
> OK
> **
> key_index int
> userid bigint
> otherid bigint
> dt string
>
> Detailed Table Information Table(tableName:ext_sample_v1, dbName:default,
> owner:vidhya, createTime:1343406972, lastAccessTime:0, retention:0,
> sd:StorageDescriptor(cols:[FieldSchema(name:key_index, type:int,
> comment:null), FieldSchema(name:userid, type:bigint, comment:null),
> FieldSchema(name:otherid, type:bigint, comment:null), FieldSchema(name:dt,
> type:string, comment:null)],
> location:hdfs://localhost:9000/hive/warehouse/sample_v1,
> inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat,
> outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat,
> compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null,
> serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
> parameters:{serialization.format=1}), bucketCols:[], sortCols:[],
> parameters:{}), partitionKeys:[FieldSchema(name:dt, type:string,
> comment:null)], parameters:{last_modified_by=vidhya, external=true,
> last_modified_time=1343407191, transient_lastDdlTime=1343407191},
> viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)
> *
>
>
> Notice the MANAGED_TABLE value! So, it doesn't treat this table as an
> external table. In fact when I drop ext_sample_v1, it also deletes the data
> associated with it. I didn't look into Hive source, but is this expected?
> Is this corrected in a future version?
>
> V
>
>
>
>
Re: Create external table like.
Posted by Bejoy Ks <be...@yahoo.com>.
Hi Vidhya
This bug was reported and fixed in a later version of hive , Hive 0.8. An upgrade would set things in place.
https://issues.apache.org/jira/browse/HIVE-2888
Regards,
Bejoy KS
________________________________
From: Vidhya Venkataraman <vi...@dropbox.com>
To: user@hive.apache.org
Sent: Friday, July 27, 2012 10:21 PM
Subject: Create external table like.
Hi
I am using Hive 0.7.x on my dev machine (yeah we will be upgrading soon :) )
I used the statement indicated in the subject to create an external table:
create external table ext_sample_v1 like sample_v1 location '/hive/warehouse/sample_v1/';
Since sample_v1 had partitions, I added some of them to ext_sample_v1 as well:
alter table ext_sample_v1 add partition (dt='8/1/2012');
Select * on ext_sample_v1 yields the correct result.
One odd thing is:
hive > describe extended ext_sample_v1;
OKkey_indexint
useridbigint
otheridbigint
dtstring
Detailed Table InformationTable(tableName:ext_sample_v1, dbName:default, owner:vidhya, createTime:1343406972, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:key_index, type:int, comment:null), FieldSchema(name:userid, type:bigint, comment:null), FieldSchema(name:otherid, type:bigint, comment:null), FieldSchema(name:dt, type:string, comment:null)], location:hdfs://localhost:9000/hive/warehouse/sample_v1, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}), partitionKeys:[FieldSchema(name:dt, type:string, comment:null)], parameters:{last_modified_by=vidhya, external=true, last_modified_time=1343407191, transient_lastDdlTime=1343407191},
viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)
Notice the MANAGED_TABLE value! So, it doesn't treat this table as an external table. In fact when I drop ext_sample_v1, it also deletes the data associated with it. I didn't look into Hive source, but is this expected? Is this corrected in a future version?
V