You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by "Lu, Wei" <wl...@microstrategy.com> on 2012/03/14 07:34:16 UTC
We cannot insert data to external table right?
Hi ,
Can we insert data to external hive tables?
1) Create an external table
create external table binary_tbl_local(byt TINYINT, bl boolean, it int, lng BIGINT, flt float, dbl double, shrt SMALLINT, str string) row format serde 'org.apache.hadoop.hive.contrib.serde2.TypedBtesSerDe' stored as sequencefile location 'file:////home/wlu/hive_local';
/home/wlu/hive_local is empty, and I insert data to the external table:
2) Insert data to the table
insert overwrite table binary_tbl_local select * from another_tbl_hdfs;
But there is still nothing under the local folder. And "select * from binary_tbl_local" returns no results.
So I guess we cannot insert data to external table right?
Regards,
Wei
Re: We cannot insert data to external table right?
Posted by Gabi D <ga...@gmail.com>.
I haven't tried external table with a none hdfs location. external with
hdfs location works great.
Question in this case - on which machine does hive look for the external
location (as it is a clustered env)? maybe you are not looking at the right
one ...
On Wed, Mar 14, 2012 at 8:34 AM, Lu, Wei <wl...@microstrategy.com> wrote:
> Hi ,****
>
> ** **
>
> Can we insert data to external hive tables?****
>
> ** **
>
> **1) **Create an external table****
>
> create external table binary_tbl_local(byt TINYINT, bl boolean, it int,
> lng BIGINT, flt float, dbl double, shrt SMALLINT, str string) row format
> serde 'org.apache.hadoop.hive.contrib.serde2.TypedBtesSerDe' stored as
> sequencefile location 'file:////home/wlu/hive_local';****
>
> ** **
>
> /home/wlu/hive_local is empty, and I insert data to the external table:***
> *
>
> **2) **Insert data to the table****
>
> insert overwrite table binary_tbl_local select * from another_tbl_hdfs;***
> *
>
> ** **
>
> But there is still nothing under the local folder. And “select * from
> binary_tbl_local” returns no results.****
>
> ** **
>
> So I guess we cannot insert data to external table right?****
>
> ** **
>
> Regards,****
>
> Wei****
>
RE: We cannot insert data to external table right?
Posted by "Lu, Wei" <wl...@microstrategy.com>.
Hi Bejoy.K.S
I can understand that we can create external table on hdfs, and we can also select/insert data for the external table. The question is I need to create the external table on a local fs, because there will be other applications access the data on local disk (and the applications are not allowed to access the hadoop).
Re
From: Bejoy Ks [mailto:bejoy_ks@yahoo.com]
Sent: Wednesday, March 14, 2012 7:46 PM
To: user@hive.apache.org
Subject: Re: We cannot insert data to external table right?
Hi Wei
Definitely you can do it. And infact the purpose of external tables is to manage the data dir on their own instead of making hive manage the same. It is not just meant for reading some already existing data on hdfs but also processing the same with hive QL. In the LOCATION try out specifying a hdfs dir rather than a local fs dir
Try out the same DDL with the following modification ...LOCATION '/dir/in/hdfs'
And for your question. Definitely you can insert data into External Tables
Regards
Bejoy.K.S
________________________________
From: hadoop hive <ha...@gmail.com>
To: user@hive.apache.org
Sent: Wednesday, March 14, 2012 4:04 PM
Subject: Re: We cannot insert data to external table right?
But you can put the data at same place and read through hive (actually no need to insert data through table)
On Wed, Mar 14, 2012 at 12:04 PM, Lu, Wei <wl...@microstrategy.com>> wrote:
Hi ,
Can we insert data to external hive tables?
1) Create an external table
create external table binary_tbl_local(byt TINYINT, bl boolean, it int, lng BIGINT, flt float, dbl double, shrt SMALLINT, str string) row format serde 'org.apache.hadoop.hive.contrib.serde2.TypedBtesSerDe' stored as sequencefile location 'file:////home/wlu/hive_local';
/home/wlu/hive_local is empty, and I insert data to the external table:
2) Insert data to the table
insert overwrite table binary_tbl_local select * from another_tbl_hdfs;
But there is still nothing under the local folder. And “select * from binary_tbl_local” returns no results.
So I guess we cannot insert data to external table right?
Regards,
Wei
Re: We cannot insert data to external table right?
Posted by Bejoy Ks <be...@yahoo.com>.
Hi Wei
Definitely you can do it. And infact the purpose of external tables is to manage the data dir on their own instead of making hive manage the same. It is not just meant for reading some already existing data on hdfs but also processing the same with hive QL. In the LOCATION try out specifying a hdfs dir rather than a local fs dir
Try out the same DDL with the following modification ...LOCATION '/dir/in/hdfs'
And for your question. Definitely you can insert data into External Tables
Regards
Bejoy.K.S
________________________________
From: hadoop hive <ha...@gmail.com>
To: user@hive.apache.org
Sent: Wednesday, March 14, 2012 4:04 PM
Subject: Re: We cannot insert data to external table right?
But you can put the data at same place and read through hive (actually no need to insert data through table)
On Wed, Mar 14, 2012 at 12:04 PM, Lu, Wei <wl...@microstrategy.com> wrote:
Hi ,
>
>Can we insert data to external hive tables?
>
>1) Create an external table
>create external table binary_tbl_local(byt TINYINT, bl boolean, it int, lng BIGINT, flt float, dbl double, shrt SMALLINT, str string) row format serde 'org.apache.hadoop.hive.contrib.serde2.TypedBtesSerDe' stored as sequencefile location 'file:////home/wlu/hive_local';
>
>/home/wlu/hive_local is empty, and I insert data to the external table:
>2) Insert data to the table
>insert overwrite table binary_tbl_local select * from another_tbl_hdfs;
>
>But there is still nothing under the local folder. And “select * from binary_tbl_local” returns no results.
>
>So I guess we cannot insert data to external table right?
>
>Regards,
>Wei
Re: We cannot insert data to external table right?
Posted by hadoop hive <ha...@gmail.com>.
But you can put the data at same place and read through hive (actually no
need to insert data through table)
On Wed, Mar 14, 2012 at 12:04 PM, Lu, Wei <wl...@microstrategy.com> wrote:
> Hi ,****
>
> ** **
>
> Can we insert data to external hive tables?****
>
> ** **
>
> **1) **Create an external table****
>
> create external table binary_tbl_local(byt TINYINT, bl boolean, it int,
> lng BIGINT, flt float, dbl double, shrt SMALLINT, str string) row format
> serde 'org.apache.hadoop.hive.contrib.serde2.TypedBtesSerDe' stored as
> sequencefile location 'file:////home/wlu/hive_local';****
>
> ** **
>
> /home/wlu/hive_local is empty, and I insert data to the external table:***
> *
>
> **2) **Insert data to the table****
>
> insert overwrite table binary_tbl_local select * from another_tbl_hdfs;***
> *
>
> ** **
>
> But there is still nothing under the local folder. And “select * from
> binary_tbl_local” returns no results.****
>
> ** **
>
> So I guess we cannot insert data to external table right?****
>
> ** **
>
> Regards,****
>
> Wei****
>