You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Kristof Vanbecelaere <kr...@gmail.com> on 2014/07/20 21:52:05 UTC
exchange partition documentation
I think the documentation related to exchanging partitions is not accurate
https://cwiki.apache.org/confluence/display/Hive/Exchange+Partition
when I try it out on hortonworks sandbox 2.1 which runs Hive 0.13 I get
this:
hive> create table T1(a string, b string) partitioned by (ds string);
OK
Time taken: 0.72 seconds
hive> create table T2(a string, b string);
OK
Time taken: 0.357 seconds
hive> alter table T1 exchange partition (ds='1') with table T2;
FAILED: SemanticException [Error 10235]: Tables have incompatible schemas
and their partitions cannot be exchanged.
Re: exchange partition documentation
Posted by Andre Araujo <ar...@pythian.com>.
Thanks for the clarification, Navis!
On 21 July 2014 14:01, Navis류승우 <na...@nexr.com> wrote:
> HIVE-4095, originally intended to implement,
>
> alter table <target_table_name> exchange partition (<partition spec>) with
> table <source_table_name>;
>
> But in implementation, Dheeraj Kumar Singh, the original implementor, seemed
> confused and implemented this in inverted manner (target to source).
>
> HIVE-6129 fixed this(in 0.13.0) and now it's consistent to document. But
> the example has been not working in any version, because HIVE-4095 coerced target_table
> and source_table should have same partition columns.
>
> https://issues.apache.org/jira/browse/HIVE-6133 is the needed one for the
> example to work. But seemed to be failed to acquire any interest.
>
> Thanks,
> Navis
>
>
> 2014-07-21 11:40 GMT+09:00 Lefty Leverenz <le...@gmail.com>:
>
> I'd be happy to update the docs, but need some guidance. The sytax
>> confused me originally -- see comments on HIVE-4095
>> <https://issues.apache.org/jira/browse/HIVE-4095?focusedCommentId=13819885&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-13819885>.
>> I'll add this discussion to those comments.
>>
>>
>> -- Lefty
>>
>>
>> On Sun, Jul 20, 2014 at 10:22 PM, Andre Araujo <ar...@pythian.com>
>> wrote:
>>
>>> Indeed! The documentation is a fair bit off.
>>>
>>> I've tested the below on Hive 0.12 on CDH and it works fine.
>>> Lefty, would you please update the documentation on the two pages below?
>>>
>>> -----------------------
>>> Source:
>>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-ExchangePartition
>>> "Exchange Partition" section
>>>
>>> {code}
>>> ALTER TABLE source_table_name EXCHANGE PARTITION (partition_spec) WITH
>>> TABLE target_table_name;
>>> {code}
>>>
>>> This statement lets you move the data in a partition from a table to
>>> another table that has the same schema and partition keys, but does not
>>> already have that partition.
>>> For details, see Exchange Partition and HIVE-4095.
>>>
>>> -----------------------
>>> Source:
>>> https://cwiki.apache.org/confluence/display/Hive/Exchange+Partition
>>> The EXCHANGE PARTITION DDL command has been proposed as part of
>>> https://issues.apache.org/jira/browse/HIVE-4095.
>>>
>>> The syntax is:
>>>
>>> {code}
>>> alter table <source_table_name> exchange partition (<partition spec>)
>>> with table <target_table_name>;
>>> {code}
>>>
>>> The partition spec can be fully or partially specified.
>>>
>>> The semantics of the above statement is that the data is moved from the
>>> source table to the target table. Both the tables must have the same schema
>>> and the same partition keys. The operation fails in the presence of an
>>> index.
>>> The partition(s) must exist in the source table and mus NOT exists in
>>> the target one. Consider the following examples:
>>>
>>> ## Full partition spec
>>>
>>> {code}
>>> create table T1(a string, b string) partitioned by (ds string);
>>> create table T2(a string, b string) partitioned by (ds string);
>>> alter table T1 add partition (ds = '1');
>>> {code}
>>>
>>> The operation:
>>>
>>> {code}
>>> alter table T1 exchange partition (ds='1') with table T2;
>>> {code}
>>>
>>> moves the data from T1 to T2@ds=1. The operation fails if T2@ds=1
>>> already exists or T1 and T2 have different schemas and/or partition keys.
>>>
>>> ## Partial partition spec
>>>
>>> {code}
>>> create table T1(a string, b string) partitioned by (ds string, hr
>>> string);
>>> create table T2(a string, b string) partitioned by (ds string, hr
>>> string);
>>> alter table T1 add partition (ds = '1', hr = '00');
>>> alter table T1 add partition (ds = '1', hr = '01');
>>> alter table T1 add partition (ds = '1', hr = '03');
>>> {code}
>>>
>>> The operation:
>>>
>>> {code}
>>> alter table T1 exchange partition (ds='1') with table T2;
>>> {code}
>>>
>>> moves the 3 partitions from T1 to T2. The operation fails if any of the
>>> partitions already exist on T2 or if T1 and T2 have different schemas
>>> and/or partition keys.
>>> Either all the partitions of T1 will get created or the whole operation
>>> will fail. All partitions of T1 are dropped.
>>>
>>>
>>>
>>> On 21 July 2014 05:52, Kristof Vanbecelaere <
>>> kristof.vanbecelaere@gmail.com> wrote:
>>>
>>>> I think the documentation related to exchanging partitions is not
>>>> accurate
>>>>
>>>> https://cwiki.apache.org/confluence/display/Hive/Exchange+Partition
>>>>
>>>> when I try it out on hortonworks sandbox 2.1 which runs Hive 0.13 I get
>>>> this:
>>>>
>>>> hive> create table T1(a string, b string) partitioned by (ds string);
>>>>
>>>> OK
>>>>
>>>> Time taken: 0.72 seconds
>>>>
>>>> hive> create table T2(a string, b string);
>>>>
>>>> OK
>>>>
>>>> Time taken: 0.357 seconds
>>>>
>>>> hive> alter table T1 exchange partition (ds='1') with table T2;
>>>>
>>>> FAILED: SemanticException [Error 10235]: Tables have incompatible
>>>> schemas and their partitions cannot be exchanged.
>>>>
>>>>
>>>
>>>
>>> --
>>> André Araújo
>>> Big Data Consultant/Solutions Architect
>>> The Pythian Group - Australia - www.pythian.com
>>>
>>> Office (calls from within Australia): 1300 366 021 x1270
>>> Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696
>>> x1270
>>> Mobile: +61 410 323 559
>>> Fax: +61 2 9805 0544
>>> IM: pythianaraujo @ AIM/MSN/Y! or araujo@pythian.com @ GTalk
>>>
>>> “Success is not about standing at the top, it's the steps you leave
>>> behind.” — Iker Pou (rock climber)
>>>
>>> --
>>>
>>>
>>>
>>>
>>
>
--
André Araújo
Big Data Consultant/Solutions Architect
The Pythian Group - Australia - www.pythian.com
Office (calls from within Australia): 1300 366 021 x1270
Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696 x1270
Mobile: +61 410 323 559
Fax: +61 2 9805 0544
IM: pythianaraujo @ AIM/MSN/Y! or araujo@pythian.com @ GTalk
“Success is not about standing at the top, it's the steps you leave behind.”
— Iker Pou (rock climber)
--
--
Re: exchange partition documentation
Posted by Navis류승우 <na...@nexr.com>.
HIVE-4095, originally intended to implement,
alter table <target_table_name> exchange partition (<partition spec>) with
table <source_table_name>;
But in implementation, Dheeraj Kumar Singh, the original implementor, seemed
confused and implemented this in inverted manner (target to source).
HIVE-6129 fixed this(in 0.13.0) and now it's consistent to document. But
the example has been not working in any version, because HIVE-4095
coerced target_table
and source_table should have same partition columns.
https://issues.apache.org/jira/browse/HIVE-6133 is the needed one for the
example to work. But seemed to be failed to acquire any interest.
Thanks,
Navis
2014-07-21 11:40 GMT+09:00 Lefty Leverenz <le...@gmail.com>:
> I'd be happy to update the docs, but need some guidance. The sytax
> confused me originally -- see comments on HIVE-4095
> <https://issues.apache.org/jira/browse/HIVE-4095?focusedCommentId=13819885&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-13819885>.
> I'll add this discussion to those comments.
>
>
> -- Lefty
>
>
> On Sun, Jul 20, 2014 at 10:22 PM, Andre Araujo <ar...@pythian.com> wrote:
>
>> Indeed! The documentation is a fair bit off.
>>
>> I've tested the below on Hive 0.12 on CDH and it works fine.
>> Lefty, would you please update the documentation on the two pages below?
>>
>> -----------------------
>> Source:
>> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-ExchangePartition
>> "Exchange Partition" section
>>
>> {code}
>> ALTER TABLE source_table_name EXCHANGE PARTITION (partition_spec) WITH
>> TABLE target_table_name;
>> {code}
>>
>> This statement lets you move the data in a partition from a table to
>> another table that has the same schema and partition keys, but does not
>> already have that partition.
>> For details, see Exchange Partition and HIVE-4095.
>>
>> -----------------------
>> Source:
>> https://cwiki.apache.org/confluence/display/Hive/Exchange+Partition
>> The EXCHANGE PARTITION DDL command has been proposed as part of
>> https://issues.apache.org/jira/browse/HIVE-4095.
>>
>> The syntax is:
>>
>> {code}
>> alter table <source_table_name> exchange partition (<partition spec>)
>> with table <target_table_name>;
>> {code}
>>
>> The partition spec can be fully or partially specified.
>>
>> The semantics of the above statement is that the data is moved from the
>> source table to the target table. Both the tables must have the same schema
>> and the same partition keys. The operation fails in the presence of an
>> index.
>> The partition(s) must exist in the source table and mus NOT exists in the
>> target one. Consider the following examples:
>>
>> ## Full partition spec
>>
>> {code}
>> create table T1(a string, b string) partitioned by (ds string);
>> create table T2(a string, b string) partitioned by (ds string);
>> alter table T1 add partition (ds = '1');
>> {code}
>>
>> The operation:
>>
>> {code}
>> alter table T1 exchange partition (ds='1') with table T2;
>> {code}
>>
>> moves the data from T1 to T2@ds=1. The operation fails if T2@ds=1
>> already exists or T1 and T2 have different schemas and/or partition keys.
>>
>> ## Partial partition spec
>>
>> {code}
>> create table T1(a string, b string) partitioned by (ds string, hr string);
>> create table T2(a string, b string) partitioned by (ds string, hr string);
>> alter table T1 add partition (ds = '1', hr = '00');
>> alter table T1 add partition (ds = '1', hr = '01');
>> alter table T1 add partition (ds = '1', hr = '03');
>> {code}
>>
>> The operation:
>>
>> {code}
>> alter table T1 exchange partition (ds='1') with table T2;
>> {code}
>>
>> moves the 3 partitions from T1 to T2. The operation fails if any of the
>> partitions already exist on T2 or if T1 and T2 have different schemas
>> and/or partition keys.
>> Either all the partitions of T1 will get created or the whole operation
>> will fail. All partitions of T1 are dropped.
>>
>>
>>
>> On 21 July 2014 05:52, Kristof Vanbecelaere <
>> kristof.vanbecelaere@gmail.com> wrote:
>>
>>> I think the documentation related to exchanging partitions is not
>>> accurate
>>>
>>> https://cwiki.apache.org/confluence/display/Hive/Exchange+Partition
>>>
>>> when I try it out on hortonworks sandbox 2.1 which runs Hive 0.13 I get
>>> this:
>>>
>>> hive> create table T1(a string, b string) partitioned by (ds string);
>>>
>>> OK
>>>
>>> Time taken: 0.72 seconds
>>>
>>> hive> create table T2(a string, b string);
>>>
>>> OK
>>>
>>> Time taken: 0.357 seconds
>>>
>>> hive> alter table T1 exchange partition (ds='1') with table T2;
>>>
>>> FAILED: SemanticException [Error 10235]: Tables have incompatible
>>> schemas and their partitions cannot be exchanged.
>>>
>>>
>>
>>
>> --
>> André Araújo
>> Big Data Consultant/Solutions Architect
>> The Pythian Group - Australia - www.pythian.com
>>
>> Office (calls from within Australia): 1300 366 021 x1270
>> Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696
>> x1270
>> Mobile: +61 410 323 559
>> Fax: +61 2 9805 0544
>> IM: pythianaraujo @ AIM/MSN/Y! or araujo@pythian.com @ GTalk
>>
>> “Success is not about standing at the top, it's the steps you leave
>> behind.” — Iker Pou (rock climber)
>>
>> --
>>
>>
>>
>>
>
Re: exchange partition documentation
Posted by Lefty Leverenz <le...@gmail.com>.
I'd be happy to update the docs, but need some guidance. The sytax
confused me originally -- see comments on HIVE-4095
<https://issues.apache.org/jira/browse/HIVE-4095?focusedCommentId=13819885&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-13819885>.
I'll add this discussion to those comments.
-- Lefty
On Sun, Jul 20, 2014 at 10:22 PM, Andre Araujo <ar...@pythian.com> wrote:
> Indeed! The documentation is a fair bit off.
>
> I've tested the below on Hive 0.12 on CDH and it works fine.
> Lefty, would you please update the documentation on the two pages below?
>
> -----------------------
> Source:
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-ExchangePartition
> "Exchange Partition" section
>
> {code}
> ALTER TABLE source_table_name EXCHANGE PARTITION (partition_spec) WITH
> TABLE target_table_name;
> {code}
>
> This statement lets you move the data in a partition from a table to
> another table that has the same schema and partition keys, but does not
> already have that partition.
> For details, see Exchange Partition and HIVE-4095.
>
> -----------------------
> Source:
> https://cwiki.apache.org/confluence/display/Hive/Exchange+Partition
> The EXCHANGE PARTITION DDL command has been proposed as part of
> https://issues.apache.org/jira/browse/HIVE-4095.
>
> The syntax is:
>
> {code}
> alter table <source_table_name> exchange partition (<partition spec>) with
> table <target_table_name>;
> {code}
>
> The partition spec can be fully or partially specified.
>
> The semantics of the above statement is that the data is moved from the
> source table to the target table. Both the tables must have the same schema
> and the same partition keys. The operation fails in the presence of an
> index.
> The partition(s) must exist in the source table and mus NOT exists in the
> target one. Consider the following examples:
>
> ## Full partition spec
>
> {code}
> create table T1(a string, b string) partitioned by (ds string);
> create table T2(a string, b string) partitioned by (ds string);
> alter table T1 add partition (ds = '1');
> {code}
>
> The operation:
>
> {code}
> alter table T1 exchange partition (ds='1') with table T2;
> {code}
>
> moves the data from T1 to T2@ds=1. The operation fails if T2@ds=1 already
> exists or T1 and T2 have different schemas and/or partition keys.
>
> ## Partial partition spec
>
> {code}
> create table T1(a string, b string) partitioned by (ds string, hr string);
> create table T2(a string, b string) partitioned by (ds string, hr string);
> alter table T1 add partition (ds = '1', hr = '00');
> alter table T1 add partition (ds = '1', hr = '01');
> alter table T1 add partition (ds = '1', hr = '03');
> {code}
>
> The operation:
>
> {code}
> alter table T1 exchange partition (ds='1') with table T2;
> {code}
>
> moves the 3 partitions from T1 to T2. The operation fails if any of the
> partitions already exist on T2 or if T1 and T2 have different schemas
> and/or partition keys.
> Either all the partitions of T1 will get created or the whole operation
> will fail. All partitions of T1 are dropped.
>
>
>
> On 21 July 2014 05:52, Kristof Vanbecelaere <
> kristof.vanbecelaere@gmail.com> wrote:
>
>> I think the documentation related to exchanging partitions is not accurate
>>
>> https://cwiki.apache.org/confluence/display/Hive/Exchange+Partition
>>
>> when I try it out on hortonworks sandbox 2.1 which runs Hive 0.13 I get
>> this:
>>
>> hive> create table T1(a string, b string) partitioned by (ds string);
>>
>> OK
>>
>> Time taken: 0.72 seconds
>>
>> hive> create table T2(a string, b string);
>>
>> OK
>>
>> Time taken: 0.357 seconds
>>
>> hive> alter table T1 exchange partition (ds='1') with table T2;
>>
>> FAILED: SemanticException [Error 10235]: Tables have incompatible schemas
>> and their partitions cannot be exchanged.
>>
>>
>
>
> --
> André Araújo
> Big Data Consultant/Solutions Architect
> The Pythian Group - Australia - www.pythian.com
>
> Office (calls from within Australia): 1300 366 021 x1270
> Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696 x1270
> Mobile: +61 410 323 559
> Fax: +61 2 9805 0544
> IM: pythianaraujo @ AIM/MSN/Y! or araujo@pythian.com @ GTalk
>
> “Success is not about standing at the top, it's the steps you leave behind.”
> — Iker Pou (rock climber)
>
> --
>
>
>
>
Re: exchange partition documentation
Posted by Andre Araujo <ar...@pythian.com>.
Indeed! The documentation is a fair bit off.
I've tested the below on Hive 0.12 on CDH and it works fine.
Lefty, would you please update the documentation on the two pages below?
-----------------------
Source:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-ExchangePartition
"Exchange Partition" section
{code}
ALTER TABLE source_table_name EXCHANGE PARTITION (partition_spec) WITH
TABLE target_table_name;
{code}
This statement lets you move the data in a partition from a table to
another table that has the same schema and partition keys, but does not
already have that partition.
For details, see Exchange Partition and HIVE-4095.
-----------------------
Source: https://cwiki.apache.org/confluence/display/Hive/Exchange+Partition
The EXCHANGE PARTITION DDL command has been proposed as part of
https://issues.apache.org/jira/browse/HIVE-4095.
The syntax is:
{code}
alter table <source_table_name> exchange partition (<partition spec>) with
table <target_table_name>;
{code}
The partition spec can be fully or partially specified.
The semantics of the above statement is that the data is moved from the
source table to the target table. Both the tables must have the same schema
and the same partition keys. The operation fails in the presence of an
index.
The partition(s) must exist in the source table and mus NOT exists in the
target one. Consider the following examples:
## Full partition spec
{code}
create table T1(a string, b string) partitioned by (ds string);
create table T2(a string, b string) partitioned by (ds string);
alter table T1 add partition (ds = '1');
{code}
The operation:
{code}
alter table T1 exchange partition (ds='1') with table T2;
{code}
moves the data from T1 to T2@ds=1. The operation fails if T2@ds=1 already
exists or T1 and T2 have different schemas and/or partition keys.
## Partial partition spec
{code}
create table T1(a string, b string) partitioned by (ds string, hr string);
create table T2(a string, b string) partitioned by (ds string, hr string);
alter table T1 add partition (ds = '1', hr = '00');
alter table T1 add partition (ds = '1', hr = '01');
alter table T1 add partition (ds = '1', hr = '03');
{code}
The operation:
{code}
alter table T1 exchange partition (ds='1') with table T2;
{code}
moves the 3 partitions from T1 to T2. The operation fails if any of the
partitions already exist on T2 or if T1 and T2 have different schemas
and/or partition keys.
Either all the partitions of T1 will get created or the whole operation
will fail. All partitions of T1 are dropped.
On 21 July 2014 05:52, Kristof Vanbecelaere <kr...@gmail.com>
wrote:
> I think the documentation related to exchanging partitions is not accurate
>
> https://cwiki.apache.org/confluence/display/Hive/Exchange+Partition
>
> when I try it out on hortonworks sandbox 2.1 which runs Hive 0.13 I get
> this:
>
> hive> create table T1(a string, b string) partitioned by (ds string);
>
> OK
>
> Time taken: 0.72 seconds
>
> hive> create table T2(a string, b string);
>
> OK
>
> Time taken: 0.357 seconds
>
> hive> alter table T1 exchange partition (ds='1') with table T2;
>
> FAILED: SemanticException [Error 10235]: Tables have incompatible schemas
> and their partitions cannot be exchanged.
>
>
--
André Araújo
Big Data Consultant/Solutions Architect
The Pythian Group - Australia - www.pythian.com
Office (calls from within Australia): 1300 366 021 x1270
Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696 x1270
Mobile: +61 410 323 559
Fax: +61 2 9805 0544
IM: pythianaraujo @ AIM/MSN/Y! or araujo@pythian.com @ GTalk
“Success is not about standing at the top, it's the steps you leave behind.”
— Iker Pou (rock climber)
--
--