kulkarni.swarnim@...)
2012-05-10, 21:52
Edward Capriolo
2012-05-10, 21:58
David Kulp
2012-05-10, 22:02
Nanda Vijaydev
2012-05-13, 02:24
Ranjith
2012-05-13, 04:05
Raja Thiruvathuru
2012-05-13, 04:13
Ranjith
2012-05-13, 17:06
Edward Capriolo
2012-05-13, 19:33
Ranjith
2012-05-13, 19:54
Ranjith
2012-05-13, 20:07
Mark Grover
2012-05-13, 20:49
Edward Capriolo
2012-05-13, 23:47
Ranjith
2012-05-14, 00:54
Mark Grover
2012-05-14, 13:40
-
Managed vs external tables in hivekulkarni.swarnim@...) 2012-05-10, 21:52
I am pretty new to hive and was trying to clearly understand the difference
between a managed and an external table.
As my current understanding stands, a managed table is a table whose data
is completely owned by hive whereas an external table is usually created to
have a hive frontend for the data managed in external systems.I would
suppose this would mean that a query on an external table goes out to fetch
data from the given external table, deserialize according to the
given/suitable SerDe and then show the output of the query in hive format.
So does this mean that cost of using external tables is much higher than
the native ones? Or is there some caching that comes into play that I am
not seeing right now.
Thanks for the help.
--
Swarnim
between a managed and an external table.
As my current understanding stands, a managed table is a table whose data
is completely owned by hive whereas an external table is usually created to
have a hive frontend for the data managed in external systems.I would
suppose this would mean that a query on an external table goes out to fetch
data from the given external table, deserialize according to the
given/suitable SerDe and then show the output of the query in hive format.
So does this mean that cost of using external tables is much higher than
the native ones? Or is there some caching that comes into play that I am
not seeing right now.
Thanks for the help.
--
Swarnim
-
Re: Managed vs external tables in hiveEdward Capriolo 2012-05-10, 21:58
The only actual differences is:
If you drop a managed table the LOCATION it refers to will be deleted.
If you drop an external table the LOCATION it refers to will not be deleted.
Confusion happens because when hive creates a managed table it defaults to :
fs.default.name+/user/hive/warehouse/+tablename
eg
hdfs://myserver:9091:/user/hive/warehouse/tablename
So people make the leap that EXTERNAL tables have a location and
managed tables do not, but MANAGED tables can have a location outside
the warehouse and EXTERNAL tables could have a location inside the
warehouse depending on how the tables/ partitions were defined.
On Thu, May 10, 2012 at 5:52 PM, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> I am pretty new to hive and was trying to clearly understand the difference
> between a managed and an external table.
>
> As my current understanding stands, a managed table is a table whose data is
> completely owned by hive whereas an external table is usually created to
> have a hive frontend for the data managed in external systems.I would
> suppose this would mean that a query on an external table goes out to fetch
> data from the given external table, deserialize according to the
> given/suitable SerDe and then show the output of the query in hive format.
>
> So does this mean that cost of using external tables is much higher than the
> native ones? Or is there some caching that comes into play that I am not
> seeing right now.
>
> Thanks for the help.
>
> --
> Swarnim
If you drop a managed table the LOCATION it refers to will be deleted.
If you drop an external table the LOCATION it refers to will not be deleted.
Confusion happens because when hive creates a managed table it defaults to :
fs.default.name+/user/hive/warehouse/+tablename
eg
hdfs://myserver:9091:/user/hive/warehouse/tablename
So people make the leap that EXTERNAL tables have a location and
managed tables do not, but MANAGED tables can have a location outside
the warehouse and EXTERNAL tables could have a location inside the
warehouse depending on how the tables/ partitions were defined.
On Thu, May 10, 2012 at 5:52 PM, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> I am pretty new to hive and was trying to clearly understand the difference
> between a managed and an external table.
>
> As my current understanding stands, a managed table is a table whose data is
> completely owned by hive whereas an external table is usually created to
> have a hive frontend for the data managed in external systems.I would
> suppose this would mean that a query on an external table goes out to fetch
> data from the given external table, deserialize according to the
> given/suitable SerDe and then show the output of the query in hive format.
>
> So does this mean that cost of using external tables is much higher than the
> native ones? Or is there some caching that comes into play that I am not
> seeing right now.
>
> Thanks for the help.
>
> --
> Swarnim
-
Re: Managed vs external tables in hiveDavid Kulp 2012-05-10, 22:02
It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
> I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
>
> As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
>
> So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
>
> Thanks for the help.
>
> --
> Swarnim
On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
> I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
>
> As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
>
> So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
>
> Thanks for the help.
>
> --
> Swarnim
-
Re: Managed vs external tables in hiveNanda Vijaydev 2012-05-13, 02:24
In hive, the raw data is in HDFS and there is a metadata layer that defines
the structure of the raw data. Table is usually a reference to metadata,
probably in a mySQL server and it contains a reference to the location of
the data in HDFS, type of delimiter or serde to use and so on.
1. With hive managed tables, when you drop a table, both the metadata in
mysql and raw data on the cluster gets deleted.
2. With external tables, when you drop a table, just the metadata gets
deleted and the raw data continues to exist on the cluster.
On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
> It's simpler than this. All files look the same -- and are often very
> simple delimited text -- whether managed or external. The only difference
> is that the files associated with a managed table are dropped when the
> table is dropped and files that are loaded into a managed table are moved
> into hive's private path. External tables never move or remove files.
> Performance is the same.
>
> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>
> > I am pretty new to hive and was trying to clearly understand the
> difference between a managed and an external table.
> >
> > As my current understanding stands, a managed table is a table whose
> data is completely owned by hive whereas an external table is usually
> created to have a hive frontend for the data managed in external systems.I
> would suppose this would mean that a query on an external table goes out to
> fetch data from the given external table, deserialize according to the
> given/suitable SerDe and then show the output of the query in hive format.
> >
> > So does this mean that cost of using external tables is much higher than
> the native ones? Or is there some caching that comes into play that I am
> not seeing right now.
> >
> > Thanks for the help.
> >
> > --
> > Swarnim
>
>
the structure of the raw data. Table is usually a reference to metadata,
probably in a mySQL server and it contains a reference to the location of
the data in HDFS, type of delimiter or serde to use and so on.
1. With hive managed tables, when you drop a table, both the metadata in
mysql and raw data on the cluster gets deleted.
2. With external tables, when you drop a table, just the metadata gets
deleted and the raw data continues to exist on the cluster.
On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
> It's simpler than this. All files look the same -- and are often very
> simple delimited text -- whether managed or external. The only difference
> is that the files associated with a managed table are dropped when the
> table is dropped and files that are loaded into a managed table are moved
> into hive's private path. External tables never move or remove files.
> Performance is the same.
>
> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>
> > I am pretty new to hive and was trying to clearly understand the
> difference between a managed and an external table.
> >
> > As my current understanding stands, a managed table is a table whose
> data is completely owned by hive whereas an external table is usually
> created to have a hive frontend for the data managed in external systems.I
> would suppose this would mean that a query on an external table goes out to
> fetch data from the given external table, deserialize according to the
> given/suitable SerDe and then show the output of the query in hive format.
> >
> > So does this mean that cost of using external tables is much higher than
> the native ones? Or is there some caching that comes into play that I am
> not seeing right now.
> >
> > Thanks for the help.
> >
> > --
> > Swarnim
>
>
-
Re: Managed vs external tables in hiveRanjith 2012-05-13, 04:05
Indexes can be built on tables managed by hive. For external tables I do not believe that to be true. Please feel to correct if I am wrong.
Thanks,
Ranjith
On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]> wrote:
> In hive, the raw data is in HDFS and there is a metadata layer that defines the structure of the raw data. Table is usually a reference to metadata, probably in a mySQL server and it contains a reference to the location of the data in HDFS, type of delimiter or serde to use and so on.
> 1. With hive managed tables, when you drop a table, both the metadata in mysql and raw data on the cluster gets deleted.
> 2. With external tables, when you drop a table, just the metadata gets deleted and the raw data continues to exist on the cluster.
>
>
> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
> It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
>
> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>
> > I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
> >
> > As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
> >
> > So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
> >
> > Thanks for the help.
> >
> > --
> > Swarnim
>
>
Thanks,
Ranjith
On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]> wrote:
> In hive, the raw data is in HDFS and there is a metadata layer that defines the structure of the raw data. Table is usually a reference to metadata, probably in a mySQL server and it contains a reference to the location of the data in HDFS, type of delimiter or serde to use and so on.
> 1. With hive managed tables, when you drop a table, both the metadata in mysql and raw data on the cluster gets deleted.
> 2. With external tables, when you drop a table, just the metadata gets deleted and the raw data continues to exist on the cluster.
>
>
> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
> It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
>
> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>
> > I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
> >
> > As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
> >
> > So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
> >
> > Thanks for the help.
> >
> > --
> > Swarnim
>
>
-
Re: Managed vs external tables in hiveRaja Thiruvathuru 2012-05-13, 04:13
No indexing in hive.
On Sunday, May 13, 2012, Ranjith wrote:
> Indexes can be built on tables managed by hive. For external tables I do
> not believe that to be true. Please feel to correct if I am wrong.
>
> Thanks,
> Ranjith
>
> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]<javascript:_e({}, 'cvml', '[EMAIL PROTECTED]');>>
> wrote:
>
> In hive, the raw data is in HDFS and there is a metadata layer that
> defines the structure of the raw data. Table is usually a reference to
> metadata, probably in a mySQL server and it contains a reference to the
> location of the data in HDFS, type of delimiter or serde to use and so on.
> 1. With hive managed tables, when you drop a table, both the metadata in
> mysql and raw data on the cluster gets deleted.
> 2. With external tables, when you drop a table, just the metadata gets
> deleted and the raw data continues to exist on the cluster.
>
>
> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]<javascript:_e({}, 'cvml', '[EMAIL PROTECTED]');>
> > wrote:
>
>> It's simpler than this. All files look the same -- and are often very
>> simple delimited text -- whether managed or external. The only difference
>> is that the files associated with a managed table are dropped when the
>> table is dropped and files that are loaded into a managed table are moved
>> into hive's private path. External tables never move or remove files.
>> Performance is the same.
>>
>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED]<javascript:_e({}, 'cvml', '[EMAIL PROTECTED]');>wrote:
>>
>> > I am pretty new to hive and was trying to clearly understand the
>> difference between a managed and an external table.
>> >
>> > As my current understanding stands, a managed table is a table whose
>> data is completely owned by hive whereas an external table is usually
>> created to have a hive frontend for the data managed in external systems.I
>> would suppose this would mean that a query on an external table goes out to
>> fetch data from the given external table, deserialize according to the
>> given/suitable SerDe and then show the output of the query in hive format.
>> >
>> > So does this mean that cost of using external tables is much higher
>> than the native ones? Or is there some caching that comes into play that I
>> am not seeing right now.
>> >
>> > Thanks for the help.
>> >
>> > --
>> > Swarnim
>>
>>
>
--
Raja Thiruvathuru
On Sunday, May 13, 2012, Ranjith wrote:
> Indexes can be built on tables managed by hive. For external tables I do
> not believe that to be true. Please feel to correct if I am wrong.
>
> Thanks,
> Ranjith
>
> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]<javascript:_e({}, 'cvml', '[EMAIL PROTECTED]');>>
> wrote:
>
> In hive, the raw data is in HDFS and there is a metadata layer that
> defines the structure of the raw data. Table is usually a reference to
> metadata, probably in a mySQL server and it contains a reference to the
> location of the data in HDFS, type of delimiter or serde to use and so on.
> 1. With hive managed tables, when you drop a table, both the metadata in
> mysql and raw data on the cluster gets deleted.
> 2. With external tables, when you drop a table, just the metadata gets
> deleted and the raw data continues to exist on the cluster.
>
>
> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]<javascript:_e({}, 'cvml', '[EMAIL PROTECTED]');>
> > wrote:
>
>> It's simpler than this. All files look the same -- and are often very
>> simple delimited text -- whether managed or external. The only difference
>> is that the files associated with a managed table are dropped when the
>> table is dropped and files that are loaded into a managed table are moved
>> into hive's private path. External tables never move or remove files.
>> Performance is the same.
>>
>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED]<javascript:_e({}, 'cvml', '[EMAIL PROTECTED]');>wrote:
>>
>> > I am pretty new to hive and was trying to clearly understand the
>> difference between a managed and an external table.
>> >
>> > As my current understanding stands, a managed table is a table whose
>> data is completely owned by hive whereas an external table is usually
>> created to have a hive frontend for the data managed in external systems.I
>> would suppose this would mean that a query on an external table goes out to
>> fetch data from the given external table, deserialize according to the
>> given/suitable SerDe and then show the output of the query in hive format.
>> >
>> > So does this mean that cost of using external tables is much higher
>> than the native ones? Or is there some caching that comes into play that I
>> am not seeing right now.
>> >
>> > Thanks for the help.
>> >
>> > --
>> > Swarnim
>>
>>
>
--
Raja Thiruvathuru
-
Re: Managed vs external tables in hiveRanjith 2012-05-13, 17:06
Starting in .7 hive introduced indexing, https://issues.apache.org/jira/browse/HIVE-417. So indexes are available in hive.
Thanks,
Ranjith
On May 12, 2012, at 11:13 PM, Raja Thiruvathuru <[EMAIL PROTECTED]> wrote:
> No indexing in hive.
>
>
> On Sunday, May 13, 2012, Ranjith wrote:
> Indexes can be built on tables managed by hive. For external tables I do not believe that to be true. Please feel to correct if I am wrong.
>
> Thanks,
> Ranjith
>
> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]> wrote:
>
>> In hive, the raw data is in HDFS and there is a metadata layer that defines the structure of the raw data. Table is usually a reference to metadata, probably in a mySQL server and it contains a reference to the location of the data in HDFS, type of delimiter or serde to use and so on.
>> 1. With hive managed tables, when you drop a table, both the metadata in mysql and raw data on the cluster gets deleted.
>> 2. With external tables, when you drop a table, just the metadata gets deleted and the raw data continues to exist on the cluster.
>>
>>
>> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>> It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
>>
>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>
>> > I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
>> >
>> > As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
>> >
>> > So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
>> >
>> > Thanks for the help.
>> >
>> > --
>> > Swarnim
>>
>>
>
>
> --
>
> Raja Thiruvathuru
Thanks,
Ranjith
On May 12, 2012, at 11:13 PM, Raja Thiruvathuru <[EMAIL PROTECTED]> wrote:
> No indexing in hive.
>
>
> On Sunday, May 13, 2012, Ranjith wrote:
> Indexes can be built on tables managed by hive. For external tables I do not believe that to be true. Please feel to correct if I am wrong.
>
> Thanks,
> Ranjith
>
> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]> wrote:
>
>> In hive, the raw data is in HDFS and there is a metadata layer that defines the structure of the raw data. Table is usually a reference to metadata, probably in a mySQL server and it contains a reference to the location of the data in HDFS, type of delimiter or serde to use and so on.
>> 1. With hive managed tables, when you drop a table, both the metadata in mysql and raw data on the cluster gets deleted.
>> 2. With external tables, when you drop a table, just the metadata gets deleted and the raw data continues to exist on the cluster.
>>
>>
>> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>> It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
>>
>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>
>> > I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
>> >
>> > As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
>> >
>> > So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
>> >
>> > Thanks for the help.
>> >
>> > --
>> > Swarnim
>>
>>
>
>
> --
>
> Raja Thiruvathuru
-
Re: Managed vs external tables in hiveEdward Capriolo 2012-05-13, 19:33
The original design docs say you can not build indexes on external tables
but I tried it in 0.8.x and confirmed you can.
On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
> Indexes can be built on tables managed by hive. For external tables I do
not believe that to be true. Please feel to correct if I am wrong.
>
> Thanks,
> Ranjith
> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]>
wrote:
>
> In hive, the raw data is in HDFS and there is a metadata layer that
defines the structure of the raw data. Table is usually a reference to
metadata, probably in a mySQL server and it contains a reference to the
location of the data in HDFS, type of delimiter or serde to use and so on.
> 1. With hive managed tables, when you drop a table, both the metadata in
mysql and raw data on the cluster gets deleted.
> 2. With external tables, when you drop a table, just the metadata gets
deleted and the raw data continues to exist on the cluster.
>
> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>>
>> It's simpler than this. All files look the same -- and are often very
simple delimited text -- whether managed or external. The only difference
is that the files associated with a managed table are dropped when the
table is dropped and files that are loaded into a managed table are moved
into hive's private path. External tables never move or remove files.
Performance is the same.
>>
>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>
>> > I am pretty new to hive and was trying to clearly understand the
difference between a managed and an external table.
>> >
>> > As my current understanding stands, a managed table is a table whose
data is completely owned by hive whereas an external table is usually
created to have a hive frontend for the data managed in external systems.I
would suppose this would mean that a query on an external table goes out to
fetch data from the given external table, deserialize according to the
given/suitable SerDe and then show the output of the query in hive format.
>> >
>> > So does this mean that cost of using external tables is much higher
than the native ones? Or is there some caching that comes into play that I
am not seeing right now.
>> >
>> > Thanks for the help.
>> >
>> > --
>> > Swarnim
>>
>
>
but I tried it in 0.8.x and confirmed you can.
On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
> Indexes can be built on tables managed by hive. For external tables I do
not believe that to be true. Please feel to correct if I am wrong.
>
> Thanks,
> Ranjith
> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]>
wrote:
>
> In hive, the raw data is in HDFS and there is a metadata layer that
defines the structure of the raw data. Table is usually a reference to
metadata, probably in a mySQL server and it contains a reference to the
location of the data in HDFS, type of delimiter or serde to use and so on.
> 1. With hive managed tables, when you drop a table, both the metadata in
mysql and raw data on the cluster gets deleted.
> 2. With external tables, when you drop a table, just the metadata gets
deleted and the raw data continues to exist on the cluster.
>
> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>>
>> It's simpler than this. All files look the same -- and are often very
simple delimited text -- whether managed or external. The only difference
is that the files associated with a managed table are dropped when the
table is dropped and files that are loaded into a managed table are moved
into hive's private path. External tables never move or remove files.
Performance is the same.
>>
>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>
>> > I am pretty new to hive and was trying to clearly understand the
difference between a managed and an external table.
>> >
>> > As my current understanding stands, a managed table is a table whose
data is completely owned by hive whereas an external table is usually
created to have a hive frontend for the data managed in external systems.I
would suppose this would mean that a query on an external table goes out to
fetch data from the given external table, deserialize according to the
given/suitable SerDe and then show the output of the query in hive format.
>> >
>> > So does this mean that cost of using external tables is much higher
than the native ones? Or is there some caching that comes into play that I
am not seeing right now.
>> >
>> > Thanks for the help.
>> >
>> > --
>> > Swarnim
>>
>
>
-
Re: Managed vs external tables in hiveRanjith 2012-05-13, 19:54
Good info Edward. Thanks.
Thanks,
Ranjith
On May 13, 2012, at 2:33 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
> The original design docs say you can not build indexes on external tables but I tried it in 0.8.x and confirmed you can.
>
> On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
> > Indexes can be built on tables managed by hive. For external tables I do not believe that to be true. Please feel to correct if I am wrong.
> >
> > Thanks,
> > Ranjith
> > On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]> wrote:
> >
> > In hive, the raw data is in HDFS and there is a metadata layer that defines the structure of the raw data. Table is usually a reference to metadata, probably in a mySQL server and it contains a reference to the location of the data in HDFS, type of delimiter or serde to use and so on.
> > 1. With hive managed tables, when you drop a table, both the metadata in mysql and raw data on the cluster gets deleted.
> > 2. With external tables, when you drop a table, just the metadata gets deleted and the raw data continues to exist on the cluster.
> >
> > On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
> >>
> >> It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
> >>
> >> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
> >>
> >> > I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
> >> >
> >> > As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
> >> >
> >> > So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
> >> >
> >> > Thanks for the help.
> >> >
> >> > --
> >> > Swarnim
> >>
> >
> >
Thanks,
Ranjith
On May 13, 2012, at 2:33 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
> The original design docs say you can not build indexes on external tables but I tried it in 0.8.x and confirmed you can.
>
> On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
> > Indexes can be built on tables managed by hive. For external tables I do not believe that to be true. Please feel to correct if I am wrong.
> >
> > Thanks,
> > Ranjith
> > On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]> wrote:
> >
> > In hive, the raw data is in HDFS and there is a metadata layer that defines the structure of the raw data. Table is usually a reference to metadata, probably in a mySQL server and it contains a reference to the location of the data in HDFS, type of delimiter or serde to use and so on.
> > 1. With hive managed tables, when you drop a table, both the metadata in mysql and raw data on the cluster gets deleted.
> > 2. With external tables, when you drop a table, just the metadata gets deleted and the raw data continues to exist on the cluster.
> >
> > On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
> >>
> >> It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
> >>
> >> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
> >>
> >> > I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
> >> >
> >> > As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
> >> >
> >> > So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
> >> >
> >> > Thanks for the help.
> >> >
> >> > --
> >> > Swarnim
> >>
> >
> >
-
Re: Managed vs external tables in hiveRanjith 2012-05-13, 20:07
Edward,
Did you confirm this through the explain plan or through the execution of the ddl alone. And have you tried buckets with external tables?
Thanks,
Ranjith
On May 13, 2012, at 2:33 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
> The original design docs say you can not build indexes on external tables but I tried it in 0.8.x and confirmed you can.
>
> On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
> > Indexes can be built on tables managed by hive. For external tables I do not believe that to be true. Please feel to correct if I am wrong.
> >
> > Thanks,
> > Ranjith
> > On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]> wrote:
> >
> > In hive, the raw data is in HDFS and there is a metadata layer that defines the structure of the raw data. Table is usually a reference to metadata, probably in a mySQL server and it contains a reference to the location of the data in HDFS, type of delimiter or serde to use and so on.
> > 1. With hive managed tables, when you drop a table, both the metadata in mysql and raw data on the cluster gets deleted.
> > 2. With external tables, when you drop a table, just the metadata gets deleted and the raw data continues to exist on the cluster.
> >
> > On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
> >>
> >> It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
> >>
> >> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
> >>
> >> > I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
> >> >
> >> > As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
> >> >
> >> > So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
> >> >
> >> > Thanks for the help.
> >> >
> >> > --
> >> > Swarnim
> >>
> >
> >
Did you confirm this through the explain plan or through the execution of the ddl alone. And have you tried buckets with external tables?
Thanks,
Ranjith
On May 13, 2012, at 2:33 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
> The original design docs say you can not build indexes on external tables but I tried it in 0.8.x and confirmed you can.
>
> On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
> > Indexes can be built on tables managed by hive. For external tables I do not believe that to be true. Please feel to correct if I am wrong.
> >
> > Thanks,
> > Ranjith
> > On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]> wrote:
> >
> > In hive, the raw data is in HDFS and there is a metadata layer that defines the structure of the raw data. Table is usually a reference to metadata, probably in a mySQL server and it contains a reference to the location of the data in HDFS, type of delimiter or serde to use and so on.
> > 1. With hive managed tables, when you drop a table, both the metadata in mysql and raw data on the cluster gets deleted.
> > 2. With external tables, when you drop a table, just the metadata gets deleted and the raw data continues to exist on the cluster.
> >
> > On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
> >>
> >> It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
> >>
> >> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
> >>
> >> > I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
> >> >
> >> > As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
> >> >
> >> > So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
> >> >
> >> > Thanks for the help.
> >> >
> >> > --
> >> > Swarnim
> >>
> >
> >
-
Re: Managed vs external tables in hiveMark Grover 2012-05-13, 20:49
Hi Ranjith,
I use buckets with external tables, no problem.
I concur with other people on the thread. Having an external table vs. managed table on HDFS should have minimal impact what operations you can perform on those tables.
Mark
----- Original Message -----
From: "Ranjith" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, May 13, 2012 4:07:48 PM
Subject: Re: Managed vs external tables in hive
Edward,
Did you confirm this through the explain plan or through the execution of the ddl alone. And have you tried buckets with external tables?
Thanks,
Ranjith
On May 13, 2012, at 2:33 PM, Edward Capriolo < [EMAIL PROTECTED] > wrote:
The original design docs say you can not build indexes on external tables but I tried it in 0.8.x and confirmed you can.
On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED] > wrote:
> Indexes can be built on tables managed by hive. For external tables I do not believe that to be true. Please feel to correct if I am wrong.
>
> Thanks,
> Ranjith
> On May 12, 2012, at 9:24 PM, Nanda Vijaydev < [EMAIL PROTECTED] > wrote:
>
> In hive, the raw data is in HDFS and there is a metadata layer that defines the structure of the raw data. Table is usually a reference to metadata, probably in a mySQL server and it contains a reference to the location of the data in HDFS, type of delimiter or serde to use and so on.
> 1. With hive managed tables, when you drop a table, both the metadata in mysql and raw data on the cluster gets deleted.
> 2. With external tables, when you drop a table, just the metadata gets deleted and the raw data continues to exist on the cluster.
>
> On Thu, May 10, 2012 at 3:02 PM, David Kulp < [EMAIL PROTECTED] > wrote:
>>
>> It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
>>
>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>
>> > I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
>> >
>> > As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
>> >
>> > So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
>> >
>> > Thanks for the help.
>> >
>> > --
>> > Swarnim
>>
>
>
I use buckets with external tables, no problem.
I concur with other people on the thread. Having an external table vs. managed table on HDFS should have minimal impact what operations you can perform on those tables.
Mark
----- Original Message -----
From: "Ranjith" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, May 13, 2012 4:07:48 PM
Subject: Re: Managed vs external tables in hive
Edward,
Did you confirm this through the explain plan or through the execution of the ddl alone. And have you tried buckets with external tables?
Thanks,
Ranjith
On May 13, 2012, at 2:33 PM, Edward Capriolo < [EMAIL PROTECTED] > wrote:
The original design docs say you can not build indexes on external tables but I tried it in 0.8.x and confirmed you can.
On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED] > wrote:
> Indexes can be built on tables managed by hive. For external tables I do not believe that to be true. Please feel to correct if I am wrong.
>
> Thanks,
> Ranjith
> On May 12, 2012, at 9:24 PM, Nanda Vijaydev < [EMAIL PROTECTED] > wrote:
>
> In hive, the raw data is in HDFS and there is a metadata layer that defines the structure of the raw data. Table is usually a reference to metadata, probably in a mySQL server and it contains a reference to the location of the data in HDFS, type of delimiter or serde to use and so on.
> 1. With hive managed tables, when you drop a table, both the metadata in mysql and raw data on the cluster gets deleted.
> 2. With external tables, when you drop a table, just the metadata gets deleted and the raw data continues to exist on the cluster.
>
> On Thu, May 10, 2012 at 3:02 PM, David Kulp < [EMAIL PROTECTED] > wrote:
>>
>> It's simpler than this. All files look the same -- and are often very simple delimited text -- whether managed or external. The only difference is that the files associated with a managed table are dropped when the table is dropped and files that are loaded into a managed table are moved into hive's private path. External tables never move or remove files. Performance is the same.
>>
>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>
>> > I am pretty new to hive and was trying to clearly understand the difference between a managed and an external table.
>> >
>> > As my current understanding stands, a managed table is a table whose data is completely owned by hive whereas an external table is usually created to have a hive frontend for the data managed in external systems.I would suppose this would mean that a query on an external table goes out to fetch data from the given external table, deserialize according to the given/suitable SerDe and then show the output of the query in hive format.
>> >
>> > So does this mean that cost of using external tables is much higher than the native ones? Or is there some caching that comes into play that I am not seeing right now.
>> >
>> > Thanks for the help.
>> >
>> > --
>> > Swarnim
>>
>
>
-
Re: Managed vs external tables in hiveEdward Capriolo 2012-05-13, 23:47
I believe I walked through the entire process.
You can ALTER TABLE a table and change it from external to managed. So
someone could always change the table to MANAGED do the index thing
and then change it back. Just be aware of the tables current status
before it is dropped.
Edward
On Sun, May 13, 2012 at 4:07 PM, Ranjith <[EMAIL PROTECTED]> wrote:
> Edward,
> Did you confirm this through the explain plan or through the execution of
> the ddl alone. And have you tried buckets with external tables?
>
> Thanks,
> Ranjith
>
> On May 13, 2012, at 2:33 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
>
> The original design docs say you can not build indexes on external tables
> but I tried it in 0.8.x and confirmed you can.
>
> On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
>> Indexes can be built on tables managed by hive. For external tables I do
>> not believe that to be true. Please feel to correct if I am wrong.
>>
>> Thanks,
>> Ranjith
>> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]>
>> wrote:
>>
>> In hive, the raw data is in HDFS and there is a metadata layer that
>> defines the structure of the raw data. Table is usually a reference to
>> metadata, probably in a mySQL server and it contains a reference to the
>> location of the data in HDFS, type of delimiter or serde to use and so on.
>> 1. With hive managed tables, when you drop a table, both the metadata in
>> mysql and raw data on the cluster gets deleted.
>> 2. With external tables, when you drop a table, just the metadata gets
>> deleted and the raw data continues to exist on the cluster.
>>
>> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>>>
>>> It's simpler than this. All files look the same -- and are often very
>>> simple delimited text -- whether managed or external. The only difference
>>> is that the files associated with a managed table are dropped when the table
>>> is dropped and files that are loaded into a managed table are moved into
>>> hive's private path. External tables never move or remove files.
>>> Performance is the same.
>>>
>>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>>
>>> > I am pretty new to hive and was trying to clearly understand the
>>> > difference between a managed and an external table.
>>> >
>>> > As my current understanding stands, a managed table is a table whose
>>> > data is completely owned by hive whereas an external table is usually
>>> > created to have a hive frontend for the data managed in external systems.I
>>> > would suppose this would mean that a query on an external table goes out to
>>> > fetch data from the given external table, deserialize according to the
>>> > given/suitable SerDe and then show the output of the query in hive format.
>>> >
>>> > So does this mean that cost of using external tables is much higher
>>> > than the native ones? Or is there some caching that comes into play that I
>>> > am not seeing right now.
>>> >
>>> > Thanks for the help.
>>> >
>>> > --
>>> > Swarnim
>>>
>>
>>
You can ALTER TABLE a table and change it from external to managed. So
someone could always change the table to MANAGED do the index thing
and then change it back. Just be aware of the tables current status
before it is dropped.
Edward
On Sun, May 13, 2012 at 4:07 PM, Ranjith <[EMAIL PROTECTED]> wrote:
> Edward,
> Did you confirm this through the explain plan or through the execution of
> the ddl alone. And have you tried buckets with external tables?
>
> Thanks,
> Ranjith
>
> On May 13, 2012, at 2:33 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
>
> The original design docs say you can not build indexes on external tables
> but I tried it in 0.8.x and confirmed you can.
>
> On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
>> Indexes can be built on tables managed by hive. For external tables I do
>> not believe that to be true. Please feel to correct if I am wrong.
>>
>> Thanks,
>> Ranjith
>> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]>
>> wrote:
>>
>> In hive, the raw data is in HDFS and there is a metadata layer that
>> defines the structure of the raw data. Table is usually a reference to
>> metadata, probably in a mySQL server and it contains a reference to the
>> location of the data in HDFS, type of delimiter or serde to use and so on.
>> 1. With hive managed tables, when you drop a table, both the metadata in
>> mysql and raw data on the cluster gets deleted.
>> 2. With external tables, when you drop a table, just the metadata gets
>> deleted and the raw data continues to exist on the cluster.
>>
>> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>>>
>>> It's simpler than this. All files look the same -- and are often very
>>> simple delimited text -- whether managed or external. The only difference
>>> is that the files associated with a managed table are dropped when the table
>>> is dropped and files that are loaded into a managed table are moved into
>>> hive's private path. External tables never move or remove files.
>>> Performance is the same.
>>>
>>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>>
>>> > I am pretty new to hive and was trying to clearly understand the
>>> > difference between a managed and an external table.
>>> >
>>> > As my current understanding stands, a managed table is a table whose
>>> > data is completely owned by hive whereas an external table is usually
>>> > created to have a hive frontend for the data managed in external systems.I
>>> > would suppose this would mean that a query on an external table goes out to
>>> > fetch data from the given external table, deserialize according to the
>>> > given/suitable SerDe and then show the output of the query in hive format.
>>> >
>>> > So does this mean that cost of using external tables is much higher
>>> > than the native ones? Or is there some caching that comes into play that I
>>> > am not seeing right now.
>>> >
>>> > Thanks for the help.
>>> >
>>> > --
>>> > Swarnim
>>>
>>
>>
-
Re: Managed vs external tables in hiveRanjith 2012-05-14, 00:54
Thanks Mark and Edward. This is good info to keep in mind. So is it fair to say that external tables offer flexibility, in that, you can have multiple schemas on the same data asset without data duplication. Is there anything else that an external table may offer versus a hive managed table or vice versa?
Thanks,
Ranjith
On May 13, 2012, at 6:47 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
> I believe I walked through the entire process.
>
> You can ALTER TABLE a table and change it from external to managed. So
> someone could always change the table to MANAGED do the index thing
> and then change it back. Just be aware of the tables current status
> before it is dropped.
>
> Edward
>
> On Sun, May 13, 2012 at 4:07 PM, Ranjith <[EMAIL PROTECTED]> wrote:
>> Edward,
>> Did you confirm this through the explain plan or through the execution of
>> the ddl alone. And have you tried buckets with external tables?
>>
>> Thanks,
>> Ranjith
>>
>> On May 13, 2012, at 2:33 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
>>
>> The original design docs say you can not build indexes on external tables
>> but I tried it in 0.8.x and confirmed you can.
>>
>> On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
>>> Indexes can be built on tables managed by hive. For external tables I do
>>> not believe that to be true. Please feel to correct if I am wrong.
>>>
>>> Thanks,
>>> Ranjith
>>> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]>
>>> wrote:
>>>
>>> In hive, the raw data is in HDFS and there is a metadata layer that
>>> defines the structure of the raw data. Table is usually a reference to
>>> metadata, probably in a mySQL server and it contains a reference to the
>>> location of the data in HDFS, type of delimiter or serde to use and so on.
>>> 1. With hive managed tables, when you drop a table, both the metadata in
>>> mysql and raw data on the cluster gets deleted.
>>> 2. With external tables, when you drop a table, just the metadata gets
>>> deleted and the raw data continues to exist on the cluster.
>>>
>>> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>>>>
>>>> It's simpler than this. All files look the same -- and are often very
>>>> simple delimited text -- whether managed or external. The only difference
>>>> is that the files associated with a managed table are dropped when the table
>>>> is dropped and files that are loaded into a managed table are moved into
>>>> hive's private path. External tables never move or remove files.
>>>> Performance is the same.
>>>>
>>>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>>>
>>>>> I am pretty new to hive and was trying to clearly understand the
>>>>> difference between a managed and an external table.
>>>>>
>>>>> As my current understanding stands, a managed table is a table whose
>>>>> data is completely owned by hive whereas an external table is usually
>>>>> created to have a hive frontend for the data managed in external systems.I
>>>>> would suppose this would mean that a query on an external table goes out to
>>>>> fetch data from the given external table, deserialize according to the
>>>>> given/suitable SerDe and then show the output of the query in hive format.
>>>>>
>>>>> So does this mean that cost of using external tables is much higher
>>>>> than the native ones? Or is there some caching that comes into play that I
>>>>> am not seeing right now.
>>>>>
>>>>> Thanks for the help.
>>>>>
>>>>> --
>>>>> Swarnim
>>>>
>>>
>>>
Thanks,
Ranjith
On May 13, 2012, at 6:47 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
> I believe I walked through the entire process.
>
> You can ALTER TABLE a table and change it from external to managed. So
> someone could always change the table to MANAGED do the index thing
> and then change it back. Just be aware of the tables current status
> before it is dropped.
>
> Edward
>
> On Sun, May 13, 2012 at 4:07 PM, Ranjith <[EMAIL PROTECTED]> wrote:
>> Edward,
>> Did you confirm this through the explain plan or through the execution of
>> the ddl alone. And have you tried buckets with external tables?
>>
>> Thanks,
>> Ranjith
>>
>> On May 13, 2012, at 2:33 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
>>
>> The original design docs say you can not build indexes on external tables
>> but I tried it in 0.8.x and confirmed you can.
>>
>> On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
>>> Indexes can be built on tables managed by hive. For external tables I do
>>> not believe that to be true. Please feel to correct if I am wrong.
>>>
>>> Thanks,
>>> Ranjith
>>> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]>
>>> wrote:
>>>
>>> In hive, the raw data is in HDFS and there is a metadata layer that
>>> defines the structure of the raw data. Table is usually a reference to
>>> metadata, probably in a mySQL server and it contains a reference to the
>>> location of the data in HDFS, type of delimiter or serde to use and so on.
>>> 1. With hive managed tables, when you drop a table, both the metadata in
>>> mysql and raw data on the cluster gets deleted.
>>> 2. With external tables, when you drop a table, just the metadata gets
>>> deleted and the raw data continues to exist on the cluster.
>>>
>>> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>>>>
>>>> It's simpler than this. All files look the same -- and are often very
>>>> simple delimited text -- whether managed or external. The only difference
>>>> is that the files associated with a managed table are dropped when the table
>>>> is dropped and files that are loaded into a managed table are moved into
>>>> hive's private path. External tables never move or remove files.
>>>> Performance is the same.
>>>>
>>>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>>>
>>>>> I am pretty new to hive and was trying to clearly understand the
>>>>> difference between a managed and an external table.
>>>>>
>>>>> As my current understanding stands, a managed table is a table whose
>>>>> data is completely owned by hive whereas an external table is usually
>>>>> created to have a hive frontend for the data managed in external systems.I
>>>>> would suppose this would mean that a query on an external table goes out to
>>>>> fetch data from the given external table, deserialize according to the
>>>>> given/suitable SerDe and then show the output of the query in hive format.
>>>>>
>>>>> So does this mean that cost of using external tables is much higher
>>>>> than the native ones? Or is there some caching that comes into play that I
>>>>> am not seeing right now.
>>>>>
>>>>> Thanks for the help.
>>>>>
>>>>> --
>>>>> Swarnim
>>>>
>>>
>>>
-
Re: Managed vs external tables in hiveMark Grover 2012-05-14, 13:40
Ranjith,
If the schema of the data changes, when using external tables, you can drop the table and re-create it on the same dataset taking care of the schema changes (hopefully, maintaining backwards compatibility).
I think you can still achieve that using alter table commands with managed tables; however, I find external tables just easier to manage, so I almost always end up making all my HDFS tables external.
Mark
Mark Grover, Business Intelligence Analyst
OANDA Corporation
www: oanda.com www: fxtrade.com
----- Original Message -----
From: "Ranjith" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, May 13, 2012 8:54:35 PM
Subject: Re: Managed vs external tables in hive
Thanks Mark and Edward. This is good info to keep in mind. So is it fair to say that external tables offer flexibility, in that, you can have multiple schemas on the same data asset without data duplication. Is there anything else that an external table may offer versus a hive managed table or vice versa?
Thanks,
Ranjith
On May 13, 2012, at 6:47 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
> I believe I walked through the entire process.
>
> You can ALTER TABLE a table and change it from external to managed. So
> someone could always change the table to MANAGED do the index thing
> and then change it back. Just be aware of the tables current status
> before it is dropped.
>
> Edward
>
> On Sun, May 13, 2012 at 4:07 PM, Ranjith <[EMAIL PROTECTED]> wrote:
>> Edward,
>> Did you confirm this through the explain plan or through the execution of
>> the ddl alone. And have you tried buckets with external tables?
>>
>> Thanks,
>> Ranjith
>>
>> On May 13, 2012, at 2:33 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
>>
>> The original design docs say you can not build indexes on external tables
>> but I tried it in 0.8.x and confirmed you can.
>>
>> On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
>>> Indexes can be built on tables managed by hive. For external tables I do
>>> not believe that to be true. Please feel to correct if I am wrong.
>>>
>>> Thanks,
>>> Ranjith
>>> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]>
>>> wrote:
>>>
>>> In hive, the raw data is in HDFS and there is a metadata layer that
>>> defines the structure of the raw data. Table is usually a reference to
>>> metadata, probably in a mySQL server and it contains a reference to the
>>> location of the data in HDFS, type of delimiter or serde to use and so on.
>>> 1. With hive managed tables, when you drop a table, both the metadata in
>>> mysql and raw data on the cluster gets deleted.
>>> 2. With external tables, when you drop a table, just the metadata gets
>>> deleted and the raw data continues to exist on the cluster.
>>>
>>> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>>>>
>>>> It's simpler than this. All files look the same -- and are often very
>>>> simple delimited text -- whether managed or external. The only difference
>>>> is that the files associated with a managed table are dropped when the table
>>>> is dropped and files that are loaded into a managed table are moved into
>>>> hive's private path. External tables never move or remove files.
>>>> Performance is the same.
>>>>
>>>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>>>
>>>>> I am pretty new to hive and was trying to clearly understand the
>>>>> difference between a managed and an external table.
>>>>>
>>>>> As my current understanding stands, a managed table is a table whose
>>>>> data is completely owned by hive whereas an external table is usually
>>>>> created to have a hive frontend for the data managed in external systems.I
>>>>> would suppose this would mean that a query on an external table goes out to
>>>>> fetch data from the given external table, deserialize according to the
>>>>> given/suitable SerDe and then show the output of the query in hive format.
If the schema of the data changes, when using external tables, you can drop the table and re-create it on the same dataset taking care of the schema changes (hopefully, maintaining backwards compatibility).
I think you can still achieve that using alter table commands with managed tables; however, I find external tables just easier to manage, so I almost always end up making all my HDFS tables external.
Mark
Mark Grover, Business Intelligence Analyst
OANDA Corporation
www: oanda.com www: fxtrade.com
----- Original Message -----
From: "Ranjith" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, May 13, 2012 8:54:35 PM
Subject: Re: Managed vs external tables in hive
Thanks Mark and Edward. This is good info to keep in mind. So is it fair to say that external tables offer flexibility, in that, you can have multiple schemas on the same data asset without data duplication. Is there anything else that an external table may offer versus a hive managed table or vice versa?
Thanks,
Ranjith
On May 13, 2012, at 6:47 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
> I believe I walked through the entire process.
>
> You can ALTER TABLE a table and change it from external to managed. So
> someone could always change the table to MANAGED do the index thing
> and then change it back. Just be aware of the tables current status
> before it is dropped.
>
> Edward
>
> On Sun, May 13, 2012 at 4:07 PM, Ranjith <[EMAIL PROTECTED]> wrote:
>> Edward,
>> Did you confirm this through the explain plan or through the execution of
>> the ddl alone. And have you tried buckets with external tables?
>>
>> Thanks,
>> Ranjith
>>
>> On May 13, 2012, at 2:33 PM, Edward Capriolo <[EMAIL PROTECTED]> wrote:
>>
>> The original design docs say you can not build indexes on external tables
>> but I tried it in 0.8.x and confirmed you can.
>>
>> On Sunday, May 13, 2012, Ranjith <ranjith.raghunat [EMAIL PROTECTED]> wrote:
>>> Indexes can be built on tables managed by hive. For external tables I do
>>> not believe that to be true. Please feel to correct if I am wrong.
>>>
>>> Thanks,
>>> Ranjith
>>> On May 12, 2012, at 9:24 PM, Nanda Vijaydev <[EMAIL PROTECTED]>
>>> wrote:
>>>
>>> In hive, the raw data is in HDFS and there is a metadata layer that
>>> defines the structure of the raw data. Table is usually a reference to
>>> metadata, probably in a mySQL server and it contains a reference to the
>>> location of the data in HDFS, type of delimiter or serde to use and so on.
>>> 1. With hive managed tables, when you drop a table, both the metadata in
>>> mysql and raw data on the cluster gets deleted.
>>> 2. With external tables, when you drop a table, just the metadata gets
>>> deleted and the raw data continues to exist on the cluster.
>>>
>>> On Thu, May 10, 2012 at 3:02 PM, David Kulp <[EMAIL PROTECTED]> wrote:
>>>>
>>>> It's simpler than this. All files look the same -- and are often very
>>>> simple delimited text -- whether managed or external. The only difference
>>>> is that the files associated with a managed table are dropped when the table
>>>> is dropped and files that are loaded into a managed table are moved into
>>>> hive's private path. External tables never move or remove files.
>>>> Performance is the same.
>>>>
>>>> On May 10, 2012, at 5:52 PM, [EMAIL PROTECTED] wrote:
>>>>
>>>>> I am pretty new to hive and was trying to clearly understand the
>>>>> difference between a managed and an external table.
>>>>>
>>>>> As my current understanding stands, a managed table is a table whose
>>>>> data is completely owned by hive whereas an external table is usually
>>>>> created to have a hive frontend for the data managed in external systems.I
>>>>> would suppose this would mean that a query on an external table goes out to
>>>>> fetch data from the given external table, deserialize according to the
>>>>> given/suitable SerDe and then show the output of the query in hive format.
