You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Павел Мезенцев <pa...@mezentsev.org> on 2012/11/15 09:14:10 UTC
hive 0.7.1 Error: Non-Partition column appears in the partition specification
Hello all!
I have a problem with dynamic partitions in hive 0.7.1.
For example I have 2 tables:
CREATE TABLE table1 (text STRING);
CREATE TABLE table2 (text STRING) PARTITIONED BY (author STRING);
And make insert into dynamic partition from table1 to table2
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
Query
INSERT OVERWRITE TABLE table2 PARTITION (author)
SELECT text FROM (SELECT text, 'Tolstoy' AS author FROM table1) tmp;
failes with error:
FAILED: Error in semantic analysis: Line 1:23 Cannot insert into target
table because column number/types are different author: Table insclause-0
has 2 columns, but query has 1 columns.
Query:
INSERT OVERWRITE TABLE table2 PARTITION (new_author)
SELECT text FROM (SELECT text, 'Tolstoy' AS new_author FROM table1) tmp;
failes with error:
FAILED: Error in semantic analysis: Non-Partition column appears in the
partition specification: new_author
What is happen? Is there any workaround for this problem?
I know that I can use static partition author = 'Tolsoy', but my real query
is more complex and dynamic partition calculates from several input fields.
Best regards
Mezentsev Pavel
Moscow.
Re: hive 0.7.1 Error: Non-Partition column appears in the partition specification
Posted by Ruslan Al-Fakikh <me...@gmail.com>.
Hey Pavel,
Also note that the dynamic partition values are selected by ordering, not
name, and taken as the last columns from the select clause.
So you have to have a column for the author partition in your most outer
'select'.
So your error messages are normal. In the first one you do not have a
column for the author partition in the outer select, in the second - you
have a wrong partition declaration as Nitin mentioned.
Ruslan
On Thu, Nov 15, 2012 at 2:56 PM, Nitin Pawar <ni...@gmail.com>wrote:
> you are little complicating your query with as and tmp tables
>
> you can just write simple query for same
> INSERT OVERWRITE TABLE table2 PARTITION (author)
> SELECT text, author FROM table1
>
> Tolstory is not any column in table1 so even that fail for query parsing
>
> if you want to all the data where author is tolstoy then you use where
> clause in query and solve that but then when you use then your partition
> (author='tolstoy')
>
>
> Thanks,
> Nitin
>
>
>
> On Thu, Nov 15, 2012 at 4:13 PM, Павел Мезенцев <pa...@mezentsev.org>wrote:
>
>> Thank you for right idea.
>> It is very strange, but normally executed query looks like:
>>
>> INSERT OVERWRITE TABLE table2 PARTITION (author)
>> SELECT text*, author* FROM (SELECT text, 'Tolstoy' AS author FROM
>> table1) tmp;
>>
>> Best regards
>> Mezentsev Pavel
>>
>>
>> 2012/11/15 Nitin Pawar <ni...@gmail.com>
>>
>>> when you add data to a partitioned table the partition column name in
>>> insert statement should match the table definition
>>>
>>> so try changing your insert query to "INSERT OVERWRITE TABLE table2
>>> PARTITION (author)"
>>> where author is the column in your table definition
>>>
>>> Thanks,
>>> Nitin
>>>
>>>
>>> On Thu, Nov 15, 2012 at 1:44 PM, Павел Мезенцев <pa...@mezentsev.org>wrote:
>>>
>>>> Hello all!
>>>>
>>>> I have a problem with dynamic partitions in hive 0.7.1.
>>>>
>>>> For example I have 2 tables:
>>>>
>>>> CREATE TABLE table1 (text STRING);
>>>> CREATE TABLE table2 (text STRING) PARTITIONED BY (author STRING);
>>>>
>>>> And make insert into dynamic partition from table1 to table2
>>>> SET hive.exec.dynamic.partition = true;
>>>> SET hive.exec.dynamic.partition.mode = nonstrict;
>>>>
>>>> Query
>>>> INSERT OVERWRITE TABLE table2 PARTITION (author)
>>>> SELECT text FROM (SELECT text, 'Tolstoy' AS author FROM table1) tmp;
>>>>
>>>> failes with error:
>>>> FAILED: Error in semantic analysis: Line 1:23 Cannot insert into target
>>>> table because column number/types are different author: Table insclause-0
>>>> has 2 columns, but query has 1 columns.
>>>>
>>>>
>>>> Query:
>>>> INSERT OVERWRITE TABLE table2 PARTITION (new_author)
>>>> SELECT text FROM (SELECT text, 'Tolstoy' AS new_author FROM table1) tmp;
>>>>
>>>> failes with error:
>>>> FAILED: Error in semantic analysis: Non-Partition column appears in the
>>>> partition specification: new_author
>>>>
>>>>
>>>> What is happen? Is there any workaround for this problem?
>>>>
>>>> I know that I can use static partition author = 'Tolsoy', but my real
>>>> query is more complex and dynamic partition calculates from several input
>>>> fields.
>>>>
>>>> Best regards
>>>> Mezentsev Pavel
>>>> Moscow.
>>>>
>>>>
>>>
>>>
>>> --
>>> Nitin Pawar
>>>
>>>
>>
>
>
> --
> Nitin Pawar
>
>
Re: hive 0.7.1 Error: Non-Partition column appears in the partition specification
Posted by Nitin Pawar <ni...@gmail.com>.
you are little complicating your query with as and tmp tables
you can just write simple query for same
INSERT OVERWRITE TABLE table2 PARTITION (author)
SELECT text, author FROM table1
Tolstory is not any column in table1 so even that fail for query parsing
if you want to all the data where author is tolstoy then you use where
clause in query and solve that but then when you use then your partition
(author='tolstoy')
Thanks,
Nitin
On Thu, Nov 15, 2012 at 4:13 PM, Павел Мезенцев <pa...@mezentsev.org> wrote:
> Thank you for right idea.
> It is very strange, but normally executed query looks like:
>
> INSERT OVERWRITE TABLE table2 PARTITION (author)
> SELECT text*, author* FROM (SELECT text, 'Tolstoy' AS author FROM table1)
> tmp;
>
> Best regards
> Mezentsev Pavel
>
>
> 2012/11/15 Nitin Pawar <ni...@gmail.com>
>
>> when you add data to a partitioned table the partition column name in
>> insert statement should match the table definition
>>
>> so try changing your insert query to "INSERT OVERWRITE TABLE table2
>> PARTITION (author)"
>> where author is the column in your table definition
>>
>> Thanks,
>> Nitin
>>
>>
>> On Thu, Nov 15, 2012 at 1:44 PM, Павел Мезенцев <pa...@mezentsev.org>wrote:
>>
>>> Hello all!
>>>
>>> I have a problem with dynamic partitions in hive 0.7.1.
>>>
>>> For example I have 2 tables:
>>>
>>> CREATE TABLE table1 (text STRING);
>>> CREATE TABLE table2 (text STRING) PARTITIONED BY (author STRING);
>>>
>>> And make insert into dynamic partition from table1 to table2
>>> SET hive.exec.dynamic.partition = true;
>>> SET hive.exec.dynamic.partition.mode = nonstrict;
>>>
>>> Query
>>> INSERT OVERWRITE TABLE table2 PARTITION (author)
>>> SELECT text FROM (SELECT text, 'Tolstoy' AS author FROM table1) tmp;
>>>
>>> failes with error:
>>> FAILED: Error in semantic analysis: Line 1:23 Cannot insert into target
>>> table because column number/types are different author: Table insclause-0
>>> has 2 columns, but query has 1 columns.
>>>
>>>
>>> Query:
>>> INSERT OVERWRITE TABLE table2 PARTITION (new_author)
>>> SELECT text FROM (SELECT text, 'Tolstoy' AS new_author FROM table1) tmp;
>>>
>>> failes with error:
>>> FAILED: Error in semantic analysis: Non-Partition column appears in the
>>> partition specification: new_author
>>>
>>>
>>> What is happen? Is there any workaround for this problem?
>>>
>>> I know that I can use static partition author = 'Tolsoy', but my real
>>> query is more complex and dynamic partition calculates from several input
>>> fields.
>>>
>>> Best regards
>>> Mezentsev Pavel
>>> Moscow.
>>>
>>>
>>
>>
>> --
>> Nitin Pawar
>>
>>
>
--
Nitin Pawar
Re: hive 0.7.1 Error: Non-Partition column appears in the partition specification
Posted by Павел Мезенцев <pa...@mezentsev.org>.
Thank you for right idea.
It is very strange, but normally executed query looks like:
INSERT OVERWRITE TABLE table2 PARTITION (author)
SELECT text*, author* FROM (SELECT text, 'Tolstoy' AS author FROM table1)
tmp;
Best regards
Mezentsev Pavel
2012/11/15 Nitin Pawar <ni...@gmail.com>
> when you add data to a partitioned table the partition column name in
> insert statement should match the table definition
>
> so try changing your insert query to "INSERT OVERWRITE TABLE table2
> PARTITION (author)"
> where author is the column in your table definition
>
> Thanks,
> Nitin
>
>
> On Thu, Nov 15, 2012 at 1:44 PM, Павел Мезенцев <pa...@mezentsev.org>wrote:
>
>> Hello all!
>>
>> I have a problem with dynamic partitions in hive 0.7.1.
>>
>> For example I have 2 tables:
>>
>> CREATE TABLE table1 (text STRING);
>> CREATE TABLE table2 (text STRING) PARTITIONED BY (author STRING);
>>
>> And make insert into dynamic partition from table1 to table2
>> SET hive.exec.dynamic.partition = true;
>> SET hive.exec.dynamic.partition.mode = nonstrict;
>>
>> Query
>> INSERT OVERWRITE TABLE table2 PARTITION (author)
>> SELECT text FROM (SELECT text, 'Tolstoy' AS author FROM table1) tmp;
>>
>> failes with error:
>> FAILED: Error in semantic analysis: Line 1:23 Cannot insert into target
>> table because column number/types are different author: Table insclause-0
>> has 2 columns, but query has 1 columns.
>>
>>
>> Query:
>> INSERT OVERWRITE TABLE table2 PARTITION (new_author)
>> SELECT text FROM (SELECT text, 'Tolstoy' AS new_author FROM table1) tmp;
>>
>> failes with error:
>> FAILED: Error in semantic analysis: Non-Partition column appears in the
>> partition specification: new_author
>>
>>
>> What is happen? Is there any workaround for this problem?
>>
>> I know that I can use static partition author = 'Tolsoy', but my real
>> query is more complex and dynamic partition calculates from several input
>> fields.
>>
>> Best regards
>> Mezentsev Pavel
>> Moscow.
>>
>>
>
>
> --
> Nitin Pawar
>
>
Re: hive 0.7.1 Error: Non-Partition column appears in the partition specification
Posted by Nitin Pawar <ni...@gmail.com>.
when you add data to a partitioned table the partition column name in
insert statement should match the table definition
so try changing your insert query to "INSERT OVERWRITE TABLE table2
PARTITION (author)"
where author is the column in your table definition
Thanks,
Nitin
On Thu, Nov 15, 2012 at 1:44 PM, Павел Мезенцев <pa...@mezentsev.org> wrote:
> Hello all!
>
> I have a problem with dynamic partitions in hive 0.7.1.
>
> For example I have 2 tables:
>
> CREATE TABLE table1 (text STRING);
> CREATE TABLE table2 (text STRING) PARTITIONED BY (author STRING);
>
> And make insert into dynamic partition from table1 to table2
> SET hive.exec.dynamic.partition = true;
> SET hive.exec.dynamic.partition.mode = nonstrict;
>
> Query
> INSERT OVERWRITE TABLE table2 PARTITION (author)
> SELECT text FROM (SELECT text, 'Tolstoy' AS author FROM table1) tmp;
>
> failes with error:
> FAILED: Error in semantic analysis: Line 1:23 Cannot insert into target
> table because column number/types are different author: Table insclause-0
> has 2 columns, but query has 1 columns.
>
>
> Query:
> INSERT OVERWRITE TABLE table2 PARTITION (new_author)
> SELECT text FROM (SELECT text, 'Tolstoy' AS new_author FROM table1) tmp;
>
> failes with error:
> FAILED: Error in semantic analysis: Non-Partition column appears in the
> partition specification: new_author
>
>
> What is happen? Is there any workaround for this problem?
>
> I know that I can use static partition author = 'Tolsoy', but my real
> query is more complex and dynamic partition calculates from several input
> fields.
>
> Best regards
> Mezentsev Pavel
> Moscow.
>
>
--
Nitin Pawar