Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - hive 0.7.1 Error: Non-Partition column appears in the partition specification


Copy link to this message
-
Re: hive 0.7.1 Error: Non-Partition column appears in the partition specification
Ruslan Al-Fakikh 2012-11-16, 16:23
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 <[EMAIL PROTECTED]>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, Павел Мезенцев <[EMAIL PROTECTED]>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 <[EMAIL PROTECTED]>
>>
>>> 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, Павел Мезенцев <[EMAIL PROTECTED]>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
>
>